Section 3

1. Section 3 CSE 444 Introduction to Databases

2. Announcements • Project 1 was due yesterday (10/14/2009) • Homework 1 was released, due 10/28/2009

3. From Last time… • DELETE FROM Table WHERE column = value • Don’t forget the WHERE clause • Otherwise this empties the content of the table

4. Today • E/R Diagrams (Brief overview) • English requirements to E/R Diagram • E/R diagram to Tables • BCNF • FDs, Closure • Examples

5. E/R basics • Know and symbols • Entity • Attributes • Relationship • Arrows • ISA • Difference from OOP in C++/Java

6. E/R (English requirements to diagram) • Each project is managed by one professor (principal investigator) • Professor can manage multiple projects Example courtesy: Database Management Systems, 3rd E, R. Ramakrishnan and J. Gehrke

7. E/R (English requirements to diagram) • Each project is managed by one professor (principal investigator) • Professor can manage multiple projects sponsor age rank ssn Specialty end_date start-date budget pid Professor Project Example courtesy: Database Management Systems, 3rd E, R. Ramakrishnan and J. Gehrke

8. E/R (English requirements to diagram) • Each project is managed by one professor (principal investigator) • Professor can manage multiple projects sponsor age rank ssn Specialty end_date start-date budget pid Manages Professor Project Example courtesy: Database Management Systems, 3rd E, R. Ramakrishnan and J. Gehrke

9. E/R (English requirements to diagram) • Each project is worked on by one or more professors • Professors can work on multiple projects sponsor age rank ssn Specialty end_date start-date budget pid Manages Professor Project Example courtesy: Database Management Systems, 3rd E, R. Ramakrishnan and J. Gehrke

10. E/R (English requirements to diagram) • Each project is worked on by one or more professors • Professors can work on multiple projects sponsor age rank ssn Specialty end_date start-date budget pid Work_in Manages Professor Project Example courtesy: Database Management Systems, 3rd E, R. Ramakrishnan and J. Gehrke

11. Convert to tables sponsor age rank ssn Specialty end_date start-date budget pid Professor Project Work_in Manages • Professor(ssn, age, rank, specialty) • Project(pid, sponsor, start_date, end_date, budget) • Work_in(ssn, pid) • Manages(ssn, pid) Example courtesy: Database Management Systems, 3rd E, R. Ramakrishnan and J. Gehrke

12. Convert to tables sponsor age rank ssn Specialty end_date start-date budget pid Professor Project Work_in Manages • Professor(ssn, age, rank, specialty) • Project(pid, sponsor, start_date, end_date, budget, ssn) • Work_in(ssn, pid) Example courtesy: Database Management Systems, 3rd E, R. Ramakrishnan and J. Gehrke

13. Example courtesy: Database Management Systems, 3rd E, R. Ramakrishnan and J. Gehrke Convert to tables sponsor age rank ssn start-date end_date Specialty budget pid Professor Project CREATETABLE Professor( ssn INTPRIMARYKEY, age INT, urank VARCHAR(30), specialty VARCHAR(30) ); CREATETABLE Project( pid INTPRIMARYKEY, sponser INT, start_dateDATE, end_date DATE, budget FLOAT, ssn INTREFERENCES Professor(ssn) ); CREATETABLE Work_In( ssn INTREFERENCES Professor(ssn), pid INTREFERENCES Project(pid), PRIMARYKEY (ssn, pid) ); Work_in Manages • Professor(ssn, age, rank, specialty) • Project(pid, sponsor, start_date, end_date, budget, ssn) • Work_in(ssn, pid)

14. Data Anomalies • Redundancy is Bad, why? • Redundancy • Update • Delete

15. Functional Dependencies • Dependencies for this relation: • A  B • A  D • B,C  E,F • Do they all hold in this instance of the relation R? • How would you go by finding these in an unknown table? • Functional dependencies are specified by the database programmer based on the intended meaning of the attributes.

16. Keys • Keys, what? • Superkey • Key

17. BCNF • What is it?

18. BCNF Decomposition Algorithm BCNF_Decompose(R) find X s.t.: X ≠X+ ≠ [all attributes] if (not found) then “R is in BCNF” let Y = X+ - Xlet Z = [all attributes] - X+ decompose R into R1(X  Y) and R2(X  Z) continue to decompose recursively R1 and R2

19. A table R(A,B,C,D,E) : Example 1 Note: a set of attributes X is a superkey if X+ = ABCDE CD+ = BCDE CD is not a superkey BAD BAD D+ = BD D is not a superkey Consider the following FDs: CD → E D → B A → CD Which one are the bad dependences? A+ = ABCDE A is a superkey

20. A table R(A,B,C,D,E) : Example 1 R(A,B,C,D,E) [CD+ = BCDE ≠ ABCDE] Note: a set of attributes X is a superkey if X+ = ABCDE BAD BAD R2(B,C,D,E) [D+ = BD ≠ BCDE] R3(A,C,D) [BCNF] Consider the following FDs: CD → E D → B A → CD R4(B,D) [BCNF] R5(C,D,E) [BCNF]

21. A table R(A,B,C,D) : Example 2 Note: a set of attributes X is a superkey if X+ = ABCDE BAD R(A,B,C,D,) [C+ = AD ≠ ABCD] BAD Consider the following FDs: C → D, C+ = AD C → A, C+ = AD B → C, B+ = ABCD R2(A,C,D) [BCNF] R3(B,C) [BCNF]

22. A table S(A,B,C,D,E) : Example 3 S(A,B,C,D,E) [AB+ = ABCD ≠ ABCDE] 1st Solution: Note: a set of attributes X is a superkey if X+ = ABCDE BAD BAD S2(A,B,C,D) [B+ = BD ≠ ABCD] S3(A,B,E) [BCNF] BAD Consider the following FDs: AB → C, AB+ = ABCD DE → C , DE+ = CDE B → D, B+ = BD S4(B,D) [BCNF] S5(A,B,C) [BCNF]

23. A table S(A,B,C,D,E) : Example 3 S(A,B,C,D,E) [DE+ = CDE ≠ ABCDE] 2nd Solution: Note: a set of attributes X is a superkey if X+ = ABCDE BAD BAD S2(A,B,D,E) [B+ = BD ≠ ABDE] S3(C,D,E) [BCNF] BAD Consider the following FDs: AB → C, AB+ = ABCD DE → C , DE+ = CDE B → D, B+ = BD S4(B,D) [BCNF] S5(A,B,E) [BCNF]

24. A table S(A,B,C,D,E) : Example 3 S(A,B,C,D,E) [B+ = BD ≠ ABCDE] 3rd Solution: Note: a set of attributes X is a superkey if X+ = ABCDE BAD BAD S2(A,B,C,E) [AB+ = ABC ≠ ABCE] S3(B,D) [BCNF] BAD Consider the following FDs: AB → C, AB+ = ABCD DE → C , DE+ = CDE B → D, B+ = BD S4(A,B,C) [BCNF] S5(A,B,E) [BCNF]