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 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
10
Relational Data Analysis
11
Relational Data Analysis
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 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
13
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
14
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
15
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
16
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

17
Relational Data Analysis

• What attribute or attributes determine the TUTOR
  CODE?
• Course Code
• Student Code
• Course Code Student Code

18
Relational Data Analysis

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

19
Relational Data Analysis

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

20
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
21
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
22
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

23
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
24
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 Tuto
r Name Result Student Code
Grade Student Name
Result Student Code Date of
Birth Student
Name Date of
Birth Tutor Code
Tutor Name
Grade Result
Foreign key
25
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

26
Further Reading

• Relational Data Analysis
• Lejk Deeks, 2nd edition, chpt 8
• Next week
• NO LECTURE but read slides on DB Admin
• Following week
• Building Interactive Forms