Algorithm to Decompose R into 3NF [256]

Input: Relation schema R(A1..An) and set of FD's F with Key K

Output: A dependency perserving decomposition of R such that each subschema is in 3NF and they have the lossless join property.

Method:

• convert F to a minimal cover
• if one FD involves A1..An, then output R
• if an A is not in any FD then make separate table A
• for each $X \rightarrow A$, make XA
• for $X \rightarrow A1..Am$, make XA1..Am
• if key K is not among XA's then make K

Since each FD generates a subschema, perservation is guaranteed.

Since the key is forced to be present in a subschema or its own subschema, there is the `glue' for a lossless join.

The last two examples were minimal covers, and the decomposition above was followed.

Design: University Database [257]

C=course, T=teacher, H=hour,

R=room,S=student,G=grade and FD's F:

1. $C \rightarrow T$ each course has one teacher

2. $HR \rightarrow C$ only one course can meet in a room at a time

3. $HT \rightarrow R$ a teacher can be in only one room at a time

4. $CS \rightarrow G$ each student has one grade in each course

5. $HS \rightarrow R$ a student can be in only one room at a time

Key: HS since $(HS)^{+} = HSRCTG$

R is not in 3NF since 1,2,3,4 violate it.

1..5 form a minimal cover. Try to remove

1. $C^{+} = C$ which does not include T

2..5 similar

The 3NF decomposition is (CT,HRC,HTR,CSG,HSR) which includes the key HS.

Design: Verify Lossless Join [258]

The 3NF decomposition is (CT,HRC,HTR,CSG,HSR) which includes the key HS.

C	T	H	R	S	G
c	t	h1	r1	s1	g1
c	t1	h	r	s2	g2
c1	t	h	r	s3	g3
c	t2	h2	r2	s	g
c2	t3	h	r	s	g4

Chase using $C \rightarrow T,HR \rightarrow C,CS \rightarrow G,C \rightarrow T$

C	T	H	R	S	G
c	t	h1	r1	s1	g1
c	t	h	r	s2	g2
c	t	h	r	s3	g3
c	t	h2	r2	s	g
c	t	h	r	s	g

Row 5 is all distinguished, hence lossless join.

Design: Summary [259]

3NF

• reduces redundancy and anomalies
• perserves dependencies, to be proven
• has lossless join

Now

• Two algorithms to show dependency preservation
• Boyce-Codd Normal Form BCNF
  • Definition
  • Algorithm
  • Example
  • Compare with 3NF
• use known FD's to minimize tableau