Midterm Review

1. Midterm Review October 20

2. Announcements • You should look on Piazza for your room assignments! • There is no class on Wednesday • It is being used for makeup exams. • GOOD LUCK!

3. Exam Details • 90 Minutes. 7 to 8:30pm • Closed Book, Closed Notes. • Some notes provided. (They are posted.) • Last name: • A through M (Nvidia) • N through Z (Bishop Auditorium in Lathrop) • Alternate: Email Firas! (fabuzaid@stanford.edu)

4. High-level Guide • Lecture & Assignments far game. • If we spent time on it, it will likely appear. • I have explicitly said you are not tested on some topics that are in your homework. • If I think the best way to drill something is by doing, it won’t be there. • If I think I can test a concept not details, it goes on the test.

5. Fair Game: Query Languages • SQL • SFW queries • Aggregation queries • Equivalence (activity) • Relational Algebra(RA) • Set algebra • How to read/write RA queries • Translate between the two! • When possible! • Equivalent queries • We spent time on some equivalences Used class time and homework time. There will be questions on it.

6. Constraints • We studied FDs (for a while) • Keys, superkeys, • Inference with FDs • Multivalued Dependencies • The connection between constraints and normal forms. • Constraints remove redundancy • 4NF not on the exam.

7. This is basically it!

8. Q&A

9. Where do Constraints come from? A  B

10. Constraints A database designer decides on whether or not to include constraints by modeling a given domain.

11. MVPs (Most Valuable Players) MVP (game-number, team, player) • If every game there is at most one MVP, how would we write that? • Does every game have an MVP? • If each game, each team has at most one MVP… we write?

12. MVD Reprise

13. Formal Definition of MVD Course ↠ Staff t1 t3 t2 • We write A↠ B if for any tuples t1,t2 s,t. • t1[A] = t2[A] then there is a tuple t3s.t. • t3[A] = t1[A] • t3[B] = t1[B]and t3[C] = t2[C] • Where C are the attributes of R not in A Union B.

14. Does Course ↠ Staff hold now?

15. Example Drinkers(name, areaCode, phone, beersLiked, manf) • A drinker can have several phones, with the number divided between areaCode and phone (last 7 digits). • A drinker can like several beers, each with its own manufacturer. Taken from Jeff Ullman’s MVD slides. Use the Google.

16. Example, Continued • Since the areaCode-phone combinations for a drinker are independent of the beersLiked-manf combinations, we expect that the following MVD’s hold: name ↠ areaCode,phone name ↠ beersLiked, manf

17. Example Data But we cannot swap area codes or phones by themselves. That is, neither name↠ areaCodenor name s↠ phone holds for this relation.

18. Equivalent RA 1/2 = 4/8

19. Definition We say two RA expressions are equivalent if they return the same result on every database. Challenge: Different as expressions but still equivalent. (think: 1/2 = 4/8)

20. Logical Equivalence of Plans R(A,B) S(B,C) s[A=5] (p[A] R) = p[A] (s[A=5] R) Here, projection and selection commute p[B] (s[A=5] R)? Can we play the same game here?

21. Review: Equivalences s[A=5] (R(A,B) JOIN S(B,C) = (s[A=5] R(A,B)) JOIN S(B,C)

22. A simple plan p[B] R(A,B) S(B,C) What SQL query does this correspond to?

23. Pushing down projection p[B] p[B] p[B] R(A,B) S(B,C) R(A,B) S(B,C) Why might we prefer this plan?

24. Where to use WHERE and where to use HAVING

25. Differences between Where and HAVING • WHERE is a condition on a single tuple • “Rating > 10” where rating is an attribute of some table • HAVING is a condition that interacts with an aggregation • “COUNT(*) > 10” or “AVG(rating) < 5”

26. HAVING Clause What does this query ask for? SELECT product, Sum(price * quantity) FROM Purchase WHERE date > ‘10/1/2005’ GROUPBY product HAVING Sum(quantity) > 100 HAVING clause contains conditions on aggregates.

27. General form of Grouping and Aggregation SELECT S FROM R1,…,Rn WHERE C1 GROUP BY a1,…,ak HAVING C2 S = may contain attributes a1,…,ak and/or any aggregates but NO OTHER ATTRIBUTES C1 = is any condition on the attributes in R1,…,Rn C2 = is any condition on aggregate expressions Why ?

28. Evaluation steps: Evaluate FROM-WHERE, apply condition C1 Group by the attributes a1,…,ak Apply condition C2 to each group (may have aggregates) Compute aggregates in S and return the result General form of Grouping and Aggregation SELECT S FROM R1,…,Rn WHERE C1 GROUP BY a1,…,ak HAVING C2

29. An example of the semantics SELECTR.A, SUM(S.C) FROM R, S WHERE R.A = S.B GROUP BY R.A Cross Product (FROM) Group By A Apply selections (WHERE) Where does HAVING Go?

30. Sample Problems

31. Express these in RA and SQL • “Find names of Sailors who’ve reserved boat #103” • “Find names of sailors who’ve reserved a red boat” • “Find sailors who reserved a red or green boat” • Find the sailors who reserved a red boat and have the highest rating. Sailors(sid,sname,rating,age) Reserves(sid,bid,date) Boats(bid,bname,color)

32. Which is Q3 equivalent to? SELECT * FROM R WHERE A = ANY (subquery) SELECT * FROM R WHERE A1 = NOT ANY (subquery) SELECT * FROM R WHERE A1 <> ALL (subquery) Q3 For each tuple t of R check that all values returned by subquery are not equal to value of t[A]

33. Constraints Prevent (some) Anomalies in the Data A poorly designed database causes anomalies: If every course is in only one room, contains redundant information! If we update the room number for just one tuple (Update anomaly) Suppose everyone drops the course suddenly… we lose information about where the course is! (Delete Anomaly) We may not be able to room reservation without students. Need to know every detail (Insert anomaly)

34. Example A, B  C A, D  E B  D A, F  B R(A,B,C,D,E,F) Compute {A,B}+ X = {A, B, } Compute {A, F}+ X = {A, F, }