Fundamentals of Database Systems Fourth Edition El Masri & Navathe - PowerPoint PPT Presentation

wynter-haney
fundamentals of database systems fourth edition el masri navathe n.
Skip this Video
Loading SlideShow in 5 Seconds..
Fundamentals of Database Systems Fourth Edition El Masri & Navathe PowerPoint Presentation
Download Presentation
Fundamentals of Database Systems Fourth Edition El Masri & Navathe

play fullscreen
1 / 18
Download Presentation
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 - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Fundamentals ofDatabase SystemsFourth EditionEl Masri & Navathe Chapter 10Functional Dependencies and Normalization for Relational Databases

  2. 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

  3. 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 • XY • 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

  4. 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

  5. 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

  6. 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

  7. 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

  8. 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

  9. F Closure • Example: • R(A,B,C,D,E,G,H) • F= { BCD, AC, EGA, CEH } • Find F+ ? • F+ = { BCD, AC, EGA, CEH , • BC, BD, • EG, EA, • CE, CH, • BE, • BH, • …. • …. } 9

  10. 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

  11. Attributes Closure • Example: • R(A,B,C,D,E,G,H) • F= { BCD, AC, EGA, CEH } • 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

  12. 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

  13. 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 XY • Ex. AB, ACB (C is redundant) • Remove all redundant attributes from Y in XY • Ex. ABC, BC (C is redundant) • Replace each pair of FDs of the form • XY1, XY2 • By XY1Y2 • Ex. AB, AC • Then ABC 13

  14. Minimal Cover of F • Example • F= { AC, • ACD, • EAD, • EH } • Remove C from ACD • Then Convert AC, AD to ACD • Then Convert EAD, EH to EADH • Remove D from EADH • Then Fc is { ADC, EAH} Find Fc? 14

  15. Problems caused by Bad Database Design • Wastes storage • Causes problems with update anomalies • Insertion anomalies • Deletion anomalies • Modification anomalies 15

  16. 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

  17. 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

  18. 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