Schema Refinement and - PowerPoint Presentation

Slide1

Schema Refinement and Normal Forms

Given a design, how do we know it is good or not? What is the best design?Can a bad design be transformed into a good one?

Conceptual design

Schemas

ICsSlide2

Normalization

A relation is said to be in a particular normal form if it satisfies a certain set of constraints.If a relation is in a certain normal form (BCNF, 3NF etc.), we know what problems it has and what problems it does not have

Each normal eliminates or minimizes certain kinds of problems

Given a relation, the process of making it to be in certain normal form is called normalization

Typically this is done by breaking up the relation into a set of smaller relations that possess desirable properties.Slide3

Boyce-Codd Normal Form (BCNF)

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.X is a superkey.

A trivial FD

XY where Y

X

key

nonkey attr_1

nonkey attr_n

nonkey attr_2

…

Example 1:

Scheme:

Hourly_Emps

(

ssn

, name, lot, rating,

hrly_wages

,

hrs_worked

)

Constraints:

ssn

is the primary key and

Rating



hrly_wages

Example 2:

Schema: R(

A

, B, C, D)

Constraints: A is the primary key, B is a candidate key, is R a BCNF? Slide4

A BCNF relation does not allow redundancy

Every field of every tuple records a piece of information that cannot be inferred from the values in all other non-key fieldsBCNF is the most desirable formSlide5

If a relation is not in BCNF, can we make it BCNF?

NormalizationExampleRelation

R(SNLRWH) has FDs

SSNLRWH and R

WSecond FD causes violation of

BCNFconsequence: W values repeatedly associated with R values

We decompose SNLRWH into SNLRH and RWSlide6

A decomposition of a relation schema R

The replacement of the schema R by two or more relation schemas, each contains a subset of R and together include all attributes of R.A decomposition must ensure two properties:Lossless join

Dependency preservation

Normalization through DecompositionSlide7

Lossless Join Decompositions

Decomposition of R into X and Y is a lossless-join decomposition w.r.t. a set of FDs F if, for every instance r that satisfies F: (r) (r) = r

It is always true that r

(r) (r)In general, the other direction does not hold! If it does, the decomposition is lossless-join.

It is essential that all decompositions used to deal with redundancy be lossless!Slide8

A decomposition of D={R1,R2,…,Rm

} of Relation R has the lossless join property with respect to the set of FDs F on R if for every relation instance r(R) that satisfies F, the following holds:NATURAL_JOIN( ) =rSlide9

Lossless Join Decomposition: Property 1

Property 1: A decomposition D={R1,R2} of R has the lossless join property with respect to a set of FDs F of R if and only if either

is in F

+

oris in F+.

Case 1:

Case 2:

R1

A

B

R2

B C

A foreign key

R1

A B

R2

B

C

A foreign key

The common attribute must be

a super key for either R1 or R2.Slide10

Lossless Join Decomposition: Property 2

If (i) a decomposition D={R1,…,Rm} of R has the lossless join property with respect to a set of FDs F on R, and (ii) a decomposition Di={Q1,…,Q2} of Ri has the lossless join property with respect to the projection of F on R

i.

then the decomposition D’={R1,R2,…, R

i-1,Q1,…,Qn,Ri+1,…,Rm

} of R has the lossless join property with respect to F.

Decomposition D

R

R

1

R

2

R

3

… R

m

Decomposition D

3

R

3

Q

1

Q

2 Q3 … Qn

Decomposition D’

R

R

1

R

2

Q

1

Q

2

… Q

n

R

4

… R

mSlide11

Lossless Join Decomposition into BCNF relations

Algorithm:1 Set D{R}2While there is a relation schema Q in D that is not in BCNF dobeginChoose 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;

We have

Since X

Y is in F,

D={(Q-Y),(X Y)} has the lossless join property.Slide12

ExerciseDetermine whether D={R1,R2, R3} of R(S

,E,P,N,L,H) is a lossless-join decomposition.R1={S,E}R2={P,N,L}R3={S,P,H}

F={S

E, SPH, PNL}Slide13

R with a set of FDs F

projection

R

1

R2 Rn

F

R1

F

R2

F

Rn

The projection of F on R

i

(F

Ri

) is defined as:

D={R

1

,…,R

n

} of R is dependency preserving with respect to F if .

DEPENDENCY PRESERVATION

D={R

1

,…,R

n

} is a decomposition of R.

, meaning that

is equivalent to F.Slide14

We want to preserve the dependencies because each FD in F represents a constraint on the database.

We want each original FD to be represented by some individual relation Ri so we can check the constraint without joining two or more relations.Otherwise, each update would require to do join operationsExample: Contracts (C S J D P Q V)

Contracts(

contractid

, supplierid, projectid, deptid, partid, qty, value)

DEPENDENCY PRESERVATION

C

CSJDPQV

JPC

SDP

JS

Is it BCNF?Slide15

Example: Contracts (C S J D P Q V)

SD

P

C

SDJQV

J

S

C

JDQV

SD

P

J

S

DEPENDENCY PRESERVATION

C

CSJDPQV

JPC

SDP

JS

Loss-less join decomposition?Slide16

Example: Contracts (C S J D P Q V)

SD

P

C

SDJQV

J

S

C

JDQV

SD

P

J

S

Where JP

C is in the result of the decomposition?

To enforce JPC, we need to join the three relations for each update

DEPENDENCY PRESERVATION

C

CSJDPQV

JPC

SDP

JSSlide17

Example: Contracts (C S J D P Q V)

J

S

C

JDPQV

J->S

Is this decomposition lossless join and dependency preserving?

An alternative decomposition

C

CSJDPQV

JPC

SDP

JSSlide18

QuestionCan any relation be decomposed into BCNF while ensuring lossless join and dependency preservation? Slide19

In general, there may not be a dependency preserving decomposition into BCNF.

e.g., CSZ, CS  Z, Z  C what NF?Let’s consider a decomposition D={ZC,SZ

}.

what NF? Is lossless-join decomposition?Is CSZ preserved?

CS

+={C,S,Z}; SZ+= {S,Z,C}; ZC

+={Z,C}R1(ZC); F

R1

={ZC}

R2(SZ); F

R2

={SZZ, SZS}

= {ZC,SZZ,SZS}

+

Is CS

Z in

?