1 / 65

Chapter 10 Functional Dependencies and Normalization for RDBs

Fundamentals of Database Systems. Chapter 10 Functional Dependencies and Normalization for RDBs. Informal Guidelines for Relation Schema Design. Four Informal Measures Semantics of the attributes Reducing the redundant values in tuples Reducing the null values in tuples

lilike
Download Presentation

Chapter 10 Functional Dependencies and Normalization for RDBs

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 Systems Chapter 10 Functional Dependencies and Normalization for RDBs 1

  2. Informal Guidelines for Relation Schema Design • Four Informal Measures • Semantics of the attributes • Reducing the redundant values in tuples • Reducing the null values in tuples • Disallowing the possibility of generating spurious tuples 2

  3. Informal Guidelines for Relation Schema Design (cont.) • Semantics of the Relation Attributes • Meaning of semantics: specifies how to interpret the attribute values stored in a tuple of the relation • In general, the easier it is to explain the semantics of the relation, the better the relation schema design will be 3

  4. ER-to-Relational Mapping (cont.) 4

  5. Informal Guidelines for Relation Schema Design (cont.) • GUIDELINE 1 • Design a relation schema so that it is easy to explain its meaning • Do not combine attributes from multiple entity types and relationship types into a single relation • Intuitively, if a relation schema corresponds to one entity type or one relationship type, the meaning tends to be clear 5

  6. Informal Guidelines for Relation Schema Design (cont.) Poor design example: • EMP_DEPT mixes attributes of employees and departments • EMP_PROJ mixes attributes of employees and projects 6

  7. Informal Guidelines for Relation Schema Design (cont.) • Redundant Information in Tuples and Update Anomalies • One goal of schema design is to minimize the storage space of the base relations • Example: compare Fig. 14.2 and 14.4 • Redundancy problem • Update anomalies problem 7

  8. Informal Guidelines for Relation Schema Design (cont.) Fig 14.2 8

  9. Informal Guidelines for Relation Schema Design (cont.) 9

  10. Informal Guidelines for Relation Schema Design (cont.) Fig 14.4 10

  11. Informal Guidelines for Relation Schema Design (cont.) 11

  12. Informal Guidelines for Relation Schema Design (cont.) • Update Anomalies • Insertion anomalies: two situations • To insert a new employee tuple into EMP_DEPT, we must include either the attribute values for the department that the employee works for, or nulls • It is difficult to insert a new department that has no employees as yet in the EMP_DEPT relation 12

  13. Informal Guidelines for Relation Schema Design (cont.) • Deletion anomalies • If we delete from EMP_DEPT an employee tuple that happens to represent the last employee working for a particular department, the information concerning that department is lost • Modification anomalies • If we change the value of one of the attributes of a particular department, we must update the tuples of all employees in that department to avoid inconsistency 13

  14. Informal Guidelines for Relation Schema Design (cont.) • GUIDELINE 2 • Design the base relation schemas so that no insertion, deletion, or modification anomalies are present in the relations • If any anomalies are present, note them clearly and make sure that the programs that update the database will operate correctly 14

  15. Informal Guidelines for Relation Schema Design (cont.) • Note • To improve the performance of certain queries, these guidelines may sometimes have to be violated • In general, it is advisable to use anomaly-free base relations and to specify views that include the JOINs for placing together the attributes frequently referenced in important queries • Example: Specify EMP_DEPT as a view to speedup query 15

  16. Informal Guidelines for Relation Schema Design (cont.) • Null Values in Tuples • Problems with null values • Waste space at the storage level • May lead to problems with understanding the meaning of the attributes • The attribute does not apply to this tuple • The attribute value for this tuple is unknown • The value is known but absent • How to account for them when aggregate operations such as COUNT or SUM are applied 16

  17. Informal Guidelines for Relation Schema Design (cont.) 17

  18. Informal Guidelines for Relation Schema Design (cont.) 18

  19. Informal Guidelines for Relation Schema Design (cont.) 19

  20. A NATURAL JOIN on EMP_PROJ1 and EMP_LOCS produces more tuples than those in EMP_PROJ 20

  21. Informal Guidelines for Relation Schema Design (cont.) • GUIDELINE 4 • Design relation schemas so that they can be JOINed with equality conditions on attributes that are either primary keys or foreign keys in a way that guarantees that no spurious tuples are generated • Do not have relations that contain matching attributes other than foreign key-primary key combinations • If such relations are unavoidable, do not join them on such attributes 21

  22. Functional Dependencies • Definition • Consider a relation schema R = {A1, A2, …, An}. A functional dependency, denoted by XY, for X, YR, specifies a constraint on a relation state r of R such that for any two tuples t1 and t2 in r, if t1[X] = t2[X], we must have t1[Y] = t2[Y]. • Note: if X is a candidate key of R, this implies that X  Y for any subset of attributes Y of R 22

  23. Functional Dependencies (cont.) • Meaning • The Y component of a tuple in rdepend on, or are determined by, the values of the X component • The values of the X component of a tuple uniquely (or functionally) determine the values of the Y component • We also say that there is a functional (FD) dependency from X to Y or that Y is functionally dependent onX 23

  24. Functional Dependencies (cont.) • Example: Relation schema EMP_PROJ 1. SSN  ENAME 2. PNUMBER  {PNAME, PLOCATION} 3. {SSN, PNUMBER}  HOURS 24

  25. Functional Dependencies (cont.) • Notice • A functional dependency is a property of the relation schema (intension) R, not of a particular legal relation state (extension) r of R. • An FD cannot be inferred automatically from a given relation extension r but must be defined explicitly by someone who knows the semantics of the attributes of R. 25

  26. Functional Dependencies (cont.) • Inference Rules for FDs • F: the set of functional dependencies specified on a relation schema R • Other dependencies can be inferred or deduced from the FDs in F • The set of all such dependencies is called the closure of F and is denoted by F+ 26

  27. Functional Dependencies (cont.) • Example • Let F = {SSN  {ENAME, BDATE, ADDRESS, DNUMBER}, DNUMBER  {DNAME, DMGRSSN}} • We can infer the following additional FDs SSN  {DNAME, DMGRSSN}, SSN  SSN, DNUMBER  DNAME 27

  28. Functional Dependencies (cont.) • Inference rules • The rules that can be used to infer new dependencies from a given F • Notation FXY: XY is inferred from the set F • For simplicity, • {X, Y} Z is abbreviated to XYZ • {X, Y, Z}  {U, V} is abbreviated to XYZUV • There are six well defined rules 28

  29. Functional Dependencies (cont.) • IR1 (reflexive rule): If XY, then XY • IR2 (augmentation rule): {XY }XZYZ • IR3 (transitive rule): {XY, YZ}XZ • IR4 (decomposition, or projective, rule): {XYZ}XY. • IR5 (union, or additive, rule): {XY, X Z}XYZ • IR6 (pseudotransitive rule): {XY, WYZ }WXZ 29

  30. Functional Dependencies (cont.) • IR1 through IR3 are known as Armstrong’s inference rules • It has been shown by Armstrong (1974) that inference rules IR1 through IR3 are sound and complete • In other words, the set of dependencies, which we called the closure of F, can be determined from F by using only inference rules IR1 through IR3 30

  31. Normalization • Introduction • Normalization of data • A process of analyzing the given relation schemas based on their FDs and primary keys to achieve the desirable properties of (1) minimizing redundancy (2) minimizing the insertion, deletion, and update anomalies • Unsatisfactory relation schemas that do not meet the normal form tests are decomposed into smaller relation schemas 31

  32. Normalization (cont.) • History • Initially, Codd (1972a) proposed three normal forms based on FD, which he called first, second, and third normal form • A stronger definition of 3NF—called Boyce-Codd normal form (BCNF)—was proposed later by Boyce and Codd • Later, a fourth normal form (4NF) and a fifth normal form (5NF) were proposed, based on the concepts of multivalued dependencies and join dependencies 32

  33. Normalization (cont.) • Notice • Normal forms, when considered in isolation from other factors, do not guarantee a good database design • The lossless join or nonadditive join property, which guarantees that the spurious tuple generation problem • The dependency preservation property, which ensures that each functional dependency is represented in some individual relations resulting after decomposition 33

  34. Normalization (cont.) • The database designers need not normalize to the highest possible normal form. • Relations may be left in a lower normalization status for performance reasons • The process of storing the join of higher normal form relations as a base relation—which is in a lower normal form—is known as denormalization 34

  35. Normalization (cont.) • Related terminology • Prime attribute • An attribute of relation schema R is called a prime attribute of R if it is a member of some candidate key of R • Nonprime attribute • An attribute is called nonprime if it is not a prime attribute • Example -- WORKS_ON relation • prime: both SSN and PNUMBER • nonprime: others 35

  36. Normal Forms • First Normal Form (1NF) • Historically, it was defined to disallow multivalued attributes, composite attributes, and their combinations • The domain of an attribute must include only atomic (simple, indivisible) values and that the value of any attribute in a tuple must be a single value from the domain of that attribute 36

  37. Normal Forms (cont.) • Example • Not in 1NF because DLOCATIONS is not an atomic attribute 37

  38. Normal Forms (cont.) • Three main techniques to achieve 1NF 1. Decomposes the non-1NF relation into two 1NF relations 38

  39. Normal Forms (cont.) 2. Expand the key to distinguish each tuple • Has the disadvantage of introducing redundancy 3. Divide the attribute into several atomic attributes • DLOCATIONS => DLOCATION1, DLOCATION2, and DLOCATION3 • The maximum number of values needs to be known • Has the disadvantage of introducing null values 39

  40. Normal Forms (cont.) • The first is superior because it does not suffer from redundancy and it is completely general • The first normal form also disallows multivalued, composite attributes • These are called nested relations • For example: EMP_PROJ(SSN, ENAME, {PROJS(PNUMBER, HOURS)}) • PROJS is a multivalued, composite attribute 40

  41. Normal Forms (cont.) 41

  42. Normal Forms (cont.) • Technique to normalize multivalued, composite attributes into 1NF • Remove the nested relation attributes into a new relation • Propagate the primary key into new relation 42

  43. Normal Forms (cont.) • Second Normal Form (2NF) • 2NF is based on the concept of full functional dependency • XY is a full functional dependency if removal of any attribute A from X invalidates the dependency • XY is a partial dependency if some attribute A X can be removed and the dependency still holds 43

  44. Normal Forms (cont.) • Example • {SSN, PNUMBER}  HOURS is a full dependency • {SSN, PNUMBER}  ENAME is partial 44

  45. Normal Forms (cont.) • Testing for 2NF • The test for 2NF involves testing for functional dependencies whose left-hand side attributes are part of the primary key • If the primary key contains a single attribute, the test need not be applied at all • A relation schema R is in 2NF if every nonprime attribute A in R is fully functionally dependent on the primary key of R 45

  46. Normal Forms (cont.) • Example • EMP_PROJ is in 1NF but not in 2NF • The nonprime attribute ENAME violates 2NF because FD2 is partial • The nonprime attributes PNAME and PLOCATION also violates 2NF because FD3 is partial • Method for normalizing a non-2NF relation • Divide the relation into several relations in which nonprime attributes are associated only with the part of the primary key on which they are fully functionally dependent 46

  47. Normal Forms (cont.) 47

  48. Normal Forms (cont.) • Third Normal Form (3NF) • 3NF is based on the concept of transitive dependency • XY in a relation schema R is a transitive dependency if there is a set of attributes Z that is neither a candidate key nor a subset of any key of R, and both XZ and ZY hold 48

  49. Normal Forms (cont.) • Example • Both SSN  DNUMBER and DNUMBER  DMGRSSN hold • DNUMBER is neither a key nor a subset of the key of EMP_DEPT • SSN  DMGRSSN is a transitive dependency 49

  50. Normal Forms (cont.) • Testing for 3NF • A relation schema R is in 3NF if it satisfies 2NF and no nonprime attribute of R is transitively dependent on the primary key • Example: the EMP_DEPT relation • Method for normalizing a non-3NF relation • Decompose and set up a relation that includes the nonkey attribute(s) that functionally determine(s) other nonkey attribute(s) 50

More Related