slide1
Download
Skip this Video
Download Presentation
4NF

Loading in 2 Seconds...

play fullscreen
1 / 16

4NF - PowerPoint PPT Presentation


  • 88 Views
  • Uploaded on

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,…)

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' 4NF' - flint


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide2

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)

problem
Problem

Assignment(servno, workcode, sin, state, datereached)

problem1
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.
tuples implied by independence
Tuples Implied by Independence

If we have the blue tuples:

Then the red tuples must also be in the relation.

slide6

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.
slide7

Example

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

Picture of MVD X ->->Y

XY others

equal

exchange

slide9

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.
slide10

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.
slide11

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.
slide12

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.
slide13

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.
slide14

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.
slide15

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.
slide16

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.