Study Guides (390,000)
CA (150,000)
UW (7,000)
CS (400)
CS348 (10)
Midterm

Midterm Solution of Fall 2009 Fall 2009 Solution only


Department
Computer Science
Course Code
CS348
Professor
Ihab Ilyas
Study Guide
Midterm

This preview shows half of the first page. to view the full 2 pages of the document.
Question 2 Part A
Relational Algebra: Take the cross product of the pickup table with itself
and ensure two different reservations exist from the same customer. Solu-
tions that assumed that a dropoff entry was required were given full credit
as well.
ρ(P1, P ickup)
ρ(P2,(cnum :cnum2, rnum :rnum2), P ickup)
ρ(T woremtals, Πcnum(σcnum=cnum2rnum6=rnum2(P1×P2)))
Answer: Πcnum,cname(C ustomer ./ T woremtals)
SQL: Solutions can use the technique similar to the relational alegbra ex-
pression or use the aggregate functions provided by SQL:
SELECT cnum, cname
FROM Customer INNER JOIN Pickup ON Pickup.cnum = Customer.cnum
GROUP BY cnum, cname
HAVING COUNT(*) >= 2
Question 2 Part B
Relational Algebra: Currently renting implies that there should be an entry
for the customer in the pickup table but not in the dropoff table:
ρ(CurrentRentals, Πrnum(P ickup)Dropof f )
ρ(W aterlooC ustomers, Πcnum,cname(σcity=W aterloo(C ustomer))
ρ(T oyotas2009,Πlicense(σmake=T oyota year=2009 (Car))
Answer: Πcnum,cname(P ickup ./ C urrentRentals ./ W aterlooC ustomers ./
T oyotas2009)
SQL: Use NOT EXISTS / EXCEPT or similar clauses to get eliminate pick-
ups that do not exist in the dropoff table:
SELECT cnum, cname
FROM Customer
INNER JOIN Pickup ON Pickup.cnum = Customer.cnum
INNER JOIN Car ON Pickup.license = Car.license
WHERE make = ’Toyota’
AND year = 2009
AND city = ’Waterloo’
AND Pickup.rnum NOT IN (SELECT rnum from Dropoff )
1
You're Reading a Preview

Unlock to view full version