Lecture No 13 Functional Dependencies & Normalization ( II ) Mar 3 rd 2011

1. Lecture No 13Functional Dependencies & Normalization ( II )Mar 3rd 2011 Database Systems

2. Example: Functional Dependencies staffNo  job staffNo  dept staffNo  dname dept  dname staffNo job dept dname SL10 Salesman 10 Sales SA51 Manager 20 Accounts DS40 Clerk 20 Accounts OS45 Clerk 30 Operations Functional Dependency Formal Definition: Attribute B is functionally dependant upon attribute A (or a collection of attributes) if a value of A determines a single value of attribute B at any one time. Formal Notation: A  B This should be read as ‘A determines B’ or ‘B is functionally dependant on A’. A is called the determinant and B is called the object of the determinant.

3. Inference Rules for FDs • Given a set of FDs F, we can infer additional FDs that hold whenever the FDs in F hold • Armstrong's inference rules • A1. (Reflexive) If Y subset-of X, then X Y e.g. {Name,Gender}->{Name} • A2. (Augmentation) If X Y, then XZ YZ (Notation: XZ stands for X U Z) {Name, Gender}->{Name} implies {Name,Gender,Age}->{Name,Age} • A3. (Transitive) If X Y and Y Z, then X Z • A1, A2, A3 form a sound and complete set of inference rules

4. Additional Useful Inference Rules • Decomposition • If X YZ, then X Y and X Z • Union • If X Y and X Z, then X YZ • Psuedotransitivity • If X Y and WY Z, then WX Z • Closure of a set F of FDs is the set F+ of all FDs that can be inferred from F

5. Full Functional Dependencies (Order#, line#)  qty (Order#, line#)  price order# line# qty price A001 001 10 200 A002 001 20 400 A002 002 20 800 A004 001 15 300 Full Functional Dependency Full Functional Dependency: Only of relevance with composite determinants. This is the situation when it is necessary to use all the attributes of the composite determinant to identify its object uniquely.

6. Example: Full Functional Dependencies (student#, unit#)  grade student# unit# room grade 9900100 A01 TH224 2 9900010 A01 TH224 14 9901011 A02 JS075 3 9900001 A01 TH224 16 Partial Functional Dependencies unit#  room Repetition of data! Partial Functional Dependency Partial Functional Dependency: This is the situation that exists if it is necessary to only use a subset of the attributes of the composite determinant to identify its object uniquely.

7. Functional Dependency: examples • Example1: Suppose we are given relation R with attributes A, B, C, D, E, F and the FD’s A  BC B  E CD  EF You can even take the example as : A emp no, B dept no, C manager’s emp no, D proj no directed by that manager, E dept name, F time allocated to specified proj. We now show that FD (AD  F) holds in R, and so is a member of the closure of the given set:

8. Example 1

9. Example 1 (…cont)

10. Example 1: Solution • 1. A  BC (given) • 2. A  C (1, decomposition) • 3. AD  CD (2, augmentation) • 4. CD  EF (given) • 5. AD  EF (3 and 4, transitivity) • 6. AD  F (5, decomposition)

11. Irreducible Sets of Dependencies • 1. The right hand side of every FD in set S involves just one attribute (singleton set) • 2. The left hand side of every FD in set S is irreducible, meaning that no attribute can be discarded from the determinant without changing the closure S’ (without converting S into some set not equivalent to S). Such an FD is left-irreducible • 3. No FD in S can be discarded from S without changing the closure S’.

12. Example 2: • Suppose we are given relation R with attributes A, B, C, D, and FD’s A  BC B  C A  B AB  C AC  D • Compute an irreducible set of FD’s that is equivalent to this given set?

13. Example 2: Solution 1. The first step is to rewrite the FD’s such that each one has a singleton right hand side: • A  B • A  C • B  C • A  B • AB  C • AC  D

14. Example 2: Solution 2. Next, attribute C can be eliminated from the left hand side of the FD AC  D, because we have A  C, so A  AC by augmentation, and we are given AC  D, so A  D by transitivity; thus the C on the left hand side of AC  D is redundant. 3. Next, we observe that the FD AB  C can be eliminated, because again we have A  C, so AB  CB by augmentation, so AB  C by decomposition. 4. Finally, the FD A  C is implied by the FD’s A  B and B  C, so it can also be eliminated. We are left with:

15. Example 2: Solution A  B B  C A  D This set is irreducible.

16. Quiz… • Given R = (A,B,C,D,E,F,G,H, I, J) and • F = {AB → E, AG → J, BE → I, E → G, GI → H} • Does F AB → GH?