Computer Science 455
Final Exam
Name
_______________________________
Thursday, May 10
200 pts.
I. Some basics.
1. Give brief but informative definitions of the following (5 pts. each)
Database Management System
Data model (as in a data model for a database management system)
Entity integrity
Functional Dependence
(definitions, continued)
Key
second normal form
third normal form
Referential Integrity
2. (10 pts.) Briefly describe either (your choice) the hierarchical or the network/CODASYL model for database systems.
3. (40 pts.) On this and the following page, describe the relational model for database management systems as if you were explaining it to your boss, an intelligent person who took some computer science but never took databases. Include in your answer an informal discussion of the essential features of the relational model (say briefly what essential means in this context, recalling that it has a special meaning in the theory), what the integrity constraints are, what domains are, and why the model is called relational (you will want to include a definition of a relation, and say how that definition relates to the model).
(additional workspace for problem 3)
II. Design
1. (20 pts.) Describe and contrast top-down and bottom-up design giving a brief description of each and saying what the relative strengths and weaknesses of each approach are.
2. (20 pts.) From the following description, construct an entity-relationship diagram, convert it to a relational schema, and write appropriate CREATE TABLE statements:
A colleague would like to maintain a gradebook database. For each section she teaches, she wants to record the course number (including department ), course name, section, term, and year. For each student she wants to record the student's student number, name, the courses that the student has or is currently taking, and the student's grade (which would be null for a course the student is currently taking). Please recall that a student can take more than one course from a teacher.
3. (20 pts.) A registration form for the
StudentNo
StudentName
Year
Term
AdvisorNo
AdvisorName
CourseNo (*)
CourseName (*)
CourseDescription (*)
An asterisk indicates a repeating field. Briefly outline the steps involved in creating a relational schema from this form, illustrating each step by example using the registration form. Please do not skip steps.
III. Theory and SQL together
1. (10 pts.) We say that SQL is (for the most part) a non-procedural, declarative language. Say what this means, and give a SQL example to show that this is not always the case.
2. (5 pts.) In SQL, write a query to retrieve the names of parts used on no London project.
3. (10 pts.) In the first several editions of Date's book, he had an SP database (the SPJ database without projects (the J table)). Write the SQL statement to create a view called SP consisting of the SPJ table restricted to sno, pno, and qty where qty is the sum of qty in the SPJ table over all projects for the given sno, pno combination.
4. (5 pts.) We say that SQL, the full relational algebra, and the full tuple calculus are equivalent languages. What do we mean by this?
5. (15 pts.) Using SQL, the relational calculus, and the relational algebra, write queries to find the names of parts used on some London project.
SQL
ALGEBRA
CALCULUS
6. (15 pts. - a forall query). Write the SQL code to find the names of parts used on all London projects. When completed, give a one-sentence English language translation (not transliteration) of the SQL query.