Databases and SQL: Question 7

Worked solution

Part (a): First Normal Form

First Normal Form (1NF) requires every field in a record to hold a single, atomic value, with no repeating groups - no field is allowed to hold more than one value for a single record.

The PET table already satisfies 1NF: for any given pet, PetID, PetName, Species and OwnerID each hold exactly one value. There is no field such as “Species1, Species2” or a list of multiple owners crammed into one record, so there is no repeating group to remove.

[2 marks]: [1] for correctly defining 1NF (atomic fields, no repeating groups), [1] for correctly explaining that every field in PET already holds a single value.

Part (b): Why PET is automatically in 2NF

Second Normal Form (2NF) requires that every non-key field depends on the whole of the primary key - not just part of it. A field that depends on only part of the primary key is called a partial dependency.

Crucially, a partial dependency can only exist when the primary key itself is made up of more than one field - there has to be a “part” of the key available for a non-key field to depend on instead of the whole thing. PET’s primary key, PetID, is a single field, so there is no smaller “part” of it to depend on: every non-key field in PET (PetName, Species, OwnerID) can only ever depend on the whole key, PetID, because that is the only key there is.

This means that any table already in 1NF whose primary key is a single field, not a composite key, is automatically in 2NF - there is no possible partial dependency to check for.

[2 marks]: [1] for explaining that a partial dependency requires a composite (multi-field) primary key, [1] for correctly applying this to PET’s single-field key PetID to conclude it is automatically in 2NF.

Part (c): Why merging OwnerName and OwnerPhone into PET would break 3NF

Third Normal Form (3NF) requires that every non-key field depends directly on the primary key, and not indirectly through another non-key field.

If OwnerName and OwnerPhone were added straight into PET, they would not actually describe the pet itself - they describe the owner. In other words, they would not depend directly on PetID; they would depend on OwnerID (which owner the pet belongs to determines the owner’s name and phone number), and OwnerID is itself only a non-key (foreign key) field within PET, not part of its primary key.

This chain - PetID determines OwnerID, and OwnerID determines OwnerName/OwnerPhone - is exactly what a transitive dependency is: a non-key field depending on the primary key only indirectly, via another non-key field. This is why OwnerName and OwnerPhone are correctly kept in their own OWNER table instead, linked to PET only via the foreign key OwnerID.

[2 marks]: [1] for correctly identifying that OwnerName/OwnerPhone would depend on OwnerID rather than directly on PetID, [1] for correctly naming this a transitive dependency and explaining why it breaks 3NF.

Final answers

• (a) 1NF: every field holds a single atomic value, no repeating groups; PET already satisfies this.
• (b) PET is automatically in 2NF because its primary key PetID is a single field, so no partial dependency (which requires a composite key) can exist.
• (c) Merging OwnerName/OwnerPhone into PET would create a transitive dependency (PetID -> OwnerID -> OwnerName/OwnerPhone), breaking 3NF.