I've been trying to understand the three first normal forms of databases and seem to be missing something:
Imagine a database with one table consisting of dancesabout dancing. There are 5 attributes:
A Dance#, MaleDancerName, MaleDancerDateofBirth, FemaleDancerName, FemaleDancerDateofBirth
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 times.
A sample database could look like this:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+ + + + + +
+ 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# | 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-and the only candidate key.
Now, as far as I can tell, thisThis database is in the first three normal forms:
1.1st Normal Form: Yes: The data is atomic.
2.2nd Normal Form: Yes: 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)
3.3rd Normal Form: Yes: 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.
Now, althoughAlthough 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#2Dance# 2 and Dance#3Dance# 3.
An obviousA solution would be to split the table into two tables, one for dancers and one for dances.
However, just looking at the Normal Formsnormal forms, the table seems to be okay.
AmWhere am I mistaken somewhere?
