First Normal Form

Designing Database Tables – Part 3

Division 2

Forward: In this part of the series, we see how to obtain the First Normal Form from an un-normalized table.

By: Chrysanthus Date Published: 5 Aug 2012

Introduction

This is part 3 of my series Designing Database Tables. In this part of the series, we see how to obtain the First Normal Form from an un-normalized table. Remember, you have to obtain the first normal form from the table (notation) developed from a form. From the first normal forms, you obtain the second normal forms; from the second normal forms, you obtain the third normal forms. First Normal Form is abbreviated as 1NF.

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.

Pulling out Repeating Groups
You obtain the first normal form by pulling out repeating groups. You do not pull them out arbitrary; you have to use a technique. The table notation we ended up with in the previous part of the series, is:

RentingForm(TransID, RentDate, CustID, Phone, Name, Address, City, State, ZipCode, (CDID, NoTaken, Title, Price))

There is one repeating group in the notation. You first of all have to identify a primary key for the whole table and a primary key for the repeating group. If any of these primary keys are not there, you may have to introduce it. For the repeating group, the primary key is CDID; it is unique for each row, for all the four columns. For the whole table, you might be tempted to think that the primary key is CustID. Remember that this table notation or the whole form from which it was developed, is actually dealing with renting transactions. A customer may come today to rent a film and come tomorrow to rent another film. In this situation the same CustID will appear in two rows and so will not uniquely define the rows. The TransID uniquely defines the rows because each transaction is unique. So the primary key for the whole table is TransID.

When pulling out the repeating group, copy the primary key for the whole table as well. The above table leads to the following two tables:

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

and

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

Note how the primary key of the previous whole table has been introduced in the repeating group notation. It remains in the main table notation. A repeating group shows a one-to-many relationship between the main table and the repeating group. If none of the resulting tables has a repeating group, then they are in first normal form.

You have to give the new tables, names. The names should be about the data in their respective tables. RentingForm2 and RentingLine are the names given above. A table is said to be in first normal form if it does not have any repeating group. The above two resulting tables are in first normal form. The story does not end there: a table must satisfy at least the first, second and third normal forms before it can be used in a database software.

We have not even finished with repeating groups. What about multiple repeating groups in a table (notation). You can have independent multiple repeating groups; something like:

    Table(Key1…(Key2…)(Key3…))

You can also have nested repeating groups; something like:

    Table(Key1…(Key2…(Key3…)))

A table in first normal form must not have repeating groups whether or not the repeating groups are multiple. If multiple repeating groups exist, in the course of developing the first normal form, you have to pull them out as follows:

Independent Repeating Groups
For independent repeating groups, you just pull out each group, copying the primary key of the main table into each group. So,

    Table(Key1…(Key2…)(Key3…))

becomes,

        MainTable1(Key1…) , TableA(Key1, Key2…)     and    TableB(Key1, Key3…)

Nested Repeating Groups
With a nested repeating group, you pull out the repeating groups beginning from the outermost group, copying the primary key of the previous main group as you go along. So,

    Table(Key1…(Key2…(Key3…)))

becomes

        MainTable(Key1…) , TableA(Key1, Key2…) and TableB(Key1, Key2, key3…)

When developing the first normal form, you must check if independent and nested repeating groups are present.

Identifying Repeating Groups
The repeating group like the one (CDs) of the form we are using, is easy to identify. Others may not be easy to identify like in the following:

    EmployeesInfo(EmpID, HomePhone, OfficePhone, Name, Address, City, State, ZipCode)

This table notation is for employee data. There are two columns there for phone: HomePhone and OfficePhone. The phone columns can be turned into a repeating group especially when the number of employees, is large.

Some employees have just one phone. Others may have more than four: home, office, cellular, pager, etc. The above table is OK if no employee has more than two phones. If some employees have only one phone, then some phone cells would be left empty, for the above table. If the number of employees is large, then the above table is better written as follows, with a phone repeating group:

    EmployeesInfo(EmpID, Name, Address, City, State, ZipCode, (PhoneID, PhoneType, PhoneNumber))

A phone ID primary key column has been introduced. A cell value for phone type can be, home or office or cellular or pager, etc. In first normal form the above table notation becomes:

    Employees(EmpID, Name, Address, City, State, ZipCode)

and

    Phone(EmpID, PhoneID, PhoneType, PhoneNumber)

The phone numbers of all the employees are stored in the phone table, giving a one-to-many relationship between the employees table and the phone table. This is a more efficient way of keeping phone data when the number of employees is large. If an employee has 4 phones, his numbers will take 4 rows in the phone table. If an employee has 3 phones, his phone numbers will take 3 rows. If an employee has one phone, his phone number will take one row.

In the development of the first normal forms you have to make an attempt to identify non-obvious repeating groups.

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

First Normal Form

Designing Database Tables – Part 3

Division 2

Introduction

Related Links

Comments