An Introduction to Relational Data Analysis (Normalisation)

1
An Introduction toRelational Data
Analysis(Normalisation)
2
Relational Data Analysis

• Steps in Normalisation
• 1. Un-normalised form
• 2. First Normal Form
• 3. Second Normal Form
• 4. Third Normal Form

3
Relational Data AnalysisStudent Results Table
4
Relational Data Analysis

• Possible Entities in System
• COURSE
• STUDENT
• TUTOR
• GRADE?

5
Relational Data Analysis

• Unnormalised Form
• Table made up of ROWS and COLUMNS
• Rows grouped together
• Write table in unnormalised form
• Choose unique KEY and underline

6
Relational 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

7
Relational 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
8
Relational Data Analysis
First Normal Form

• Any relation is in First Normal Form when it
  contains no repeating groups of data

9
Relational 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
10
Relational 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
11
Relational 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
12
Relational 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
13
Relational 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

14
Relational Data Analysis

• Student Code Student Name
• Student Code
• Tutor Code
• Course Code

15
Relational Data Analysis

• Student Name
• Date of Birth
• Course Code Tutor Code
• Student Code Tutor Name
• Grade
• Result

16
Relational 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
17
Relational 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

18
Relational 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
19
Relational 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
20
Relational 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

21
Normalisation

• Constructing a Data Model from 3NF

22
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
23
Student 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
27
Redrawn Entity Model
COURSE
STUDENT
Course Code
Student Code
TUTOR
GRADE
Tutor Code
Grade
COURSE- STUDENT
Course Code Student Code Tutor Code Grade
28
Example 2
29
Rule 1

• Create an entity type for each data relation e.g.

COURSE-STUDENT
COURSE
Course Code Student Code
Course Code
30
Rule 2

• Mark the qualifying elements of hierarchic keys
  as a foreign key
• hierarchic key in this example

Rule 2
ASSIGNMENT
(Course Code) (Ass. Number)
31
Rule 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
32
Rule 4

• Make compound key relations into details
• refers to master / detail relationship

COURSE
STUDENT
Course Code
Student Code
COURSE- STUDENT
Course Code Student Code
33
Rule 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
34
Rule 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
35
3NF 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