=========================preview======================
(Comp231)[2009](f)midterm~cs_cwmaa^_10182.pdf
Back to COMP231 Login to download
======================================================

COMP231 Database Management Systems (Fall Semester 2009)
Midterm Examination (Question Paper)
Date: 8 Oct, 2009 (Thu)
Time: 15:05-16:15
Duration: 1 hour 10 minutes

Student ID:__________________
Instructions:
(1)
Please answer all questions in Part A in the answer sheet.

(2)
You can optionally answer the bonus question in Part B in the answer sheet. You can obtain additional marks for the bonus question if you answer it correctly.

(3)
You can use a calculator.


Question Paper

1/4



Part A (Compulsory Questions)
Q1 (20 Marks)
Consider a scenario that an employee works for some projects where a project is owned by a department and a department is managed by an employee. The following shows the ER-diagram of this scenario.


From this ER-diagram, we want to construct all relations so as to minimize the total number of relations. Please list all relations. Please underline the primary key of each relation.
2/4



Q2 (20 Marks)
Suppose we are given the following relations. r s t u v
a b c d
a1 b1 c4 d1
a2 b1 c3 d2
a3 b2 c2 d1
a4 b2 c1 d1

b d e
b1 d1 e1
b1 d1 e2
b1 d4 e3
b2 d1 e4



d1 d4
b d
b1 d1
b2 d4
b3 d4
b1 d4
b3 d1
b2 d2
b4 d3
b1 d2


f g e
b1 d1 e1
b1 d1 e2
b1 d4 e3
b2 d1 e4


(a)
What is the result of r


s?
(b)
What is the result of r

s?
(c)
What is the result of s x t?

(d)
What is the result of u/t?

(e)
What is the result of d(b = b2 d = d1 (r))?


(f)
If table s is renamed to table v as shown above, please write the relational algebra for this rename operation.


Q3 (20 Marks)
A company keeps a database about customers who make orders for items supplied by some suppliers. In the following, cust_name is customer name and sname is supplier name.
Customers (cust_name, address, balance) Orders (order_no, cust_name, item, quantity) Suppliers (sname, item, price)
Given the above relation schemas where the primary keys are underlined. Consider querying based on the schemas.
Please write each of the following in SQL.
(a)
Find the sname of suppliers who supply all items ordered by Raymond.

(b)
Find the most popular item(s) (i.e., the item(s) ordered by the greatest number of customers). Show also the number of customers that ordered the most popular item(s). (Note: A customer should not be counted twice for each item. If there are any ties (i.e., there are multiple items with the same greatest number of customers), please list all the items with the same greatest number of customers.)


3/4



Q4 (20 Marks)
(a)
Consider the schema R = (A, B, C) with a set of functional dependencies {AB . C, C . A}.
Formally prove that BC is a candidate key.


(b)
Consider the schema R = (A, B, C, D, E) with a set F of functional dependencies {EB.D, A.BC, E . A, E. B, B.C}. Find the canoni