4TH NORMAL FORMREVIEW OF NFsREVIEW OF NF Cont…4th Normal Form4th Normal Form Cont…Slide 6Slide 7Note about FDs and MVDsFormal DefinitionsExample 1Example 1 Cont…Example 2Example 2 Cont…4th NF and Anomalies4th Normal Form and anomalies Cont…Slide 16Slide 17case 3: Assume the following relation with multi-value dependency: Employee (Eid:pk1, Languages:pk2, Skills:pk3) Eid --->> Languages Eid --->> Skills Languages and Skills are independent.Slide 19Slide 20Slide 21More anomaliesThe relation is projected to the following two non-loss projections which are in forth normal formEmplyee_Language(Eid:pk1, Skills:pk2)References44THTH NORMAL FORM NORMAL FORMBy: Karen McVayBy: Karen McVayREVIEW OF NFsREVIEW OF NFs1NF 1NF All values of the columns All values of the columns are atomic. That is, they contain are atomic. That is, they contain no repeating values. no repeating values. 2NF 2NF it is in 1NF and every non- it is in 1NF and every non-key column is fully dependent key column is fully dependent upon the primary key. upon the primary key.REVIEW OF NF Cont…REVIEW OF NF Cont…3NF 3NF it is already in 2NF and every non-key it is already in 2NF and every non-key column is non transitively dependent upon its column is non transitively dependent upon its primary key. In other words, all non-key primary key. In other words, all non-key attributes are functionally dependent only upon attributes are functionally dependent only upon the primary key. the primary key. BCNF BCNF A relation is in BCNF if every A relation is in BCNF if every determinant is a candidate key. This is an determinant is a candidate key. This is an improved form of third normal form. improved form of third normal form. Determinant: an attribute on which some other Determinant: an attribute on which some other attribute is fully functionally dependentattribute is fully functionally dependent4th Normal Form4th Normal FormA Boyce Codd normal form relation is A Boyce Codd normal form relation is in fourth normal form if in fourth normal form if (a)(a)there is no multi value dependency there is no multi value dependency in the relation or in the relation or (b)(b)there are multi value dependency there are multi value dependency but the attributes, which are multi but the attributes, which are multi value dependent on a specific value dependent on a specific attribute, are dependent between attribute, are dependent between themselves. themselves.44thth Normal Form Cont… Normal Form Cont…This is best discussed through mathematical This is best discussed through mathematical notation. notation. Assume the following relationAssume the following relationR(a:pk1, b:pk2, c:pk3)R(a:pk1, b:pk2, c:pk3)Recall that a relation is in BCNF if all its Recall that a relation is in BCNF if all its determinant are candidate keys, in other words determinant are candidate keys, in other words each determinant can be used as a primary key. each determinant can be used as a primary key. Because relation Because relation RR has only one determinant has only one determinant (a, (a, b, c)b, c), which is the composite primary key and , which is the composite primary key and since the primary is a candidate key therefore R since the primary is a candidate key therefore R is in BCNF.is in BCNF.44thth Normal Form Cont… Normal Form Cont…Now R may or may not be in fourth normal form. Now R may or may not be in fourth normal form. ++1. If R contains 1. If R contains no multi value dependencyno multi value dependency then R will be then R will be in Fourth normal form.in Fourth normal form.++2. Assume R has the following two-multi value dependencies:2. Assume R has the following two-multi value dependencies:++a --->> b a --->> b and and a --->> c a --->> c 33In this case R will be in the fourth normal form if In this case R will be in the fourth normal form if bb and and c c dependent on each otherdependent on each other.. However if b and However if b and c are independent of each other c are independent of each other then then R R is notis not in in fourth fourth normal formnormal form and the relation has to be and the relation has to be projected to following two non-loss projections. These non-projected to following two non-loss projections. These non-loss projections will be in fourth normal form.loss projections will be in fourth normal form.Many-to-many relationships Many-to-many relationships Fourth Normal Form applies to Fourth Normal Form applies to situations involving many-to-many situations involving many-to-many relationships. relationships. In relational databases, many-to-many In relational databases, many-to-many relationships are expressed through relationships are expressed through cross-reference tables.cross-reference tables.4th Normal Form 4th Normal Form Cont…Cont…Note about FDs and Note about FDs and MVDsMVDsEvery Functional Dependency is a MVDEvery Functional Dependency is a MVD(if (if AA11AA22…A…Ann B B11BB22…B…Bnn , then , then AA11AA22…A…Ann B B11BB22…B…Bnn ) )FDs rule out certain tuples (i.e. if FDs rule out certain tuples (i.e. if A A B B then two tuples will not have the same then two tuples will not have the same value for A and different values for B)value for A and different values for B)MVDs do not rule out tuples. They MVDs do not rule out tuples. They guarantee that certain tuples must exist. guarantee that certain tuples must exist.Formal DefinitionsFormal DefinitionsFourth Normal FormFourth Normal Form- if - if RR is valid BCNF and… is valid BCNF and…- given the “non-trivial” MVD: - given the “non-trivial” MVD: AA11AA22…A…Ann B B11BB22…B…Bnn {{AA11AA22…A…Ann} is a superkey} is a superkeyA MVD: A MVD: AA11AA22…A…Ann B B11BB22…B…Bnn for a Relation for a Relation RR is “ is “non-non-trivialtrivial” if:” if:1.1.none of the none of the BBs are among the s are among the AAss2.2.Not all of the attributes of Not all of the attributes of RR are among the are among the AAs and s and BBssA MVD is “A MVD is “trivialtrivial” if it contains all the variations ” if it contains all the variations of of AA11AA22…A…Ann xx B B11BB22…B…Bn.n.A relation cannot be decomposed any further A relation cannot be decomposed any further (under 4NF rules) if it has a trivial MVD(under 4NF rules) if it has a trivial MVDConsider a case of
View Full Document
Unlocking...