1 / 18

Fundamentals of Database Systems Fourth Edition El Masri & Navathe

Fundamentals of Database Systems Fourth Edition El Masri & Navathe. Chapter 10 Functional Dependencies and Normalization for Relational Databases. Problems caused by Bad Database Design Wastes storage Causes problems with update anomalies Insertion anomalies Deletion anomalies

kelly-hoover
Download Presentation

Fundamentals of Database Systems Fourth Edition El Masri & Navathe

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

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

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

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

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

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

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

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

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

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

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

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

  13. 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, • …. • …. } 13

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

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

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

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

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

More Related