Download Midterm Examination - Database Systems - 2010 | CS 411 and more Exams Deductive Database Systems in PDF only on Docsity!
>98?+48(,%56%@//8958%'(%>4)'9'A!:'-;'8B9%
C8D(+4-%EF'-89'(859%
G=(5)+4%H2%01$1%
I8-+%J8-8(K%HL%-89<(+*%
M%3489(%,5<4%9'-+%'9D%N+(@&%)+/5O7%@9%'DD8(8592%;489(%,5<4%N+(@&%89%(:+%<;;+4%48B:(%
=549+4%56%+?+4,%;'B+7%
!"#$ K%PPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP%%%%%%%% !$%&' K%PPPPPPPPPPPPPPPPPPPPPPPPPPPPPP%%
M%@9=/<D89B%(:8%=5?+4%;'B+2%(:8%+F'-%)55Q/+(%=59('89%R%;'B+7%!:+=Q%86%,5<%:'?+%
-8*89B%;'B+7%
M%I:+%+F'-%8%=/5+D%)55Q%'9D%=/5+D%95(+7%N5%='/=</'(54*%54%5(:+4%+/+=(4598=%
D+?8=+%'4+%;+4-8((+D7%S9,%654-%56%=:+'(89B%59%(:+%+F'-89'(859%O8//%4+</(%89%'%T+45%
B4'D+7%
M%3/+'+%O48(+%,5<4%5/<(859%89%(:+%;'=+%;45?8D+D%59%(:+%+F'-7%U5<%-',%<+%(:+%
)/'9Q%'4+'%'9D%)'=Q%56%(:+%+F'-%;'B+%654%=4'(=:%O54Q7%3/+'+%D5%95(%<+%'9,%
'DD8(859'/%*=4'(=:%;';+47%
M%3/+'+%-'Q+%,5<4%'9O+4%=/+'4%'9D%<==89=(V%,5<%O8//%/5+%=4+D8(%654%?+4)5+2%
=59?5/<(+D2%54%=596<89B%'9O+47%"8-;/8=8(,%D5+%=5<9(W%
M%X+9+4'//,2%O+%(:89Q%59+%-89<(+%;+4%;589(%8%'%4+'59')/+%'//5='(859%56%(8-+V%*5%;/'9%
,5<4%(8-+%'==54D89B/,7%U5<%*:5</D%/55Q%(:45<B:%(:+%+9(84+%+F'-%)+654+%B+((89B%
('4(+D2%(5%;/'9%,5<4%(4'(+B,7%
345)/+-% $% 0 % Y% #% L% I5('/%
3589(*% $1% $Z% $Z% $H% $Z% HL%
X4'D+4% %
&2$1@AB?$CD$ +"%+,-(.#/"(0#+& .$E5$D&F$5006$)40$)3)090()$'$%32)'366D$)2F0$3(;$
%32)'366D$5360:$-4&&0$1@AB?.$G&F$7'66$80)$"$%&'()$5&2$03-4$-&220-)$3(*702:$!#.H$%&'()$
5&2$03-4$'(-&220-)$3(702:$3(;$#$%&'()$5&2$03-4$3(702$605)$C63(I.$
!"+$@(*702J$$ +',#$$ -).(#$
E5$0()')D$0)$@$3(;$K$43L0$3$93(DM&(0$2063)'&(4'%$!3$'66F*)23)0;$C06&7+:$03-4$
0()')D$'($@$9F*)$C0$2063)0;$)&$0N3-)6D$&(0$0()')D$'($K.$
!O+$@(*702J$$ +',#$$ -).(#$
<40$5&66&7'(8$'$3($0N39%60$&5$FC-63*'(8$'($?P=$;'38239J$
!Q+$@(*702J$$ +',#$$ -).(#$
E($3$8&&;$;0'8(:$3($0()')D$0)$9F)$ )./)0($ C0$9&20$)43($)40$(390$&5$&90)4'(8R$'.0.:$
')$9F)$43L0$3)$603)$&(0$(&(MI0D$3))2'CF)0.$
!S+$@(*702J$$ +',#$$ -).(# $
1&2$)23(63)'(8$3$2063)'&(4'%$ 1 $52&9$3($?P=$;'38239$'()&$3$2063)'&($'($3$2063)'&(36$
-4093:$)40$C3'-$2F60$!7')4$-02)3'($0N-0%)'&(+$'J$ 23%(!',2!$)$'#.)!&3%$/&!"$
!H+$@(*702J$$ +',#$$ -).(#$
BF%%&0$2063)'&($ 1 ;$ 3(;$ 1 < $-F220()6D$43L0$ %;$ 3(;$ %< $)F%60$20%0-)'L06D.$<40($)40'2$
/32)0'3($T2&;F-)$ 1 ;$ N$ 1 < $-3($43L0$3)$9&)$ %;$ N$ %< $)F%60:$CF)$7'66$)D%'-366D$43L0$60*$
)43($ %;$ N$ %< $)F%60*.$
$$@$ $$K$
$/&F20$ $$>('L02')D$
Problem 2 (16 points) Database Design
It is possible that two entity sets are connected to each other by more than one relationship. Keep this kind of possibility in mind as you answer this question.
You have been asked to design a database about a university:
- Professors have an SSN, a name, and a rank (e.g., Associate Professor)
- Projects are identified by a project number. Each project also has a starting date and an ending date
- Each project is always managed by one professor
- Each project is worked on by one or more professors
- Professors can work on multiple projects. Professors also can manage multiple projects.
- Departments have a unique department number, a department name, and a main office
- Departments can have a professor (department chair) who runs the department. A depart- ment can have no more than one department chair, but may temporarily have no chair.
- Professors can work in one or more departments, but a professor can’t be chair of more than one department.
(a) Draw an ER diagram for this application. Be sure to mark the multiplicity of each relationship of the diagram. Decide the key attributes and identify them on the diagram. Please state all assumptions you make in your answers. (8 points)
(b) Translate your ER diagram into a relational schema, following the methodology discussed in class. Merge relations where appropriate. Specify the key of each relation in your schema. ( 8 points)
Problem 4 (17 points) Relational Algebra
The following database schema consists of three relations, whose schemas are:
Product(model, maker, type), PC(model, speed, ram, hd, pc − price), Printer(model, color, choice, printer − price),
The Product relations gives the manufacturer, model number and type (PC or printer) of various products. We assume for convenience that model numbers are unique over all manufacturers and product types. The PC relation gives for each model number that is a PC the speed (of the processor, in gigahertz), the amount of RAM (in megabytes), the size of the hard disk (in gigabytes), and the price. The Printer relation records for each printer model whether the printer produces color output (true for color, false for black-white), the process choice (laser or ink-jet, typically), and the price.
A sample instance of this schema is shown below:
Product Relation model maker type 1001 HP pc 1002 HP pc 1003 Dell pc 1004 Dell pc 1005 Dell pc 1006 Acer pc 1007 Sony pc 1008 Sony pc 1009 Fujitsu pc 2001 Dell printer 2002 Dell printer 2003 Canon printer 2004 Canon printer 2005 HP printer 2006 Sony printer
PC Relation model speed ram hd pc-price 1001 2.66 1024 250 2114 1002 2.10 512 250 995 1003 1.42 512 80 478 1004 2.80 1024 250 649 1005 3.20 512 250 630 1006 3.20 1024 320 1049 1007 2.20 1024 200 510 1008 2.20 2048 250 770 1009 2.00 1024 250 650
Printer Relation model color choice printer-price 2001 true ink-jet 99 2002 false laser 239 2003 true laser 899 2004 true ink-jet 120 2005 false laser 120 2006 true ink-jet 100
Answer the following queries using expressions of relational algebra. Your queries should work for any instance of the database, not just this one.
(a) List all the makers who make PC with a hard disk of at least 200GB. (4 points)
(b) List all the makers who sell only one type of products (either PC or printer, not both). ( 7 points)
(c) List all the makers who sell at least two different models of PC (6 points)
>98?+48(,%56%@//8958%'(%>4)'9'A!:'-;'8B9%
C8D(+4-%EF'-89'(859%
G=(5)+4%H2%01$1%
I8-+%J8-8(K%HL%-89<(+*%
M%3489(%,5<4%9'-+%'9D%N+(@&%)+/5O7%@9%'DD8(8592%;489(%,5<4%N+(@&%89%(:+%<;;+4%48B:(%
=549+4%56%+?+4,%;'B+7%
!"#$ K%PPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP%%%%%%%% !$%&' K%PPPPPPPPPPPPPPPPPPPPPPPPPPPPPP%%
M%@9=/<D89B%(:8%=5?+4%;'B+2%(:8%+F'-%)55Q/+(%=59('89%R%;'B+7%!:+=Q%86%,5<%:'?+%
-8*89B%;'B+7%
M%I:+%+F'-%8%=/5+D%)55Q%'9D%=/5+D%95(+7%N5%='/=</'(54*%54%5(:+4%+/+=(4598=%
D+?8=+%'4+%;+4-8((+D7%S9,%654-%56%=:+'(89B%59%(:+%+F'-89'(859%O8//%4+</(%89%'%T+45%
B4'D+7%
M%3/+'+%O48(+%,5<4%5/<(859%89%(:+%;'=+%;45?8D+D%59%(:+%+F'-7%U5<%-',%<+%(:+%
)/'9Q%'4+'%'9D%)'=Q%56%(:+%+F'-%;'B+%654%=4'(=:%O54Q7%3/+'+%D5%95(%<+%'9,%
'DD8(859'/%*=4'(=:%;';+47%
M%3/+'+%-'Q+%,5<4%'9O+4%=/+'4%'9D%<==89=(V%,5<%O8//%/5+%=4+D8(%654%?+4)5+2%
=59?5/<(+D2%54%=596<89B%'9O+47%"8-;/8=8(,%D5+%=5<9(W%
M%X+9+4'//,2%O+%(:89Q%59+%-89<(+%;+4%;589(%8%'%4+'59')/+%'//5='(859%56%(8-+V%*5%;/'9%
,5<4%(8-+%'==54D89B/,7%U5<%*:5</D%/55Q%(:45<B:%(:+%+9(84+%+F'-%)+654+%B+((89B%
('4(+D2%(5%;/'9%,5<4%(4'(+B,7%
345)/+-% $% 0 % Y% #% L% I5('/%
3589(*% $1% $Z% $Z% $H% $Z% HL%
X4'D+4% %
*A+ ?"<&/((
B+ ,-%.&/(4%56&(3B8(4;C&'-(6&.5D2(%&;1<"&/+(
E+ ?"<&/(4%56&(@E8(4;C&'-(6&.5D2(%&;1<"&/+(
*)F+ ,-%.&/(
Solution:
(b) Translate your ER diagram into a relational schema, following the methodology discussed in class. Merge relations where appropriate. Specify the key of each relation in your schema. ( 8 points)
Solution: If we do not merge relations at all, we get: Professor(SSN, name, rank) Project(pid, s date, e date) Department(DeptNO, name, office) WorkOn(SSN, pid) Manages(SSN, pid) WorksIn(SSN, DeptNO) Runs(SSN, DeptNO)
But, we could combine the Project relation with the Manages relation, and the Department rela- tionwith the Runs relation: Professor(SSN, name, rank) Project(pid, SSN, s date, e date) Department(DeptNO, SSN, name, office) WorkOn(SSN, pid) WorksIn(SSN, DeptNO)
Problem 3 (16 points) Relational Schema Design
Consider a relation R(A,B,C,D,E), with FDs AB → C, C → A, C → BD, D → E
(a) List all the keys of R. Do not list superkeys which are not (minimal) keys. (6 points)
Solution: AB, C
(b) Is this relation in BCNF? If you answer is yes, explain why it is. If you answer is no, decompose the relation into BCNF, showing your decomposition steps. (10 points)
Solution: No. The last FD, D → E, violates BCNF. We decompose into R1(DE) and R2(ABCD). R1 is in BCNF because the only relevant FD, D → E, does not violate BCNF (and because its a two-attribute relation). R2 has the relevant FDs AB → C, C → A, C → B, C → D, none of which violate BCNF. Therefore we are done.
NetID:
Problem 5 (16 points) SQL
(a) select model, maker, speed from Product, PC where Product.model = PC.model and pc.price > 800. (4 points)
Solution: model maker speed 1001 HP 2. 1002 HP 2. 1006 Acer 3.
(b) List all the makers who make either laser printers or 1024M-RAM PCs(can be both). ( 6 points) Solution: (select maker from Product, PC where Product.model = PC.model and ram = 1024 ) union (select maker from Product, Printer where Product.model = Printer.model and choice = ’laser”)
(c) List all the model numbers of printers whose price are higher than any existing PC. (6 points) select model from Printer where printer-price > ALL (select pc-price from PC)
