Imagine a database with one table about dancing.
Names of different people can be identical, but we assume no people have the same name and the same birthdate. The same pair of dancers can dance several times.
| Dance# | MaleDancerName | MaleDancerDoB | FemaleDancerName | FemaleDoB |
|---|---|---|---|---|
| 1 | Brown | 3/7/1989 | Cortez | 5/2/1983 |
| 2 | Howard | 7/5/1978 | Taylor | 8/12/1990 |
| 3 | Brown | 1/4/1986 | Taylor | 8/12/1990 |
| 4 | Meyer | 2/1/1984 | Andrews | 11/10/1988 |
| 5 | Brown | 3/7/1989 | Cortez | 5/2/1983 |
Dance# is the primary key--and the only candidate key.
This database is in the first three normal forms:
1st Normal Form: The data is atomic.
2nd Normal Form: No non-prime attribute is dependent on any proper subset of any candidate key. ( A non-prime attribute of a table is an attribute that is not a part of any candidate key of the table. There is only one candidate key, Dance#, and it doesn't have a subset)
3rd Normal Form: All the attributes in the table are determined only by the candidate keys (there is only one of them, Dance#) and not by any non-prime attributes. The name of the dancer does not determine the Date of Birth - e.g. Brown in Dance1 and Brown in Dance3 are not the same person, they have different birth dates.
Although this table is in all three normal forms, it has update anomalies: If we find out that Dancer Taylor was actually born on 9/12/1990, we have to correct this information in Dance# 2 and Dance# 3.
A solution would be to split the table into two tables, one for dancers and one for dances.
However, just looking at the normal forms, the table seems to be okay.
Where am I mistaken?
