IST366: Example 4.

Third Normal Form

For simplicity, assume the relation has one key
Consider the following "bad" relation:
```
   Employee2(SSN, FName, LName, DNumber, DName, MgrSSN)
```
Represents employee, department and works_for information

Sample content of the Employee2 relation:

SSN	FName	LName	DNumber	DName	MgrSSN
111-11-1111	John	Smith	5	Research	123-45-6789
111-22-3333	Jane	Doe	5	Research	123-45-6789
123-45-6789	Ian	Flemming	1	HeadQ	123-45-6789

Relation Employee2 has anomalies...
- Key of relation: (SSN)
- Insert: cannot insert a new department that does not have any employees (NULL values in the key is NOT allowed in the relational model)
- Delete: deleting "Ian Fleming" will unintentionally delete information of department "HeadQ"
Is relation Employee2 in 2NF ?
- Key: (SSN)
- Non-Key attributes:
```
 FName, LName, DNumber, DName, MgrSSN
 
```
- Every non-key attribute is fully dependent on the primary key... Yes, Employee2 is in 2NF...
- So what is wrong ???
- 2NF is not string enough...
A relation is in 3NF if:
- Relation is in 2NF, and
- every non-key attribute is non-transitively dependent on the primary key
Relation Employee2 is not in 3NF because:
- Primary key is (SSN)
- There is a transitive dependency:
```
	 SSN -> DNumber
	 DNumber -> DName, MgrSSN
   
```

Now you should what to do about it?

But to make things more interesting, let us try to decompose from a more "formal" perspective:

	R = (SSN, FName, LName, DNumber, DName, MgrSSN)
	R = (A, B, C, D, E, F)

	A   -> B, C, D, E, F
	D   -> E, F

Step 0: Is (A, B, C, D, E, F) in 3NF ?
In other words:
- Does every non-key attribute non-transitively ("directly") dependent on the primary key ?
Once again, we CANNOT answer this question without first finding the key(s) !!!

Step 1: Find the key(s)

	A⁺ = A, B, C, D, E, F  => A is the key

Step 2: find non-key attributes (easy after you found the key...)
```
	B, C, D, E, F
	
```

Step 3: is (A, B, C, D, E, F) in 3 NF ?

	No:   A -> E, F   is transitive through D -> E, F

Step 4: Decompose: remove the violating FD (remove D -> E, F) !
Compute: D⁺ to find all the attributes that "belongs" to D
```
	D⁺ = D E F
	
```
Decompose (A, B, C, D, E, F) into:
```
	R₁ = (D, E, F)		   (D⁺)
	R₂ = (A, B, C, D)		(Again, we need to satisfy Lemma 2 !)
	
```

Step 5: Check !

	R₁ = (D, E, F)		  Key = D  (D -> EF)	  is in 3NF
	R₂ = (A, B, C, D)	   Key = A  (A -> BCD)	 is in 3NF !!

Now, plug in the real world example:

  A = SSN		D = DNumber
  B = FName	  E = DName
  C = LName	  F = MgrSSN

  A   -> B, C, D, E, F
  D   -> E, F

  Result:

  (SSN, FName, LName, DNumber, DName, MgrSSN)

  Decomposed into:

  (SSN, FName, LName, DNumber)
  (DNumber, DName, MgrSSN)

So, the correct tables now are:

SSN FName LName DNumber
111-11-1111 John Smith 5
111-22-3333 Jane Doe 5
123-45-6789 Ian Flemming 1

DNumber DName MgrSSN

5 Research 123-45-6789

1 HeadQ 123-45-6789