I. BASICS

1. (25 pts.) It is proposed to move an accounting system from a collection of (computer) files and programs to a database system. Considering such issues as data independence, control of redundancy, administrative oversight, and ease of access, say why this move would be a good idea. Express at least one (non financial) concern and say how that objection could be overcome.

2. (15 pts.) Give a description of the relational data model.

3. (15 pts.) Give a description of the object-oriented data model, and say why it may present an improvement over the relational model.

4. (15 pts.) Give a definition of BCNF (Boyce-Codd Normal Form), followed by an explanation suitable for someone who understands programming but does not know the definition of functional dependence (i.e., someone who has gone through CSci 255 or 261).

5. (15 pts.) The University maintains a number of residences, each of which have a number of rooms. In each room are one or more persons. One student is assigned to each residence to serve as a resident advisor (each RA is assigned to only one residence, however).

Sketch a brief Entity-relationship diagram for this situation.

6. (15 pts.) In the manner you have done on earlier exams, outline the steps you would follow to construct a menu giving the user options to enter a form, run a report, or to exit the menu.

II. Query languages.

The following questions are all with respect to the SPJ database:

S(SNO, SNAME, STATUS, CITY)

P(PNO, PNAME, COLOR, WEIGHT, CITY)

J(JNO, JNAME, CITY)

SPJ(SNO, PNO, JNO, QTY)

"SQL" refers to the SQL language. "ALGEBRA" refers to either EASYALG or to the full relational algebra (your choice, but some of the following can not be done with EASYALG). "CALCULUS" refers to the tuple calculus - either in the notational form used in class (or the book) or to the lisp-form used in the interpreter (your choice - either should be sufficient).

1. (30 pts.) Using SQL, ALGEBRA, and CALCULUS, write queries to find the names of projects using parts supplied by LONDON suppliers.

SQL

ALGEBRA

CALCULUS

2. Using SQL, ALGEBRA, and CALCULUS, write queries to find the names of projects using no parts supplied by a London supplier.

SQL

ALGEBRA

CALCULUS

3. Using SQL, ALGEBRA, and CALCULUS, write the queries to find the names of projects using parts supplied by every London supplier (i.e., if there is a London supplier, then that supplier supplies some part to this project. HINT: This is a "forall" query, and which part being supplied is not important).

SQL

ALGEBRA

CALCULUS

4. (10 pts). Using SQL, find the names of suppliers who supply more than two distinct parts.