Transcript Normalisation - dolinski.co.uk

Ham Ham’s Hammy Club
• What is normalisation?
“a technique for designing relational database
tables to minimize duplication of information and,
in so doing, to safeguard the database against
certain types of logical or structural problems”
wikipedia.com
MemberID:
Initial:
Surname:
Title:
Sex:
Postcode:
Tel No:
I’m kind of
a big deal!
Hammy Details:
Name
Sex
DofB
Breed
Origen of Breed
Breed life
expectancy
MemberID: 0001
Initial: A
Surname: Dolinski
Title: Mr
Sex: Male
Postcode: HA1 HA1
Tel No: 0123456789
I’m kind of
a big deal!
Hammy Details:
Name
Sex
DofB
Breed
Origen of Breed
Breed life
expectancy
Michelle
Schuhamster
F
11.10.2007
Syrian
Syria
2-3
Stormy
F
26.11.2007
Syrian
Syria
2-3
Minnie
F
25.04.2009
Syrian
Syria
2-3
Biscuit
M
25.04.2009
Russian Dwarf
Russia
1-2
• Hamster can have one owner
• Owners can have many hamsters
Hamster
Owner
• A hamster can only be one breed
• A breed can have many hamsters
Hamster
Breed
Owner
Hamster
Breed
MemberID: 0001
Initial: A
Surname: Dolinski
Title: Mr
Sex: Male
Postcode: HA1 HA1
Tel No: 0123456789
I’m kind of
a big deal!
Hammy Details:
Name
Sex
DofB
Breed
Origen of Breed
Breed life
expectancy
Michelle
Schuhamster
F
11.10.2007
Syrian
Syria
2-3
Stormy
F
26.11.2007
Syrian
Syria
2-3
Syrian
Syria
2-3
Russian Dwarf
Russia
1-2
Minnie
Biscuit
We can see we
already
have issues25.04.2009
F
with repeated data!
M
25.04.2009
MID
Initial
Last
Name
Title
Sex
Post
Code
Tel
Ham
Name
Ham
Sex
DOB
Breed
Origin
Life
Exp.
0001
A
Dolinski
Mr
M
HA1
HA1
01234
56789
Schuey
F
11.10.
2007
Syrian
Syria
2-3
0001
A
Dolinski
Mr
M
HA1
HA1
01234
56789
Stormy
F
26.11.
2007
Syrian
Syria
2-3
0001
A
Dolinski
Mr
M
HA1
HA1
01234
56789
Minnie
F
25.04.
2009
Syrian
Syria
2-3
0001
A
Dolinski
Mr
M
HA1
HA1
01234
56789
Biscuit
M
25.04.
2009
R.
Dwarf
Russia
1-2
0002
L
Pibski
Miss
F
FU1
BA4
98765
43210
Flabs
M
13.05.
2008
C.
Dward
China
1-3
0002
L
Pibski
Miss
F
FU1
BA4
98765
43210
Babs
F
17.02.
2008
Syrian
Syria
2-3
0003
S
Gatti
MS
F
QW3
RT7
46513
27980
Kebabs
M
28.09.
2007
R.
Dwarf
Russia
1-2
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
MID
Initial
Last
Name
Title
Sex
Post
Code
Tel
Ham
Name
Ham
Sex
DOB
Breed
Origin
Life
Exp.
0001
A
Dolinski
Mr
M
HA1
HA1
01234
56789
Schuey
F
11.10.
2007
Syrian
Syria
2-3
0001
A
Dolinski
Mr
M
HA1
HA1
01234
56789
Stormy
F
26.11.
2007
Syrian
Syria
2-3
0001
A
Dolinski
Mr
M
HA1
HA1
01234
56789
Minnie
F
25.04.
2009
Syrian
Syria
2-3
0001
A
Dolinski
Mr
M
HA1
HA1
01234
56789
Biscuit
M
25.04.
2009
R.
Dwarf
Russia
1-2
0002
L
Pibski
Miss
F
FU1
BA4
98765
43210
Flabs
M
13.05.
2008
C.
Dward
China
1-3
0002
L
Pibski
Miss
F
FU1
BA4
98765
43210
Babs
F
17.02.
2008
Syrian
Syria
2-3
0003
S
Gatti
MS
F
QW3
RT7
46513
27980
Kebabs
M
28.09.
2007
R.
Dwarf
Russia
1-2
...
...
...
...
...
...
...
... Customer
...
...
...
...
...
...
...
...
...
...
...
...
information
is ...
...
...
repeated
...
...
...
MID
Initial
Last
Name
Title
Sex
Post
Code
Tel
Ham
Name
Ham
Sex
DOB
Breed
Origin
Life
Exp.
0001
A
Dolinski
Mr
M
HA1
HA1
01234
56789
Schuey
F
11.10.
2007
Syrian
Syria
2-3
0001
A
Dolinski
Mr
M
HA1
HA1
01234
56789
Stormy
F
26.11.
2007
Syrian
Syria
2-3
0001
A
Dolinski
Mr
M
HA1
HA1
01234
56789
Minnie
F
25.04.
2009
Syrian
Syria
2-3
0001
A
Dolinski
Mr
M
HA1
HA1
01234
56789
Biscuit
M
25.04.
2009
R.
Dwarf
Russia
1-2
0002
L
Pibski
Miss
F
FU1
BA4
98765
43210
Flabs
M
13.05.
2008
C.
Dward
China
1-3
0002
L
Pibski
Miss
F
FU1
BA4
98765
43210
Babs
F
17.02.
2008
Syrian
Syria
2-3
0003
S
Gatti
MS
F
QW3
RT7
46513
27980
Kebabs
M
28.09.
2007
R.
Dwarf
Russia
1-2
...
...
...
...
...Breed
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
information
is...repeated
...
...
– No repeating attributes and primary key of non repeating data
must be placed in table of repeating data.
– Look for non-key attributes that depend upon all the attributes
in the compound primary keys i.e. does the attribute depend on
both primary keys?
– Look for non-key attributes that depend upon other non-key
attributes.
MemberID
Initial
LastName
Title
Sex
PostCode
Tel
HamName
HamSex
DOB
Breed
Origin
LifeExpectancy
MemberID
Initial
LastName
Title
Sex
PostCode
Tel
HamName
HamSex
DOB
Breed
Origin
LifeExpectancy
MemberID
Initial
LastName
Title
Sex
PostCode
Tel
MemberID
HamID
HamName
HamSex
DOB
Breed
Origin
LifeExpectancy
In some cases, you may need to
add a new field to make a
compound primary key.
MemberID
Initial
LastName
Title
Sex
PostCode
Tel
HamName
HamSex
DOB
Breed
Origin
LifeExpectancy
MemberID
Initial
LastName
Title
Sex
PostCode
Tel
MemberID
HamID
HamName
HamSex
DOB
Breed
Origin
LifeExpectancy
MemberID
Initial
LastName
Title
Sex
PostCode
Tel
MemberID
HamID
HamID
HamName
HamSex
DOB
Breed
Origin
LifeExpectancy
This fixes the many-to-many
problem!
MemberID
Initial
LastName
Title
Sex
PostCode
Tel
HamName
HamSex
DOB
Breed
Origin
LifeExpectancy
MemberID
Initial
LastName
Title
Sex
PostCode
Tel
MemberID
HamID
HamName
HamSex
DOB
Breed
Origin
Here we have aLifeExpectancy
foreign key – a
non-primary key that links to a
primary key in another table.
MemberID
Initial
LastName
Title
Sex
PostCode
Tel
MemberID
HamID
HamID
HamName
HamSex
DOB
Breed
Origin
LifeExpectancy
MemberID
Initial
LastName
Title
Sex
PostCode
Tel
Member
MemberID
HamID
Ownage
HamID
HamName
HamSex
DOB
Breed*
Breed
Origin
LifeExpectancy
Hammy
Breed
MemberID
Initial
LastName
Title
Sex
PostCode
Tel
HamName
HamSex
DOB
Breed
Origin
LifeExpectancy
MemberID
Initial
LastName
Title
Sex
PostCode
Tel
MemberID
HamID
HamName
HamSex
DOB
Breed
Origin
LifeExpectancy
MemberID
Initial
LastName
Title
Sex
PostCode
Tel
MemberID
HamID
HamID
HamName
HamSex
DOB
Breed
Origin
LifeExpectancy
MemberID
Initial
LastName
Title
Sex
PostCode
Tel
Member
MemberID
HamID
Ownage
HamID
HamName
HamSex
DOB
Breed*
Breed
Origin
LifeExpectancy
Hammy
Breed
• You don’t actually need to know how to
normalise a database. It’s useful to know
though because you’ll have a better
understanding of it – it’ll also help in
coursework!
• However, you need to know the advantages of
doing so and you’ll need to know the rules.