Normalisation

Normalisation is the process of identifying and eliminating data anomalies and redundancies,
thereby improving data integrity and efficiency in a relational database. This process is designed to
remove repeated data and improve database design.

Basically we are making a database where –

1. Records are unique – So two people named John Smith won’t break the database
2. We aren’t doubling up data – So if we update something we only need to do it in the one
place
3. We can add data without creating empty fields
4. We can delete data without losing other data we would like to keep

During normalisation a relation is split into a number of smaller tables suitable for implementation
in a relational database.

For the course we need to normalise up until the 3rd Normal Form 3NF.

Let’s look at an example.

EB Games is a store that sells a variety of consoles for example –

Microsoft Xbox 1S $399 Sony PlayStation 4 $509 $148

Here are EB games sales records.

Cust Name Item Shipping Supplier Supplier Price
Address Phone
John Smith XBox One 35 Palm St, Microsoft (08) BUY- $399
Melville XBOX
Roger Banks Playstation 4 47 Campus Sony (08) BUY- $509
Rd, Murdoch SONY
Evan Wilson XBox One, PS 38 Rock Av, Wholesale Toll Free $547
Vita Coogee
John Smith Playstation 4 47 Campus Sony (08) BUY- $509
Rd, Murdoch SONY

There are some problems with this database so we will Normalise it to remove these possible
problems.
1st Normal Form
1. Each cell to be Single valued
2. Entries in a column are same type
3. Rows uniquely identified – Add Unique ID, or Add more columns to make unique

3. Row uniquely Identified *doesn’t account for people with the same name*

Cust Name Item Shipping Supplier Supplier Price

Address Phone
John Smith XBox One 35 Palm St, Microsoft (08) BUY- $399
Melville XBOX
Roger Banks Playstation 4 47 Campus Sony (08) BUY- $509
Rd, Murdoch SONY
Evan Wilson XBox One, PS 38 Rock Av, Wholesale Toll Free $547
Vita Coogee
John Smith Playstation 4 47 Campus Sony (08) BUY- $509
Rd, Murdoch SONY

1. Not a Single Value 2. Not Same Type

So here is that table Normalised to the 1st Normal Form. The colour is just to highlight the
changes.
Primary
Key
Cust ID Cust Name Item Shipping Supplier Supplier Price
Address Phone
1000 John Smith XBox One 35 Palm St, Microsoft (08) BUY- $399
Melville XBOX
1001 Roger Banks Playstation 47 Campus Sony (08) BUY- $509
4 Rd, SONY
Murdoch
1002 Evan Wilson XBox One 38 Rock Av, Microsoft (08) BUY- $399
Coogee XBOX
1002 Evan Wilson PS Vita 38 Rock Av, Sony (08) BUY- $148
Coogee SONY
1003 John Smith Playstation 47 Campus Sony (08) BUY- $509
4 Rd, SONY
Murdoch

1. So to get to single values Evan Wilson’s entry was separated to two entries one for the
XBox and one for the Vita.
2. Wholesale and Toll Free were removed and replaced with proper values that reflect the
information expected.
3. A Primary Key (Cust ID) was added to make sure each row is uniquely identified. So we now
see clearly that the two John Smiths are two different customers.
2nd Normal Form
1. All attributes (Non-Key Columns) dependant on the key.

So which attributes are not dependant on Cust ID in previous table?

Primary
Key
Cust ID Cust Name Item Shipping Supplier Supplier Price
Address Phone
1000 John Smith XBox One 35 Palm St, Microsoft (08) BUY- $399
Melville XBOX
1001 Roger Banks Playstation 47 Campus Sony (08) BUY- $509
4 Rd, SONY
Murdoch
1002 Evan Wilson XBox One 38 Rock Av, Microsoft (08) BUY- $399
Coogee XBOX
1002 Evan Wilson PS Vita 38 Rock Av, Sony (08) BUY- $148
Coogee SONY
1003 John Smith Playstation 47 Campus Sony (08) BUY- $509
4 Rd, SONY
Murdoch

The Supplier, Supplier Phone and Price and not dependant on the key. They are independent to
the person buying the console. Microsoft is the supplier of XBox regardless of who is buying it.

So we will take these items out into their own table.

I have used the name of the item as a primary key to make it a little clearer what is going on. You
probably wouldn’t do this in real life, there would be a stock number associated with these items.

Primary Primary
Key Key
Cust ID Cust Name Shipping Stock ID Supplier Supplier Price
Address Phone
1000 John Smith 35 Palm St, XBox One Microsoft (08) BUY- $399
Melville XBOX
1001 Roger 47 Campus Rd, PlayStation 4 Sony (08) BUY- $509
Banks Murdoch SONY
1002 Evan 38 Rock Av, PS Vita Sony (08) BUY- $148
Wilson Coogee SONY
1003 John Smith 47 Campus Rd,
Murdoch

So we have the non-dependant data out but now we can’t see who bought what so we add table
called a junction table to join them together.

Primary Key Primary Key

Cust ID Stock ID
1000 XBox One
1001 PlayStation 4
1002 XBox One
1002 PS Vita
1003 PlayStation 4
3rd Normal Form
1. All fields (columns) can be determined only by the key in the table and no other column.

In our example the Supplier Phone is dependent on the supplier. If we left this and the supplier
changed their number we would need to change it in multiple places.
Primary Primary
Key Key
Cust ID Cust Name Shipping Stock ID Supplier Supplier Price
Address Phone
1000 John Smith 35 Palm St, XBox One Microsoft (08) BUY- $399
Melville XBOX
1001 Roger 47 Campus Rd, PlayStation 4 Sony (08) BUY- $509
Banks Murdoch SONY
1002 Evan 38 Rock Av, PS Vita Sony (08) BUY- $148
Wilson Coogee SONY
1003 John Smith 47 Campus Rd,
Murdoch

Primary Key Primary Key

Cust ID Stock ID
1000 XBox One
1001 PlayStation 4
1002 XBox One
1002 PS Vita
1003 PlayStation 4

So we pull out the Supplier making it a Primary Key in its own table and a Foreign key in Stock
Table. Which will means you can only add suppliers from the Supplier Table in the Stock Table.

Primary Primary Foreign

Key Key Key
Cust ID Cust Name Shipping Stock ID Supplier Price
Address
1000 John Smith 35 Palm St, XBox One Microsoft $399
Melville
1001 Roger 47 Campus Rd, PlayStation 4 Sony $509
Banks Murdoch
1002 Evan 38 Rock Av, PS Vita Sony $148
Wilson Coogee
1003 John Smith 47 Campus Rd,
Murdoch Primary Key
Supplier Supplier
Primary Key Primary Key Phone
Cust ID Stock ID Microsoft (08) BUY-
1000 XBox One XBOX
1001 PlayStation 4 Sony (08) BUY-
1002 XBox One SONY
1002 PS Vita
1003 PlayStation 4
And we are done! Yes there is 4th
Normal form but we don’t need to
know about that.
3NF Terminology
Primary key
A primary key is a single attribute or multiple attributes (composite key) that uniquely identify
each tuple in the relation. The primary key attribute must contain unique values.

Composite key
A composite key is a primary key that includes multiple attributes.

Foreign key
A foreign key is an attribute in a table that stores a value that must match a value in the primary
key field in the related table. The foreign key attribute may have duplicate values. Therefore, a
foreign key is an attribute in a table that is a primary key in another table.

Driver Driver Driver Driver Car Car Car

Licence Firstname Surname Email Registration Manufacturer Manufacturer
Number Web Site
19289385 John Smith jsmith@[Link] 1COB 293 Ford [Link]
19289385 John Smith jsmith@[Link] 1QAZ 889 Holden [Link].a
u
19289385 John Smith jsmith@[Link] 1CCT 441 Ford [Link]
26453790 May Hogarth mhogarth@[Link] 1COB 293
t

Update Anomaly
An update anomaly is a problem that occurs when data that is repeated in a number of records
requires updating. If all records are not updated the data could become inconsistent or inaccurate.
In the relation above, if John Smith changes his email address, all 3 occurrences would need to be
updated to ensure that the data remains accurate.

Insert Anomaly
There are 2 types of insert anomaly.
1. When data needs to be added to a table and not all the data is known then some fields will
be null. For example, if a new driver (e.g. Sam Spade) is added to the Car Driver table and
the car details are unknown then the Car Registration, Car Value, Car Colour, Car Model
and Car Manufacturer Website fields would be null.
2. When data is added to a table and this results in some data being repeated. For example,
because John Smith can drive 3 cars the data in the Driver Licence

Deletion Anomaly
A deletion anomaly occurs when a record is deleted and this results in the loss of other data that
only occurs in that record. For example, if the second row is deleted because John Smith sells the
car with registration 1QAZ 889 then the website for Holden ([Link]) is also lost as
this data only occurs in that record.
Exercise 1

Student Student Name House Year Gender Event Event Time Place Points
ID No
1 John Jones Blue 10 M 1 Boys 50 Fly 00:49:30 1 6
2 Alan Hunt Green 10 M 1 Boys 50 Fly 00:50:19 2 4
3 Sam Lint Blue 10 M 1 Boys 50 Fly 00:51:19 3 2
4 Mark Lo Red 10 M 1 Boys 50 Fly 01:00:01 4 1
1 John Jones Blue 10 M 2 Boys 50 Free 00:50:30 2 4
2 Alan Hunt Green 10 M 2 Boys 50 Free 00:50:19 1 6
10 Mary Hill Red 11 F 8 Boys 50 Fly 00:50:20 1 6

Other Info
1st Place – 6 points
2nd Place – 4 points
3rd Place – 2 points
4th Place – 1 point
5th Place – 1 point
6th Place – 1 point

1. What type of “database” is this?

2. What are the appropriate data types and data validations/limits for each field?
3. What is the primary key? Is there one? Can one be identified?
4. Is this table normalized in any form? Justify your answer.
Exercise 2

Consider the following report from a Home Rental Company that has recently employed you as
their Information Management advisor.

Page 1 Dream Home 7 Oct 1996

Customer Rental Report

Customer John Kay Customer CR 76

Name Number

Property Property Rent Start Rent Finish Rent Owner Owner Name
Number Address Number
PG 4 6 Lawrie St 1 Jul 93 31 Aug 95 350 CO40 Tina Murphy
Glasgow

PG 16 5 Novice Drive 1 Sept 95 1 Sept 96 450 CO 93 Tony Shaw

Glasgow

Page 2 Dream Home 7 Oct 1996

Customer Rental Report

Customer Customer
Name Aline Stewart Number CR 56

Property Property Rent Start Rent Finish Rent Owner Owner Name
Number Address Number
PG 4 6 Lawrie St 1 Sept 92 10 Jun 93 350 CO40 Tina Murphy
Glasgow

PG 36 2 Manor Road 10 Oct 93 1 Dec 94 375 CO 93 Tony Shaw

Glasgow

PG 16 5 Novice Drive 1 Jan 95 10 Aug 95 450 CO 93 Tony Shaw

Glasgow

Study the data from the two pages of this report and transfer the data to a table. Ignore the date
of the report; this does not need to be taken into account at this time.

1. Create an un-normalised table first of all.

2. Identify a primary key (which may be composite) and put the table into 1NF.
3. Study the dependencies and create a set of tables in 2NF.
4. Look for a dependency that are not dependant on a primary keys from step 3 and move
your tables to 3NF.

References
[Link]
[Link]
Chen’s Notation
The Computer Science ATAR syllabus requires the use of Chen’s notation as a convention to represent
Entity Relationship (ER) diagrams when modelling a data base solution. However, online and text resources
are inconsistent in the representation of foreign key fields and attributes when using Chen’s notation.

To provide clarity and ensure consistency, when constructing ER diagrams using Chen's notation the
following applies:

An entity is represented by a rectangle, containing the name

of the entity expressed as a singular noun. An entity is
Entities
connected to an attribute and/or a relationship by a straight
line.
A relationship is represented by a diamond, containing the
relationship type expressed as a verb. Two single lines either
Relationships treats side of the diamond connect the relationship to the entities.

Cardinality is represented by placing the type of cardinality

Cardinality (1:1, 1:M, M:N), at the extremities of the connectors to the
1 M
treats entities.

An attribute is represented by an oval. An oval contains a

single attribute label expressed as an adjective and is
connected to an entity by a single straight line.

Multiple attributes can be connected to an entity by a

nested connecting line.

Attributes

For the purpose of this course:

StudentID • The primary key field/s is identified by a single
underline

• The foreign key field/s is identified by the use of the

StudentID FK
letters ‘FK’ next to the field

Note: The use of the following is beyond the requirements of the Computer Science ATAR syllabus:
• Weak entity
• Multi-valued and derived attributes
• Weak and optional relationships
• Participation constraints
Teachers may wish to provide more able students extension activities to explore these concepts however
they do not reflect the examinable content of the Computer Science syllabus.
Cardinality
The degree of relationship (cardinality) is represented by characters “1”, “M” or “N” usually placed at the
ends of the relationships:
 one-to-one (1:1)
The employee can manage only one department, and each department can be managed by one
employee only:

 one-to-many (1:M)
The customer may place many orders, but each order can be placed by one customer only:

 many-to-one (M:1)
Many employees may belong to one department, but one particular employee can belong to one
department only:

 many-to-many (M:N)
One student may belong to more than one student organizations, and one organization can admit
more than one student:
Cardinality Many to Many
A many-to-many relationship occurs when multiple records in a table are associated with multiple records
in another table. For example, a many-to-many relationship exists between customers and products:
customers can purchase various products, and products can be purchased by many customers.
Relational database systems usually don't allow you to implement a direct many-to-many relationship
between two tables.

To avoid this problem, you can break the many-to-many relationship into two one-to-many relationships by
using a third table, called a join table. Each record in a join table includes a match field that contains the
value of the primary keys of the two tables it joins. (In the join table, these match fields are foreign keys.)
These foreign key fields are populated with data as records in the join table are created from either table it
joins. The foreign key would sit in the table with the many.

A typical example of a many-to many relationship is one between students and classes. A student can
register for many classes, and a class can include many students.

Student M Takes N
Class

Student ID Class ID

First Name Title

First Name Description

To resolve this problem we create a join table, Enrollments, creates two one-to-many relationships—one
between each of the two tables.

The primary key Student ID uniquely identifies each student in the Students table. The primary key Class ID
uniquely identifies each class in the Classes table. The Enrollments table contains the foreign keys Student
ID and Class ID.
 We would create a new entity to represent this table in the entity relationship diagram.

1 M M
Student Enrols Enrolments Delivers Class

Enrolment ID Class ID
Student ID

Student ID FK
First Name Title

Class ID FK
First Name Description