1 / 44

Chapter 13 & 14

Chapter 13 & 14. Schema Normalization. Normalization. A design process to reduce redundancies and update anomalies in a relational schema Result: a set of decomposed relations that meet certain normal form tests

Samuel
Télécharger la présentation

Chapter 13 & 14

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. Chapter 13 & 14 Schema Normalization

  2. Normalization • A design process to reduce redundancies and update anomalies in a relational schema • Result: a set of decomposed relations that meet certain normal form tests • Four most commonly used normal forms are first (1NF), second (2NF) and third (3NF) normal forms, and Boyce–Codd normal form (BCNF) • A process that is based on keys and functional dependencies among the attributes of a relation

  3. Data Redundancy • Grouping attributes into relation schemas has an effect on storage space. • A bad grouping may produce data redundancy • Problems associated with data redundancy are illustrated by comparing the following Staff and Branch relations with the StaffBranch relation. • StaffBranch is the Natural Join of Staff and Branch • This relation has redundant data (Baddress)

  4. Data Redundancy

  5. Update Anomalies • Types of update anomalies include: • Insertion • Insert tuple (‘SL22’, ‘John Wayne’, ‘Assistant’, 12000, ‘B003’, ‘163 Main, Rapid City’ ) in StaffBranch • Different address for Branch with BranchNo ‘B003’ • Deletion • Delete tuple for StaffNo ‘SA9’ in StaffBranch • Loose information about Branch with BranchNo ‘B007’ • Modification. • Update Baddress for StaffNo ‘SG14’ • Different address for Branch with BranchNo ‘B003’

  6. Lossless-join and Dependency Preservation Properties • Two important properties of decomposition: - Lossless-join property enables us to find any instance of original relation from corresponding instances in the smaller relations. - Dependency preservation property enables us to enforce a constraint on original relation by enforcing some constraint on each of the smaller relations.

  7. Functional Dependency (FD) • Main concept associated with normalization. • Functional Dependency (FD) • Describes relationship among relation attributes. • If A and B are attributes of relation R, B is functionally dependent on A (denoted A  B), if each value of A in R is associated with exactly one value of B in R. • A maps to a single value of B • Given a Branch’s BranchNo, I know its address BranchNo  Baddress

  8. Functional Dependency (cont.) • Property of the meaning (or semantics) of the attributes in a relation. • Diagrammatic representation: • Determinant of a functional dependency refers to attribute or group of attributes on left-hand side of the arrow.

  9. Example - Functional Dependency

  10. Functional Dependency (cont.) • Main characteristics of FDs used in normalization: • have a 1:1 relationship between attribute(s) on left and right-hand side of a dependency; • hold for all time; • are nontrivial. (StaffNoStaffNo is trivial)

  11. Functional Dependency (cont.) • Complete set of functional dependencies for a given relation can be very large. • Important to find an approach that can reduce set to a manageable size. • Need to identify a smaller set of functional dependencies (X) for a relation • A bigger set of FDs, Y can be found, so that every FD in Y can be implied by X.

  12. Functional Dependency (cont.) • Set of all functional dependencies implied by a given set of functional dependencies F called closure of F (written F+). • Set of inference rules, called Armstrong’s axioms, specifies how new functional dependencies can be inferred from given ones.

  13. Functional Dependency: Armstrong’s axioms • Let A, B, and C be subsets of the attributes of relation R. Armstrong’s axioms (AA) are as follows:  1. Reflexivity If B is a subset of A, then A® B 2. Augmentation If A® B, then A,C® B,C ( Written also as AC  BC ) 3. Transitivity If A® B and B® C, then A® C

  14. Reasoning About FDs • Given some FDs, we can infer new FDs F = {StaffNoBranchNo, BranchNoBaddress} Implies StaffNoBaddress • F+ is the set of all FDs that are implied by F • Additional rules that follows from AA • Union: IF AB and AC, then ABC • Decomposition: If ABC, then AB and AC

  15. Example of Implied FDs using Inference rules • Relation PROJECT(EmpID, ProjID, Budget, TimeSpent) Each project has a single budget: ProjIDBudget [FD1] The relation keeps the time spent for an employee on each project: EmpID, ProjIDTimeSpent [FD2] • Implied FDs • By augmentation on FD1: • EmpID,ProjID  EmpID,Budget [FD3] • By decomposing FD3: • EmpID,ProjID  EmpID [FD4] • EmpID,ProjID  Budget [FD5]

  16. Determine whether a set of FDs F implies XY • Computing F+ is expensive. Instead, • Compute X+ (the closure of X) and check whether X+ includes all the attributes in Y. • Algorithm for X+ X+ = X repeat oldX+ = X+; for each FD TZ in F do if X+  T then X+ = X+  Z; until (X+ = oldX+); • If X+ includes all attributes, then X is a key of the relation

  17. Example of Implied FDs using X+ • Relation Contracts(EmpID, ProjID, Budget, TimeSpent) with set F = {FD1: ProjIDBudget; FD2: EmpID, ProjIDTimeSpent} • Find out if F implies FDx: EmpID, ProjIDBudget • Using the algorithm for X+ Initial X+ = EmpID, ProjID X+ = EmpID, ProjID + Budget (using FD1) X+ = EmpID, ProjID, Budget + TimeSpent (using FD2) X+ includes Y therefore FDx is implied by F • Is EmpID  Budget implied by F?

  18. The Process of Normalization • Formal technique for designing a relation based on its primary key and functional dependencies between its attributes. • Often executed as a series of steps. Each step corresponds to a specific normal form, which has known properties. • As normalization proceeds, relations become progressively more restricted (stronger) in format and also less vulnerable to update anomalies.

  19. Relationship Between Normal Forms

  20. Unnormalized Form (UNF) • A table that contains one or more repeating groups. • To create an unnormalized table: • transform data from information source (e.g. form) into table format with columns and rows.

  21. First Normal Form (1NF) • A relation in which intersection of each row and column contains one and only one value. • An unnormalized relation must be converted to a 1NF relation • First identify a primary key, then • place repeating data along with copy of the original key attribute(s) into a separate relation.

  22. Second Normal Form (2NF) • Based on concept of full functional dependency: • A and B are attributes of a relation, • B is fully dependent on A if B is functionally dependent on A but not on any proper subset of A. • 2NF - A relation that is in 1NF and every non-primary-key attribute is fully functionally dependent on the primary key. EMP_PROJ(SSN, Pnum, Hours, Ename, Pname, Ploc) {SSN,Pnum}Hours, SSNEname, Pnum{Pname, Ploc} * Relation is not in 2NF

  23. 1NF to 2NF • Identify FD’s in the relation. • If partial dependencies exist on the primary key remove them by placing them in a new relation along with copy of their determinant. EMP_PROJ(SSN, Pnum, Hours, Ename, Pname, Ploc) {SSN,Pnum}Hours, SSNEname, Pnum{Pname, Ploc} EMP_PROJ decomposed into: EMP1(SSN, Pnum, Hours) , {SSN,Pnum}Hours EMP2(SSN, Ename) , SSNEname EMP3(Pnum,Pname, Ploc) , Pnum{Pname, Ploc}

  24. Third Normal Form (3NF) • Based on concept of transitive dependency: • A, B and C are attributes of a relation such that if A  B and B  C, • then C is transitively dependent on A through B. (Provided that A is not functionally dependent on B or C). • 3NF - A relation that is in 1NF and 2NF and in which no non-primary-key attribute is transitively dependent on the primary key.

  25. 2NF to 3NF • Identify FD’s in the relation. • If transitive dependencies exist on the primary key remove them by placing them in a new relation along with copy of their determinant. EMP_DEPT(Enum, Ename, Sal, Dnum, Dname, Mgr) Enum{Ename, Sal, Dnum}, Dnum{Dname, Mgr} second FD is transitive through Dnum Decompose EMP_DEPT into EMP(Enum, Ename, Sal, Dnum) and DEPT(Dnum, Dname, Mgr)

  26. General Definitions of 2NF and 3NF • Second normal form (2NF) • A relation that is in 1NF and every non-primary-key attribute is fully functionally dependent on anycandidate key. • Third normal form (3NF) • A relation that is in 1NF and 2NF and in which no non-primary-key attribute is transitively dependent on any candidate key. • These definitions may find hidden redundancies (see example

  27. Boyce–Codd Normal Form (BCNF) • Based on functional dependencies that take into account all candidate keys in a relation, however BCNF also has additional constraints compared with general definition of 3NF. • BCNF - A relation is in BCNF if and only if every determinant is a candidate key.

  28. Boyce–Codd normal form (BCNF) • Difference between 3NF and BCNF is that for a functional dependency A  B, 3NF allows this dependency in a relation if B is a primary-key attribute and A is not a candidate key. • Whereas, BCNF insists that for this dependency to remain in a relation, A must be a candidate key. • Every relation in BCNF is also in 3NF. However, relation in 3NF may not be in BCNF.

  29. Boyce–Codd normal form (BCNF) • Violation of BCNF is quite rare. • Potential to violate BCNF may occur in a relation that: • contains two (or more) composite candidate keys; • the candidate keys overlap (ie. have at least one attribute in common).

  30. Example 14.2 • Relation ClientInterview(clientNo, interviewDate, interviewTime, staffNo, roomNo) with FDs: • FD1: clientNo, interviewDate  interviewTime, staffNo, roomNo [PK] • FD2: staffNo, interviewDate, interviewTime  clientNo [CK] • FD3: roomNo, interviewDate, interviewTime  staffNo, clientNo [CK] • FD4: staffNo, interviewDate  roomNo • Last FD makes the relation not in BCNF • Possible anomaly: to change roomNo for staffNo SG5 on 13-May-05 we must update two tuples. • ClientInterview must be decomposed into 2 relations (pp. 421)

  31. Example of decomposition (example 13.9) • R( A, B, C, D, E, F, G, H, I) F: {ABEF, AC, BDGHI, HI, AEBDFGHI, BEACF } fd1 fd2 fd3 fd4 fd5 fd6 PK of R: AB, candidate keys: AE, BE R is not in 2NF because of fd2 and fd3, decompose it R1(A, C), R21(B, D, G, H, I), R22( A, B, E, F) (R1 is Client relation) fd2 fd3, fd4 fd1, fd5’, fd6’ (R21 is PropertyOwner) R21 is not in 3NF because of fd4, decompose it R211(H, I), R212( B, D, G, H) (R211 is Owner) fd4 fd3’ (R212 is PropertyForRent) Decomposition is also in BCNF Relation is ClientRental(clientNo, propertyNo, cName, pAddress, rentStart, rentFinish, rent, ownerNo, oName)

  32. Review of Normalization (UNF to BCNF)

  33. Review of Normalization (UNF to BCNF)

  34. Review of Normalization (UNF to BCNF)

  35. Review of Normalization (UNF to BCNF) Use fd2 Use fd3 Use fd4 fd3 fd4 fd1’,fd6’ fd2 (lost fd5)

  36. Fourth Normal Form (4NF) • Although BCNF removes anomalies due to functional dependencies, another type of dependency called a multi-valued dependency (MVD) can also cause data redundancy. • Possible existence of MVDs in a relation is due to 1NF and can result in data redundancy.

  37. Fourth Normal Form (4NF) - MVD • Dependency between attributes (for example, A, B, and C) in a relation, such that for each value of A there is a set of values for B and a set of values for C. However, set of values for B and C are independent of each other. M 1 B A 1 M C

  38. Fourth Normal Form (4NF) • MVD between attributes A, B, and C in a relation using the following notation: A  B A  C 1NF UNF 

  39. Fourth Normal Form (4NF) • MVD can be further defined as being trivial or nontrivial. • MVD A  B in relation R is defined as being trivial if (a) B is a subset of A or (b) A  B = R. • MVD is defined as being nontrivial if neither (a) nor (b) are satisfied. • Trivial MVD does not specify a constraint on a relation, while a nontrivial MVD does specify a constraint.

  40. Fourth Normal Form (4NF) • Defined as a relation that is in BCNF and contains no nontrivial MVDs. BranchNo Sname BranchNoOname Decompose BranchStaffOwner into two tables

  41. Lossless Join Decomposition • Decomposition of R into R1, R2,…Rm is lossless-join with respect to a set of FDs F in R if for every instance r of R, the following holds: • (R1(r) |X| R2(r) |X|…|X| Rm(r)) = r • The decomposition of R into X and Y is lossless-join with respect to F if and only if F+ contains: (X  Y) X, or (X  Y)  Y The decomposition of EMP_DEPT into EMP and DEPT is lossless-join.

  42. Fifth Normal Form (5NF) • A relation decomposed into two relations must have lossless-join property, which ensures that no spurious tuples are generated when relations are reunited through a natural join. • However, there are requirements to decompose a relation into more than two relations. • Although rare, these cases are managed by join dependency and fifth normal form (5NF).

  43. Fifth Normal Form (5NF) • A relation that has no join dependency. Join dependency exists here

  44. 5NF - Example Eliminate join dependency by decomposing the relation into three relations: Performing join on 2 relations produce spurious tables Performing the join on all 3 relations will produce the original relation

More Related