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.

6.2*

[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.)

6.3

[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.

(a)

Propose a syntax [extending the SQL DDL if necessary] for expressing such constraints.

(b)

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?]

6.4

[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.]