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 attributes of band are:
The attributes of a fan are:
The database needs to be in first normal form.
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
email@example.com , firstname.lastname@example.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.
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.
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
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
challenge see if you can find out one extra fact on this topic that we haven't already told you
Click on this link: Designing 1NF