????? 5 ??????????????????????

1
????? 5?????????????????????? (Normal Form)
2
Outline

• Data Dependency
• Functional Dependency
• Normalization
• 1NF
• 2NF
• 3NF
• BCNF

3
Data Dependency

• ???????????????????????????????????????
  ?????????? relation scheme ?????????? create
  table
• ??????? DBMS ???????????????? Data Dependency
  Constraint ???????????????
• Data Dependency ??????????????????????????????????
  ???????? ??????????????????(Normalization)

4
Data Dependency(???)

• ??????????????????? ??????? ???????????
  ????????????????????????? ????????????????????????
  ? ??????????????????????????????????????????
  ???????????? Software Hardware ???????????????
  ????????????????????????????????? scheme
  ??????????????
• ??????? Data Dependency Constraint
  ??????????????????????? ?????? DBMS
  ??????????????????????? ??????????????????????????
  ????????

5
???????????????????????????????????????????

• ??????????????????????????????????????
  ??????????????????????????????????????????????????
  ? ?????????????????????????????????????
• ????????????????? ????????????????????????????????
  ???? ?????????????????????????????
  ????????????????????????????????
  ??????????????????

6
Data Dependency Constraint

• Functional Dependency
• ?????????????????????????????????? Attribute
  ????? relational scheme ????????
• Multivalued Dependency
• Join Dependency

7
????? ??????????????????????(Functional
Dependency FD)

• ??? R ???????????? ?????? X,Y ???? arbitrary
  subsets ??????????????????? R ??????????????? Y
  is functionally dependent on X ???? X
  functionally determines Y ?????????? ??????????
  X ??????? Y ??????????????????? 1 ???????
• ??????????????????????????? (X?Y)

8
Functional Dependency

• ??????????????????? ??????attribute Y
  ?????????????????????????? X ???? ???attribute X
  1 ??? ??????????????????????????????attribute Y
  ??? 1 ???????????
• ????????????????? X ????????????? 2 ??????????
  ??????????????? 2 ???????????? Y
  ???????????????????

9
Functional Dependency

• ??????????????????????????????????????????????????
  ???????????????????? ???????? Determinant
• ???????????????? ????????????? ???????? Dependent
• ????????? Functional Dependency ??????? primary
  key ???????????????????????? normalization ????

10
Functional Dependency

• ?????????????????????
• A B
• ?????? FD (FD-Diagram)
• F ??? ?????? Nontrivial Functional Dependency
  ??????? Relation

A
B
11
????????????? Newlearn
Stud_id Stud_name Cousre_id Semester Year Grade
111 Jack 4121201 1 2005 A
111 Jack 4123201 1 2005 B
111 Jack 4122201 2 2005 B
111 Jack 4123303 1 2006 C
112 John 4121201 1 2005 B
112 John 4123201 2 2005 B
113 Tom 4123303 1 2006 A
113 Tom 4122205 2 2006 B
114 Mary 4122205 2 2006 B
114 Mary 4121201 1 2007 C
12

• ??????????? ????????? 2 ??? ????? stud_id
  ????????????? ????2 ??????????? stud_name
  ????????????
• ??????????????? Functional Dependency
• ?????????????????? ???
• stud_id ? stud_name
• ??????????????? FD ??????
• stud_id,course_id,semester,year ? grade
• ?????????????????? stud_id 1 ????????? course_id
  1??? ?????? semester 1 ????????? year 1 ???
  ???????? grade ??? 1 ??? ???????? ????
• ??? stud_id111 ??? course_id4121201 ???
  semester1 ??? year 2005 ????????????? 1 ???
  ??? A
• ??????? 4 ??????????????????? ????????????
  ????????????? ???? ?????? ???? ???????????????????
  

13

• ???????? newlearn ????? ??????????????? 2 tuples
  ??? (2 tuples ???????????tuple ???) ?? stud_id
  ??????? ????? course_id ??????? ??? semester
  ?????????? year ???????
• ????? primary key ???????? newlearn ???
  stud_id,course_id,semester,year
• ????????????????????????????? ????????????? 2
  tuples ????????????????? ?????????????????????????
  ???????? functional dependency ????

14
FD ??????? Relation ???????? 2 ???????????

• Trivial Functional Dependency
• Nontrivial Functional Dependency

15
Trivial Functional Dependency

• ??? FD ?????????????? x?x ???? ????????? x
  ? y ??? y ? x
• Trivial FD ??????? FD ??????????????? ??????????
  relation
• ???????? Trivial FD ??????? ??????????????????????
  ??????????????????????????

16
???????? Trivial FD

• ????? R(A,B,C) ?? Trivial FD ??????
• A?A
• B?B
• C?C
• AB?AB
• AC?AC
• BC?BC
• ABC?ABC

• AB?A
• AB?B
• AC?C
• AC?A
• ABC?AB
• ABC?BC
• ???

17
Nontrivial Functional Dependency

• ??? FD ??? ????????? Trivial FD
• ????????????? Nontrivial FD ????????????????
  ???????????? ???????????????????????????????????
  ????????????? Nontrivial FD ???
• ???????????? Nontrivial FD ?????????????????????
  ???????????????????????????? ?????????????????????
  ????????????????????????????????

18
???????? Nontrivial FD

• ???????? Newlearn ?? Nontrivial FD ??????
• stud_id ? stud_name
• stud_id,course_id,semester,year ?grade
• stud_id,course_id ? stud_name,cours_id
• stud_id,semester ? stud_name,semester
• ???????

19
????????????? SCP
S P QTY City
S1 P1 100 London
S1 P2 100 London
S2 P1 200 Paris
S2 P2 200 Paris
S3 P2 300 Paris
S4 P2 400 London
S4 P4 400 London
S4 P5 400 London
20
FD ???????? SCP
1. S,P ? QTY
2. S,P ? CITY ????
3. S,P ? CITY,QTY (????????????????attribute???????)
4. S,P ? S 5. S,P ? P
6. S ? CITY S ??????? CK ???? redundancy ??????? Supplier ?????????? ?????? ????????????????????????
7. S,P ? S,P,CITY,QTY (????????????????attribute???????)
8. S ? QTY
9. QTY ? S
????????????????????????????? ?????? FD
?????????????
21
FD ???????? SCP
10. S ? S trivial FD 11. P ? P trivial FD 12. S,P?S trivial FD 13. S,P?P trivial FD







22
???????? ?????????????????????
???????????? ???????????
440001 ???????????? ???????
460005 ??????????? ??????
451125 ???????????? ???????
465200 ???????? ??????
FD ???????????? ? ???????????
??????????????????? ??????????????????????????????
??????????????????
??? ??????????????????????????? 1 ??
?????????????????????? 1 ??
23
????????????? orders
Order_id orderDate Product_id Amount
1111 2/5/2006 P1 100
1111 2/5/2006 P2 30
1112 2/5/2006 P1 50
1113 3/5/2006 P5 20
1113 3/5/2005 P2 100
24
FD ????????????? orders

• Trivial FD ?????? ????
• order_id? order_id
• order_date?order_date
• Nontrivial FD ????
• Order_id?order_date
• Order_id,Product_id? Amount
• Order_id,Product_id? order_date,Product_id
• Order_id,Product_id? order_date,Amount
• Order_id,Amount ? order_date,Amount

25
FD-diagram ????? orders

• Orders(Order_id,OrderDate,Product_id,Amount)
• F order_id ? orderDate , order_id,product_id
  ?Amount
• ?? F ??????????? FD diagram ?????????

OrderDate
Order_id
Product_id
Amount
26
???????? ????? ???????
??????????? ???????? ??????????? ??????????? ??????????
111 D01 1/1/2007 800 1900
111 D01 2/1/2007 805 1700
112 D02 1/1/2007 730 1825
112 D02 2/1/2007 645 1749
113 D01 1/1/2007 545 1645
27
FD ????????????? ???????

• Trivial FD ?????? ????
• ???????????? ???????????
• ????????? ????????
• Nontrivial FD ????
• ????????????????????
• ??????????,???????????? ???????????,??????????
• ??????????,??????????? ? ????????,???????????
• ??????????,???????????,???????? ?
  ???????????,??????????

28
?????????????? FD ????????????

• ?????????????????????????????????
  ?????????????????? ER-model ???? ?????????? F
  ???????? Nontrivial FD ???
• ?????????? ?????? primary key ???????????????
  Nontrivial FD ????? determinant ??? FD ????
  primary key

29
????????????????????????????????????????
????????? 3 ???

• ?????????????????????????????????????
• (Full Functional Dependency)
• ???????????????????????????????????????? (Partial
  Dependency)
• ??????????????????????????????????????????
• (Transitive Dependency)

30
?????????????????????????????????????(Full
Functional Dependency)

• ????????? Full FD ??? attribute ?????????? FD
  ??????????? attribute ?????????????? Full FD
• ???? ????? FD ??????
• ???????????,???????? ? ???????????????
• FD ????????? Full FD ??? ??????????? FD ????????
• ??????????? ? ??????????????? ????
• ???????? ? ???????????????
• ????? FD ???????????????????????????
• FD ???????????,???????? ? ???????????????
• ??????? Full FD

31
Full Functional Dependency)

• ???? determinant ???????????????????????
• ?????????????

???????????? ???? ??????? ??????????????
471002 ????? ?????? 15 ?.? 2529
475001 ????? ??????? 24 ?.?. 2530
460005 ???? ?????? 21 ?.?. 2528
FD ???????????? ? ????, ???????,
??????????????
??????????? ????, ???????, ??????????????
?????????????????????????????????????????? ???
??????????? ????????????
32
Full Functional Dependency)

• ???? determinant ?????????????????????????
• ??????????????????

???????? ?????????? ?????????????????
218552 001 ??????
218001 002 ???????
218001 003 ??????
FD ????????,?????????? ? ?????????????????
??????????? ????????????????? ????????????????????
?????????????????????? ??? ??????????? ????????,
??????????
33
???????????????????????????????????????(Partial
Dependency)

• ??????????????????? ?? primary key ???? composite
  key ???????????????????????? primary key
  ???????????????? ????????????????
  ???????????????? ????????? primary key
  ??????????????

34
???????? ?????????????????
???????????? ???????? ???????? ??????????
4420001 4122531 ????????????? 001
4420005 4122531 ????????????? 001
4420090 4122522 ?????????????? 003
FD ????????????, ???????? ? ????????,
?????????? ???????? ? ???????? partial FD
??????????? ???????? ???????????????????? primary
key ???????????????????????? ?????????????????????
? ????????????????????????????????????????????????
?????????
35
?????????????????????????????????
?????????(Transitive Dependency)

• ????????????????????????????????
  ?????????????????????????????? primary key
  ?????????????????????????????????????
  ?????????????? ??????????????

36
???????? ??????????????
???????????? ???????????? ??????????? ???????????
4420001 ????? ????? 1010 ????? ?????
4420005 ????? ????? 1000 ???? ?????
4420090 ?????? ?????? 2001 ???? ?????
FD ???????????? ? ????????????,
???????????, ??????????? ??????????? ?
??????????? transitive FD
??????????? ??????????? ??????????????????????????
?? primary key ????????????????????????????????
???????????????? ??? ???????????????????????
37
Normalization

• ??? ??.????? ????????????? ?.?.1968
• ??? normalization ????????????????????????????????
  ???? ?????????????????????????????????????????????
  ????????????????????????
• ????????????????????????? ER
• ????? normalization ??????????????????????????????
  ?????????????????????????????DBMS ???????????

38
???????????????? ????????????????????????

• ??????????????????????????????????????????????????
  ??? ???? ???????????????????????????? ER
  ????????????????????
• ??????????????????? ????? ????????????????????????
  ??????????????????????????????????????????????????
  ??? ?????????????????????????????

39
???????????????????????????????????

• ??????????????????? (Insert Anomalies)
• ??????????????????? (Update Anomalies)
• ????????????????(Delete Anomalies)

40
?????????????????????????????????

• ????????????????????????????????????????
  ???????????????????????????????
  ???????????????????????????????????? 1 ?????
• ???????????????????? 2 ?????????
• ??? A ??????????????????
• ??? B ?????????????????????

41
Design A
???????
???????
??????????? ???? ???????????
E1 ???? P1
E2 ???? P1
E3 ???? P3
E4 ???? P3
E5 ???? P3
E6 ????? P2
E7 ???? P4
??????????? ??????? ???????? (???????)
P1 ??????? 10
P2 ??? 26
P3 ???? 79
P4 ?????? 10
42
Design B

• ?? 1 ????????? ????? ?????????? 2 ??????? design
  A ?? natural join

??????????? ???? ??????????? ??????? ????????
E1 ???? P1 ??????? 10
E2 ???? P1 ??????? 10
E3 ???? P3 ???? 79
E4 ???? P3 ???? 79
E5 ???? P3 ???? 79
E6 ????? P2 ??? 26
E7 ???? P4 ?????? 10
43
?????????????????????? ???B
Insert Anomaly

• ???????????? insert ??????? ??????????????????????
  ?????????????? ???????????????????????? insert ??
  relation ??? ????
• ????????????????? (E8,?????,P2) ????????? insert
  ?? relation ??????? (???A) ?????????? ????????? B
  ?????????????????????????? insert ???

44
?????????????????????? ???B
Deletion Anomaly

• ???????????????????????????? ?????????????????????
  ????????????????????????
• ???? ??????? E7 ??????? ????????????????? E7
  ??????????????????????????????????? P4
  ??????????????

45
?????????????????????? ???B
update Anomaly

• ???????? relation ???????? design B
  ????????????????????????????? ??? ?????????????
  ??????????????????? 1 ?? ??????????????????????
  ????????????????????????????????????????????

46
????? normalization ???????

• ???????????? 3 ?????????????? ????????????????????
  ???????????????????????????????????????????????
• Normalization ??? ?????????????????????????????
  ???????????????????? ?????????????????????????????
  ?????????????????????????

47
??????????????? (Normal Form)

• ??? ??????????????????????????????????????????????
  ?????????????????????????????????
  ???????????????????????????????????????????????
  ????????? ????????????????????????????????????????
  ????????
• ???? ???????????????????????? ???????????????????
  ???????????????????? ?????????????????????????????
  ??? ??????????????????????????????????
  ????????????????? GPA 3.75 ???????????????????????
  ????????? 3 ??

48
???????????????????????????????

• ???????????????????????? ?????????????????????????
  ???????????
• ???????????????????????????????
• ?????????????????????????????? ???????????????????
  (Insert, Update, Delete Anomalies)
• ????????????????????????

49
??????????????? (Normal Form)

• ???????????????????????? ?????????
  ?????????????????????????? Normal Form
• ??????????????? First Normal Form(1NF) ???????
  Fifth Normal Form(5NF)

50
??????????????????? Normal Form ??????????
1NF
???????????????????? Normal Form ??????????????
???? ?????????????????????????? Normal Form
?????????????? ?????????? ?????????????????
???????????????????????????? normal form
?????????????? ??????????????????? normal form
??????????????????
2NF
3NF
BCNF
4NF
5NF
51
??????? normalization ???????????????
????????????????? 2 ???

1. ?????????????????????????????????????????(Lossless
   Join) ??? ???????????????????????????????????
   ??????????????????????????????????????????????
   natural join ????????????????????????????????
   ????????? tuple ????????????????

R
???
R1
R2
join
R
52
??????? normalization ???????????????
????????????????? 2 ???

• 2. ????? Functional Dependency ???????????????
  (Dependency Preservation) ????????????????????
  FD????????????????????? ??????? FD ???????
  (?????????????????????????????????????????????????
  BCNF ????? FD ???????????)

53
??????????????????????? (First Normal Form 1NF)

• ???????????????????? ?????????????
• ????????????????????????? 1NF ??????????
• ????????????????? attribute ????? 1 tuple
  ????????????????(single value) ???????????
  attribute ????????????????????????????????(composi
  te attribute) ???????????????????????? (multivalue
  d attribute)

54
???????? ?????????????????????
???????????? ?????????? ?????????????? ??????????????
???????????? ?????????? ?????????? ?????
123 4/5/2007 P1 10
123 4/5/2007 P2 20
123 4/5/2007 P3 10
124 4/5/2007 P1 5
124 4/5/2007 P4 20
125 5/5/2007 P3 10
-?????????????????????? 1NF ????? attribute
?????????????? ???? composite attribute
??????????? ?????????? ??? ????? - ??? 1
????????????? 1 ??? ????? attribute
???????????????????????????(multivalued attribute)
55
???????? ????????????????????? 1NF
???????????? ?????????? ?????????? ?????
123 4/5/2007 P1 10
123 4/5/2007 P2 20
123 4/5/2007 P3 10
124 4/5/2007 P1 5
124 4/5/2007 P4 20
125 5/5/2007 P3 10
-?????????????????????????????? relation 1NF
?????????? multivalued attribute ??? composite
attribute
????????????????? attribute ????? 1
?????????????????????????
56
???????? ????????????
??????????????? 1NF ????? attribute ??????????
composite attribute ?????????? ??????? ???????
???? ??????? ???????? attribute
????????????????????????? atomic
??????????????????????????????????
??????????? ??????? ??????
999 19/9/1799 ??????? 173 ?? ??????? 50 ??. ???? ??
888 18/8/1788 ??????? 160 ?? ??????? 55 ??. ???? ??????
57
???????? ???????????? 1NF
??????????? ??????? ??????? ??????? ????
999 19/9/1799 173 50 ??
888 18/8/1788 160 55 ??????
??????????????????? ???????? Attribute
????????????????? ????????????? attribute ??????
58
???????? ?????????????????????
?????? relation UNF
Relation1NF
?????????? ???????????
001 111-555-9
001 112-557-1
001 115-225-9
002 114-256-0
002 111-999-9
003 115-236-5
?????????? ???????????
001 111-555-9
001 112-557-1
001 115-225-9
002 114-256-0
002 111-999-9
003 115-236-5
59
???????? ????? First
S P City Status QTY
S1 P1 London 20 300
S1 P2 London 20 200
S1 P3 London 20 400
S1 P4 London 20 200
S1 P5 London 20 100
S1 P6 London 20 100
S2 P1 Paris 10 300
S2 P2 Paris 10 400
S3 P2 Paris 10 200
S4 P2 London 20 200
S4 P4 London 20 300
S4 P5 London 20 400
Multivalued attribute
60
????????????? First ???? 1NF
S P City Status QTY
S1 P1 London 20 300
S1 P2 London 20 200
S1 P3 London 20 400
S1 P4 London 20 200
S1 P5 London 20 100
S1 P6 London 20 100

• 1. ?????????????????????? 1 ?????????????????
• 2. ???????????????????????????????????????????????
  ?????????????????????
• 3. ????? Primary Key ??????????????

61
????? First ?????? 1NF
S P City Status QTY
S1 P1 London 20 300
S1 P2 London 20 200
S1 P3 London 20 400
S1 P4 London 20 200
S1 P5 London 20 100
S1 P6 London 20 100
S2 P1 Paris 10 300
S2 P2 Paris 10 400
S3 P2 Paris 10 200
S4 P2 London 20 200
S4 P4 London 20 300
S4 P5 London 20 400
62
????????? 1NF ?????????????

• ??????????? First ????????? 1NF ?????????
  ??????? data redundancy ??? ??? 2 ?????? ?????
  S ??????? ?????? City ?????????? ??? P
  ??????????? ??????????????????????????? 1NF
  ?????????????
• ???????????????????? 3 ??????????????? First
• ????????????????????????????????????????
  ??????????? ??? 2NF

63
?????????????????? First

• ???????? insert
• ?????????????? supplier ??????????????????????????
  ???????????? ?????? supplier ?????????????????????
  ?????
• ???????? delete
• ???????????? S3 ??? P2 ?? 100 ???? ????????????
  3 ????? ???????? 2 ?????????????????
• ???????? update
• ???????????????????????????????
  ???????????????????????????????????

64
????????????????????? ER

• ??????????????????? ER ???? ?????????????? 1NF
  ???? ??????? ER-model ?? single attribute,
  multivalued attribute,??? composite attribute
  ????????????????
• ??????? composite ??? multivalued attribute
  ?????????????????????? ER ??????????????????
  ????????????????? mulivalued ??? composite
  attribute ??????????????????????????

65
?????????????????????(Second Normal Form 2NF)

• ??????????? ???????? 2NF ??????????
• ?????????????????? 1 NF ???
• ???? nonkey attribute ??????? partially
  functional dependency ?? Candidate Key
  ??????????? ????
• ???? nonkey attribute ??????????????? Candidate
  key ??? Full functional dependency

Nonkey attribute ??? attribute ???????????????????
??? Candidate Key ????????
66
?????????????????????(Second Normal Form 2NF)

• ??? nonkey attribute ?????????????? Candidate
  key ??? Full FD ??????? ???????????
  ???????? nonkey attribute ??????????? Candidate
  key ??? Full FD ????????????????????????? 2NF
• ???? ????? First ???????????? 2NF

67
??????? ????? First

• ????? First( S,P,Status,City,QTY) ?????? 1NF
• FD-diagram ???????? First

City
S
QTY
P
Status
68
??????? ????? First (con.)

• ??? FD S,P ? City
• ????? nonkey (City) ?????????? primary key ???
  Full FD
• ???????????????? FD S ? City ??????????????
  ??????? FD S,P ? City ??????? Full FD
  ??? FD S ? City ?????????????? Partial FD
• ??? FD S,P ? Status
• ????? nonkey (Status) ?????????? primary key ???
  Full FD
• ???????????????? FD S ? Status
  ?????????????? ??????? FD S,P ? Status
  ??????? Full FD ??? FD S ? Status
  ?????????????? Partial FD

69
??????????????????????? 2NF

• ????? First ???????????? 2NF ?????????????????????
  ? ???????????????????? 2NF
• ????? R ??????????? ??? R(A,B,C,D) ????? FD A?
  D ?????????????????
• R1(A,D)
• R2(A,B,C)

70
???????? First

• ????? First ?????? 1NF
• ???????????? 2NF ?????????????????????? 2
  ????????
• Second( S,Status,City
• SP( S,P,QTY
• ???? Second ??? SP ???????????? 2NF ????
• ???????????? Second ??? SP ??????????????????????
  ? First ????? Second ??? SP ??????????????????????
  ?????????? First ????????????????????????????????

71
????????? ???????? First

• ????? First ???? 2 ???????????????? ??? Shipment
  ????????? Supplier ???????????? First ???????????
  2 ????????
• Second( S,Status,City)
• SP( S,P,QTY)

72
???????? ????? R1

• R1(A,B,C,D) ???
• FD A,B?C ??? B?D
• Nonkey attribute ??? C, D
• A,B? C ??????? C Full Functional Dependency
  ?? Primary Key
• B?D ??????? D Partial Functional Dependency
  ?? Primary Key ( ??? D functional dependency ?? B
  ??? B ???? attribute ?????????? Primary key A,B
  )
• ??????? ????? R1 ???????????? 2NF

73
???????? ????? R2

• R2(A,B,C,D) ???
• FD A,B?C ??? A,B?D
• Nonkey attribute ??? C, D
• A,B? C ??????? C Full Functional Dependency
  ?? Primary Key
• A,B?D ??????? D Full Functional Dependency ??
  Primary Key
• ??????? ????? R2 ????????? 2NF

74
???????? ?????????????
??????????? ??????????? ????????? (???????) ???????????????????????????????? (???)
111 Proj1 15 30
113 Proj1 15 15
112 Proj2 25 24
112 Proj3 30 45
113 Proj2 25 60
112 Proj1 15 65
114 Proj3 30 40
75
???????? ?????????????(???)

• nonkey attribute ??? ????????? ??? ?????????????
• ???????????????? ???????????? 2NF ??????????????
  partial FD ?? Primary key ????????? 1
  ?????????????????????????? ??????????????????????
  ?????????????
• ??????? 2 ????????????????????????????????????
  ??????????????????????????? ?????????????????????
  ???????????????? ???????????????????? ???????
  ????? ??????? ??? ????????????

76
???????? ?????????????(???)
???????
???????
??????????? ?????????
Proj1 15
Proj2 25
Proj3 30
??????????? ??????????? ????????
111 Proj1 30
113 Proj1 15
112 Proj2 24
112 Proj3 45
113 Proj2 60
112 Proj1 65
114 Proj3 40
77
???????? ?????????????(???)

• ????????????(???????????,?????????)
• nonkey ??? ?????????
• FD ??????????? ? ?????????
• ????????? ???? Full Functional Dependency ??
  Primary Key
• ??????? ???????????? ????????? 2NF
• ????????????(???????????,???????????,????????????
  ?)
• Nonkey ??? ?????????????
• FD ???????????,??????????? ? ?????????????
• ????????????? ???? Full Functional Dependency ??
  Primary Key
• ??????? ???????????? ????????? 2NF

78
?????????????
????????? 2NF
???
????????????
????????????
?????? 2NF
???????????? ?????????? ??????????????????????????
????????????? ????????????????? ????
??????????????????????? ????????????
???????????? ?? natural join ???????????????
????????????????????????????????
Join
?????????????
79
?????????

• ???????????? ?? primary key ??? ???????????
  ?????????? ?? foreign key ??? ???????????
  ????????????????????????
• ????? 2 ??????? join ??? ??????????????????
  foreign key primary key ????????????????????????
  ???????? ???????????????????

80
?????????????????????(Third Normal Form 3NF)

• ??????????? ???????? 3NF ??????????
• ?????????????????? 2NF ???
• ???? nonkey attribute ?????????Transitive
  Functional Dependency ?? Candidate key
  ??????????? ???????????????
• ??????? FD ??????? nonkey attribute

3NF ?????????????????????????????? 2NF ???????
data redundancy ?????????
81
???????? ????? R1

• R1(A,B,C,D)
• FD A,B ? C , A,B ? D
• Nonkey ??? C,D
• A,B? C ??????? C Full FD ?? PK
• A,B? D ??????? D Full FD ?? PK
• ??????? R1 ????????? 2NF
• A,B? C ??????? C ??? Transitive FD ?? PK
• A,B? D ??????? D ??? Transitive FD ?? PK
• ??????? R1 ????????? 3NF

82
???????? ????? R2

• R2(A,B,C,D)
• FD A,B ? C , C ? D
• Nonkey ??? C,D
• ?????? C
• A,B? C ??????? C Full FD ?? PK
• ?????? D
• A,B? C ??? C? D ???????? A,B ? D
• A,B ? D ??????? D Full FD ?? PK
• ??????? R2 ????????? 2NF
• ?????? C
• A,B? C ??????? C ??? Transitive FD ?? PK
• ?????? D
• A,B? C ??? C? D ???????? A,B ? D
• A,B ? D ??????? D Transitive FD ?? PK
• ??????? R2 ???????????? 3NF

83
??????????????????????? 3NF

• ????? R ??????????? ??? R(A,B,C,D) ????? FD B
  ? C ???? ?????????????
• R1(B,C)
• R2(A,B,D)

84
????????

• ?????????????????????? 2NF ???????????? 3NF
• ????? Second( S,City,Status)

FD-Diagram
1
S
City
2
3
Status
85
????????(???)

• ??????? Transitive Dependency ???
• S ? Status
• ???????????? Transitive Dependency ??????????????
  2 ????????
• ????? SC( S,City)
• ????? CS( City,Status)
• ???? 2 ?????????????? 3NF ????

86
???????? ??????????????????????? FD ??????????????

• ????? Second( S,City,Status)
• ?? FD 3 ??????
• S ? City
• City ? Status
• S ?Status
• ??????????? A ???
• SC(S,City)
• CS(City,Status)
• ??????????? B ???
• SC(S,City)
• SS(S,Status)

87
???????? ??????????????????????? FD ??????????????

• ???????????? A ??????????????????? ??? B
  ???????????????? FD ???? 3 ?????????
• S ? City
• City ? Status
• S ?Status
• ?????? B ??? FD 2 ??????
• S ? City
• S ?Status

88
???????????????????????(Boyce/Codd Normal Form
BCNF)

• ???? normal form ????????? 3NF ??????? data
  redundancy ?????????????
• ???????? BCNF ?????????? 2NF,3NF ????????
• 2NF ??????????????????????????? 1NF
• 3NF ??????????????????????????? 2NF
• ??? BCNF ?????????????????????????? 1NF,2NF????
  3NF ?????????
• ???????????????????????????????? BCNF
  ??????????? ???????????????????? BCNF ????
  ???????? 1NF,2NF ??? 3NF ???? ????????????????????
  ?? 3NF ?????????????? BCNF ?????

89
???????????????????????(Boyce/Codd Normal Form
BCNF)

• ??????????? ????????????????????? BCNF
  ??????????? ????????????????????
  ????????????????????????????? 3 ?????????????
• 1. Candidate key ??????? 2 ?????????
• 2. Candidate key ??????? 2 ????????? ???
• ???? Composite key ????
• 3. Candidate key ??????? 2 ????????? ???
• ???? Composite key ??????????????????????(Overlapp
  ed)???? CK ??? S,J ??? S,T
• ?????????????????????????? ???????????????
  1NF,2NF,3NF ??????????????????????
  ???????????????????????????????????????????
• ????????????????????????? ?? ???????????? BNCF
• ???????????????????????? 3 ??????
  ????????????????????????? 1NF,2NF,3NF ??? BCNF

90
???????????????????????(Boyce/Codd Normal Form
BCNF)

• ???????????????????????????????????????? 3 ???
  ???????? ???????????????????????????????? BCNF
  ??????????
• ??????????????????????? primary key ????
  candidate key ?????????? nonkey ???????????
• Determinant ?????????????? Candidate key

91
???????? ????? R1

• R1(A,B,C,D)
• Candidate Key ??? AB
• FD AB?C ,AB?D
• Determinant ??? R1 ??? AB
• ??????? R1 ??????????? BCNF

92
???????? ????? R2

• R2(A,B,C,D)
• Candidate Key ??? AB
• FD AB?C ,C?D
• Determinant ??? R2 ??? AB, C
• Determinant AB ???? Candidate Key
• Determinant C ??????? Candidate Key
• ??????? R2 ?????????????? BCNF

93
???????? ????? R3

• R3(A,B,C,D)
• Candidate Key ??? AB, C, D
• FD AB?C ,C?D, D?AB
• Determinant ??? R2 ??? AB, C, D
• Determinant AB ???? Candidate Key
• Determinant C ???? Candidate Key
• Determinant D ???? Candidate Key
• ??????? R3 ??????????? BCNF

94
???????? ???????????

• ??????????????????? 1 ?????????????? 1 ????
  ?????????????? 1 ???? ?????????????????
  (???????????????????????? ????????????????????????
  ???)
• ???????????????????????????????????????????????

?????????? ???? ???????? ????? ?????????????
????? 1/2548 ?????????????????? Sec1 40
????? 1/2548 ?????????????????? Sec2 30
????? 1/2548 ??????????????? Sec1 45
???? 2/2548 ?????????????????? Sec1 40
????? 2/2548 ????????????????? Sec1 45
????? 2/2548 ????????????????? Sec2 30
95
???????? ???????????(???)

• Candidate Key ?? 2 ??? ???
• ????????,????,?????
• ??????????,????,?????
• FD
• ??????????,???? ? ????????
• ????????,???? ? ??????????
• ????????,????,????? ? ?????????????
• ?? determinant 3 ??? ??? ??????????,???? ,
  ????????,???? , ????????,????,?????
• Nonkey attribute ??? ?????????????

96
???????? ???????????(???)

• ??????????? ??? ?????????? BCNF ????? Candidate
  Key ?? 2 ??? ???????? composite key ???
  Overlapped ????
• ??????????? ??????? BCNF ?????
• determinant ??????????,???? ??????? CK
• determinant ????????,???? ??????? CK
• ???????????? normal form ????????????????
• ???? 1NF ????
• ???? 2NF ???? ????? ????????????? Full FD ??
  Primary key ?????
• FD ????????,????,????? ? ?????????????
• ???? 3NF ???? ?????????????????? ??? Transitive
  FD ?? Primary Key

97
???????? ???????????(???)

• ????????? ??????????????????? ???????????????
  ????????????????????? 3NF ?????
• ??? FD ??????????,???? ? ???????? ????????
  2 ????? ??????????????????????????????????????????
  ??? ???????????????????????????????????????? ???
  ??????????,???? ?????????? Candidate Key ????
• ??? FD ????????,???? ? ?????????? ????????
  2 ????? ?????????????????????????????????????????
  ???????????????????????????????????????????? ???
  ????????,???? ?????????? Candidate Key ????
• ?????????????????????????????? ??????????????? 2
  ????????
• R1(??????????,????,????????)
• R2(????,????????,?????,?????????????)

98
???????? ???????????(???)

• R1(??????????,????,????????)
• FD ??????????,???? ? ???????? ,
  ????????,???? ? ??????????
• Candidate Key ??? ????????,????,
  ??????????,????
• Determinant ??? ????????,????,
  ??????????,????
• ????? nonkey attribute
• ??????? ????? R1 ???? BCNF ???? ?????
• Determinant ??????????,???? ???? Candidate Key
  ??? R1
• Determinant ????????,???? ???? Candidate Key
  ??? R1

99
???????? ???????????(???)

• R2(????,????????,?????,?????????????)
• FD ????????,????,????? ? ?????????????
• Candidate Key ??? ????????,????,?????
• Determinant ??? ????????,????,?????
• nonkey attribute ??? ?????????????
• ??????? ????? R2 ???? BCNF ???? ?????
• Determinant ????????,????,????? ???? Candidate
  Key ??? R2

100
???????? ???????????(???)

• ???????????????? R1 ??? R2 ???? BCNF
  ???????????????????????????????? 3NF
• ????????? R1 ??? R2 ??????????????????????????????
  ??????????????????? ???????????????????????
  ??????? R1 ??? R2 ???????????????????????????????
  ???????????

101
????????????????????????? BCNF

• ????? SSP( S,SName, P,QTY)
• ?? Candidate Key 2 ??????
• S,P
• SName,P

102
????????????????????????? BCNF

• ?? FD 4 ??????
• S ? SName
• SName ? S
• S,P ?QTY
• SName,P ?QTY
• ???????????? FD ??determinant 2 ??? ????????? CK
• ???????????? SSP ????????? BCNF ?????????????? 2
  ????????
• SS( S,SName)
• SP( S,P,QTY)

2 ?????????????? BCNF
103

• ?????????????????????? 3NF ???? BCNF?????????
  ??????????????????????????????????????????????????
  ????????????????????
• ?????????????????????????? ?????????????????????
  ????????????????????????????????????????? 4 ?????
• ????????? ?????????????? Insert/Update/Delete
  ?????? ???????????? 4NF ???
• ????????? ?????????? Normalized ???????? BCNF
  ?????????????

104
Denormalization

• ????????????????????? R1,R2Rn ????????? join
  ??? ??????? R
• ????????????????????????????? ????????????????????
  ?????????????????????????????????????????????
• ???? ????????????? ?????????????????? 3NF
  ?????????????????????? 2 NF ??????????????????????
  ??????????????????????????????????????????????????
  ???????????????????????????????

105
Denormalization

• ???????????????????? ????????????????????????????
  ????????????? ????????????????????????????????????
  ?????????????????????????????????????
• ?????????????????????????????? ???????????????????
  ????? ???????????????????????????????????????????
  ???????????????????