Normalization

1
Normalization

• 353352 Special Problem (DB)
• Choopan Rattanapoka
• choopanr_at_kmutnb.ac.th

2
??????????????????

• InfoFilm(type, title, airtime, year, studioName,
  studioLocation)
• ?????????????????????????????????????

3
????? redundancy and anomalies

• Data Redundancy
• ??????????????????????????????????
  ????????????????????????????????????
• ???????????????????????????????????????
  ???????????????????????????
• Modification/insertion anomalies
• ????????????? ???? ???????????????????????????????
  ??????????????????????????????????????????????????
  ?????????
• Deletion anomalies
• ??????????????????????????????????????????????????
  ???????????????????????????????????

4
Decomposition

• ???????? ???????????????????????? RA
  ???????????????????? R1, R2, ... RN
  ??????????????????????????????????????????????????
  ???????????????????? RA
• ????? natural join ???????????????????????
  ???????????????????????????????????????
• R1 R2 .. RN RA

5
??????????? decomposition
6
Functional Dependencies

• ???????? X ??? Y, ???? 2 attributes (????
  ???????? attribute) ?????????????????????????????
• ?????? X ? Y, ????????? X ?????????????????? Y

7
Example Functional Dependencies
InfoFilm(type, title, airtime, year, studioName,
studioLocation)
????????????????? (title) ??????????????????
(studioName) ???????????????
title ? type title ? airtime title ?
year title ? studioName title ?
studioLocation studioName ? studioLocation
8
Functional Dependancy Properties
9
Canonical Cover Fc

• Canonical Cover (Fc) ??? subset
  ?????????????????? transitivity ????????????
  functional dependancy ????? ???
• ?? set ??? functional dependancy ????????
  canonical cover ????

10
Example Canonical Cover

• ????? Functional Dependency (F) ???????? ???? Fc
• F numFilm ? title
• numFilm ? airtime
• numFilm ? numStudio
• numStudio ? studioName
• numFilm ? studioName
• numFilm ? studioLocation
• numStudio ? studioLocation

???
11
Example Canonical Cover

• F numFilm ? title
• numFilm ? airtime
• numFilm ? numStudio
• numStudio ? studioName
• numFilm ? studioName
• numFilm ? studioLocation
• numStudio ? studioLocation

Canonical Conver ??? numFilm ? title numFilm
? airtime numFilm ? numStudio numStudio ?
studioName numStudio ? studioLocation
12
Normal forms
1NF 2NF 3NF BCNF 4NF 5NF
????? normal form ?????????? I ?????????????????
normal form ?????????? I - 1
?????????? ???????????????????????????????
schema ???????? ????????????????? ??? ??????????
update ??????
13
1NF the first normal form

• Definition ??????????????????????? 1NF ??????
  attribute ??????????????????

?????????????????????? 1NF ????? attribute acteur
????????? 1 ???
14
???????????????????????? 1NF

• ????? attribute ?????????

???????(numFilm, numClient, period)
???????(numFilm, numClient, startTime, endTime)
15
???????????????????????? 1NF
2. ?????????????????????
Studio(name, filmTitle)

• Studio(name)
• Film(name, filmTitle)

16
2NF the second normal form

• Definition ?????????????????? 2NF ??????????
• ??????????????????? 1NF
• ??? attribute ??????????????????? key
  ????????????????????????????? key
• Objectif
• ???????????????????????
• ???????????? attribute ????????????? key
  ??????????????????????????????????? key

17
Example 2NF

• Actor (numFilm, numArtist, title, salary)

• Canonical Cover
• numFilm, numArtist ? salary
• numFilm ? title

18
Example 2NF

• Actor (numFilm, numArtist, title, salary)

• Canonical Cover
• numFilm, numArtist ? salary
• numFilm ? title ?????

19
????? 2NF
?????????????????????
Actor (numFilm, numArtist, title, salary)
Actor (numFilm, numArtist, salary) Film (numFilm,
title)
20
3NF the third normal form

• Definition ?????????????????? 3NF ??????????
• ??????????????????? 2NF
• ??? attribute ???????????????????? key
  ????????????????? attribute ????????? key
• ????????
• ????????????????????????????????
• ???????????????????? ????????? key ????????

21
Example 3NF

• Film (numFilm, title, numStudio, studioName)

• Canonical Cover
• numFilm ? title
• numFilm ? numStudio
• numStudio ? studioName

22
Example 3NF

• Film (numFilm, title, numStudio, studioName)

• Canonical Cover
• numFilm ? title
• numFilm ? numStudio
• numStudio ? studioName (?????)

23
???????????????????????? 3NF
?????????????????????
Film (numFilm, title, numStudio, studioName)
Film (numFilm, title, numStudio) Studio
(numStudio, studioName)
24
BCNF Boyce-Codd normal form

• Definition ?????????????????? BCNF
  ????????????????????? functional dependency ????
  candidate key

25
Example BCNF

• Localisation (cru, pays, region, quality)
• Cru ???????
• Pays ???????????????
• Region ????????????????
• Quality ??????

• Canonical Cover
• cru, pays ? region
• cru, pays ? quality
• region ? pays

26
BCNF exemple

• Localisation (cru, pays, region, quality)

• Canonical Cover
• cru, pays ? region
• cru, pays ? quality
• region ? pays (?????)

Crus (cru, pays, quality) Region(region, pays)
27
Exercise 1
Student(ID, name, age, address, foodCost,
subject, lecturer, grade)

• ID ????????????????????
• foodCost ???????????????? T1, T2, T3
  ??????????????????????
• subject ?????????????????????
• lecturer ?????????????????????
• ???? Caninocal Cover ???????????????
  Student

28

• ??? Exercise 1 ??? Canonical Cover
  ?????????????????????
• 1NF
• 2NF
• 3NF
• BCNF
• ???????
• ??????????????????????????????? BCNF

29
Exercise 2
??????????????????????????? ???????????? 3NF
InfoFilm(type, title, airtime, year, studioName,
studioLocation)

• ????????
• title ???????????????????????
• studioName ????????????????