1 / 16

4NF

4NF. Project (fragment). Auth. PTypes. Employees. MWorks. HasType. Assignment. Dates. Planes. AppliedOn. MServices. States. PTypes (model, capacity,…) Planes (regno, model) Employees (sin,…) MWorks (workcode,…) Authorized (sin,model,workcode) MServices (servno,regno,…)

flint
Download Presentation

4NF

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. 4NF

  2. Project (fragment) Auth. PTypes Employees MWorks HasType Assignment Dates Planes AppliedOn MServices States PTypes(model, capacity,…) Planes(regno, model) Employees(sin,…) MWorks(workcode,…) Authorized(sin,model,workcode) MServices(servno,regno,…) States(state) Dates(datereached) Assignment(servno, workcode, sin, state, datereached)

  3. Problem Assignment(servno, workcode, sin, state, datereached)

  4. Problem Assignment(servno, workcode, sin, state, datereached) We might assert one FD: servno workcode state  datereached (functional dependency) • However, this FD doesn’t really help here! Problem • In this table, employee's sin numbers are independent of the states. • Thus, for any existing servno, workcode pair, say (s,w), the sin numbers of employees assigned to (s,w) appear with each of the states that (s,w) reaches in all the combinations.

  5. Tuples Implied by Independence If we have the blue tuples: Then the red tuples must also be in the relation.

  6. Definition of MVD • A multivalued dependency (MVD), denoted by • X ->->Y • is an assertion that: • If two tuples agree on all the attributes of X, then their Y-components may be swapped, and the result will be two tuples that are also in the relation.

  7. Example • Example servno, workcode, sin, state illustrated MVD • servno workcode ->-> sin • and the MVD • servno workcode ->-> state

  8. Picture of MVD X ->->Y XY others equal exchange

  9. FDs vs. MVDs • Every FD is an MVD. • If X ->Y, then swapping Y ’s between two tuples that agree on X doesn’t change the tuples. • Therefore, the “new” tuples are surely in the relation, and we know X ->->Y.

  10. Fourth Normal Form • The redundancy that comes from MVD’s is not removable by putting the database schema in BCNF. • There is a stronger normal form, called 4NF, that (intuitively) treats MVD’s as FD’s when it comes to decomposition.

  11. 4NF Definition • A relation R is in 4NF if whenever X ->->Y is a nontrivial MVD, then X is a superkey. • "Nontrivial" means that: • Y is not a subset of X, and • X and Y are not, together, all the attributes. • Note that the definition of “superkey” still depends on FD’s only.

  12. BCNF Versus 4NF • Remember that every FD X ->Y is also an MVD, X ->->Y. • Thus, if R is in 4NF, it is certainly in BCNF. • Because any BCNF violation is a 4NF violation. • But R could be in BCNF and not 4NF, because MVD’s are “invisible” to BCNF.

  13. Decomposition and 4NF • If X ->->Y is a 4NF violation for relation R, we can decompose R using the same technique as for BCNF. • XY is one of the decomposed relations. • The other relation has X and all the other attributes of R that are not in X or Y.

  14. Example • Assignment(servno, workcode, sin, state, datereached) • FD: • servno workcode state  datereached • MVDs: • servno, workcode ->-> sin • servno, workcode ->-> state • Key is {servno, workcode, sin, state} • All dependencies violate 4NF.

  15. Example, Continued • Decompose using • servno workcode state  datereached : Assignment1(servno,workcode,state,datereached) • In 4NF. Assignment2(servno,workcode,sin,state) • Not in 4NF. MVD’s servno, workcode ->-> sin • servno, workcode ->-> state apply. • No FD’s, so all four attributes form the key.

  16. Example: Decompose Assignment2 • Either MVD • servno, workcode ->-> sin • servno, workcode ->-> state • tells us to decompose to: • Assignment3(servno,workcode,sin) • Assignment4(servno,workcode,state) • The last one (Assignment4) is completely contained in Assignment1,and thus, not needed.

More Related