Page 128 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 7: Relational Database Design

Notes
Figure 7.5: Manages and Works_In

since
name dname
ssn lot did budget

Employees Manages Departments

Works_In

since

Translating Weak Entity Sets: A weak entity set always participates in a one-to-many binary
relationship and has a key constraint and total participation. The weak entity has only a partial
key. Also, when an owner entity is deleted, we want all owned weak entities to be deleted.
Consider the Dependents weak entity set shown in Figure 7.6, with partial key pname.
A Dependents entity can be identified uniquely only if we take the key of the owning Employees
entity and the pname of the Dependents entity and the Dependents entity must be deleted if the
owning Employees entity is deleted.
We can capture the desired semantics with the following definition of the Dep-Policy relation:
CREATE TABLE Dep_Policy (pname CHAR(20),
age INTEGER,
cost REAL,
ssn CHAR(11),
PRIMARY KEY(pname, ssn),
FOREIGN KEY (ssn) REFERENCES Employees,
ONDELETECASCADE )

Figure 7.6:The Dependents Weak Entity Set

name cost pname age
ssn lot

Employees Policy Dependents

The primary key is pname, ssn, since Dependent is a weak entity.
Translating Class Hierarchies: We present the two basic approaches to handling ISA hierarchies
by applying them to the ER diagram.

LOVELY PROFESSIONAL UNIVERSITY 121