Normalisation II

1
Normalisation II

• A Worked Example

2
An Employee Training System
Employee Training History





Training Given
3
Normalising The Training Request-1
EmpNo Surname FirstName Address Telephone AppNo Ap
pSurname AppFirstName ManNo ManSurname ManFirstNam
e Course Code Course Name Appraisal Date
4
Normalising The Training Request-1
EmpNo Surname FirstName Address Telephone AppNo Ap
pSurname AppFirstName ManNo ManSurname ManFirstNam
e Course Code Course Name Appraisal Date
0NF
5
Normalising The Training Request-1
EmpNo Surname FirstName Address Telephone AppNo Ap
pSurname AppFirstName ManNo ManSurname ManFirstNam
e Course Code Course Name Appraisal Date
0NF
For 1st Normal Form Remove repeating
groups and create new entities with a
key identifier made up of the key of the
repeating group AND the key of the original
entity.
6
Normalising The Training Request-1
EmpNo Surname FirstName Address Telephone AppNo Ap
pSurname AppFirstName ManNo ManSurname ManFirstNam
e Course Code Course Name Appraisal Date
0NF
For 1st Normal Form Remove repeating
groups and create new entities with a
key identifier made up of the key of the
repeating group AND the key of the original
entity.
1NF
EmpNo Course Code Course Name Appraisal Date
7
Normalising The Training Request-1
EmpNo Surname FirstName Address Telephone AppNo Ap
pSurname AppFirstName ManNo ManSurname ManFirstNam
e Course Code Course Name Appraisal Date
0NF
For 1st Normal Form Remove repeating
groups and create new entities with a
key identifier made up of the key of the
repeating group AND the key of the original
entity.
1NF
EmpNo Surname FirstName Address Telephone AppNo Ap
pSurname AppFirstName ManNo ManSurname ManFirstNam
e
EmpNo Course Code Course Name Appraisal Date
8
Normalising The Training Request-2
EmpNo Surname FirstName Address Telephone AppNo Ap
pSurname AppFirstName ManNo ManSurname ManFirstNam
e
EmpNo Course Code Course Name Appraisal Date
1NF
9
Normalising The Training Request-2
EmpNo Surname FirstName Address Telephone AppNo Ap
pSurname AppFirstName ManNo ManSurname ManFirstNam
e
EmpNo Course Code Course Name Appraisal Date
1NF
For 2nd Normal Form Look for Functional
Dependencies on only part of the key,
remove the dependent group and create a new
entity.
10
Normalising The Training Request-2
EmpNo Surname FirstName Address Telephone AppNo Ap
pSurname AppFirstName ManNo ManSurname ManFirstNam
e
1NF
EmpNo Course Code Course Name Appraisal Date
For 2nd Normal Form Look for Functional
Dependencies on only part of the key,
remove the dependent group and create a new
entity.
2NF
EmpNo Surname FirstName Address Telephone AppNo Ap
pSurname AppFirstName ManNo ManSurname ManFirstNam
e
11
Normalising The Training Request-2
EmpNo Surname FirstName Address Telephone AppNo Ap
pSurname AppFirstName ManNo ManSurname ManFirstNam
e
EmpNo Course Code Course Name Appraisal Date
1NF
For 2nd Normal Form Look for Functional
Dependencies on only part of the key,
remove the dependent group and create a new
entity.
Course Code Course Name
2NF
EmpNo Surname FirstName Address Telephone AppNo Ap
pSurname AppFirstName ManNo ManSurname ManFirstNam
e
12
Normalising The Training Request-2
EmpNo Surname FirstName Address Telephone AppNo Ap
pSurname AppFirstName ManNo ManSurname ManFirstNam
e
EmpNo Course Code Course Name Appraisal Date
1NF
For 2nd Normal Form Look for Functional
Dependencies on only part of the key,
remove the dependent group and create a new
entity.
EmpNo Course Code Appraisal Date
Course Code Course Name
2NF
EmpNo Surname FirstName Address Telephone AppNo Ap
pSurname AppFirstName ManNo ManSurname ManFirstNam
e
13
Normalising The Training Request - 3
EmpNo Course Code Appraisal Date
Course Code Course Name
2NF
EmpNo Surname FirstName Address Telephone AppNo Ap
pSurname AppFirstName ManNo ManSurname ManFirstNam
e
14
Normalising The Training Request - 3
EmpNo Course Code Appraisal Date
Course Code Course Name
2NF
EmpNo Surname FirstName Address Telephone AppNo Ap
pSurname AppFirstName ManNo ManSurname ManFirstNam
e
For 3rd Normal Form Look for
Functional Dependencies between Non Key
Attributes, remove them and create new entities.
15
Normalising The Training Request - 3
EmpNo Course Code Appraisal Date
Course Code Course Name
2NF
EmpNo Surname FirstName Address Telephone AppNo Ap
pSurname AppFirstName ManNo ManSurname ManFirstNam
e
For 3rd Normal Form Look for
Functional Dependencies between Non Key
Attributes, remove them and create new entities.
3NF
EmpNo Course Code Appraisal Date
Course Code Course Name
16
Normalising The Training Request - 3
EmpNo Course Code Appraisal Date
Course Code Course Name
2NF
EmpNo Surname FirstName Address Telephone AppNo Ap
pSurname AppFirstName ManNo ManSurname ManFirstNam
e
For 3rd Normal Form Look for
Functional Dependencies between Non Key
Attributes, remove them and create new entities.
3NF
ManNo ManSurname ManFirstName
EmpNo Course Code Appraisal Date
Course Code Course Name
17
Normalising The Training Request - 3
EmpNo Course Code Appraisal Date
Course Code Course Name
2NF
EmpNo Surname FirstName Address Telephone AppNo Ap
pSurname AppFirstName ManNo ManSurname ManFirstNam
e
For 3rd Normal Form Look for
Functional Dependencies between Non Key
Attributes, remove them and create new entities.
3NF
AppNo AppSurname AppFirstName
ManNo ManSurname ManFirstName
EmpNo Course Code Appraisal Date
Course Code Course Name
18
Normalising The Training Request - 3
EmpNo Course Code Appraisal Date
Course Code Course Name
2NF
EmpNo Surname FirstName Address Telephone AppNo Ap
pSurname AppFirstName ManNo ManSurname ManFirstNam
e
For 3rd Normal Form Look for
Functional Dependencies between Non Key
Attributes, remove them and create new entities.
3NF
EmpNo Surname FirstName Address Telephone AppNo Ma
nNo
AppNo AppSurname AppFirstName
ManNo ManSurname ManFirstName
EmpNo Course Code Appraisal Date
Course Code Course Name
19
Normalising Training History - 1
EmpNo Surname FirstName Address Telephone ManNo Ma
nSurname ManFirstName Dept DeptName Course
Code Course Name Date Taken
20
Normalising Training History - 1
EmpNo Surname FirstName Address Telephone ManNo Ma
nSurname ManFirstName Dept DeptName Course
Code Course Name Date Taken
0NF
21
Normalising Training History - 1
EmpNo Surname FirstName Address Telephone ManNo Ma
nSurname ManFirstName Dept DeptName Course
Code Course Name Date Taken
0NF
For 1st Normal Form Remove repeating
groups and create new entities with a
key identifier made up of the key of the
repeating group AND the key of the original
entity.
22
Normalising Training History - 1
EmpNo Surname FirstName Address Telephone ManNo Ma
nSurname ManFirstName Dept DeptName Course
Code Course Name Date Taken
0NF
For 1st Normal Form Remove repeating
groups and create new entities with a
key identifier made up of the key of the
repeating group AND the key of the original
entity.
EmpNo Course Code Course Name Date Taken
1NF
23
Normalising Training History - 1
EmpNo Surname FirstName Address Telephone ManNo Ma
nSurname ManFirstName Dept DeptName Course
Code Course Name Date Taken
0NF
For 1st Normal Form Remove repeating
groups and create new entities with a
key identifier made up of the key of the
repeating group AND the key of the original
entity.
EmpNo Surname FirstName Address Telephone ManNo Ma
nSurname ManFirstName Dept DeptName
EmpNo Course Code Course Name Date Taken
1NF
24
Normalising Training History - 2
EmpNo Surname FirstName Address Telephone ManNo Ma
nSurname ManFirstName Dept DeptName
EmpNo Course Code Course Name Date Taken
1NF
25
Normalising Training History - 2
EmpNo Surname FirstName Address Telephone ManNo Ma
nSurname ManFirstName Dept DeptName
EmpNo Course Code Course Name Date Taken
1NF
For 2nd Normal Form Look for Functional
Dependencies on only part of the key,
remove the dependent group and create a new
entity.
26
Normalising Training History - 2
EmpNo Surname FirstName Address Telephone ManNo Ma
nSurname ManFirstName Dept DeptName
EmpNo Course Code Course Name Date Taken
1NF
For 2nd Normal Form Look for Functional
Dependencies on only part of the key,
remove the dependent group and create a new
entity.
EmpNo Surname FirstName Address Telephone ManNo Ma
nSurname ManFirstName Dept DeptName
2NF
27
Normalising Training History - 2
EmpNo Surname FirstName Address Telephone ManNo Ma
nSurname ManFirstName Dept DeptName
EmpNo Course Code Course Name Date Taken
1NF
For 2nd Normal Form Look for Functional
Dependencies on only part of the key,
remove the dependent group and create a new
entity.
EmpNo Surname FirstName Address Telephone ManNo Ma
nSurname ManFirstName Dept DeptName
Course Code Course Name
2NF
28
Normalising Training History - 2
EmpNo Surname FirstName Address Telephone ManNo Ma
nSurname ManFirstName Dept DeptName
EmpNo Course Code Course Name Date Taken
1NF
For 2nd Normal Form Look for Functional
Dependencies on only part of the key,
remove the dependent group and create a new
entity.
EmpNo Surname FirstName Address Telephone ManNo Ma
nSurname ManFirstName Dept DeptName
Course Code Course Name
EmpNo Course Code Date Taken
2NF
29
Normalising Training History - 3
2NF
EmpNo Surname FirstName Address Telephone ManNo Ma
nSurname ManFirstName Dept DeptName
EmpNo Course Code Date Taken
Course Code Course Name
30
Normalising Training History - 3
2NF
EmpNo Surname FirstName Address Telephone ManNo Ma
nSurname ManFirstName Dept DeptName
EmpNo Course Code Date Taken
Course Code Course Name
For 3rd Normal Form Look for
Functional Dependencies between Non Key
Attributes, remove them and create new entities.
31
Normalising Training History - 3
2NF
EmpNo Course Code Date Taken
Course Code Course Name
EmpNo Surname FirstName Address Telephone ManNo Ma
nSurname ManFirstName Dept DeptName
For 3rd Normal Form Look for
Functional Dependencies between Non Key
Attributes, remove them and create new entities.
3NF
EmpNo Course Code Date Taken
Course Code Course Name
32
Normalising Training History - 3
2NF
EmpNo Surname FirstName Address Telephone ManNo Ma
nSurname ManFirstName Dept DeptName
EmpNo Course Code Date Taken
Course Code Course Name
For 3rd Normal Form Look for
Functional Dependencies between Non Key
Attributes, remove them and create new entities.
3NF
Dept DeptName
EmpNo Course Code Date Taken
Course Code Course Name
33
Normalising Training History - 3
2NF
EmpNo Surname FirstName Address Telephone ManNo Ma
nSurname ManFirstName Dept DeptName
EmpNo Course Code Date Taken
Course Code Course Name
For 3rd Normal Form Look for
Functional Dependencies between Non Key
Attributes, remove them and create new entities.
3NF
ManNo ManSurname ManFirstName
Dept DeptName
EmpNo Course Code Date Taken
Course Code Course Name
34
Normalising Training History - 3
2NF
EmpNo Surname FirstName Address Telephone ManNo Ma
nSurname ManFirstName Dept DeptName
EmpNo Course Code Date Taken
Course Code Course Name
For 3rd Normal Form Look for
Functional Dependencies between Non Key
Attributes, remove them and create new entities.
3NF
EmpNo Surname FirstName Address Telephone ManNo De
pt
ManNo ManSurname ManFirstName
Dept DeptName
EmpNo Course Code Date Taken
Course Code Course Name
35
All the Entities
From Training Requests Form
EMPLOYEE APPRAISER
MANAGER TRAINING REQUEST COURSE
AppNo AppSurname AppFirstName
ManNo ManSurname ManFirstName
EmpNo Course Code Appraisal Date
EmpNo Surname FirstName Address Telephone AppNo Ma
nNo
Course Code Course Name
From Training History Form
EMPLOYEE MANAGER DEPARTMENT
TRAINING RECORD COURSE
EmpNo Surname FirstName Address Telephone ManNo De
pt
ManNo ManSurname ManFirstName
Dept DeptName
EmpNo Course Code Date Taken
Course Code Course Name
36
All the Entities
EMPLOYEE APPRAISER
MANAGER TRAINING REQUEST COURSE
AppNo AppSurname AppFirstName
ManNo ManSurname ManFirstName
EmpNo Course Code Appraisal Date
EmpNo Surname FirstName Address Telephone AppNo Ma
nNo
Course Code Course Name
DEPARTMENT TRAINING RECORD
Dept DeptName
EmpNo Course Code Date Taken
37
With Duplicates Removed