Algorithm to Decompose R into BCNF [266]

A relation schema R is in BCNF wrt a set of FD's F over R, iff whenever $X \rightarrow A \in F^{+}$ and $A \not \in X$, X is a superkey.

• $A \not \in X$ takes care of trivial dependencies
• only non-trivial dependencies are those induced by the keys
• definition is identical to 3NF except 3NF had `or A is prime'
• BCNF is stronger than 3NF
  • has less redundancy
  • might have more relations
  • both guarantee lossless join
  • BCNF may not preserve dependencies

BCNF Example [267]

BAD(S#,P#,NAME,CITY,XNAME,YCITY,QTY)

is not in BCNF wrt

$F = \{S \rightarrow N C, P \rightarrow XY, SP \rightarrow Q\}$

since the first two are not superkeys since the key is SP.

Decompose:

S(S#,CITY,NAME) is in BCNF wrt $S \rightarrow NC$

P(P#,XNAME,YCITY) is in BCNF wrt $P \rightarrow XY$

SP(S#,P#,QTY) is in BCNF wrt $SP \rightarrow Q$

In fact, this is the same decomposition as the 3NF algorithm would yield.

If its in BCNF, then its in 3NF but not vice versa.

Algorithm to Decompose into BCNF [268]

Input: Relation schema R and FD's F

Output: A BCNF decomposition of R with lossless join

Method:

• at each step compute the key for the subrelation S
• if not in BCNF, pick any $X \rightarrow Y$ which violates
• break the relation into 2 subrelations
  • S1(XY)
  • S2(S - Y)
  • this has a lossless join
  • project FD's onto each subrelation
• continue until no more offending FD's

BCNF Example [269]

R = (ABCDE) and $F = \{A \rightarrow B,B \rightarrow AE, AC \rightarrow D\}$

Key: AC,BC

1. A 3NF Decomposition

ABCD, $A \rightarrow B, B \rightarrow A, AC \rightarrow D$

with Keys AC,BC is 3NF since A,B are primes

Not in BCNF.

ABE, $A \rightarrow B, B \rightarrow A, B \rightarrow E$

with Keys A,B is in 3NF and is in BCNF

2. Use Algorithm for producing 3NF

$A \rightarrow B, B \rightarrow A, B \rightarrow E, AC \rightarrow D$

which yields (AB,BE,ACD) which contains the key AC

This is 3NF and BCNF.

BCNF Example - continued [270]

Now use the BCNF algorithm.

ABCDE

$A \rightarrow B,B \rightarrow AE, AC \rightarrow D$

Key: AC,BC

The first one violates BCNF, branch it off.

AB

$A \rightarrow B,B \rightarrow A$

Key: A,B

ACDE

$A \rightarrow E, AC \rightarrow D$

Key: AC

$A \rightarrow E$ violates, branch it off

AE

$A \rightarrow E$

Key: A

ACD

$AC \rightarrow D$

Key: AC

Answer: (AB,AE,ACD) Lossless Join?

Preserve $B \rightarrow E$?

$Z := B$

$Z := B \cup ((B \cap AB)^{+} \cap AB) = AB$

$Z := AB \cup ((AB \cap AE)^{+} \cap AE) = ABE$

YES.

BCNF Example [271]

Billto = (AIRPORT,COMPANY,OFFICE)

If someone from company C charges a ticket at airport A, then send the bill to office O.

AIRPORT	COMPANY	OFFICE
lax	ibm	ibmsdbranch
sd	ibm	ibmnyoffice
pitt	ibm	ibmsdbranch

$AC \rightarrow O, O \rightarrow C$ Key: AO,AC

is already in 3NF since C is prime.

But there are duplicate CO pairs, would like to separate.

BCNF alg:

ACO
$AC \rightarrow O, O \rightarrow C$
Key: AO,AC

Branch off $O \rightarrow C$

OC
$O \rightarrow C$
Key: O

AO
Key: AO

$AC \rightarrow O$ has not been preserved.

BCNF and 3NF Decomposition [272]

Repeat of the last example:

ABCDE
$A \rightarrow B, B \rightarrow A, B \rightarrow E, AC \rightarrow D$
Key: AC

AB
$A \rightarrow B,B \rightarrow A$
Key: A,B

ACDE
$A \rightarrow E, AC \rightarrow D$
Key: AC

AE
$A \rightarrow E$
Key: A

ACD
$AC \rightarrow D$
Key: AC

BCNF Answer: (AB,AE,ACD)

3NF:

$A \rightarrow B, B \rightarrow A, B \rightarrow E, AC \rightarrow D$

Yields (AB,BE,ACD) with the key AC already contained.

University Example Again [273]

CTHRSG
$C \rightarrow T,CS \rightarrow G,HR \rightarrow C,HS \rightarrow R,TH \rightarrow R$
Key: HS

CSG
$CS \rightarrow G$
Key: CS

CTHRS
$C \rightarrow T,HR \rightarrow C,HS \rightarrow R,TH \rightarrow R$
Key: HS

CT
$C \rightarrow T$
Key: C

CHRS
$HR \rightarrow C,HS \rightarrow R$
$CH \rightarrow R$
Key: HS

CHR
$HR \rightarrow C,CH \rightarrow R$
Key: CH,HR

CHS
$HS \rightarrow C$
Key: HS

BCNF: (CSG,CT,CHR,CHS)

$TH \rightarrow R$ is not preserved

Summary of Design [274]

• choose attributes R
• specify dependencies F
• find lossless join/dependency preserving
  • BCNF if possible
  • 3NF otherwise
• FD's can be checked locally on updates
• FD's can be checked to minimize queries