This exercise asked you to match first, second, and third normal forms to their respective requirements.
The proper matches are listed below:
- First normal form: The relation cannot contain any repeating groups.
- Second normal form: Every field in the relation must be functionally dependent upon the entire primary key.
- Third normal form: The relation cannot contain any transitive dependencies.
A database is in second normal form if it satisfies the following conditions:
- It is in first normal form
- All non-key attributes are fully functional dependent on the primary key
In a table, if attribute B is functionally dependent on A, but is not functionally dependent on a proper subset of A, then B is considered fully functional dependent on A.
Hence, in a 2NF table, all non-key attributes cannot be dependent on a subset of the primary key. Note that if the primary key is not a composite key, all non-key attributes are always fully functional dependent on the primary key.
A table that is in 1st normal form and contains only a single key as the primary key is automatically in 2nd normal form.
A database is in third normal form if it satisfies the following conditions:
- It is in second normal form
- There is no transitive functional dependency
By transitive functional dependency, we mean we have the following relationships in the table: A is functionally dependent on B, and B is functionally dependent on C. In this case, C is transitively dependent on A via B.