Fordham, New York City's Jesuit University
back Back to course home page

FORDHAM UNIVERSITY, Fordham College Lincoln Center
CSEU 3500 -- Data Base Systems
Dept. of Computer & Info. Sciences, Spring, 2004

Homework for Chapter 6
Due date: Wednesday, April 21

Note: Questions marked with an asterisk are substantially different from the corresponding questions in the text. Relatively minor clarifications and additions to the other questions are in brackets.

[30 pts.] Consider the following relational database schema:
course ( c-num, title, credits )
course-offering ( c-num, sec-num, semester, instructor, room, time )
student ( name, id-num, major )
enroll ( c-num, sec-num, semester, id-num, grade )
The keys are underlined.

Give an SQL DDL definition of this database. Choose domains for the attributes that would be suitable for the values used at Fordham, e.g. for c-num like CSLU3500 and so forth. Identify referential-integrity constraints that should hold, and include them in the DDL definition. (For ``real'' SQL, you can change all hyphens to underscores.)

[20 pts.] Referential-integrity constraints as defined in this chapter involve exactly two relations. Consider a database that includes the following relations [(the keys are underlined)]:

salaried-worker ( name, office, phone, salary )
hourly-worker ( name, hourly-wage )
address ( name, street, city )

Suppose that we wish to require that every name that appears in address appear in either salaried-worker or hourly-worker, [and possibly] but not necessarily in both.
Propose a syntax [extending the SQL DDL if necessary] for expressing such constraints.
Discuss the actions that the system must take to enforce a constraint of this form. [Can this constraint be enforced with reasonable efficiency, as compared to the two-relation constraints?]

[10 pts.] SQL allows a foreign-key dependency to refer to the same relation, as in the following example:

create table manager
(employee-name char(20) not null,
manager-name char(20) not null,
primary key (employee-name),
foreign key (manager-name) references manager
on delete cascade )

Here, employee-name is a key to the table manager, meaning that each employee has at most one manager. The foreign-key clause requires that every manager also be an employee [who is managed, i.e., a subordinate]. Explain exactly what happens when a tuple in the relation manager is deleted. [That is, what steps must the system take to check the referential-integrity constraint and to maintain consistency if it is violated? Give an example, using a small instance of the manager table (with 4 or 5 rows), showing the sequence of steps that would occur following deletion of one row.]

Robert Moniot 2004-04-07