Third Normal Form
Designing Database Tables – Part 5
Division 2
Forward: In this part of the series, we see how to develop a table into the third normal form, from a table in the second normal form.
By: Chrysanthus Date Published: 5 Aug 2012
Introduction
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 Third Normal Form
A table is in third normal form if every non-key column depends on nothing but the key and if the table is already in 2NF and 1NF.
The Example
We shall continue to develop the tables got from the Renting form. In the previous parts of the series, we have three tables in 2NF. The names of the 2NF tables are RentingForm2, CDs and CDsRented. We have to check each of these tables to verify if any happens to already be in 3NF. If any is not, we take it to 3NF.
Checking if a Table needs to go to Second Normal Form
The three table notations from the previous part of the series are:
RentingForm2(TransID, RentDate, CustID, Phone, Name, Address, City, State, ZipCode),
CDs(CDID, Title, Price)
and
CDsRented(TransID, CDID, NoTaken)
The last table is already in 3NF: it has only one non-key column that depends only on the entire composite primary key. The second table is already in 3NF; it has two non-key columns that depend only on the key, TransID. That is, a value in the CDID column determines one value in the Title column and also one value in the Price column.
The first table is in 2NF but it is not in the third normal form. There are like two groups in the table. The columns, RentDate and CustID form one group and each of them depends on the key; that is, a value in the TransID column determines one value in the RentDate column and also one value in the CustID column. Phone, Name, Address, City, State and ZipCode columns form the other group. All these columns are dealing with the complete address of the customer. They do not depend on the key, TransID. They depend on the non-key column, CustID. When you know the CustID, you know the values in the columns of the second group, in the sense that there is correspondence between each of the values in the CustID column and each of the values in the columns of the second group.
The table notation for the RentingForm2 2NF table is,
RentingForm2(TransID, RentDate, CustID, Phone, Name, Address, City, State, ZipCode),
To develop the 3NF from a 2NF, pull out the non-key columns that do not depend on the key, copying the non-key column that they depend on, into a new table. Then you check if the resulting tables are in 3NF. Doing that to the RentingForm2 table, results in:
Renting(TransID, RentDate, CustID)
and
Customers(CustID, Phone, Name, Address, City, State, ZipCode)
The non-key columns that the other columns depend on, becomes the primary key in the pulled out table, while in the main remaining table it becomes a foreign key. In table notation, primary keys are normally underlined while foreign keys are underlined with dash lines.
Final Tables
The final tables are:
CDs(CDID, Title, Price),
CDsRented(TransID, CDID, NoTaken),
Renting(TransID, RentDate, CustID),
and
Customers(CustID, Phone, Name, Address, City, State, ZipCode)
These tables are all in 3NF. In your database design you can end at the 3NF. There are other normal forms beyond the third normal form; I will not go into those. Ending at 3NF will give you good results. Ending beyond 3NF will give you perfect results. The difference in errors between 3NF and beyond 3NF is not much, though sometimes these errors can be important.
Keys are used by database software to link tables together. Open the following web page to see the linked class diagrams of the four tables above.
When a table does not have repeated groups, it is in 1NF. When all non-key columns depend on the entire composite (or single column) primary key, the table is in 2NF. When all non-key columns depend on the entire primary key and do not depend on any non-key columns, the table is in 3NF. When designing a database, your tables must be in at least 3NF.
Naming Tables
In one of the previous parts of the series, when we arrived at the 1NF, the names of the tables were, RentingForm2 and RentingLine. These names are not very meaningful. However, you can allow them like that because the tables are not in their final forms. Well, when your table is at the third normal form it should have a name that is meaningful. If at 3NF your table name is not meaningful, then you must have made a mistake in the development process.
Combining Tables
In practice, when you design tables from many forms (and reports), some forms may produce tables that other forms do not produce. You will have to bring the tables together. You will realize that some tables produced by different forms are the same in the sense that they have the same primary keys and possibly the same names; the meaning of the names should be the same if the names are slightly different (the meaning of some column names in the two tables should be the same). In such a case, just combine the tables, column-wise, without duplicating the columns. Of course chose one name for the combined tables.
In other situations you may need to add new columns to a table for convenience; do this on condition that the 1NF, 2NF and 3NF rules are respected.
That is it for this part of the series; we take a break here and continue in the next part, with a new way of determining tables.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT