Computer Science 455

First Hour Exam

Name _______________________________

Friday, Feb. 20

100 pts.

I. Some basics.

a. (10 pts.) Give a brief definition of a database management system, including some descriptive detail (i.e., a one sentence answer will probably not receive full credit).

b. (5 pts.) What is Data Independence, and why is it important?.

c. (15 pts.) Give brief definitions for the following:

key

foreign key

entity integrity

referential integrity

d. (5 pts.) Briefly, what is the role of the data administrator? The database administrator

e. (10 pts) Give brief (one sentence) descriptions of each of the three data models (hierarchical, network/CODASYL, and relational) we have studied so far.

f. (15 pts.) Consider the following entity-relationship diagram:

A is the key to T1, C is the key to T2, and E is the key of T3. Neither of the relations R1 and R2 have attributes. From this diagram, construct a relational schema, clearly identifying keys and any foreign keys in each table (write them down separately – don’t simply circle them).

Questions following are 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)

a. (10 pts.) Using both EASYALG and SQL, write queries to find the names (only) of parts stored in Paris (i.e., for which the CITY field in P is equal to ‘PARIS’

EASYALG

SQL

b. (10 pts.) Using both EASYALG and SQL, write queries to find the names of parts supplied by supplier S2..

EASYALG

SQL

c. (10 pts.) (SQL only) Write a query to find the names of suppliers who supply a blue part to a PARIS project (City field of J = ‘PARIS’).

d. (10 pts.) Write the CREATE TABLE command to create the supplier table. sno, sname, and city are all variable character fields of maximum length 10, status is a number, and sno is the key.