Transcript Normalisation presentation

Project and Data
Management Software
Data Analysis and Data Modelling
Normalisation
Project and Data Management Software
1
Normalisation
 Normalisation provides an algorithm for
reducing complex data structures into simple
structures
 Formalised by set of rules known as Codd’s
laws
 Tidying up the data so there is no data
redundancy
 Ensuring data is grouped logically
Project and Data Management Software
2
Why Use Normalization?
 Relations formed by the process makes the
data easier to understand and manipulate.
 Provides a stable base for future database
growth.
 Simplifies relations and reduces anomalies.
Project and Data Management Software
3
Stages of Normalization
 There are 3 stages:
 1st Normal Form – 1NF
 2nd Normal Form – 2NF
 3rd Normal Form – 3NF
 BCNF Boyce Codd Normal Form
 4NF also exists
Project and Data Management Software
4
First Normal Form – 1NF
 For a relation to be in 1NF all its attributes
must be atomic


Each attribute must contain a single value
not a repeating group of values.
Every non-primary key attribute must be
functionally dependent on the Primary Key.
Project and Data Management Software
5
Un-normalised data
Course Code
Course Desc
Employee Number
Name
Block
Room No
Date Joined Course
Allocated Hours
Project and Data Management Software
6
Un-normalised data
 A list of fields needed for the system
 E.g. Staff Development Course
 All staff are released for two hours a week for staff
dev.
 Employees work at their own pace in a lab.
 A total of six attributes are recorded about each
employee including their normal office location (block
and room), the date they joined the course and how
many hours it is planned for them to work on it.
Project and Data Management Software
7
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 get to 1NF we must remove all repeating
groups (data elements)
Project and Data Management Software
8
Our Example
COURSE
Course Code
Course Desc.
EMP_ON_COURSE
Course Code
Employee Number
Name
Block
Room No
Date Joined Course
Allocated Hours
Project and Data Management Software
9
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
 To get to 2NF we remove part key
dependencies
 All data items must be dependant on the
primary key
Project and Data Management Software
10
Our Example
 Course is already in 2NF
 Emp_On_Course is not because
Attribute
Name
Block
RoomNo
Depends On
Employee No
Employee No
Employee No
Attribute
Date Joined
Hours
Depends On
Employee No + Course Code
Employee No + Course Code
Project and Data Management Software
11
So we..
 Take out details that are linked only to employee into
a separate table
 If in any doubt, ask a question such as ‘Are these
fields affected when they join a course’
Attribute
Name
Block
RoomNo
Depends On
Employee No
Employee No
Employee No
Project and Data Management Software
12
Cont.
COURSE
Course Code
Course Desc
EMP_ON_
COURSE
Course Code
Emp No
Date Joined
Course
Allocated Hours
Project and Data Management Software
EMPLOYEE
Emp No
Name
Block
Room No
13
Problems
 Block and Room Number are related, so if
one is updated the other will be affected.
 If the block names change, then the whole
of the employee records will have to be
altered
Project and Data Management Software
14
Third Normal Form (3NF)
 An entity is in 3NF if, and only if, it is in 2NF
and no non-key attribute depends on another
non-key attribute.
 To get to 3NF we must remove attributes that
depend on other non-key attributes
 It removes any mutual dependence between
non-key attributes
Project and Data Management Software
15
Third Normal Form 3NF
 In other words:

“The attributes is a relation in 3NF must
depend on the key, the whole key and nothing
but the key” !
Project and Data Management Software
16
How to do that: Dependency
 Decide on the direction of the dependency
between the attributes
 If B determines A, then A is dependant on B
 If A depends on B, create a new entity, keyed
by B, with A as an attribute
 Leave B in the original entity and mark it as a
foreign key, but remove A from the original
entity
Project and Data Management Software
17
Our Example: Dependency
 If, given a value for A, there is only one possible
value for B, then B is dependant on A
 Therefore, given a value for room no., there is only
one value for block. The same is not true vice-versa.
 Hence Block is dependent on Room No.
 Leave Room No in the original entity and mark it as a
foreign key, but remove Block from the original entity
Project and Data Management Software
18
Our Example
 Hence the EMPLOYEE (2NF) entity becomes
EMPLOYEE
LOCATION
Employee No
Name
Room No *
Room No
Block
* Room No is a foreign key in the
Employee entity
Project and Data Management Software
19
Entity Relationship Modelling
Course
Location
Emp_On_Course
Project and Data Management Software
Employee
20
Background - Keys
 Primary key
 Unique Identifier
 Can be made up of more than one attribute
and then is called a composite key
 If there is no obvious choice, use a number
 Foreign Key
 Does not belong to the entity
 Used to relate entity to entity
 A primary key in another table
Project and Data Management Software
21
To Normalise

Follow 3 simple steps
1. Remove all repeating data elements
2. Ensures data items are dependant on the
primary key
3. Remove all fields dependant on non-key
fields
Project and Data Management Software
22