ER-Diagram to Relational Database Design

Entity Sets to Tables

An entity set is converted into a relation by mapping each attribute of the set to an attribute of the table, ensuring that we know the domain and primary key of each attribute.

The following SQL statement captures the preceding information, including the domain constraints and key information:

CREATE TABLE Employees ( ssn CHAR(11),
	name CHAR(30),
	lot INTEGER,
	PRIMARY KEY (ssn) )

Relationship Sets (without Constraints) to Tables

The relational model maps a relationship set to an entity set, starting with relationship sets without key and participation constraints. To represent a relationship, each participating entity must be identified and its descriptive attributes, such as values, must be assigned.

Thus, the attributes of the relation include:

• the primary key attributes of each participating entity set, as foreign key fields. 
• the descriptive attributes of the relationship set.

Consider the Works_In2 relationship set shown in above Figure. Each department has offices in several locations and we want to record the locations at which each employee works.

CREATE TABLE Works In2 ( ssn CHAR(11),
	did INTEGER,
	address CHAR(20),
	since DATE,
	PRIMARY KEY (ssn, did, address),
	FOREIGN KEY (ssn) REFERENCES Employees,
	FOREIGN KEY (address) REFERENCES Locations,
	FOREIGN KEY (did) REFERENCES Departments )

Note that the address, did, and ssn fields cannot take on null values. Because these fields are part of the primary key for Works_In2, a NOT NULL constraint is implicit for each of these fields.

Finally, consider the Reports To relationship set shown in the following Figure. The role indicators supervisor and subordinate are used to create meaningful field names in the CREATE statement for the Reports To table:

Observe that we need to explicitly name the referenced field of Employees because the field name differs from the name(s) of the referring field(s).

CREATE TABLE Reports To (
	supervisor ssn CHAR(11),
	subordinate ssn CHAR(11),
	PRIMARY KEY (supervisor ssn, subordinate ssn),
	FOREIGN KEY (supervisor ssn) REFERENCES Employees(ssn),
	FOREIGN KEY (subordinate ssn) REFERENCES Employees(ssn) )

Translating Relationship Sets with Key Constraints

A relationship set with n entity sets and m linked via arrows in the ER diagram has m candidate keys, with one of these being the primary key. The translation from relationship sets to a relation can be used in the presence of key constraints, considering this point about keys.

The Manages relation can be defined using the following SQL statement:

CREATE TABLE Manages ( ssn CHAR(11),
	did INTEGER,
	since DATE,
	PRIMARY KEY (did),
	FOREIGN KEY (ssn) REFERENCES Employees,
	FOREIGN KEY (did) REFERENCES Departments )

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

CREATE TABLE Dept Mgr ( did INTEGER,
dname CHAR(20),
budget REAL,
ssn CHAR(11),
since DATE,
PRIMARY KEY (did),
FOREIGN KEY (ssn) REFERENCES Employees )

Translating Relationship Sets with Participation Constraints

The SQL statement reflects the second translation approach discussed in the previous sub-section, requiring every department to have at least one manager due to the participation constraint and the key constraint.

The text highlights the importance of capturing the participation constraint that every department must have a manager. Each tuple of Dept_Mgr identifies a tuple in Employees, who is the manager. The default NO ACTION specification ensures that an Employees tuple cannot be deleted while it is pointed to by a Dept_Mgr tuple. To delete such an Employees tuple, the Dept_Mgr tuple must be changed to have a new employee as manager. 

 

The constraint that every department must have a manager cannot be captured using the first translation approach of previous subsection. The second approach is preferred for one-to-many relationships, especially when the entity set with the key constraint also has a total participation constraint.

CREATE TABLE Dept_Mgr ( did INTEGER,
	dname CHAR(20),
	budget REAL,
	ssn CHAR(11) NOT NULL,
	since DATE,
	PRIMARY KEY (did),
	FOREIGN KEY (ssn) REFERENCES Employees
	ON DELETE NO ACTION )

Translating Weak Entity Sets

A weak entity set is a binary relationship with a key constraint and total participation. The second translation approach is ideal, but it must consider the partial key of the weak entity. When an owner entity is deleted, all owned weak entities must be deleted. For example, the Dependents weak entity set has a partial key pname, and a unique identifier is achieved by taking the key of the owning Employees entity and the pname of the Dependents entity.

The primary key for a relational database is pname and ssn, as Dependents is a weak entity. This constraint ensures that every Dependents entity is associated with an Employees entity (owner), and ssn cannot be null. The CASCADE option deletes information about an employee's policy and dependents if the corresponding Employees tuple is deleted.

CREATE TABLE Dep Policy ( pname CHAR(20),
	age INTEGER,
	cost REAL,
	ssn CHAR(11),
	PRIMARY KEY (pname, ssn),
	FOREIGN KEY (ssn) REFERENCES Employees
	ON DELETE CASCADE )

Translating Class Hierarchies

We can map each of the entity sets Employees, Hourly_Emps, and Contract_Emps to a distinct relation. We discuss Hourly_Emps here; Contract_Emps is handled similarly. The relation for Hourly_Emps includes the hourly wages and hours worked attributes of Hourly_Emps. It also contains the key attributes of the superclass (ssn, in this example), which serve as the primary key for Hourly_Emps, as well as a foreign key referencing the superclass (Employees). 

 

For each Hourly Emps entity, the value of the name and lot attributes are stored in the corresponding row of the superclass (Employees). Note that if the superclass tuple is deleted, the delete must be cascaded to Hourly_Emps.

Alternatively, we can create just two relations, corresponding to Hourly_Emps and Contract_Emps. The relation for Hourly_Emps includes all the attributes of Hourly_Emps as well as all the attributes of Employees (i.e., ssn, name, lot, hourly wages, hours worked).

Translating ER Diagrams with Aggregation

The Employees, Projects, and Departments entity sets and the Sponsors relationship set are mapped using the standard mapping for a relationship set. For the Monitors relationship set, a relation is created with key attributes of Employees (ssn), Sponsors (did, pid), and descriptive attributes of Monitors (until). However, there is a special case where this translation can be further refined by dropping the Sponsors relation. The Sponsors relation has attributes pid, did, and since, which are needed for recording descriptive attributes and not every sponsorship has a monitor. 

 

If Sponsors has no descriptive attributes and has total participation in Monitors, every possible instance of the Sponsors relation can be obtained by looking at the pid, did columns of the Monitors relation, so the Sponsors relation is not stored.

---

Happy Exploring!