There are many rules that help in designing a good relational database. Among them are called the normal forms. There are altogether 5 normal forms. For our purpose, we only need to know the first three.
The First Normal Form
(1NF)
A table is in 1NF if each row is unique and each field stores a single value (rather than a collection of values).
All of the tables we have seen and designed so far are in 1NF. Below is an example of a table that is not in 1NF. Can you see why?
CUSTOMER_ID |
LAST_NAME |
FIRST_NAME |
PHONES |
|
JS1 |
Smith |
John |
310-456-4022 (W) 310-444-8712 (H) |
|
PH1 |
Pocahontas |
|
310-432-2813 (H) |
For the above table, it is difficult to look up phone numbers and to make any change to them. This is one of the reasons why the table design is bad.
The Second Normal
Form (2NF)
A table is in 2NF if
· it is in 1NF, and
· each row has a primary key (which can be a combination of several fields), and
· each non-key field depends on the entire primary key and not on only parts of the key.
Below is a table that is not in 2NF.
COURSE |
SECTION |
INSTRUCTOR |
COURSE NAME |
|
Cosc 250 |
1 |
Nguyen |
Computer Science for Business |
|
Cosc 250 |
2 |
Warford |
Computer Science for Business |
|
Cosc 250 |
3 |
Nguyen |
Computer Science for Business |
|
Cosc 250 |
4 |
Zimmerman |
Computer Science for Business |
|
Cosc 480 |
1 |
Nguyen |
Programming Languages |
The primary key for the above table is the combination (COURSE, SECTION). The Instructor field depends fully on this compound key. However, the DESCRIPTION field depends only on Course. As a result, the DESCRIPTION field has the problem of data redundancy. To fix this problem, we need to break up the above table into separate tables related via the COURSE field.
Courses Table
COURSE |
COURSE NAME |
|
Cosc 250 |
Computer Science for Business |
|
Cosc 480 |
Programming Languages |
Sections Table
COURSE |
SECTION |
INSTRUCTOR |
|
Cosc 250 |
1 |
Nguyen |
|
Cosc 250 |
2 |
Warford |
|
Cosc 250 |
3 |
Nguyen |
|
Cosc 250 |
4 |
Zimmerman |
|
Cosc 480 |
1 |
Nguyen |
The Third Normal Form
(3NF)
A table is in 3NF if
· it is in 2NF, and
· there is no transitive dependency, that is, each non-key field depends directly on the primary key field(s) only.
Below is a sample of a table that is not in 3NF. Can you see why?
EMP_ID |
EMP_NAME |
JOB_CODE |
JOB_TITLE |
DATE_HIRED |
JOB_DESCRIPTION |
|
A120 |
Jones |
1 |
Programmer |
9/17/95 |
Write computer code. |
|
A721 |
Harpo |
1 |
Programmer |
7/17/93 |
write computer code. |
|
B270 |
Garfunkel |
2 |
Analyst |
1/12/95 |
Perform cost analysis. |
|
C273 |
Selsi |
3 |
Designer |
5/21/94 |
Design graphics. |
In the above table, the primary key is EMP_ID. However, JOB_TITLE and JOB_DESCRIPTION depend on JOB_CODE and not on EMP_ID. This causes the problem of data redundancy in JOB_TITLE and JOB_DESCRIPTION. As a result, this is a bad design. To fix this problem, we must break the above table into two separate tables related to each other via the JOB_CODE field.
Employees Table
EMP_ID |
EMP_NAME |
JOB_CODE |
DATE_HIRED |
|
A120 |
Jones |
1 |
9/17/95 |
|
A721 |
Harpo |
1 |
7/17/93 |
|
B270 |
Garfunkel |
2 |
1/12/95 |
|
C273 |
Selsi |
3 |
5/21/94 |
Jobs Table
|
JOB_CODE |
JOB_TITLE |
JOB_DESCRIPTION |
|
1 |
Programmer |
Write computer code. |
|
2 |
Analyst |
Perform cost analysis. |
|
3 |
Designer |
Design graphics. |
What kind of relationship link should you create between these two tables?