An Introduction to Relational Data Analysis (Normalisation)

1 / 26

# An Introduction to Relational Data Analysis (Normalisation) - PowerPoint PPT Presentation

An Introduction to Relational Data Analysis (Normalisation). Relational Data Analysis. Steps in Normalisation 1. Un-normalised form 2. First Normal Form 3. Second Normal Form 4. Third Normal Form. Relational Data Analysis Student Results Table. Relational Data Analysis.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about 'An Introduction to Relational Data Analysis (Normalisation)' - ira-stuart

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

### An Introduction toRelational Data Analysis(Normalisation)

Relational Data Analysis

Steps in Normalisation

1. Un-normalised form

2. First Normal Form

3. Second Normal Form

4. Third Normal Form

Relational Data Analysis

Possible Entities in System

• COURSE
• STUDENT
• TUTOR
Relational Data Analysis

Unnormalised Form

• Table made up of ROWS & COLUMNS
• Rows grouped together
• write table in unnormalised form
• choose unique KEY and underline
Data Attributes

Student Code

Student Name

Date of Birth

All REPEAT for a given value of COURSE CODE

Tutor Code

Tutor Name

Result

Relational Data Analysis
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

Result 2

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

First Normal Form

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

Result 2 Tutor Name

Result

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

Result 2 Date of Birth

Tutor Code

Tutor Name

Result

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

Result 2 Date of Birth

Tutor Code

Tutor Name

Result

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

Result 2 Date of Birth

Tutor Code

Tutor Name

Result

Relational Data Analysis
• 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

Second Normal Form

Relational Data Analysis

What attribute or attributes determine the TUTOR CODE?

• Course Code
• Student Code
• Course Code + Student Code
Relational Data Analysis

Student Code Student Name

Student Code

Tutor Code

Course Code

Relational Data Analysis

Student Name

Date of Birth

Course Code Tutor Code

Student Code Tutor Name

Result

Relational Data Analysis

Normalisation Table - 2NF

UNF UNF 1NF 2NF 3NF LEVEL

Course Code 1 Course CodeCourse Code

Course Title 1 Course Title Course Title

Student Code 2

Student Name 2 Course CodeCourse Code

Date of Birth 2 Student CodeStudent Code

Tutor Code 2 Student Name Tutor Code

Tutor Name 2 Date of Birth Tutor Name

Result 2 Tutor Name Result

Result Student Code

Student Name

Date of Birth

Relational Data Analysis

Normalisation Table - 2NF

UNF UNF 1NF 2NF 3NF LEVEL

Course Code 1 Course CodeCourse Code

Course Title 1 Course Title Course Title

Student Code 2

Student Name 2 Course CodeCourse Code

Date of Birth 2 Student CodeStudent Code

Tutor Code 2 Student Name Tutor Code

Tutor Name 2 Date of Birth Tutor Name

Result 2 Tutor Name Result

Result Student Code

Student Name

Date of Birth

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
Relational Data Analysis

Normalisation Table - 3NF

UNF UNF 1NF 2NF 3NF

LEVEL

Course Code 1 Course CodeCourse CodeCourse Code

Course Title 1 Course Title Course Title Course Title

Student Code 2

Student Name 2 Course CodeCourse CodeCourse Code

Date of Birth 2 Student Code Student CodeStudent Code

Tutor Code 2 Student Name Tutor Code Tutor Code

Tutor Name 2 Date of Birth Tutor Name Grade

Result 2 Tutor Name Result Student Code

Result Student CodeDate of Birth

Student Name

Date of Birth Tutor Code

Tutor Name

Result

Relational Data Analysis

Normalisation Table - 3NF

UNF UNF 1NF 2NF 3NF

LEVEL

Course Code 1 Course CodeCourse CodeCourse Code

Course Title 1 Course Title Course Title Course Title

Student Code 2

Student Name 2 Course CodeCourse CodeCourse 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

Result 2 Tutor Name Result Student Code

Result Student Code Date of Birth

Student Name

Date of Birth Tutor Code

Tutor Name

Result

Foreign key

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