Chapter 6 relational database design
This presentation is the property of its rightful owner.
Sponsored Links
1 / 77

Chapter 6 Relational Database Design PowerPoint PPT Presentation


  • 137 Views
  • Uploaded on
  • Presentation posted in: General

Chapter 6 Relational Database Design. 6.1 Pitfalls in Relational-Database Design 6.2 Decomposition 6.3 Normalization Using Functional Dependence 6.4 BCNF decomposition 6.5 3NF decomposition 6.6 Fourth Normal Form 6.7 Exercise. Chapter 6 Relational Database Design.

Download Presentation

Chapter 6 Relational Database Design

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


Chapter 6 relational database design

Chapter 6 Relational Database Design

6.1 Pitfalls in Relational-Database Design

6.2 Decomposition

6.3 Normalization Using Functional Dependence

6.4 BCNF decomposition

6.5 3NF decomposition

6.6 Fourth Normal Form

6.7 Exercise


Chapter 6 relational database design1

Chapter 6 Relational Database Design

In general, the goal of a relational-database design is to generate a set of relation schemas that allows us to store information withoutunnecessary redundancy, yet also allows us to retrieve information easily. One approach is to design schemas that are in an appropriate normal form.


6 1 pitfalls in relational database design

6.1 Pitfalls in Relational-Database Design

Among the undesirable properties that a bad design may have are.

① Repetition of information

② Inability to represent certain information

Example:

The information concerning loans is now kept in one single relation, lending, which is defined over the relation schema.

Lending-schema=(branch-name, branch-city, assets, customer-name, loan-number, amount)


6 1 pitfalls in relational database design1

6.1 Pitfalls in Relational-Database Design

Lending Figure A


6 1 pitfalls in relational database design2

6.1 Pitfalls in Relational-Database Design

Problems:

① add a new loan

The loan is made by the Perryridge branch to Adams in the amount of $1500. Let the loan-number be L-31.

We add the tuple

(Perryridge, Horseneck, 1700000,Adams, L-31, 1500)

ⅰRepeating information wastes space.

ⅱRepeating information complicates updating the database.


6 1 pitfalls in relational database design3

6.1 Pitfalls in Relational-Database Design

② we cannot represent directly the information concerning a branch.(branch-name, branch-city,assets) unless there exists at least one loan at the branch. The problem is that tuples in the lending relation require values for loan-number, amount and customer-name.

Solution:

introduce null values to handle updates through views.(difficult)


6 2 decomposition

6.2 Decomposition

A bad design suggests that we should decompose a relation schema that has many attributes into several schemas with fever attributes. Careless decomposition, however, many lead to another form of bad design.


6 2 decomposition1

6.2 Decomposition

Example:

Consider an alternative design in which Lending-schema is decomposed into the following two schemas:

Branch-customer-schema=(branch-name, branch-city, assets,customer-name)

Customer-loan-schema=(customer-name,loan-number, amount)


6 2 decomposition2

6.2 Decomposition

Branch-customer B

Customer-loan C


6 2 decomposition3

6.2 Decomposition

Suppose that we wish to find all branches that have loans with amount less than $1000. We should write.

Figure D


6 2 decomposition4

∏branch-name(amount<1000(branch-customer customer-loan))

6.2 Decomposition

Problems:

Additional tuples:

(Downtown, Brooklyn, 9000000,Jones, L-93, 500)

(Mianus, Horsereck, 400000,Jones, L-17, 1000)

Consider the query, “Find all branches that have made a loan in an amount less than $1000”.

Figure A Mianus Round Hill

Figure D Mianus Round HillDowntown


6 2 decomposition5

If a customer happens to have several loans from different branches, we cannot tell which loan belongs to which branch.

Although we have more tuples in branch-customer customer-loan, we actually have less information. We are no longer able, in general, to represent in the database information about which customers are borrowers form which branch.

Loss information:

6.2 Decomposition


6 2 decomposition6

6.2 Decomposition

Lossy decomposition: Because of this loss of information, we call the decomposition of Lending-schema into Branch-customer-schema and customer-loan-schema a lossy decomposition, or a lossy-join decomposition.


6 2 decomposition7

r=∏R1(r) ∏R2(r) …… ∏Rn(r)

6.2 Decomposition

Lossless-join decomposition: A decomposition that is not a lossy-join decomposition is a lossless-join decomposition.

Let C represent a set of constrains on the database. A decomposition {R1,R2……Rn} of a relation schema R is a lossless-join decomposition for R if for all relation r on schema R that are legal under C.


6 3 normalization using functional dependence

6.3 Normalization Using Functional Dependence

Lossless-join Decomposition:

We must first present a criterion for determining whether a decomposition is lossy.

Let R be a relation schema, and let F be a set of functional dependencies on R. This decomposition is a lossless-join decomposition of R if at least one of the following functional dependencies are in F+.

① R1∩R2→R1

② R1∩R2→R2


6 3 normalization using functional dependence1

6.3 Normalization Using Functional Dependence

Example:

We now show that our decomposition of Lending-schema is a lossless-join decomposition by showing a sequence of steps that generate the decomposition

① Branch-schema=(branch-name,branch-city, assets)

Loan-info-schema=(branch-name,customer-name,loan-number, amount)

Branch-schema∩Loan-info-schema={branch-name}

branch-name→{branch-name,branch-city,assets}


6 3 normalization using functional dependence2

6.3 Normalization Using Functional Dependence

② Loan-info-schema

Loan-schema=(branch-name,loan-number, amount)

Borrower-schema=(customer-name, loan-number)

Loan-schema∩Borrower-schema={loan-number}

loan-number→{loan-number,branch-name,amount}

This step results is a lossless-join decomposition.


6 3 normalization using functional dependence3

6.3 Normalization Using Functional Dependence

F={A→B,C→F,E→A,CE→D}

Example:

Suppose that we decompose the schema R=(A,B,C,D,E,F) into R1={CF,BE,ECD,AB}; R2={ABE,CDEF}. Determine which decomposition is a lossless-join decomposition if the following set F of functional dependencies holds.

{ABE }∩{ CDEF}= {E}

E→A A→B E→B

E→A E→B E→AB E→ABE

R2 is a lossless-join decomposition


6 3 normalization using functional dependence4

6.3 Normalization Using Functional Dependence

Another Algorithm:

F={A→B,C→F,E→A,CE→D}

a2

a6

b21

Lossy-join decomposition


6 3 normalization using functional dependence5

6.3 Normalization Using Functional Dependence

F={A→B,C→F,E→A,CE→D}

a1

a2

Lossless-join decomposition


6 3 normalization using functional dependence6

6.3 Normalization Using Functional Dependence

Dependency Preservation:

There is another goal in relational-database design: dependency preservation.

Let F be a set of functional dependencies on a schema R, and let R1,R2……Rn be a decomposition of R. The restriction of F to Ri is the set Fi of all functional dependencies in F+ that include only attributes of Ri.


6 3 normalization using functional dependence7

6.3 Normalization Using Functional Dependence

Let F’=F1∪ F2∪…… ∪ Fn. F’ is a set of functional dependencies on schema R. if F’+= F+ is true, then every dependency in F is logically implied by F’, and, if we verify that F’ is satisfied, we have verified that F is satisfied. We say that a decomposition having the property F’+= F+ is a dependency-preserving decomposition.


6 3 normalization using functional dependence8

6.3 Normalization Using Functional Dependence

Normal Form:

The process of further normalization—here in after abbreviated to just normalization—is built around the concept of normal forms. A relvar is said to be in a particular normal form if it satisfies a certain prescribed set of conditions.


6 3 normalization using functional dependence9

City

Customer-name

Customer-city

Customer-street

Jones

Brooklyn

Ray

Hayes

Palo Alto

Heroes

6.3 Normalization Using Functional Dependence

① First normal form: A relvar is in 1NF if and only if, in every legal value of that relvar, every tuple contains exactly one value for each attribute.


6 3 normalization using functional dependence10

6.3 Normalization Using Functional Dependence

②Second normal form (definition assuming only one candidate key, which we assume is the primary key): A relvar is in 2NFif and only if it is in 1NF and every nonkey attribute is irreducibly dependent on the primary key.

Example:

Relation R=(BNO,Title, Author, Price,Lo-No, Card, Name, Dept,Date)

F={BNO→Title, Author, Price, Lo-No

Card →Name, Dept

BNO,Card →Date}


6 3 normalization using functional dependence11

6.3 Normalization Using Functional Dependence

Candidate key {BNO,Card}

{BNO,Card} →Title

BNO →Title∈F

So, it not in 2NF

Redundancy


6 3 normalization using functional dependence12

6.3 Normalization Using Functional Dependence

③Third normal form(definition assuming only one candidate key, which we further assume is the primary key): A relvar is in 3NFif and only if it is in 2NF and every nonkey attribute is nontransitively dependent on the primary key. Note: “No transitive dependences” implies no mutual dependencies.


6 3 normalization using functional dependence13

SNO →CLASS

CLASS →MASTER

6.3 Normalization Using Functional Dependence

Example:

Relvar student=(SNO, SNAME, SAGE,MASTER,CLASS, SEX)

F={SNO→SNAME, SNO →SAGE, SNO →SEX,SNO →CLASS,CLASS →MASTER}

Candidate key {SNO}

MASTER transitively dependent on SNO.


6 3 normalization using functional dependence14

6.3 Normalization Using Functional Dependence

④ Boyce/codd normal form(BCNF): A relvar is in BCNF if and only if every nontrivial, left-irreducible FD has a candidate key as its determinant.

A relation schema R is in BCNF with respect to a set F of functional dependencies if for all functional dependencies in F+ of the form →, where R and R, at least one of the following holds:

ⅰ → is a trivial functional dependency (that is  )

ⅱ  is a superkey for schema R


6 3 normalization using functional dependence15

6.3 Normalization Using Functional Dependence

Examples:

①customer-schema=(customer-name,customer- street, customer-city)

customer-name→{customer-street,customer-city}

customer-name is a candidate key

customer-schema is in BCNF

② branch-schema=(branch-name,assets,branch-city)

branch-name→ {assets,branch-city}

branch-schema is a candidate key

branch-schema is in BCNF


6 3 normalization using functional dependence16

6.3 Normalization Using Functional Dependence

③loan-info-schema=(branch-name, customer-name,loan-number, amount)

loan-number→{amount,branch-name}

loan-number is not a candidate key

(Downtown,John Bell,L-44,1000)

(Downtown,Jane Bell,L-44,1000)

loan-number → {amount,branch-name}is not a trivial dependency.

loan-info-schema is not in BCNF


6 4 bcnf decomposition

6.4 BCNF decomposition

loan-info-schema=(branch-name, customer-name,loan-number, amount)

loan-info-schema is not in BCNF. Redundancy exist, so we should decompose it.

loan-schema=(loan-number, branch-name, amount)

borrower-schema=(customer-name,loan-number)

loan-number→{amount,branch-name}

loan-number is a candidate key of loan-schema

loan-schema and borrower-schema are both in BCNF


6 4 bcnf decomposition1

6.4 BCNF decomposition

If R is not in BCNF, we can decompose R into a collection of BCNF schemas R1,R2……Rn. Which not only a BCNF decomposition, but also a lossless-join decomposition.


6 4 bcnf decomposition2

6.4 BCNF decomposition

Algorithm:

result:={R};

done:=false;

compute F+;

while (not done) do

if (there is a schema Ri in result that is not in BCNF)

then begin

let   be a nontrivial functional dependencythat

holds on Ri such that  Ri is not in F+ ,and ∩ =;

result:=(result-Ri) ∪(Ri- )∪( ,);

end

else done:=true;


6 4 bcnf decomposition3

branch-name→{branch-city,assets}

F=

loan-number→{amount,branch-name}

6.4 BCNF decomposition

Example:

Lending-schema={branch-name,branch-city, assets,customer-name,loan-number,amount}

A candidate key for this schema is:

{loan-number,customer-name}

BCNF decomposition :

branch-name is not a superkey. Thus ,Lending-schema is not in BCNF, so decompose it.


6 4 bcnf decomposition4

6.4 BCNF decomposition

branch-name→{branch-city,assets}

{branch-name,branch-city, assets,customer-name,loan-number,amount}{branch-name}+

① branch-name→ {branch-name,branch-city, assets,customer-name,loan-number,amount} is not in F+

②branch-name∩{branch-city,assets}= 

R1=branch-name∪{branch-cityassets}

R2={branch-name,branch-city, assets,customer-name,loan-number,amount}-{branch-city,assets}


6 4 bcnf decomposition5

loan-number→{amount,branch-name}

F’=

6.4 BCNF decomposition

Branch-schema={branch-name,branch-city, assets}

Loan-info-schema={branch-name,customer-name,loan-number,amount}

branch-name is a key for Branch-schema, Thus ,Branch-schema is in BCNF.

loan-number is not a key for Loan-info-schema. Thus,Loan-info-schema is not in BCNF

Decompose Loan-info-schema


6 4 bcnf decomposition6

6.4 BCNF decomposition

R3= {branch-name,customer-name,loan-number,amount}-{amount,branch-name}

loan-number→{amount,branch-name}

{branch-name,customer-name,loan-number,amount}{loan-number}+

① branch-name→{branch-name,customer-name,loan-number,amount} is not in F’+

②loan-number∩{amount,branch-name}= 

R2= loan-number ∪{amount,branch-name}


6 4 bcnf decomposition7

6.4 BCNF decomposition

Loan-schema={branch-name,loan-number,amount}

Borrower-schema={customer-name,loan-number}

Branch-schema and Borrower-schema are in BCNF.

Lending-schema=(branch-name,branch-city, assets,customer-name,loan-number,amount)

Branch-schema={branch-name,branch-city, assets}

Loan-schema={branch-name,loan-number,amount}

Borrower-schema={customer-name,loan-number}


6 4 bcnf decomposition8

6.4 BCNF decomposition

Algorithm:

Input: a schema R and R satisfied the FDs F.

Output: R is a lossless-join decomposition which satisfies the FDs F and for each subschemas Ri is a BCNF decomposition which satisfies Fi=∏Ri(F)


6 4 bcnf decomposition9

6.4 BCNF decomposition

① initialize the result closure  to {R}, ={R}

②if all subschemas in  are BCNF then turn to ④. Let us find a subschemas S in  that is not in BCNF. According to the definition of BCNF, there must has a FD X→A and A-X=, X do not include any candidate key of S, S-X-A≠ .

③ Let S={S1,S2}, S1 is XA, S2 is S-A∪(A∩X), replace the S with {S1,S2} turn to ②

④ Stop decomposing and output 


6 4 bcnf decomposition10

6.4 BCNF decomposition

Compute candidate key:

If x+=u and y+is a proper subset of x+ (y+ ≠u). then the attributes in R can be classified into four groups:

L: the attributes appear only on the left-hand side of FD

R: the attributes appear only on the right-hand side of FD

LR: the attributes appear both on the left-hand side and left-hand side of FD

N: the attributes do not appear on the FD


6 4 bcnf decomposition11

6.4 BCNF decomposition

③ L+N unknown:

① the classification of R arenot candidate key

② the classification of(L+N) must appear in candidate key

ⅰ L+N =x1 x1+=u then haveonly one candidate key

ⅱ L+N =x1 x1+≠u find an attribute in R and add it to the x1.repeat!


6 4 bcnf decomposition12

6.4 BCNF decomposition

Example:

Relvar R=(O,I,S,Q,B,D) satisfies the following FDs.

F={S→D, I →B, IS →Q, B →O}

Give a lossless-join decomposition into BCNF of the schema R

L: S, I R: D, Q, O LR: B

(SI)+=SIDBQO=Uhave only one candidate key.

S→D ISS not in BCNF

S→D D-S≠  S do not include IS

Decompose: R1=SD R2=OISQB


6 4 bcnf decomposition13

6.4 BCNF decomposition

FR2={I→B, IS →Q, B →O}

L: S, I R: Q, O LR: B

(SI)+=SIBOQ=Uhave only one candidate key.

I→B ISI B-I≠  not in BCNF

Decompose:R21=IB R22=OISQ

FR21={I→B}

FR22={IS →Q}

=(R1, R21, R22)


6 4 bcnf decomposition14

6.4 BCNF decomposition

Example:

Relvar R=(A,B,C,D) satisfies the following FDs.

F={A→D, C→D, D →B}

Give a lossless-join decomposition into BCNF of the schema R

L: A,C R: B LR: D

(AC)+=ACDB=Uhave only one candidate key.

Decompose: R1=AD R2=ACB

FR2={C→B, A →B}

……


6 4 bcnf decomposition15

banker-name→{branch-name}

F=

{branch-name,customer-name}→banker-name

6.4 BCNF decomposition

Not every BCNF decomposition is dependency preserving.

Example:

Banker-schema={branch-name,customer-name,banker-name}

banker-name is not a key for Banker-schema.

Thus, Banker-schema is not in BCNF. Decompose it.


6 4 bcnf decomposition16

6.4 BCNF decomposition

Banker-branch-schema={banker-name,branch-name}

Customer-banker-schema={customer-name,banker-name}

F1={banker-name→branch-name}

F2= 

{branch-name,customer-name}→banker-name in F+

{branch-name,customer-name}→banker-name not in {F1∪F2}+

{F1∪F2}+<>F+ and the decomposition is not dependency preserving


6 4 bcnf decomposition17

6.4 BCNF decomposition

③ Dependency preservation

This example demonstrates that not every BCNF decomposition is dependency preserving. Moreover, it demonstrates that we cannot always satisfy all three design goals:

① BCNF

② Lossless join


6 5 3nf decomposition

6.5 3NF decomposition

A relation schema R is in 3NF with respect to a set F of functional dependencies if, for all functional dependencies in F+ of the form →, where R and R, at least one of the following holds:

①→ is a trivial functional dependency (that is  )

② is a superkey for schema R

③ each attribute A in - is contained in a candidate key for R.


6 5 3nf decomposition1

banker-name→ {branch-name}

F=

{branch-name,customer-name}→banker-name

6.5 3NF decomposition

Example:

Banker-schema={branch-name,customer-name,banker-name}

banker-name is not a key for Banker-schema.

Thus, Banker-schema is not in BCNF.

{branch-name,customer-name} is a candidate key for Banker-schema. Banker-schema is in 3NF.


6 5 3nf decomposition2

6.5 3NF decomposition

If R is not in 3NF, we can decompose R into a collection of 3NF schemas R1,R2……Rn, which is not only a lossless-join decomposition, but also a dependency-preserving decomposition.


6 5 3nf decomposition3

6.5 3NF decomposition

Algorithm:

let Fc be a canonical cover for F’;

i:=0;

for each functional dependency  in Fc do

if none of the schemas Rj, j=1,2……,i contains  

then begin

i:=i+1;

Ri:=  ;

end

If none of the schemas Rj, j=1,2……,i contains a candidate key for R

then begin

i:=i+1;

Ri:= any candidate key for R;

end

Return(R1,R2,……,Ri)


6 5 3nf decomposition4

6.5 3NF decomposition

Example:

Relvar R=(O,I,S,Q,B,D) satisfies the following FDs.

F={S→D, I→B, IS →Q, B →O}

Give a lossless-join, dependency-preserving decomposition into 3NF of the schema R.

F=FC

L: S,I R: D,Q,O LR: B

(IS)+=ISDBQO=Uhave only one candidate key.

={SD,IB,ISQ,BO}


6 5 3nf decomposition5

6.5 3NF decomposition

Example:

Banker-info-schema={branch-name,customer-name,banker-name,office-number}

Banker-name→{branch-name,office-number}

{customer-name,branch-name}→banker-name

3NF decomposition:

R1= banker-name∪{branch-name,office-number}

R2= {customer-name,branch-name}∪banker-name


6 5 3nf decomposition6

6.5 3NF decomposition

Banker-office-schema={banker-name,branch-name,office-number}

Banker-schema={customer-name,branch-name,banker-name}

banker-schema contains a candidate key for banker-info-schema

Banker-info-schema={branch-name,customer-name,banker-name,office-number}

Banker-office-schema={banker-name,branch-name,office-number}

Banker-schema={customer-name,branch-name,banker-name}


6 5 comparison of bcnf and 3nf

6.5 comparison of BCNF and 3NF

Advantage of 3NF:

It is always possible to obtain a 3NF design without sacrificing a lossless join or dependency preservation.

Disadvantage of 3NF:

If we do not eliminate all transitive dependencies, we may have to use null values to represent some of the possible meaningful relationships among data items, and there is the problem of repetition of information.

If a schema is in 3NF but not in BCNF,then redundancy will occur.


6 6 fourth normal form

6.6 Fourth Normal Form

Consider banking example:

BC-schema={loan-number,customer-name,customer-street,customer-city}

customer-name→{customer-street,customer-city}

customer-name is not a key for BC-schema, so BC-schema is not a BCNF schema.

Remove this functional dependency, BC-schema is a BCNF schema. But repetition of information are still exist.


6 6 fourth normal form1

6.6 Fourth Normal Form

Solution:

multivalued dependencies

As we did for functional dependencies, we shall use multivalued dependencies to define a normal form for relation schemas. This normal form, called fourth normal form(4NF), is more restrictive than BCNF.

4NF schema is in BCNF, BCNF schema is not in 4NF


6 6 1 multivalued dependencies

6.6.1 Multivalued Dependencies

Functional dependencies rule out certain tuples from being in a relation. If A→B, then we cannot have two tuples with the same A value but different B values. Multivalued dependencies do not rule out the existence of certain tuples. Instead, they require that other tuples of a certain form be present in the relation. For this , functional dependencies sometimes are referred to as equality-generating dependencies, and multivalued dependencies are referred to as tuple-generating dependencies.


6 6 1 multivalued dependencies1

6.6.1 Multivalued Dependencies

Definition:

Let R be a relation schema and let Rand R. then multivalued dependency

→→

holds on R if , in any legal relation r( R ), for all pairs of tuples t1 and t2 in r such that t1[]=t2[], there exist tuples t3 and t4 in r such that

t1[]=t2[]=t3[]=t4[]

t3[]=t1[]

t3[R--]=t2[R--]

t4[]=t2[]

t4[R--]=t1[R--]


6 6 1 multivalued dependencies2

  R--

t1 1… I i+1… j j+1… n

t2 1… I bi+1… bj bj+1… bn

t3 1… I i+1… j bj+1… bn

t4 1… I bi+1… bj j+1… n

6.6.1 Multivalued Dependencies

Intuitively, the multivalued dependency →→ says that the relationship between  and  is independent of the relationship between  and R-. If the multivalued dependency →→ is satisfied by all relations on schema R, then →→is a trivial multivalued dependency on schema R. thus,→→is trivial if   or ∪=R.


6 6 1 multivalued dependencies3

C T H R S G

time classroom

C1 T1 H1 R1S1 G1

C1 T1 H2 R2S1 G1

C1 T1 H1 R1S2 G2

C1 T1 H2 R2S2 G2

6.6.1 Multivalued Dependencies

Example:

Relvar R-schema={C,T,H,R,S,G}

C: course name T: teacher H: time R: classroom S: student G: grade

C→→HR

T→→HR


6 6 2 theory of multivalued dependencies

6.6.2 Theory of Multivalued Dependencies

Let D denote a set of functional and multivalued dependencies. The closure D+ of D is the set of all functional and multivalued dependencies logically implied by D.

The following list of inference rules for functional and multivalued dependencies is sound and complete.

1) Reflexivity rule: if  is a set of attributes and , then    holds.

2) Augmentation rule: if    holds and  is a set of attributes, then     holds.


6 6 2 theory of multivalued dependencies1

6.6.2 Theory of Multivalued Dependencies

3) Transitivity rule: if  holds and  holds, then  holds.

4) Complementation: if  holds, then R-- holds.

5) Multivalued augmentation rule: if  holds and  R and    then   holds.

6) Multivalued Transitivity rule: if  holds and   then  - holds.

7) Replication rule: if  holds then holds.


6 6 2 theory of multivalued dependencies2

6.6.2 Theory of Multivalued Dependencies

8) Coalescence rule: if  holds and  , and there is asuch thatR, and∩= , and  then   holds.

Multivalued union rule: if  holds and  then  holds.

Intersection rule: if  holds and  then  ∩ holds.

Difference rule: if  holds and  then - holds and - holds.


6 6 2 theory of multivalued dependencies3

6.6.2 Theory of Multivalued Dependencies

Example:

Let R={A,B,C,G,H,I} with the following set of dependencies D given:

AB BHI CGH

R+

ACGHI: AB AR-B-A (CGHI) ACGHI(complementation rule)

AHI: AB BHI AHI-B (HI) AHI(multivalued transitivity rule)


6 6 2 theory of multivalued dependencies4

6.6.2 Theory of Multivalued Dependencies

BH: BHI HHIand CG∩HI= BH BH(coalescence rule)

ACG: ABCGHI AHI ACGHI-HI (CG) ACG(difference rule)


6 6 3 fourth normal form

6.6.3 Fourth Normal Form

Definition:

A relation schema R is in 4NF with respect to a set D of functional and multivalued dependencies if, for all multivalued dependencies in D+ of the form , where R and R , at least one of the following holds

 is a trivial multivalued dependency

 is a superkey for schema R

a database design is in 4NF if each member of the set of relation schemas that constitutes the design is in 4NF


6 6 3 4nf decomposition

6.6.3 4NF decomposition

Algorithm:

result:={R};

done:=false;

compute D+;

while (not done) do

if (there is a schema Ri in result that is not in 4NF)

then begin

let  be a nontrivial functional dependencythat

holds on Risuch thatRiis not inD+and∩= ;

result:=(result-Ri) ∪(Ri- )∪( ,);

end

else done:=true;


6 6 3 4nf decomposition1

6.6.3 4NF decomposition

Example:

BC-schema={loan-number,customer-name,customer-street,customer-city}

customer-nameloan-number

customer-name is not a superkey for BC-schema

customer-name{loan-number,customer-name,customer-street,customer-city}not exist in D+

customer-name∩loan-number=


6 6 3 4nf decomposition2

6.6.3 4NF decomposition

R1=customer-name∪loan-number

R2={loan-number,customer-name,customer-street,customer-city}-loan-number

Borrower-schema={customer-name,loan-number}

Customer-schema={customer-name,customer-street,customer-city}


6 6 3 4nf decomposition3

6.6.3 4NF decomposition

This algorithm generates only lossless-join decompositions:

let R be a relation schema, and let D be a set of functional and multivalued dependencies of R. Let R1 and R2 form a decomposition of R. This decomposition is a lossless-join decomposition of R if and only if at least one of the following multivalued dependencies is inD+:

R1∩R2 R1

R1∩R2 R2


6 6 3 4nf decomposition4

6.6.3 4NF decomposition

consider a set of D of both functional and multivalued dependencies. The restriction of D to Ri is the set Di, consisting of

All functional dependencies in D+ that include only attributes of Ri.

All multivalued dependencies of the form

  ∩Ri

Where Ri and    is in D+


6 6 3 4nf decomposition5

6.6.3 4NF decomposition

The 4NF decomposition can not ensure dependency preservation.

③Dependency preservation

If we are given a set of multivalued and functional dependencies, it is advantageous to find a database design that meets the three criteria of

① 4NF (all we have are functional dependencies, BCNF)

② Lossless join

When we can not achieve our three goals, we compromise on 4NF,and accept BCNF or even 3NF, if necessary, to ensure dependency preservation.


Exercise

Exercise:

① Suppose that we decompose the schema R=(U,V,W,X,Y,Z) into 1={WZ,VY,WXY,UVY}; 2={UVY,WXYZ} determine which decomposition is a lossless-join decomposition if the following set F of functional dependencies holds

F={U→V, W →Z, Y →U, WY →X}

② Suppose that we decompose the schema ={R1(A1A4), R2(A1A2), R3(A2A3), R4(A3A4A5),R5(A1A5)} determine whether this decomposition is a lossless-join decomposition. If the following set F of functional dependencies holds

F={A1→A3, A3→A4, A2→A3, A4A5→A3, A3A5→A1}


Exercise1

Exercise:

③ Relvar R=(A,B,C,D,E) satisfies the following FDs:

F={A→C,C →D,B →C,DE →C,CE →A}

ⅰFind an irreducible equivalent for this set of FDs

ⅱFind all candidate keys of the schema R.

ⅲDetermine whether the decomposition ={AD,AB,BC,CDE,AE} is a lossless-join decomposition or not.

ⅳGive a lossless-join decomposition into BCNF of the schema R.

ⅴGive a lossless-join, dependency-preserving decomposition into 3NF of the schema R.


  • Login