| Lesson 7 | Second normal form |
| Objective | Explain the requirements for second normal form. |
Relations in 1NF can have significant design flaws resulting in reduced efficiency. The goal of second normal form (2NF) is to correct
these flaws.
Consider the following table, which might be used to list cars available for sale at different dealerships. The relation assumes that a given car can only be stocked by a single dealer.
second normal form (2NF): A table is in second normal form when every attribute is functionally dependent on the entire primary key.
Consider the following table, which might be used to list cars available for sale at different dealerships. The relation assumes that a given car can only be stocked by a single dealer.
second normal form (2NF): A table is in second normal form when every attribute is functionally dependent on the entire primary key.
Note that, in this example, values in the CarID field are equivalent to book ISBN's. The CarID denotes a particular make and model of
car, but not a specific instance of that make and model.
In relational notation, this table would be written as:
Car (CarID, Color, Make, Model, Year, DealerID, DealerName)
The problem with this relation is that the fields relating to a car's dealer are not functionally dependent on the primary key (CarID). If any car can be acquired from more than one dealer, you will waste space by creating a second relation to save information about that car and its second dealer.
For a relation to be in 2NF, every descriptor (non-key column) must be functionally dependent on every primary-key column in the relation.
The next lesson defines functional dependencies .Car (CarID, Color, Make, Model, Year, DealerID, DealerName)
The problem with this relation is that the fields relating to a car's dealer are not functionally dependent on the primary key (CarID). If any car can be acquired from more than one dealer, you will waste space by creating a second relation to save information about that car and its second dealer.
For a relation to be in 2NF, every descriptor (non-key column) must be functionally dependent on every primary-key column in the relation.
functional dependency: A one-way relationship between two attributes so that, for any unique value of one attribute, there is only one value of the other attribute.
Two Relations - Exercise
Before moving on to the next lesson, click the Exercise link below to reinforce your understanding of the requirements of 1NF and 2NF.
Two Relations - Exercise
Two Relations - Exercise
