Second Normal Form

Sometimes a table requires a multiple part key. If so, then every attribute (column) must be dependent
upon the entire multiple part key and not just part of the key. There is one example in the insurance data
model (created by resolving the many-to-many relationship). Each CLAIM must have at least one
CLAIM_TREATMENT.
To build the key to this new entity, you will have to include the CLAIM_ID (key to the CLAIM entity) and
another key that will relate each row to a particular TREATMENT entity key. Deciding which attributes
belong with CLAIM and which belong with CLAIM_TREATMENT is done by assessing whether the attribute
can be uniquely identified by just the CLAIM_ID or whether you will have to know which specific TREATMENT
of that CLAIM. Therefore, in Figure 4-4, you see that TREATMENT_ID has been included in this entity.
CLAIM_DATE has been left in the CLAIM entity because it applies to the entire claim. Further analysis may
lead to additional attributes that are dependent upon this entity’s composite key, but frequently intersection
entities like this carry just the key values that related it to the two primary entities in order to resolve the
many-to-many relationship.

So, the rule of second normal form (2NF) is that, for any entity that has a multiple-column key, all data
attributes in that entity must be dependent upon the full key, not just part of that key. If the value of a
column could be determined strictly through knowing part of the key, then that column doesn’t belong
in this entity (or else you have not correctly defined the key structure.).
When you find that you need multiple columns to identify an instance of an entity, our rule not allowing
keys to be null now applies to each of the columns in the multipart key.