CS180 Database Management Systems - Winter 2003
Homework Assignment 2
Due on 28 Jan 2003
(Late homeworks will not be accepted.)
Instructions: Answer all the questions concisely below. Points will be taken off for unnecessarily long
answers. Please remember to include your name, student-id number, and email address in your homework
submission.
1. Exercise 4.3 of textbook, page 127.
2. Exercise 5.4 of textbook, page 176.
3. Assume the following schema Rating(cid, name, rating) which shows the name of the movie that a
customer watches and the rating that the customer gives for that movie.
For each of the given condition below, find the smallest instance of Rating that will fail the condition.
Also, find the smallest instance of Rating that will satisfy the condition.
• ∀r∈Rating (r.rating < 6→r.cid = ”J ohn”)
• ∃r∈Rating r.rating = 10
4. Are the following SQL queries equivalent? If yes, provide a short proof of why they are equivalent.
If no, provide a counter-example. You may assume that the relational schema is R(A, B) and S(A, B).
SELECT *
FROM R r
WHERE r.A IN (SELECT s.A
FROM S s
WHERE s.B < 10)
SELECT *
FROM R r, S s
WHERE r.A = s.A AND s.B < 10
5. How many possible instances are there for the relational schema R(A:boolean, B:boolean, C:boolean),
S(A:boolean, B:boolean)?
6. Consider the following simple relational schema for an internet bookshop: Authors(aid, name, age,
affiliation), Publications(id, title, year), PublishersPub(pid, id), PubAuthors(id, aid), Sales(id, year,
qty)
You may assume that each publication is published by only one publisher. Moreover, a publication
may be made by one or more authors and each author may make more than one publication.
1
