1 / 35

An Introduction to Relational Data Analysis (Normalisation)

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.

helmut
Download Presentation

An Introduction to Relational Data Analysis (Normalisation)

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 and COLUMNS • Rows grouped together • Write table in unnormalised form • Choose unique KEY and underline

  6. 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

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

  10. 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

  11. 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

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

  13. 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

  14. Relational Data Analysis Student Code Student Name Student Code Tutor Code Course Code

  15. Relational Data Analysis Student Name Date of Birth Course Code Tutor Code Student Code Tutor Name Grade Result

  16. 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

  17. 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

  18. 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

  19. 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

  20. 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

  21. Normalisation Constructing a Data Model from 3NF

  22. 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

  23. Student Results Relations

  24. Create an entity type for each data relation e.g. COURSE-STUDENT COURSE Course Code Student Code *Tutor Code *Grade Course Code

  25. Make compound key relations into details refers to master / detail relationship COURSE STUDENT Course Code Student Code COURSE- STUDENT Course Code Student Code

  26. 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

  27. Redrawn Entity Model COURSE STUDENT Course Code Student Code TUTOR GRADE Tutor Code Grade COURSE- STUDENT Course Code Student Code *Tutor Code *Grade

  28. Example 2

  29. Rule 1 Create an entity type for each data relation e.g. COURSE-STUDENT COURSE Course Code Student Code Course Code

  30. 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)

  31. 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

  32. 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

  33. 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

  34. 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

  35. 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

More Related