370 likes | 510 Views
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.
E N D
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 • GRADE?
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
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 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 Analysis First Normal Form • Any relation is in First Normal Form when it contains no repeating groups of data
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 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 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 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 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
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 Grade 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 Grade 2 Tutor Code Grade Result 2 Tutor Name Result Grade 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 CodeStudent 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 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 CodeStudent 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 Code Date of Birth Student Name Date of Birth Tutor Code Tutor Name Grade 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
Normalisation Constructing a Data Model from 3NF
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 Code Date of Birth Student Name Date of Birth Tutor Code Tutor Name Grade Result Foreign key
Create an entity type for each data relation e.g. COURSE-STUDENT COURSE Course Code Student Code *Tutor Code *Grade Course Code
Make compound key relations into details refers to master / detail relationship COURSE STUDENT Course Code Student Code COURSE- STUDENT Course Code Student Code
COURSE STUDENT Make relations with foreign keys into details Course Code Student Code COURSE- STUDENT Course Code Student Code *Tutor Code *Grade TUTOR GRADE Tutor Code Grade
Redrawn Entity Model COURSE STUDENT Course Code Student Code TUTOR GRADE Tutor Code Grade COURSE- STUDENT Course Code Student Code *Tutor Code *Grade
Rule 1 Create an entity type for each data relation e.g. COURSE-STUDENT COURSE Course Code Student Code Course Code
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)
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
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
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
Rule 5 Rule 5 COURSE Rule 2 Make relations with foreign keys into details Course Code ASSIGNMENT STUDENT *(Course Code) (Ass. Number) Student Code COURSE- STUDENT RESULT Student Code (Course Code) (Ass. Number) Course Code Student Code
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