Functional Dependencies

(1)

(2)

Functional Dependencies

• Functional dependencies: modeling

constraints on attributes

– stud-id Æ name address

– course-id session-id Æ classroom instructor

• Functional dependencies should be

obtained from application requirement

analysis

(3)

Closure of Functional Dependencies

• A set of functional dependencies may

logically imply other functional

dependencies

– If A → B and B → C, then A → C

• The set of all functional dependencies

logically implied by F is the closure of F

• We denote the closure of F by F

+

(4)

Armstrong’s Axioms

• Finding F

+

– (reflexivity) If β ⊆ α, then α → β

– (augmentation) If α → β, then γ α → γ β

– (transitivity) If α → β, and β → γ, then α → γ

• These rules are

– Sound: generate only functional dependencies that actually hold

(5)

Computing Closure

F + = F repeat

for each functional dependency f in F+

apply reflexivity and augmentation rules on f

add the resulting functional dependencies to F + for each pair of functional dependencies f₁and f₂ in F +

if f₁ and f₂ can be combined using transitivity

then add the resulting functional dependency to F +

(6)

Problem of Non-Key Dependencies

• bor_loan(customer_id, loan_number,

amount)

• loan_number

Æ amount, but loan_number

Æ customer_id does not hold

• The amount information repeats

unnecessarily

(7)

Update and Deletion Anomalies

• bor_loan(customer_id, loan_number, amount)

• Update anomalies: if the amount of a loan is changed and the loan has n customers, the n tuples have to be updated

• Deletion anomalies: if a loan having n customers is deleted, we have to delete n tuples

– The amount information has to be deleted n times

• Failing to update/delete all tuple leads to inconsistency

(8)

Reducing Redundancy

• Make a table loan(loan_number, amount)

where loan_number is the key

• Rename bor_loan to borrower(customer_id,

loan_number) where (customer_id,

loan_number) is the key

• No information loss by joining on

loan_number

(9)

Boyce-Codd Normal Form

• A relation schema R is in BCNF if for all

functional dependencies in F

+

of the form

α →

β

at least one of the following holds

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

– α is a superkey for R

• bor_loan = (customer_id, loan_number,

amount) is not in BCNF

(10)

Decomposing into BCNF

• For schema R and a non-trivial dependency

α →β causes a violation of BCNF,

decompose R into

– (α U β ): α is the key – (R - ( β - α ))

• bor_loan = (customer_id, loan_number,

amount), loan_number

→ amount

(11)

Missing Functional Dependencies

• cust_banker_branch(customer_id,

employee_id, branch_name, type)

– R1: employee_id Æ branch_name

– R2: customer_id, branch_name Æ employee_id

• Not in BCNF, decompose into

– banker_branch(employee_id, branch_name) – cust_banker(customer_id, employee_id, type)

(12)

Insertion Anomalies

• Decompose cust_banker_branch(customer_id, employee_id, branch_name, type) into

– banker_branch(employee_id, branch_name) and – cust_banker(customer_id, employee_id, type)

• Cannot insert (c1, e1, Burnaby, loan) and (c1, e2, Burnaby, loan) into cust_banker_branch

– “R2: customer_id, branch_name Æ employee_id” is violated

(13)

Insertion Anomalies

• Decompose cust_banker_branch(customer_id, employee_id, branch_name, type) into

– banker_branch(employee_id, branch_name) and – cust_banker(customer_id, employee_id, type)

• Can insert (e1, Burnaby) (e2, Burnaby) into

banker_branch, and (c1, e1, loan) and (c1, e2, loan) into cust_banker

• (c1, e1, Burnaby, loan) and (c1, e2, Burnaby, loan) are inserted into the join of banker_branch and

(14)

BCNF and Insertion Anomalies

• Decomposition to BCNF may lose some

functional dependencies

• The lost functional dependencies lead to

insertion anomalies

(15)

BCNF and Dependency Preservation

• If it is sufficient to test only those dependencies on each individual relation of a decomposition in

order to ensure that all functional dependencies

hold, then that decomposition is dependency

preserving

– Constraints, including functional dependencies, are costly to check in practice unless they pertain to only one relation – joining multiple tables is costly

(16)

Decomposition for Preservation

• Banker-info-schema = (branch-name,

customer-name, banker-customer-name, office-number)

– R1: banker-name → branch-name office-number

– R2: customer-name branch-name → banker-name

• Decomposition preserving all functional dependencies

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

– Banker-schema = (customer-name, branch-name,

(17)

Third Normal Form

• A relation schema R is in the third normal form

(3NF) if for all α →

β

in F+ _{at least one of the}

following holds

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

– Each attribute A in β – α is contained in a candidate key for R

• Each attribute may be in a different candidate key

• If a relation is in BCNF it is in 3NF

(18)

Comparison of BCNF and 3NF

• It is always possible to decompose a

relation into a set of relations that are in 3NF

such that the

decomposition is lossless

and

the dependencies are preserved

• It is always possible to decompose a

relation into a set of relations that are in

BCNF such that the

decomposition is

lossless

(19)

Design Goals

• Goal for a relational database design is

– BCNF

– Lossless join

– Dependency preservation

• If we cannot achieve all, we have to trade off

between

– Lack of dependency preservation – Redundancy due to use of 3NF

(20)

Functional Dependencies and SQL

• SQL does not provide a direct way of

specifying functional dependencies other

than superkeys

– Can specify FDs using assertions, but they are expensive to test

• Even if we had a dependency preserving

decomposition, using SQL we would not be

able to efficiently test a functional

(21)

Summary

• Non-key dependencies causes redundancy

and anomalies

• BCNF – lossless decomposition, but may

not preserve all functional dependencies

• 3NF – lossless decomposition preserving all

functional dependencies, but may still

(22)