1 / 32

The Robert Gordon University

nalani
Download Presentation

The Robert Gordon University

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. 18/09/2012 The Robert Gordon University 1

    2. 18/09/2012 The Robert Gordon University 2 Normalisation in Context

    3. 18/09/2012 The Robert Gordon University 3 Aim of lecture

    4. 18/09/2012 The Robert Gordon University 4 Contents First normal form Why normalisation is useful Information redundancy Various types of anomaly Functional dependency How to normalise Second normal form Third normal form (but not Boyce-Codd form)

    5. 18/09/2012 The Robert Gordon University 5 First Normal Form

    6. 18/09/2012 The Robert Gordon University 6 Tables NOT in First normal form

    7. 18/09/2012 7 Converting department table to 1st normal form

    8. 18/09/2012 8 What to do if there are several multi-valued attributes

    9. 18/09/2012 9 How repeating groups might arise

    10. 18/09/2012 10 Removing repeating groups

    11. 18/09/2012 11 Exercise on 1st normal form

    12. 18/09/2012 The Robert Gordon University 12 Exercise on 1st normal form

    13. 18/09/2012 The Robert Gordon University 13 Staff and Branch Data

    14. 18/09/2012 The Robert Gordon University 14 Insertion Anomalies

    15. 18/09/2012 The Robert Gordon University 15 Modification Anomaly

    16. 18/09/2012 The Robert Gordon University 16 Deletion Anomaly

    17. 17 A better design

    18. 18/09/2012 The Robert Gordon University 18 Anomalies in the Purchase Item table

    19. 18/09/2012 The Robert Gordon University 19 Functional Dependency StudentID ? Student_surname u0006610 ? Smith Student_surname ? StudentID Smith ? u0006610 Smith ? u0107554 Smith ? u9801718

    20. 18/09/2012 The Robert Gordon University 20 Full Functional Dependency

    21. 18/09/2012 The Robert Gordon University 21 Functional Dependencies

    22. 18/09/2012 22 Removing partial dependencies

    23. 18/09/2012 The Robert Gordon University 23 Second normal form

    24. 24 Exercise

    25. 18/09/2012 The Robert Gordon University 25 Redundancies in the Purchase Order table

    26. 18/09/2012 The Robert Gordon University 26 Transitive dependency If A ? B and B ? C, then we can write A ? B ? C OrderNo ? SupplierNo ? SupplierName We say “C is transitively dependent on A” Or “A determines C via B” “Supplier name is transitively dependent on OrderNo”, or “OrderNo determines SupplierName via SupplierNo” A table is in third normal form if It is in second normal form, and There are no transitive dependencies

    27. 18/09/2012 The Robert Gordon University 27 Removing Transitive Dependencies from the purchase order table

    28. 18/09/2012 The Robert Gordon University 28 Note on transitive dependency

    29. 18/09/2012 The Robert Gordon University 29 Exercise

    30. 18/09/2012 The Robert Gordon University 30 Summary UN-normalised DBs cause problems Redundancy/Waste of time and space Anomalies (Insert, Update, Delete) Each table should have a single “topic” This will be indicated by the primary key An UN-normalised table tries to combine SEVERAL topics Contains inappropriate dependencies

    31. 18/09/2012 The Robert Gordon University 31 Summary

    32. 18/09/2012 The Robert Gordon University 32 The Normalisation Process

More Related