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:
- X → B
- X is a
superkey ,
or
- B is a key attribute (i.e., B is part of some key )
-
For every
functional dependency
in relation R, one of the following must be true:
(If one of the above is true, then the functional dependency is "good")
- A relation R
is in
3NF iff:
- 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)
-
X → B
is
an
natural functional dependency
- 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....
- If condition 1
(X is a
super key) is
true, then:
-
Definition
of the
3NF :
-
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 of the
relation Employee1 is:
- (SSN, PNumber)
- The
key of the
relation Employee1 is:
- Previously,
we saw that
relation Employee1
has anomalies:
SSN FName LName PNumber PName Hours 111-11-1111 John Smith pj1 DBApplet 20 111-11-1111 John Smith pj2 WebServer 10 111-22-3333 Jane Doe pj1 DBApplet 5
- 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
which causes the following anomaly: (duplication of infomation)
SSN FName LName PNumber PName Hours 111-11-1111 John Smith pj1 DBApplet 20 111-11-1111 John Smith pj2 WebServer 10 111-22-3333 Jane Doe pj1 DBApplet 5
- Also,
the functional dependency
- PNumber → PName
violates the 3NF because:
- PNumber is not a super key
which causes the following anomaly: (duplication of infomation)
SSN FName LName PNumber PName Hours 111-11-1111 John Smith pj1 DBApplet 20 111-11-1111 John Smith pj2 WebServer 10 111-22-3333 Jane Doe pj1 DBApplet 5
- The functional dependency
- Consider
the following relation:
-
How to solve normal form violations: decomposition
- How can you remove the violation of a normal form criteria
- Decompose (= break up) a relation into two or more relations.
- How can
decomposition
remove
duplication of
information:
- Because
SSN is
not a
key in the
relation Employee1,
we can have multiple tuples
with the same SSN value:
SSN FName LName PNumber PName Hours 111-11-1111 John Smith pj1 DBApplet 20 111-11-1111 John Smith pj2 WebServer 10 111-22-3333 Jane Doe pj1 DBApplet 5 The function dependency SSN → FName,LName caused information duplication
- Suppose we
split (= break up)
the relation Employee1
into
R1(SSN, FName, LName) and
other relations:
where SSN is a key in relation R1.
Then:
- The duplication will be removed because the model will not allow multiple tuples with the same (key) value for the key SSN
- Because
SSN is
not a
key in the
relation Employee1,
we can have multiple tuples
with the same SSN value:
$64,000 Question:
Answer:
-
Good and bad decompositions
- Fact:
- There are good decompositions and
- There are bad decompositions
- We need to learn more about
the effect of
breaking a
relation into
2 of more relations....
- Fact: