Chapter 7: Relational Database Design

(1)

& %

Chapter 7: Relational Database Design

• Pitfalls in Relational Database Design

• Decomposition

• Normalization Using Functional Dependencies

• Normalization Using Multivalued Dependencies

• Normalization Using Join Dependencies

• Domain-Key Normal Form

• Alternative Approaches to Database Design

Database Systems Concepts 7.1 Silberschatz, Korth and Sudarshan c 1997

' $

Pitfalls in Relational Database Design

• Relational database design requires that we find a “good”

collection of relation schemas. A bad design may lead to – Repetition of information.

– Inability to represent certain information.

• Design Goals:

– Avoid redundant data

– Ensure that relationships among attributes are represented – Facilitate the checking of updates for violation of database

integrity constraints

(2)

& %

Example

• Consider the relation schema:

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

• Redundancy:

– Data forbranch-name, branch-city, assetsare repeated for each loan that a branch makes

– Wastes space and complicates updating

• Null values

– Cannot store information about a branch if no loans exist – Can use null values, but they are difficult to handle

' $

Decomposition

• Decompose the relation schemaLending-schemainto:

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

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

• All attributes of an original schema (R) must appear in the decomposition (R₁, R₂):

R = R₁ _∪ R₂

• Lossless-join decomposition.

For all possible relationsr on schemaR r = ΠR1 (r) ¹ ΠR2 (r)

(3)

& %

Example of a Non Lossless-Join Decomposition

• Decomposition of R = (A, B)

R₁ = (A) R₂ = (B)

A B A B

α 1 α 1

α 2 β 2

β 1

r _Π_A(r) ΠB (r )

• ΠA (r) ¹ ΠB (r) A B α 1 α 2 β 1 β 2

' $

Goal — Devise a Theory for the Following:

• Decide whether a particular relation Ris in “good” form.

• In the case that a relationR is not in “good” form, decompose it into a set of relations{R1, R2, ..., Rn}such that

– each relation is in good form

– the decomposition is a lossless-join decomposition

• Our theory is based on:

– functional dependencies – multivalued dependencies

(4)

& %

Normalization Using Functional Dependencies

When we decompose a relation schemaR with a set of functional dependenciesF intoR₁ andR₂ we want:

• Lossless-join decomposition: At least one of the following dependencies is in F+:

– R₁ _∩ R₂ _→ R₁ – R₁ _∩ R₂ _→ R₂

• No redundancy: The relationsR₁ andR₂ preferably should be in either Boyce-Codd Normal Form or Third Normal Form.

• Dependency preservation: LetF_i be the set of dependencies in F⁺ that include only attributes inR_i. Test to see if:

– (F1 ∪ F2)⁺ = F⁺

Otherwise, checking updates for violation of functional dependencies is expensive.

' $

Example

• R = (A, B, C)

F = {A _→ B,B _→ C_}

• R₁ = (A, B), R₂ = (B, C) – Lossless-join decomposition:

R₁ _∩ R₂ = {B_}and B _→ BC – Dependency preserving

• R₁ = (A, B), R₂ = (A, C) – Lossless-join decomposition:

R₁ _∩ R₂ = {A_}and A _→ AB – Not dependency preserving

(cannot checkB _→ C without computingR₁ ¹ R₂)

(5)

& %

Boyce-Codd Normal Form

A relation schemaR is inBCNFwith respect to a setFof functional dependencies if for all functional dependencies inF⁺ of the form α → β, whereα ⊆Randβ ⊆R, at least one of the following holds:

• α → β is trivial (i.e.,β ⊆ α)

• αis a superkey forR

' $

Example

• R = (A, B, C) F = {A _→ B

B _→ C_} Key ={A_}

• R is not inBCNF

• Decomposition R₁ = (A, B), R₂ = (B, C) – R₁ andR₂inBCNF

– Lossless-join decomposition – Dependency preserving

(6)

& %

BCNF Decomposition Algorithm

result:={R_}; done:= false;

computeF⁺;

while (notdone) do

if (there is a schema R_i inresultthat is not inBCNF) then begin

letα → β be a nontrivial functional dependency that holds onR_i such thatα →Ri is not inF⁺, andα ∩ β = ∅;

result:= (result₋R_i)∪(R_i _{− β})∪(α,_β);

end

elsedone:= true;

Note: eachR_i is inBCNF, and decomposition is lossless-join.

' $

Example of BCNF Decomposition

• R= (branch-name, branch-city, assets,

customer-name, loan-number, amount) F = {branch-name _→ assets branch-city

loan-number _→ amount branch-name_} Key ={loan-number, customer-name_}

• Decomposition

– R₁ = (branch-name, branch-city, assets)

– R₂ = (branch-name, customer-name, loan-number, amount) – R₃ = (branch-name, loan-number, amount)

– R₄ = (customer-name, loan-number)

• Final decomposition

R₁,R₃, R₄

(7)

& %

BCNF and Dependency Preservation

It is not always possible to get aBCNFdecomposition that is dependency preserving

• R = (J, K, L) F = {JK _→ L

L _→ K_}

Two candidate keys =JK andJL

• R is not inBCNF

• Any decomposition ofR will fail to preserve JK _→ L

' $

Third Normal Form

• A relation schemaR is in third normal form (3NF) if for all:

α → β inF⁺ at least one of the following holds:

– α → βis trivial (i.e.,β ∈ α) – αis a superkey forR

– Each attributeAinβ − α is contained in a candidate key forR.

• If a relation is inBCNFit is in3NF(since inBCNFone of the first two conditions above must hold).

(8)

& %

3NF (Cont.)

• Example

– R = (J, K, L)

F = {JK _→ L,L _→ K_} – Two candidate keys: JK andJL – R is in3NF

JK _→ L JK is a superkey

L _→ K K is contained in a candidate key

• Algorithm to decompose a relation schemaR into a set of relation schemas{R1, R2, ..., Rn}such that:

– each relation schemaR_i is in3NF – lossless-join decomposition – dependency preserving

' $

3NF Decomposition Algorithm

LetF_c be a canonical cover forF; i:= 0;

for each functional dependency α → β inF_c do if none of the schemas R_j, 1 ≤ j _≤ i containsα β

then begin i:=i+ 1;

R_i :=α β; end

if none of the schemas R_j, 1 ≤ j _≤ i contains a candidate key for R

then begin

i := i + 1;

R_i := any candidate key forR; end

return (R₁, R₂, ..., R_i)

(9)

& %

Example

• Relation schema:

Banker-info-schema= (branch-name,customer-name, banker-name, office-number)

• The functional dependencies for this relation schema are:

banker-name_→branch-name office-number customer-name branch-name_→ banker-name

• The key is:

{customer-name,branch-name_}

' $

Applying 3NF to Banker − info − schema

• The for loop in the algorithm causes us to include the following schemas in our decomposition:

Banker-office-schema= (banker-name, branch-name, office-number)

Banker-schema= (customer-name,branch-name, banker-name)

• SinceBanker-schemacontains a candidate key for

Banker-info-schema, we are done with the decomposition process.

(10)

& %

Comparison of BCNF and 3NF

• It is always possible to decompose a relation into relations in 3NFand

– the decomposition is lossless – dependencies are preserved

• It is always possible to decompose a relation into relations in BCNFand

– the decomposition is lossless

– it may not be possible to preserve dependencies

' $

Comparison of BCNF and 3NF (Cont.)

• R = (J, K, L) F = {JK _→ L

L _→ K_}

• Consider the following relation

J L K

j1 l1 k1

j2 l1 k1

j3 l1 k1

null l2 k2

• A schema that is in3NFbut not inBCNFhas the problems of – repetition of information (e.g., the relationshipl1, k1) – need to use null values (e.g., to represent the relationship

l₂, k₂ where there is no corresponding value forJ).

(11)

& %

Design Goals

• Goal for a relational database design is:

– BCNF.

– Lossless join.

– Dependency preservation.

• If we cannot achieve this, we accept:

– 3NF.

– Lossless join.

– Dependency preservation.

' $

Normalization Using Multivalued Dependencies

• There are database schemas inBCNFthat do not seem to be sufficiently normalized

• Consider a database

classes(course, teacher, book)

such that (c,t,b)∈classesmeans thatt is qualified to teachc, andbis a required textbook forc

• The database is supposed to list for each course the set of teachers any one of which can be the course’s instructor, and the set of books, all of which are required for the course (no matter who teaches it).

(12)

& %

course teacher book

database Avi Korth

database Avi Ullman

database Hank Korth

database Hank Ullman

database Sudarshan Korth

database Sudarshan Ullman

operating systems Avi Silberschatz operating systems Avi Shaw

operating systems Jim Silberschatz operating systems Jim Shaw

classes

• Since there are no non-trivial dependencies, (course, teacher, book) is the only key, and therefore the relation is inBCNF

• Insertion anomalies – i.e., if Sara is a new teacher that can teach database, two tuples need to be inserted

(database, Sara, Korth) (database, Sara, Ullman)

' $

• Therefore, it is better to decomposeclassesinto:

course teacher

database Avi

database Hank

database Sudarshan

operating systems Avi operating systems Jim

teaches

course book

database Korth

database Ullman

operating systems Silberschatz operating systems Shaw

text

• We shall see that these two relations are in Fourth Normal Form (4NF)

(13)

& %

Multivalued Dependencies (MVDs)

• LetRbe a relation schema and letα ⊆ R andβ ⊆R. The multivalued dependency

α →→ β

holds onRif in any legal relation r(R), for all pairs of tuplest₁ andt₂inrsuch thatt₁[α] = t₂[α], there exist tuplest₃ andt₄ in rsuch that:

t1[α] = t2[α] = t3[α] = t4[α] t₃[β] = t₁[β]

t₃[R _{− β}] = t₂[R _{− β}] t₄[β] = t₂[β]

t₄[R _{− β] =} t₁[R _{− β]}

' $

MVD (Cont.)

• Tabular representation ofα →→ β

α β R _{− α − β}

t1 a1 ... ai ai + 1 ... aj aj + 1 ... an

t₂ a₁ ... a_i b_{i + 1} ... b_j b_{j + 1} ... b_n t₃ a₁ ... a_i a_{i + 1} ... a_j b_{j + 1} ... b_n t4 a1 ... ai bi + 1 ... bj aj + 1 ... an

(14)

& %

Example

• LetRbe a relation schema with a set of attributes that are partitioned into 3 nonempty subsets,

Y,Z,W

• We say that Y _→→ Z (Y multideterminesZ) if and only if for all possible relationsr(R)

<y₁, z₁, w₁ _{> ∈} r and<y₁, z₂, w₂_{> ∈} r then

<y₁, z₁, w₂ _{> ∈} r and<y₁, z₂, w₁_{> ∈} r

• Note that since the behavior ofZ andW are identical it follows thatY _→→ Z iffY _→→ W

' $

Example (Cont.)

• In our example:

course _→→ teacher course _→→ book

• The above formal definition is supposed to formalize the notion that given a particular value ofY (course) it has associated with it a set of values ofZ (teacher) and a set of values of W (book), and these two sets are in some sense independent of each other.

• Note:

– IfY _→ Z thenY _→→ Z

– Indeed we have (in above notation)Z1 = Z2

The claim follows.

(15)

& %

Use of Multivalued Dependencies

• We use multivalued dependencies in two ways:

1. To test relations to determine whether they are legal under a given set of functional and multivalued dependencies.

2. To specify constraints on the set of legal relations. We shall thus concern ourselvesonlywith relations that satisfy a given set of functional and multivalued dependencies.

• If a relationrfails to satisfy a given multivalued dependency, we can construct a relationr⁰ that does satisfy the multivalued dependency by adding tuples tor.

' $

Theory of Multivalued Dependencies

• LetDdenote a set of functional and multivalued dependencies.

The closureD⁺ ofDis the set of all functional and multivalued dependencies logically implied byD.

• Sound and complete inference rules for functional and multivalued dependencies:

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.

3. Transitivity rule. Ifα → βholds andβ → γ holds, then α

→ γ holds.

(16)

& %

Theory of Multivalued Dependencies (Cont.)

4. Complementation rule. If α →→ βholds, then α →→ R _{− β − α}holds.

5. Multivalued augmentation rule. Ifα →→ β holds and γ

⊆Randδ ⊆ γ, thenγα →→ δβholds.

6. Multivalued transitivity rule. Ifα →→ β holds and β →→ γ holds, thenα →→ γ − β holds.

7. Replication rule. Ifα → β holds, then α →→ β. 8. Coalescence rule. Ifα →→ β holds andγ ⊆ β and

there is aδsuch thatδ ⊆Randδ ∩ β= ∅andδ → γ, then α → γ holds.

' $

Simplification of the Computation of D

⁺

• We can simplify the computation of the closure ofDby using the following rules (proved using rules 1–8).

– Multivalued union rule. Ifα →→ β holds andα →→ γ holds, thenα →→ βγ holds.

– Intersection rule. Ifα →→ β holds andα →→ γ holds, then α →→ β ∩ γ holds.

– Difference rule. Ifα →→ β holds andα →→ γ holds, then α →→ β − γ holds andα →→ γ − β holds.

(17)

& %

Example

• R= (A, B, C, G, H, I) D = {A_→→B

B _→→ HI CG _→ H_}

• Some members ofD⁺: – A_→→CGHI.

SinceA_→→B, the complementation rule (4) implies that A_→→R₋B₋A.

SinceR₋B₋A= CGHI, soA_→→ CGHI. – A_→→HI.

SinceA_→→BandB_→→HI, the multivalued transitivity rule (6) implies thatA_→→HI₋B.

SinceHI₋B= HI,A_→→HI.

' $

Example (Cont.)

• Some members ofD⁺ (cont.):

– B_→H.

Apply the coalescence rule (8);B_→→HIholds.

SinceH_⊆HIandCG_→HandCG_∩HI=∅, the

coalescence rule is satisfied withα beingB, β beingHI,δ beingCG, andγ beingH. We conclude thatB_→H. – A_→→CG.

A_→→CGHIandA_→→HI.

By the difference rule,A_→→CGHI₋HI. SinceCGHI₋HI= CG,A_→→CG.

(18)

& %

Fourth Normal Form

• A relation schemaR is in4NFwith respect to a set Dof functional and multivalued dependencies if for all multivalued dependencies inD⁺ of the formα →→ β, whereα ⊆Randβ ⊆ R, at least one of the following hold:

– α →→ β is trivial (i.e.,β ⊆ α orα ∪ β = R) – αis a superkey for schema R

• If a relation is in4NFit is inBCNF

' $

4NF Decomposition Algorithm

result:={R_}; done:= false;

computeF⁺;

while (notdone) do

if (there is a schemaR_i inresultthat is not in4NF) then begin

letα → βbe a nontrivial multivalued dependency that holds onR_i such that α →R_i is not inF⁺, andα ∩ β = ∅; result:= (result₋R_i)∪(R_i _{− β})∪(α,_β);

end

elsedone:= true;

Note: eachRi is in4NF, and decomposition is lossless-join.

(19)

& %

Example

• R = (A, B, C, G, H, I) F = {A _→→ B

B _→→ HI CG _→ H_}

• R is not in4NFsinceA _→→ B andAis not a superkey for R

• Decomposition

a) R₁ = (A, B) (R₁ is in4NF) b) R₂ = (A, C, G, H, I) (R₂ is not in4NF) c) R₃ = (C, G, H) (R₃ is in4NF) d) R₄ = (A, C, G, I) (R₄ is not in4NF)

• SinceA _→→ BandB _→→ HI,A _→→ HI,A _→→ I e) R5= (A, I) (R5 is in4NF) f) R₆ = (A, C, G) (R₆ is in4NF)

' $

Multivalued Dependency Preservation

• LetR₁, R₂, . . . , R_n be a decomposition ofR, andDa set of both functional and multivalued dependencies.

• TherestrictionofDtoR_i is the setD_i, consisting of – All functional dependencies inD⁺ that include only

attributes ofR_i

– All multivalued dependencies of the formα →→ β ∩R_i whereα ⊆R_i andα →→ βis inD⁺

• The decomposition isdependency-preserving with respect to Dif, for every set of relationsr₁(R₁), r₂(R₂),. . .,r_n(R_n) such that for alli,r_i satisfiesD_i, there exists a relationr(R) that satisfies Dand for whichri = ΠRi(r) for all i.

• Decomposition into 4NF may not be dependency preserving

(20)

& %

Normalization Using Join Dependencies

• Join dependencies constrain the set of legal relations over a schemaRto those relations for which a given decomposition is a lossless-join decomposition.

• LetRbe a relation schema andR₁, R₂, ..., R_nbe a

decomposition ofR. IfR =R₁ _∪R₂ _∪ ... _∪ R_n, we say that a relationr(R) satisfies thejoin dependency *(R₁, R₂, ..., R_n) if:

r = ΠR1 (r) ¹ ΠR2 (r) ¹ ... ¹ ΠRn (r) A join dependency istrivialif one of theR_i isRitself.

• A join dependency *(R1, R2) is equivalent to the multivalued dependencyR₁_∩R₂ _→→R₂. Conversely,α →→ β is

equivalent to∗(α ∪(R_{− β}),_{α ∪ β})

• However, there are join dependencies that are not equivalent to any multivalued dependency.

' $

Project-Join Normal Form (PJNF)

• A relation schemaRis inPJNFwith respect to a set Dof functional, multivalued, and join dependencies if for all join dependencies inD+ of the form

*(R₁, R₂, ..., R_n) where eachR_i _⊆ R andR = R₁ _∪ R₂ _∪ ... _∪ R_n, at least one of the following holds:

– *(R₁, R₂, ..., R_n) is a trivial join dependency.

– EveryR_i is a superkey forR.

• Since every multivalued dependency is also a join dependency, everyPJNFschema is also in 4NF.

(21)

& %

Example

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

• Each loan has one or more customers, is in one or more branches and has a loan amount; these relationships are independent, hence we have the join dependency

*((loan-number, branch-name), (loan-number, customer-name), (loan-number, amount))

• Loan-info-schema is not inPJNF with respect to the set of dependencies containing the above join dependency. To put Loan-info-schema intoPJNF, we must decompose it into the three schemas specified by the join dependency:

– (loan-number, branch-name) – (loan-number, customer-name) – (loan-number, amount)

' $

Domain-Key Normal Form (DKNY)

• Domain declaration. LetAbe an attribute, and let dom be a set of values. The domain declarationA_⊆dom requires that theAvalue of all tuples be values in dom.

• Key declaration. LetRbe a relation schema with K_⊆R. The key declaration key (K) requires thatKbe a superkey for schemaR(K_→R). All key declarations are functional dependencies but not all functional dependencies are key declarations.

• General constraint. A general constraint is a predicate on the set of all relations on a given schema.

• Let D be a set of domain constraints and let K be a set of key constraints for a relation schemaR. Let G denote the general constraints forR. SchemaRis in if D∪K logically imply

(22)

& %

Example

• Accounts whoseaccount-number begins with the digit 9 are special high-interest accounts with a minimum balance of

$2500.

• General constraint: “If the first digit oft[account-number] is 9, thent[balance]≥2500.”

• ^DKNFdesign:

Regular-acct-schema= (branch-name, account-number, balance) Special-acct-schema= (branch-name, account-number, balance)

• Domain constraints forSpecial-acct-schema require that for each account:

– The account number begins with 9.

– The balance is greater than 2500.

' $

DKNF rephrasing of PJNF Definition

• LetR = (A₁, A₂, ..., An) be a relation schema. Let dom(A_i) denote the domain of attributeA_i, and let all these domains be infinite. Then all domain constraints D are of the form

A_i _⊆ dom(A_i).

• Let the general constraints be a set G of functional,

multivalued, or join dependencies. IfFis the set of functional dependencies in G, let the set K of key constraints be those nontrivial functional dependencies inF⁺ of the form α →R.

• SchemaRis inPJNF if and only if it is inDKNFwith respect to D, K, and G.

(23)

& %

Alternative Approaches to Database Design

• Dangling tuples—Tuples that “disappear” in computing a join.

– Letr₁(R₁), r₂(R₂), ..., r_n(R_n) be a set of relations.

– A tupletof relationri is adangling tupleift is not in the relation:

ΠRi (r₁ ¹ r₂ ¹ ... ¹ rn)

• The relationr1 ¹ r2 ¹ ... ¹ rn is called auniversal relation since it involves all the attributes in the “universe” defined by R₁ _∪ R₂ _∪ ... _∪ R_n.

• If dangling tuples are allowed in the database, instead of decomposing a universal relation, we may prefer tosynthesize a collection of normal form schemas from a given set of

attributes.