Normalisation
Atomic Data
Atomic data is data that cannot be further divided into smaller parts.
Examples
✓ Product ID
✓ Customer number
✓ First name
✓ Surname
✓ Telephone number
Non-Atomic Data
Non-atomic data is data that can be further divided into smaller parts.
Examples
✓ Full name (first name and last name)
✓ Full address (street address, city, state, zip code)
✓ Birthdate (year, month, day)
1
Normalisation
Normalisation is a technique used to reduce the duplication of data (data
redundancy) in a relational database.
Normalisation organises the data into multiple related tables.
It ensures that each table is structured correctly and does not contain redundant
data.
Normalisation starts with a flat-file and finishes with a set of related tables.
Normalisation is completed in steps.
Each step has a rule that improves the efficiency of the database.
These rules are normal forms.
Each normal form measures the extent to which the data has been normalised.
Un-normalised Form (UNF or 0NF)
Un-normalised form (UNF or 1NF) is when a database is not normalised.
It is often a flat-file database (a single table) that contains
✓ duplicated data (redundant data) and
✓ more than one item of data (non-atomic data)
stored within a single field.
Example
2
Types of Normalisation
1) First Normal Form (1NF)
2) Second Normal Form (2NF)
3) Third Normal Form (3NF)
First Normal Form (1NF)
First Normal Form (1NF) is when:
✓ All data is stored in a database table.
✓ A unique key (primary key) exists in each table.
✓ Only atomic data is stored.
✓ Each field has a unique name.
✓ Each record is unique.
✓ There are no repeating groups of columns.
Example
3
Second Normal Form (2NF)
Second Normal Form (2NF) is when:
✓ the table is already in 1NF.
✓ any non-key attributes that only depend on part of the table key are placed in a new
table.
Example
4
Third Normal Form (3NF)
Third Normal Form (3NF) is when:
✓ the table is already in 2NF.
✓ any non-key attributes that are more dependent on other non-key attributes than the
table key, are removed to a new table.
Example
5
Advantages of Normalisation
1) Normalisation removes duplicate data from a database. This reduces the size of the
database.
2) Normalisation removes the potential for errors and inconsistencies.
3) Normalised databases allow searches using indexed fields.
4) Searching on one table will be much faster as there is less data to go through.
5) Data can be linked across related tables.
6) Maintenance tasks can be completed more quickly because tables do not contain
redundant data.
7) The database becomes more flexible. Adding new fields and tables becomes
easier.
8) Having smaller tables allows forms and reports to fit onto one screen or one page
more easily.
9) It groups data logically as well as reducing inconsistent data in tables by
enforcing referential integrity
10) Security permissions can be applied to individual tables.
11) Any changes which are needed in certain records can be made automatically to
any related records.
12) With no duplicated data there will be fewer errors in the data and making changes to a
table is easier as there is less data to alter.
6
Disadvantages of Normalisation
1) Expertise is needed to build the database properly and to understand the process of
normalisation.
2) Large numbers of tables require more relationships to be designed. This takes more
time, when designing a large database, as well as requiring workers to have greater
knowledge. This, in turn, may lead to workers having to be brought in from other
companies and adds to the cost.
3) With the larger number of tables, setting up complex queries can be more difficult.
4) Having more tables than an unnormalised database makes it difficult to monitor where
particular data is.
5) Multi-table lookups and queries may take longer to execute using multiple-table
databases.
6) It can be difficult to understand the data stored in tables. Foreign keys may mean
very little to the user.
7) Nested queries are needed to look up data from linked tables. These queries can be
quite complex and require expertise from the user.
8) The processing of data can be slower with a greater number of tables and links to
navigate.
9) Removing non-atomic data may not always be a good idea if putting data into separate
fields serves no useful purpose.