Second Normal Form

Designing Database Tables – Part 4

Division 2

Forward: In this part of the series, we see how to obtain the Second Normal Form for a database.

By: Chrysanthus Date Published: 5 Aug 2012

Introduction

This is part 4 of my series Designing Database Tables. In this part of the series, we see how to obtain the Second Normal Form for a database. Second Normal Form abbreviated, 2NF, is developed from First Normal Form. INF is obtained by pulling out any repeating group from the raw table notation. After that both the pulled out repeating group and the main remaining group are in 1NF. You then have to go to both first normal forms and check if they happen to already be in second normal form. If any of them does not happen to be in second normal form, take it to second normal form as explained below.

Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.

Definition of Second Normal Form
A table is in second normal form if each non-key column depends on the entire key and not just part of the key. Turning a first normal form table into second normal form can only be done if the first normal form table has a composite key as primary key. If a first normal form table does not have a composite primary key, it means it happens to already be in second normal form.

The Example
We shall continue to develop the tables got from the Renting Form. In the previous part of the series, we have two tables in 1NF. The names of the 1NF tables are RentingForm2 and RentingLine. We have to check each of these tables to verify if any happens to already be in 2NF. If any is not, we take it to 2NF.

Table to go to Second Normal Form
The two table notations from the previous part of the series are:

RentingForm2(TransID, RentDate, CustID, Phone, Name, Address, City, State, ZipCode)

and

RentingLine(TransID, CDID, NoTaken, Title, Price)

The table, RentingForm2, has just one key column, which is TransID as primary key; it happens to already be in 2NF. The table, RentingLine, has two columns, which are TransID and CDID as primary key. Here we have a composite key as primary key. So RentingForm2 is already in 2NF. We then have to check if RentingLine is in 2NF. If it is not, we take it to 2NF. The question of second normal form can only be asked on a table that has a composite primary key. It cannot be asked on a table that has a single column primary key. If a 1NF table has a composite key, it does not necessary mean that it has to be taken to 2NF. You first have to go to the table and check if the non-key columns depend on the entire key and not just part of the key.

Checking if a Table needs to go to Second Normal Form
We need to check if the 1NF table, RentingLine has to go to 2NF. The first thing we know is that the primary key is a composite key. Does each of the non-key columns depends on the two columns (entire key) of the primary key or depends just on one of the primary key columns (part of the key). That is what we have to verify. If any non-key column depends on just part of the composite primary key, then that table has to go to the second normal form.

Checking:
The table is,

RentingLine(TransID, CDID, NoTaken, Title, Price)

- The column, NoTaken depends on both columns of the primary key. The number of copies of the same CD that the customer takes home depends on the two columns. The customer comes to the CD renting store at a particular time and carries out a transaction. So NoTaken depends on TransID. NoTaken also depends on CDID because the CDID identifies a particular film (burned in the CD), which is of interest to the customer. If the customer finds a film very interesting, she can take several copies to share them with her family and friends.
- The column, Title depends only on the CDID part of the composite key. This is because a CDID identifies a film burned in a CD, by title; a CDID and title has a direct connection.
- The column, Price can depend on the TransID or CDID or both (see explanation below). For simplicity, let us say that it depends only on the CDID. So for this project, Price depends only on CDID.

Obtaining the Second Normal Form
To take a table to second normal form, you pull out the non-key columns that depend on only part of the key, copying the part key as well into the new table. The part key becomes the primary key of the pulled out table. For the above table, the pulled out table is,

CDs(CDID, Title, Price)

and the main table remaining is,

CDsRented(TransID, CDID, NoTaken)

You have to give the new tables, names. I have given the name, CDs to the pulled out table and the name, CDsRented to the main remaining table. The pulled out CDs table is in 2NF because its primary key has just one column. The CDsRented table is also in 2NF; its non-key columns, which is just NoTaken depends on the entire composite primary key.

In the development of the tables so far, you now have three tables, which are the RentingForm2, CDs and CDsRented. These three tables are in 2NF. The non-2NF table, RentingLine, which was only in 1NF, gave rise to the CDs and CDsRented tables. Both of these two new tables are in 2NF.

Where to store the Price
In the course of designing a business database, most designers quickly come to the problem of where to store the price. I will use the above price situation for explanation. When developing the 2NF above the question is “does price depend on only TransID or only CDID or both”. The answer depends on the business rules for your CD store.

You can have a rule where the price is determine by the days of the week as follows: During the weekend, more people want to relax and so the demand for renting CDs is high, so you make the weekend prices high. During the working days the demand for renting CDs is low so you make the prices low for the working days. In this case the price depends on the transID (transaction ID); the price is determined by the time (of week) the transaction of renting, takes place. Assuming that the price depends on TransID alone, then the price column will have to be pulled out with TransID copied to form a new 2NF table.

You can make a rule that the price is determined only by the popularity of the film, (assuming that you have your way of knowing that a film is popular before you fix the price). In this case, the price depends only on the CDID (indirectly only on the title). This is the situation of the CDs 2NF above.

In practice the price would depend on both the TransID and the CDID. In this situation, for the above tables, the price column would be in the CDsRented table. For convenience, put the price also in the CDs table, so that there it would be the least price. The decision to put the price in the CDs table should be taken after you have got all your tables in the third normal forms. Taking that decision now may lead to problems as you continue to analyze your tables to take them to the third normal forms, which we have not yet arrived at. The value for the price would be different in the two tables for the same CD. The one in the CDsRented table is not the least price and it depends on time of the week and popularity.

Dependence
We have used the word, “depend” in the development of the second normal form. This word is very important in the development of the second and third normal forms. We say, a column B, depends on A if and only if each value of A determines one value of B. So, in the development of the second normal form, we ask if every non-key column depends on the entire key, meaning that the entire key should determine one value in each non-key column. Any non-key column for which this condition does not hold, has to be removed as we did above.

That is it for this part of the series, we take a break here and continue in the next part.

Chrys

Broad Network

Related Articles

Second Normal Form

Designing Database Tables – Part 4

Division 2

Introduction

Related Links

Comments