ŠĻą”±į > ž’ l n ž’’’ k ’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’ģ„Į @ šæ ą! bjbjīFīF "@ , , 6 ’’ ’’ ’’ š š š š Č Č Č Ü B B B 8 ¼B $ ąB T Ü ^ | @C @C " bC bC bC WQ WQ WQ ] ] ] ] ] ] ] $ _ R ća \ ŗ] Č WQ 5Q " WQ WQ WQ ŗ] š š bC bC Ó
Ļ] ÆZ ÆZ ÆZ WQ č š bC Č bC ] ÆZ WQ ] ÆZ ÆZ (] ü Ģ Č t] bC 4C 0`÷?ŅßÅ B ?U Ö @] ] å] 0 ^ H] , ?b Y r ?b t] Ü Ü š š š š ?b Č t] ÆZ WQ WQ WQ ŗ] ŗ] Ü Ü D" ' d Z ( Ü Ü ' CSE 444 Midterm --- Fall 2005
Name___________________________________
Total number of points: 100 points
Time: 50 minutes.
1. [35points] An e-commerce company takes orders daily, and bills its customers once per month. There is one bill per customer per month, including all purchases made by that customer that month. The company stores the data in the following schema:
Customer(cid, name, email)
Bill(cid, year, month, totalAmount)
-- cid is a foreign key to Customer, not null
-- totalAmount is updated once a month and is either NULL or is ( 0
Purchase(pid, cid, year, month, product, unitPrice, quantity, amount)
-- cid is a foreign key to Customer, not null
-- amount is updated once a month and is either NULL or is ( 0
a. [15 points] Write a sequence of SQL statements generating 'October' 2005 bills for all customers. You need to first update the amount field (=unitPrice * quantity) in all Purchase records during that month, then create a new Bill record for each customer that made any purchases that month. The totalAmount field in each Bill record must be the sum of the amount of all Purchase records plus 8% tax.
Your answer should consist of two SQL statements.
[This page is intentionally left blank. Use it to write your answer.]
b. [20 points] Consider the following pairs of SQL queries Q1 and Q2. For each pair, indicate whether the two queries are equivalent or not. For example, if the two queries were:
Q1: select distinct Bill.month
from Bill
where Bill.totalAmount > 200 and Bill.year=2004
Q2: select distinct Bill.month
from Customer, Bill
where Customer.cid = Bill.cid
and Bill.totalAmount > 200 and Bill.year=2004
then you would say 'equivalent', since the two queries return exactly the same answers (since Bill.cid is a foreign key and cannot be null). However, if the queries were:
Q1: select distinct Customer.name
from Customer
where Customer.email = joe@db.com
Q2: select distinct Customer.name
from Customer, Bill
where Bill.cid = Customer.cid and Customer.email = joe@db.com
then you would say 'not equivalent', since customers without any bills will be returned by Q1 but not by Q2.
Your answer only needs to indicate equivalent or not equivalent, without any justification.
i.
Q1: select distinct Customer.name
from Customer x, Bill y
where x.cid = y.cid and y.totalAmount = 150
Q2: select distinct Customer.name
from Customer x, Bill y, Bill z
where x.cid = y.cid and y.totalAmount = 150
and x.cid = z.cid and z.totalAmount < 300
ii.
Q1: select distinct Customer.name
from Customer x, Bill y
where x.cid = y.cid and y.totalAmount > 100
and y.year=2004 and y.month='October'
Q2: select distinct Customer.name
from Customer x, Bill y, Bill z
where x.cid = y.cid and y.totalAmount > 100 and y.year=2004
and x.cid = z.cid and z.month='October'
iii.
Q1: select distinct Customer.cid, Customer.name,
(select sum(Bill.totalAmount)
from Bill
where Customer.cid = Bill.cid) as total
from Customer
Q2: select Customer.cid, Customer.name, sum(Bill.totalAmount) as total
from Customer, Bill
where Customer.cid = Bill.cid
group by Customer.cid, Customer.name
iv.
Q1: select Customer.cid, Customer.name
from Customer, Bill
where Customer.cid = Bill.cid
group by Customer.cid, Customer.name
having sum(Bill.totalAmount) > 0
Q2: select distinct Customer.cid, Customer.name
from Customer, Bill
where Customer.cid = Bill.cid
and Bill.totalAmount > 0
2. [15 points] Draw an E/R diagram describing the following domain:
Employees. Attributes: ssn, name, department
Managers are Empolyees. Attributes: level
Consultants. Attributes: ssn, address
Projects. Attributes: pid (key), name
Every Employee is managed-by (at most one) Manager
Employees work on Projects: an employee may work on an arbitrary number of projects, and an arbitrary number of employees may work for a project.
Every project has exactly one leader, who is either an employee or a consultant
Your answer should consist of an E/R diagram, which includes entity sets, attributes, relationships, ISA relations. Indicate the type of each relationship with appropriate arrows (one-one, one-many, or many-many).
3. [30 points]
a. [20 points] Consider the table R(A, B, C, D, E) satisfying the following functional dependencies:
AB ( C
BD ( A
E ( BD
Decompose the table in BCNF. Your answer should consist of (1) a list of table names and attributes, and (2) an indication of the keys in each table. (like R1(A,B), R2(B,C), R3(B,D,E) -- but note that this is NOT the correct answer.)
b. [10 points] For each of the statements below, indicate whether they are true or false. You do not need to justify your answer. Note that X+ and Y+ denote the closure of the set of attributes X and Y respectively:
i. For any two sets X, Y: (X ( Y)+ = X+ ( Y+
ii. For any two sets X, Y: if X ( Y then X+ ( Y+
iii. For any two sets X, Y: if X ( Y then X+ ( Y+
iv. For any two sets X, Y: if X+ = X and Y+ = Y then (X ( Y)+ = X ( Y
4. [20 points] Consider the following XML document:
1
2
3
4
5
6
For each of the XPath expressions below indicate what they return.
For example, if the expression is:
/a/a/a/a/text()
then you will answer:
2
3
5
6
and if the expression is:
//a[a/text() = 8]/a/text()
you will answer EMPTY, since the value 8 is not in the document.
a. //a/text()
b. //a[a/text()='2']/a/text()
c. /a/a[.//text()='1']//a/text()
d. //a[a/text()=2][a/text()=3]/a/text()
e //a[.//a/text()=3][.//a/text()=4]//a/text()
f. //a[.//a/text()=1]//a[.//a/text()=4]//a[.//a/text()=5]//a/text()
PAGE
PAGE 1
a
a
a
a
a
a
a
a
a
a
a
1
2
3
4
6
5
§ ¬ ¼ Ź Ü ß ō ü
#
U
V
`
i
l
¦
ø
»
Š
Ų
ō
ś
, - / 0 1 3 “ ŗ Ć Ģ Ļ × ą č ] h w { Ø ° D
F
+ / A F { ¢ ¦ üõźüäßüßŲßüßĪßüßüßüßüĒüßŲßüßüßüßüĒüĄüßüßüøüøüßüßüßüßüøüøüßüßüßüßüßüßüßüß héß hõ> 5héß hõ> j ³šhõ> héß hõ> 5>* hõ> 5>* hõ> 5
hõ> CJ hõ> 5CJ OJ QJ
hõ> 5CJ0 hõ> H ! I J K n ~ ” Ė
Y
Z
§
ć
0 1 Ę Ē ü D
ł
ś
ż ū ū ū ū ū ū ż ż ż õ õ õ õ õ õ õ õ õ õ õ õ õ õ õ õ õ õ 7$ 8$ H$ ! ©! ß! žžž ś
ū
5 q r ¶ ą Ļ Š ų D E m Ū Ü M N ® ° µ Ü ł ł ł ł ł ł ł ł ł ł ł ł ł ł ł ł ł ł ł ł ł ł ł ī ł ł ł
ĘąĄ! 7$ 8$ H$ 7$ 8$ H$ ¦ Ā Ē 6 B Ś ą į é % O U V ^ z ~ ó u ° µ ¾ Ä Å Ķ č ģ B H I Q l p
! " * E I i n Õ Ū Ü ä ü % * Ø ® Æ · ’ F J z ® ² Ę Ģ 5 : _ g ¤ Ķ Ń ķ ņ H N ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ü÷ hõ> 5hõ> b 8 9 ` Ä ž ’
9 ] Ė Ģ ó a ł ł ł ł ł ł ł ł ł ł ł ł ł ł ł ł ł ł ł ł ł ł ł ł ł ł ł ł ł 7$ 8$ H$ Õ P ¢ ¼ ½ ) S
Į į < ł ł ł ł ł ł ł ł ł ł ł ī ł ł ł ł ł ł ł ł ł ł ł ł ł ł ł
ĘąĄ! 7$ 8$ H$ 7$ 8$ H$ < i j æ é Y Z ² Ų ž 1 Ć ė ū ` a m y u ł ł ł ł ł ł ł ł ł ļ ļ ļ ļ ļ ļ ļ ä ł ł ł ł ł ł ł ł ł
ĘąĄ! 7$ 8$ H$
&