NORMALISATION 1 Introduction Overview Objectives Intro to Subject

NORMALISATION 1

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

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

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

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

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

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 7

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

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 9

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

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

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

Second Normal Form (2 NF) • 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 13

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

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

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

Example Course Registration Record Course Code: FR 001 Course Description: Beginning French 17

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

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

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

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

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