An introduction to relational data analysis normalisation
Download
1 / 26

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


  • 60 Views
  • Uploaded on

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.

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

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


An Image/Link below is provided (as is) to download presentation

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 to relational data analysis normalisation

An Introduction toRelational Data Analysis(Normalisation)


Relational data analysis
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 AnalysisStudent Results Table


Relational data analysis1
Relational Data Analysis

Possible Entities in System

  • COURSE

  • STUDENT

  • TUTOR

  • GRADE?


Relational data analysis2
Relational Data Analysis

Unnormalised Form

  • Table made up of ROWS & COLUMNS

  • Rows grouped together

  • write table in unnormalised form

  • choose unique KEY and underline


Relational data analysis3

Data Attributes

Student Code

Student Name

Date of Birth

All REPEAT for a given value of COURSE CODE

Tutor Code

Tutor Name

Grade

Result

Relational Data Analysis


Relational data analysis4
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

Result 2


Relational data analysis5
Relational Data Analysis

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

First Normal Form





Relational data analysis9
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


Relational data analysis10
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

Grade

Result


Relational data analysis11
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

Grade

Result


Relational data analysis12
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

Grade

Result


Relational data analysis13
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 analysis14
Relational Data Analysis

What attribute or attributes determine the TUTOR CODE?

  • Course Code

  • Student Code

  • Course Code + Student Code


Relational data analysis15
Relational Data Analysis

Student Code Student Name

Student Code

Tutor Code

Course Code


Relational data analysis16
Relational Data Analysis

Student Name

Date of Birth

Course Code Tutor Code

Student Code Tutor Name

Grade

Result


Relational data analysis17
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

Grade 2 Tutor Code Grade

Result 2 Tutor Name Result

Grade

Result Student Code

Student Name

Date of Birth


Relational data analysis18
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

Grade 2 Tutor Code Grade

Result 2 Tutor Name Result

Grade

Result Student Code

Student Name

Date of Birth


Relational data analysis19
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 analysis20
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

Grade 2 Tutor Code Grade

Result 2 Tutor Name Result Student Code

Grade Student Name

Result Student CodeDate of Birth

Student Name

Date of Birth Tutor Code

Tutor Name

Grade

Result


Relational data analysis21
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

Grade 2 Tutor Code Grade

Result 2 Tutor 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


Relational data analysis22
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


Further reading
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


ad