4NF

1 / 16

# 4NF - PowerPoint PPT Presentation

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

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

## PowerPoint Slideshow about ' 4NF' - flint

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

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

States(state)

Dates(datereached)

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

Problem

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

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

If we have the blue tuples:

Then the red tuples must also be in the relation.

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.

Example

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

Picture of MVD X ->->Y

XY others

equal

exchange

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.

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.

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.

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.

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.

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.

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.

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.