Computer Science 455

Spring 2002

Final Exam (bluebook)

180 points (will be normalized to 200 in the gradebook)

Basic instructions:

1. Please be sure to put your name on the cover of each bluebook that you use. To be on the safe side, you could also put your name on the back of the last sheet in the bluebook. Please do not put your name on other pages in the bluebook – I prefer to grade exams anonymously.

2. Please start a new problem on a new page. If a problem has subparts (for example, problem #1 asks for several definitions), do not start the subsections on a new page (we don't have enough bluebooks for that).

3. It is generally not possible to do an exam in order. Don't worry about that, but please be sure to clearly indicate what problem you are working on, and (leaving space if necessary) please don't break up a problem into separate chunks in your bluebook (I might miss something if you do). If you do have two parts of a problem separated by another problem, please clearly say so and point to where the rest of the problem can be found.

4. Please indicate clearly what problem you are solving. If you are doing definitions or essay questions, please clearly indicate which one you are doing.

I. Definitions and short descriptions/answers (in your blue book, please clearly write down the term you are defining). (5 points each – 50 pts. total)

1. Required

a. Database Management System (note: to say "software that manages a database" is not enough" for this problem)

b. Relational model

c. Key

d. Boyce-Codd Normal Form

2. Please answer six of the following questions/definitions

a. Define: Data independence

b. Define: Entity Integrity

c. Define: Referential Integrity

d. How does SQL enforce entity integrity?

e. How does SQL enforce referential integrity?

f. Give an example of a DML statement in SQL

g. Give an example of a DDL statement in SQL

h. Define: Functional Dependency

i. Define: Second Normal Form

j. Define: Third Normal Form

k: Give an example of a SQL query with a relational algebra element.

II. Design issues:

1. (20 pts.) Enormous Publishing Company (EPC) is organized into a number of specialty publishing houses (Mystery Press, SF Pub., etc.). EPC manages a number of authors, who each publish a number of books. Each publishing house publishes a number of these authors, and each author publishes with a number of houses (the relationship between Publishing House and Author is many-to-many). No books are jointly authored (the relation between author and book is one-to-many). Each publishing house has a title (Mystery Press) and a description. Each author has a social security number and a name. Each book has an ISBN and a title.

a. Using the above information, construct an Entity-Relationship diagram.

b. From the Entity-Relationship diagram construct a relational schema.

c. In each table in the relational schema, identify primary and foreign keys.

d, With respect to your relational schema, write a SQL query to find the book title, author, and publishing house for the book with

ISBN = "0-12-123456-1".

2. (20 pts.) List and briefly describe the steps to be taken by the database designer in examining a collection of forms and reports and constructing from them a bottom-up relational schema.

III. Some final language issues

1. (10 pts. each). Do each of the following queries (with respect to the SPJ database – last page of this exam) in SQL and in the relational algebra:

a. Find the names of parts used on London projects

b. Find the names of parts used on no London project

c. Find the names of parts used on all London projects.

2. (15 pts.) Construct a view SP on the SPJ table which includes only supplier and part numbers, and, for each sno,pno pair, the total quantity of the part pno supplied by sno (sum over jno).

IV. Some Theory

1. (15 pts.) What are the essential features of the relational model? What do we mean by essential in this context?

2. (10 pts.) Suppose that A = {a, b, c, d}, B = {c, d, e, f}, and C = {x,y}. What is the result (as sets) of the following operations:

3. (10 pts.) What exactly do we mean when we say that SQL is at least as powerful as the full Relational Algebra?

V. Two essays (choice of topics - 20 points each)

Pick two of the following five topics (only the first two seen will be graded). I will be looking for clarity of expression and the inclusion of some detail in your answers.

1. In the form of a memo to your boss (be sure not to include your own name here), justify the acquisition of a database system. Be sure to describe what one is and what problems (discussion in Date) it solves.

2. We have looked (briefly) at the hierarchical and network models for database systems, at the relational and entity-relationship model in some more detail, and finally taken a brief look at the notion of an object-oriented model. Does this appear to be going in some direction? (some possibilities might be: simplification, inclusion of more semantic material, modeling closer to the user's way of looking at things). Write a response, including in your answer some comparison between the various models.

3. Why do we call a relational database management system relational?

4. Briefly describe the software engineering process (requirements, design, implementation, testing) as it applies to the design and implementation of a database application.

5. (for students who have taken the operating system class): What is an operating system? Make an argument that a database management system (use Oracle as an example) is an operating system.

S(SNO, SNAME, STATUS, CITY)

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

J(JNO, JNAME, CITY)

SPJ(SNO, PNO, JNO, QTY)