2NF
[!example] A relation is in second normal form (2NF) if and only if:
- It is in 1NF.
- No non-prime attributes are functionally dependent on a subset of the candidate key(s). In other words, any column that’s not a key column is dependent on the whole information in the candidate key. ^[https://vertabelo.com/blog/normalization-1nf-2nf-3nf/]
2NF is a Data Normalisation form. It requires that all attributes must rely on the entire Candidate Key. In other words if an attribute relates to one key but not another this will not satisfy 2NF.
In this example we have a vending machine with drinks. Each drink has a price and an amount left. We could assume there would be ever more data in this table like the amount of coke in vending machine 2 and 3. This kind of structure may get confusing because only one of our two attributes relate to the entire Candidate Key.
| Vending machine | Drink | Price | quantity |
|---|---|---|---|
| 1 | Coke | 6.10 | 10 |
| 2 | Pepsi | 6.20 | 30 |
| 3 | Mount Dew | 5.80 | 22 |
| 4 | Sprite | 1.20 | 19 |
| 5 | Fanta | 3.30 | 12 |
The price of the drinks here does not relate to the vending machine, only the drink. However the quantity of drinks in each vending machine relates to both, which drink, and which vending machine. To clear this up we can pull out the pricing data into a separate entity and create a relationship between the tables through the foreign key. In this case drink.
| Vending Machine | Drink | Quantity |
|---|---|---|
| 1 | Coke | 10 |
| 2 | Pepsi | 30 |
| 3 | Mount Dew | 22 |
| 4 | Sprite | 19 |
| 5 | Fanta | 12 |
| drink | price |
|---|---|
| Coke | 6.10 |
| Pepsi | 6.20 |
| Mount Dew | 5.80 |
| Sprite | 1.20 |
| Fanta | 3.30 |
We now have two entities and all attributes relate to the entire candidate key. This therefor satisfies 2NF.