1 / 15

Mr C Johnston ICT Teacher computechedu.co.uk

G057 - Lecture 07 Normalisation. Mr C Johnston ICT Teacher www.computechedu.co.uk. Session Objectives. Appreciating the need for normalisation, Understand what is meant by 1st, 2nd & 3rd Normal Form, Be able to perform simple normalisation. Definition:.

kirk-parrish
Download Presentation

Mr C Johnston ICT Teacher computechedu.co.uk

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. G057 - Lecture 07Normalisation Mr C Johnston ICT Teacher www.computechedu.co.uk

  2. Session Objectives • Appreciating the need for normalisation, • Understand what is meant by 1st, 2nd & 3rd Normal Form, • Be able to perform simple normalisation.

  3. Definition: • Normalisation is a process for removing redundant data from a database, • It is a method for reducing complex data structures to simple ones, • It is a step-by-step technique of putting data into “normal forms”.

  4. Why Normalise • Minimise Data Redundancy, • Data that is not normalised can create insert/update/delete anomalies, • Achieve logical data grouping.

  5. Course No. Course Title Start Date Attendee Name Attendee Department IND1 Staff Induction 27/10/2001 Edward Cole James Taylor Marketing Finance FIR1 Fire Safety 29/10/2001 Jenny Kennedy Peter James IT Accounts ACC1 Intro To Access 14/11/2001 Peter James Peter Hill Accounts Personnel IND1 Staff Induction 20/11/2001 John Mayer Stephen Moore IT Finance Data Redundancy Example • Here is a database for training offered in an organisation. • There are some duplications: course numbers, names and attendees have been entered on several occasions.

  6. Un-Normalised - 0NF • The first stage of normalisation is to show the data un normalised, • The information for the training looks like this: COURSE(course-no,course-title, start-date, {attendee-name, attendee-dept}) • Even at this stage the entity needs to be singular, in capitals and have a primary key.

  7. 1st Normal Form – 1NF (1) • An entity is in First Normal Form if it has an identifying key and there are no repeating attributes or groups of attributes. • Involves looking at the ONF attributes and deciding which have singular values and which have multiple. • Keep the singular values together in one entity and create an other for multiple

  8. Course No. Course No. Start Date Start Date Course Title Attendee Name Attendee Department IND1 IND1 27/10/2001 27/10/2001 Staff Induction Edward Cole Marketing FIR1 IND1 27/10/2001 29/10/2001 Fire Safety James Taylor Finance FIR1 ACC1 29/10/2001 14/11/2001 Jenny Kennedy Intro To Access IT IND1 FIR1 20/11/2001 29/10/2001 Staff Induction Peter James Accounts ACC1 14/11/2001 Peter James Accounts ACC1 14/11/2001 Peter Hill Personnel IND1 20/11/2001 John Mayer IT IND1 20/11/2001 Stephen Moore Finance 1st Normal Form – 1NF (2) • Details about a course are singular so they have been placed together in the COURSE entity. COURSE-1 entity containing the attributes related to a course • Attendee attributes are multiple so they are stored in another entity. ATTENDEE-1 entity containing the multiple attributes related to an attendee at a course

  9. 2nd Normal Form – 2NF (1) • 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. • We need to look at attributes in the entity with the multiples and decide which are dependant on the primary key and which are not. • Often an ERD which has had the many-to-many relationships resolved is in 2NF.

  10. Course No. Course No. Start Date Course Title IND1 IND1 Staff Induction 27/10/2001 FIR1 FIR1 Fire Safety 29/10/2001 ACC1 ACC1 Intro To Access 14/11/2001 IND1 20/11/2001 2nd Normal Form – 2NF (2) • You can find out a course name from the course No. • You cant however find out which instance of course it is as we need a date. • Therefore we spilt the COURSE entity into two creating: COURSE-2 entity containing the attributes related to a course COURSE OFFERED-2 entity contain the attributes of an occurrence of a course

  11. Course No. Start Date Attendee Name Attendee Department IND1 27/10/2001 Edward Cole Marketing IND1 27/10/2001 James Taylor Finance FIR1 29/10/2001 Jenny Kennedy IT FIR1 29/10/2001 Peter James Accounts ACC1 14/11/2001 Peter James Accounts ACC1 14/11/2001 Peter Hill Personnel IND1 20/11/2001 John Mayer IT IND1 20/11/2001 Stephen Moore Finance 3rd Normal Form - 3NF (1) • An entity is in 3NF if and only if it is in 2NF and if all non-key attributes are mutually independent. • Involves looking again at tables with multiples and pulling out attributes which are not linked to the primary key but are dependant to each other. ATTENDEE-2 entity not in 3NF as attendee names / departments are not independent from a course no / start date

  12. Course No. Staff No Last Name Start Date Staff No First Name Department 00492 IND1 Cole 27/10/2001 00492 Edward Marketing IND1 00765 Taylor 27/10/2001 00765 James Finance 00256 FIR1 Kennedy 29/10/2001 00256 Jenny IT 00835 FIR1 29/10/2001 James Peter 00835 Accounts 00345 ACC1 Hill 14/11/2001 00835 Peter Personnel ACC1 00428 Mayer 14/11/2001 John 00345 IT IND1 00562 Moore 20/11/2001 Stephen 00428 Finance IND1 20/11/2001 00562 3rd Normal Form (2) • To change the ATTENDEE attribute we need to spilt it into two: Revised ATTENDEE-3 entity COURSE ENROLEMENT-3 entity

  13. Finished Normalised Database • COURSES(course-no,course-title, start-date, {attendee-name, attendee-dept}) becomes: COURSE-3(course-ID, course-title) COURSE-OFFERED-3(course-no,*course-ID, start-date, duration) COURSE-ENROL-3(course-no, *staff-no) ATTENDEE-3(staff-no, last-name, first-name, dept)

  14. Normalisation Summary • Normalisation is used to reduce complex data structures into simple ones, • There are 3 forms of normalisation: 1NF, 2NF, 3NF. • By resolving any many to many relations our entities are generally in 2NF, • To ensure our entities are 3NF compliant we need to ensure all non-key attributes are independent.

  15. Normalisation Exercises • Below are un-normalised attributes for two database systems. Normalise each to 3NF. Library system CatalogueNoTitleAuthorPublisherNamePublisherAddressCopyNoBorrowerNoBorroweNameDateDueBack A library holds a system that catalogues the books it holds. Each book is unique but the library can hold multiple copies. Student records system (once again!) StudentNoStudentNameStudentAgeModuleNoModuleNameLecturerNoLecturerName A records system to detail students and the modules they take. Each student is unique and module numbers are also unique in the system. Each student takes six modules per semester. Each module can have one or more lecturers teaching on it.

More Related