Part3 Database Analysis and Design Techniques Chapter 06- Theory of Relational Database

1. Part3 Database Analysis and Design TechniquesChapter 06- Theory of Relational Database Lu Wei College of Software and Microelectronics Northwestern Polytechnical University Database Systems

2. Outline • Potential Problems in a Base Relation • The concept of Normalization • Functional Dependencies • The process of Normalization • First Normal Form (1NF) • Second Normal Form (2NF) • Third Normal Form (3NF) • General Definitions of 2NF and 3NF Lu Wei

3. Outline • Boyce-Codd Noraml Form (BCNF) • Review of Normalization (1NF to BCNF) • Fourth Noramal Form (4NF) • Fifth Normal Form (5NF) Lu Wei

4. Outline • Potential Problems in a Base Relation • The concept of Normalization • Functional Dependencies • The process of Normalization • First Normal Form (1NF) • Second Normal Form (2NF) • Third Normal Form (3NF) • General Definitions of 2NF and 3NF Lu Wei

5. Potential Problems in a Base Relation Lu Wei

6. Potential Problems in a Base Relation • In the figure above, The StaffBranch relation is an alternative format of the Staff and Branch relations. • Problems • StaffBranch relation has redundant data; the details of a branch are repeated for every member of staff. In contrast, the branch information appears only once for each branch in the Branch relation and only the branch number (branchNo) is repeated in the Staff relation, to represent where each member of staff is located. Lu Wei

7. Potential Problems in a Base Relation • Problems • Relations that contain redundant information may potentially suffer from update anomalies • Types of update anomalies include • Insertion • Deletion • Modification Lu Wei

8. Potential Problems in a Base Relation Relation TeacherCourse Problems Lu Wei

9. Potential Problems in a Base Relation Relation Course Relation Teacher Lu Wei

10. Potential Problems in a Base Relation Relation StuDeptCourse Lu Wei

11. Potential Problems in a Base Relation Relation Course Relation StuDept Relation SC Lu Wei

12. Potential Problems in a Base Relation Relation StuDept Are there problems in relation StuDept? Lu Wei

13. Potential Problems in a Base Relation Relation Student Relation Department Lu Wei

14. Potential Problems in a Base Relation • What problems do often exist in relations? • Why are there problems in some relations but not in other relations? • How to resolve these problems? • How to measure the appropriateness of attributes groupings in relations? • How to achieve a better relation schema? • Normalization is helpful for resolving these problems. Lu Wei

15. Outline • Potential Problems in a Base Relation • The concept of Normalization • Functional Dependencies • The process of Normalization • First Normal Form (1NF) • Second Normal Form (2NF) • Third Normal Form (3NF) • General Definitions of 2NF and 3NF Lu Wei

16. The concept of Normalization • Normalization • A technique for producing a set of relations with desirable properties, given the data requirements of an enterprise. • The process of normalization is a formal method that identifies relations based on their primary or candidate keys and the functional dependencies among their attributes. Lu Wei

17. The concept of Normalization • The process of normalization was first developed by E.F.Codd. • Normalization is often performed as a series of tests on a relation to determine whether it satisfies or violates the requirements of a given normal form. • 1NF,2NF,3NF, BCNF • 4NF,5NF Lu Wei

18. Outline • Potential Problems in a Base Relation • The concept of Normalization • Functional Dependencies • The process of Normalization • First Normal Form (1NF) • Second Normal Form (2NF) • Third Normal Form (3NF) • General Definitions of 2NF and 3NF Lu Wei

19. Functional Dependencies • Data dependency • The relationships between attributes in a relation that descript the dependency or restriction on each other. • The types of data dependency • Functional dependency(FD) • Multi-valued denpendency(MVD) • Join denpendency(JD) Lu Wei

20. Functional Dependencies • Functional dependency(FD) • Describes the relationship between attributes in a relaiton. • If A and B are attributes of relation R, B is functionally dependent on A(denoted AB), if each value of A is associated with exactly one value of B. A is determinant of B • Functional dependency is a property of the meaning or simantics of the attributes in a relation. Lu Wei

21. Functional Dependencies • Functional dependency(FD) • If AB and BA, then we denote AB • Trivial functional dependency（平凡函数依赖） • If AB and B is a subset of A(BA) • Nontrivial functional dependency • If AB and B is not a subset of A(BA) Lu Wei

22. Functional Dependencies • Trivial dependencies are not very interesting in practical terms; we are normally more interested in nontrivial dependencies because they reprensent integrity constraints for the relation. Lu Wei

23. Functional Dependencies • Identifying a functional dependency • When identifying functional dependencies between attributes in a relation it is important to distinguish clearly between the values held by an attribute at a given point in time and the set of all possible values that an attribute may hold at different times. • A functional dependency is a property of a relational schema(intension) and not a property of a particular instance of the schema(extension). Lu Wei

24. Functional Dependencies • Generally we can identify • If the relationship between X and Y is one-to-one(1:1), then XY • If the relationship between X and Y is one-to-many(1:*), then YX • If the relationship between X and Y is many-to-many(*:*), then maybe there is no functional dependency between X and Y Lu Wei

25. Functional Dependencies • Formal description of a relation schema R(U,D,dom,F) R-relation name U-attribute set D-domains of all attributes Dom-mapping between attributes and domains F-data dependecy between attributes D and dom is not important for relation schema, so we can simplify it as R(U,F) Lu Wei

26. Functional Dependencies Given relation schema R(U,F) U={sNo,sName,sDept,deptAddr,deptMn,cNo,cName,score} F={sNosName,sNosDept,sDeptdeptAddr,deptMn,cNocName,(sNo,cNo)score} Lu Wei

27. Inference Rules for Functional Dependencies • The complete set of functional dependencies for a given relation can be very large. • It is important to find an approach that can reduce that set to a manageable size. Lu Wei

28. Inference Rules for Functional Dependencies • We want to identify a set of functional dependencies (represented as X) for a relation that is smaller than the complete set of functional dependencies(represented as Y) for that relation and has the property that every functional dependency in Y is implied by the functional dependencies in X. Lu Wei

29. Inference Rules for Functional Dependencies • The set of all functional dependencies that are implied by a given set of functional dependencies X is called the closure of X, written X＋. • We need a set of rules to help compute X＋from X. • A set of inference rules, called Armstrong’s axioms, specifies how new functional dependencies can be inferred from given ones. Lu Wei

30. Inference Rules for Functional Dependencies • Armstrong’s axioms are as follows: • Reflexivity: If B is a subset of A, then AB • Augmentation: If AB, then A,CB,C • Transitivity: If AB and BC, then AC. • Self-fetermination: A A • Decomposition: If AB,C, then AB and AC. • Union: If AB and AC , then AB,C • Composition: If AB and CD , then A,CB,D Lu Wei

31. Inference Rules for Functional Dependencies • Minimal Sets of Functional Dependencies(Xmin) • Every dependency in X has a single attribute on its right-hand side. • We cannot replace any dependency AB in X with dependency CB, where C is a proper subset of A, and still have a set of dependencies that is equivalent to X.(if X＋= Y＋, then X is equivalent to Y) • We cannot remove any dependency from X and still have a set of dependencies that is equivalent to X. • Xmin is equivalent to X Lu Wei

32. Inference Rules for Functional Dependencies • A minimal set of dependencies should be in a standard form with no redundancies. • There can be several minimal covers for a set of functional dependencies. • Try to identify the minimal set of a functional dependency using Armstrong axioms. Lu Wei

33. Inference Rules for Functional Dependencies Given relation schema R(U,F) U={sNo,sName,sDept,deptAddr,deptMn,cNo,cName,score} F={sNosName, sNosDept, sNodeptMn, sDeptdeptAddr, deptMn, cNocName,(sNo,cNo)score} Is F a minimal cover? If F is not a minimal cover, try to give a Fmin. F’={sNosName, sNosDept, sDeptdeptAddr, sDeptdeptMn, cNocName, (sNo,cNo)score} Lu Wei

34. Outline • Potential Problems in a Base Relation • The concept of Normalization • Functional Dependencies • The process of Normalization • First Normal Form (1NF) • Second Normal Form (2NF) • Third Normal Form (3NF) • Boyce-Codd Noraml Form (BCNF) Lu Wei

35. The process of Normalization • Normalization is a Formal technique for analyzing a relation based on its primary key and the functional dependencies between the attributes of that relation. • Normalization is often executed as a series of steps. Each step corresponds to a specific normal form, which has known properties. Lu Wei

36. The process of Normalization Lu Wei

37. Outline • Potential Problems in a Base Relation • The concept of Normalization • Functional Dependencies • The process of Normalization • First Normal Form (1NF) • Second Normal Form (2NF) • Third Normal Form (3NF) • General Definitions of 2NF and 3NF Lu Wei

38. First Normal Form (1NF) • Definition of 1NF • A relation in which the intersection of each row and column contains one and only one value. • For the relational data model, it is important to recognize that it is only 1NF that is critical in creating relations. All the subsequent normal forms are optional. Lu Wei

39. First Normal Form (1NF) • UNF(Unnormalized form) to 1NF • Flattening the table (平修表): Entering appropriate data into the empty columns of rows containing the repeating data. • Placing the repeating data along with a copy of the original key attribute(s) into a separate relation. • Add columns to restrict each cell contains only one value. Lu Wei

40. First Normal Form (1NF) • Examples Student Lu Wei

41. First Normal Form (1NF) Student Lu Wei

42. First Normal Form (1NF) StudentPhone StudentBaseInfo Lu Wei

43. First Normal Form (1NF) Student Lu Wei

44. First Normal Form (1NF) Add columns to restrict each cell contains only one value if we can identify the number of values may appear in a cell. Or adding columns cannot resolve the problem. Lu Wei

45. First Normal Form (1NF) ClientRental Lu Wei

46. First Normal Form (1NF) ClientRental Lu Wei

47. First Normal Form (1NF) Client PropertyRentalOwner Lu Wei

48. Outline • Potential Problems in a Base Relation • The concept of Normalization • Functional Dependencies • The process of Normalization • First Normal Form (1NF) • Second Normal Form (2NF) • Third Normal Form (3NF) • General Definitions of 2NF and 3NF Lu Wei

49. Second Normal Form (2NF) • Problems in a relation that is in 1NF Lu Wei

50. Second Normal Form (2NF) • 2NF is based on the concept of full functional dependency so we introduce full and partial functional dependency in first. Lu Wei