An Introduction to Relational Data Analysis (Normalisation)

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 & 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 • Any relation is in First Normal Form when it contains no repeating groups of data First Normal Form

9. Relational Data Analysis

10. Relational Data Analysis

11. Relational Data Analysis

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

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

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

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

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

17. Relational Data Analysis What attribute or attributes determine the TUTOR CODE? • Course Code • Student Code • Course Code + Student Code

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

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

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

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

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

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

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

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

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