Second Normal Form

A big problem with the Stock table is redundancy. What we really have is the single fact that IBM's price is 100 but we have to express that fact for each customer that owns IBM. Second and third normal forms help ensure that you state all important facts exactly once (instead of multiple times). 2NF also ensures that facts don't disappear.

A table is in 2NF if 1) it is already in 1NF, and 2) every attribute in the table is functionally determined by the whole identifier (and not just a part of the identifier).

Rules to get to 2NF
1 Find some set of attributes, D, that are dependent on only part of the primary identifier, that is, some K that is a strict subset of P. Thus, we have K —> D.
2 Create a new relation composed of each attribute that is in K or D.
3 The primary identifier of this new relation is K.
4 Remove the attributes in D from the original relation.
5 Repeat until you've removed all attributes that are dependent on only part of the primary identifier.

In the Stock example, we know that Stock functionally determines Curr Price but that Stock is not the whole identifier — the whole primary identifier is [Cust ID, Stock]. So we're not in 2NF.

The solution is to break up the relation into three relations. All attributes in each relation must depend on the whole identifier in that relation. Since Curr Price is only dependent on part of the primary identifier (i.e., Stock), this information must be moved to another relation.

Problem 2.4

In the Stock example, what are the other attributes that are dependent on only part of the primary identifier? Don't forget: you should attempt to do these problems before you read the answers.

Answer to Problem 2.4

Problem 2.5

Visualize what the data in these tables look like: what are the column names, and what data goes in each row. Verify that we have achieved 2NF.

Answer to Problem 2.5

Problem 2.6

Is the table below in 2NF? Why or why not? If it is not, create tables in 2NF.

Best selling books
Bookstore Location Store's best seller
Border's AA How to train a dog
Border's Philadelphia Fly like a turkey
Little Professor AA How to train a dog

Answer to Problem 2.6

Problem 2.7

Can violations of 2NF exist when you don't have a concatenated identifier (i.e., one made of two or more attributes)?

Answer to Problem 2.7

Continue with 3rd normal form.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License