chapter 8: relational database design first normal form first normal form functional dependencies...
TRANSCRIPT
Chapter 8: Relational Chapter 8: Relational Database DesignDatabase Design
First Normal FormFirst Normal Form Functional DependenciesFunctional Dependencies DecompositionDecomposition Boyce-Codd Normal FormBoyce-Codd Normal Form Third Normal FormThird Normal Form
First Normal FormFirst Normal Form
R is in R is in first normal formfirst normal form if if the domains of the domains of all attributes of R are atomicall attributes of R are atomic
In object relational/object oriented In object relational/object oriented databases, attributes can be composite or databases, attributes can be composite or multi-valuedmulti-valued
But in relational databases, composite But in relational databases, composite attributes will need to be flatten out and attributes will need to be flatten out and multi-valued attributes need to be multi-valued attributes need to be represented by another relationrepresented by another relation
Pitfalls in Relational Pitfalls in Relational Database DesignDatabase Design
We can create tables to represent an ER We can create tables to represent an ER design in many different ways design in many different ways Combine attributes differently to create Combine attributes differently to create
tablestables Why do we choose some ways over the Why do we choose some ways over the
others?others? RedundancyRedundancy Inability to represent certain informationInability to represent certain information
E.g. relationships among attributes E.g. relationships among attributes
ER for Banking EnterpriseER for Banking Enterprise
Schema Diagram for the Schema Diagram for the Banking EnterpriseBanking Enterprise
ExampleExample Consider the relation schema:Consider the relation schema:
Lending-schema Lending-schema = (= (branch-name, branch-city, branch-name, branch-city, assets, assets, customer-name,customer-name, loan-number,loan-number, amount) amount)
Redundancy, why?Redundancy, why? Inability to represent certain information, why?Inability to represent certain information, why?
Cannot store information about a branch if no loans Cannot store information about a branch if no loans exist exist
Can use null values, but they are difficult to handle.Can use null values, but they are difficult to handle.
Why Redundancy Is Bad?Why Redundancy Is Bad?
Wastes space Wastes space Complicates updating, introducing Complicates updating, introducing
possibility of inconsistency of possibility of inconsistency of assetsassets value value We know why inability to represent certain We know why inability to represent certain
information is bad.information is bad.
DecompositionDecomposition
Decompose the relation schema Decompose the relation schema Lending-Lending-schema schema into:into:Branch-schema = (Branch-schema = (branch-name,branch-name, branch- branch-
city,assets)city,assets)
Loan-info-schema = (Loan-info-schema = (customer-name,customer-name, loan-loan-number,number, branch-name, amount) branch-name, amount)
Lossless-join decompositionLossless-join decomposition
All attributes of an original schema (All attributes of an original schema (R) R) must appear in the decomposition (must appear in the decomposition (RR11, R, R22):):
R = RR = R1 1 RR22
For all possible relations For all possible relations rr on schema on schema RR
r = r = R1 R1 ((rr) ) R2 R2 ((rr) )
Non Lossless-Join Non Lossless-Join Decomposition Decomposition
Decomposition of Decomposition of R = (A, B)R = (A, B) RR11 = (A) = (A) RR22 = (B) = (B)
A B
121
A
B
12
rA(r)
B(r)
A (r) B (r)A B
1212
We do not loss any tuple but we lose the relationship between A and B
Relational DB Design Relational DB Design Process Process
Decide whether a particular relation Decide whether a particular relation RR is in is in “good” form.“good” form.
In the case that a relation In the case that a relation RR is not in “good” is not in “good” form, decompose it into a set of relations form, decompose it into a set of relations {{RR11, R, R22, ..., R, ..., Rnn} such that } such that each relation is in good form each relation is in good form the decomposition is a lossless-join decompositionthe decomposition is a lossless-join decomposition
Our theory is based on:Our theory is based on: functional dependenciesfunctional dependencies multivalued dependenciesmultivalued dependencies
Functional DependenciesFunctional Dependencies
Constraints on the set of legal relations.Constraints on the set of legal relations. Require that the value for a certain set of Require that the value for a certain set of
attributes determines uniquely the value attributes determines uniquely the value for another set of attributes.for another set of attributes.
A functional dependency is a A functional dependency is a generalization of the notion of a generalization of the notion of a key.key.
Functional DependenciesFunctional Dependencies Let Let RR be a relation schema be a relation schema
R and R and RR The functional dependencyThe functional dependency
holds onholds on RR if and only if for any legal relations if and only if for any legal relations rr(R), whenever any two tuples (R), whenever any two tuples tt11 and and tt22 of of rr agree on the attributes agree on the attributes , they also agree on , they also agree on the attributes the attributes . . That is, That is,
tt11[[] = ] = tt2 2 [[] ] tt11[[ ] = ] = tt2 2 [[ ] ]
ExampleExample
Example: Consider Example: Consider r(A,B)r(A,B) with the with the following instance of following instance of r.r.
On this instance, On this instance, AA BB does does NOTNOT hold, hold, but but BB AA does hold. does hold.
Note: function dependency needs to hold Note: function dependency needs to hold for for anyany possible instance of a relation. possible instance of a relation.
1 41 53 7
More ExampleMore Example
Consider the schema:Consider the schema:
Loan-info-schema Loan-info-schema = = ((customer-name,customer-name, loan-loan-number,number, branch-name, amount). branch-name, amount).
We expect this set of functional We expect this set of functional dependencies to hold:dependencies to hold:
loan-numberloan-number amountamountloan-number loan-number branch-name branch-name
but would not expect the following to hold: but would not expect the following to hold:
loan-number loan-number customer-name customer-name (why?)(why?)
Keys Defined by Functional Keys Defined by Functional DependenciesDependencies
KK is a superkey for relation schema is a superkey for relation schema RR if if and only if and only if K K RR
KK is a candidate key for is a candidate key for RR if and only if if and only if K K RR, and, and for no for no K, K, RR
Example KeysExample Keys
Lending-schema Lending-schema = (= (branch-name, branch-branch-name, branch-city, assets, city, assets, customer-name,customer-name, loan-number,loan-number, amount)amount)
Superkeys?Superkeys? Candidate keys?Candidate keys?
Functional DependenciesFunctional Dependencies
A A functional dependency is functional dependency is trivialtrivial if it is if it is satisfied by all instances of a relationsatisfied by all instances of a relation E.g.E.g.
customer-name, loan-number customer-name, loan-number customer-namecustomer-name customer-name customer-name customer-namecustomer-name
In general, In general, is trivial ifis trivial if
Closure of Attribute SetsClosure of Attribute Sets
Given a set of attributes Given a set of attributes define the define the closureclosure of of underunder FF (denoted by (denoted by ++) as the ) as the set of attributes that are functionally set of attributes that are functionally determined by determined by under under F:F:
is in is in FF++ ++
Algorithm to compute Algorithm to compute ++
The closure of The closure of under under FFresult result := := ;;whilewhile (changes to (changes to resultresult) ) dodo
for each for each inin F F do dobeginbegin
if if resultresult then then result result := :=
result result endend
Example of Attribute Set Example of Attribute Set ClosureClosure
R = (A, B, C, G, H, I)R = (A, B, C, G, H, I) F = F = {{A A BB
A A C C CG CG HHCG CG IIB B HH}}
((AG)AG)++
1.1. result = AGresult = AG2.2. result = ABCGresult = ABCG (A (A C C and and A A B) B)3.3. result = ABCGHresult = ABCGH (CG (CG HH and and CG CG AGBC)AGBC)4.4. result = ABCGHIresult = ABCGHI (CG (CG II and and CG CG AGBCH)AGBCH)
Example ClosuresExample Closures
Lending-schema Lending-schema = (= (branch-name, branch-city, branch-name, branch-city, assets, assets, customer-name,customer-name, loan-number,loan-number, amount)amount) Closure of attribute set (loan-number)?Closure of attribute set (loan-number)? Closure of attribute set (customer-name, loan-Closure of attribute set (customer-name, loan-
number)?number)? Closure of attribute set (branch-name)?Closure of attribute set (branch-name)?
Given:Given: loan-number loan-number branch-name, amount branch-name, amount branch-name branch-name branch-city, assets branch-city, assets customer-name,loan-number customer-name,loan-number Lending-schema Lending-schema
Uses of Attribute ClosureUses of Attribute Closure
Testing for superkey and candidate keyTesting for superkey and candidate key Testing functional dependenciesTesting functional dependencies Computing closure of FComputing closure of F
Testing for KeysTesting for Keys
To test if To test if is a superkey, we compute is a superkey, we compute ++,, and check if and check if ++ contains all attributes of contains all attributes of RR..
To test if To test if is a candidate key, first make is a candidate key, first make sure sure is a superkey. Then make sure no is a superkey. Then make sure no subset of subset of is a superkey is a superkey
In the previous example, is In the previous example, is AGAG a candidate key? a candidate key? 1.1. Is AG a super key?Is AG a super key?
1.1. Does Does AG AG R? == R? == Is (AG)Is (AG)+ + R R
2.2. Is any subset of AG a superkey?Is any subset of AG a superkey?
1.1. Does Does AA RR? ? == == Is (A)Is (A)+ + R R
2.2. Does Does GG RR? == Is (G)? == Is (G)+ + R R
Example: Testing for keysExample: Testing for keys
Lending-schema Lending-schema = (= (branch-name, branch-city, branch-name, branch-city, assets, assets, customer-name,customer-name, loan-number,loan-number, amount) amount) Is (loan-number) a superkey/candidate key?Is (loan-number) a superkey/candidate key? Is (customer-name, loan-number) a superkey/candidate Is (customer-name, loan-number) a superkey/candidate
key?key? Is (branch-name) a superkey/candidate key?Is (branch-name) a superkey/candidate key? Is (customer-name, loan-number,branch-name) a Is (customer-name, loan-number,branch-name) a
superkey/candidate key?superkey/candidate key? Given:Given:
loan-number loan-number branch-name,amount branch-name,amount branch-name branch-name branch-city, assets branch-city, assets customer-name,loan-number customer-name,loan-number Lending-schema Lending-schema
Testing Functional Testing Functional DependenciesDependencies
To check if a functional dependency To check if a functional dependency holds (or, in other words, is in holds (or, in other words, is in FF++), just ), just check if check if ++. .
Example: Testing for Example: Testing for functional dependenciesfunctional dependencies
Lending-schema Lending-schema = (= (branch-name, branch-branch-name, branch-city, assets, city, assets, customer-name,customer-name, loan-number,loan-number, amount)amount) Does Does loan-number loan-number branch-city, assets branch-city, assets hold? hold? Does Does customer-name, loan-number, amount customer-name, loan-number, amount
amount amount hold?hold? Given:Given:
loan-number loan-number branch-name,amount branch-name,amount branch-name branch-name branch-city, assets branch-city, assets customer-name,loan-number customer-name,loan-number Lending-schema Lending-schema
Computing Closure of FComputing Closure of F But what is a closure of F?But what is a closure of F?
Given a set Given a set FF set of functional dependencies, set of functional dependencies, there are certain other functional there are certain other functional dependencies that are logically implied by dependencies that are logically implied by FF..
E.g. If A E.g. If A B and B B and B C, then we can infer that A C, then we can infer that A CC
The set of all functional dependencies logically The set of all functional dependencies logically implied by implied by FF is the is the closureclosure of of FF..
We denote the We denote the closure closure of of FF by by FF++..
Armstrong’s AxiomsArmstrong’s Axioms
Sound and Complete rules:Sound and Complete rules: if if , then , then (reflexivity)(reflexivity) if if , , then then
(augmentation)(augmentation) if if , , and and , then , then (transitivity)(transitivity)
These rules are These rules are soundsound (generate only functional dependencies (generate only functional dependencies
that actually hold) and that actually hold) and completecomplete (generate all functional (generate all functional
dependencies that hold).dependencies that hold).
ExampleExample R = (A, B, C, G, H, I)R = (A, B, C, G, H, I)
F = F = { { A A B, A B, A C, CG C, CG H, CG H, CG I, B I, B H H }} some members of some members of FF++
A A H H by transitivity from by transitivity from A A B and B B and B HH
AG AG I I by augmenting by augmenting A A C C with G, to get with G, to get AG AG CG CG and and
then transitivity with then transitivity with CG CG I I CG CG HI HI
from from CG CG H and CG H and CG I : I : “union rule” can be “union rule” can be inferred frominferred from definition of functional dependencies, or definition of functional dependencies, or Augmentation of Augmentation of CG CG I I to infer to infer CG CG CG CGI, I, augmentation augmentation
ofofCG CG H H to inferto infer CGI CGI HI, HI, and then transitivityand then transitivity
Closure of Functional Closure of Functional DependenciesDependencies
Derived rules from Armstrong’s Derived rules from Armstrong’s axiomsaxioms If If holdsholds a and nd holds, then holds, then
holds holds (union)(union) If If holds, then holds, then holds and holds and
holds holds (decomposition)(decomposition) If If holdsholds a and nd holds, then holds, then
holds holds (pseudotransitivity) (pseudotransitivity)
When we compute the closure of attribute When we compute the closure of attribute set set , we already implicitly used , we already implicitly used Armstrong’s axiomsArmstrong’s axioms
Let us just use the closure of attribute set Let us just use the closure of attribute set to calculate the closure of functional to calculate the closure of functional dependency setdependency set
Now Compute Closure of FNow Compute Closure of F
For each For each R, R, we find the closure we find the closure ++, and for each , and for each SS ++, we output a , we output a functional dependency functional dependency S.S.
Example: Closure of FExample: Closure of F
Lending-schema Lending-schema = (= (branch-name, branch-branch-name, branch-city, assets, city, assets, customer-name,customer-name, loan-number,loan-number, amount)amount) How to compute the closure of F (F is given How to compute the closure of F (F is given
below)?below)? Given F:Given F:
loan-number loan-number branch-name,amount branch-name,amount branch-name branch-name branch-city, assets branch-city, assets customer-name,loan-number customer-name,loan-number Lending- Lending-
schema schema
We just talked about the We just talked about the maximalmaximal set of set of functional dependencies that can be functional dependencies that can be derived from a functional dependent set Fderived from a functional dependent set F Closure of FClosure of F
What about the What about the minimalminimal set of functional set of functional dependencies that is equivalent functional dependencies that is equivalent functional dependent set Fdependent set F Canonical cover of FCanonical cover of F
Canonical CoverCanonical Cover
Intuitively, a canonical cover of F is a Intuitively, a canonical cover of F is a ““minimalminimal” set of functional dependencies ” set of functional dependencies equivalentequivalent to F, having no redundant to F, having no redundant dependencies or redundant parts of dependencies or redundant parts of dependencies dependencies
What does “equivalent” mean?What does “equivalent” mean? What does “minimal” mean?What does “minimal” mean?
Equivalent Means:Equivalent Means:
Give two functional dependency sets F and Give two functional dependency sets F and F’, they are equivalent if and only if:F’, they are equivalent if and only if: F logically implies all functional dependencies in F logically implies all functional dependencies in
F’F’ F’ logically implies all functional dependencies in F’ logically implies all functional dependencies in
FF We use to represent We use to represent logically implylogically imply and and
to represent to represent equivalentequivalent How to test if F F’?How to test if F F’?
For each For each F’, test if it holds in FF’, test if it holds in F You may need to calculate You may need to calculate + + in Fin F
Minimal Means:Minimal Means: No redundant functional dependencies!No redundant functional dependencies! Sets of functional dependencies may have Sets of functional dependencies may have
redundant dependencies that can be inferred redundant dependencies that can be inferred from the othersfrom the others Eg: A Eg: A C is redundant in: {A C is redundant in: {A B, B B, B C, A C, A C} C}
Parts of a functional dependency may be Parts of a functional dependency may be redundantredundant
E.g. on RHS: {A E.g. on RHS: {A B, B B, B C, A C, A CD} can be simplified CD} can be simplified to {A to {A B, B B, B C, A C, A D} D}
E.g. on LHS: {A E.g. on LHS: {A B, B B, B C, AC C, AC D} can be simplified D} can be simplified to {A to {A B, B B, B C, A C, A D} D}
Extraneous AttributesExtraneous Attributes For any For any in in FF
Attribute Attribute A A is is extraneous ifextraneous if FF ( (FF – { – { }) }) {( {( – – AA) ) }.}.
Attribute Attribute AA is is extraneousextraneous if if F F ( (FF – { – { }) }) { { (( – – AA)})}
Use attribute closures to check Use attribute closures to check equivalenceequivalence
ExamplesExamples
Given Given FF = { = {AA CC, , ABAB CC } } BB is extraneous in is extraneous in ABAB C C because { because {AA C, ABC, AB
C C} is equivalent to {} is equivalent to {AA CC, , AA CC } = { } = {AA CC}}
Given Given FF = { = {AA CC, , ABAB CD}CD} CC is extraneous in is extraneous in ABAB CDCD because { because {AA CC, ,
ABAB CD} CD} is equivalent to {is equivalent to {AA CC, , ABAB D} D}
Canonical CoverCanonical Cover A A canonical covercanonical cover for for FF is a set of is a set of
dependencies dependencies FFc c such that such that FF logically implies all dependencies in logically implies all dependencies in FFc,c, and and FFcc logically implies all dependencies in logically implies all dependencies in F,F, and and No functional dependency in No functional dependency in FFcc contains an contains an
extraneous attribute, andextraneous attribute, and Each left side of functional dependency in Each left side of functional dependency in FFcc is is
unique.unique.
Compute a canonical cover for Compute a canonical cover for FF
repeatrepeatUse the union rule to replace any Use the union rule to replace any
dependencies in dependencies in FF 11 11 and and 11 22 with with 11 11 22
Find a functional dependency Find a functional dependency with an with an extraneous attribute either in extraneous attribute either in or in or in
If an extraneous attribute is found, delete it If an extraneous attribute is found, delete it from from
until until FF does not change does not change
Note: Union rule may become applicable after some extraneous Note: Union rule may become applicable after some extraneous attributes have been deleted, so it has to be re-appliedattributes have been deleted, so it has to be re-applied
ExampleExample R R = (= (A, B, C)A, B, C)
F = {A F = {A BC, B BC, B C, A C, A BB,,ABAB CC}} Combine Combine A A BC BC and and A A B B into into A A BCBC
Set is now Set is now {A {A BC, B BC, B C, ABC, AB CC}} Is Is BB extraneous in extraneous in AA BCBC
{A {A BC, B BC, B C, ABC, AB CC} ? } ? {A {A C, B C, B C, ABC, AB CC} NO} NO Is Is CC extraneous in extraneous in AA BCBC
{A {A BC, B BC, B C, ABC, AB CC} ? } ? {A {A B, B B, B C, ABC, AB CC} Yes} Yes Set is now Set is now {A {A B, B B, B C, ABC, AB CC} }
Is Is AA is extraneous in is extraneous in ABAB CC {A {A B, B B, B C, ABC, AB CC} ? {} ? {A A B, B B, B C, BC, B CC} Yes} Yes Set is now Set is now {A {A B, B B, B CC}}
The canonical cover is: The canonical cover is: {A {A B, B B, B C}C}
Example: Canonical Cover Example: Canonical Cover of Fof F
Lending-schema Lending-schema = (= (branch-name, branch-branch-name, branch-city, assets, city, assets, customer-name,customer-name, loan-number,loan-number, amount)amount) How to compute the Canonical Cover of F (F is How to compute the Canonical Cover of F (F is
given below)?given below)? Given F:Given F:
loan-number loan-number branch-name,branch- branch-name,branch-city,assets,amountcity,assets,amount
branch-name branch-name branch-city, assets branch-city, assets customer-name,loan-number customer-name,loan-number Lending-schema Lending-schema