5. 1NF Examples 2

Suppose a designer has been asked to compile a database for a fan club web site. Fans visit the web site to find like-minded friends.

The entities to be stored are

This indicates that each band has many fans. Each person is a fan of only one group.

The database needs to be in first normal form.

First Attempt

This is the first time this person has designed a database and is not really sure of how to go about it. He designs the FAN table and loads it with the following records

mh@fan.org ,    mary@myhome.org

He has correctly set up a primary key. He also used a foreign key to refer to the band. But this is not in 1NF because Mary has two email addresses loaded into the email field. The data is not atomic. Loading data in this way is also going to make it very difficult to extract email addresses. Also the data length of the email field now has to be long enough to handle many email addresses, this is very inefficent and would be a problem if exceeded.

Second Attempt

He soon realises this is not a good idea. So then he decides to create two email fields

This is also a poor approach - note that email2 is not being used in Tom's record and so is causing wasted storage, so not 1NF which seeks to avoid wasted / redundant data. Another problem is what if a fan has many more emails? Adding more and more email fields will make the wasted storage even worse.

Another problem is that the query to extract email addresses is far more complex than it needs to be as it has to examine each email field.

Solution

After trying out various ideas, he comes up with a good solution - create another entity called 'email' and use a foreign key in that table to link the fan and email tables together. The ER diagram is as follows:

The ER diagram shows that each fan can have many emails, but an email can only belong to one fan.

The FAN and EMAIL table now look like this

FAN

EMAIL

Mary (FanID = 2) has two entries in the email table. There is no problem adding even more emails for her. Extracting emails is now simple as there is only one email column. There is no wasted storage.

The tables are now in first normal form (1NF) as they obey the following rules

• Each table has a primary key
• Each field name is unique
• Data is atomic
• No repeating / redundant fields