Design Database : Sale System

1. Design Database : Sale System THINAPHAN NITHIYUWITH Email : thinaphan@pcru.ac.th

2. Contents 1 การออกแบบฐานข้อมูล 2 การออกแบบ Relation Database 3 ขั้นตอนการออกแบบ E-R Diagram 4 Mapping E-R Diagram to Relation 5 สรุป Q&A

3. ระบบสารสนเทศเพื่อการขายหนังสือผ่านระบบเครือข่ายระบบสารสนเทศเพื่อการขายหนังสือผ่านระบบเครือข่าย กรณีศึกษา : ร้าน OHO Bookshop

4. ขอบเขตการทำงาน • จัดการข้อมูลสินค้าและประเภทสินค้าในร้านโดยผู้ดูแลระบบ (เจ้าของร้าน) • จัดการข้อมูลบริษัทคู่ค้าของสินค้าโดยโดยผู้ดูแลระบบ (เจ้าของร้าน) • ลูกค้าแบ่งเป็น 2 ประเภท คือ ลูกค้าจร กับลูกค้าสมาชิก โดยลูกค้าสมาชิกอาจมีการให้ส่วนลด • ลูกค้าสามารถสมัครสมาชิกผ่านการกรอกแบบฟอร์มใบสมัคร และระบบออกบัตรสมาชิกให้กับลูกค้า • ลูกค้าสามารถเลือกซื้อสินค้าได้ตามต้องการ • ระบบจะทำการยืนยันรายการสั่งซื้อ และตัดสต็อกสินค้า • ลูกค้าชำระเงินตามรายการสั่งซื้อ และระบบออกใบเสร็จรับเงินให้กับลูกค้า

5. ขอบเขตการทำงาน ผู้ดูแลระบบสามารถทำการสั่งซื้อสินค้ากับบริษัทคู่ค้า โดยระบบจะออกใบสั่งซื้อไปยังบริษัทคู่ค้า ผู้ดูแลระบบสามารถทำการรับสินค้าตามรายการสั่งซื้อสินค้า ระบบสามารถแสดงรายงานข้อมูลพื้นฐาน อาทิ เช่น ข้อมูลลูกค้าที่เป็นสมาชิก, ข้อมูลประเภทสินค้า, ข้อมูลสินค้า ระบบสามารถรายงานสินค้าใกล้หมด (ถึงจุดสั่งซื้อ) ระบบสามารถแสดงยอดขายสินค้าสูงสุด 5 อันดับ ระบบสามารถแสดงรายการขายสินค้าในแต่ละวัน ระบบสามารถแสดข้อมูลสรุปยอดการขาย และผลประกอบการในแต่ละเดือน อื่น ๆ .... ลองพิจารณาเพิ่ม

6. Designing Databases is . . . • การออกแบบฐานข้อมูล (Designing Databases) มีความสำคัญต่อการจัดการระบบฐานข้อมูล (DBMS) ทั้งนี้เนื่องจากข้อมูลที่อยู่ภายในฐานข้อมูลจะต้องศึกษาถึงความสัมพันธ์ของข้อมูล โครงสร้างของข้อมูลการเข้าถึงข้อมูลและกระบวนการที่โปรแกรมประยุกต์จะเรียกใช้ฐานข้อมูล

7. ขั้นตอนการออกแบบ Relation Database • ขั้นตอนการดำเนินการ การออกแบบฐานข้อมูลเชิงสัมพันธ์ มี 6 ขั้นตอน • การรวบรวมและวิเคราะห์ความต้องการในการใช้ข้อมูล • การเลือกระบบจัดการฐานข้อมูล • การออกแบบฐานข้อมูลในระดับแนวคิด • การนำฐานข้อมูลที่ออกแบบในระดับแนวคิดเข้าสู่ระบบจัดการฐานข้อมูล • การออกแบบฐานข้อมูลในระดับกายภาพ • การนำฐานข้อมูลไปใช้และการประเมินผล

8. ขั้นตอนการออกแบบ Relation Database • การรวบรวมและวิเคราะห์ความต้องการในการใช้ข้อมูล • ขั้นตอนนี้ข้อมูลจะได้มาจากแบบฟอร์มต่างๆ เช่น ใบสั่งซื้อ, ใบเสร็จรับเงิน , หรือหน้าตารายงานที่ต้องการแสดงผล เช่น รายงานลูกค้าที่ชำระเงินแล้ว, รายงานการสั่งซื้อสินค้าประจำวัน

9. ขั้นตอนการออกแบบ Relation Database • การเลือกระบบจัดการฐานข้อมูล • ขั้นตอนนี้คุณต้องตัดสินใจว่าคุณจะเลือกใช้ DBMS ตัวไหน เช่น SQLServer, MS Access, MySQL, Oracle ฯ (ไม่ต้องคิดมาก คุณก็เอามาจากบทที่ 1 ของคุณ นอกจากลองไปศึกษาดูแล้วมีเหตุให้ต้องเปลี่ยน ก็เปลี่ยนได้) • ทำไมต้องเลือก ? เพราะว่าจะเอามากำหนดชนิดของข้อมูล ตอนทำเป็นตารางจ้า

10. ขั้นตอนการออกแบบ Relation Database • การออกแบบฐานข้อมูลในระดับแนวคิด • การออกแบบฐานข้อมูลในระดับแนวความคิด หรือในระดับตรรกะ เป็นขั้นตอนการออกแบบความสัมพันธ์ระหว่างข้อมูลในระบบโดยใช้แบบจำลองข้อมูลเชิงสัมพันธ์ ซึ่งอธิบายโดยใช้แผนภาพแสดงความสัมพันธ์ระหว่างข้อมูล (E-R Diagram) จากแผนภาพ E-R Diagram นำมาสร้างเป็นตารางข้อมูล (Mapping E-R Diagram to Relation) และใช้ทฤษฏีการ Normalization เพื่อเป็นการรับประกันว่าข้อมูลมีความซ้ำซ้อนกันน้อยที่สุด

11. ขั้นตอนการออกแบบ Relation Database • กำหนด Entity • Strong Entity • Weak Entity • กำหนด Attribute • Composite attribute • Simple attribute • Single-value attribute • Multivalued attributes • Derived attributes • กำหนด Primary Key • กำหนด Relationship • 1:1 • 1:M • M:N • ชนิดความสัมพันธ์ • Unary/Recursive • Binary • Ternary • EER

12. ขั้นตอนการออกแบบ E-R Diagram : Sale System • 1. กำหนด Entity และ Attribute พร้อมพิจารณาว่าเป็น • Entity คือ ข้อมูลที่เราสนใจจะจัดเก็บข้อมูล • ข้อมูลหาได้จากไหน ? • แบบฟอร์ม • รายงาน • ข้อมูลอื่น ๆ ที่เราไปหา requirement อาจโดยการพูดคุย สัมภาษณ์ หรือแบบสอบถาม • Attributeคือ รายละเอียดหรือคุณลักษณะของ Entity นั้น ๆ เช่นต้องการจัดเก็บข้อมูลหนังสือ (Entity)ซึ่ง รายละเอียดหนังสือ (Attribute) ประกอบด้วย รหัสหนังสือ ชื่อเรื่อง ผู้แต่ง รูปภาพ เป็นต้น

13. ขั้นตอนการออกแบบ E-R Diagram : Sale System

14. ขอบเขตการทำงาน จัดการข้อมูลสินค้าและประเภทสินค้าในร้านโดยผู้ดูแลระบบ (เจ้าของร้าน) จัดการข้อมูลบริษัทคู่ค้าของสินค้าโดยโดยผู้ดูแลระบบ (เจ้าของร้าน) ลูกค้าแบ่งเป็น 2 ประเภท คือ ลูกค้าจร กับลูกค้าสมาชิก โดยลูกค้าสมาชิกอาจมีการให้ส่วนลด ลูกค้าสามารถสมัครสมาชิกผ่านการกรอกแบบฟอร์มใบสมัคร และระบบออกบัตรสมาชิกให้กับลูกค้า ลูกค้าสามารถเลือกซื้อสินค้าได้ตามต้องการ ระบบจะทำการยืนยันรายการสั่งซื้อ และตัดสต็อกสินค้า ลูกค้าชำระเงินตามรายการสั่งซื้อ และระบบออกใบเสร็จรับเงินให้กับลูกค้า

15. ขอบเขตการทำงาน ผู้ดูแลระบบสามารถทำการสั่งซื้อสินค้ากับบริษัทคู่ค้า โดยระบบจะออกใบสั่งซื้อไปยังบริษัทคู่ค้า ผู้ดูแลระบบสามารถทำการรับสินค้าตามรายการสั่งซื้อสินค้า ระบบสามารถแสดงรายงานข้อมูลพื้นฐาน อาทิ เช่น ข้อมูลลูกค้าที่เป็นสมาชิก, ข้อมูลประเภทสินค้า, ข้อมูลสินค้า ระบบสามารถรายงานสินค้าใกล้หมด (ถึงจุดสั่งซื้อ) ระบบสามารถแสดงยอดขายสินค้าสูงสุด 5 อันดับ ระบบสามารถแสดงรายการขายสินค้าในแต่ละวัน ระบบสามารถแสดข้อมูลสรุปยอดการขาย และผลประกอบการในแต่ละเดือน อื่น ๆ .... ลองพิจารณาเพิ่ม

16. ISBN รูป ราคาทุน สำนักพิมพ์ ราคาขาย ผู้แต่ง จำนวนคงเหลือ ชื่อเรื่อง จุดสั่งซื้อ รหัสหนังสือ เรื่องย่อ Book Entity Attribute และ PK จากขอบเขตของระบบ เราสามารถค้นหา Entity และกำหนด Attribute และ PKได้ ดังนี้ • จัดการข้อมูลสินค้าและประเภทสินค้าในร้านโดยผู้ดูแลระบบ (เจ้าของร้าน) • Entity หนังสือ(สินค้า) : Book (รหัสหนังสือ, ชื่อเรื่อง, ผู้แต่ง, สำนักพิมพ์, isbn, รูปหนังสือ, ราคาทุน, ราคาขาย, จำนวนคงเหลือ, จุดสั่งซื้อ, เรื่องย่อ) • Entity หมวดหมู่(ประเภทสินค้า): Category (รหัสหมวด, ชื่อหมวด) รหัสหมวด ชื่อหมวด Category

17. เบอร์โทร ที่อยู่ แฟกซ์ ที่อยู่ ชื่อ-สกุล เบอร์โทร ชื่อบริษัท ชื่อ-สกุลผู้ติดต่อ รหัสสมาชิก อีเมล์ รหัสบริษัท อีเมล์ Member Partner Entity Attribute และ PK • จัดการข้อมูลบริษัทคู่ค้าของสินค้าโดยโดยผู้ดูแลระบบ • Entity บริษัทคู่ค้า : Partner  (รหัสบริษัท, ชื่อบริษัท, ที่อยู่, เบอร์โทร, แฟกซ์, ชื่อ-สกุลผู้ติดต่อ, อีเมล์) • ลูกค้าแบ่งเป็น 2 ประเภท คือ ลูกค้าจร กับลูกค้าสมาชิก โดยลูกค้าสมาชิกอาจมีการให้ส่วนลด โดยลูกค้าสามารถสมัครสมาชิกผ่านการกรอกแบบฟอร์มใบสมัคร และระบบออกบัตรสมาชิกให้กับลูกค้า • Entity ลูกค้า : Member  (รหัสสมาชิก, ชื่อ-สกุล, ที่อยู่, เบอร์โทร, อีเมล์ )

18. ยอดรวมสุทธิ ยอดรวมสุทธิ วันที่ วันที่ เลขที่ใบเสร็จ สถานะการรับ เลขที่รายการขาย เลขที่รายการสั่งซื้อ Sale_Orders Purchase_Orders Entity Attribute และ PK • ระบบจะทำการยืนยันรายการขายสินค้า และตัดสต็อกสินค้า • Entity รายการขาย(สินค้า) : Sale_Orders (เลขที่รายการขาย, วันที่ขาย, ยอดเงินรวมสุทธิ,เลขที่ใบเสร็จ ) • ผู้ดูแลระบบสามารถทำการสั่งซื้อสินค้ากับบริษัทคู่ค้า โดยระบบจะออกใบสั่งซื้อไปยังบริษัทคู่ค้า • Entity รายการสั่งซื้อ(สินค้า)กับบริษัทคู่ค้า : Purchase_Orders (เลขที่ใบสั่งซื้อ, วันที่สั่งซื้อ, ยอดเงินรวมสุทธิ, สถานะการรับหนังสือ)

19. Relationship • กำหนดความสัมพันธ์ระหว่าง Entity ทั้ง 6 ที่มีความเกี่ยวข้องกัน 1. Entity หนังสือ กับ Entity หมวดหมู่ มีความสัมพันธ์ในลักษณะM:1 คือ หนังสือ 1 เล่ม จัดอยู่ได้ 1 หมวดหมู่ และในทางกลับกัน 1 หมวดหมู่ มีหนังสือได้ หลายเล่ม • รหัสหนังสือ • ชื่อเรื่อง • ผู้แต่ง • สำนักพิมพ์ • ISBN • รูปหนังสือ • ราคาทุน • ราคาขาย • จำนวนคงเหลือ • จุดสั่งซื้อ • เรื่องย่อ • รหัสหมวดหนังสือ • ชื่อหมวด M 1 Book has Category รูปความสัมพันธ์ที่ 1

20. Relationship 2. Entity หนังสือ กับ Entity บริษัทคู่ค้า มีความสัมพันธ์ในลักษณะM:1 คือ หนังสือ 1 เล่ม จัดจำหน่ายโดยบริษัทคู่ค้า 1 บริษัท และในทางกลับกัน 1 บริษัท จัดจำหน่ายหนังสือได้ หลายเล่ม • รหัสหนังสือ • ชื่อเรื่อง • ผู้แต่ง • สำนักพิมพ์ • ISBN • รูปหนังสือ • ราคาทุน • ราคาขาย • จำนวนคงเหลือ • จุดสั่งซื้อ • เรื่องย่อ • รหัสบริษัทคู่ค้า • ชื่อบริษัท • ที่อยู่ • เบอร์โทร • แฟกซ์ • อีเมล์ • ชื่อ-สกุลผู้ติดต่อ M 1 Book has Partner รูปความสัมพันธ์ที่ 2

21. Relationship 3. Entity ลูกค้า(สมาชิก) กับ Entity หนังสือ มีความสัมพันธ์ในลักษณะM:N คือ ลูกค้า(สมาชิก) 1 คน ซื้อหนังสือได้หลายเล่ม และหนังสือ 1 เล่ม ก็สามารถูกลูกค้า(สมาชิก) ซื้อได้หลายคน หรือถ้ามองในมุมผู้ขาย อาจอธิบายได้ว่า ร้านขายหนังสือให้กับลูกค้า โดยหนังสือ 1 เล่มขายให้ลูกค้าได้ หลายคน และ ทางร้านสามารถขายหนังสือได้ ให้กับลูกค้าแต่ละคนได้ หลายเล่ม M N Member has Book

22. M N 1 M has Book Member has Sale_Orders Relationship • จากความสัมพันธ์ดังกล่าว ทำให้เกิดจึงเกิด Composite Entityใบรายการขาย ขึ้นดังนั้น รูปภาพความสัมพันธ์ข้างต้นจึงถูกเปลี่ยนเป็น M N Member has Book

23. M N 1 M has Book Member has Sale_Orders Relationship • จากความสัมพันธ์ ใบรายการขาย กับ หนังสือ มีลักษณะM:N คือ ใบรายการขาย 1 ใบมีหนังสือได้หลายรายการ และหนังสือ 1 เล่มก็สามารถอยู่ได้หลายใบรายการขาย จึงเกิด Composite Entityรายละเอียดใบรายการขาย • รหัสหนังสือ • ชื่อเรื่อง • ผู้แต่ง • สำนักพิมพ์ • ISBN • รูปหนังสือ • ราคาทุน • ราคาขาย • จำนวนคงเหลือ • จุดสั่งซื้อ • เรื่องย่อ • รหัสสมาชิก • ชื่อ-สกุล • ที่อยู่ • เบอร์โทร • อีเมล์ • เลขที่รายการขาย • วันที่ • ยอดเงินรวมสุทธิ • เลขที่ใบเสร็จ • จำนวนที่ขาย รูปความสัมพันธ์ที่ 3

24. Relationship 4. Entity ใบรายการสั่งซื้อหนังสือ(บริษัทคู่ค้า) กับ Entity หนังสือ มีความสัมพันธ์ในลักษณะM:N คือ ใบรายการสั่งซื้อหนังสือ 1 ใบ สามารถสั่งซื้อหนังสือจากบริษัทคู่ค้าได้ หลายรายการ และ หนังสือ 1 รายการก็สามารถอยู่ได้หลายใบรายการสั่งซื้อ ณ วันเวลาที่ต่างกัน M N Purchase_Orders has Book

25. Relationship • จากความสัมพันธ์ดังกล่าว ทำให้เกิดจึงเกิด Composite Entityรายละเอียดใบรายการสั่งซื้อ ขึ้นดังนั้น M N Purchase_Orders has Book • รหัสหนังสือ • ชื่อเรื่อง • ผู้แต่ง • สำนักพิมพ์ • ISBN • รูปหนังสือ • ราคาทุน • ราคาขาย • จำนวนคงเหลือ • จุดสั่งซื้อ • เรื่องย่อ • เลขที่ใบรายการสั่งซื้อ • วันที่ซื้อ • ยอดเงินรวมสุทธิ • สถานะการับสินค้า • จำนวนที่สั่งซื้อ รูปความสัมพันธ์ที่ 4

26. M 1 has Partner M N M 1 Purchase_Orders has Book has Category N M 1 M has Member has Sale_Orders E-R Diagram • นำรูปความสัมพันธ์ทั้ง 4 มาเชื่อมโยงกัน

27. Mapping E-R to Relation • ก่อน Map จะได้ข้อมูลดังนี้

28. Mapping E-R to Relation • หลัง Map จะได้ข้อมูลดังนี้

29. Mapping E-R to Relation • หลัง Map จะได้ข้อมูลดังนี้ (ต่อ)

30. Normalization • พิจารณาจากความสัมพันธ์ระหว่าง attribute ของ entity ทั้ง 8 พบว่า เป็น 3NF แล้วเนื่องจาก เป็น 2 NF แล้ว และไม่มีความสัมพันธ์แบบ Transitive เกิดขึ้น

31. Data Dictionary การทำ Data Dictionary คือ การกำหนดรายละเอียดให้กับ Relation ดังนี้ Category (หมวดหมู่หนังสือ) 31

32. Data Dictionary Partner (บริษัทคู่ค้า) 32

33. Data Dictionary book (หนังสือ) 33

34. Data Dictionary member (สมาชิก) 34

35. Data Dictionary Sale_Orders (รายการขายหนังสือ) 35

36. Data Dictionary Orders_Detail (รายละเอียดใบรายการซื้อสินค้า) 36

37. Data Dictionary Purchase_Orders (รายการสั่งซื้อหนังสือกับบริษัทคู่ค้า) 37

38. Data Dictionary Purchase_Detail (รายละเอียดรายการสั่งซื้อหนังสือ) 38

39. db_bookshop : SQLServer create database db_bookshop on primary(name = db_bookshop_Data, filename = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\db_bookshop.mdf', size = 1 mb, maxsize = 20 mb, Filegrowth = 10%) log on (name=db_bookshop_log, Filename = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\db_bookshop.ldf', size = 1 mb, maxsize = 10 mb, filegrowth = 10%)

40. Table Category : SQLServer create table Category (C_ID tinyint(1)primary key, C_Na varchar(50)) 40

41. Table Partner : SQLServer 41

42. Table Book : SQLServer 42

43. Table Member : SQLServer 43

44. Table Sale_Orders : SQLServer 44

45. Table Orders_Detail : SQLServer 45

46. Table Purchase_Orders : SQLServer 46

47. Table Purchase_Detail : SQLServer 47

48. Diagram db_bookshop

49. Q&A