<From ERD to Relational Model (Tables) Part 1 |Java & Friends Activities |ERD to Relational Model Part 3 >

DATABASE MODELING & DESIGN:

From ERD To Relational Model Part 2

Converting Generalization Hierarchies

The approach to convert generalization hierarchies mimic the entity relationship notation as mush as possible. Rule 6 convert each ET of a generalization hierarchy into a table. The only column appearing that are different from attributes in the associated ERD is the inherited PK. In the following figure, EmpNo is a column in the SalaryEmp and HourlyEmp tables because it is the PK of the parent ET (Employee). In addition the SalaryEmp and HourlyEmp tables have a FK constraint referring to the Employee table. TheCASCADE delete option is set in both FK constraints.

Step-by-step on database modeling using ERD on MySQL database screenshots

CREATE TABLE Employee(
            EmpNo                  INTEGER,
            EmpName                VARCHAR(30),
            EmpHireDate            DATE,
            CONSTRAINT PKEmployee PRIMARY KEY (EmpNo)
);

CREATE TABLE SalaryEmp(
            EmpNo         INTEGER,
            EmpSalary     DECIMAL(10,2),
            CONSTRAINT PKSalaryEmp PRIMARY KEY (EmpNo),
            CONSTRAINT FKSalaryEmp FOREIGN KEY (EmpNo) REFERENCES Employee ON DELETE CASCADE
);

CREATE TABLE HourlyEmp(
            EmpNo          INTEGER,
            EmpRate        DECIMAL(10,2),
            CONSTRAINT PKHourlyEmp PRIMARY KEY (EmpNo),
            CONSTRAINT FKHourlyEmp FOREIGN KEY (EmpNo) REFERENCES Employee ON DELETE CASCADE
);

Converting the generic SQL script to tables we have the following:

Employee
EmpNo (PK)	EmpName	EmpHireDate	...
...	...	...	...

SalaryEmp
EmpNo (PK + FK)	EmpSalary	...
shared or inherited PK, FK is ON DELETE CASCADE	...	...

HourlyEmp
EmpNo (PK + FK)	EmpRate	...
Shared or inherited PK, FK is ON DELETE CASCADE	...	...

Well, let define rule no. 6:

6. Generalization Hierarchy Rule: Each ET of a generalization hierarchy becomes a table. The columns of a table are the attributes of the corresponding ET plus the PK of the parent ET. For each table representing a subtype, define a FK constraint that references the table corresponding to the parent ET. Use CASCADE (ON DELETE CASCADE) option for deletion of referenced rows.

Rule 6 also applies to generalization hierarchies of more than one level. To convert the generalization hierarchy of the following figure, five tables are produced as shown in the following SQL script. In each table, the PK of the parent (security) is included. In addition, FK constraints are added in each table corresponding to a subtype.

Step-by-step on database modeling using ERD on MySQL database screenshots

CREATE TABLE Security(
            Symbol        CHAR(6),
            SecName       VARCHAR(30),
            LastClose     DECIMAL(10,2),
            CONSTRAINT PKSecurity PRIMARY KEY (Symbol)
);

CREATE TABLE Stock(
            Symbol           CHAR(6),
            OutShares        INTEGER,
            IssuedShares     INTEGER,
            CONSTRAINT PKStock PRIMARY KEY (Symbol),
            CONSTRAINT FKStock FOREIGN KEY (Symbol) REFERENCES Security ON DELETE CASCADE
);

CREATE TABLE Bond(
            Symbol        CHAR(6),
            Rate          DECIMAL(12,4),
            FaceValue     DECIMAL(10,2),
            CONSTRAINT PKBond PRIMARY KEY (Symbol),
            CONSTRAINT FKBond FOREIGN KEY (Symbol) REFERENCES Security ON DELETE CASCADE
);

CREATE TABLE Common(
            Symbol          CHAR(6),
            PERatio         DECIMAL(12,4),
            Dividend        DECIMAL(10,2),
            CONSTRAINT PKCommon PRIMARY KEY (Symbol),
            CONSTRAINT FKCommon FOREIGN KEY (Symbol) REFERENCES Stock ON DELETE CASCADE
);

CREATE TABLE Preferred(
            Symbol           CHAR(6),
            CallPrice        DECIMAL(12,2),
            Arrears          DECIAML(10,2),
            CONSTRAINT PKPreferred PRIMARY KEY (Symbol),
            CONSTRAINT FKPreferred FOREIGN KEY (Symbol) REFERENCES Stock ON DELETE CASCADE
);

Converting the generic SQL script to tables we have the following:

Security
Symbol (PK)	SecName	LastClose	...
...	...	...	...

Stock
Symbol (PK + FK)	OutShares	IssuedShares	...
shared PK, FK is ON DELETE CASCADE	...	...	...

Bond
Symbol (PK + FK)	Rate	FaceValue	...
shared PK, FK is ON DELETE CASCADE	...	...	...

Common
Symbol (PK + FK)	PERatio	Dividend	...
inherited PK, FK is ON DELETE CASCADE	...	...	...

Preferred
Symbol (PK + FK)	CallPrice	Arrears	...
inherited PK, FK is ON DELETE CASCADE	...	...	...

Because the Relational Model does not directly support generalization hierarchies, there are several other ways to convert generalization hierarchies. The other approaches vary depending on the number of tables and the placement of inherited columns. Rule 6 may result in extra joins to gather all data about an entity, but there are no NULL values and only small amounts of duplicate data. For example, to collect all data about a common stock, you should join the Common, Stock and Security tables. Other conversion approaches may require fewer joins, but result in more redundant data and NULL values.

The SQL:2003 standard for object relational database supports generalization hierarchies for tables. In the SQL:2003 standard (ISO/IEC 9075(1-4,9-11,13,14):2003 –downloaded version), subtitle families provide a direct conversion from generalization hierarchies avoiding the loss of semantic information when converting to the traditional Relational Model. However, few commercial DBMS products fully support the object relational features in SQL 2003. Thus, usage of the generalization hierarchy conversion rule will likely be necessary.

Converting 1-1 Relationships

Outside of generalization hierarchies, 1-1 relationships are not common. They can occur when entities with separate identifiers are closely related. For example, the following figure shows the Employee and Office ETs connected by a 1-1 relationship.

Step-by-step on database modeling using ERD on MySQL database screenshots

Separate ETs seem intuitive, but 1-1 relationship connects the ETs. Rule 7 converts 1-1 relationships into 2 FK unless many NULL values will results. From the figure, most employees will not manage offices. Thus, the conversion in the following SQL script eliminates the FK (OfficeNo) in the employee table.

CREATE TABLE Employee(
            EmpNo       INTEGER,
            EmpName     VARCHAR(30),
            CONSTRAINT PKEmployee PRIMARY KEY (EmpNo)
);

CREATE TABLE Office(
            OfficeNo      INTEGER,
            OffAddress    VARCHAR(30),
            OffPhone      CHAR(10),
            EmpNo         INTEGER,
            CONSTRAINT PKOffice PRIMARY KEY (OfficeNo),
            CONSTRAINT FKEmpNo FOREIGN KEY (EmpNo) REFERENCES Employee,
            CONSTRAINT EmpNoUnique UNIQUE (EmpNo)
);

Converting the generic SQL script to tables we have the following:

Employee
EmpNo (PK)	EmpName	...
...	...	...

Office
OfficeNo (PK)	OffAddress	OffPhone	EmpNo (FK)	...
...	...	...	UNIQUE	...

7. 1-1 Relationship Rule: Each 1-1 relationship is converted into 2 FKs. If the relationship is optional with respect to one of the ETs, the corresponding FK may be dropped to eliminate NULL values.

THE WHOLE ERD SAMPLE FOR THE PREVIOUS EXAMPLE

Step-by-step on database modeling using ERD on MySQL database screenshots

Example on converting to MySQL script (relational model).

create table Student(
stdSSN                  char(11) not null,
stdFirstName            varchar(30) not null,
stdLastName             varchar(30) not null,
stdCity                 varchar(30) not null,
stdState                char(2) not null,
stdZip                  char(10) not null,
stdMajor                char(6),
stdClass                char(2),
stdGPA                  numeric(3,2),
CONSTRAINT StudentPk PRIMARY KEY (StdSSN)
)type=innodb;

create table Course(
CourseNo         char(6) not null,
crsDesc          varchar(50) not null,
CrsUnits         integer,
CONSTRAINT CoursePK PRIMARY KEY (CourseNo)
)type=innodb;

create table offering(
OfferNo           INTEGER not null,
CourseNo          char(6) not null,
OffTerm           char(6) not null,
OffYear           INTEGER not null,
OffLocation       varchar(30),
OffTime           varchar(10),
FacSSN            char(11),
OffDays           char(4),
CONSTRAINT OfferingPK PRIMARY KEY (OfferNo),
CONSTRAINT CourseFK FOREIGN KEY (CourseNo) REFERENCES Course(CourseNo),
CONSTRAINT FacultyFK FOREIGN KEY (FacSSN) REFERENCES Faculty(FacSSN)
)type=innodb;

create table Faculty(
FacSSN                  char(11) not null,
FacFirstName            varchar(30) not null,
FacLastName             varchar(30) not null,
FacCity                 varchar(30) not null,
FacState                char(2) not null,
FacZipCode              char(10) not null,
FacRank                 char(4),
FacHireDate             date,
FacSalary               numeric(10,2),
FacSupervisor           char(11),
FacDept                 char(6),
CONSTRAINT FacultyPK PRIMARY KEY (FacSSN),
CONSTRAINT SupervisorFK FOREIGN KEY (FacSupervisor) REFERENCES Faculty(FacSupervisor)
)type=innodb;

create table Enrollment(
OfferNo                  INTEGER not null,
StdSSN                   char(11) not null,
EnrGrade                 numeric(3,2),
CONSTRAINT EnrollmentPK PRIMARY KEY (OfferNo, StdSSN),
CONSTRAINT OfferingFK FOREIGN KEY (OfferNo) REFERENCES Offering
ON DELETE CASCADE,
CONSTRAINT StudentFK FOREIGN KEY (StdSSN) REFERENCES Student(StdSSN) ON DELETE CASCADE
)type=innodb;

Water Utility ERD With A Generalization Hierarchy Example

The following is the ERD diagram for Water Utility company. Study the converted Relational model as the in the SQL script.

Step-by-step on database modeling using ERD on MySQL database screenshots

The above figure shows ERD for water utility company. For brevity, some attributes have been omitted. The following SQL script (generic) shows the relational tables derived through the conversion rules.

CREATE TABLE Customer(
            CustNo        INTEGER,
            CustName      VARCHAR(30),
            CustType      CHAR(6),
            RateSetNo     INTEGER NOT NULL,
            CONSTRAINT PKCustomer PRIMARY KEY (CustNo),
            CONSTRAINT FKRateSetNo FOREIGN KEY (RateSetNo) REFERENCES RateSet
)

CREATE TABLE Commercial(
            CustNo             INTEGER,
            TaxPayerID         CHAR(30) NOT NULL,
            EnterpriseZone     BOOLEAN,
            CONSTRAINT PKCommercial PRIMARY KEY (CustNo),
            CONSTRAINT FKCommercial FOREIGN KEY (CustNo) REFERENCES Customer ON DELETE CASCADE
)

CREATE TABLE Residential(
            CustNo       INTEGER,
            Subsidized   BOOLEAN,
            DwellingType CHAR(6),
            CONSTRAINT PKResidential PRIMARY KEY (CustNo),
            CONSTRAINT FKResidential FOREIGN KEY (CustNo) REFERENCES Customer ON DELETE CASCADE
)

CREATE TABLE RateSet(
            RateSetNo   INTEGER,
            RSApprDate DATE,
            RSEffDate   DATE,
            CONSTRAINT PKRateSet PRIMARY KEY (RateSetNo)
)

CREATE TABLE Rate(
            RateSetNo    INTEGER,
            MinUsage     INTEGER,
            MaxUsage     INTEGER,
            FixedAmt     DECIMAL(10,2),
            CONSTRAINT PKRate PRIMARY KEY (RateSetNo, MinUsage),
            CONSTRAINT FKRateSetNo2 FOREIGN KEY (RateSetNo) REFERENCES RateSet
)

CREATE TABLE Meter(
            MeterNo         INTEGER,
            MtrSize         INTEGER,
            MtrModel        CHAR(6),
            CustNo          INTEGER NOT NULL,
            CONSTRAINT PKMeter PRIMARY KEY (MeterNo),
            CONSTRAINT FKCustNo FOREIGN KEY (CustNo) REFERENCES Customer
)

CREATE TABLE Reading(
            ReadNo        INTEGER,
            ReadTime      TIMESTAMP,
            ReadLevel     INTEGER,
            MeterNo       INTEGER NOT NULL,
            EmpNo         INTEGER NOT NULL,
            BillNo       INTEGER,
            CONSTRAINT PKReading PRIMARY KEY (ReadNo),
            CONSTRAINT FKEmpNo FOREIGN KEY (EmpNo) REFERENCES Employee,
            CONSTRAINT FKMeterNo FOREIGN KEY (MeterNo) REFERENCES Meter,
            CONSTRAINT FKBillNo FOREIGN KEY (BillNo) REFERENCES Bill
)

CREATE TABLE Bill(
            BillNo        INTEGER,
            BillDate       DATE,
            BillStartDate DATE,
            CONSTRAINT PKBill PRIMARY KEY (BillNo),
)

CREATE TABLE Employee(
            EmpNo       INTEGER,
            EmpName     VARCHAR(50),
            EmpTitle    VARCHAR(20),
            CONSTRAINT PKEmployee PRIMARY KEY (EmpNo),
)

Conversion rules used in this example are listed in the following table.

Rule	How it is used
1	All ETs except subtype converted to tables with PKs.
2	1-M relationships converted to FKs: Contains relationship to Rate.RateSetNo. Uses relationship to Meter.CustNo. ReadBy relationship to Reading.MeterNo. Includes relationship toReading.BillNo. Performs relationship to Reading.Empno. Assigned relationship to Customer.RateSetNo.
3	Not used because there are no M-M relationships.
4	PK of Rate table is a combination of RateSetNo and MinUsage.
5	Not used although it could have been used for the Includes relationship.
6	Subtypes (Commercial and Residential) converted to tables. PK of Customer is added to the Commercial and Residential tables. FK constraints with CASCADE DELETE options added to tables corresponding to the subtypes.
7	Not used. There is no 1-1 relationship.

Converting the ERD to Relational Model (MySQL)

(This is an optional part, you can skip this part)

Let try executing the previous script in MySQL. Firstly we create a database named WaterBill.

CREATE DATABASE WaterBill;

Step-by-step on database modeling using ERD on MySQL database screenshots

Next we use NetBeans 6.0 to execute our SQL script. Firstly we create a connection to the MySQL database.

Step-by-step on database modeling using ERD on MySQL database screenshots

The syntax for a foreign key constraint definition in InnoDB of MySQL looks like the following, so we need to do some editing:

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

We need to modify the SQL generic script to suit MySQL syntax. The edited parts are the FOREIGN KEYs. In addition we need to re-arrange the table creation order to make sure the FOREIGN KEY references are valid, that is the referred FOREIGN KEY tables must be created first and this also applies when you want to delete those tables.

CREATE TABLE Bill(
            BillNo         INTEGER,
            BillDate       DATE,
            BillStartDate DATE,
            CONSTRAINT PKBill PRIMARY KEY (BillNo)
)type=innodb;

CREATE TABLE Employee(
            EmpNo       INTEGER,
            EmpName     VARCHAR(50),
            EmpTitle    VARCHAR(20),
            CONSTRAINT PKEmployee PRIMARY KEY (EmpNo)
)type=innodb;

CREATE TABLE RateSet(
            RateSetNo   INTEGER,
            RSApprDate DATE,
            RSEffDate   DATE,
            CONSTRAINT PKRateSet PRIMARY KEY (RateSetNo)
)type=innodb;

CREATE TABLE Rate(
            RateSetNo    INTEGER,
            MinUsage     INTEGER,
            MaxUsage     INTEGER,
            FixedAmt     DECIMAL(10,2),
            CONSTRAINT PKRate PRIMARY KEY (RateSetNo, MinUsage),
            CONSTRAINT FKRateSetNo2 FOREIGN KEY (RateSetNo) REFERENCES RateSet(RateSetNo)
)type=innodb;

CREATE TABLE Customer(
            CustNo          INTEGER,
            CustName        VARCHAR(30),
            CustType        CHAR(6),
            RateSetNo       INTEGER NOT NULL,
            CONSTRAINT PKCustomer PRIMARY KEY (CustNo),
            CONSTRAINT FKRateSetNo FOREIGN KEY (RateSetNo) REFERENCES RateSet(RateSetNo)
)type=innodb;

CREATE TABLE Commercial(
            CustNo                 INTEGER,
            TaxPayerID             CHAR(30) NOT NULL,
            EnterpriseZone         BOOLEAN,
            CONSTRAINT PKCommercial PRIMARY KEY (CustNo),
            CONSTRAINT FKCommercial FOREIGN KEY (CustNo) REFERENCES Customer(CustNo) ON DELETE CASCADE
)type=innodb;

CREATE TABLE Residential(
            CustNo       INTEGER,
            Subsidized   BOOLEAN,
            DwellingType CHAR(6),
            CONSTRAINT PKResidential PRIMARY KEY (CustNo),
            CONSTRAINT FKResidential FOREIGN KEY (CustNo) REFERENCES Customer(CustNo) ON DELETE CASCADE
)type=innodb;

CREATE TABLE Meter(
            MeterNo         INTEGER,
            MtrSize         INTEGER,
            MtrModel        CHAR(6),
            CustNo          INTEGER NOT NULL,
            CONSTRAINT PKMeter PRIMARY KEY (MeterNo),
            CONSTRAINT FKCustNo FOREIGN KEY (CustNo) REFERENCES Customer(CustNo)
)type=innodb;

CREATE TABLE Reading(
            ReadNo        INTEGER,
            ReadTime      TIMESTAMP,
            ReadLevel     INTEGER,
            MeterNo       INTEGER NOT NULL,
            EmpNo         INTEGER NOT NULL,
            BillNo       INTEGER,
            CONSTRAINT PKReading PRIMARY KEY (ReadNo),
            CONSTRAINT FKEmpNo FOREIGN KEY (EmpNo) REFERENCES Employee(EmpNo),
            CONSTRAINT FKMeterNo FOREIGN KEY (MeterNo) REFERENCES Meter(MeterNo),
            CONSTRAINT FKBillNo FOREIGN KEY (BillNo) REFERENCES Bill(BillNo)
)type=innodb;

------------------------------------------------------------------------------------------------------------

Step-by-step on database modeling using ERD on MySQL database screenshots

Make sure there is no error in the Output window.

Step-by-step on database modeling using ERD on MySQL database screenshots

Next, continue on this tutorial, Part 3

The following are useful notes and tutorials on ERD, Normalization and database modeling in PDF format:

Database Modeling & Design:Part 1 |Part 2 |Part 3

<From ERD to Relational Model (Tables) Part 1 |Java & Friends Activities |ERD to Relational Model Part 3 >