Download Database System and Application - Exam 1 | CPSC 310 and more Exams Deductive Database Systems in PDF only on Docsity!
CPSC 310: Database Systems / CSPC 603: Database Systems and Applications Exam 1 October 5, 2005
Name:
Instructions:
- This is a closed book exam. Do not use any notes or books, other than your 8.5-by-11 inch review sheet. Do not confer with any other student. Do not use any computer equipment.
- Show your work. Partial credit will be given. Grading will be based on correctness, clarity and neatness.
- I suggest that you read the whole exam before beginning to work any problem. Budget your time wisely—according to the point distribution.
- There are 5 questions worth a total of 100 points, on 7 pages (including this page).
DO NOT BEGIN THE EXAM UNTIL INSTRUCTED TO DO SO. GOOD LUCK!
Please sign the academic integrity statement: “On my honor, as an Aggie, I have neither given nor received unauthorized aid on this academic work. In particular, I certify that I have not received or given any assistance that is contrary to the letter or the spirit of the guidelines for this exam.”
Signature:
References for these problems:
our textbook
Modern Database Management, Seventh Edition, Hoffer, Prescott, and McFadden, Pearson Prentice
Hall, 2005)
Database System Concepts, Fifth Edition , Silbershcatz, Korth, and Sudarshan, McGraw-Hill, 2006)
Gradiance web site
- (16 pts total) Short answers.
(a) (4 pts) Give two design guidelines for E/R diagrams.
(b) (4 pts) Define two kinds of anomalies can occur with poorly-structured relations.
(c) (4 pts) What is the definition of Third Normal Form (3NF)? What is its advantage over Boyce-Codd Normal Form?
(d) (4 pts) In SQL, name one advantage of set semantics over bag semantics and one advantage of bag semantics over set semantics.
(b) (13 pts) Convert the E/R diagram above into a relational schema. Use the algorithm presented in class and combine relations according to those rules. Handle the is-a hierarchy using the NULLs method.
- (24 pts total) Normal Forms.
Consider the relation Lending(branch name, branch city, assets, customer name, loan number,amount) with the following functional dependencies:
branch name! assets branch city
loan number! amount branch name
(a) (4 pts) How many keys does this relation have? List them.
(b) (5 pts) Compute the closure of the set of attributes f loan number g.
(c) (4 pts) Calculate all additional nontrivial FDs that follow from those given above.
(d) (4 pts) Why is the relation Lending not in Boyce-Codd Normal Form?
(e) (7 pts) Convert the relation Lending into Boyce-Codd Normal Formal. Be sure to show your work, i.e., explain the process you are using.
- (21 pts total) SQL.
Consider the following (informal) database schema for our familiar example from class:
Candies(name, manf) : lists candies and their manufacturers
Stores(name, city, addr, phone) : lists the name of stores, their city, (street) address,
and phone number
Consumers(name, city, addr, phone) : lists consumers by their name, city, (street)
address, and phone number
Likes(consumer, candy) : indicates which consumers like which candies
Sells(store, candy, price) : indicates which stores sell which candies at which prices
(assume each store sells each candy at only one price)
Frequents(consumer, store) : tells which consumers shop at which stores
Write queries to find the following information.
(a) (7 pts) Find the names of all candies, and their prices, sold by the store ’HEB’.
(b) (7 pts) Find the name and phone number of every consumer who likes the candy ’Snickers’.
(c) (7 pts) Find all stores frequented by both ’Moe’ and ’Curly’.
