Title: An Introduction to Relational Data Analysis (Normalisation)
1An Introduction toRelational Data
Analysis(Normalisation)
2Relational Data Analysis
- Steps in Normalisation
- 1. Un-normalised form
- 2. First Normal Form
- 3. Second Normal Form
- 4. Third Normal Form
3Relational Data AnalysisStudent Results Table
4Relational Data Analysis
- Possible Entities in System
- COURSE
- STUDENT
- TUTOR
- GRADE?
5Relational Data Analysis
- Unnormalised Form
- Table made up of ROWS and COLUMNS
- Rows grouped together
- Write table in unnormalised form
- Choose unique KEY and underline
6Relational Data Analysis
- Data Attributes
- Student Code
- Student Name
- Date of Birth
- All REPEAT for a given value of COURSE CODE
- Tutor Code
- Tutor Name
- Grade
- Result
7Relational Data Analysis
Normalisation Table
UNF UNF 1NF 2NF 3NF
LEVEL Course Code 1 Course
Title 1 Student Code 2 Student
Name 2 Date of Birth 2 Tutor
Code 2 Tutor Name 2 Grade 2 R
esult 2
8Relational Data Analysis
First Normal Form
- Any relation is in First Normal Form when it
contains no repeating groups of data
9Relational Data Analysis
Normalisation Table
UNF UNF 1NF 2NF 3NF
LEVEL Course Code 1 Course Code
Course Title 1 Course Title
Student Code 2 Student
Name 2 Course Code Date of Birth 2
Student Code Tutor Code 2 Student
Name Tutor Name 2 Date of
Birth Grade 2 Tutor
Code Result 2 Tutor Name
Grade Result
10Relational Data Analysis
Normalisation Table
UNF UNF 1NF 2NF 3NF
LEVEL Course Code 1 Course
Code Course Title 1 Course
Title Student Code 2 Student Name
2 Date of Birth 2 Tutor Code
2 Course Code Tutor Name
2 Student Code Grade 2 Studen
t Name Result 2 Date of
Birth Tutor Code T
utor Name Grade Re
sult
11Relational Data Analysis
Normalisation Table
UNF UNF 1NF 2NF 3NF
LEVEL Course Code 1 Course
Code Course Title 1 Course
Title Student Code 2 Student
Name 2 Date of Birth 2 Tutor
Code 2 Course Code Tutor
Name 2 Student Code Grade
2 Student Name Result
2 Date of Birth Tutor
Code Tutor Name Gr
ade Result
12Relational Data Analysis
Normalisation Table
UNF UNF 1NF 2NF 3NF
LEVEL Course Code 1 Course
Code Course Title 1 Course
Title Student Code 2 Student
Name 2 Date of Birth 2 Tutor
Code 2 Course Code Tutor
Name 2 Student Code Grade
2 Student Name Result
2 Date of Birth Tutor
Code Tutor Name Gr
ade Result
13Relational Data Analysis
Second Normal Form
- Any relation already in 1NF is also in 2NF if
EITHER the key is a single attribute OR the
non-key items are fully dependent on the WHOLE
key - In Second Normal Form, you remove data items
which depend on only part of a key
14Relational Data Analysis
- Student Code Student Name
- Student Code
- Tutor Code
- Course Code
15Relational Data Analysis
- Student Name
- Date of Birth
- Course Code Tutor Code
- Student Code Tutor Name
- Grade
- Result
16Relational Data Analysis
Normalisation Table - 2NF
UNF UNF 1NF 2NF 3NF
LEVEL Course Code 1 Course Code
Course Code Course Title 1 Course
Title Course Title Student Code
2 Student Name 2 Course Code Course
Code Date of Birth 2 Student
Code Student Code Tutor Code 2 Studen
t Name Tutor Code Tutor Name 2 Date
of Birth Tutor Name Grade 2
Tutor Code Grade Result 2 Tut
or Name Result Grade
Result Student
Code Student
Name Date of Birth
17Relational Data Analysis
Third Normal Form
- Any relation in 2NF is also 3NF if all non-key
attributes are independent of all other non-key
attributes and all key attributes are independent
of all the other key attributes - In Third Normal Form, you remove any attributes
which are not directly dependent upon the key
18Relational Data Analysis
Normalisation Table - 3NF
UNF UNF 1NF 2NF 3NF
LEVEL Course Code 1 Course Code
Course Code Course Code Course
Title 1 Course Title Course
Title Course Title Student Code
2 Student Name 2 Course Code Course
Code Course Code Date of Birth 2
Student Code Student Code Student
Code Tutor Code 2 Student Name Tutor
Code Tutor Code Tutor Name 2 Date of
Birth Tutor Name Grade Grade 2
Tutor Code Grade Result 2
Tutor Name Result Student
Code Grade Studen
t Name Result Student
Code Date of Birth St
udent Name Date of
Birth Tutor Code
Tutor Name
Grade Resu
lt
19Relational Data Analysis
Normalisation Table - 3NF
UNF UNF 1NF 2NF 3NF
LEVEL Course Code 1 Course Code
Course Code Course Code Course
Title 1 Course Title Course
Title Course Title Student Code 2 Student
Name 2 Course Code Course Code Course
Code Date of Birth 2 Student
Code Student Code Student Code Tutor
Code 2 Student Name Tutor
Code Tutor Code Tutor Name 2 Date
of Birth Tutor Name Grade Grade 2
Tutor Code Grade Result 2
Tutor Name Result Student
Code Grade Student
Name Result Student
Code Date of Birth Stu
dent Name Date of
Birth Tutor Code
Tutor Name
Grade Result
Foreign key
20Relational Data Analysis
- Summary
- Choose a suitable key from a table of raw data
- Identify repeating groups
- Write the data in unnormalised form
- Convert unnormalised data to first normal form
- Convert first normal form to second normal form
- Convert second normal form to third normal form
21Normalisation
- Constructing a Data Model from 3NF
22Normalisation Table - 3NF
UNF UNF 1NF 2NF 3NF
LEVEL Course Code 1 Course Code
Course Code Course Code Course
Title 1 Course Title Course
Title Course Title Student Code 2 Student
Name 2 Course Code Course Code Course
Code Date of Birth 2 Student
Code Student Code Student Code Tutor
Code 2 Student Name Tutor
Code Tutor Code Tutor Name 2 Date of
Birth Tutor Name Grade Grade 2
Tutor Code Grade Result 2
Tutor Name Result Student
Code Grade Student
Name Result Student
Code Date of Birth Stu
dent Name Date of
Birth Tutor Code
Tutor Name
Grade Result
Foreign key
23Student Results Relations
24- Create an entity type for each data relation e.g.
COURSE-STUDENT
COURSE
Course Code Student Code Tutor Code Grade
Course Code
25- Make compound key relations into details
- refers to master / detail relationship
COURSE
STUDENT
Course Code
Student Code
COURSE- STUDENT
Course Code Student Code
26- Make relations with foreign keys into details
COURSE
STUDENT
Course Code
Student Code
COURSE- STUDENT
Course Code Student Code Tutor Code Grade
TUTOR
GRADE
Tutor Code
Grade
27Redrawn Entity Model
COURSE
STUDENT
Course Code
Student Code
TUTOR
GRADE
Tutor Code
Grade
COURSE- STUDENT
Course Code Student Code Tutor Code Grade
28Example 2
29Rule 1
- Create an entity type for each data relation e.g.
COURSE-STUDENT
COURSE
Course Code Student Code
Course Code
30Rule 2
- Mark the qualifying elements of hierarchic keys
as a foreign key - hierarchic key in this example
Rule 2
ASSIGNMENT
(Course Code) (Ass. Number)
31Rule 3
- Check that all masters of compound key relations
are present - This example has two compound keys
- one made from simple key and hierarchic key
- one made from two simple keys
RESULT
COURSE-STUDENT
Student Code (Course Code) (Ass. Number)
Course Code Student Code
32Rule 4
- Make compound key relations into details
- refers to master / detail relationship
COURSE
STUDENT
Course Code
Student Code
COURSE- STUDENT
Course Code Student Code
33Rule 4
- Make compound key relations into details
- refers to master / detail relationship
ASSIGNMENT
COURSE
STUDENT
(Course Code) (Ass. Number)
Course Code
Student Code
Rule 4
Rule 4
Rule 4
COURSE- STUDENT
RESULT
Student Code (Course Code) (Ass. Number)
Course Code Student Code
34Rule 5
- Make relations with foreign keys into details
Rule 5
COURSE
Rule 2
Course Code
ASSIGNMENT
STUDENT
(Course Code) (Ass. Number)
Student Code
COURSE- STUDENT
RESULT
Student Code (Course Code) (Ass. Number)
Course Code Student Code
353NF Model
Rule 5
COURSE
Rule 2
Course Code
ASSIGNMENT
STUDENT
(Course Code) (Ass. Number)
Student Code
Rule 4
Rule 4
Rule 4
COURSE- STUDENT
RESULT
Student Code (Course Code) (Ass. Number)
Course Code Student Code