Loading in 5 sec....

Chapter 6 Relational Database DesignPowerPoint Presentation

Chapter 6 Relational Database Design

- 760 Views
- Uploaded on

Download Presentation
## PowerPoint Slideshow about 'Chapter 6 Relational Database Design' - eudora

**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

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

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

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 Design

Lending Figure A

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

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 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 Decomposition

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

Figure D

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

6.2 DecompositionProblems:

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

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 Decomposition6.2 Decomposition different branches, we cannot tell which loan belongs to which branch.

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.

r=∏ different branches, we cannot tell which loan belongs to which branch. R1(r) ∏R2(r) …… ∏Rn(r)

6.2 DecompositionLossless-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 different branches, we cannot tell which loan belongs to which branch.

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 Dependence different branches, we cannot tell which loan belongs to which branch.

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 Dependence different branches, we cannot tell which loan belongs to which branch.

② 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 Dependence different branches, we cannot tell which loan belongs to which branch.

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 Dependence different branches, we cannot tell which loan belongs to which branch.

Another Algorithm:

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

a2

a6

b21

Lossy-join decomposition

6.3 Normalization Using Functional Dependence different branches, we cannot tell which loan belongs to which branch.

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

a1

a2

Lossless-join decomposition

6.3 Normalization Using Functional Dependence different branches, we cannot tell which loan belongs to which branch.

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 Dependence different branches, we cannot tell which loan belongs to which branch.

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 Dependence different branches, we cannot tell which loan belongs to which branch.

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.

City different branches, we cannot tell which loan belongs to which branch.

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 Dependence different branches, we cannot tell which loan belongs to which branch.

②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 Dependence different branches, we cannot tell which loan belongs to which branch.

Candidate key {BNO,Card}

{BNO,Card} →Title

BNO →Title∈F

So, it not in 2NF

Redundancy

6.3 Normalization Using Functional Dependence different branches, we cannot tell which loan belongs to which branch.

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

SNO →CLASS different branches, we cannot tell which loan belongs to which branch.

CLASS →MASTER

6.3 Normalization Using Functional DependenceExample:

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 Dependence different branches, we cannot tell which loan belongs to which branch.

④ 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 Dependence different branches, we cannot tell which loan belongs to which branch.

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 Dependence different branches, we cannot tell which loan belongs to which branch.

③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 different branches, we cannot tell which loan belongs to which branch.

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 decomposition different branches, we cannot tell which loan belongs to which branch.

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 decomposition different branches, we cannot tell which loan belongs to which branch.

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;

branch-name different branches, we cannot tell which loan belongs to which branch. →{branch-city,assets}

F=

loan-number→{amount,branch-name}

6.4 BCNF decompositionExample:

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 decomposition different branches, we cannot tell which loan belongs to which branch.

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}

loan-number different branches, we cannot tell which loan belongs to which branch. →{amount,branch-name}

F’=

6.4 BCNF decompositionBranch-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 decomposition different branches, we cannot tell which loan belongs to which branch.

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 decomposition different branches, we cannot tell which loan belongs to which branch.

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 decomposition different branches, we cannot tell which loan belongs to which branch.

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 decomposition different branches, we cannot tell which loan belongs to which branch.

① 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 decomposition different branches, we cannot tell which loan belongs to which branch.

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 decomposition different branches, we cannot tell which loan belongs to which branch.

③ 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 decomposition different branches, we cannot tell which loan belongs to which branch.

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 ISS not in BCNF

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

Decompose: R1=SD R2=OISQB

6.4 BCNF decomposition different branches, we cannot tell which loan belongs to which branch.

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

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

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

I→B ISI B-I≠ not in BCNF

Decompose:R21=IB R22=OISQ

FR21={I→B}

FR22={IS →Q}

=(R1, R21, R22)

6.4 BCNF decomposition different branches, we cannot tell which loan belongs to which branch.

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}

……

banker-name different branches, we cannot tell which loan belongs to which branch. →{branch-name}

F=

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

6.4 BCNF decompositionNot 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 decomposition different branches, we cannot tell which loan belongs to which branch.

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 decomposition different branches, we cannot tell which loan belongs to which branch.

③ 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 different branches, we cannot tell which loan belongs to which branch.

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.

banker-name different branches, we cannot tell which loan belongs to which branch. → {branch-name}

F=

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

6.5 3NF decompositionExample:

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 decomposition different branches, we cannot tell which loan belongs to which branch.

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 decomposition different branches, we cannot tell which loan belongs to which branch.

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 decomposition different branches, we cannot tell which loan belongs to which branch.

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 decomposition different branches, we cannot tell which loan belongs to which branch.

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 decomposition different branches, we cannot tell which loan belongs to which branch.

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 different branches, we cannot tell which loan belongs to which branch.

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 different branches, we cannot tell which loan belongs to which branch.

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 Form different branches, we cannot tell which loan belongs to which branch.

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 different branches, we cannot tell which loan belongs to which branch.

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 Dependencies different branches, we cannot tell which loan belongs to which branch.

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--]

different branches, we cannot tell which loan belongs to which branch. 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 DependenciesIntuitively, 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.

C T H R S G different branches, we cannot tell which loan belongs to which branch.

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 DependenciesExample:

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 different branches, we cannot tell which loan belongs to which branch.

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 Dependencies different branches, we cannot tell which loan belongs to which branch.

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 Dependencies different branches, we cannot tell which loan belongs to which branch.

8) Coalescence rule: if holds and , and there is asuch thatR, 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 Dependencies different branches, we cannot tell which loan belongs to which branch.

Example:

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

AB BHI CGH

R+

ACGHI: AB AR-B-A (CGHI) ACGHI(complementation rule)

AHI: AB BHI AHI-B (HI) AHI(multivalued transitivity rule)

6.6.2 Theory of Multivalued Dependencies different branches, we cannot tell which loan belongs to which branch.

BH: BHI HHIand CG∩HI= BH BH(coalescence rule)

ACG: ABCGHI AHI ACGHI-HI (CG) ACG(difference rule)

6.6.3 Fourth Normal Form different branches, we cannot tell which loan belongs to which branch.

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 different branches, we cannot tell which loan belongs to which branch.

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 thatRiis not inD+and∩= ;

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

end

else done:=true;

6.6.3 4NF decomposition different branches, we cannot tell which loan belongs to which branch.

Example:

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

customer-nameloan-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 decomposition different branches, we cannot tell which loan belongs to which branch.

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 decomposition different branches, we cannot tell which loan belongs to which branch.

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 decomposition different branches, we cannot tell which loan belongs to which branch.

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 decomposition different branches, we cannot tell which loan belongs to which branch.

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: different branches, we cannot tell which loan belongs to which branch.

① 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}

Exercise: different branches, we cannot tell which loan belongs to which branch.

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

Download Presentation

Connecting to Server..