แผนภาพ E-R และการทำให้เป็นรูปทรงมาตรฐาน (normalisation)
แนะนำใหม่| English | ความหมาย |
|---|---|
| entity-relationship diagram/ˈentɪti rɪˈleɪʃənʃɪp ˈdaɪəɡræm/ | แผนภาพความสัมพันธ์ของเอนทิที |
| normalisation/ˌnɔːməlaɪˈzeɪʃn/ | การทำให้เป็นมาตรฐาน |
| entity/ˈentɪti/ | เอนทิตี |
| cardinality/ˌkɑːdɪˈnælɪti/ | кардинаล |
| one-to-many/wʌn tə ˈmeni/ | หนึ่งต่อหลาย |
| many-to-many/ˈmeni tə ˈmeni/ | หลายต่อหลาย |
| link table/lɪŋk ˈteɪbl/ | ตารางเชื่อมโยง |
| normal forms/ˈnɔːml fɔːmz/ | รูปแบบมาตรฐาน |
| atomic/əˈtɒmɪk/ | อะตอมมิก |
| transitive dependency/ˈtrænsɪtɪv dɪˈpendənsi/ | การขึ้นต่อกันแบบส่งต่อ |
สี่สิบแถวเพื่อเปลี่ยนเบอร์โทรศัพท์เพียงหนึ่ง
- โรงเรียนเก็บข้อมูลการลงทะเบียนไว้ในสเปรดชีตหนึ่ง Every row represents one student on one course, and every row also carries the student's form tutor and the tutor's phone number.
- ครูที่ปรึกษาเปลี่ยนเบอร์โทร自己的号码需要编辑四十行。三十九行已更新。对于剩余的一年，一个课程列表将联系到一个陌生人。
- ไม่มีคำไหนพิมพ์ผิด ความผิดพลาดอยู่ที่ การออกแบบ: ข้อเท็จจริงที่เป็นของคุณครูที่ปรึกษากำหนดไว้ครั้งต่อการลงทะเบียนหนึ่งครั้ง ทำให้มันอาจเป็นจริงในที่หนึ่งและเป็นเท็จในที่อื่น
- บทเรียนนี้จะแนะนำเครื่องมือออกแบบสองตัวที่ช่วยป้องกันปัญหาดังกล่าว: แผนภาพความสัมพันธ์ของเอนทิที (entity-relationship diagram) ซึ่งแสดงโครงสร้าง และ การทำให้เป็นมาตรฐาน (normalisation) ซึ่งกำจัดความซ้ำซ้อน
แผนภาพความสัมพันธ์ของเอนทิที
- แผนภาพความสัมพันธ์ของเอนทิที (E-R diagram) ใช้บันทึกการออกแบบฐานข้อมูล: แต่ละ เอนทิที (entity) จะถูกแทนด้วยสี่เหลี่ยมผืนผ้า แต่ละความสัมพันธ์จะถูกแทนด้วยเส้นเชื่อมระหว่างสี่เหลี่ยมผืนผ้าสองอัน และ คาร์ดิinality (cardinality) จะถูกระบุไว้ที่ปลายแต่ละด้านของเส้น
- ในสัญลักษณ์ Crow's-foot เส้นเดียวหมายถึง "หนึ่ง" และเท้าสามแฉกหมายถึง "หลาย" อ่านแต่ละเส้นได้ทั้งสองทิศทาง: ลูกค้าแต่ละคนสามารถสั่งซื้อได้หลายรายการ; และคำสั่งซื้อแต่ละรายการถูกจัดโดยลูกค้าเพียงคนเดียว
- แผนภาพนี้ถูกวาดขึ้นก่อนการสร้างตารางใดๆ และความสัมพันธ์บนแผนภาพจะกลายเป็น foreign keys
สี่เหลี่ยมผืนผ้าหนึ่งอันต่อเอนทิทีหนึ่ง, เส้นหนึ่งเส้นต่อความสัมพันธ์หนึ่ง
เส้นสำหรับหนึ่ง, เท้าสำหรับหลาย
ในแผนภาพ E-R จำนวนของเอนทิตีหนึ่งที่สามารถเกี่ยวข้องกับอีกเอนทิตีหนึ่ง ซึ่งระบุไว้ที่ปลายทั้งสองข้างของเส้น คือ ____
Difficulty 1/5 · 100 coins + a small bonus on first completion today
Cardinality เป็นหนึ่งหรือหลายที่ปลายแต่ละด้าน; สัญลักษณ์ crow's-foot ใช้เส้นตรงสำหรับหนึ่งและเท้าสำหรับหลาย
ความสัมพันธ์สามประเภท
- หนึ่งต่อหนึ่ง (1:1): สมาชิกแต่ละคนมีบัตรสมาชิกห้องสมุดหนึ่งใบ และบัตรหนึ่งใบเป็นของสมาชิกหนึ่งคน-only. น้อยมาก; เอนทิทีทั้งสองมักจะถูกผสานเข้าด้วยกันในตารางเดียว
- หนึ่งต่อ-many (1:M): ลูกค้าหนึ่งคนสามารถสั่งซื้อได้หลายรายการ; และคำสั่งซื้อแต่ละรายการเป็นของลูกค้าหนึ่งคน-only. สามารถนำไปใช้จริงได้โดยการนำ primary key ของฝั่ง "หนึ่ง" ไปใส่ในตารางของฝั่ง "many" เป็น foreign key
- Many-to-many (M:N): นักเรียนหนึ่งคนสามารถลงทะเบียนเรียนหลายรายวิชา และรายวิชานั้นมีนักเรียนหลายคน. ไม่สามารถนำไปใช้จริงได้โดยตรง; จำเป็นต้องใช้ link table
ลูกค้าแต่ละคนสามารถวางคำสั่งซื้อได้หลายรายการ แต่คำสั่งซื้อแต่ละรายการเป็นของลูกค้าคนละหนึ่งคน ความสัมพันธ์นี้คือ:
Difficulty 1/5 · 100 coins + a small bonus on first completion today
ลูกค้าหนึ่ง → คำสั่งซื้อหลาย, คำสั่งซื้อแต่ละ → ลูกค้าหนึ่ง: เป็นความสัมพันธ์หนึ่งต่อหลาย
จับคู่ความสัมพันธ์แต่ละชนิดกับ cardinality ของมัน
Difficulty 2/5 · 140 coins + a small bonus on first completion today
1:1 ทั้งสองด้านมีหนึ่ง; 1:M ด้านหนึ่งมีหลาย; M:N ทั้งสองด้านมีหลาย (ต้องใช้ตารางเชื่อมโยง)
ตัวอย่างฝึกทำ: วาดแผนภาพ E-R
- โรงเรียนมีครูผู้สอน, ชั้นเรียนและนักเรียน ครูแต่ละคนสอนหลายชั้นเรียน; แต่ละชั้นเรียนถูกสอนโดยครูหนึ่งคน. แต่ละชั้นเรียนมีนักเรียนหลายคน; และนักเรียนแต่ละคนอยู่ในชั้นเรียนหนึ่งเท่านั้น. นักเรียนอาจเข้าร่วมหลายชมรม และแต่ละชมรมมีนักเรียนหลายคน.
- สี่เหลี่ยมผืนผ้าสี่อัน:
TEACHER,CLASS,STUDENT,CLUB.TEACHER—CLASSเป็น one-to-many, เท้าอยู่ที่CLASS.CLASS—STUDENTเป็น one-to-many, เท้าอยู่ที่STUDENT.STUDENT—CLUBเป็น many-to-many, มีเท้าอยู่ทั้งสองปลาย - คะแนน: ต้องมีเอนทิทีทั้งหมด, มีความสัมพันธ์ทั้งหมด, และมีสัญลักษณ์คาร์ดิinality ที่ถูกต้องที่ ทุก ปลายเส้น. เส้นที่ไม่มีสัญลักษณ์จะได้คะแนนครึ่งหนึ่ง
Link tables
- ความสัมพันธ์ many-to-many จะถูกแยกออกเป็น one-to-many สองความสัมพันธ์ ผ่าน link table ที่เก็บ foreign key ทั้งสองค่า
ENROLMENT(StudentID, CourseID, EnrolmentDate): นักเรียนหนึ่งคนมีการสมัครเรียนหลายครั้ง, รายวิชามีการสมัครเรียนหลายครั้ง, และแต่ละแถวคือหนึ่งนักเรียนต่อหนึ่งรายวิชา. Primary key คือ composite ของ foreign key ทั้งสอง- ข้อมูลเกี่ยวกับการจับคู่ itself, วันที่, หรือเกรด จะอยู่ใน link table; ส่วนข้อมูลเกี่ยวกับนักเรียนหรือรายวิชาจะยังคงอยู่ในตารางของแต่ละฝ่าย
One many-to-many กลายเป็น two one-to-many
ความสัมพันธ์หลายต่อหลายถูกนำไปใช้ในฐานข้อมูลแบบความสัมพันธ์อย่างไร?
Difficulty 1/5 · 100 coins + a small bonus on first completion today
ตารางเชื่อมโยง (junction table) เก็บคีย์ภายนอกไปยังทั้งสองฝั่ง เปลี่ยน M:N ให้เป็นความสัมพันธ์ 1:M สองทาง
ENROLMENT(StudentID, CourseID, EnrolmentDate) เป็นตารางเชื่อมโยง Statements are true? Select ทั้งหมด that apply.
Difficulty 2/5 · 140 coins + a small bonus on first completion today
ตารางเชื่อมโยงเก็บคู่ของข้อมูลและข้อเท็จจริงเกี่ยวกับคู่นั้น ข้อมูลส่วนตัวของนักศึกษาอยู่ใน STUDENT มิฉะนั้นจะซ้ำซ้อนในทุกการลงทะเบียน
การทำให้เป็นมาตรฐาน (Normalisation)
- การทำให้เป็นมาตรฐาน จัดระเบียบตารางเพื่อให้ข้อเท็จจริงแต่ละข้อถูกจัดเก็บเพียงครั้งเดียว ลดความซ้ำซ้อนและความขัดแย้ง.它会ผ่าน normal forms按照顺序: แรก,ที่สอง,ที่สาม.
- ขั้นตอน: หาเอนทิทีและ属性的; เลือก primary key สำหรับแต่ละอย่าง; กำจัดกลุ่มซ้ำซ้อนและค่าที่ไม่ใช่ atomic (1NF); กำจัด属性ที่ขึ้นอยู่กับ composite key เพียงบางส่วน (2NF); กำจัด属性ที่ขึ้นอยู่กับ non-key attribute อื่น (3NF); เพิ่ม foreign keys สำหรับความสัมพันธ์
- ค่าใช้จ่ายคือจำนวนตารางมากขึ้นและการ join มากขึ้น. การสอบจะถามถึง 3NF.
ห้องปฏิบัติการบริการฐานข้อมูล
ดูวิธีที่ DBMS แปลงคำสั่ง query ให้เป็นการเข้าถึงข้อมูลที่ปลอดภัยและแชร์ร่วมกันได้
เรียงลำดับรูปทรงมาตรฐานตามที่คุณนำไปใช้
Difficulty 3/5 · 200 coins + a small bonus on first completion today
คุณไปถึง 3NF ผ่าน 1NF แล้ว 2NF — แต่ละขั้นสร้างจากขั้นก่อนหน้า
เป้าหมายหลักของการทำให้เป็นรูปทรงมาตรฐานคือ:
Difficulty 1/5 · 100 coins + a small bonus on first completion today
การทำให้เป็น 3NF จัดเก็บข้อเท็จจริงครั้งหนึ่งเดียว กำจัด anomalous update/insert/delete (แต่代价คือมี join มากขึ้น)
First normal form
- ตารางจะอยู่ใน 1NF เมื่อทุก field มีค่าที่เป็น atomic ตัวเดียว, ไม่มีกลุ่มซ้ำซ้อน, และมี primary key
STUDENT(StudentID, Name, Phone)กับPhoneที่ถือ0123, 0456ไม่เป็นอะตอมSTUDENT(StudentID, Name, Course1, Course2, Course3)มีกลุ่มซ้ำ- แก้ไขทั้งสองอย่างด้วยการย้ายข้อมูลซ้ำไปยังตารางของตัวเองพร้อมแถวต่อค่า:
STUDENT_PHONE(StudentID, Phone),ENROLMENT(StudentID, CourseID)
ฟิลด์ Phone เก็บ "0123, 0456" สำหรับนักศึกษาหนึ่งคน ตารางนี้ล้มเหลวในรูปทรงมาตรฐานใด?
Difficulty 1/5 · 100 coins + a small bonus on first completion today
สองค่าในเซลล์เดียวคือการล้มเหลวของ 1NF ย้ายตัวเลขไปที่ STUDENT_PHONE(StudentID, Phone) ตัวละบรรทัด
ตัวอย่างฝึกทำ: Second normal form
ORDER(OrderID, CustomerID, CustomerName, ProductID, Quantity)มี composite primary key เป็น(OrderID, ProductID). อยู่ใน 2NF หรือไม่?- ทดสอบแต่ละ non-key field กับ key ทั้งหมด.
Quantityขึ้นอยู่กับทั้งOrderIDและProductID: Which order, which product. ดีแล้ว CustomerIDและCustomerNameขึ้นอยู่กับOrderIDเพียงอย่างเดียว ซึ่งเป็น ส่วน ของ key เท่านั้น: partial dependency, ดังนั้นตารางจึง ไม่ได้อยู่ใน 2NF. แยกออก:ORDER(OrderID, CustomerID, CustomerName)และORDER_LINE(OrderID, ProductID, Quantity).
ตัวอย่างฝึกทำ: Third normal form
ORDER(OrderID, CustomerID, CustomerName)อยู่ใน 3NF หรือไม่?- ตารางจะอยู่ใน 3NF เมื่ออยู่ใน 2NF และทุก non-key field ขึ้นอยู่กับ primary key เท่านั้น ไม่ใช่กับ non-key attribute อื่น.
CustomerNameขึ้นอยู่กับCustomerIDซึ่งไม่ใช่ key: transitive dependency, ดังนั้นตารางจึงไม่ได้อยู่ใน 3NF - แยกอีกครั้ง:
ORDER(OrderID, CustomerID)และCUSTOMER(CustomerID, CustomerName), โดยCustomerIDเป็น foreign key. ชื่อจะถูกจัดเก็บเพียงครั้งเดียว ไม่ว่าลูกค้าจะสั่งซื้อกี่ครั้งก็ตาม
แต่ละ form กำจัด dependency แบบหนึ่ง
การทำให้เป็น 3NF จัดเก็บข้อเท็จจริงครั้งหนึ่งเดียวและกำจัด anomalous update แต่代价คือมีตารางและ join เพิ่มขึ้น
Difficulty 1/5 · 100 coins + a small bonus on first completion today
การแลกเปลี่ยนนี้ — ข้อมูลที่สะอาดขึ้นเทียบกับจำนวนการต่อตารางที่มากขึ้น — เป็นเหตุผลที่ทำให้ 3NF เป้าหมายทั่วไป
การอธิบายว่าทำไมตารางถึงอยู่ในหรือไม่อยู่ใน 3NF
- ไม่อยู่ใน 3NF: ระบุชื่อ dependency. "
TEACHERไม่ได้อยู่ใน 3NF เพราะ non-key attributeDepartmentNameขึ้นอยู่กับ non-key attributeDepartmentIDไม่ใช่ primary key" - อยู่ใน 3NF: ครอบคลุมเงื่อนไขทั้งสาม. "ทุก attribute เป็น atomic โดยไม่มีกลุ่มซ้ำซ้อน; ไม่มี partial dependency ต่อส่วนหนึ่งของ key; ทุก non-key attribute ขึ้นอยู่กับ primary key เพียงอย่างเดียว โดยไม่มี transitive dependency"
- จากนั้น หากถูกถาม ให้ให้ตารางที่ผ่านการทำให้เป็นมาตรฐานแล้วในรูปแบบมาตรฐาน พร้อมระบุ foreign keys
ใน TEACHER(TeacherID, Name, DepartmentID, DepartmentName) ทำไมตารางนี้จึงยังไม่เป็น 3NF?
Difficulty 1/5 · 100 coins + a small bonus on first completion today
เป็นการพึ่งพาส่งต่อ แยก DEPARTMENT(DepartmentID, DepartmentName) ออกมา และเก็บ DepartmentID ไว้ใน TEACHER ในฐานะฟอเรนคีย์
คะแนนที่หลุดหายไป
- 2NF เป็นปัญหาเฉพาะเมื่อคีย์หลักเป็น คีย์ผสม คีย์ที่มีช่องเดียวไม่สามารถเกิด partial dependency ได้
- 3NF ต้องอาศัย 2NF ก่อนเสมอ ให้ระบุทั้งสองอย่างเมื่อให้เหตุผลประกอบ
- Atomic หมายความว่าแต่ละเซลล์มีเพียงค่าเดียว "การใส่เบอร์โทรศัพท์สอง号码ในช่องเดียว" คือความล้มเหลวของ 1NF ไม่ใช่ 3NF
- การตั้งชื่อ normal form ไม่ได้เป็นคำตอบที่ถูกต้อง แต่การระบุ dependency ต่างหากคือคำตอบ ที่มีการสร้างตารางเพิ่มขึ้นหลังทำ normalization เป็นจุดประสงค์ของการออกแบบ ไม่ใช่ข้อบกพร่อง
คุณเข้าใจแล้ว
- E-R diagram แสดง entities ด้วยสี่เหลี่ยมผืนผ้า และแสดง relationships ด้วยเส้นที่มี cardinality อยู่ที่ปลายทั้งสอง: 1:1, 1:M, M:N
- many-to-many relationship จะถูกจัดเก็บผ่าน link table ที่มี foreign keys ทั้งสองตัว และ primary key ของมันจะเป็นคีย์ผสมของทั้งสองนั้น
- 1NF มีค่าแบบ atomic ไม่มีกลุ่มซ้ำ ซ้ำๆ และมี primary key · 2NF ไม่มี partial dependency ต่อส่วนหนึ่งของคีย์ผสม · 3NF ไม่มี transitive dependency ระหว่าง non-key attributes
- ให้เหตุผลโดยระบุชื่อ dependency แล้วแสดงตารางที่ถูกแยกออกพร้อม foreign keys ของแต่ละตาราง