=========================preview======================
(Comp231)[2009](f)midterm~cs_cwmaa^sol_10183.pdf
Back to COMP231 Login to download
======================================================
COMP231 Database Management Systems (Fall Semester 2009)
Midterm Examination (Answer Sheet)
Date: 8 Oct, 2009 (Thu)
Time: 15:05-16:15
Duration: 1 hour 10 minutes

Student ID:__________________ Student Name:__________________ _______________
Seat No. :__________________
Instructions:
(1)
Please answer all questions in Part A in this paper.

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

(3)
The total marks in Part A are 100.

(4)
The total marks in Part B are 10.

(5)
The total marks you can obtain in this exam are 100 only. If you answer the bonus question in Part B correctly, you can obtain additional marks. But, if the total marks you obtain from both Part A and Part B are over 100, your marks will be truncated to 100 only.

(6)
You can use a calculator.


Answer Sheet

Question Full Mark Mark
Q1 20
Q2 20
Q3 20
Q4 20
Q5 20
Total (Part A) 100
Q6 (OPTIONAL) 10
Total (Part A and Part B) 100

Part A (Compulsory Questions)
Q1 (20 Marks)
Project (pid, pname, dept_no)
Works_on (emp_id, pid, hours)
Employee (emp_id, ename, address, salary)
Department (dept_no, dname, emp_id)

Q2 (20 Marks)
(a)

(b)


a b c d e
a1 b1 c4 d1 e1
a1 b1 c4 d1 e2
a3 b2 c2 d1 e4
a4 b2 c1 d1 e4

a b c d e
a1 b1 c4 d1 e1
a1 b1 c4 d1 e2
a2 b1 c3 d2 null
a3 b2 c2 d1 e4
a4 b2 c1 d1 e4


Q2 (Continued)
(c)

(d)

(e)

(f)

(v(b.f, d.g), s)


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



Q3 (20 Marks)
(a)
select S1.sname from Suppliers S1 where not exists (
( select item from orders O where O.cust_name = Raymond
)
except
(

select item from suppliers S2 where S1.sname = S2.sname
) )
Q3 (Continued)
(b)
create view Step1

as select item, count(distinct cust_name) as NumberOfOrder from orders group by item
select item, NumberOfOrder from Step1 where NumberOfOrder = (select max(NumberOfOrder) from Step1)
drop view Step1
Q4 (20 Marks)
(a) BC+ = ABC+ (C . A)
= ABC Thus, BC is a key for R
B+ = B
ABC

C+ = AC+ (C . A)
= AC
ABC

Thus, any subset of BC (i.e., B and C) is not a key for R.
We conclude that BC is a candidate key for R.

Q4 (Continued)
(b)
F = {EB .D, A.BC, E.A, E.B, B.C} We combine the FDs with the same LHS and obtain the following. F = {EB .D, A.BC, E.AB, B.C} We can re-write the above as F as follows F = {E.D, A.BC, E.AB, B.C}
(This is because B is an extraneous attribute in EB.D. In fact, EB.D can be deduced from E.D in F.
E.D(1) (given)
EB . E (2) (by reflexivity)
EB . D (by transitivity of (1) and (2))
)

We can re-write the above