Database Design

Mr.WarawutMr.Warawut KhangkhanKhangkhanFacebookFacebook: : http://www.facebook.com/AjWarawuthttp://www.facebook.com/AjWarawut

Twitter: Twitter: http://twitter.com/awarawuthttp://twitter.com/awarawut

EE--Mail: Mail: [email protected]@hotmail.com

Mobile: Mobile: 089089--461461--95919591

�� 5 5 ก�ก�ก�� ก�� ((Database Design)Database Design)

Mobile: Mobile: 089089--461461--95919591

��ก)*+ก,-.-/,01,+2/*345 67,89:+;/*0**ก<==1,+2/*345

8.>?ก)*+

�ก,-**ก<==@>AB>6CD)E?F+G5,?B/,+

Mr.Warawut Khangkhan Chapter 05 Database Design 2

ก,-**ก<==@>AB>6CD)E?F+G5,?B/,+�ก,-B4<5-HกI,-C==1,+2/*345

�2/*345@>AJB/3>KE,389:+-C==-C8=>?=

�.,3,-L-E=-E32/*345;),0M 82/,B/E?กH+JB/.CBEก -EB8-NE

�ก,-895>A?+<950O,?G5H0JB/J3)?P)0?,ก

��ก��ก��ก��ก�� 8*+@Q;>R (Entity)

�<*;@-Q=QE;V (Attribute)

�KE,3.H3WH+XV (Relationships)


�KE,3.H3WH+XV (Relationships)

�� !�� ! (Entity)(Entity)� 89:+K7,@>A*/,0L[0=PKK5 .L,+@>A <5C.QA02*0;),0M

�� L/,;/*0ก,-.-/,0-C==1,+2/*3458ก>A?EกH=ก,-.HA0\]R*.Q+K/, 8*+@Q;>R@>A8ก>A?E2/*0กH=-C==ก,-.HA0\]R*


ก,-.HA0\]R*.Q+K/, 8*+@Q;>R@>A8ก>A?E2/*0กH=-C==ก,-.HA0\]R*.Q+K/,9-Cก*=J9B/E? 8*+@Q;>R54กK/, F=.HA0\]R*.Q+K/, กH=.Q+K/,

��ก��(Customers)

0��12!(Orders)

��(Products)

� ��7 �� 7 � (Attribute)(Attribute)� 89:+2/*345@>A<.B05HกI^C2*08*+@Q;>R

� 8*+@Q;>R54กK/, 6C3>D]A* @>A*?4) <5C-GH.J9-I >̂?V��ก�� (Customers)

-GH.54กK/,8*+@Q;>R


��ก�� (Customers)-GH.54กK/,D]A*54กK/,

@>A*?4)54กK/,-GH.J9-I >̂?V

G3,?852_@- H̀W@V

<*;@-Q=QE;V (@HR0G3B)

� ��7 �� 7 � (Attribute)(Attribute)

0��12! (Orders)

-GH.F=.HA0\]R*EH+@>A.HA0.Q+K/,EH+@>A.)0.Q+K/,

�� (Products)

-GH..Q+K/,D]A*.Q+K/,

-,K,;)*G+)E?


EH+@>A.)0.Q+K/,-GH.54กK/,@>A.HA0-GH..Q+K/,@>A.HA0

67,+E+.Q+K/,@>A.HA0.)E+5B

-,K,;)*G+)E?-E3O,I>B/E?G-]*J3)67,+E+.Q+K/,F+K5H0

-GH.D+QB.Q+K/,D]A*D+QB.Q+K/,

�7��:��;��7��:��;� (Relationships)(Relationships)�G3,?L[0

KE,3.H3WH+XV-CGE),08*+@Q;>R;),0M


@>A*?4)54กK/,

0��12! (Orders)

-GH.F=.HA0\]R*EH+@>A.HA0.Q+K/,EH+@>A.)0.Q+K/,

1 N


8*+@Q;>R;),0M F+-C==

@>A*?4)54กK/,-GH.J9-I >̂?V

G3,?852_@- H̀W@V

EH+@>A.)0.Q+K/,-GH.54กK/,@>A.HA0-GH..Q+K/,@>A.HA0

67,+E+.Q+K/,@>A.HA0.)E+5B

��A��:��;� ��A��:��;� (Relational Database)(Relational Database)

�K]* ก,--E=-E38*+@Q;>R@>A*?4)F+-C==@>A3>KE,3.H3WH+XV


@>A*?4)54กK/,

0��12! (Orders)

-GH.F=.HA0\]R*EH+@>A.HA0.Q+K/,

1 N


KE,3.H3WH+XV-CGE),0กH+82/,JE/B/E?กH+

D]A*54กK/,@>A*?4)54กK/,

-GH.J9-I >̂?VG3,?852_@- H̀W@V

EH+@>A.HA0.Q+K/,EH+@>A.)0.Q+K/,

-GH.54กK/,@>A.HA0-GH..Q+K/,@>A.HA0

67,+E+.Q+K/,@>A.HA0.)E+5B

��C�:��0A�0� ��C�:��0A�0� ��DE�F� (Field) / � ��7 � (Attribute)

G+)E?2/*345@>A9-Cก*=3,6,ก*Hก2-C;),0M G5,?*Hก2-C

� ��F (Record) / ��:�� (Tuple)


��F (Record) / ��:�� (Tuple)6C89:+ก,-+7,bc5BVG5,?M bc5BV3,-E3กH+

� �� (Table) / ��A�� (Relation)6C89:+ก,-+7,8-K*-VBG5,?M 8-K*-VB3,-E3กH+

��C�:��0A�0� ��C�:��0A�0� ��

003 P&S STORE D HR+ 1 *,K,-8.-Q33Q;-@,E8E*-V 159 .P2 P3EQ@ 21 (*_`ก) K5*08;? ก@3.

015 Happy Shop .3,K3k-HA08`. 29 L..,X-F; / @ P)03G,83l ?,++,E, ก-P08@Wm

J��ก� �� A2 K��ก� �� L�K��ก� ��

FieldField



181 The Book Shop Asia Hotel 296 L.Wo,J@ ก-P08@Wm

213 P'S Line 2675 G34) 9 5,BW-/,E \.95 EH0@*0G5,0 =,0กC9 Q ก@3.

008 The Bookseller 428 Rama I Road, Siam Square Bangkok.

RecordRecord

A��F��7��:��;�A��F��7��:��;� (Relationships)(Relationships)�KE,3.H3WH+XVG+[A0;)*G+[A0

(One-to-One Relationships)

�KE,3.H3WH+XV<==G+[A0;)*ก5P)3


KE,3.H3WH+XV<==G+[A0;)*ก5P)3(One-to-Many Relationships)

�KE,3.H3WH+XV<==ก5P)3;)*ก5P)3(Many-to-Many Relationships)

OneOne--toto--One RelationshipsOne Relationships ((11::11))� 89:+KE,3.H3WH+XV@>A@7,KE,382/,F60),?@>A.PB 8+]A*06,ก

8-K*-VB 1 8-K*-VBF+;,-,0G+[A06C3>KE,3.H3WH+XVกH=*>ก8-K*-VB 1 8-K*-VBF+*>ก;,-,0G+[A08@),+HR+ J3).,3,-L3>8กQ+ 1 JB/ 8D)+ ;,-,0-,K,.Q+K/, <5C;,-,0


.,3,-L3>8กQ+ 1 JB/ 8D)+ ;,-,0-,K,.Q+K/, <5C;,-,067,+E+.Q+K/,6C3>KE,3.H3WH+XV<== 1:1

1:1 J��

0005

0100

0125

0051

0010

0007

0008

0023

0055

VIDEO CONFERENCING CARD-FLY VIDEO OF SOUND

D PBก5/*0L ),?E>B >_* Panasonic Model VHS -P)+ NV-VX3

JAPANESE 1, LIYC

JAPANESE 2, LIYC

US ATLAS

SECRETS OF STARGATE

SALES MANAGER FACTOMATIC

W 4B*?),03>E,@C

K 4)3 ]*.7,G-H=� 4/+�F+?PK 90

A2 K�� KJ� K7L (��)

219.63

219.63

700.93

1,842.99

540.19

369.16

260.00

278.00

278.00

P��

Yes

Yes

No

No

No

Yes

Yes

No

No

��L��LF��

1


J��

0005

0100

0125

0051

0010

0007

0008

0023

0055



JAPANESE 1, LIYC

JAPANESE 2, LIYC

US ATLAS

SECRETS OF STARGATE


W 4B*?),03>E,@C

K 4)3 ]*.7,G-H=� 4/+�F+?PK 90

A2 K�� QR�7�0��

400

155

169

197

159

205

177

326

159

��QR�7�� 0��1

1

OneOne--toto--One RelationshipsOne Relationships ((11::11))�KE,3.H3WH+XV<== 1:1 8-,.,3,-L-E389:+;,-,08B>?E

JB/

�F+=,0K-HR08-,;/*0.-/,0KE,3.H3WH+XV<==+>R 8+]A*06,ก


F+=,0K-HR08-,;/*0.-/,0KE,3.H3WH+XV<==+>R 8+]A*06,ก8G;P�5=,09-Cก,- 8D)+ ;/*0ก,-;,-,0@>AFD/89:+<G5)02/*345F+ก,-.-/,0-,?0,+_B?8�W,C 8W]A*8WQA3KE,38-NEF+ก,-@7,0,+ 89:+;/+

OneOne--ToTo--Many Relationships (Many Relationships (11:N):N)� 89:+KE,3.H3WH+XV<==@>AW==)*?@>A.PBF+-C==

1,+2/*345@HAEJ9

�KE,3.H3WH+XV<==+>R89:+KE,3.H3WH+XV@>A8-K*-VB 1


KE,3.H3WH+XV<==+>R89:+KE,3.H3WH+XV@>A8-K*-VB 1 8-K*-VBF+;,-,0G+[A0 6C.H3WH+XVกH=67,+E+8-K*-VB 2 8-K*-VB G-]*3,กกE),F+*>ก;,-,0G+[A0 8D)+ 54กK/,.,3,-L.HA0.Q+K/,JB/G5,?F=.HA0\]R*.Q+K/, <;)F=.HA0.Q+K/,F=G+[A06C3>54กK/,.HA08W>?0-,?8B>?E8@),+HR+

1:N003 P&S STORE D HR+ 1 *,K,-8.-Q33Q;-@,E8E*-V 159 .P2 P3EQ@ 21 (*_`ก) K5*08;? ก@3.


181 The Book Shop Asia Hotel 296 L.Wo,J@ ก-P08@Wm

213 P'S Line 2675 G34) 9 5,BW-/,E \.95 EH0@*0G5,0 =,0กC9 Q ก@3.

008 The Bookseller 428 Rama I Road, Siam Square Bangkok.

J��ก� �� A2 K��ก� �� L�K��ก� ��

��ก� ��

J��ก��12 �

��0��12 �

J��ก� �� 7�� 7�� K�� J�� QR�7�� 12 � �K7��F (%)

11

NN

J��ก��12 �

10258

10265

10278

J��ก� ��

015

181

015

7��

21/10/xx

21/10/xx

24/10/xx

7�� K��

28/10/xx

27/10/xx

3/11/xx

J��

0005

0100

0125

0005

0010

0007

0008

0023

QR�7�� 12 �

8

8

15

31

15

16

31

4

�K7��F (%)

25.00%

0.00%

0.00%

0.00%

0.00%

0.00%

25.00%

0.00%

NN

ManyMany--toto--Many Relationships (M:N)Many Relationships (M:N)� 89:+KE,3.H3WH+XV<==@>AJ3).,3,-L<.B0FG/8GN+JB/0),?

8+]A*06,ก8-,.,3,-L.-/,0KE,3.H3WH+XV<==+>RJB/ _B?.-/,0;,-,0FG3)@>AKE,3.H3WH+XV<== Many-to-One กH=;,-,0 2 ;,-,0@>A3>KE,3.H3WH+XV<== Many-to-Many


;,-,0 2 ;,-,0@>A3>KE,3.H3WH+XV<== Many-to-Many Relationships

ManyMany--toto--Many Relationships (M:N)Many Relationships (M:N)�;HE*?),0 F=.HA0\]R*.Q+K/,@>A54กK/,.HA0\]R*3, 6C.,3,-L3>

.Q+K/,@>A.HA0JB/3,กE), 1 *?),0F+F=G+[A0 <5CF+@,0ก5H=กH+.Q+K/,+HR+.,3,-L*?4)F+F=.HA0\]R*JB/G5,?F=8D)+กH+ KE,3.H3WH+XV-CGE),0F=.HA0.Q+K/,กH=.Q+K/,6[0


8D)+กH+ KE,3.H3WH+XV-CGE),0F=.HA0.Q+K/,กH=.Q+K/,6[089:+<== Many-to-Many

M:NM:NJ��ก��12 �

��0��

10258

10265

10278

J��ก� ��

015

181

015

7��

21/10/xx

21/10/xx

24/10/xx

7�� K��

28/10/xx

27/10/xx

3/11/xx

J��ก��12 �

��Lก��

10258

10258

10258

10265

10265

10278

10278

10278

J��

0005

0100

0125

0005

0010

0007

0008

0023

QR�7�� 12 �

8

8

15

31

15

16

31

4

�K7��F (%)

25.00%

0.00%

0.00%

0.00%

0.00%

0.00%

25.00%

0.00%

��

11

NN

NN

NNNN

NN

Chapter 05 Database Design

J��

0005

0100

0125

0051

0010

0007

0008

0023

0055



JAPANESE 1, LIYC

JAPANESE 2, LIYC

US ATLAS

SECRETS OF STARGATE


W 4B*?),03>E,@C

K 4)3 ]*.7,G-H=� 4/+�F+?PK 90

A2 K�� KJ� K7L (��)

219.63

219.63

700.93

1,842.99

540.19

369.16

260.00

278.00

278.00

P��

Yes

Yes

No

No

No

Yes

Yes

No

No

��

QR�7�0��

400

155

169

197

159

205

177

326

159

11

A��F��L�0� ��A��:��;�A��F��L�0� ��A��:��;��Primary Key: PK (K>?VG5Hก)

�Candidate Key (K>?VK4)<2)0)

�Composite Key


�Composite Key

�Foreign Key: FK

Primary Key: PKPrimary Key: PK�6C89:+bc5BV@>A3>K),J3)\R7,กH+85?F+<;)5C8-K*-VBF+;,-,0

+HR+ 8-,.,3,-LFD/bc5BV@>A89:+ Primary Key +>R89:+;HE<@+2*0;,-,0+HR+JB/@H+@>


Candidate KeyCandidate Key� 89:+bc5BVG+[A0G-]*G5,?bc5BV@>AW*8*,3,-E3กH+<5/E3>

KP^.3=H;Q89:+ Primary Key <5CJ3)JB/L4ก89:+K>?VG5Hก 8D)+ -GH.6H0GEHB89:+K>?VG5Hก .)E+D]A*6H0GEHBกNJ3)\R7,8D)+กH+ <;)J3)JB/89:+K>?VG5Hก6[089:+K>?VK4)<2)0<@+


8D)+กH+ <;)J3)JB/89:+K>?VG5Hก6[089:+K>?VK4)<2)0<@+

Composite KeyComposite Key�=,0;,-,0G,bc5BV@>AJ3)\R7,J3)JB/85? 6[0;/*0FD/G5,?M

bc5BV3,-E3กH+89:+ Primary Key bc5BV@>AFD/-E3กH++>R8-,8->?กE), Composite Key


Foreign KeyForeign Key� 89:+bc5BVFBM F+;,-,0G+[A0 (k��0 Many) @>A3>

KE,3.H3WH+XVกH=bc5BV@>A89:+ Primary Key F+*>ก;,-,0G+[A0 (k��0 One) _B?@>A;,-,0@HR0 2 3>KE,3.H3WH+XV<==One-to-Many ;)*กH+


One-to-Many ;)*กH+

J��ก��12 �

��0��

10258

10265

10278

J��ก� ��

015

181

015

7��

21/10/xx

21/10/xx

24/10/xx

7�� K��

28/10/xx

27/10/xx

3/11/xx

J��ก��12 �

��Lก��

10258

10258

10258

10265

10265

10278

J��

0005

0100

0125

0005

0010

0007

QR�7�� 12 �

8

8

15

31

15

16

�K7��F (%)

25.00%

0.00%

0.00%

0.00%

0.00%

0.00%

11NN


10278 015 24/10/xx 3/11/xx 10278

10278

10278

0007

0008

0023

16

31

4

0.00%

25.00%

0.00%Primary KeyPrimary Key

Foreign KeyForeign Key Composite KeyComposite Key

ก�ก�� ก�ก�� 89:+2HR+;*+<-กF+ก,-.-/,0<*WW5Q8KDHA+1,+2/*345

(Application Database)

�6C;/*0ก7,G+B8*+@Q;>R2*0-C==J96+L[0;,-,0


6C;/*0ก7,G+B8*+@Q;>R2*0-C==J96+L[0;,-,01,+2/*345

QXF��0�ก�ก�� QXF��0�ก�ก�� 5BKE,3\R7,\/*+2/*345F+1,+2/*345�;*=.+*0KE,367,89:+F+ก,-8->?กFD/2/*345F+8E5,@>A.HR+@>A.PB�D)E?FG/;-E6.*=KE,3L4ก;/*0-E3@HR06HB3,;-1,+2*02/*345

JB/.CBEก (�5;)*8+]A*06,ก2/*<-ก)


JB/.CBEก (�5;)*8+]A*06,ก2/*<-ก)�.,3,-Lก7,G+B5HกI^Cก,-82/,L[02/*345.7,G-H=�4/FD/<;)5C

9-C8O@JB/�@7,FG/3>KE,3*Q.-C-CGE),02/*345กH=<*WW5Q8KDHA+

��!� �0�ก�ก�� !� �0�ก�ก�� 1. ก7,G+B8*+@Q;>R@Pก;HEF+-C==1,+2/*345+HR+M

2. ก7,G+BK>?VG5Hก <5C<*;@-Q=QE;V;),0M 2*08*+@Q;>R

3. ก7,G+BKE,3.H3WH+XV-CGE),08*+@Q;>R;),0M


3. ก7,G+BKE,3.H3WH+XV-CGE),08*+@Q;>R;),0M

4. @7,ก,-895>A?+8*+@Q;>R@>AJB/J9*?4)F+-49;,-,0 _B?FD/ก�ก,- Normalization

NormalizationNormalization� 89:+@�I�>@>AFD/F+ก,-@7,FG/8*+@Q;>R <5C<*;@-Q=QE;V@>AJB/

**ก<==JE/ L4ก6HBก5P)389:+;,-,0@>A3>KE,3.H3WH+XVกH+� 6PB9-C.0KV2*0ก,- Normalization K]*

�5BKE,3\R7,\/*+2*02/*345F+;,-,0 8W]A*6CJB/J3);/*0<ก/J22/*345


�5BKE,3\R7,\/*+2*02/*345F+;,-,0 8W]A*6CJB/J3);/*0<ก/J22/*345F+G5,?M @>A

�@7,FG/ก,-895>A?+<950<ก/J2_K-0.-/,02*0;,-,0F+O,?G5H0@7,FG/0),?

�@7,FG/ก,-895>A?+<950_K-0.-/,02/*345 3>�5ก-C@=;)*<*WW5Q8KDHA+@>A82/,L[02/*345F+1,+2/*345+/*?@>A.PB

0NF � ?H0J3)JB/L4ก Normalization

1NF � ;/*0J3)3>ก5P)3@A7,\R7,กH+ (Repeating Group)

2NF � ;/*0J3)3><*;@-Q=QE;V@>AJ3)FD)K>?V;HEFB2[R+กH=.)E+FB.)E+G+[A02*0K>?V2NF

3NF� ;/*0J3)3><*;@-Q=QE;VFBF+;,-,02[R+กH=<*;@-Q=QE;V*]A+@>AJ3)FD) PK

G-]* Candidate Key

4NF � ;/*0J3)3>ก,-2[R+;)*กH+<==8DQ0ก5P)3 (Multivalve Dependency)


กZ�� กZ�� 1 1 (First Normal Form)(First Normal Form)�6C;/*0J3)3>8\55VFBF+;,-,0@>A3>K),8กQ+G+[A0K),

� 8-,.,3,-L@7,FG/;,-,0�),+ก�2/*@>A 1 JB/B/E?ก,-<?ก8\55V@>A3>K),8กQ+G+[A0**ก89:+8-K*-VBFG3)


ก,-<?ก8\55V@>A3>K),8กQ+G+[A0**ก89:+8-K*-VBFG3)

กZ�� กZ�� 2 2 (Second Normal Form)(Second Normal Form)�;,-,0@>A�),+ก�2/*@>A 2 6C;/*0J3)3><*;@-Q=QE;V@>AJ3)FD)

K>?V;HEFB (8-,8->?กE), Non-key Attribute) 2[R+กH=.)E+FB.)E+G+[A02*0K>?VG5Hก 6C;/*02[R+กH=K>?VG5Hก<==8;N3M 8@),+HR+


8@),+HR+

� 8-,.,3,-L@7,FG/;,-,0�),+ก�2/*@>A 2 _B?ก,-<?กbc5BV@>A2[R+8�W,CกH=.)E+G+[A02*0K>?VG5Hก กH=bc5BV@>A3H+2[R+B/E? **ก3,.-/,089:+;,-,0FG3)

กZ�� กZ�� 3 3 (Third Normal Form)(Third Normal Form)�;,-,0@>A�),+ก�2/*@>A 2 6C;/*0J3)3><*;@-Q=QE;VFBF+

;,-,02[R+กH= <*;@-Q=QE;V*]A+@>AJ3)FD)K>?VG5Hก G-]*K>?VK4)<2)0


� 8-,.,3,-L@7,FG/;,-,02*08-,�),+ก�2/*@>A 3 B/E?ก,-<?กbc5BV@>A2[R+กH=bc5BV*]A+M +HR+**ก3,89:+;,-,0FG3) <5CK>?VG5Hก2*0;,-,0FG3)6C89:+bc5BV@>Abc5BV+HR+2[R+B/E?

กZ�� กZ�� 4 4 (Fourth Normal Form)(Fourth Normal Form)�;,-,0@>A�),+ก�2/*@>A 4 6C;/*0J3)3>ก,-2[R+;)*กH+<==

8DQ0ก5P)3 (Multivalve Dependency) \[A089:+KE,3.H3WH+XV<== Many-to-Many O,?F+;,-,08B>?EกH+ 8D)+ F+;,-,08B>?EกH+6C3>bc5BV B @>A2[R+;)*bc5BV A _B?ก,-2[R+


;,-,08B>?EกH+6C3>bc5BV B @>A2[R+;)*bc5BV A _B?ก,-2[R+;)*กH++>R89:+*Q.-C6,ก*>กbc5BVG+[A0 K]* bc5BV C 8-,.,3,-L@7,FG/;,-,0�),+ก�2/*@>A 4 JB/_B?<?ก;,-,0FG3)\[A09-Cก*=B/E?bc5BV@>A8ก>A?E2/*0กH+8@),+HR+

EE--R DiagramR Diagram


Database Design

Education

Transcript of Database Design