THIRD NORMAL FORM (3NF) - PowerPoint Presentation

Slide1

THIRD NORMAL FORM (3NF)

A relation R is in BCNF if whenever a FD X

A holds in R, one of the following statements is true:

XA is a trivial FD, or

X is a superkey, or

A is part of some keySlide2

Why 3NF?

A relation R is in

3NF if

whenever a FD X

A holds in R, one of the following statements is true:

XA is a trivial FD, orX is a superkey, orA is part of some key

By making an exception for certain dependencies involving some key attributes, we can ensure that every relation schema can be decomposed into a collection of 3NF with two desirable properties:

lossless-join

and

dependency-preserving

.Slide3

Cases of

3NF Violation

If X



Y

causes a violation of 3NF, there are two cases:X is a proper subset of some key (partial dependency)

Partial dependency causes data

redundancy:

Since XY and X is not a key, X could be redundant, so Y. X is not a proper subset of any key (transitive dependency)

KEY

Attributes X

Attribute

Y

KEY

Attributes X

Attribute

Y

KEY

Attributes X

Attribute

Y

Transitive dependency KXY makes it impossible to record the values of (K, X, Y) unless all of them are

known: Since

KXY, we cannot associate an X value with a K value unless we also associate an X value with Y valueSlide4

Dependency-Preserving Decomposition

Find all dependencies in

Fmin

that are not preserved

For each such dependency XA, create a relation schema XA and add it to the decomposition of R Every dependency in Fmin is now preserved

Inputs:

A relation R with a set

Fmin of FDs that is minimum coverD(R1, R2, …, Rn) is a lossless-join decomposition of R

Proof:

XA is in 3rd NF

X must be a key for XA, Since XA is in a minimal cover, YA does not hold for any Y that is a subset of X

For any other dependencies hold over XA, say YZ, in Fmin, it must satisfy 3

rd NF conditionsIf

Z is A, Y must be X

If Z is not A, Z must be part of XSlide5

A. Lossless-Join Decomposition

Set D{R}

While

there is a relation schema Q in D that is not in BCNF

do

beginChoose a relation schema Q in D that is not in BCNF;Find a functional dependency XY in Q that violates BCNF;Replace Q in D by two schemas (Q-Y) and (XUY)end;

Top-Down Approach:

Lossless-Join and Dependency Preserving Decomposition into 3NFB. Dependency-Preserving DecompositionAssume the decomposition is D(R1, R2, …,

Rn) and the FD sets are accordingly F1, F2, …, and Fn

(let their union be F’)For each dependency XA in the original F (

needs to be a minimum cover), check if it can be inferred from F’If not, create a relation schema XA and add it to the decomposition of RSlide6

Exercise

R(ABCDE)

F={ABCD

E,ED,AB,ACD}

Top-down approach

Loss-less join decomposition: R(ACBDE) is not in BCNF

3.

Dependency-preserving decomposition:

{ABCDE, ED, ABB, ACD}+ == {ACE, AB, ED}+ ??

/* Find a minimum cover first */

/* If XY is not preserved, add (XY) into the decomposition */

E

D

R1(

AC

BE)

R2(ED)

AB

R1(AC

E)

R2(AB)Slide7

Bottom-up Approach:

Lossless-Join and Dependency Preserving Decomposition into 3NF

Find a minimal cover G of F.

For each left-hand side X of a FD G, create a relation schema in D with attributes

where X

A1, XA2, …, XA

m

are the only dependencies in G with X as the left-hand side.

Prove that this relation is in 3rd NFIf none of the relation schemas in D contains a key of R, then create one more relation schema in D that contains attributes that form a key of R.

Prove that this decomposition is lossless-join

Inputs:

A relation R and a set of functional dependencies F on the attributes of R.Slide8

Exercise

R(ABCDE)

F={ABCD

E,ED,AB,ACD}

Bottom up approach (Synthesis):

Step 1: Find a minimum cover, G={ACE,ED,AB}Step 2: R1(

AC

E), R2(

ED), R3(AB)Step 3: Is this a lossless-join decomposition? Slide9

Functional

DependencyAmstrong’s

axioms

Attribute closure

(A+)

Dependency closure (F+)Minimum cover (Fmin)Normal FormsBCNF

3NF

Decomposition

Lossless joinDependency preservingNormalization Review

Conceptual design

Schemas

ICsSlide10

Determine Normal Forms

BCNFFor each X

A, is it a trivial dependency?

Is X a superkey?

3NF

Suppose XA violate BCNFIs A part of some key?Slide11

Exercise 1

For each of the following relation schemas and sets of FDs

R(ABCD) with FDs AB

C, CD, and DA

R(ABCD) with FDs BC and BD.

R(ABCD) with FDs ABC, BCD, CDA, and ADBCheck if they are in BCNF or 3NF, if not, perform a lossless join and dependency preserving decomposition

BCNF

For each X

A, is it a trivial dependency? Is X a superkey

?

3NF

Suppose XA violate BCNF

Is A part of some key?Slide12

Exercise 2

Prove that, if R is in 3NF and every key is simple (i.e, a single attribute), then R is in BCNFProve that, if R has only one key, it is in BCNF if and only if it is in 3NF. Slide13

Quiz

For each of the following relation schemas Indicate the strongest normal form of each of the following relations

R1(ABCDE) F1={A

B, CD, ACEABCDE}

R2(ABCEF) F2={ABC, BF, FE}

Consider a relation R with five attributes: ABCDE. F={AB, BCE, EDA}Are {ECD}, {ACD}, {BCD} keys for R?Is R in BCNF? Why?Is R in 3NF? Why?Slide14

Data Model Mapping

Conceptual Schema (

ER diagram

)

DBMS independent

DBMS specific

Conceptual Schema (

Relations

)

BCNF/3NF?

Decomposition

Lossless join

Dependency preservation

Normalization