business computer project 4

36
(Database Database Design) Design) .

Upload: warawut

Post on 18-Nov-2014

663 views

Category:

Education


3 download

DESCRIPTION

Database Design

TRANSCRIPT

Page 1: Business Computer Project 4

������������������ ������������������� � ((Database Database Design)Design)

��..������� �� !������� �� !

Page 2: Business Computer Project 4

�����������������"��� ����� � #$�%&'(�� ����������� �%")*���

����������+),-)#�.��*/0��*-��

K.Warawut Database Design 2

���������+),-)#�.��*/0��*-������- ���!�1���������� �

���� �+),2-��)3���%&'������%�)*�

�"����4��������� �(�� 5 %��-��*�!2-�"�-�� ��-%�6�

����%&�),*�&� 7�*0�! 2-�2��*�� *��

Page 3: Business Computer Project 4

����� ���������� ������� ���������� ��� %�+�()8 (Entity)

���(+�����(< (Attribute)

�3���"!�@!A< (Relationships)

K.Warawut Database Design 3

�3���"!�@!A< (Relationships)

Page 4: Business Computer Project 4

���������������� (Entity)(Entity)� %&'3$�+),��� 4G ��33� "4�+), ���"�, � (�� 5

�(!��*�� 4��(�� ���"��� ��������� �%�),*��!����"!, HI8�"�3�� %�+�()8+),%�),*��� �!��������"!, HI8�"�3��

K.Warawut Database Design 4

"!, HI8�"�3�� %�+�()8+),%�),*��� �!��������"!, HI8�"�3��&�����2&-��* %�+�()8� �3�� /�"!, HI8�"�3�� �!�"�3��

�! �"�(Customers)

*���+�,-��(Orders)

����"�(Products)

Page 5: Business Computer Project 4

��������2���������2� (Attribute)(Attribute)� %&'��� �+),�"- �!�1J�� %�+�()8

� %�+�()8� �3�� #��).I,� +),�* � ����0!"2&�1J)*<�! �"� (Customers)

�0!"� �3��%�+�()8

K.Warawut Database Design 5

�! �"� (Customers)�0!"� �3��.I,�� �3��+),�* �� �3��

�0!"2&�1J)*<0��*%�K+�L!@+<

��(+�����(< (+!8 0�-)

Page 6: Business Computer Project 4

��������2���������2� (Attribute)(Attribute)� %�+�()8/�"!, HI8�"�3�� #��)�0!"/�"!, HI8� �!+),"!, "�3�� �!+),"� "�3�� �0!"� �3��+),"!, �0!""�3��+),"!,

*���+�,-�� (Orders)

�0!"/�"!, HI8��!+),"!, "�3���!+),"� "�3��

K.Warawut Database Design 6

� �3��+),"!, �0!""�3��+),"!, #$��"�3��+),"!, ���"���-

�!+),"� "�3���0!"� �3��+),"!, �0!""�3��+),"!,

#$��"�3��+),"!, "���-

Page 7: Business Computer Project 4

��������2���������2� (Attribute)(Attribute)� %�+�()8"�3�� #��)�0!""�3�� .I,�"�3�� ��3�(��0��* ���7�1)-��*0�I�2�� #$��"�3��/

����"� (Products)

�0!""�3��.I,�"�3��

��3�(��0��*

K.Warawut Database Design 7

0�I�2�� #$��"�3��/3�! �0!".�-"�3�� .I,�.�-"�3��

��3�(��0��*���7�1)-��*0�I�2��#$��"�3��/3�!

�0!".�-"�3��.I,�.�-"�3��

Page 8: Business Computer Project 4

�2�6��67��8�2�6��67��8 (Relationships)(Relationships)�0��*4G 3���"!�@!A<��0��� %�+�()8(�� 5

�! �"� (Customers)�0!"� �3��.I,�� �3��+),�* �� �3��

*���+�,-�� (Orders)

�0!"/�"!, HI8��!+),"!, "�3���!+),"� "�3��

1 N

K.Warawut Database Design 8

%�+�()8(�� 5 /����

+),�* �� �3���0!"2&�1J)*<

0��*%�K+�L!@+<

�!+),"� "�3���0!"� �3��+),"!, �0!""�3��+),"!,

#$��"�3��+),"!, "���-

Page 9: Business Computer Project 4

>��?"�6!��@����67��8>��?"�6!��@����67��8 (Relational Database)(Relational Database)

�3I� ���������%�+�()8+),�* �/����+),�)3���"!�@!A<

�! �"� (Customers)�0!"� �3��.I,�� �3��+),�* �� �3��

*���+�,-�� (Orders)

�0!"/�"!, HI8��!+),"!, "�3��

1 N

K.Warawut Database Design 9

3���"!�@!A<��0��� �!%��2��-��*�!

.I,�� �3��+),�* �� �3��

�0!"2&�1J)*<0��*%�K+�L!@+<

�!+),"!, "�3���!+),"� "�3���0!"� �3��+),"!, �0!""�3��+),"!,

#$��"�3��+),"!, "���-

Page 10: Business Computer Project 4

���B�7���+*@"*�>��?"�6!����B�7���+*@"*�>��?"�6!��CD�E (Field) / ��������2� (Attribute)0��*��� �+),&�������#���!���(�� 5 0��*�!���

� �����E (Record) / �!�7�� (Tuple)

K.Warawut Database Design 10

�����E (Record) / �!�7�� (Tuple)#�%&'���$�OP�-<0��*5 OP�-<������!

������ (Table) / ����@�� (Relation)#�%&'���$�%�3��<-0��*5 %�3��<-������!

Page 11: Business Computer Project 4

���B�7���+*@"*�>��?"�6!����B�7���+*@"*�>��?"�6!�

003 P&S STORE . !8 1 ��3��%"�����(�+��%���< 159 "� ����+ 21 (�KL�) 3�� %(* �+�.

015 Happy Shop "��3�\�!, %L" 29 4."�A�/( � + �� �0�%�] *���� ��� %+@^

�I���! � "� @- J��! � "� � �+�K!J�! � "�

FieldField

K.Warawut Database Design 11

015 Happy Shop "��3�\�!, %L" 29 4."�A�/( � + �� �0�%�] *���� ��� %+@^

181 The Book Shop Asia Hotel 296 4.@c�2+ ��� %+@^

213 P'S Line 2675 0� � 9 ��-@���� H.95 �! +� 0�� �� ��& � �+�.

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

RecordRecord

Page 12: Business Computer Project 4

@��E?���2�6��67��8@��E?���2�6��67��8 (Relationships)(Relationships)�3���"!�@!A<0G, (��0G, (One-to-One Relationships)

�3���"!�@!A<���0G, (������� (One-to-Many Relationships)

K.Warawut Database Design 12

Relationships)

�3���"!�@!A<��������(������� (Many-to-Many Relationships)

Page 13: Business Computer Project 4

OneOne--toto--One RelationshipsOne Relationships ((11::11))� %&'3���"!�@!A<+),+$�3���%��/# ��*+),"�- %I,� #��%�3��<- 1 %�3��<-/(��� 0G, #��)3���"!�@!A<�!��)�%�3��<- 1 %�3��<-/�)�(��� 0G, %+��!8 2��"����4�)%�� 1 2-� %.� (��� ��3�"�3�� ���(���

K.Warawut Database Design 13

"����4�)%�� 1 2-� %.� (��� ��3�"�3�� ���(��� #$��"�3��#��)3���"!�@!A<��� 1:1

Page 14: Business Computer Project 4

1:1�I������ "�

0005

0100

0125

0051

0010

0007

0008

0023

0055

VIDEO CONFERENCING CARD-FLY VIDEO OF SOUND

. �-���� 4 ��*�)- )K� Panasonic Model VHS ��� NV-VX3

JAPANESE 1, LIYC

JAPANESE 2, LIYC

US ATLAS

SECRETS OF STARGATE

SALES MANAGER FACTOMATIC

@ -�*�� �)��+�

3 �� I�"$�0�!�y �z/*�3 90

@- J����� "� ����� J�I� J2K (���)

219.63

219.63

700.93

1,842.99

540.19

369.16

260.00

278.00

278.00

O���

Yes

Yes

No

No

No

Yes

Yes

No

No

�������K�����KE���� "�

1

K.Warawut Database Design 14

�I������ "�

0005

0100

0125

0051

0010

0007

0008

0023

0055

VIDEO CONFERENCING CARD-FLY VIDEO OF SOUND

. �-���� 4 ��*�)- )K� Panasonic Model VHS ��� NV-VX3

JAPANESE 1, LIYC

JAPANESE 2, LIYC

US ATLAS

SECRETS OF STARGATE

SALES MANAGER FACTOMATIC

@ -�*�� �)��+�

3 �� I�"$�0�!�y �z/*�3 90

@- J����� "� PQ�2�*�����

400

155

169

197

159

205

177

326

159

�����PQ�2����� "�*�����1

1

Page 15: Business Computer Project 4

OneOne--toto--One RelationshipsOne Relationships ((11::11))�3���"!�@!A<��� 1:1 %��"����4���%&'(��� %-)*�2-�

�/�� 3�!8 %��(�� "��� 3���"!�@!A<���)8 %I,� #��

K.Warawut Database Design 15

/�� 3�!8 %��(�� "��� 3���"!�@!A<���)8 %I,� #��%0(�y��� &����� %.� (�� ���(��� +),/.�%&'�0�� ��� �/���"��� ��* �K-*%{@�� %@I,�%@�,�3���%�6�/���+$� � %&'(�

Page 16: Business Computer Project 4

OneOne--ToTo--Many Relationships (Many Relationships (11:N):N)� %&'3���"!�@!A<���+),@����*+),"�-/��������� �+!,�2&

�3���"!�@!A<���)8%&'3���"!�@!A<+),%�3��<- 1

K.Warawut Database Design 16

3���"!�@!A<���)8%&'3���"!�@!A<+),%�3��<- 1 %�3��<-/(��� 0G, #�"!�@!A<�!�#$��%�3��<- 2 %�3��<- 0�I��������/�)�(��� 0G, %.� � �3��"����4"!, "�3��2-�0��*/�"!, HI8�"�3�� �(�/�"!, "�3��/�0G, #��)� �3��"!, %@)* ��*%-)*�%+��!8

Page 17: Business Computer Project 4

1:N003 P&S STORE . !8 1 ��3��%"�����(�+��%���< 159 "� ����+ 21 (�KL�) 3�� %(* �+�.

015 Happy Shop "��3�\�!, %L" 29 4."�A�/( � + �� �0�%�] *���� ��� %+@^

181 The Book Shop Asia Hotel 296 4.@c�2+ ��� %+@^

213 P'S Line 2675 0� � 9 ��-@���� H.95 �! +� 0�� �� ��& � �+�.

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

�I���! � "� @- J��! � "� � �+�K!J�! � "�

������! � "�

�I�� ����+�,- "�

�����*���+�,- "�

�I���! � "� 2��� �+��+����� "� 2��� �+�J����� "� �I������ "� PQ�2����� "�� �+��+�,- "� �J2��E (%)

11

NN

K.Warawut Database Design 17

�I�� ����+�,- "�

10258

10265

10278

�I���! � "�

015

181

015

2��� �+��+����� "�

21/10/xx

21/10/xx

24/10/xx

2��� �+�J����� "�

28/10/xx

27/10/xx

3/11/xx

�I������ "�

0005

0100

0125

0005

0010

0007

0008

0023

PQ�2����� "�� �+��+�,- "�

8

8

15

31

15

16

31

4

�J2��E (%)

25.00%

0.00%

0.00%

0.00%

0.00%

0.00%

25.00%

0.00%

NN

Page 18: Business Computer Project 4

ManyMany--toto--Many Relationships (M:N)Many Relationships (M:N)� %&'3���"!�@!A<���+),2��"����4�"- /0�%062-� ��* %I,� #��%��"����4"��� 3���"!�@!A<���)82-� K-*"��� (��� /0��+),3���"!�@!A<��� Many-to-One �!�(��� 2 (��� +),�)3���"!�@!A<��� Many-to-Many

K.Warawut Database Design 18

(��� 2 (��� +),�)3���"!�@!A<��� Many-to-Many Relationships

Page 19: Business Computer Project 4

ManyMany--toto--Many Relationships (M:N)Many Relationships (M:N)�(!��*�� /�"!, HI8�"�3��+),� �3��"!, HI8��� #�"����4�)"�3��+),"!, 2-������� 1 �*�� //�0G, ���/+� ��!��!"�3��!8"����4�* �//�"!, HI8�2-�0��*/�%.��! 3���"!�@!A<��0��� /�"!, "�3���!�"�3��#G

K.Warawut Database Design 19

%.��! 3���"!�@!A<��0��� /�"!, "�3���!�"�3��#G %&'��� Many-to-Many

Page 20: Business Computer Project 4

M:NM:N

�I�� ����+�,- "�

�����*���+����� "�

10258

10265

10278

�I���! � "�

015

181

015

2��� �+��+����� "�

21/10/xx

21/10/xx

24/10/xx

2��� �+�J����� "�

28/10/xx

27/10/xx

3/11/xx

�I�� ����+�,- "�

�������K ����+����� "�

10258

10258

10258

10265

10265

10278

10278

10278

�I������ "�

0005

0100

0125

0005

0010

0007

0008

0023

PQ�2����� "�� �+��+�,- "�

8

8

15

31

15

16

31

4

�J2��E (%)

25.00%

0.00%

0.00%

0.00%

0.00%

0.00%

25.00%

0.00%

��������� "�

11

NN

NN

NNNN

NN

K.Warawut Database Design 20

�I������ "�

0005

0100

0125

0051

0010

0007

0008

0023

0055

VIDEO CONFERENCING CARD-FLY VIDEO OF SOUND

. �-���� 4 ��*�)- )K� Panasonic Model VHS ��� NV-VX3

JAPANESE 1, LIYC

JAPANESE 2, LIYC

US ATLAS

SECRETS OF STARGATE

SALES MANAGER FACTOMATIC

@ -�*�� �)��+�

3 �� I�"$�0�!�y �z/*�3 90

@- J����� "� ����� J�I� J2K (���)

219.63

219.63

700.93

1,842.99

540.19

369.16

260.00

278.00

278.00

O���

Yes

Yes

No

No

No

Yes

Yes

No

No

��������� "�

PQ�2�*�����

400

155

169

197

159

205

177

326

159

11

Page 21: Business Computer Project 4

@��E?����K*�>��?"�6!��@����67��8@��E?����K*�>��?"�6!��@����67��8�Primary Key: PK (3)*<0�!�)

�Candiate Key (3)*<3 ��� )

�Composite Key

K.Warawut Database Design 21

�Composite Key

�Foreign Key: FK

Page 22: Business Computer Project 4

Primary Key: PKPrimary Key: PK�#�%&'OP�-<+),�)3��2��H8$��!%�*/�(���%�3��<-/(��� !8 %��"����4/.�OP�-<+),%&' Primary Key )8%&'(!��+� (��� !82-�+!+)

K.Warawut Database Design 22

Page 23: Business Computer Project 4

Candidate KeyCandidate Key� %&'OP�-<0G, 0�I�0��*OP�-<+),@�%��������!�����)3�J"��!(�%&' Primary Key ���2��2-�4 �%&'3)*<0�!� %.� �0!"#! 0�!-%&'3)*<0�!� "��.I,�#! 0�!-�62��H8$�%.��! �(�2��2-�%&'3)*<0�!�#G %&'3)*<3 ��� �+

K.Warawut Database Design 23

%.��! �(�2��2-�%&'3)*<0�!�#G %&'3)*<3 ��� �+

Page 24: Business Computer Project 4

Composite KeyComposite Key��� (��� 0�OP�-<+),2��H8$�2��2-�%�* #G (�� /.�0��*5 OP�-<������!%&' Primary Key OP�-<+),/.�����!)8%��%�)*���� Composite Key

K.Warawut Database Design 24

Page 25: Business Computer Project 4

Foreign KeyForeign Key� %&'OP�-</-5 /(��� 0G, (\�� Many) +),�)3���"!�@!A<�!�OP�-<+),%&' Primary Key /�)�(��� 0G, (\�� One) K-*+),(��� +!8 2 �)3���"!�@!A<���One-to-Many (���!

K.Warawut Database Design 25

One-to-Many (���!

Page 26: Business Computer Project 4

�I�� ����+�,- "�

�����*���+����� "�

10258

10265

10278

�I���! � "�

015

181

015

2��� �+��+����� "�

21/10/xx

21/10/xx

24/10/xx

2��� �+�J����� "�

28/10/xx

27/10/xx

3/11/xx

�I�� ����+�,- "�

�������K ����+����� "�

10258

10258

10258

10265

10265

10278

�I������ "�

0005

0100

0125

0005

0010

0007

PQ�2����� "�� �+��+�,- "�

8

8

15

31

15

16

�J2��E (%)

25.00%

0.00%

0.00%

0.00%

0.00%

0.00%

11NN

K.Warawut Database Design 26

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

Page 27: Business Computer Project 4

���� �������>��?"�6!� ���� �������>��?"�6!�� %&'!8(����/���"��� ��@@��%3.!,����� � (Application Database)

�#�(�� �$�0-%�+�()8� ����2&#4G (���

K.Warawut Database Design 27

#�(�� �$�0-%�+�()8� ����2&#4G (��� ����� �

Page 28: Business Computer Project 4

PWE�����*� ���� ���>��?"�6!�PWE�����*� ���� ���>��?"�6!���-3���H8$�H����� �/����� ��(��"� 3����#$�%&'/���%�)*�/.���� �/%���+),"!8

+),"�-�.��*/0�(��#"��3���4 �(�� ���+!8 #!-��(���� ��� �

K.Warawut Database Design 28

�.��*/0�(��#"��3���4 �(�� ���+!8 #!-��(���� ��� �2-�"�-�� (y�(��%I,� #�������)

�"����4�$�0-�!�1J����%��4G ��� �"$�0�!�y �/.��(���&��%7+2-�

�+$�/0��)3�����"����0��� ��� ��!���@@��%3.!,

Page 29: Business Computer Project 4

?������*� ���� ���>��?"�6!�?������*� ���� ���>��?"�6!�1. �$�0-%�+�()8+��(!�/��������� �!85

2. �$�0-3)*<0�!� �����(+�����(<(�� 5 � %�+�()8

3. �$�0-3���"!�@!A<��0��� %�+�()8(�� 5

K.Warawut Database Design 29

3. �$�0-3���"!�@!A<��0��� %�+�()8(�� 5

4. +$����%&�),*%�+�()8+),2-�2&�* �/� &(��� K-*/.������ Normalization

Page 30: Business Computer Project 4

�� �� NormalizationNormalization� %&'+�1�)+),/.�/���+$�/0�%�+�()8 �����(+�����(<+),2-�

������2�� 4 �#!-�����%&'(��� +),�)3���"!�@!A<�!� #�-&��" 3<� ��� Normalization 3I�

��-3���H8$�H��� ��� �/(��� %@I,�#�2-�2��(�� ���2��� �

K.Warawut Database Design 30

��-3���H8$�H��� ��� �/(��� %@I,�#�2-�2��(�� ���2��� �/0��*5 +),

�+$�/0����%&�),*�&� ���2K3� "��� � (��� /7�*0�! +$�/0� ��*

�+$�/0����%&�),*�&� K3� "��� ��� � �)y����+�(����@@��%3.!,+),%��4G ��� �/����� ���*+),"�-

Page 31: Business Computer Project 4

Y �� Y �� NormalizationNormalizationK��Z6JZE"[! Normalization

1st Normal Form1st Normal Form

2nd Normal Form2nd Normal Form

�"��Z6J6� �WJ6�+��,��� ��(Repeating Group)

�"��Z6J6���������2���+Z6J*@J��K��2*E?^�� ��

K.Warawut Database Design 31

2nd Normal Form

3rd Normal Form3rd Normal Form

4th Normal Form4th Normal Form

�"��Z6J6���������2���+Z6J*@J��K��2*E?^�� ���J2�*E�J2�I�^+�?����K

�"��Z6J6���������2�*E*������?^�� ����������2��-+���+Z6J*@J PK I�-� Candidate Key

�"��Z6J6� ��?^���J� ������@�� �WJ6(Multivalued Dependency)

Page 32: Business Computer Project 4

Y?"���+ Y?"���+ 1 1 (First Normal Form)(First Normal Form)�#�(�� 2���)%H��</-/(��� +),�)3��%��0G, 3��

� %��"����4+$�/0�(��� y������+), 1 2-�-��*����*�%H��<+),�)3��%��0G, ���%&'%�3��<-/0��

K.Warawut Database Design 32

%H��<+),�)3��%��0G, ���%&'%�3��<-/0��

Page 33: Business Computer Project 4

Y?"���+ Y?"���+ 2 2 (Second Normal Form)(Second Normal Form)�(��� +),y������+), 2 #�(�� 2���)��(+�����(<+),2��/.�3)*<(!�/- (%��%�)*���� Non-key Attribute) G8�!�"��/-"��0G, � 3)*<0�!� #�(�� G8�!�3)*<0�!����%(6�5 %+��!8

K.Warawut Database Design 33

%+��!8

� %��"����4+$�/0�(��� y������+), 2 K-*����*�OP�-<+),G8%{@���!�"��0G, � 3)*<0�!� �!�OP�-<+),�!G8-��* �����"��� %&'(��� /0��

Page 34: Business Computer Project 4

Y?"���+ Y?"���+ 3 3 (Third Normal Form)(Third Normal Form)�(��� +),y������+), 2 #�(�� 2���)��(+�����(</-/(��� G8�!� ��(+�����(<�I,+),2��/.�3)*<0�!� 0�I�3)*<3 ���

K.Warawut Database Design 34

� %��"����4+$�/0�(��� � %��y������+), 3 -��*����*�OP�-<+),G8�!�OP�-<�I,5 !8�����%&'(��� /0�� ���3)*<0�!�� (��� /0��#�%&'OP�-<+),OP�-<!8G8-��*

Page 35: Business Computer Project 4

Y?"���+ Y?"���+ 4 4 (Fourth Normal Form)(Fourth Normal Form)�(��� +),y������+), 4 #�(�� 2���)���G8(���!���%.� ����� (Multivalued Dependency) HG, %&'3���"!�@!A<��� Many-to-Many 7�*/(��� %-)*��! %.� /(��� %-)*��!#��)OP�-< B +),G8(��OP�-< A K-*���G8

K.Warawut Database Design 35

(��� %-)*��!#��)OP�-< B +),G8(��OP�-< A K-*���G8(���!)8%&'��"��#���)�OP�-<0G, 3I� OP�-< C %��"����4+$�/0�(��� y������+), 4 2-�K-*�*�(��� /0��HG, &�����-��*OP�-<+),%�),*��� �!%+��!8

Page 36: Business Computer Project 4

EE--R DiagramR Diagram

K.Warawut Database Design 36