1 / 40

Lecture9: Functional Dependencies and Normalization for Relational Databases

Lecture9: Functional Dependencies and Normalization for Relational Databases. Ref. Chapter14 - 15. Prepared by L. Nouf Almujally. How to produce a good relation schema?. STEPS : Start with a set of relation. Define the functional dependencies for the relation to specify the PK.

orrin
Télécharger la présentation

Lecture9: Functional Dependencies and Normalization for Relational Databases

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. Lecture9:Functional Dependencies and Normalization for Relational Databases Lecture9 Ref. Chapter14 - 15 Prepared by L. NoufAlmujally

  2. How to produce a good relation schema? STEPS: • Start with a set of relation. • Define the functional dependencies for the relation to specify the PK. • Transform relations to normal form. Lecture9

  3. Functional Dependencies • Describes the relationship between attributes in a relation. • If A and B are attributes of relation R, B is functionally dependent on A, denoted by A B, if each value of A is associated with exactly one value of B. B may have several values of A. Determinant Dependent B is functionally dependent on A A B Lecture9

  4. Functional Dependencies X Y • X -> Y holds if whenever two tuples have the same value for X, they must have the same value for Y • For any two tuples t and u in any relation instance r(R): If t[X]=u[X], thent[Y]=u[Y] If t & u agree here Then they must agree here Lecture9 X Y t u

  5. Functional Dependencies Example 1:1 or M:1 relationship between attributes in a relation StaffNo position Position is functionally dependent on Staffno SL21 Manager Lecture9 1:M relationship between attributes in a relation StaffNo is NOT functionally dependent on position position StaffNo Manager SL21 SG5

  6. Examples of FD constraints • Social security number determines employee name • SSN -> ENAME • Project number determines project name and location • PNUMBER -> {PNAME, PLOCATION} • Employee ssn and project number determines the hours per week that the employee works on the project • {SSN, PNUMBER} -> HOURS Lecture9

  7. Identifying the PK • Purpose of functional dependency, specify the set of integrity constraints that must hold on a relation. • The determinant attribute(s) are candidate of the relation, if: • 1:1 relationship between determinant & dependent. • No subset of determinant attribute(s) is a determinant. (nontrivial) If (A, B) C, then NOT A B, and NOT B A • All attributes that are not part of the CK should be functionally dependent on the key: CK all attributes of R • Hold for all time. • PK is the candidate attribute(s) with the minimal set of functional dependency. Lecture9

  8. Identifying the PK • If a relation schema has more than one key, each is called a candidate key. • One of the candidate keys is arbitrarily designated to be the primary key, and the others are called secondary keys. • A Prime attribute must be a member of some candidate key • A Nonprime attribute is not a prime attribute—that is, it is not a member of any candidate key. Lecture9

  9. The Purpose of Normalization • Normalization is a bottom-up approach to database design that begins by examining the relationships between attributes. It is performed as a series of tests on a relation to determine whether it satisfies or violates the requirements of a given normal form. • Purpose: - Guarantees no redundancy due to FDs - Guarantees no update anomalies • Normal Forms: • First Normal Form (1NF) • Second Normal Form (2NF) • Third Normal Form (3NF) • Boyce-CoddNormal Form (BCNF) Lecture9

  10. Normal Forms Defined Informally • 1st normal form • All attributes depend on the key • 2nd normal form • All attributes depend on the whole key • 3rd normal form • All attributes depend on nothing but the key Lecture9

  11. First Normal Form (1NF) • Unnormalized form (UNF): A relation that contains one or more repeating groups. • First normal form (1NF): A relation in which the intersection of each row and column contains one & only one value. • 1NF Disallows: • composite attributes • multivalued attributes • nested relations; attributes whose values for an individual tuple are non-atomic Lecture9

  12. First Normal Form (1NF) CLIENT_PROPERTY Name ClientNo PropertyNo John Key CR76 PG4 PG16 Aline Stewart CR56 PG4 PG36 Lecture9 PG16 Unnormalized form (UNF) • Not in the 1NF because there are Multivalued attribute in the table (PropertyNo)

  13. UNF 1NF Approach 1 • Expand the key so that there will be a separate tuple in the original relation for each repeated attribute(s). • Primary key becomes the combination of primary key and redundant value (multivalued attribute). 1NF relation • Disadvantage: introduce redundancy in the relation. CLIENT_PROPERTY Name ClientNo PropertyNo John Key CR76 PG4 John Key CR76 PG16 Lecture9 Aline Stewart CR56 PG4 Aline Stewart CR56 PG36 Aline Stewart CR56 PG16

  14. UNF 1NF Approach 2 • If the maximum number of values is known for the attribute, replace repeated attribute (PropertyNo) with a number of atomic attributes (PropertyNo1, PropertyNo2, PropertyNo3). 1NF relation • Disadvantage: introduce NULL values in the relation. CLIENT_PROPERTY Name ClientNo PropertyNo1 PropertyNo2 PropertyNo3 John Key PG16 NULL CR76 PG4 Lecture9 Aline Stewart PG36 PG16 CR56 PG4

  15. Summary : first normal form • 1NF : if all attribute values are atomic: no repeating group, no composite attributes. Lecture9

  16. UNF (multivalued) 1NF Lecture9

  17. UNF (nested relations) 1NF Lecture9

  18. Example : First normal form -1NF The following table is not in 1NF because there are nested relations in the table Lecture9

  19. Table in 1NF • all attribute values are atomic because there are no repeating group and no composite attributes. Lecture9

  20. Second Normal Form • Uses the concepts of FDs, primary key • Definitions • Prime attribute: An attribute that is member of the primary key K • Full functional dependency: a FD Y -> Z where removal of any attribute from Y means the FD does not hold any more • Examples: • {SSN, PNUMBER} -> HOURS is a full FD since neither SSN -> HOURS nor PNUMBER -> HOURS hold • {SSN, PNUMBER} -> ENAME is not a full FD (it is called a partial dependency ) since SSN -> ENAME also holds Lecture9

  21. Second Normal Form • Second normal form (2NF) further addresses the concept of removing duplicative data • A relation R is in 2NF if • R is 1NF , and • All non-prime attributes are fully dependent on the candidate keys. Which is creating relationships between these new tables and their predecessors through the use of foreign keys. • A prime attribute appears in a candidate key. • There is no partial dependency in 2NF. Lecture9

  22. Summary : Second Normal Form (2NF) • Meet all the requirements of the 1NF • Remove columns that are not fully dependent upon the primary key. Lecture9

  23. Example1: 1NF 2NF Lecture9 • Remove partial dependencies by placing the functionally dependent attributes in a new relation along with a copy of their determinants.

  24. Example2: Second normal form -2NF • There are two non-key fields.  So, here are the questions: • If I know just Description, can I find out Cost?  No, because we have more than one supplier for the same product. • If I know just Supplier, and I find out Cost?  No, because I need to know what the Item is as well. • Therefore, Cost is fully, functionally dependent upon the ENTIRE PK (Description-Supplier) for its existence. Lecture9

  25. Example 2: Second normal form -2NF • If I know just Description, can I find out Supplier Address?  No, • because we have more than one supplier for the same product. • If I know just Supplier, and I find out Supplier Address?  Yes.  • The Address does not depend upon the description of the item. • Therefore, Supplier Address is NOT functionally dependent upon the ENTIRE PK (Description-Supplier) for its existence. Lecture9

  26. Example 2: Second normal form -2NF The above relations are now in 2NF Lecture9

  27. Third Normal Form (1) • Transitive functional dependency X, Y, Z are attributes of a relation, such that: • If X Y and Y Z, then Z is transitively dependent on X via Y. • Provided X is NOT functionally dependent on Y or Z (nontrivial FD). • Examples: • SSN -> DMGRSSN is a transitive FD • Since SSN -> DNUMBER and DNUMBER -> DMGRSSN hold • SSN -> ENAME is non-transitive • Since there is no set of attributes X where SSN -> X and X -> ENAME Lecture9

  28. Third Normal Form (2) • A relation schema R is in third normal form (3NF) if : • R in 2NF and • no non-prime attribute A in R is transitively dependent on the primary key • R can be decomposed into 3NF relations via the process of 3NF normalization • NOTE: • In X -> Y and Y -> Z, with X as the primary key, we consider this a problem only if Y is not a candidate key. • When Y is a candidate key, there is no problem with the transitive dependency . • E.g., Consider EMP (SSN, Emp#, Salary ). • Here, SSN -> Emp# -> Salary and Emp# is a candidate key. Lecture9

  29. Summary : Third Normal Form (3NF) • Meet all the requirements of the 1NF • Meet all the requirements of the 2NF • Remove columns that are not dependent upon the primary key. Lecture9

  30. Example: 2NF 3NF Lecture9 • If transitive dependencies exist, place transitively dependent attributes in a new relation along with a copy of their determinants.

  31. Example : Third normal form -3NF • describes parcels of land for sale in various counties of a state. Suppose that there are two candidate keys: Property_id# and {County_name, Lot#} • lot # are unique only within each county • Property_id# numbers are unique across counties for the entire state. Lecture9

  32. Example: 2NF 3NF Lecture9

  33. Example : Third normal form -3NF • If I know # of Pages, can I find out Author's Name?  No.  Can I find out Author's Non-de Plume?  No. • If I know Author's Name, can I find out # of Pages?  No.  Can I find out Author's Non-de Plume?  YES. • Therefore, Author's Non-de Plume is functionally dependent upon Author's Name, not the PK for its existence.  Lecture9

  34. Review Example STAFF_PROPERTY_INSPECTION pAddress iDate iTime sName Pno comments StaffNo CarReg Lawrence St, Glasgow 5 Novar Dr., Glasgow PG4 PG16 18-Oct-00 22-Apr-01 1-Oct-01 22-Apr-01 24-Oct-01 10:00 09:00 12:00 13:00 14:00 Replace crockery Good order Damp rot Replace carpet Good condition SG37 SG14 SG14 SG14 SG37 M23JGR M53HDR N72HFR M53HDR N72HFR Ann David David David Ann Lecture9 Unnormalized relation

  35. UNF 1NF STAFF_PROPERTY_INSPECTION pAddress iDate iTime sName Pno comments StaffNo CarReg PG4 PG4 PG4 PG16 PG16 Lawrence St, Glasgow Lawrence St,Glasgow 5 Novar Dr., Glasgow 5 Novar Dr., Glasgow 5 Novar Dr., Glasgow 18-Oct-00 22-Apr-01 1-Oct-01 22-Apr-01 24-Oct-01 10:00 09:00 12:00 13:00 14:00 Replace crockery Good order Damp rot Replace carpet Good condition SG37 SG14 SG14 SG14 SG37 M23JGR M53HDR N72HFR M53HDR N72HFR Ann David David David Ann Lecture9 1NF

  36. 1NF 2NF STAFF_PROPERTY_INSPECTION iDate pAddress iTime sName Pno comments StaffNo CarReg Lecture9 Partial Dependency : PnopAddress

  37. 1NF 2NF PROPERTY 2NF pAddress Pno PnopAddress PROPERTY_INSPECTION iDate iTime sName Pno comments StaffNo CarReg 2NF Lecture9 Transitive Dependency : StaffNoSname

  38. 2NF 3NF PROPERTY 3NF pAddress Pno STAFF 3NF sName StaffNo PROPERTY_INSPECTION Lecture9 iDate iTime 3NF Pno comments StaffNo CarReg PROPERTY(Pno, pAddres) STAFF(StaffNo, sName) PROPERTY_INSPECT(Pno, iDate,iTime, comments, staffNo, CarReg)

  39. Lecture9

  40. References • “Database Systems: A Practical Approach to Design, Implementation and Management.” Thomas Connolly, Carolyn Begg. 5thEdition, Addison-Wesley, 2009. Lecture9

More Related