Article
Concepts of Database Design and Management
Second Normal Form
First, lets just say this. The table is automatically 2NF if its Primary Key contains only one column. That was easy, wasn't it? But then, if your Primary Key has more than one column, read on.
Lets jump right into the table here. Primary Keys are Order Number (Order #) and Part Number (Part #).
| Order # | Order Date | Part # | Part Desc. | Number Ordered | Price |
| 5245 | 3/02/99 | 35465 | Gas Key | 1 | $10 |
| 5246 | 3/02/99 | 65466 | Lawn Chair | 4 | $25 |
| 5246 | 3/02/99 | 65473 | Picnic Table | 1 | $30 |
| 5247 | 3/02/99 | 44654 | Lawn Darts | 1 | $45 |
| 5248 | 4/02/99 | 44665 | Volleyball | 3 | $20 |
At first glance, everything is okay with this table. However, there are some problems that need to be resolved. Lets first map out the functional dependencies.
Order Number -> Order Date
Part Number -> Part Description
Order Number, Part Number -> Number Ordered, Price, Order Date, Part Description
As you can see, this is a big mess, and needs to be taken care of. In fact, there are other problems with this table as well.
First, a change to the Part Description in this table would also mean a change in other tables, like the Products table. This is obviously cumbersome to code, and cumbersome to run.
Second, you could very well have different descriptions for the same part. This is simply not acceptable in a business environment.
This is because Part Description is dependent on only part of the primary key, and that is Part Number. Part Description is therefore a nonkey attribute. A nonkey attribute is simply a column that is not a part of the primary key.
This relates specifically to the 2NF definition, which is a table that is in the 1NF and no nonkey attribute is dependent on only a portion of the primary key. This of course reaffirms the fact that if the table only contains one column in its primary key, it is in second normal form.
So, how do we solve this problem? Well, we already solved this problem above. Here are the three new tables for the one above, with the first part being the primary keys and the second part being the other fields in the table.
Order Number -> Order Date
Part Number -> Part Description
Order Number, Part Number -> Number Ordered, Price
Those are our new tables. Notice that I took out the Order Date and the Part Description, as there is no need to use data that is already stored in other tables. Also note that you only have one description for each part. Quality database design needs no redundant data.