Normalisation A formal technique for analysing relations based

Normalisation • “A formal technique for analysing relations based on their primary key and functional dependencies. ” [Codd]. • Process – Analyse each relation using a set of rules – If a relation does meet a rule, decompose the relation into two or more relations

Overview • Normalisation – Steps • 1 NF – Remove repeating groups • 2 NF – Remove partial dependencies • 3 NF – Remove transitive dependencies

Understanding the data Normalisation gives us a set of rules for determining whether we have grouped our data items correctly BUT it cannot, in itself, ensure that we have captured all the data the system may require, or that we have fully understood it.

Normal form • Normal form means a convenient structure into which the data can be organized - in this case Codd’s Laws. • Here we study only first, second and third normal forms.

Determinacy and Dependency • If A determines B then B is dependent on A. • A determines B if each value of A is always associated with only one value of B.

An example of normalization (Un-Normalised Data)

Un-normalised data Staff-Development-Course: course-code (key) course-description (employee # name block room # date-joined-course allocated-hours)

First normal form (1 NF) • An entity is in 1 NF if, and only if, it has an identifying key and there are no repeating attributes or groups of attributes. • To remove repeating groups: – Leave non-repeating attributes alone ( as a 1 NF entity or table) – Remove repeating attributes to separate 1 NF entity whose key will be original key + key to repeating group

1 NF Course (table or entity) Emp-on-Course (table or entity) course-code (key) course-description course-code (key part 1) employee # (key part 2) name block room # date-joined-course allocated-hours

Second normal form (2 NF) An entity is in 2 NF if, and only if, it is in 1 NF and has no attributes which require only part of the key to identify them uniquely.

To get into 2 NF • We remove part-key dependencies • Where a key has attributes following, check that each attribute depends upon the whole key to determine it, not just part of the key. • Where any part of a key identifies an attribute or set of attributes, create a new separate entity.

Dependencies Attribute Depends on name block room # employee # But date-joined-course allocated-hours course-code + employee #

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.

2 NF entities Course course-code course-description Emp-on-Course course-code employee # date-joined-course allocated-hours Employee employee # name block room #

Block and room numbers

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

To get into 3 NF • We must remove attributes that depend on other non-key attributes • Decide on the direction of the dependency between the attributes • If A depends on B, create a new entity, keyed by B, with A as an attribute (A may be a set of attributes). • Leave B in the original entity and mark it as a foreign key, but remove A from the original entity

3 NF entities Employee (2 NF) employee # name block room # becomes Employee (3 NF) employee # name room #* and Location room # block

The total list of 3 NF entities is: • Course (course-code, course-description) • Emp-on-Course (course-code, employee #, date-joined-course, allocated-hours) • Employee (employee #, name, room #*) • Location (room #, block)

Staff Development Course entities and their keys

E-R diagram of staff course example