Page 127 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Database Management Systems/Managing Database

Notes CREATE TABLE Manages (ssn CHAR(11),
Did INTEGER,
since DATE,
PRIMARY KEY(did),
FOREIGN KEY(ssn) REFERENCES Employees,
FOREIGN KEY(did) REFERENCES Departments )
A second approach to translating a relationship set with key constraints is often superior because
it avoids creating a distinct table for the relationship set.
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. In this case the added fields would have to be filled with null values. The first
translation (using a separate table for Manages) avoids this inefficiency, but some important
queries require us to combine information from two relations, which can be a slow operation.
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)

Notes The ssn can take on null values.

Translating Relationship Sets with Participation Constraints: Consider the ER diagram in
Figure 7.5, which shows two relationship sets, Manages and Works. In Every department is
required to have a manager, due to the participation constraint, and at most one manager, due
to the key 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)REFERENCESEmployees,
ON DELETE NO ACTION )

Table constraints and assertions can be specified using the null power of the SQL query language
and are very expressive, but also very expensive to check and enforce.

Example: We cannot enforce the participation constraints on the Works_In relation
without using these general constraint.

To ensure total participation of Departments in Works_In, we have to guarantee that every did
value in Departments appears in a tuple of Works_In.

120 LOVELY PROFESSIONAL UNIVERSITY