Normalisation 1

1
Normalisation 1

• Krys McAvan

2
Lesson Objectives

• Recap on Relational Database Basics
• Introduce DBMS Terminology - more jargon!!!!!
• Gain an appreciation of what normalisation is
• Why we do it
• Introduce 1st to Boyce Codd Normal Form
• Attempt to normalise a database

3
Relational Database Recaps

• Database Management System Architecture

4
DB Terminology - Schemas

• Views of DB known as Schema
• External / users Schema - users view
• Logical / Conceptual Schema how the data is
  organised
• Internal / Physical Schema how data is stored

5
What is normalisation

• Normalisation is part of the process of designing
  the logical schema of the DBMS
• Design tables
• Identifies keys

6
Normalisation is
The process of removing redundant data from
relational tables by decomposing them into
smaller tables.
7
Why Normalise ?

• Minimise redundancy (therefore db size)
• Ensure data held only once in the system
• wasteful and (worse) risks anomalies and
  inconsistency
• Faster INSERT/UPDATE performance
• Easier to adapt or add new applications
• Eliminate null values and reduce opportunities
  for inconsistency

8
Normal Forms
1NF First normal form 2NF Second normal
form 3NF Third normal form BCNF Boyce-Codd
normal form 4NF Fourth normal form 5NF
Fifth normal form DK/NF Domain-key normal form
9
Codds Wallop
"Future users of large data banks must be
protected from having to know how the data is
organised in the machine."
Edgar Ted Codd 1923 - 2003
10
(No Transcript)
11
Un-Normalised Data
12
1st Normal Form The Key

• Every attribute is atomic or single valued
  there are no repeating fields.   

13
Short Hand Notation

• Table name (Primary Key, Attribute, attribute,
  attribute)
• example
• Books (ISBN, Author, Title, Publisher ID Price)

14
Primary Key
Student ID, Subject, Day
Lots of duplicate data -2nd , 3rd normal form
deals with this!
15
2nd Normal Form
2NF Definition All attributes that are not part
of the primary key must be dependant on the full
key and not just part of the key.
Problems Student ID -gt student name
16
2nd Normal Form
2NF Definition All attributes that are not part
of the primary key must be dependant on the full
key and not just part of the key. Time table
(Student ID, Subject, Day Teacher ID, Teacher
Name, Room) Student (Student ID -gtStudent Name)
17
3rd Normal Form

• There is still considerable duplication in the
  table, for example the teachers name. This is
  because the primary key student ID, Subject, Day
  determines the teachers code, and the teachers
  code in turn determines the teachers name.
• Stud ID, Subject, Day -gt Teacher ID , Room
• Teacher ID -gt Teacher Name
• The Teachers name is thus transitively determined
  by the key for the table.
•  

18
3rd Normal Form
3NF Definition There must be no transitive
determinants, or each attribute that is not part
of the key must be determined only by the key and
not by another attribute. Timetable (Student ID,
Subject, Day Teacher ID, Room) Student (Student
ID, Student Name) Teacher (Teacher ID, Teacher
Name)
19
Boyce Codd Normal Form

• Teacher can only teach in 1 room per day

The problem is that  the day and teacher ID
determined the room. This can be seen from a
modified determinacy diagram.
20
BC Normal Form
BCNF Definition A table is in BCNF if it is in
third normal form and it has no determinants that
are not candidate keys for the table. (Student
ID, Subject, Day, Teacher ID,) (Day, Teacher ID,
Room) (Student ID, Student Name) (Teacher ID,
Teacher Name)
21
Live log Prosper!!!
22
(No Transcript)