1 / 32

FD and Normalization

Learn about functional dependency in database management to control data redundancy and ensure data consistency. Explore examples, inference axioms, and canonical cover.

sbecker
Télécharger la présentation

FD and Normalization

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. FD and Normalization www.assignmentpoint.com

  2. Functional Dependency • Objectives - To control the redundancy of data. - To preserve the consistency of data. Data redundancy occurs when a piece of data is stored in more than one place in the database. If the content of that piece of data is changed to a particular value, then it is necessary to ensure that every copy of the same piece of data is changed to the same value. This piece of data is said to be consistent in the database. If some, but not all, copies of this piece of data are changed to the same value, the data is said to be inconsistent. www.assignmentpoint.com

  3. Functional Dependency • Functional Dependency (FD) Given a relation r(R) and two sets of its attributes A and B. We will say that attribute(s) A functionally determines attribute(s) B with respect to r, denoted by A → B, if and only if for any two tuples t1 and t2 of r whenever t1(A) = t2(A) then t1(B) =t2(B). That is, if A functionally determines B, then whenever two tuples of r have identical values in column A, their respective values in column B must also be identical. www.assignmentpoint.com

  4. Functional Dependency Name Continent Area Length Caspian Sea Asia – Europe 143244 760 Superior North America 31700 350 Victoria Africa 26828 250 Aral Sea Asia 24904 280 Huron North America 23000 206 Michigan North America 22300 307 Tanganyika Africa 12700 420 Lakes-of-The-World • Functional Dependency (FD) Example: Given the Lakes-Of-The-World relation shown below, state whether or not the functional dependencies (a) Continent → Name and (b) Name → Length are satisfied by this relation. www.assignmentpoint.com

  5. Functional Dependency • Functional Dependency (FD) (a) Here t1(Continent)= t2(Continent)=North America But t1(Name)= Superior ≠ t2(Name)=Huron So, Continent → Name is not satisfied by the relation i.e. Continent Name. (b) The functional dependency Name → Length is satisfied by the relation. Because for any given lake, there is only one length associated with it. www.assignmentpoint.com

  6. Functional Dependency • Inference Axioms for FDs/Armstrong's axioms Given a set F of FDs, the inference axioms are a set of rules that tell us that if a relation satisfies the FDs of F, the relation must satisfy certain other FDs. The latter set of FDs are said to be derived or logically deduced from the FDs of F. • If Y X, then X → Y (Reflexivity) • If X → Y, then XW → Y and/or XW → YW(Augmentation) • If X → Y and Y → Z, then X → Z (Transitivity) • If X → Y and YW → Z, then XW → Z (Pseudotransitivity) • If X → Z and X → Y, then X → YZ (Additivity or Union) • If X → YZ then X → Y and X → Z ( Projectivity or Decomposition) www.assignmentpoint.com

  7. Functional Dependency • Canonical Cover For a given set F of FDs, a canonical cover, denoted by Fc, is a set of FDs where the following conditions are simultaneously satisfied: • Every FD of Fc is simple. That is, the right-hand side of every functional dependency of Fc has only one attribute. • Fc is left-reduced. • Fc is nonredundant. www.assignmentpoint.com

  8. Functional Dependency • Canonical Cover Example: Given the set F of FDs shown below, find a canonical cover for F. F= {X → Z, XY → WP, XY → ZWQ, XZ → R}. FC = {X → Z, XY → WP, XY → ZWQ, XZ → R} = {X → Z, XY → W, XY → P, XY → Z, XY → W, XY → Q, XZ → R}. [Simple] = {X → Z, XY → W, XY → P, XY → Z, XY → Q, XZ → R}. [Remove redundancy] = {X → Z, XY → W, XY → P, XY → Z, XY → Q, XZ → R}. = {X → Z, XY → W, XY → P, XY → Q, XZ → R}. [Remove redundancy] = {X → Z, XY → W, XY → P, XY → Q, XZ → R}. = {X → Z, XY → W, XY → P, XY → Q, X → R}. [Left-reduced]  FC = {X → Z, XY → W, XY → P, XY → Q, X → R}. This resulting set where all FDs are simple, left-reduced and non-redundant, is the canonical cover of F. www.assignmentpoint.com

  9. Functional Dependency Problem 1: Assume that AB → C, C → D and D → A are simultaneously satisfied by a relation r(R). What are the candidate keys of this relation? Which one is the PK? What are the prime attributes? Are there any super keys for this relation? A set of attributes of r is a candidate key of r if and only if the set functionally determines every attribute of the relation. The set of attributes that can be selected as candidate keys of the relation r are: AB and DB. Let's see why. AB is a candidate key. The given FDs are: AB → C, C → D and D → A. If AB is a key of r then it must be true that AB → A, AB → B, AB→ C and AB→ D. Using the inference axioms and the FDs of F we have that: AB → A and AB → B ( Reflexivity axiom) AB → C (given) AB → C (given) and C → D (given) then AB → D ( Transitivity axiom) Therefore, AB is a candidate key of the relation r because it functionally determines any other attribute of the relation r. www.assignmentpoint.com

  10. Functional Dependency DB is another candidate key. The given FDs are: AB → C, C → D and D → A. If DB is a key of r then it must be true that DB → A, DB → B, DB → C and DB → D. Using the inference axioms and the FDs of we have that; DB → D and DB → B (Reflexivity axiom) DB → D (previous step) and D → A (given) we have that DB → A (Transitivity axiom) DB → A (from previous step) and AB → C (given) then DBB → C (Pseudotran-sitivity axiom) Since B B= B we can rewrite DBB → C as DB → C. Therefore, DB is a candidate key of the relation r because it functionally determines all other attributes of the relation r. www.assignmentpoint.com

  11. Functional Dependency   There are two candidate keys AB and DB. However, only one can be selected as the PK of the relation. The database designer or the DBA should choose the one that makes more sense or is more convenient for the application.  The prime attributes of this relation are: A, B and D. A prime attribute is any attribute that is part of a PK or an alternate key. There are several super keys. They are: ABC, ADB, ABCD, DBA, DBC and DBAC. www.assignmentpoint.com

  12. Functional Dependency Problem 2: Given the relation r(A, B, C) and the set F={AB → C, B → D, D → B} of functional dependencies, find the candidate keys of the relation. How many candidate keys are in this relation? What are the prime attributes? AB is a candidate key. AB → A and AB → B ( Reflexivity axiom) AB → C (given) AB → B (step 1) and B → D (given) then AB → D ( Transitivity axiom) Since AB determines every other attribute of the relation, we have that AB is a candidate key. www.assignmentpoint.com

  13. Functional Dependency Problem 2:. . . . . AD is a candidate key. AD → A and AD → D ( Reflexivity axiom) AD → D (step 1) and D → B (given) then AD → B AD → B (step 2) and AB → C (given) then AAD → C (Pseudotransitivity axiom). i.e. AD → C Since AD determines every other attribute of the relation, we have that AD is another candidate key. The prime attributes are: A, B, and D. www.assignmentpoint.com

  14. Normalization Normalization: It refers to a reversible step-by-step process in which a given set of relations is replaced by successive collections of relations that have a progressively simpler and more regular structure. Each step is referred to as a normal form. Since the process is reversible, the original set of relations can be recovered with no loss of information. Objectives: • To make it feasible to represent any relation in the database. • To obtain powerful relational retrieval algorithms based on a collection of primitive relational operators. • To free relations from undesirable insertion, update, and deletion anomalies. • To reduce the need for restructuring the relations as new data types are introduced. www.assignmentpoint.com

  15. Normalization Question: Normalize step by step the following database into 1NF, 2NF and 3NF: PROJECT(Proj-ID, Proj-Name, Proj-Mgr-ID, Emp-ID, Emp-Name, Emp-Dpt, Emp-Hrly-rate, Total-Hrs) Solution: Table entries that have more than one value are called multivalue entries. Table with multivalue entries are called unnormalized tables. Within an unnormalized table, we will call a repeating group an attribute or group of attributes that may have multivalue entries for single occurrences of the table identifier. Here table PROJECT is an unnormalized table where attributes Emp-ID, Emp-Name, Emp-Dpt, Emp-Hrly-rate, and Total-Hrs are repeating groups. Thus, we need to put the table in first normal form (1NF). www.assignmentpoint.com

  16. Normalization PROJECT(Proj-ID, Proj-Name, Proj-Mgr-ID, Emp-ID, Emp-Name, Emp-Dpt, Emp-Hrly-rate, Total-Hrs) FIRST NORMAL FORM (1NF) A relation r(R) is said to be in First Normal Form (1NF), if and only if every entry of the relation has at most a single value. Thus, it requires to decompose the table PROJECT into two new tables. It is also necessary to identify a PK for each table. First table with nonrepeating groups: PROJECT (Proj-ID, Proj-Name, Proj-Mgr-ID) Second table with table identifier and all repeating groups: PROJECT-EMPLOYEE (Proj-ID, Emp-ID, Emp-Name, Emp-Dpt, Emp-Hrly-rate, Total-Hrs) Here Proj-ID is the table identifier. www.assignmentpoint.com

  17. Normalization Data Anomalies in 1NF Relations Redundancies in 1NF relations lead to a variety of data anomalies: • Insertion anomalies, • Deletion anomalies and • Update anomalies. Insertion anomalies occur in PROJECT-EMPLOYEE relation because we cannot insert information about any new employee unless that employee is already assigned to a project. Any attribute of the composite key (Proj-ID, Emp-ID) cannot be NULL. www.assignmentpoint.com

  18. Normalization Data Anomalies in 1NF Relations Deletion anomalies occur when we delete the last tuple of a particular employee. In this case, we not only delete the project information that connects that employee to a particular project but also lose other information about the department for which this employee works. Update anomalies occur because the department for which an employee works may appear many times in the table. It is this redundancy of information that causes the anomaly because if an employee moves to another department, we are now faced with two problems: we either search the entire table looking for that employee and update his Emp-Dpt value or we miss one or more tuples of that employee and end up with an inconsistent state. www.assignmentpoint.com

  19. Normalization Emp-Name Proj-Id Emp-Dpt Emp-Id Emp-Hrly-Rate Partial Dependencies Given a relation r(R), the sets of attributes X and Y ( X,Y R), and X → Y, we will say that attribute Y is fully dependent on attribute X if only if there is no proper subset W of X such that W → Y. If there is a proper subset W of X such that W → Y then attribute Y is said to be partially dependent on attribute X. PROJECT-EMPLOYEE (Proj-ID, Emp-ID, Emp-Name, Emp-Dpt, Emp-Hrly-rate, Total-Hrs) www.assignmentpoint.com

  20. Normalization PROJECT-EMPLOYEE (Proj-ID, Emp-ID, Emp-Name, Emp-Dpt, Emp-Hrly-rate, Total-Hrs) SECOND NORMAL FORM (2NF) A relation r (R) is in Second Normal Form (2NF) if and only if the following two conditions are met simultaneously: • r(R) is already in 1NF. • No nonprime attribute is partially dependent on any key or, equivalently, each nonprime attribute in R is fully dependent on every key. • In PROJECT-EMPLOYEE relation attributes Emp-Name, Emp-Dpt and Emp-Hrly-rate are fully dependent only on Emp-ID. • The only attribute Total-Hrs fully depends on the composite PK (Proj-ID, Emp-ID). This is shown below.   www.assignmentpoint.com

  21. Normalization SECOND NORMAL FORM (2NF) Now break this relation into two new relations so that partial dependency does not exist. • HOURS-ASSIGNED (Proj-ID, Emp-ID, Total-Hrs) • EMPLOYEE (Emp-ID, Emp-Name, Emp-Dpt, Emp-Hrly-rate) www.assignmentpoint.com

  22. Normalization Data Anomalies in 2NF Relations Relations in 2NF are still subject to data anomalies: • Insertion anomalies, • Deletion anomalies and • Update anomalies. Insertion anomalies: For sake of explanation, let us assume that the department in which an employee works functionally determines the hourly rate charged by that employee. That is, Emp-Dpt → Emp-Hrly-rate. Insertion anomalies occur in the EMPLOYEE relation. For example, consider a situation where we would like to set in advance the rate to be charged by the employees for a new department. We cannot insert this information until there is an employee assigned to that department. www.assignmentpoint.com

  23. Normalization Data Anomalies in 2NF Relations Deletion anomalies occur whenever we delete the tuple of an employee who happens to be the only employee left in a department. In this case, we lose the information about the rate that the department charges. Update anomalies occur because there may be several employees from the same department working on different projects. If the department rate changes, we need to make sure that the corresponding rate is changed for all employees that work for that department. Otherwise, the database may end up in an inconsistent state. www.assignmentpoint.com

  24. Normalization Transitive Dependencies Assume that A, B, and C are the set of attributes of a relation r(R). Further assume that the following functional dependencies are satisfied simultaneously: A → B, B A, B → C, C A and A → C. C → B is neither prohibited nor required. If all these conditions are true, we will say that attribute C is transitively dependent on attribute A. www.assignmentpoint.com

  25. Normalization EMPLOYEE (Emp-ID, Emp-Name, Emp-Dpt, Emp-Hrly-rate) THIRD NORMAL FORM (3NF) A relation r(R) is in Third Normal Form (3NF) if and only if the following conditions are satisfied simultaneously: • r(R) is already in 2NF. • No nonprime attribute is transitively dependent on the key (i.e. No nonprime attribute functionally determines any other nonprime attribute). In EMPLOYEE relation, attribute Emp-Hrly-rate is transitively dependent on the PK Emp-ID through the functional dependency Emp-Dpt → Emp-Hrly-rate. This is shown below. www.assignmentpoint.com

  26. Normalization EMPLOYEE (Emp-ID, Emp-Name, Emp-Dpt, Emp-Hrly-rate) Now break this relation into two new relations so that transitive dependency does not exist. EMPLOYEE (Emp-ID, Emp-Name, Emp-Dpt) CHARGES (Emp-Dpt, Emp-Hrly-rate) The new set of relations that we have obtained through the normalization process does not exhibit any of the anomalies. That is, we can insert, delete and update tuples without any of the side effects. THIRD NORMAL FORM (3NF) www.assignmentpoint.com

  27. Normalization Data Anomalies in 3NF Relations Relations in 3NF are still susceptible to data anomalies particularly when the relations have two overlapping candidate keys or when a non prime attribute functionally determines a prime attribute. Example: Consider the MANUFACTURER relation shown below where each manufacturer has a unique ID and name. Manufacturers produce items (identified by their unique item numbers) in the amounts indicated. Manufacturers may produce more than one item and different Manufacturers may produce the same items. MANUFACTURER www.assignmentpoint.com

  28. Normalization Data Anomalies in 3NF Relations This MANUFACTURER relation has two candidate keys: (ID, Item-No) and (Name, Item-No) that overlap on the attribute Item-No. The relation is in 3NF because there is only one nonprime attribute and therefore it is impossible that this attribute can determine another nonprime attribute. The relation MANUFACTURER is susceptible to update anomalies. Consider for example the case in which one of the manufacturers changes its name. If the value of this attribute is not changed in all of the corresponding tuples there is not possibility of having an inconsistent database. www.assignmentpoint.com

  29. Normalization Boyce-Codd Normal Form A relation r(R) is in Boyce-Codd Normal Form (BCNF) if and only of the following conditions are met simultaneously: • The relation is in 3NF. • For every functional dependency of the form X → A, we have that either A X or X is a super key of r. In other words, every functional dependency is either a trivial dependency or in the case that the functional dependency is not trivial, then X must be a super key. To transform the relation of example into BCNF, we can decompose it onto the following set of relational schemes: Set No. 1 • MANUFACTURER (ID, Name) • MANUFACTURER-PART (ID, Item-No, Quantity) OR, Set No. 2 • MANUFACTURER (ID, Name) • MANUFACTURER-PART (Name, Item-No, Quantity) www.assignmentpoint.com

  30. Normalization Lossless or Lossy Decomposition ? Whenever a relation is decomposed it is necessary to ensure that the data in the original relation is represented faithfully. That is, we need to make sure that we can recover the original relation from the new relations that have replaced it. Generally, the original relation is recovered by forming the natural join of the new relations. If we can recover the original relation we say that the decomposition is lossless. Otherwise, it is lossy. www.assignmentpoint.com

  31. Normalization Decompose R1 Join R2 Lossless or Lossy Decomposition ? Example: Consider the relation r shown below and its decomposition R1 and R2. Is this decomposition lossless or lossy? r R1 R2 www.assignmentpoint.com

  32. Normalization Lossless or Lossy Decomposition ? Example: Consider the relation r shown below and its decomposition R1 and R2. Is this decomposition lossless or lossy? Notice that the natural join of R1 and R2 has tuples that were not present in the original relation. These tuples are called spurious tuples because they represent spurious or false information. Since the natural join of R1 and R2 does not recover the original relation, the decomposition is lossy. www.assignmentpoint.com

More Related