Page 126 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Unit 7: Relational Database Design

Notes
Figure 7.3: A Ternary Relationship Set

since
name dname
ssn lot did budget

Employees Works_In2 Departments

address Locations capacity

All the available information about the Works_In2 table is captured by the following SQL
definition:
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)REFERENCESLocations,
FOREIGN KEY(did) REFERENCES Departments)

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. These
constraint ensures that these fields uniquely identify a department, an employee, and a location
in each tuple of Works_In.
Translating Relationship Sets with Key Constrains: If a relationship set involves n entity sets
and some m of them are linked via arrows in the ER diagram, the key for any one of these m
entity sets constitutes a key for the relation to which the relationship set is mapped. Thus we
have m candidate keys, and one of these should be designated as the primary key.
Consider the relationship set Manages shown in Figure 7.4. The table corresponding to Manages
has the attributes ssn, did, since. However, because each department has at most one manager,
no two tuples can have the same did value but differ on the ssn value. A consequence of this
observation is that did is itself a key for Manages; indeed, the set did, ssn is not a key (because it
is not minimal). The Manages relation can be defined using the following SQL statement:

Figure 7.4: Key Constraint on Manages

since
name dname
ssn lot did budget

Employees Manages Departments

LOVELY PROFESSIONAL UNIVERSITY 119