Normalisation Workbook

First Normal Form (1NF)

The rules for 1NF:

• Each data item cannot be broken down any further i.e. it is ‘atomic’
• Each row/record is unique and has a primary key
• There are no records with repeating data
• Each field should be unique

Atomic data

Rule 1: Each data item cannot be broken down any further i.e. it is ‘atomic’

State whether the following examples are atomic or non atomic

Data Atomic or non atomic

Miss Jane Green Non atomic

9 Oak Crescent, Warwick Non atomic

Teacher Atomic

01926 123456 Atomic

King George IV School London Non atomic

CV11 9NB Atomic

Keyboard Atomic

Star Trek 12A Non atomic

Jaguar XJS Non atomic

Buckingham Palace Atomic

Supersize cheeseburger Non atomic

© www.teach-ict.com All Rights Reserved

Change the following examples of non atomic data into atomic data

Non Atomic Atomic

Name: Sally Chadwick First name: Sally

Surname: Chadwick

Address: 9 Oak Crescent, Snitterfield, Street & house no: 9 Oak Crescent
CV11 9NB Town: Snitterfield
Postcode: CV11 9NB

King George IV School London School: King George IV School

Town/City: London

Star Trek 12A Film: Star Trek

Rating: 12A

Jaguar XJS CarMake: Jaguar

Model: XJS

Rule 2: Each row/record is unique and has a primary key

The records below have no primary key. Choose a suitable primary key and
complete the table.

CustomerID* Title First Name Surname Dob

001 Mr James Smith 14/05/57
002 Miss Emma White 21/11/78

The records below have no primary key. Choose a suitable primary key and
complete the table.

FilmID* FilmTitle Rating Genre

0120 Star Trek 12A Sci Fi
0145 The Little Mermaid U Children

© www.teach-ict.com All Rights Reserved

Rule 3: There are no records with repeating data

Look at the tables below and answer the questions:

Table 1:
ID* Title Surname Telephone no
001 Miss Smith 01234 567890
002 Miss White 01234 890123
003 Miss James 01234 798453
004 Miss Green 01234 578345

Is this table in 1NF? Yes No

Reason: There is no repeating data (Miss in title field is not repeating in each
record)

Table 2:
ID* Title Surname Full Name Telephone no
001 Mrs Field Mrs Field 01234 523645
002 Miss White Miss White 01234 890123
003 Mr Hancock Mr Hancock 01234 989654
004 Miss Green Miss Green 01234 578345

Is this table in 1NF? Yes No

Reason: Full name is repeated data from the title and surname fields.

Table 3:
ID* Title Surname Email
001 Mrs Field [email protected]

002 Miss White [email protected]

[email protected]
003 Mr Hancock [email protected]

004 Miss Green [email protected]

[email protected]

Is this table in 1NF? Yes No

Reason: email addresses have repeating data

© www.teach-ict.com All Rights Reserved

Rule 4: Each field name should be unique

Look at the tables below and answer the questions:

Table 1:

ID* Title Surname Telephone no Telephone no Telephone no

001 Mr Smith 01234 567890 01234 789123 01234 345678
002 Miss White 01234 890123 01234 456789 01234 213456

Is this table in 1NF? Yes No

Reason: Not all of the field names in the record are unique, telephone
repeats three times.

Table 2:

ID* FirstName Surname Course Module Module

00123 Tamsin Wall Business Accounts Economics
Studies
00245 Steven Weeks Business Operations Business Law
Studies Management

Is this table in 1NF? Yes No

Reason: Not all of the field names in the record are unique, module repeats
two times.

© www.teach-ict.com All Rights Reserved

Identifying tables in First Normal Form (1NF)

Remembering all of the rules of First Normal Form, identify which of the
following tables are in 1NF. Explain your reason

Example 1:

Title FirstName Surname Address City Postcode

Mr Tom Smith 42 Mill Street London SE31 1WE

Is this table in 1NF? Yes No

Reason: There is no primary key defined.

Example 2:

ID* Username Last Activity Result AccountActive

accessed
1003 Smithj08 2009 06 Save file Success Y
21: 14:03

Is this table in 1NF? Yes No

Reason: A primary key is defined. The data is atomic. Each field has a
unique name. There is no repeating data.

Example 3:

ItemID* Product Description Size Colour Colour Colour

234 Shoe High Heel 6 Red Blue Green

Is this table in 1NF? Yes No

Reason: It has a primary key. Data is atomic. There are fields with the
same name. Data is repeated (colour)

Example 4:

StudentID* FirstName Surname SchoolID ClassID

354 Tom James 016 5F

Is this table in 1NF? Yes No

© www.teach-ict.com All Rights Reserved

Reason: A primary key is defined. The data is atomic. Each field has a
unique name. There is no repeating data.
Example 5:

ID* Title FirstName Surname Full Address City Postcode

name
004 Mr Tom Smith Tom 42 Mill London SE31
Smith Street 1WE

Is this table in 1NF? Yes No

Reason: The full name is repeated

Example 6:

ID* Film Rating Genre Actor Length

004 Star Trek 12A Sci Fi Leonard 180 mins
Nimoy

Is this table in 1NF? Yes No

Reason: A primary key is defined. The data is atomic. Each field has a
unique name. There is no repeating data.

1NF Worked Example

Entity – Band
Entity – Fan

Each band has many fans. Each person is a fan of only one band.

Taking this information into account, draw the correct ER diagram below:

BAND FAN

The The
attributes of attributes of
the band the fans
might be: might be:

© www.teach-ict.com All Rights Reserved

Write the correct database notation for each table.

BAND (BandID, BandName, MusicType)

FAN (FanID, FirstName, Surname, Email)

The database designer sets up the FAN table:

FanID* FirstName Surname BandID Email

001 Tom Smith 23 [email protected]
002 Jane Green 43 [email protected]
[email protected]

Identify the problem with this table:

Answer:
This table is not in 1NF because Jane has two email addresses loaded into the
email field. The data is not atomic.
Loading the data in this way is 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 inefficient.

The database designer has a second go at setting up the FAN table:

FanID* FirstName Surname BandID Email Email

001 Tom Smith 23 [email protected]
002 Jane Green 43 [email protected] [email protected]

Identify the problem with this new design.

Answer:
This table is also not in 1NF because there are repeating fields.
The second email in Tom’s record is not being used and so is causing wasted
storage.
Also, what if a fan has a lot of emails – how many fields would you set up?

The database designer finally comes up with the correct solution. He needs
to create a third table called EMAIL and use a foreign key in the fan table to
link to the primary key in the EMAIL table.

© www.teach-ict.com All Rights Reserved

Complete the ER diagram below:

BA FA

EM

Complete the three tables for this database below and add one record for
each. Indicate the primary keys by using a *

BAND
BandID* BandName MusicType
005 Girls Aloud Pop

FAN
FanID* FirstName Surname BandID EmailID
001 Tom Smith 23 012

EMAIL
EmailID* FanID Email
012 001 [email protected]

© www.teach-ict.com All Rights Reserved

Second Normal Form (2NF)

The rules for 2NF:

• The table must be in 1NF

• Non-key attributes must depend on every part of the primary key

Venue* Artist* Date* Attendance Profit Style

Wembley Girls Aloud 14/05/09 23000 12334 Girl band
NEC Leona 18/05/09 15000 43025 Female
Lewis soloist

Identify the key attributes in this table:

Venue, Artist, Date

Identify the non-key attributes in this table:

Attendance, Profit, Style

Does every non-key attribute depend on every key attribute?

No

If your answer above was no, is this table in 2NF?

No

If it is not in 2NF, identify which non-key attribute does not depend on one of
the key attributes.

Answer:
The non-key attribute ‘style’ isn’t dependent on all of the key attributes, it has
nothing to do with the venue or the date.

© www.teach-ict.com All Rights Reserved

Redesign this database so that it is in 2NF

CONCERT:
Venue* ArtistID* Date* Attendance Profit
Wembley 002 14/05/09 23000 12334

ARTIST:
ARTISTID* Artist StyleID
002 Girls Aloud 014

STYLE:
STYLEID* Style
014 Girl Band

2NF another example

PROJECT:
EmployeeID* ProjectID* DateHired ProjectManager

Identify the key attributes in this table:

EmployeeID, ProjectID

Identify the non-key attributes in this table:

DateHired, ProjectManager

Does every non-key attribute depend on every key attribute? Explain your
answer

No.

DateHired has nothing to do with ProjectID*

ProjectManager has nothing to do with EmployeeID

© www.teach-ict.com All Rights Reserved

Redesign this database so that it is in 2NF

PROJECT:
ProjectID* EmployeeID ProjectManagerID

EMPLOYEE:
EmployeeID* Date Hired

PROJECTMANAGER:
ProjectManagerID* ProjectManagerName

2NF another example

COURSES:
CourseID* Term* PlacesAvailable CourseTitle
001 Autumn 50 Computing
001 Spring 40 Computing
001 Summer 45 Computing
002 Autumn 100 ICT
002 Spring 90 ICT
003 Summer 100 Robotics

Identify the key attributes in this table:

CourseID, Term

Identify the non-key attributes in this table:

PlacesAvailable, CourseTitle

Does every non-key attribute depend on every key attribute? Explain your
answer

No.
Course title is dependent on CourseID, but it has nothing to do with Term

© www.teach-ict.com All Rights Reserved

Redesign this database so that it is in 2NF

COURSES AVAILABLE:
CourseID* Term* PlacesAvailable

COURSE:
CourseID* CourseTitle

© www.teach-ict.com All Rights Reserved

Third Normal Form (3NF)

The rules for 3NF:

• The table must be in 2NF

• There are no non-key attributes that depend on another non-key
attribute
• Every non-key attribute is non-transitively dependent on the primary
key

Example 1: Concert table

Venue* Artist* Date* Attendance Profit City Country

Wembley Girls 14/05/09 23000 12334 London UK

Aloud
NEC Leona 18/05/09 15000 43025 Birmingham UK
Lewis
Carnegie Girls 20/05/09 13453 16785 New York USA
Hall Aloud

Key attributes: Venue, Artist, Date

Non-key attributes: Attendance, Profit, City, Country

The table is in 2NF but it is not in 3NF because ‘Country’ could be derived
from ‘City’

Country could be stored in another table e.g.

City* Country

London UK

Birmingham UK

New York USA

The new table called ‘countries’ has City as the primary key and ‘country’ as
an attribute.
The concert table has ‘city’ as a foreign key. Now there is no redundant data.

3NF – an example

© www.teach-ict.com All Rights Reserved

COURSES AVAILABLE:
CourseID* Term* PlacesAvailable TeacherID Teacher
001 Autumn 50 024 Mrs White
001 Spring 40 024 Mrs White
001 Summer 45 019 Mr Brown
002 Autumn 100 024 Mrs White
002 Spring 90 031 Miss Green
003 Summer 100 026 Mr Black

Is this table in 3NF?

No

If you answered ‘no’, explain your reason.

Teacher – a non-key attribute is dependent on another non-key attribute,

teacherID

Redesign this database so that it is in 3NF

COURSES AVAILABLE
CourseID* Term* PlacesAvailable TeacherID

TEACHER
TeacherID Teacher

© www.teach-ict.com All Rights Reserved