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.

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.

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.

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 following is the ERD diagram for Water Utility company. Study the converted Relational model as the in the SQL script.

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.

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;

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

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.