STORING AND MANAGING

DATA
Normalisation

Dr Mohammed Bahja
School of Computer Science

[Link]@[Link]
2024/2025 - Week 4
Aims of the Session
This session aims to help you:
 Apply the principles of database design to produce to produce relations that are
normalised to the 3NF (third normal form)
Outline
Normalisation
Data Anomolies
Functional Dependence
Data Wrangling and reporting with
SQL(last week)
 Raw data is often messy and unstructured, making it challenging to
extract valuable insights.
 Data wrangling is the process of cleaning and transforming this data into
a usable format.
 It involves cleaning, transforming, and enriching raw data
into a more usable format for analysis.
 Data wrangling is the process of cleaning and
transforming this data into a usable format.
 SQL is the standard language for interacting with relational databases
and is widely used for both data wrangling and reporting.
Data Wrangling with SQL (last week)

 Filtering and Selecting Data

 Select specific rows and columns based on certain conditions
 Aggregating Data
 Summarize data using aggregate functions like SUM, COUNT,
AVG, MIN, and MAX.
 Joining Tables
 Combine data from multiple tables based on related column
 Handling Missing Values
 Identify and manage NULL values.
 Data Type Conversion
• Change data types using casting functions.
 Creating Calculated Fields
• Generate new columns based on calculations.
Reporting with SQL (last week)

 Generating Summary Reports

 Create high-level overviews using grouping and aggregation.
 Using Subqueries and Common Table Expressions
 Simplify complex queries and improve readability
 Implementing Conditional Logic
 Use CASE statements for conditional reporting.
 Formatting Output
 Enhance the readability of reports.
 Exporting Data
 Prepare data for export to reporting tools like Excel or Tableau
SQL SELECT COMMAND (last week)
 The SELECT statement is composed of up to six clauses,
each serving a specific purpose in constructing a query.
 The six clauses of the SELECT statement are:
 SELECT: Specifies the columns to retrieve.
 FROM: Indicates the tables from which to retrieve data.
 WHERE: Filters rows based on specified conditions.
 GROUP BY: Groups rows that have the same values in
specified columns.
 HAVING: Filters groups based on a condition.
 ORDER BY: Sorts the result set in a specified order.
WHERE Clause (last week)

Purpose: Filters rows based on specified conditions,

returning only those that meet the criteria.
SELECT col_1, col_2, col_3, col_4, col_5
FROM Table
WHERE col_1 = ‘cell_1_1’;

col_1 col_2 col_3 col_4 col_5

cell_1_1 cell_1_2 cell_1_3 cell_1_4 cell_1_5
cell_2_1 cell_2_2 cell_2_3 cell_2_4 cell_2_5
cell_3_1 cell_3_2 cell_3_3 cell_3_4 cell_3_5

SELECT product_name, price

FROM products
WHERE price > 100;
CONDITIONS (last week)
 Types of Conditions:
 Comparison Operators : =, !=, <>, <, <=, >, >=
 Logical Operators: AND, OR, NOT
 Pattern Matching (LIKE): _ for single character, % for Zero or more characters
 Range Checking (BETWEEN): Select values within a given range.
 Set Membership (IN): Check if a value matches any value in a list.
 NULL Value Handling (IS NULL): Check for NULL values.
 Existence Checking (EXISTS): Check if subquery returns any records.
 Subqueries: Use a subquery to compare with a single or multiple values.
GROUP BY Clause (last week)
 The GROUP BY clause in SQL is used to arrange identical
data into groups.
 It is commonly used with aggregate functions (such as
COUNT, SUM, AVG, MAX, MIN) to perform calculations on
each group of data.
 By grouping data based on one or more columns, you can
generate meaningfulcol_1
summaries
col_2 and insights
Report from your
A 6
datasets.
SELECT col_1, col_2 A 4
col_1
A
col_2
6
FROM table 4
B 5
GROUP BY col_1; 2
A 2 7
C 9 B 5
B 3 3

A 7 C 9
GROUP BY and HAVING Clauses (last week)
 The HAVING Clause – Restrict Group output
SELECT col_1, COUNT(*), SUM(col_2)
FROM table
GROUP BY col_1
HAVING SUM(col_2) < 10;

col_1 col_2 col_1 col_2

A 6 A 6 Report
A 4 A 4 col_1 COUNT(*) SUM(col_2)
B 5 A 2 B 2 8
A 2 A 7 C 1 9
C 9 B 5
B 3 B 3
A 7 C 9
Multiple Table Queries (last week)
 Report the Family Name, Given Name, Salary and
Department Name of all employees.
 Can all the columns be drawn from one table?

EMPLOYEE DEPARTMENT BUILDING

EmployeeNo DepartmentNo BuildingName
GivenName DepartmentName Address
FamilyName Manager * Quality
BirthDate
HireDate FLOORSPACE
ExtensionNo
Salary FSID JOB
PensionContrib FloorArea JobType
JobType Floor JobDescription
Manager * Position MinSalary
DepartmentNo * BuildingName * MaxSalary
DepartmentNo *
Multiple Table Queries (last week)
 Report the Family Name, Given Name, Salary and
Department Name of all employees.
 Can all the columns be drawn from one table?
 Subquery EMPLOYEE DEPARTMENT
EmployeeNo DepartmentNo
BUILDING
BuildingName
GivenName DepartmentName Address
FamilyName Manager * Quality
BirthDate
HireDate FLOORSPACE
SELECT ExtensionNo
FSID JOB
[Link], Salary
FloorArea
PensionContrib JobType
[Link], JobType Floor JobDescription
Manager * Position MinSalary
[Link], BuildingName *
DepartmentNo * MaxSalary
(SELECT [Link] DepartmentNo *

FROM DEPARTMENT
WHERE [Link] = [Link]) AS DepartmentName
FROM
EMPLOYEE;
Multiple Table Queries (last week)
 Subquery
 Performance: Subqueries can be slow, especially on large datasets, because
the subquery runs once for each row in the outer query.
 Readability: As queries grow in complexity, using multiple subqueries can
make the query harder to read and maintain.
 Subqueries are more limited in terms of the operations they can perform.
 As queries grow more complex, nested subqueries can become unwieldy.
 In some scenarios we use it in Nested Select Statements
SELECT
[Link],
[Link],
[Link],
(SELECT [Link]
FROM DEPARTMENT
WHERE [Link] = [Link]) AS DepartmentName
FROM
EMPLOYEE;
SQL JOIN clauses (last week)
 In the realm of relational databases, the ability to combine data from multiple
tables is crucial for effective data analysis, reporting, and preparation.
 SQL provides powerful tools to accomplish this, primarily through the use of JOIN
statements and nested queries.
 SQL JOIN clauses are used to combine rows from two or more tables based on a
related column between them.
 By using JOINs, you can retrieve data that is spread across multiple tables,
making it possible to analyse and report on complex datasets.
Nested Queries (Subqueries)
 Definition: A query within another SQL query, usually enclosed within
parentheses.
 When to Use Nested Queries Over Joins
 Complex Filtering: When the filtering condition depends on an aggregated
result from another table.
 Non-Relational Subsets: When you need to compare a value against a set of
values.
 Existence Checks: Using EXISTS or NOT EXISTS with subqueries to check for
the presence or absence of rows.

The inner query

(subquery) is executed
first
Its result is then used by
the outer query
Nested Queries (Subqueries) Examples
 Retrieve customers who have not placed any orders.

SELECT Name
FROM Customers
WHERE CustomerID NOT IN (
SELECT CustomerID FROM Orders
);
Nested Queries (Subqueries) Examples
 Retrieve customers along with the total number of orders they have placed.

SELECT Name, (
SELECT COUNT(*)
FROM Orders
WHERE [Link] =
[Link]
) AS OrderCount
FROM Customers;
Problem-Solving Approach for SQL
Queries
1. Understand the Problem
• Read the question carefully and identify what is being asked.
• Highlight keywords such as "find," "list," "calculate," etc.
2. Identify the Required Data
• Determine which tables contain the necessary information.
• List out the specific columns needed for the result.
3. Determine Table Relationships
• Identify primary and foreign keys to understand how tables are related.
• Decide on the type of join (INNER, LEFT, RIGHT, FULL) based on the
requirement.
4. Choose Between Join or Subquery
• Use joins when you need to combine rows from two or more tables based on
related columns.
• Use subqueries when you need to use the result of one query in another.
Problem-Solving Approach for SQL
Queries
5. Plan the Query Structure
• Write down the basic SQL clauses: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.
• Order your clauses logically to build the query step by step.
6 .Write and Test the Query Incrementally
1. Start with a simple query and add complexity gradually.
2. Test each part of the query to ensure it's working as expected.
Problem-Solving Approach for SQL
Queries
 Example : Using Joins
 Problem: "List the names of customers and the orders they've placed."
 Step 1: Understand the Problem
 We need customer names and their corresponding orders.
 Step 2: Identify the Required Data
 Tables needed: Customers, Orders
 Columns needed: [Link], [Link]
 Step 3: Determine Table Relationships
 [Link] (primary key) relates to [Link] (foreign key).
 Step 4: Choose Between Join or Subquery
 A join is appropriate because we are combining data from two tables based on a relationship.
Problem-Solving Approach for SQL
Queries
 Step 5: Plan the Query Structure
Get names from customers
OrderId from order
Maker the join based on the PK and FK

 Step 6: Write and Test the Query Incrementally

 First, retrieve customer names:

SELECT Name FROM Customers;

 Then, retrieve orders:

SELECT OrderID, CustomerID FROM Orders;
 Combine them using a join:
SELECT [Link], [Link] FROM
Customers JOIN Orders ON [Link] =
[Link];
 Normalisation
 Normalization is a systematic approach
developed by Edgar F. Codd in 1970 for
organizing data in relational databases.
 Defining a simple structure for
storing data: Creating tables that are
straightforward and efficient.
 Eliminating data redundancy:
Reducing duplicate data to save
storage space and improve data
integrity.
 Avoiding update anomalies:
Preventing issues during data
insertion, update, or deletion that can
lead to inconsistencies.
 Decomposing relations (tables):
Breaking down complex tables into
simpler, related tables without losing
Normalisation
Project Project Title Project Project Employee No Employee Name Department No Department Name Hourly
Code Manager Budget Rate
PC010 Pensions System M. Phillips 24500 510001 A. Smith L004 IT 22.00
PC010 Pensions System M. Phillips 24500 510030 L. Jones L023 Pensions 18.50
PC010 Pensions System M. Phillips 24500 521010 P. Gold L004 IT 22.00
PC045 Salaries System H. Martin 17400 510010 B. Jones L004 IT 21.75
PC045 Salaries System H. Martin 17400 510001 A. Smith L004 IT 18.00
PC045 Salaries System H. Martin 17400 531002 T. Gilbert L028 Database 25.50
PC045 Salaries System H. Martin 17400 513210 W. Richards L008 Salary 17.00
PC064 HR System K. Lewis 12250 531002 T. Gilbert L028 Database 23.25
PC064 HR System K. Lewis 12250 521010 P. Gold L004 IT 17.50
PC064 HR System K. Lewis 12250 510034 B. Jeffries L009 HR 16.50

Consider the activities listed and determine how changing the data could be
undertaken more efficiently by reorganizing the data.

a) Kathy Lewis leaves on maternity leave, replaced by Andrea Smith.

b) Senior Management agrees to increase the Salaries System budget by
5%
c) The Database department is renamed Information Services
d) Employee Numbers are to be changed, the leading 5 is no longer
required.
Normalisation Un-normalised Form (UNF)

 UNF is the initial stage in the normalisation process of a relational

database.
 In UNF, data is stored in its raw, unstructured format without any
normalisation rules applied. This means the table may contain:
 Repeating groups or arrays within a single record.
 Non-atomic values (fields containing multiple values).
 Nested relations (tables within tables).
Normalisation Un-normalised Form (UNF)

Student ID Name Date of Birth Subject Grade

960100 Smith, J 14/11/1977 Databases C

Software Development A
ISDE D
960105 White, A 10/05/1975 Software Development B
ISDE B
960120 Moore, T 11/03/1970 Databases A
Software Development B
Workshop C
960145 Smith, J 09/01/1972 Databases B

We describe this state as below:

960150 Black, D 21/08/1973 Databases
Software Development
ISDE
B
D
C
Workshop D

(StudentID, Name, DoB, (Subject,

Grade))
Normalisation normal forms (NF)

 Normalization involves applying a series of rules called normal forms (NF)

to ensure that the database structure is optimal.
 The most commonly used normal forms are the First (1NF), Second (2NF),
and Third Normal Forms (3NF).
 There are other Normal Forms, but we will not be considering these on this course
1 NF
st
Normalisation st
(1 NF)

 Rule:
 All attributes in a table must be atomic (no repeating groups).
 A Primary Key is defined from all Candidate Keys
 Normalisation st
(1 NF)

 Rule:
 All attributes in a table must be atomic (no repeating groups.
 A Primary Key is defined from all Candidate Keys
Student ID Name Date of Subject Grade
Birth
960100 Smith, J 14/11/1977 Databases C
Software Development A
ISDE D
960105 White, A 10/05/1975 Software Development B
ISDE B
960120 Moore, T 11/03/1970 Databases A
Software Development B
Workshop C
960145 Smith, J 09/01/1972 Databases B
960150 Black, D 21/08/1973 Databases B
Software Development D
ISDE C
Workshop D
Non Atomic Fields
Normalisation st
(1 NF)

 Rule:
 All attributes in a table must be atomic (no repeating groups.
 A Primary Key is defined from all Candidate Keys
Student ID Name Date of Subject Grade
Birth
960100 Smith, J 14/11/1977 Databases C
960100 Smith, J 14/11/1977 Software Development A
Which Column(s) should act 960100 Smith, J 14/11/1977 ISDE D
as Primary Key – Candidate 960105 White, A 10/05/1975 Software Development B
Key 960105 White, A 10/05/1975 ISDE B
960120 Moore, T 11/03/1970 Databases A
960120 Moore, T 11/03/1970 Software Development B
960120 Moore, T 11/03/1970 Workshop C
960145 Smith, J 09/01/1972 Databases B
960150 Black, D 21/08/1973 Databases B
960150 Black, D 21/08/1973 Software Development D
960150 Black, D 21/08/1973 ISDE C
960150 Black, D 21/08/1973 Workshop D
Normalisation st
(1 NF)

 Rule:
 All attributes in a table must be atomic (no repeating groups.
 A Primary Key is defined from all Candidate Keys

Student ID Name Date of Subject Grade

Birth
960100 Smith, J 14/11/1977 Databases C
960100 Smith, J 14/11/1977 Software Development A
960100 Smith, J 14/11/1977 ISDE D
960105 White, A 10/05/1975 Software Development B
Table is in First 960105 White, A 10/05/1975 ISDE B
Normal Form 960120 Moore, T 11/03/1970 Databases A
960120 Moore, T 11/03/1970 Software Development B
960120 Moore, T 11/03/1970 Workshop C
960145 Smith, J 09/01/1972 Databases B
960150 Black, D 21/08/1973 Databases B
960150 Black, D 21/08/1973 Software Development D
960150 Black, D 21/08/1973 ISDE C
960150 Black, D 21/08/1973 Workshop D
 Normalisation st
(1 NF)

Student ID Name Date of Subject Grade

 We have eliminated Birth
960100 Smith, J 14/11/1977 Databases C
repeating groups 960100 Smith, J 14/11/1977 Software Development A
960100 Smith, J 14/11/1977 ISDE D
 Decided on a key for the 960105 White, A 10/05/1975 Software Development B
table 960105 White, A 10/05/1975 ISDE B
960120 Moore, T 11/03/1970 Databases A
 We describe this state as 960120 Moore, T 11/03/1970 Software Development B
960120 Moore, T 11/03/1970 Workshop C
below: 960145 Smith, J 09/01/1972 Databases B
960150 Black, D 21/08/1973 Databases B
960150 Black, D 21/08/1973 Software Development D
Enrolment (StudentID, 960150 Black, D 21/08/1973 ISDE C
Name, DoB, Subject, 960150 Black, D 21/08/1973 Workshop D
Grade)

Table Name provided -

Enrolment
No repeating attribute – no
2 nd
NF
Normalisation (2
nd
NF)

 Rule:
 Table must be in First Normal Form
 Every non-key attribute must be fully functionally dependent on
the Primary Key.
 Define uniqueness
 Determination of non-key attributes
 Key also can be called a Determinant

If the value of an attribute (B) can be determined by the

value of another (A), then B is functionally dependant on A.

 A is the determinant … it determines the value of B

 B is the dependant … it depends upon the value of A
 Therefore, A is a key
Normalisation Functional Dependence
 If the value of an attribute (B) can be determined by the
value of another (A), then B is functionally dependant on A.
StudentID ModuleID Module Grade
Name
960100 5COM1064 Enterprise 67
Databases
960102 5COM1088 Program 70
ming
960103 5COM1085 Advance 70
Databases

StudentID is unique identifier, there are no two student have

same ID.
Thus, the StudentID determines the other four attributes
Student  ( StudentID, ModuleID, [Link], Grade )
Normalisation Functional Dependence
What are the Functional dependencies here?
Student ID Name Date of Birth Subject Grade

960100 Smith, J 14/11/1977 Databases C

960100 Smith, J 14/11/1977 Software Development A
960100 Smith, J 14/11/1977 ISDE D
960105 White, A 10/05/1975 Software Development B
960105 White, A 10/05/1975 ISDE B
960120 Moore, T 11/03/1970 Databases A
960120 Moore, T 11/03/1970 Software Development B
960120 Moore, T 11/03/1970 Workshop C
960145 Smith, J 09/01/1972 Databases B
960150 Black, D 21/08/1973 Databases B
960150 Black, D 21/08/1973 Software Development D
960150 Black, D 21/08/1973 ISDE C
960150 Black, D 21/08/1973 Workshop D
Normalisation Functional Dependence

Enrolment (StudentID, Name, DoB, Subject, Grade)

StudentID Name DoB Subject Grade

Normalisation Functional Dependence

Enrolment (StudentID, Name, DoB, Subject, Grade)

Functional Dependence

Student ID Name
DoB

Student ID, Subject Grade

FULL Functional Dependence

Normalisation (2
nd
NF)

 Rule:
 Table must be in First Normal Form
 Every non-key attribute must be fully functionally dependent on
the Primary Key.
Student ID Name Date of Birth Subject Grade

960100 Smith, J 14/11/1977 Databases C

960100 Smith, J 14/11/1977 Software Development A
960100 Smith, J 14/11/1977 ISDE D
960105 White, A 10/05/1975 Software Development B
960105 White, A 10/05/1975 ISDE B
960120 Moore, T 11/03/1970 Databases A
960120 Moore, T 11/03/1970 Software Development B
960120 Moore, T 11/03/1970 Workshop C
960145 Smith, J 09/01/1972 Databases B
960150 Black, D 21/08/1973 Databases B
960150 Black, D 21/08/1973 Software Development D
960150 Black, D 21/08/1973 ISDE C
960150 Black, D 21/08/1973 Workshop D

Are they for the Enrolment table?

Normalisation (2
nd
NF)
 Rule:
 Table must be in First Normal Form
 Every non-key attribute must be fully functionally dependent on
the Primary Key. Name
Student ID Date of Subject Grade
Birth
960100 Smith, J 14/11/1977 Databases C
960100 Smith, J 14/11/1977 Software Development A
960100 Smith, J 14/11/1977 ISDE D
960105 White, A 10/05/1975 Software Development B
960105 White, A 10/05/1975 ISDE B
960120 Moore, T 11/03/1970 Databases A
960120 Moore, T 11/03/1970 Software Development B
960120 Moore, T 11/03/1970 Workshop C
960145 Smith, J 09/01/1972 Databases B
960150 Black, D 21/08/1973 Databases B
960150 Black, D 21/08/1973 Software Development D
960150 Black, D 21/08/1973 ISDE C
960150 Black, D 21/08/1973 Workshop D
Normalisation (2
nd
NF)
 Rule:
 Table must be in First Normal Form
 Every non-key attribute must be fully functionally dependent on
the Primary Key.

How do we get the data into Second Normal Form?

 If an attribute is only functionally dependent on part of the key :

 Make a separate table including the part of the key on which the
attribute is fully functionally dependant, and that attribute
 Set the determinant as the primary key for that new table.
 The determinant becomes a foreign key in the original table
Normalisation (2
nd
NF)

Student ID Name Date of Birth Student ID Subject Grade

960100 Smith, J 14/11/1977 960100 Databases C
960105 White, A 10/05/1975 960100 Software Development A
960120 Moore, T 11/03/1970 960100 ISDE D
960145 Smith, J 09/01/1972 960105 Software Development B
960150 Black, D 21/08/1973 960105 ISDE B
960120 Databases A
960120 Software Development B
960120 Workshop C
960145 Databases B
960150 Databases B
960150 Software Development D
960150 ISDE C
960150 Workshop D
Normalisation (2
nd
NF)

Student ID Name Date of Birth Student ID Subject Grade

960100 Smith, J 14/11/1977 960100 Databases C
960105 White, A 10/05/1975 960100 Software Development A
960120 Moore, T 11/03/1970 960100 ISDE D
960145 Smith, J 09/01/1972 960105 Software Development B
960150 Black, D 21/08/1973 960105 ISDE B
960120 Databases A
960120 Software Development B
All non key attributes are fully functionally
960120 Workshop C
dependent on the Primary Key
960145 Databases B
960150 Databases B
We describe this state as below:
960150 Software Development D
Enrolment (StudentID*, Subject, Grade) 960150 ISDE C
Student (StudentID, Name, DoB ) 960150 Workshop D
Normalisation (2
nd
NF)

Student ID Name Date of Birth Student ID Subject Grade

960100 Smith, J 14/11/1977 960100 Databases C
960105 White, A 10/05/1975 960100 Software Development A
960120 Moore, T 11/03/1970 960100 ISDE D
960145 Smith, J 09/01/1972 960105 Software Development B
960150 Black, D 21/08/1973 960105 ISDE B
960120 Databases A
960120 Software Development B
All non key attributes are fully functionally
960120 Workshop C
dependent on the Primary Key
960145 Databases B
960150 Databases B
We describe this state as below:
960150 Software Development D
Enrolment (StudentID*, Subject, Grade) 960150 ISDE C
Student (StudentID, Name, DoB ) 960150 Workshop D

Note: A table in 1NF with a Simple Primary Key, MUST be

in 2NF
3 rd
NF
Normalisation (3
rd
NF)
 Table must be in Second Normal Form
 No non-key attribute must be dependent on any other attribute(s) other
than the Primary Key.
 Known as a Transitive Dependency
Student ID Subject Grade
Student ID Name Date of Birth
960100 Databases C
960100 Smith, J 14/11/1977
960100 Software Development A
960105 White, A 10/05/1975
960100 ISDE D
960120 Moore, T 11/03/1970
960105 Software Development B
960145 Smith, J 09/01/1972
960105 ISDE B
960150 Black, D 21/08/1973
960120 Databases A
960120 Software Development B
960120 Workshop C

Yes
960145 Databases B
960150 Databases B
960150 Software Development D
960150 ISDE C
960150 Workshop D
What about this scenario?
Project Project Title Project Project Employee No Employee Name Department No Department Hourly
Code Manager Budget Name Rate
PC010
PC010 Pensions
Pensions System
System M.
M. Phillips
Phillips 24500
24500 510001
510001 A.
A. Smith
Smith L004
L004 IT
IT 22.00
22.00
PC010
PC010 Pensions System
Pensions System M.
M. Phillips
Phillips 24500
24500 510030
510030 L. Jones
L. Jones L023
L023 Pensions
Pensions 18.50
18.50
PC010
PC010 Pensions
Pensions System
System M. Phillips
M. Phillips 24500
24500 521010
521010 P.
P. Gold
Gold L004
L004 IT
IT 22.00
22.00
PC045
PC045 Salaries
Salaries System
System H.
H. Martin
Martin 17400
17400 510010
510010 B.
B. Jones
Jones L004
L004 IT
IT 21.75
21.75
PC045
PC045 Salaries
Salaries System
System H. Martin
H. Martin 17400
17400 510001
510001 A.
A. Smith
Smith L004
L004 IT
IT 18.00
18.00
PC045
PC045 Salaries System
Salaries System H.
H. Martin
Martin 17400
17400 531002
531002 T. Gilbert
T. Gilbert L028
L028 Database
Database 25.50
25.50
PC045
PC045 Salaries
Salaries System
System H.
H. Martin
Martin 17400
17400 513210
513210 W.
W. Richards
Richards L008
L008 Salary
Salary 17.00
17.00
PC064
PC064 HR
HR System
System K.
K. Lewis
Lewis 12250
12250 531002
531002 T.
T. Gilbert
Gilbert L028
L028 Database
Database 23.25
23.25
PC064
PC064 HR
HR System
System K.
K. Lewis
Lewis 12250
12250 521010
521010 P.
P. Gold
Gold L004
L004 IT
IT 17.50
17.50
PC064 HR System K. Lewis 12250 510034 B. Jeffries L009 HR 16.50

Is it in First
SecondNormal Form
Normal (1NF)?
Form (2NF)? No
What about this scenario?
Project ( ProjectCode, ProjectTitle, ProjectManager, ProjectBudget,
EmployeeNo, EmployeeName, DepartmentNo, DepartmentName, HourlyRate )

ProjectCode ProjectTitle
Project Manager
Project Budget

EmployeeNo EmployeeName
DepartmentNo
DepartmentName

ProjectCode, EmployeeNo HourlyRate

Normalisation (3
rd
NF)
 Table must be in Second Normal Form
 No non-key attribute must be dependent on any other attribute(s) other
than the Primary Key.
 Known as a Transitive Dependency

Employee No Employee Name Department No Department

Name
510001 A. Smith L004 IT
510030
521010
L. Jones
P. Gold
L023
L004
Pensions
IT
Department Name is
510010 B. Jones L004 IT dependent on
531002 T. Gilbert L028 Database
513210 W. Richards L008 Salary Department No
510034 B. Jeffries L009 HR
Normalisation (3
rd
NF)
 How do we get the data into Third Normal Form?

 If an attribute is dependent on another non-key attribute:

 Make a new table formed from the non-key
 determinant and dependant attribute
 The determinant remains as a non-key attribute in the original table as
a Foreign Key
 The determinant becomes the primary key for the new table.
Normalisation (3
rd
NF)
Non-key determinant Dependent Attribute

Employee No Employee Name Department No Department

Employee No Employee Name Department No Name
510001
510001 A.
A. Smith
Smith L004
L004 IT
510030
510030 L.
L. Jones
Jones L023
L023 Pensions
521010
521010 P. Gold
P. Gold L004
L004 IT
510010
510010 B.
B. Jones
Jones L004
L004 IT
531002
531002 T. Gilbert
T. Gilbert L028
L028 Database
513210
513210 W.
W. Richards
Richards L008
L008 Salary
510034
510034 B. Jeffries
B. Jeffries L009
L009 HR

Department No Department
Name
L004 IT
L023 Pensions
L028 Database
L008 Salary
L009 HR
Normalisation Notation - UNF

Project Project Title Project Project Employee No Employee Name Department No Department Hourly
Code Manager Budget Name Rate
PC010 Pensions M. Phillips 24500 510001 A. Smith L004 IT 22.00
System
PC010 Pensions M. Phillips 24500 510030 L. Jones L023 Pensions 18.50
System
PC010 Pensions M. Phillips 24500 521010 P. Gold L004 IT 21.00
System
PC045 Salaries H. Martin 17400 510010 B. Jones L004 IT 21.75
System
PC045 Salaries H. Martin 17400 510001 A. Smith L004 IT 18.00
System
PC045 Salaries H. Martin 17400 531002 T. Gilbert L028 Database 25.50
System
PC045 Salaries H. Martin 17400 513210 W. Richards L008 Salary 17.00
System
PC064 HR System K. Lewis 12250 531002 T. Gilbert L028 Database 23.25
PC064 HR System K. Lewis 12250 521010 P. Gold L004 IT 17.50
PC064 HR System K. Lewis 12250 510034 B. Jeffries L009 HR 16.50

Rates ( ProjCode, ProjTitle, ProjManager, ProjBudget,

( EmpNo, EmpName, DeptNo, DeptName, Hrate) )
Normalisation Notation – 1NF

Project Project Title Project Project Employee No Employee Name Department No Department Hourly
Code Manager Budget Name Rate
PC010 Pensions M. Phillips 24500 510001 A. Smith L004 IT 22.00
System
PC010 Pensions M. Phillips 24500 510030 L. Jones L023 Pensions 18.50
System
PC010 Pensions M. Phillips 24500 521010 P. Gold L004 IT 21.00
System
PC045 Salaries H. Martin 17400 510010 B. Jones L004 IT 21.75
System
PC045 Salaries H. Martin 17400 510001 A. Smith L004 IT 18.00
System
PC045 Salaries H. Martin 17400 531002 T. Gilbert L028 Database 25.50
System
PC045 Salaries H. Martin 17400 513210 W. Richards L008 Salary 17.00
System
PC064 HR System K. Lewis 12250 531002 T. Gilbert L028 Database 23.25
PC064 HR System K. Lewis 12250 521010 P. Gold L004 IT 17.50
PC064 HR System K. Lewis 12250 510034 B. Jeffries L009 HR 16.50

Rates ( ProjCode, ProjTitle, ProjManager, ProjBudget,

EmpNo, EmpName, DeptNo, DeptName, Hrate )
Normalisation Notation – 2
nd
NF

Project Employee No Hourly Project Project Title Project Project

Code Rate Code Manager Budget
PC010 Pensions System M. Phillips 24500
PC010 510001 22.00
PC045 Salaries System H. Martin 17400
PC010 510030 18.50
PC064 HR System K. Lewis 12250
PC010 521010 21.00
PC045 510010 21.75
Employee No Employee Name Department No Department
PC045 510001 18.00 Name
PC045 531002 25.50 510001 A. Smith L004 IT
510030 L. Jones L023 Pensions
PC045 513210 17.00
521010 P. Gold L004 IT
PC064 531002 23.25
510010 B. Jones L004 IT
PC064 521010 17.50
531002 T. Gilbert L028 Database
PC064 510034 16.50
513210 W. Richards L008 Salary
510034 B. Jeffries L009 HR

Rates ( ProjCode*, EmpNo*, Hrate )

Project ( ProjCode, ProjTitle, ProjManager, ProjBudget )
Employee (EmpNo, EmpName, DeptNo, DeptName )
Normalisation Notation – 3
rd
NF
Project Project Title Project Project
Project Employee No Hourly Code Manager Budget
Code Rate
PC010 Pensions System M. Phillips 24500
PC010 510001 22.00 PC045 Salaries System H. Martin 17400
PC010 510030 18.50
PC064 HR System K. Lewis 12250
PC010 521010 21.00
PC045 510010 21.75 Employee No Employee Name Department No
PC045 510001 18.00 Department No Department
510001 A. Smith L004 Name
PC045 531002 25.50 510030 L. Jones L023 L004 IT
PC045 513210 17.00 521010 P. Gold L004 L023 Pensions
PC064 531002 23.25 510010 B. Jones L004 L028 Database
PC064 521010 17.50 531002 T. Gilbert L028 L008 Salary
PC064 510034 16.50 513210 W. Richards L008 L009 HR
510034 B. Jeffries L009

Rates ( ProjCode*, EmpNo*, Hrate )

Project ( ProjCode, ProjTitle, ProjManager, ProjBudget )
Employee ( EmpNo, EmpName, DeptNo* )
Department ( DeptNo, DeptName )
Normalisation remember

 Every time we split a table in Normalisation, the new

tables share Key values

Enrolment (StudentID*, Subject, Grade)

Student (StudentID, Name, DoB )

 The StudentID value in Enrolment can reference the

StudentID value in Student for other information about
that student
 The referencing value is known as the FOREIGN KEY, the reference key is
an existing PRIMARY KEY.
 Foreign keys are denoted by an *
References
Silberschatz, A., Korth H. F., and Sudarshan’s S. (7th Edition) Database System
Concepts, 7th Edition. McGraw-Hill.
⇒ Section 6.6 “Removing Redundant Attributes in Entity Sets” up to and
including Section 6.10 “Alternative Notations for Modeling Data”
⇒ Section 7.1 “Features of Good Relational Designs” up to and including
Section 7.3 “Normal Forms”
Thank You