Third Normal Form

Once an entity is in 2NF, the final test of purity of design that is  performed looks for transient dependencies
among the entity’s attributes. In this third normal form (abbreviated as 3NF), you verify that each attribute is
really dependent upon the key as opposed to being dependent upon some other attribute that is dependent
upon the key. Did that make sense? Let’s look at a violation of 3NF as a way to better understand it.
Look back at Figure 4-3. There’s another questionable attribute in the first-pass POLICY entity attribute list.
Does ANNUAL_MAX_OUT_OF_POCKET_EXP really belong here? Is this dependent upon the key, POLICY_ID?
Perhaps. But it may be true that this value changes each year as the policy is renewed. If so, the value of
ANNUAL_MAX_OUT_OF_POCKET_EXP is dependent upon the POLICY_ID and the year. Since you may need
to process CLAIMs from one year during the next year, it might be necessary to keep both the current year
and some previous years’ values in the database. This attribute, and perhaps some others, really should be
put into a new entity, POLICY_YEAR. Further discussion of requirements will be needed to know for sure.
Let’s continue our examination of Figure 4-3’s proposed list of attributes just a little longer. What about
AGENT_NAME? It can be argued that the agent for any given policy should be known once you identify
the specific policy, so there isn’t a clear violation of first normal form. However, as you continue to flesh
out the design, you are likely to find that you need an AGENT entity to which the POLICY entity will be
related. When that happens you will want to carry the key (such as AGENT_ID) within the POLICY entity
and use AGENT_NAME as a non-key attribute within the AGENT entity. This would be a better 3NF design
if you are going to track any other information about agents besides their name.