Homework 5 Solutions

Database Management Systems (COP 5725)
(Spring 2014)
Instructor: Dr. Markus Schneider
TAs: Soham Das, Yan Qiao, Yi Wang
Homework 5 Solutions
Name:
UFID:
Email Address:
Pledge (Must be signed according to UF Honor Code)
On my honor, I have neither given nor received unauthorized aid in doing this
assignment.
_______________________________________________
Signature
For scoring use only:
Exercise 1
Exercise 2
Exercise 3
Maximum
22
28
25
Exercise 4
Total
25
100
Received
Exercise 1 (Functional Dependency) [22 points]
Consider a relation with schema R(A,B,C,D) and functional dependencies (FD’s):
ABC, CD, DA.
(a) What are all the nontrivial FD’s that follow from the given FD’s? You can restrict to
FD’s with single attributes on the right side. [5 pts]
Solution:
CA, ABD, ACD, BCA, BCD, BDA, BDC, CDA, ABCD,
ABDC, and BCDA.
(b) What are all the keys of R? [5 pts]
Solution: AB, BC, and BD are keys. All other sets either do not have ABCD as the
closure or contain one of these three sets.
(c) What are all the superkeys of R that are not keys? [5 pts]
Solution: The superkeys are all those that contain one of those three keys. That is, a
superkey that is not a key must contain B and more than one of A, C, and D. Thus, the
(proper) superkeys are ABC, ABD, BCD, and ABCD.
(d) Consider schema T(A,B,C,D) with FD’s: BDC, CD, CDA and schema
S(A,B,C,D) with FD’s: BDC, CD, DA. Use Armstrong’s axioms to prove or
disprove if S or T or both are equivalent to R. [7 pts]
Solution: Neither of them is equivalent to R.
First, R implies S. Need to prove ABC implies BDC. Since DA, so BDAB
(augmentation), together with ABC we get BDC (transitivity)
Second, R implies T. Need to prove DA implies CDA. Since DA, we have
CDAC (augmentation), then we get CDA (decomposition)
Third, T can’t imply R. Because CD, CDA can’t derive DA, it only gets CA.
Forth, T can’t imply S. Because BDC, DA can’t derive ABC, it only gets
ABDC.
Exercise 2 (Normal Forms) [25 points]
(a) Consider relation schema R(A,B,C,D,E) with FD’s: ABC, DEC, BD. First
indicate all the BCNF violations. You don’t need to consider violations that has more
than one attributes on the right side. Then decompose the relations into collections of
relations that are in BCNF. [14 pts]
Solution: We can find all the nontrivial FDs: ABC, DEC, BD, ABD, BCD,
BEC, BED, ABCD, ABDC, ABEC, ABED, ADEC, BCED,
BDEC, ABCED, and ABDEC. From the closures we can also deduce that the only
key is ABE. Thus, any dependency above that does not contain ABE on the left is a
BCNF violation. These are: ABC, DEC, BD, ABD, BCD, BEC, BED,
ABCD, ABDC, ADEC, BCED and BDEC.
One choice is to decompose using the violation ABC. Using the above FDs, we get
ABCD and ABE as decomposed relations. ABE is in BCNF because there are no keys
and no nontrivial FDs, but ABCD is not in BCNF since AB is its only key and the FD
BD follows for ABCD. Using violation BD to further decompose, we get BD and
ABC as decomposed relations. BD is in BCNF because it is a two-attribute relation. ABC
is in BCNF since AB is the only key and ABC is the only nontrivial FD. Thus the three
relations of the decomposition are ABC, BD and ABE.
(b) Consider relation schema R(A,B,C,D,E) with FD’s: ABC, CD, DB and DE.
First indicate all the 3NF violations. You don’t need to consider violations that has more
than one attributes on the right side. Then decompose the relations into collections of
relations that are in 3NF. [14 pts]
Solution: There are 41 nontrivial dependencies: CB, CD, CE, DB, DE,
ABC, ABD, ABE, ACB, ACD, ACE, ADB, ADC, ADE, BCD,
BCE, BDE, CDB, CDE, CEB, CED, DEB, ABCD, ABCE,
ABDC, ABDE, ABEC, ABED, ACDB, ACDE, ACEB, ACED,
ADEB, ADEC, BCDE, BCED, CDEB, ABCDE, ABCED, ABDEC
and ACDEB.
The keys are AB, AC and AD. FDs where the left side is not a superkey or the attributes
on the right are not part of some key are 3NF violations. The 3NF violations are CE,
DE, BCE, BDE, CDE and BCDE.
We can decompose into relations using the minimal basis ABC, CD, DB and
DE. The resulting decomposed relations would be ABC, CD, BD and DE. Since
relation ABC contains a key, we can stop with the decomposition. The final set of
decomposed relations is ABC, CD, BD and DE.
Exercise 3 (Constraints and Triggers) [25 points]
(a) How to use assertion and trigger to check integrity constraints? What’s the difference
between assertion and trigger? [5 pts]
Solution: refer to lecture slides.
(b) Consider the same database schema as in exam 2 question 3:
Product (maker, model, type)
Desktop (model, speed, ram, hd, rd, price)
Laptop (model, speed, ram, hd, screen, price)
Printer (model, color, type, price)
Write the following assertions in Oracle SQL.
(1) No manufacturer of desktop may also make laptops. [5 pts]
(2) A manufacturer of a desktop must also make a laptop with at least as great a
processor speed. [5 pts]
Solution:
(1) CREATE ASSERTION CHECK (NOT EXISTS ( (SELECT maker FROM Product NATURAL JOIN Desktop) INTERSECT (SELECT maker FROM Product NATURAL JOIN Laptop) ) ); (2) CREATE ASSERTION CHECK (NOT EXISTS (SELECT maker FROM Product NATURAL JOIN Desktop WHERE speed > ALL (SELECT L2.speed FROM Product P2, Laptop L2 WHERE P2.maker = maker AND P2.model = L2.model ) ) ); Write the following triggers in Oracle SQL.
(3) When updating the price of a desktop, check that there is no lower priced desktop
with the same speed. [5 pts]
(4) When inserting a new printer, check the model number exists in Product. [5 pts]
Solution:
(3)
CREATE TRIGGER LowPriceDesktopTrigger AFTER UPDATE OF price ON Desktop D REFERENCING OLD ROW AS OldRow, OLD TABLE AS OldStuff, NEW ROW AS NewRow, NEW TABLE AS NewStuff FOR EACH ROW WHEN (NewRow.price < ALL (SELECT Desktip.price FROM Desktop WHERE Desktop.speed = NewRow.speed)) BEGIN DELETE FROM Desktop WHERE (model, speed, ram, hd, price) IN NewStuff; INSERT INTO Desktop (SELECT * FROM OldStuff); END; (4) CREATE TRIGGER NewPrinterTrigger AFTER INSERT ON Printer REFERENCING NEW ROW AS NewRow, NEW TABLE AS NewStuff FOR EACH ROW WHEN (NOT EXISTS (SELECT * FROM Product WHERE Product.model = NewRow.model)) DELETE FROM Printer WHERE (model, color, type, price) IN NewStuff; Exercise 4 (PL/SQL) [25 points]
Consider the same relational schemas as in Exercise 3 (b).
(a) Write a PL/SQL function to return and print out the total number of printer models. [8
pts]
Solution:
CREATE OR REPLACE FUNCTION totalPrinters RETURN number IS total number(5) := 0; BEGIN SELECT count(*) into total FROM Product Where type = ‘printer’; dbms_output.put_line('Number of printer models: ' || total); RETURN total; END; / (b) Write a PL/SQL block to set the desktop price to be at least half of the most expensive
one and then print out the average desktop price after update. [8 pts]
Solution:
declare avg_price number; max_price number; begin select max(price) into max_price from desktop; update desktop set price := 0.5 * max_price where price < 0.5 * max_price; commit; select avg(price) into avg_price from desktop; dbms_output.put_line(“Average price is “ || avg_price); exception when others then dbms_output.put_line(“Update error!”); rollback; end; (c) Write a PL/SQL procedure to set the laptops with same speed and ram size to have the
same price (their average price), then print out the new minimum price of all laptops. [9
pts]
Solution:
declare min_price number; CURSOR c1 IS SELECT speed, ram, ave(price) as avp FROM laptop FOR UPDATE of laptop; begin FOR x IN c1 LOOP UPDATE laptop SET price = x.avp WHERE speed = x.speed and ram = x.ram; END LOOP; commit; select min(price) into min_price from laptop; dbms_output.put_line(“Minimum price is “ || min_price); exception when others then dbms_output.put_line(“Update error!”); rollback; end;

Download Report