3NF,BCNF and Lossless Decomposition

1
3NF,BCNF and Lossless Decomposition
Lecture 6
CS157B

• Prof. Sin-Min Lee
• Department of Computer Science
• San Jose State University

2
Functional Dependency TheoryMotivation

• Decide whether a particular relation R is in
  good form.
• In the case that a relation R 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
• Preferably, the decomposition should be
  dependency preserving

3
Functional DependenciesBackground

• Constraints on the set of legal relations in the
  database schema.
• Require that the value for a certain set of
  attributes determines uniquely the value for
  another set of attributes.
• Is a rule that specifies that a key uniquely
  determines an attribute or a set of attributes.

4
Formal Definition
Let R be a relation schema where ?????????????????
???? ? R and ? ? R The functional dependency
? ? ? ?holds on R if
and only if for any legal relations r(R),
whenever any two tuples t1 and t2 of r agree on
the attributes ?, they also agree on the
attributes ?. That is, t1? t2 ?
? t1? t2 ?
5
We now consider the formal theory that tells us
which functional dependencies are implied
logically by a given set of functional
dependencies.
6
Closure of a Set of Functional Dependencies

• Given a set F of functional dependencies,
  there exist other functional dependencies
  that are logically implied by F.
• The set of all functional dependencies
  logically implied by F is the closure of F.
• Denote the closure of F by F.
• F is a superset of F

7
Closure of a Set of Functional Dependencies
given R (A, B, C) and F A ? B, B ? C
then we can infer that A ? C suppose that t1
and t2 are tuples such that
t1A t2A since we are given A ? B, then
it follows from definition of FD that t1B
t2B also B ? C implies that t1C
t2C by FD def hence, we have shown that A ?
C by FD def
8
Closure of a Set of Functional Dependencies

• But this is convoluted method of finding F
• If F is large set of FDs, it would take
  forever to get F
• There is a much simpler method
  Armstrongs Axioms
• other additional rules

9
Closure of a Set of Functional Dependencies

• Armstrongs Axioms
• Reflexivity rule if ? ? ?, then ? ? ?
• Augmentation rule if ? ? ?, then ? ? ? ? ?
• Transitivity rule if ? ? ?, and ? ? ?,
  
  then ? ? ?
• These rules are
• sound generate only functional dependencies that
  actually hold
• complete generate all functional dependencies
  that hold

10
EX Find F using Armstrongs Axioms

• R (A, B, C, G, H, I)F A ? B, A ? C, CG
  ? H, CG ? I, B ? H
• some members of F
• A ? H by transitivity from A ? B and B ? H
  
• AG ? I augmenting A ? C with G, to
  get AG ? CG and then transitivity with CG
  ? I
• CG ? HI by augmenting CG ? I to infer CG ?
  CGI, and augmenting of CG ? H to infer
  CGI ? HI, and then transitivity

11
Closure of a Set of Functional Dependencies

• We can further simplify manual computation of F
  by using the following additional rules.
• Union If ? ? ? holds and ? ? ? holds,
  then ? ? ? ? holds
• Decomposition If ? ? ? ? holds, then ? ? ?
  holds and ? ? ? holds
• Pseudotransitivity If ? ? ? holds and
  ? ? ? ? holds, then ? ? ? ? holds
• The above rules can be inferred from Armstrongs
  axioms.

12
Closure of Attribute Sets

• Given a set of attributes ?? we can define the
  closure of ? under F (denoted by ?) as the set
  of attributes that are functionally determined
  by ? under F
• Algorithm to compute ?, the closure of ? under
  F
• result ?while (changes to result)
  do for each ? ? ?? in F do begin if ? ?
  result then result result ? ? end

13
Application of Algorithm to Compute Closure of
Attribute Sets

• R (A, B, C, G, H, I)
• F A ? B, A ? C, CG ? H, CG ? I, B ? H
• Compute (AG)
• 1. result AG
• 2. result ABCG (add BC A
  ? B and A ? C)
• 3. result ABCGH (add H CG ? H and
  CG ? AGBC)
• 4. result ABCGHI (add I CG ? I and CG ?
  AGBCH)

14
Uses of Attribute Closure Algorithm

• Testing for superkey
• To test if ? is a superkey, we compute ?, and
  check if ? contains all attributes of R.
• Testing functional dependencies
• To check if a functional dependency ? ? ? holds
  (or, in other words, is in F), just check if ? ?
  ?.
• That is, we compute ? by using attribute
  closurealgorithm, and then check if it contains
  ?

15
Uses of Attribute Closure Algorithm(cont)

• Is a simple and cheap test, and very useful
• Alternative way to compute closure of F
• For each ? ? R, we find the closure ?, and for
  each S ? ?, we output a functional dependency ?
  ? S.

16
In prior example
Note that AG is a super key AG ? R (AG) ?
R But A is not a super key G is not a super
key Hence AG is a candidate key
17
Purpose of Normalization

• To reduce the chances for anomalies to occur in a
  database.
• normalization prevents the possible corruption of
  databases stemming from what are called
  "insertion anomalies," "deletion anomalies," and
  "update anomalies."

18
Third Normal Form

• A relational schema R is in 3NF if
• (1) it is 2NF
• (2) for any two non-prime attributes X,Y there
  does not exist X?Y or Y?X

19
(No Transcript)
20
BCNF

• Definition A relation schema R is in BCNF if for
  every FD X? Y associated with R either
• Y ? X (i.e., the FD is trivial) or
• X is a superkey of R
• Example Person1(SSN, Name, Address)
• The only FD is SSN ? Name, Address
• Since SSN is a key, Person1 is in BCNF

21
(non) BCNF Examples

• Person (SSN, Name, Address, Hobby)
• The FD SSN ? Name, Address does not satisfy
  requirements of BCNF
• since the key is (SSN, Hobby)
• HasAccount (AcctNum, ClientId, OfficeId)
• The FD AcctNum? OfficeId does not satisfy BCNF
  requirements
• since keys are (ClientId, OfficeId) and (AcctNum,
  ClientId) not AcctNum.

22
Redundancy

• Suppose R has a FD A ? B, and A is not a
  superkey. If an instance has 2 rows with same
  value in A, they must also have same value in B
  (gt redundancy, if the A-value repeats twice)
• If A is a superkey, there cannot be two rows with
  same value of A
• Hence, BCNF eliminates redundancy

SSN ? Name, Address SSN Name
Address Hobby 1111 Joe 123 Main
stamps 1111 Joe 123 Main coins
redundancy
23
3NF Example

• HasAccount (AcctNum, ClientId, OfficeId)
• ClientId, OfficeId ? AcctNum
• OK since LHS contains a key
• AcctNum ? OfficeId
• OK since RHS is part of a key
• HasAccount is in 3NF but it might still contain
  redundant information due to AcctNum ? OfficeId
  (which is not allowed by BCNF)

24
3NF (Non) Example

• Person (SSN, Name, Address, Hobby)
• (SSN, Hobby) is the only key.
• SSN? Name violates 3NF conditions since Name is
  not part of a key and SSN is not a superkey

25
3NF but not boyce-codd NF

• SUPPLIER_PART (supplier, supplier_name, part,
  quantity)
• Two candidate keys
• (supplier, part) and (supplier_name, part)
• (supplier, part) -gt quantity
• (supplier, part) -gt supplier_name
• (supplier, part) -gt quantity
• (supplier, part) -gt supplier
• supplier_name -gt supplier
• supplier -gt supplier_name

26
(No Transcript)
27
Another example of Boyce-Codd NF
28
Example (contd)

• title, year, starName as candidate key
• title, year ? length, filmType, studioName
• The above FD (Functional Dependency) violates the
  BCNF condition because title and year do not
  determine the sixth attribute, starName

29
Example (contd)

• We solve this BCNF violation by decomposing
  relation Movies into
• 1. The schema with all the attributes of the FD
• title, year, length, filmType,
  studioName
• 2. The schema with all attributes of Movies
  except the three that appear on the right of
  the FD
• title, year, starName

30
Decompositions

• Goal Eliminate redundancy by decomposing a
  relation into several relations in a higher
  normal form
• Decomposition must be lossless it must be
  possible to reconstruct the original relation
  from the relations in the decomposition
• We will see why

31
Decomposition

• Schema R (R, F)
• R is set a of attributes
• F is a set of functional dependencies over R
• Each key is described by a FD
• The decomposition of schema R is a collection of
  schemas Ri (Ri, Fi) where
• R ?i Ri for all i (no new attributes)
• Fi is a set of functional dependences involving
  only attributes of Ri
• F entails Fi for all i (no new FDs)
• The decomposition of an instance, r, of R is a
  set of relations ri ?Ri(r) for all i

32
(No Transcript)
33
Example Decomposition
Schema (R, F) where R SSN, Name,
Address, Hobby F SSN? Name,
Address can be decomposed into R1 SSN,
Name, Address F1 SSN ? Name,
Address and R2 SSN, Hobby F2

34
Lossless Schema Decomposition

• A decomposition should not lose information
• A decomposition (R1,,Rn) of a schema, R, is
  lossless if every valid instance, r, of R can be
  reconstructed from its components
• where each ri ?Ri(r)

r2
r r1
rn

35
Lossy Decomposition
The following is always the case (Think why?)
r ? r1
r2
rn
...
But the following is not always true
r ? r1
r2
rn
...
?
r1
r2
r
Example
SSN Name Address SSN Name
Name Address 1111 Joe 1 Pine
1111 Joe Joe 1 Pine 2222 Alice
2 Oak 2222 Alice Alice 2
Oak 3333 Alice 3 Pine 3333 Alice
Alice 3 Pine
The tuples (2222, Alice, 3 Pine) and (3333,
Alice, 2 Oak) are in the join, but not in the
original
36
Lossy Decompositions What is Actually Lost?

• In the previous example, the tuples (2222, Alice,
  3 Pine) and (3333, Alice, 2 Oak) were gained, not
  lost!
• Why do we say that the decomposition was lossy?
• What was lost is information
• That 2222 lives at 2 Oak In the
  decomposition, 2222 can live at either 2 Oak or 3
  Pine
• That 3333 lives at 3 Pine In the
  decomposition, 3333 can live at either 2 Oak or 3
  Pine

37
Example
Schema (R, F) where R SSN, Name,
Address, Hobby F SSN ? Name,
Address can be decomposed into R1 SSN,
Name, Address F1 SSN ? Name,
Address and R2 SSN, Hobby F2
Since R1 ? R2 SSN and SSN ? R1
the decomposition is lossless
38
Intuition Behind the Test for Losslessness

• Suppose R1 ? R2 ? R2 . Then a row of r1 can
  combine with exactly one row of r2 in the
  natural join (since in r2 a particular set of
  values for the attributes in R1 ? R2 defines a
  unique row)

R1 ? R2 R1 ? R2 . a
a ... a b
. b c .
c r1 r2
39
Proof of Lossless Condition

• r ? r1

r2 this is true for any decomposition
r2

• r ? r1

If R1 ? R2 ? R2 then card (r1
r2) card (r1)
(since each row of r1 joins with exactly one
row of r2)
But card (r) ? card (r1) (since r1 is a
projection of r) and therefore card (r) ? card
(r1 r2)
Hence r r1
r2
40
Dependency Preservation

• Consider a decomposition of R (R, F) into R1
  (R1, F1) and R2 (R2, F2)
• An FD X ? Y of F is in Fi iff X ? Y ? Ri
• An FD, f ?F may be in neither F1, nor F2, nor
  even (F1 ? F2)
• Checking that f is true in r1 or r2 is
  (relatively) easy
• Checking f in r1 r2 is harder requires
  a join
• Ideally want to check FDs locally, in r1 and
  r2, and have a guarantee that every f ?F holds
  in r1 r2
• The decomposition is dependency preserving iff
  the sets F and F1 ? F2 are equivalent F (F1
  ? F2)
• Then checking all FDs in F, as r1 and r2 are
  updated, can be done by checking F1 in r1 and F2
  in r2

41
Dependency Preservation

• If f is an FD in F, but f is not in F1 ? F2,
  there are two possibilities
• f ? (F1 ? F2)
• If the constraints in F1 and F2 are maintained,
  f will be maintained automatically.
• f ? (F1 ? F2)
• f can be checked only by first taking the join
  of r1 and r2. This is costly.

42
Example
Schema (R, F) where R SSN, Name,
Address, Hobby F SSN ? Name,
Address can be decomposed into R1 SSN,
Name, Address F1 SSN ? Name,
Address and R2 SSN, Hobby F2
Since F F1 ? F2 the decomposition
is dependency preserving
43
Example

• Schema (ABC F) , F A ? B, B? C, C? B
• Decomposition
• (AC, F1), F1 A?C
• Note A?C ? F, but in F
• (BC, F2), F2 B? C, C? B
• A ? B ? (F1 ? F2), but A ? B ? (F1 ? F2).
• So F (F1 ? F2) and thus the decompositions
  is still dependency preserving

44
BCNF Decomposition Algorithm
Input R (R F) Decomp R while there is S
(S F) ? Decomp and S not in BCNF do
Find X ? Y ? F that violates BCNF // X isnt
a superkey in S Replace S in Decomp with
S1 (XY F1), S2 (S - (Y - X) F2) //
F1 all FDs of F involving only attributes of
XY // F2 all FDs of F involving only
attributes of S - (Y - X) end return Decomp
45
Simple Example

• HasAccount

(ClientId, OfficeId, AcctNum)
ClientId,OfficeId ? AcctNum AcctNum ? OfficeId

• Decompose using AcctNum ? OfficeId

(OfficeId, AcctNum)
(ClientId , AcctNum) BCNF (only trivial FDs)
BCNF AcctNum is key FD AcctNum ? OfficeId
46
A Larger Example
Given R (R F) where R ABCDEGHK and F
ABH? C, A? DE, BGH? K, K? ADH, BH? GE step 1
Find a FD that violates BCNF Not ABH
? C since (ABH) includes all attributes
(BH is a key) A ? DE
violates BCNF since A is not a superkey (A
ADE) step 2 Split R into R1 (ADE, F1A?
DE ) R2 (ABCGHK F1ABH?C, BGH?K, K?AH,
BH?G) Note 1 R1 is in BCNF Note 2
Decomposition is lossless since A is a key of
R1. Note 3 FDs K ? D and BH ? E are not in
F1 or F2. But both can be derived from F1?
F2 (E.g., K? A and
A? D implies K? D) Hence,
decomposition is dependency preserving.
47
Example (cont)
Given R2 (ABCGHK ABH?C, BGH?K, K?AH,
BH?G) step 1 Find a FD that violates
BCNF. Not ABH ? C or BGH ? K, since BH is a
key of R2 K? AH violates BCNF since K is not
a superkey (K AH) step 2 Split R2 into
R21 (KAH, F21K ? AH) R22 (BCGK
F22) Note 1 Both R21 and R22 are in
BCNF. Note 2 The decomposition is
lossless (since K is a key of R21) Note
3 FDs ABH? C, BGH? K, BH? G are not in F21
or F22 , and they cant be derived
from F1 ? F21 ? F22 . Hence the
decomposition is not dependency-preserving
48
Properties of BCNF Decomposition Algorithm

• Let X ? Y violate BCNF in R (R,F) and R1
  (R1,F1),
• R2 (R2,F2) is the resulting decomposition.
  Then
• There are fewer violations of BCNF in R1 and R2
  than there were in R
• X ? Y implies X is a key of R1
• Hence X ? Y ? F1 does not violate BCNF in R1 and,
  since X ? Y ?F2, does not violate BCNF in R2
  either
• Suppose f is X ? Y and f ? F doesnt violate
  BCNF in R. If f ? F1 or F2 it does not violate
  BCNF in R1 or R2 either since X is a superkey
  of R and hence also of R1 and R2 .

49
Properties of BCNF Decomposition Algorithm

• A BCNF decomposition is not necessarily
  dependency preserving
• But always lossless
• since R1 ? R2 X, X ? Y, and R1 XY
• BCNFlosslessdependency preserving is sometimes
  unachievable (recall HasAccount)

50
(No Transcript)
51
BoyceCodd Normal Form (BCNF)

• A relation r is in BoyceCodd normal form if
  for every (non-trivial)
• functional dependency X -gt Y defined on it, X
  contains a key K of r.
• That is, X is a superkey for r.
• Anomalies and redundancies, as discussed above,
  do not appear in
• databases with relations in BoyceCodd normal
  form, because the
• independent pieces of information are separate,
  one per relation.

52
(No Transcript)
53
(No Transcript)
54
Decomposition into BoyceCodd normal form

• Given a relation that does not satisfy
  BoyceCodd normal form, we
• can often replace it with one or more normalized
  relations using a
• process called normalization.
• We can eliminate redundancies and anomalies for
  the example
• relation if we replace it with the three
  relations, obtained by
• projections on the sets of attributes
  corresponding to the three
• functional dependencies.
• The keys of the relations we obtain are the
  left hand side of a
• functional dependency the satisfaction of the
  BoyceCodd normal
• form is therefore guaranteed.

55
(No Transcript)
56
(No Transcript)
57
(No Transcript)
58
(No Transcript)
59
(No Transcript)
60
(No Transcript)
61
(No Transcript)
62
(No Transcript)
63
(No Transcript)
64
(No Transcript)
65
(No Transcript)
66
(No Transcript)
67
(No Transcript)
68
(No Transcript)
69
(No Transcript)