an introduction to relational data analysis normalisation
Download
Skip this Video
Download Presentation
An Introduction to Relational Data Analysis (Normalisation)

Loading in 2 Seconds...

play fullscreen
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
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 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