Database Normalization Exercise

Question

I am learning the normalization of tables. But I am not quite sure if I did it correctly. I have created the following table in the UNF:

1NF

2NF

Is this correct? I am not so sure...

Answer 1

Normalization is the process by which we examine and produce effective table structures. Each table should represent a single entity (subject). No data value will be unnecessarily stored in more than one table. All non-key values are dependent on the primary key and only the primary key. Lastly, each table shall have no data anomalies. That said, normalization boils down to table design.

In order to qualify for 1NF the primary key must be identified, and the table must not have any repeating groups. That is, each row/column intersection contains one and only one value. Also, each non-key attribute must be dependent on the primary key.

Regarding your tables in 1NF:
The one on the left, the incident table: This is correct 1NF.

The one on the right also meets the requirements for 1NF. The responder_status depends on the primary key entirely or partly which is enough for 1NF.

In order to qualify for 2nd normal form, the table must be in 1NF. And it must have no partial dependencies.

Regarding your tables in 2NF: The first table on the left, incident, correctly qualifies. Note that converting to 2NF from 1NF is done only when the 1NF table has a composite primary key. Otherwise it is already in 2NF.

The middle table, I'm guessing is named incident_response (or something similar) also qualifies. It is in 1NF and it's primary key is a composite of incident_id and responder_id. It has no other attributes (columns), so partial dependencies are not a concern.

The last table on the right, responder, is incorrect. It is not in 1NF. Values are repeated in the primary key column responder_id. Furthermore, responder_status seems to be dependent on incident_id and not on responder_id. You will likely want to rethink the semantics of responder_status and rename it and/or place it in a different table. If it is intended to mean a responder is unavailable due to being assigned to an incident, you probably don't need the column at all since the incident_response table already captures the event of a responder being assigned to an incident.

A quick word about dependencies: How can we tell if a value is dependent on the primary key? Here's an example. If I look at incident_id I101, it always matches responder_status of 1. Likewise, every time incident I020 appears in the data it always has responder_status of 0. This indicates that responder_status depends on incident_id. Conversely, every time responder_id R010 appears it does not always have the same responder_status value. Sometimes it's 1 and another time 0. So it is not dependent.

For a table to qualify for 2NF, it must have no partial dependencies. Attributes in such tables must depend upon both primary key column values.

I hope this has helped to explain what normalization is and why it is done. Good luck.