ER to Relational Mapping

ER to Relational Mapping

AU: May-17, Dec.-19, Marks 13

In this section we will discuss how to map various ER model constructs to Relational Model construct.

Mapping of Entity Set to Relationship

• An entity set is mapped to a relation in a straightforward way.

• Each attribute of entity set becomes an attribute of the table.

• The primary key attribute of entity set becomes an entity of the table.

• For example - Consider following ER diagram.

The converted employee table is as follows –

The SQL statement captures the information for above ER diageam as follows -

CREATE TABLE Employee( EmpID CHAR(11),

EName CHAR(30),

Salary INTEGER,

PRIMARY KEY(EmpID))

Mapping Relationship Sets(Without Constraints) to Tables

• Create a table for the relationship set.

• Add all primary keys of the participating entity sets as fields of the table.

• Add a field for each attribute of the relationship.

• Declare a primary key using all key fields from the entity sets.

• Declare foreign key constraints for all these fields from the entity sets.

For example - Consider following ER model

The SQL statement captures the information for relationship present in above ER diagram as follows -

CREATE TABLE Works In (EmpID CHAR(11),

DeptID CHAR(11),

EName CHAR(30),

Salary INTEGER,

DeptName CHAR(20),

Building CHAR(10),

PRIMARY KEY(EmpID,DeptID),

FOREIGN KEY (EmpID) REFERENCES Employee,

FOREIGN KEY (DeptID) REFERENCES Department

)

Mapping Relationship Sets( With Constraints) to Tables

• If a relationship set involves n entity sets and some m of them are linked via arrows in the ER diagram, the key for anyone of these m entity sets constitutes a key for the relation to which the relationship set is mapped.

• Hence we have m candidate keys, and one of these should be designated as the primary key.

• There are two approaches used to convert a relationship sets with key constraints into table.

• Approach 1:

      • By this approach the relationship associated with more than one entities is separately represented using a table. For example - Consider following ER diagram. Each Dept has at most one manager, according to the key constraint on Manages.

Here the constraint is each department has at the most one manager to manage it. Hence no two tuples can have same DeptID. Hence there can be a separate table named Manages with DeptID as Primary Key. The table can be defined using following SQL statement

CREATE TABLE Manages (EmpID CHAR(11),

DeptID INTEGER,

Since DATE,

PRIMARY KEY (DeptID),

FOREIGN KEY (EmpID) REFERENCES Employees,

FOREIGN KEY (DeptID) REFERENCES Departments)

Approach 2:

     • In this approach, it is preferred to translate a relationship set with key constraints.

     • It is a superior approach because, it avoids creating a distinct table for the relationship set.

    • The idea is to include the information about the relationship set in the table corresponding to the entity set with the key, taking advantage of the key constraint.

   • This approach eliminates the need for a separate Manages relation, and queries asking for a department's manager can be answered without combining information from two relations.

   • The only drawback to this approach is that space could be wasted if several departments have no managers.

     • The following SQL statement, defining a Dep_Mgr relation that captures the information in both Departments and Manages, illustrates the second approach to translating relationship sets with key constraints:

CREATE TABLE Dep_Mgr (DeptID INTEGER,

DName CHAR(20),                                             

Budget REAL,

EmpID CHAR (11),

since DATE,

PRIMARY KEY (DeptID),

FOREIGN KEY (EmpID) REFERENCES Employees)

Mapping Weak Entity Sets to Relational Mapping

• A weak entity can be identified uniquely only by considering the primary key of another (owner) entity. Following steps are used for mapping Weak Entity Set to Relational Mapping

• Create a table for the weak entity set.

• Make each attribute of the weak entity set a field of the table. AI baris M

• Add fields for the primary key attributes of the identifying owner.

• Declare a foreign key constraint on these identifying owner fields.

• Instruct the system to automatically delete any tuples in the table for which there are no owners

For example - Consider following ER model,

Following SQL Statement illustrates this mapping

CREATE TABLE Department (DeptID CHAR(11),

DeptName CHAR(20),

Bldg No CHAR(5),

PRIMARY KEY (DeptID,Bldg_No),

FOREIGN KEY(Bldg_No) References Buildings on delete cascade

)

Mapping of Specialization / Generalization (EER Construct)to Relational Mapping

The specialialization/Generalization relationship (Enhanced ER Construct) can be mapped to database tables(relations) using three methods. To demonstrate the methods, we will take the - InventoryItem, Book,  DVD

Method 1: All the entities in the relationship are mapped to individual tables

InventoryItem(ID, name)

Book(ID,Publisher)

DVD(ID, Manufacturer)

Method 2: Only subclasses are mapped to tables. The attributes in the superclass are duplicated in all subclasses. For example -

Book(ID,name, Publisher)

DVD(ID, name, Manufacturer)

Method 3: Only the superclass is mapped to a table. The attributes in the subclasses are taken to the superclass. For example -

InventoryItem(ID, name, Publisher, Manufacturer)

This method will introduce null values. When we insert a Book record in the table, the Manufacturer column value will be null. In the same way, when we insert a DVD record in the table, the Publisher value will be null.

Example 2.6.1 Construct an E-R diagram for a hospital with a set of patients and a set of medical doctors. Associate with each patient a log of the various tests and examinations conducted. Also construct appropriate tables for the ER diagram you have drawn.

Solution:

ER Diagram - Refer example 2.5.8.

Relational Mapping

Patients (P_id, name, insurance, date-admitted, date-checked-out)

Doctors (Dr_id, name, specialization)

Test (testid, testname, date, time, result)

doctor-patient (P_id, Dr_id)

test-log (testid, P_id) performed-by (testid, Dr_id)

Review Questions

1. Discuss the correspondence between the ER model construct and the relational model constructs. Show how each ER model construct can be mapped to the relational model. Discuss the option for mapping EER construct.     AU: May-17, Marks 13 2.

2. Discuss in detail the steps involved in the ER to relational mapping in the process of relational database design.