CS457 Syllabus & Progress

### Intro to the 3NF - How to detect "good"/"bad" relations

• The definition of the 3NF

• Definition of the 3NF :

• A relation R is in 3NF iff:

For every functional dependency

 X → B

in relation R, one of the following must be true:

 X   is a superkey ,            or B   is a key attribute (i.e., B is part of some key )

(If one of the above is true, then the functional dependency is "good")

• Comment:

• If condition 1 (X is a super key) is true, then:

 X → B is an natural functional dependency (Because a super key will functionally determine every attribute in a relation)

• Condition 2 was included in the 3NF definition because:

 Codd (the inventor of the Relational Model) did not want to break up keys in a relation....

• Example of a violation of the 3NF

• Consider the following relation:

 ``` Employee1(SSN, FName, LName, PNumber, PName, Hours) Functional Dependencies: SSN → FName, LName PNumber → PName SSN, PNumber → Hours ```

• The key(s) of the relation is:

 (SSN, PNumber)

• Relation Employee1 is not in 3NF because of the following 3NF violation:

• The functional dependency

 SSN → FName, LName

violates the 3NF because:

 SSN is not a super key

• Also, the functional dependency

 PNumber → PName

violates the 3NF because:

 PNumber is not a super key

• How to solve normal form violations: decomposition

\$64,000 Question:

 How can you remove the violation of a normal form criteria

 Decompose (= break up) a relation into two or more relations.

Example:

 ``` Employee1(SSN, FName, LName, PNumber, PName, Hours) SSN → FName, LName causes 3NF violation A (bad !!!) decoposition: R1(PNumber, PName, Hours) R2(SSN, LName, FName) ```

Note:

 The decomposition suggested by the previous example was a bad decomposition