DB Review Session

1. DB Review Session

2. salary ER Diagrams 1 title birthday id Movie Actor year Acted In name type address Where does the salary attribute belong?

3. role ER Diagrams 2 • The entities in a relationship set identify the relationship id Actor Movie Acted In title name

4. Role title ER Diagrams 2 • The entities in a relationship set identify the relationship • How would we store information about a person who acted in one movie in several roles? id Actor Movie Acted In title name

5. ER Diagrams 3 Where would you put the arrow? age father id Person FatherOf child name

6. ER Diagrams 3 age father id Person FatherOf child name

7. ER Diagrams 4 Actor id name id Director Movie produced title name What does this mean? A movie has at most one actor and one director

8. ER Diagrams 5 • What does this diagram mean? id Director Movie Directed title name A movie has exactly one director. A director directs at least one movie.

9. author title Book isbn ER Diagrams 6 name Library Owned By id Copy Of Person Borrowed Copy copy number condition

10. pno P2 sno Division 1  =

11. pno P2 sno Division 1 S1 S2 S3 S4  =

12. sno Division 2 pno  = P2 P4

13. sno Division 2 pno  = P2 P4 S1 S4

14. sno Division 3 pno  = P1 P2 P4

15. sno Division 3 pno  = P1 P2 P4 S1

16. Tuples: (id of a reserved red boat, # of reservations of the red boat) SQL Aggregation 1 What does this query return? SELECT B.bid, COUNT(*) FROM Boats B, Reserves R WHERE R.bid=B.bid and B.color=‘red’ GROUP BY B.bid

17. SQL Aggregation 2 What if we put the condition into the HAVING clause? SELECT B.bid, COUNT(*) FROM Boats B, Reserves R WHERE R.bid=B.bid GROUP BY B.bid, B.color HAVING B.color=‘red’ We have also to put the color in the grouping list!

18. What is wrong with this? How would you fix it? Aggregation 3 The Color for which there are the most boats SELECT color FROM Boats B GROUP BY color HAVING max(count(bid))

19. SQL Aggregation 3 The Color for which there are the most boats SELECT color FROM Boats B GROUP BY color HAVING count(bid) >= ALL (SELECT count(bid) FROM Boats GROUP BY Color)

20. Attribute Closure 1 Compute Closure(X, F) C:= X while there is a V W in F such that (V ÍC)and (W ËC) do C:= C W return C

21. Attribute Closure 2 • R=ABCDE • F={ABC, CEB, DA, BCE} • {A}+ = • {A,B}+ = • {B,D}+ = {A} {A,B,C,E} {A,B,C,D,E}

22. Dependency Preservation 1a IsDependencyPreserving(F,R1…k) for each X->Y in F do if not IsPreserved(X,Y,R1…k) return false return true

23. Dependency Preservation 1b /* check if X->Y is preserved */ IsPreserved(X,Y,R1…k) Z:=X while changes to Z occur do for i=1 to k do Z:= Z  ((Z  Ri)+  Ri) if YZ return true else return false

24. Dependency Preservation 2 • Is the following decomposition dependency preserving? • R = ABCDE • F = {A -> ABCDE, BC -> A, DE -> C} • R1 = ABDE, R2 = DEC No! BC->A is not preserved

25. Normal Forms • R = ABC. For each F below, decide whether R is in BCNF/3NF: • F = {} • F = {A -> B} • F = {A -> B, A -> C} • F = {A -> B, B -> C} • F = {A -> B, BC -> A}