Download Database Systems -Introduction to Database Systems - Exams and more Exams Introduction to Database Management Systems in PDF only on Docsity!
UNIVERSITY OF CALIFORNIA
College of Engineering Department of EECS, Computer Science Division CS186 Eben Haber Fall 2003 Midterm Midterm Exam: Introduction to Database Systems This exam has seven problems, worth different amounts of points each. Each problem is made up of multiple questions. You should read through the exam quickly and plan your time-management accordingly. Before beginning to answer a question, be sure to read it carefully and to answer all parts of every question!
Good luck!
I. Query Languages (30 points). Given the following database tables, choose all queries in SQL, Relational Algebra or Tuple Relational Calculus that return the proper answer to the corresponding question. Note that each question may have more than one correct answers. Circle all that apply.
Primary keys are underlined. Note that the driver involved in a car accident may not always be the owner of the car. Assume that accident_date is of type integer, and represents a year (e.g. 1980). Year is also of type integer. We assume that a car cannot get involved in more than one accident at a certain date.
Person(SSN, name, address) Car(license, year, model) Accident(license, accident_date, driver, damage_amount) Owns(SSN, license)
- (4 points) Find the SSN of every person who owns one or more cars, none of which has ever been involved in a car accident. A. SELECT O.SSN FROM Owns O WHERE O.license NOT IN (SELECT A.license FROM Accident A)
D. None of the above
- (4 points) Find the SSN of every person, who owns a TOYOTA or a DODGE A. SELECT O.SSN FROM Owns O, Car C WHERE O.license=C.license AND (C.model=’TOYOTA’ OR C.model=’DODGE’)
D. None of the above
D. None of the above
- (4 points) Find the accident with the smallest cost and return the corresponding owner of the car, his/her address and the amount of damage. A. SELECT O.SSN, P.address, MIN(A.damage_amount) FROM Accident A, Owns O, Person P WHERE O.license=A.license AND O.SSN=P.SSN GROUP BY O.SSN, P.address
B. SELECT O.SSN, P.address, (SELECT MIN(damage_amount) FROM Accident) FROM Owns O, Person P, Accident A WHERE O.SSN=P.SSN AND A.license=O.license
C. SELECT O.SSN, P.address, A.damage_amount FROM Owns O, Person P, Accident A WHERE O.SSN=P.SSN AND A.damage_amount=(SELECT MIN(damage_amount) FROM Accident) D. None of the above.
- (4 points) Find the license number of all cars that have been involved in more than one accident. (DO NOT RETURN DUPLICATES) A. SELECT A1.license FROM Accident A1, Accident A WHERE A1.license=A2.license AND A1.accident_date<>a2.accident_date
B. SELECT DISTINCT A1.license FROM Accident A WHERE A1.license IN (SELECT A2.license FROM Accident A WHERE A1.accident_date<>A2.accident_date) C. SELECT license FROM Accident GROUP BY license HAVING COUNT(accident_date)> D. None of the above
- (2 points) Explain, in English, what the following Relational Calculus query expresses:
II. External Sorting (10 points)
- (6 points) Suppose we wish to sort the following values: 84, 22, 19, 11, 60, 68, 31, 29, 58, 23, 45, 93, 48, 31, 7 Assume that:
- you have three pages of memory for sorting
- you will use an external sorting algorithm with a 2-way merge
- a page only holds two values For each sorting pass, show the contents of all temporary files.
- (2 points) If you have 100 pages of data, and 10 pages of memory, what is the minimum number of passes required to sort the data.
- (2 points) If you have 500,000 pages of data, what is the minimum number of pages of memory required to sort the data in 3 passes?
IV Query Optimization
Consider the following schema
Sailors(sid, sname, rating, age) Reserves(sid, did, day) Boats(bid, bname, size)
Reserves.sid is a foreign key to Sailors and Reserves.bid is a foreign key to Boats.bid.
We are given the following information about the database: Reserves contains 10,000 records with 40 records per page. Sailors contains 1000 records with 20 records per page. Boats contains 100 records with 10 records per page. There are 50 values for Reserves.bid. There are 10 values for Sailors.rating (1...10) There are 10 values for Boat.size There are 500 values for Reserves.day
Consider the following query
SELECT S.sid, S.sname, B.bname FROM Sailors S, Reserves R, Boats B WHERE S.sid = R.sid AND R.bid = B.bid AND B.size > 5 AND R.day = 'July 4, 2003';
(a) Assuming uniform distribution of values and column independence, estimate the number of tuples returned by this query.
Consider the following query
SELECT S.sid, S.sname, B.bname FROM Sailors S, Reserves R, Boats B WHERE S.sid = R.sid AND R.bid = B.bid (b) Draw all possible left-deep query plans for this query:
(c) For the first join in each query plan (the one at the bottom of the tree, what join algorithm would work best? Assume that you have 50 pages of memory. There are no indexes, so indexed nested loops is not an option.
VI. Functional Dependencies and Normalization (20 points total) Consider the attributes A B C D E F G which have the following functional dependencies: AD à F AE à G DF à BC E à C G à E
- List all candidate keys (not superkeys): (5 points)
- Which of the following dependencies are implied by those above: (5 points) a. (IS) (IS NOT) G à C b. (IS) (IS NOT) A à G c. (IS) (IS NOT) ADF à E d. (IS) (IS NOT) ADC à B e. (IS) (IS NOT) AGF à E
- Consider the decomposition into 4 relations: (ADF) (CE) (EG) (ABDG). This decomposition: (5 points) a. (IS) (IS NOT) in BCNF b. (IS) (IS NOT) in 3NF c. (IS) (IS NOT) in 1NF d. (IS) (IS NOT) Dependency Preserving e. (IS) (IS NOT) Lossless
- Consider the decomposition into 3 relations: (ADF) (EC) (ABDEG). This decomposition: (5 points) a. (IS) (IS NOT) in BCNF b. (IS) (IS NOT) in 3NF c. (IS) (IS NOT) 1NF d. (IS) (IS NOT) Dependency Preserving e. (IS) (IS NOT) Lossless
VII. Entity-Relational Model (20 Points) a) (10 points) Draw an E-R diagram for the following situation:
- This is a simplified model for reserving baseball tickets.
- There are teams, which are identified by the team name. Teams are also located in a city.
- Teams play each other in games, which occur on a particular date at a particular time. Games are identified by a game ID, and each game has exactly two teams that play in it.
- A game is played in exactly one stadium.
- A stadium is identified by its name, and is also located in a city.
- Stadiums have seats, which have a section number, a row number, and a seat number.
- Ticket holders reserve seats for a game. Ticket holders are identified by their name.
- Some ticket holders are students (students get discounts, but we are not including that in the model).
b) (10 points) Using SQL, convert the following ER diagram to the relational model. Hint: You do not need CHECK constraints.
