Normalisation

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

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

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

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

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

6. An example of normalization (Un-Normalised Data)

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

8. First normal form (1NF) • An entity is in 1NF 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 1NF entity or table) • Remove repeating attributes to separate 1NF entity whose key will be original key + key to repeating group

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

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

11. To get into 2NF • 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.

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

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

14. 2NF entities Course Emp-on-Course Employee course-codecourse-codeemployee # course-description employee # name date-joined-course block allocated-hours room #

15. Block and room numbers

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

17. To get into 3NF • 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

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

19. The total list of 3NF 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)

20. Staff Development Course entities and their keys

21. E-R diagram of staff course example