1 / 22

NORMALISATION

NORMALISATION. Introduction. Overview Objectives Intro. to Subject Why we normalise 1, 2 & 3 NF Normalisation Process Example Summary. Overview. The development of the application (creating an Access database) is usually one of the easiest steps in the process.

rocio
Télécharger la présentation

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

  2. Introduction • Overview • Objectives • Intro. to Subject • Why we normalise • 1, 2 & 3 NF • Normalisation Process • Example • Summary

  3. Overview The development of the application (creating an Access database) is usually one of the easiest steps in the process. Correct design of the database is one of the hardest steps but is of the utmost importance. In this lecture we concentrate upon this.

  4. Objectives By the end of this lecture the student should: • Appreciate the need for normalisation • Understand what is meant by 1st, 2nd & 3rd Normal Form • Be able to perform simple normalisation

  5. Intro. to Subject • DFD’s provide data flow info and process modelling • ERM provides entities & attributes (top down modelling) • Normalisation is designing a robust DB (bottom up modelling)

  6. Why Normalise ? • Data Modelling captures the data a system needs to store • Normalisation has two main objectives: ID of entities Logical model

  7. How it links to Access • All data of interest to system belongs in one database • Each entity is an Access table • Each attribute is an Access field • Using Normalisation you will identify the primary key

  8. Normalisation • Normalisation is an algorithm for reducing complex data structures to simple structures • Also known as Codd’s or Boyce Codd’s law

  9. Normalisation • Data that is not normalised can create insert/update/delete anomalies. • Minimise Data Redundancy • Achieve logical data grouping • Will not ensure that we have captured all the data

  10. First Normal Form (1NF) • Definition: An entity is in First Normal Form if it has an identifying key and there are no repeating attributes or groups of attributes

  11. First Normal Form (1NF) To remove repeating groups: • *Leave non-repeating attributes alone ( 1NF entity) • *Remove repeating attributes to separate 1NF entity whose key will be original key + key to repeating group

  12. Second Normal Form (2NF) Definition: An entity is in 2NF if it is in first normal form and has no attributes which require only part of the key to identify them uniquely.

  13. Second Normal Form (2NF) • To remove part-key dependencies • check that each non -key attribute depends on the whole key to determine it. • for each subset of a key, create a new separate entity

  14. Third Normal Form (3NF) Definition: An entity is in 3NF if and only if it is in 2NF and no non-key attribute depends on any other non-key

  15. Keys A Simple key is the unique identifier. A Compound key is made up of two or more simple keys. This creates the link between the two entities. A Foreign key is a non-key attribute in one entity which is a key attribute in another.

  16. Example For example a school has four floors and on each floor there are rooms one to twenty. Floor and room repeat; to identify a single room we use: (Floor Number) (Room Number) The brackets mean that the lower level non-unique identifier room number must have the higher level unique identifier to make the whole key unique.

  17. Example Course Registration Record Course Record Course Code: FR001 Course Description: Beginning French

  18. Example UNF UNF Level Course Code 1 Course Descr. 1 E-No 2 Name 2 Hours 2 Room 2 Tel-no 2

  19. Example 1NF Course Code Course Descr Course Code E-No Name Hours Room Tel-no

  20. Example 2NF Course Code Course Descr Course Code E-No Hours E-No Name Room Tel-No

  21. Example 3NF Course Code Course Descr Course Code E-No Hours E-No Name Room* Room Tel-No Thus we haveidentified FOUR entities: Course, Employee-on-Course, Employee & Room

  22. Summary • Overview • Objectives • Intro. to Subject • Why we normalise • 1, 2 & 3 NF • Normalisation Process • Example

More Related