Fundamentals of Database Systems Fourth Edition El Masri & Navathe

166 Views

Download Presentation
## Fundamentals of Database Systems Fourth Edition El Masri & Navathe

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -

**Fundamentals ofDatabase SystemsFourth EditionEl Masri &**Navathe Chapter 10Functional Dependencies and Normalization for Relational Databases**Functional Dependencies**• Functional dependencies (FDs) are used to specify formal measures of the "goodness" of relational designs. • FDs and keys are used to define normal forms for relations • FDs are constraints that are derived from the meaning and interrelationships of the data attributes. 2**Functional Dependencies**• A set of attributes X functionally determines a set of attributes Y if the value of X determines a unique value for Y • XY • X Y holds if whenever two tuples have the same value for X, they must have the same value for Y. • X Y in R specifies a constraint on all relation instances r(R). 3**Examples of FD constraints**• social security number determines employee name • SSN ENAME • project number determines project name and location • PNUMBER {PNAME, PLOCATION} • City name determines ZIP code • City ZIP • Employee’s ssn and project number determines the hours per week that the employee works on the project • {Level, Experience_Years} Salary 4**Examples of FD constraints**• If K is a key of R, then K functionally determines all attributes in R • (since we never have two distinct tuples with t1[K]=t2[K]) • So: • Keys are Special Cases of FDs 5**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: • IR1.(Reflexive) • If Y is subset of X, then X Y • IR2.(Augmentation) • If X Y, then XZ YZ • (Notation: XZ stands for X U Z) • IR3.(Transitive) • If X Y and Y Z, then X -> Z 6**Inference Rules for FDs (Cont.)**• IR1, IR2, IR3 form a sound and complete set of inference rules • Some additional inference rules that are useful: • (Decomposition) • If X YZ, then X Y and X Z • (Union) • If X Y and X Z, then X YZ • (Pseudo transitivity) • If X Y and WY Z, then WX Z • The last three inference rules, as well as any other inference rules, can be deduced from IR1, IR2, and IR3 (completeness property) 7**F Closure**• Closure of a set F of FDs is the set F+of all FDs that can be inferred from F. • F+ =F +{all FDs that can be logically imply from F using the inference rules} • So F ≡ F+ 8**F Closure**• Example: • R(A,B,C,D,E,G,H) • F= { BCD, AC, EGA, CEH } • Find F+ ? • F+ = { BCD, AC, EGA, CEH , • BC, BD, • EG, EA, • CE, CH, • BE, • BH, • …. • …. } 9**Attributes Closure**• Closure of a set of attributes X with respect to F is the set X+ of all attributes that are functionally determined by X • Given a relation schema R and a set of FDs F that hold on R, Let X belongs to R ( X is a set of attributes that found in R) • X+ = X + { all attributes that can be derived from X using F} 10**Attributes Closure**• Example: • R(A,B,C,D,E,G,H) • F= { BCD, AC, EGA, CEH } • A+ = ACEHG • (CE)+ = CEHGA • B+ = BCDEHGA • Note: Here B Give me all other attributes, So B is a key for R. • So We can Use X+ to find Keys for any relation 11**Minimal Cover of F**• Also called Canonical Cover of F and is denoted by Fc • Every set of FDs has an equivalent Fc. • There can be several equivalent minimal sets. 12**Minimal Cover of F**• Compute Fc from F • Let Fc F. • Repeat the following steps until no more reduction can be made on Fc • Remove all redundant attributes from X in XY • Ex. AB, ACB (C is redundant) • Remove all redundant attributes from Y in XY • Ex. ABC, BC (C is redundant) • Replace each pair of FDs of the form • XY1, XY2 • By XY1Y2 • Ex. AB, AC • Then ABC 13**Minimal Cover of F**• Example • F= { AC, • ACD, • EAD, • EH } • Remove C from ACD • Then Convert AC, AD to ACD • Then Convert EAD, EH to EADH • Remove D from EADH • Then Fc is { ADC, EAH} Find Fc? 14**Problems caused by Bad Database Design**• Wastes storage • Causes problems with update anomalies • Insertion anomalies • Deletion anomalies • Modification anomalies 15**EXAMPLE OF AN UPDATE ANOMALY**• Consider the relation: • EMP_PROJ(Emp#, Proj#, Ename, Pname, No_hours) • Update Anomaly: • Changing the name of project number P1 from “Billing” to “Customer-Accounting” may cause this update to be made for all 100 employees working on project P1. 16**EXAMPLE OF AN INSERT ANOMALY**• Consider the relation: • EMP_PROJ(Emp#, Proj#, Ename, Pname, No_hours) • Insert Anomaly: • Cannot insert a project unless an employee is assigned to it. • Conversely • Cannot insert an employee unless a he/she is assigned to a project. 17**EXAMPLE OF AN DELETE ANOMALY**• Consider the relation: • EMP_PROJ(Emp#, Proj#, Ename, Pname, No_hours) • Delete Anomaly: • When a project is deleted, it will result in deleting all the employees who work on that project. • Alternately, if an employee is the sole employee on a project, deleting that employee would result in deleting the corresponding project. 18