Computer Science 455

Final Exam

Name _________________

The relational schema for the supplier-parts-projects (SPJ) database:

s(sno, sname, status, city)

p(pno, pname, color, weight, city)

j(jno, jname, city)

spj(sno, pno, jno, qty)

Friday, May 14, 2004

8:00 AM – 10:00 AM

200 pts

I. Some general ideas

A. (30 pts.) You are gathered around the camp fire after a long and pleasant day of hiking in the Hoh River Rain Forest. Although the purpose of this break was to forget about school for a time, the conversation has drifted to a discussion of the year just ended. When someone asks “What did you take last term?” you reply “The database course.” All are interested (as who would not be?). “What is a database?” they ask. “What is a database management system?” “What problems does it solve?” “What is this relational model?” And you go on to tell them, making sure to talk about data independence, integrity constraints, and the like. What would you say? Please note that you have this page and the next for your answer.

( more workspace for problem 1 (A) (if you want it))

B. “What about data normalization?” someone asks. “Can you give me a simple statement to describe what that means?”. You recall that the person asking the question is an English major, and well aware of the roles that paragraphs play in an essay.

1. (5 pts.) Give your friend a concise statement about the goal of data normalization in design.

2. (10 pts.) Sadly, one of your friends has heard about data normalization and asks for more details. Give a definition of second normal form, and give an example (via a functional dependency diagram of the sort used in class) of a relation not in second normal form (but in first normal form).

3. (10 pts.) You are being pressed even further by your friend. Give a definition of third normal form and, once again, a functional dependency of a diagram showing what can go wrong (your example should be in second normal form).

4. (10 pts.) You give up. Give your friend a definition of a determinant (with an example), and a formal definition of BCNF. You can leave the informal definition for the hike tomorrow.

C. Top-down design and entity-relationship diagrams.

1. (10 pts.) A video store rents DVDs to customers. Each customer is identified by a customer number, customer name, and an address (assume a single field for this). DVDs have a DVD number, title, genre, ranking (e.g., PG-13), and a daily rental cost. Customers may rent several DVDs at one time: we want to record and store when they were checked out, when returned, and the total rental cost for that rental. Each DVD, of course, can be rented (over time) by several customers.

Sketch an entity-relationship diagram for this.

2. (10 pts.) Consider your solution to the previous problem (the DVD rental store). Convert your entity-relationship diagram to a relational schema (list of tables and fields), clearly identifying any primary and foreign keys.

D. (15 pts) The process of developing a database application is a software engineering process, one which we’ve just been through in this course. Describe how the discipline of software engineering is applied to a database application, listing and briefly describing the steps in the process.

II. Some technical details.

A. (15 pts.) We refer to SQL as a hybrid language: Mostly relational calculus with some relational algebra mixed in. What do we mean by “relational algebra” and “relational calculus”? What is the difference between that calculus and the algebra approach? Illustrate your answer with some appropriate details of SQL.

B. SQL consists of a data definition language (DDL) together with a data manipulation language (DML). Here are some questions on the DDL side of SQL.

1. (10 pts.) A student entity consists of a student number, student name, major, and a faculty advisor (a faculty number). The student number is the key, and the faculty number is a foreign key for which we want to maintain referential integrity. Assuming reasonable data types, write the appropriate CREATE TABLE statement for the student table.

2. (5 pts.) Write the statement necessary to create a non-unique index on the MAJOR field of the student table just created in problem B.1.

3. (5 pts.) Create a view on the student table defined above which presents only those students with MAJOR = “CSCI”.

C. Let’s do some DML. All of the following questions call for SQL queries, but some will ask for relational algebra as well. For the relational algebra questions you are free to use either form of the relational algebra we have used this term, remembering that divideby, minus, times, intersect, and union require the full relational algebra.

Remember: p – parts, j – projects.

1. (15 pts.) Using both SQL and the relational algebra, write queries to find the names (only) of parts used on London projects.

SQL

Algebra (either variety)

2. (10 pts.) Find the names of parts used on no London projects (recall – j for projects)

SQL

Algebra (5 pts. extra credit)

3. (15 pts.) Using both SQL and the (full) relational algebra, find the names of parts supplied by all London suppliers (i.e., parts for which the following is true: if S is a London supplier, then S supplies that part. A forall question)

SQL

Algebra

4. (15 pts.) Find supplier numbers, part numbers and total (sum) quantity of that part supplied by that supplier, but only for suppliers and parts supplied in excess of 500 units. The answer to the query should be:

SNO PNO SUM(QTY)

--- --- ----------

S1 P1 900

S2 P3 3100

S4 P6 600

S5 P1 1000

S5 P3 1200

S5 P4 800

S5 P5 1000

S5 P6 700

D. (10 pts.) What is the role of the data dictionary? What sorts of things are contained in it? What form does it take?