Chapter 4.pptxdatabase presentation foe fund

good one

1. Chapter Four Functional Dependency and Normalization content Purpose of Normalization Functional Dependency Normalization Prepared by Feven T. Chap 5 1

2. Purpose of Normalization Normalization Is technique for producing set of suitable relations that support data of enterprise. Is the process of decomposing unsatisfactory "bad" relations by breaking up their attributes into smaller relations. Why do we need to normalize? To avoid redundancy (less storage space needed , and data is consistent) To avoid update/delete anomalies. Organize data efficiently Prepared by Feven T. Chap 5 2

3. Functional Dependencies A functional dependency occurs when the value of one (a set of) attribute(s) determines the value of a second (set of) attribute(s) in the same table. StudentID  StudentName StudentID  ( DormName , DormRoom , Fee) The attribute on the left side of the functional dependency is called the determinant. Function dependencies are not equations! Prepared by Feven T . Chap 5 3

4. Characteristics of Functional Dependencies Diagrammatic representation Determinant Attribute or group of attributes on left-hand side of arrow. Prepared by Feven T. Chap 5 4

5. Composite determinant : A determinant of a functional dependency that consists of more than one attribute ( StudentName , ClassName )  (Grade) Functional Dependency Rules If A  (B, C), then A  B and A C If (A,B)  C, then neither A nor B determines C by itself. Prepared by Feven T. Chap 5 5

6. An Example Functional Dependency Prepared by Feven T. Chap 5 6

7. Example Functional Dependency that holds for all Time EmpNum EmpEmail EmpFname EmpLname 123 [email protected] John Doe 456 [email protected] Peter Smith 555 [email protected] Alan Lee 633 [email protected] Peter Doe 787 [email protected] Alan Lee Prepared by Feven T. Chap 5 7 If EmpNum is the PK then the FDs: EmpNum  EmpEmail EmpNum  EmpFname EmpNum  EmpLname must exist.

8. Functional Dependencies Prepared by Feven T. Chap 5 8 EmpNum  EmpEmail EmpNum  EmpFname EmpNum  EmpLname EmpNum EmpEmail EmpFname EmpLname EmpNum EmpEmail EmpFname EmpLname 3 different ways you might see FDs depicted

9. Transitive dependency Prepared by Feven T. Chap 5 9 Transitive dependency Consider attributes A, B, and C, and where A  B and B  C. Functional dependencies are transitive, which means that we also have the functional dependency A  C We say that C is transitively dependent on A through B.

10. Transitive dependency Prepared by Feven T. Chap 5 10 EmpNum EmpEmail DeptNum DeptNname EmpNum EmpEmail DeptNum DeptNname DeptName is transitively dependent on EmpNum via DeptNum EmpNum  DeptN ame EmpNum  DeptNum DeptNum  DeptN ame

11. Partial dependency Prepared by Feven T. Chap 5 11 A partial dependency exists when an attribute B is functionally dependent on an attribute A, and A is a component of a multipart candidate key. InvNum LineNum Qty InvDate Candidate keys: {InvNum, LineNum} InvDate is partially dependent on {InvNum, LineNum} as InvNum is a determinant of InvDate and InvNum is part of a candidate key

12. Multivaled Dependencies Prepared by Feven T. Chap 5 12 A multivaled dependency occurs when a determinant determines a particular set of values: Employee  Degree Employee  Sibling PartKit  Part The determinant of a multivaled dependency can never be a primary key

13. Characteristics of Functional Dependencies Determinants should have minimal number of attributes necessary to maintain functional dependency with attribute(s) on right hand-side Called full functional dependency. Full functional dependency indicates that if A and B are attributes of relation, B is fully functionally dependent on A, if B is functionally dependent on A, but not on any proper subset of A. Prepared by Feven T. Chap 5 13

14. Example Full Functional Dependency Exists in the Staff relation staffNo , sName → branchNo Each value of ( staffNo , sName ) is associated with single value of branchNo branchNo also functionally dependent on subset of ( staffNo , sName ) ( staffNo , sName ) - partial dependency Prepared by Feven T. Chap 5 14

15. Con’t.... Main characteristics of functional dependencies used in normalization: One-to-one relationship between attribute(s) on left-hand side (determinant) and right-hand side of functional dependency Determinant has minimal number of attributes necessary to maintain dependency with attribute(s) on right hand-side Prepared by Feven T. Chap 5 15

16. Normalization Normalization : The process of decomposing unsatisfactory "bad" relations by breaking up their attributes into smaller relations. Normal form : Condition using keys and FDs of a relation to certify whether a relation schema is in a particular normal form Prepared by Feven T. Chap 5 16

17. Normal Forms: Review Unnormalized – There are multivalued attributes or repeating groups 1 NF – No multivalued attributes or repeating groups. 2 NF – 1 NF plus no partial dependencies 3 NF – 2 NF plus no transitive dependencies Prepared by Feven T. Chap 5 17

18. First Normal Form (1NF) All attributes are atomic(no repeating groups). Disallows composite attributes, multivalued attributes, and nested relations; attributes whose values for an individual tuple are non-atomic Values must be simple. Remove horizontal redundancies No two columns hold the same information No single column holds more than a single item Prepared by Feven T. Chap 5 18

19. Con’t… Each row must be unique Use a primary key Benefits Easier to query/sort the data More scalable Each row can be identified for updating Prepared by Feven T. Chap 5 19

20. First Normal Form Prepared by Feven T. Chap 5 20 The following in not in 1NF EmpNum EmpPhone EmpDegrees 123 233-9876 333 233-1231 BA, BSc, PhD 679 233-1231 BSc, MSc EmpDegrees is a multi-valued field: employee 679 has two degrees: BSc and MSc employee 333 has three degrees: BA, BSc , PhD To obtain 1NF relations without loss of information, replace the above with two relations

21. First Normal Form Prepared by Feven T. Chap 5 21 EmpNum EmpDegree 333 BA 333 BSc 333 PhD 679 BSc MSc 679 EmpNum EmpPhone 123 233-9876 333 233-1231 679 233-1231 An outer join between Employee and EmployeeDegree will produce the information we saw before Employee EmployeeDegree

22. Second Normal Form (2NF) Prepared by Feven T. Chap 5 22 Table must be in First Normal Form Remove vertical redundancy The same value should not repeat across rows Composite keys All columns in a row must refer to BOTH parts of the key Benefits Increased storage efficiency Less data repetition All non-key attributes are fully dependent on the PK (“no partial dependencies”) A relation in 2NF will not have any partial dependencies.

23. Second Normal Form (2NF) Prepared by Feven T. Chap 5 23 This table has a composite primary key [Customer ID, Store ID]. The non-key attribute is [Purchase Location]. In this case, [Purchase Location] only depends on [Store ID], which is only part of the primary key. Therefore, this table does not satisfy second normal form.

24. Second Normal Form (2NF) Prepared by Feven T. Chap 5 24 To bring this table to second normal form, we break the table into two tables, and now we have the following: What we have done is to remove the partial functional dependency that we initially had. Now, in the table [TABLE_STORE], the column [Purchase Location] is fully dependent on the primary key of that table, which is [Store ID].

25. Third Normal Form (3NF) A relation in 3NF will not have any transitive dependencies of non-key attribute on a candidate key through another non-key attribute. Table must be in Second Normal Form If your table is 2NF, there is a good chance it is 3NF All columns must relate directly to the primary key Benefits No extraneous data Prepared by Feven T. Chap 5 25

26. Third Normal Form Prepared by Feven T. Chap 5 26 EmpNum EmpName DeptNum DeptName EmpName, DeptNum, and DeptName are non-key attributes. DeptNum determines DeptName, a non-key attribute, and DeptNum is not a candidate key. Consider this Employee relation Is the relation in 3NF? … no Is the relation in 2NF? … yes Candidate keys are? …

27. Third Normal Form Prepared by Feven T. Chap 5 27 EmpNum EmpName DeptNum DeptName We correct the situation by decomposing the original relation into two 3NF relations. Note the decomposition is lossless . EmpNum EmpName DeptNum DeptName DeptNum Verify these two relations are in 3NF.

28. Reading Assignment Boyce Code Normal Form Prepared by Feven T. Chap 5 28