Download Foreign Key - Introduction to Database Systems - Solved Exams and more Exams Introduction to Database Management Systems in PDF only on Docsity!
University of California, Berkeley College of Engineering Computer Science Division – EECS Fall 2000 Prof. Michael J. Franklin Midterm Exam - SOLUTIONS October 18, 2000 CS 186 Introduction to Database Systems
NAME : ____J. Bond_____________________ STUDENT ID:____007______________
Circle the last two letters of your class account: cs186 a b c d e f g h i j k l m n o p q r s t u v w x y z a b c d e f g h i j k l m n o p q r s t u v w x y z
DISCUSSION SECTION DAY & TIME: Saturday 10pm TA NAME: E.F. Codd General Information: This is a closed book examination – but you are allowed one 8.5” x 11” sheet of notes (double sided). You have 1 hour and 30 minutes to answer as many questions as possible. Partial credit will be given. There are 100 points in all. You should read all of the questions before starting the exam, as some of the questions are substantially more time-consuming than others. Write all of your answers directly on this paper. Be sure to clearly indicate your final answer for each question. Also, be sure to state any assumptions that you are making in your answers. Please try to be as concise as possible. GOOD LUCK!!! Problem Possible Score
**1. Data Models (3 parts) 20 20
- Formal Relational Languages (3 parts) 15 15
- SQL (4 parts) 25 25
- Disks, Pages, Buffer Mgmt (3 parts) 15 15
- Indexes and File Organization (4 parts) 25 25** TOTAL 100 100
Question 1 [3 parts, 20 points total]: Data Models a) (10 points) Draw a (simple) E-R diagram that results in a primary key/foreign key constraint to be created between tables. Show the SQL statements that create the tables including the foreign key and primary key indications.
b) (5 points) For the relational tables you generated in question 1(a), Describe which insert and delete operations in this database must be checked to ensure that referential integrity is not violated for that foreign key. Please state specifically which operations on which relations can cause problems. Many to Many: On insert(Order) -> exists(Customers) and exist(Products); On delete(Customers) -> delete(Orders) or not allowed; On delete(Products) -> delete(Orders) or not allowed; One to Many: On insert(Customers) -> exists(Salespersons); On delete(Salespersons) -> delete(Customers) or not allowed if the foreign key can not be null. Otherwise set_default(Customers). Weak entity: On insert(Order) -> exists(Customers); On delete(Customers) -> delete(Orders) or not allowed;
Customers (^) Orders Products
cid pid
CREATE TABLE Customers ( cid CHAR(10), primary key (cid)) CREATE TABLE Products (pid CHAR(10), CREATE TABLE Orders (primary key (pid)) cid CHAR(10),pid CHAR(10), PRIMARY KEY (cid, pid),FOREIGN KEY (cid) REFERENCES Customers, FOREIGN KEY (pid) REFERENCES Products)
Many to Many
Customers (^) Services Salesperson
cid One to Many pid
CREATE TABLE Salespersons (sid CHAR(10), CREATE TABLE Customers(primary key (sid)) cid CHAR(10),sid CHAR(10), PRIMARY KEY (cid),FOREIGN KEY (sid) REFERENCES Salespersons)
Customers Custom -Order Orders
cid Weak Entity pid
CREATE TABLE Customers (cid CHAR(10), CREATE TABLE Orders (primary key (cid)) cid CHAR(10),pid CHAR(10), PRIMARY KEY (cid, pid),FOREIGN KEY (cid) REFERENCES Customers)
Question 2 [3 parts, 15 points total]: Pure Relational Languages Consider the following schema for an airline database (primary key attributes are in bold ): FLIGHTS ( flight_num , source_city, destination_city) DEPARTURES ( flight_num, date, plane_type) PASSENGERS ( passenger_id , passenger_name, passenger_address) BOOKINGS( passenger_id , flight_num, date , seat_number) Express the following queries in one of (your choice): relational algebra or relational calculus. Feel free to use different languages for different querires and to abbreviate relation and attribute names: a) (5 points) Find the cities that have direct (non-stop) flights to both Honolulu and Newark. πsource_city(σdestination_city = “Honolulu”(FLIGHTS))∩ πsource_city(σdestination_city = “Newark”(FLIGHTS))
{P|∃ F∈ FLIGHTS, ∃ G ∈ FLIGHT ( P.source_city = F.source_city ∧ P.source_city = G.source_city ∧ F.destination_city = “Honolulu” ∧ G.destination_city = “Newark”)}
b) (5 points) Find the passenger_name of all passengers who have a seat booked on at least one plane of every type. π (^) passenger_name, plane_type(PASSENGERS ∞ BOOKINGS ∞ DEPARTURES) / πplane_type(DEPARTURES)
{P|∃ P1∈PASSENGERS (∀D1∈DEPARTURES (∃ B∈BOOKINGS, ∃D2∈DEPARTURES (
P.passenger_name = P1.passenger_name ∧ P1.passenger_id = B.passenger_id ∧ B.flight_num = D2.flight_num ∧B.date = D2.date ∧D1.plane_type = D2.plane_type)))}
c) (5 points) Find the flight_num and date of all flights for which there are no reservations. πflight_num, date(DEPARTURES) - πflight_num, date(BOOKINGS)
{P|∃ D ∈ DEPARTURES (P.flight_num = D.flight_num ∧P.date = D.date ∧∀B∈ BOOKINGS (D.flight_num <> B.flight_num ∨ D.date <> B.date))}
Question 3 [4 parts, 25 points total]: SQL Consider the relational schema of question 2. Express the following queries in SQL (feel free to abbreviate relation and attribute names and to use INTERSECT and EXCEPT if you need to): a) (5 points) Find the cities that have direct (non-stop) flights to both Honolulu and Newark
SELECT DISTINCT source_city FROM Flights F WHERE F.dest_city = “Honolulu” AND F.source_city IN (SELECT source_city FROM Flights F WHERE dest_city = “Newark”)
Could also be done with a self join on Flights, or with INTERSECT, or…, just can’t use a simple selction with “AND” in the Where clause --- this would return no tuples
b) (5 points) Find the passenger_id of all passengers who have a seat booked on a plane of type ``747'' from San Francisco to Washington. Do not return any duplicate values.
SELECT DISTINCT B.passenger_id FROM Flights F, Departures D, Bookings B WHERE B.flight_num = D.flight_num AND B.date = D.date AND F.flight_num = D.flight_num AND F.source_city = “San Francisco” AND F.destination_city = “Washington” AND D.plane_type = “747”
Since key of Departures is flight_num and date, you need both of these to do the join and find out what type of plane the passenger is booked on.
d) (8 points) Print an ordered list of all source cities and the number of distinct destination cities that they have direct (non-stop) flights to. The list should be ordered in decreasing number of destinations and should contain only those source cities that have flights to 25 or more distinct destinations. For example, the output should look like: Source_City NumDestinations Chicago 120 Atlanta 106 Boston 97 ... ... Austin 25
SELECT source_city, COUNT(DISTINCT destination_city) AS NumDestinations FROM Flights F GROUP BY source_city HAVING NumDestinations >= 25 ORDER BY NumDestinations DESC
Question 4 [3 parts, 15 points total]: Disks and Buffer Management a) (3 points) The main components of the cost of performing a disk read are seek time, rotational delay , and transfer time. For each of these three components state whether or not it is reduced by doing sequential reads rather than random reads :
Component Reduced by sequential? Yes or No Seek Time Yes Rotational Delay Yes Transfer Time No
b) (2 points) For the question above, which of the three is likely to result in the largest savings when comparing sequential reads to random reads? (No explanation necessary) Seek Time c) (10 points) Consider a page reference pattern that performs three consecutive scans over a set of five pages. Assume you start with an empty buffer pool of three frames. 1) How many page faults will be incurred with an LRU page replacement policy, and 2) how many will be incurred with an MRU page replacement policy?
- 15 page faults
Frames v
Read Page >>
Pagefault? Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y
- 9 Page faults
Frames v
Read Page >>
Pagefault? Y Y Y Y Y N N Y Y N N Y Y N N
d) (10 points) Create a B+tree where each node can hold at most 3 pointers and 2 keys when the following keys are inserted in the following order: 1, 10, 2, 11, 3, 4, 8, 5, 7 Tree 1:
Tree 2:
Tree 3:
Tree 4 (different search algorithm):
3 5
2 4 7 10
1 2 3 4 5 7 8 10 11
8
3 5
1 2 3 4 5 7
10
8 10 11
3 8
2
1 2
10
8 10 11
4 5
3 4 5 7
4 8
2
1 2 3 4
7
5 7 8
10
10 11
