Lecture 11: Functional Dependencies

1. Lecture 11:Functional Dependencies January 31st, 2003

2. Outline • Relational decomposition • Normal forms • Begin relational algebra

3. Relational Schema Design Recall set attributes (persons with several phones): SSN Name, City, but not SSN  PhoneNumber • Anomalies: • Redundancy = repeat data • Update anomalies = Fred moves to “Bellvue” • Deletion anomalies = Fred drops all phone numbers: what is his city ?

4. Relation Decomposition Break the relation into two:

5. name buys Person Product price name ssn Relational Schema Design Conceptual Model: Relational Model: plus FD’s Normalization: Eliminates anomalies

6. Decompositions in General R(A1, ..., An) Create two relations R1(B1, ..., Bm) and R2(C1, ..., Cp) such that: B1, ..., Bm C1, ..., Cp= A1, ..., An and: R1 = projection of R on B1, ..., Bm R2 = projection of R on C1, ..., Cp

7. Incorrect Decomposition • Sometimes it is incorrect: Decompose on : Name, Category and Price, Category

8. Incorrect Decomposition When we put it back: Cannot recover information

9. Normal Forms First Normal Form = all attributes are atomic Second Normal Form (2NF) = old and obsolete Third Normal Form (3NF) = this lecture Boyce Codd Normal Form (BCNF) = this lecture Others...

10. Boyce-Codd Normal Form A simple condition for removing anomalies from relations: A relation R is in BCNF if: Whenever there is a nontrivial dependency A1, ..., An B in R , {A1, ..., An} is a key for R In English (though a bit vague): Whenever a set of attributes of R is determining another attribute, should determine all the attributes of R.

11. Example What are the dependencies? SSN  Name, City What are the keys? {SSN, PhoneNumber} Is it in BCNF?

12. Decompose it into BCNF SSN  Name, City

13. Summary of BCNF Decomposition Find a dependency that violates the BCNF condition: A , A , … A B , B , … B 1 2 m 1 2 n Heuristics: choose B , B , … B “as large as possible” 1 2 m Decompose: Continue until there are no BCNF violations left. Others A’s B’s Is there a 2-attribute relation that is not in BCNF ? R1 R2

14. Example Decomposition Person(name, SSN, age, hairColor, phoneNumber) SSN  name, age age  hairColor Decompose in BCNF (in class): Step 1: find all keys Step 2: now decompose

15. Other Example • R(A,B,C,D) A B, B C • Keys: • Violations of BCNF:

16. Correct Decompositions A decomposition is lossless if we can recover: R(A,B,C) R1(A,B) R2(A,C) R’(A,B,C) should be the same as R(A,B,C) Decompose Recover R’ is in general larger than R. Must ensure R’ = R

17. Correct Decompositions • Given R(A,B,C) s.t. AB, the decomposition into R1(A,B), R2(A,C) is lossless

18. 3NF: A Problem with BCNF Unit Company Product FD’s: Unit  Company; Company, Product  Unit So, there is a BCNF violation, and we decompose. Unit Company Unit  Company Unit Product No FDs

19. So What’s the Problem? Unit Company Unit Product Galaga99 UW Galaga99 databases Bingo UW Bingo databases No problem so far. All local FD’s are satisfied. Let’s put all the data back into a single table again: Unit Company Product Galaga99 UW databases Bingo UW databases Violates the dependency: company, product -> unit!

20. Solution: 3rd Normal Form (3NF) A simple condition for removing anomalies from relations: A relation R is in 3rd normal form if : Whenever there is a nontrivial dependency A1, A2, ..., An Bfor R , then {A1, A2, ..., An } a super-key for R, or B is part of a key.

21. Relational Algebra • Formalism for creating new relations from existing ones • Its place in the big picture: Declartivequerylanguage Algebra Implementation Relational algebraRelational bag algebra SQL,relational calculus

22. Relational Algebra • Five operators: • Union:  • Difference: - • Selection: s • Projection: P • Cartesian Product:  • Derived or auxiliary operators: • Intersection, complement • Joins (natural,equi-join, theta join, semi-join) • Renaming: r

23. 1. Union and 2. Difference • R1  R2 • Example: • ActiveEmployees  RetiredEmployees • R1 – R2 • Example: • AllEmployees -- RetiredEmployees

24. What about Intersection ? • It is a derived operator • R1  R2 = R1 – (R1 – R2) • Also expressed as a join (will see later) • Example • UnionizedEmployees  RetiredEmployees

25. 3. Selection • Returns all tuples which satisfy a condition • Notation: sc(R) • Examples • sSalary > 40000(Employee) • sname = “Smithh”(Employee) • The condition c can be =, <, , >,, <>

26. Find all employees with salary more than $40,000. s Salary > 40000(Employee)

27. 4. Projection • Eliminates columns, then removes duplicates • Notation: P A1,…,An(R) • Example: project social-security number and names: • PSSN, Name (Employee) • Output schema: Answer(SSN, Name)

28. PSSN, Name (Employee)

29. 5. Cartesian Product • Each tuple in R1 with each tuple in R2 • Notation: R1  R2 • Example: • Employee  Dependents • Very rare in practice; mainly used to express joins

31. Relational Algebra • Five operators: • Union:  • Difference: - • Selection: s • Projection: P • Cartesian Product:  • Derived or auxiliary operators: • Intersection, complement • Joins (natural,equi-join, theta join, semi-join) • Renaming: r

32. Renaming • Changes the schema, not the instance • Notation: rB1,…,Bn (R) • Example: • rLastName, SocSocNo (Employee) • Output schema: Answer(LastName, SocSocNo)

33. Renaming Example Employee Name SSN John 999999999 Tony 777777777 • LastName, SocSocNo (Employee) LastName SocSocNo John 999999999 Tony 777777777

34. Natural Join • Notation: R1 ⋈ R2 • Meaning: R1 ⋈ R2 = PA(sC(R1  R2)) • Where: • The selection sC checks equality of all common attributes • The projection eliminates the duplicate common attributes

35. Employee Dependents = PName, SSN, Dname(sSSN=SSN2(Employee x rSSN2, Dname(Dependents)) Natural Join Example Employee Name SSN John 999999999 Tony 777777777 Dependents SSN Dname 999999999 Emily 777777777 Joe Name SSN Dname John 999999999 Emily Tony 777777777 Joe

36. Natural Join • R= S= • R ⋈ S=

37. Natural Join • Given the schemas R(A, B, C, D), S(A, C, E), what is the schema of R ⋈ S ? • Given R(A, B, C), S(D, E), what is R ⋈ S ? • Given R(A, B), S(A, B), what is R ⋈ S ?

38. Theta Join • A join that involves a predicate • R1 ⋈q R2 = sq (R1  R2) • Here q can be any condition

39. Eq-join • A theta join where q is an equality • R1 ⋈A=B R2 = s A=B (R1  R2) • Example: • Employee ⋈SSN=SSN Dependents • Most useful join in practice

40. Semijoin • R ⋉ S = PA1,…,An (R ⋈ S) • Where A1, …, An are the attributes in R • Example: • Employee ⋉ Dependents

41. Semijoins in Distributed Databases • Semijoins are used in distributed databases Dependents Employee network Employee ⋈ssn=ssn (s age>71 (Dependents)) T = PSSNs age>71 (Dependents) R = Employee ⋉ T Answer = R ⋈ Dependents

42. seller-ssn=ssn pid=pid buyer-ssn=ssn Complex RA Expressions Pname Person Purchase Person Product Pssn Ppid sname=fred sname=gizmo

43. Operations on Bags A bag = a set with repeated elements All operations need to be defined carefully on bags • {a,b,b,c}{a,b,b,b,e,f,f}={a,a,b,b,b,b,b,c,e,f,f} • {a,b,b,b,c,c} – {b,c,c,c,d} = {a,b,b,d} • sC(R): preserve the number of occurrences • PA(R): no duplicate elimination • Cartesian product, join: no duplicate elimination Important ! Relational Engines work on bags, not sets ! Reading assignment: 5.3 – 5.4

44. Finally: RA has Limitations ! • Cannot compute “transitive closure” • Find all direct and indirect relatives of Fred • Cannot express in RA !!! Need to write C program