Converting ER Diagram to Relational Schema

วิดีโอที่อาจารย์ให้ดูก่อนเรียนบทนี้:

12.1ตารางกฎการแปลง — ท่องให้ได้ทั้งตาราง

นี่คือสไลด์สรุปของอาจารย์ทั้งแผ่น ถ้าจำได้ครบ ทำข้อสอบส่วน Database Design ได้เกือบหมด

12.2Entity และ Attribute

การแปลง: ทุก entity type กลายเป็น relation (ตาราง) และ attribute ของมันกลายเป็นคอลัมน์ในตารางนั้น · primary key ที่ปกติขีดเส้นใต้ใน ER diagram กลายเป็น primary key ใน relational schema

-- ER: Student(ID, Name, DOB, Major)
Student(ID, Name, DOB, Major)

CREATE TABLE Student (
    ID    INT PRIMARY KEY,
    Name  VARCHAR(100),
    DOB   DATE,
    Major VARCHAR(50)
);

Attribute แบ่งเป็นสองชนิด:

• Simple attribute — แมปตรง ๆ เป็นคอลัมน์
• Composite attribute — ต้องแตกออกเป็นคอลัมน์แยกสำหรับแต่ละส่วนประกอบ

-- Address เป็น composite attribute: Street, City, State, ZipCode
CREATE TABLE Student (
    ID     INT PRIMARY KEY,
    Name   VARCHAR(100),
    Street VARCHAR(100),
    City   VARCHAR(50),
    State  VARCHAR(50),
    ZipCode VARCHAR(10)
);

12.3Binary Relationships ทั้ง 4 แบบ

a) One-to-One (1:1)

การแปลง: เพิ่ม foreign key ในตารางใดก็ได้ โดยอ้างถึง primary key ของอีกตาราง

-- ER: Student(ID, Name) --- has_profile (1:1) --- Profile(ProfileID, Bio)
--     Student, Profile = entity (สี่เหลี่ยม) · has_profile = relationship (ข้าวหลามตัด)
Student(ID, Name, ProfileID)
Profile(ProfileID, Bio)

-- ต้องสร้างตารางที่ถูกอ้างถึงก่อนเสมอ
CREATE TABLE Profile (
    ProfileID INT PRIMARY KEY,
    Bio       TEXT
);

-- เพิ่ม UNIQUE เพื่อรับประกันว่าไม่มี entity สองตัวชี้ไปที่เดียวกัน
CREATE TABLE Student (
    ID        INT PRIMARY KEY,
    Name      VARCHAR(100),
    ProfileID INT UNIQUE,
    FOREIGN KEY (ProfileID) REFERENCES Profile(ProfileID)
);

Unique constraint: สำหรับความสัมพันธ์ 1:1 ต้อง เพิ่ม UNIQUE constraint บน foreign key เพื่อรับประกันว่าไม่มี entity สองตัวอ้างถึงคู่ของมันตัวเดียวกัน · ตัวอย่างอื่น: Person(PersonID, Name, PassportNumber UNIQUE) — ไม่มีคนสองคนที่มีเลขพาสปอร์ตเดียวกัน

b) One-to-Many (1:N)

การแปลง: เพิ่ม foreign key ในตารางฝั่ง "many" โดยอ้างถึงฝั่ง "one"

-- ER: Department(DeptID, DeptName) --- (1:N) --- Student(StudentID, Name)
-- อ่านว่า: หนึ่งภาควิชามีนักศึกษาได้หลายคน · นักศึกษาหนึ่งคนสังกัดได้ภาคเดียว
Department(DeptID, DeptName)
Student(StudentID, Name, DeptID)   -- FK อยู่ฝั่ง many

c) Many-to-Many (M:N)

การแปลง: สร้าง relation ใหม่ที่มี foreign key ไปยัง entity ทั้งสอง

-- ER: Student(ID, Name) --- (M:N) --- Course(CourseID, CourseName)
Student(ID, Name)
Course(CourseID, CourseName)
Enrollment(StudentID, CourseID)   -- ตารางใหม่แทนความสัมพันธ์

CREATE TABLE Enrollment (
    StudentID INT,
    CourseID  INT,
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Student(ID),
    FOREIGN KEY (CourseID)  REFERENCES Course(CourseID)
);

d) Many-to-One (N:1)

ในความสัมพันธ์ many-to-one entity หลายตัวในเซตหนึ่ง สัมพันธ์กับ entity ไม่เกินหนึ่งตัวในอีกเซตหนึ่ง · กฎการแปลงเหมือน 1:N คือใส่ FK ที่ฝั่ง "many"

-- ER: Employee(EmployeeID, Name) --- (N:1) --- Department(DepartmentID, DeptName)
Employee(EmployeeID, Name, DepartmentID)
Department(DepartmentID, DeptName)

ทำให้พนักงานหลายคนสังกัดแผนกเดียวกันได้

12.4Ternary Relationship

การแปลง: สร้าง relation ใหม่ที่มี foreign key ไปยัง entity ที่เกี่ยวข้องทั้งหมด พร้อม attribute ของ relationship · PK ของตารางใหม่ = FK ทั้งสามรวมกัน

-- ER: Supplier / Part / Project ทั้งสามเชื่อมเข้าข้าวหลามตัดเดียวชื่อ Supplies
-- (ternary — ไม่ใช่ binary สามอันมาต่อกัน) · attribute ของ relationship = Quantity
Supplier(SupplierID, SupplierName)
Part(PartID, PartName)
Project(ProjectID, ProjectName)
Supplies(SupplierID, PartID, ProjectID, Quantity)

CREATE TABLE Supplies (
    SupplierID INT,
    PartID     INT,
    ProjectID  INT,
    Quantity   INT,
    PRIMARY KEY (SupplierID, PartID, ProjectID),
    FOREIGN KEY (SupplierID) REFERENCES Supplier(SupplierID),
    FOREIGN KEY (PartID)     REFERENCES Part(PartID),
    FOREIGN KEY (ProjectID)  REFERENCES Project(ProjectID)
);

12.5Participation Constraints

-- Employee ---(Total)--- Works_In --- Department
-- พนักงานทุกคนต้องสังกัดแผนก
Employee(EmpID, Name, DeptID NOT NULL)
Department(DeptID, DeptName)

-- Employee ---(Partial)--- Assigned to --- Project
-- ไม่ใช่พนักงานทุกคนที่ถูกมอบหมายโปรเจกต์
Employee(EmpID, Name, ProjectID)

12.6Weak Entity

การแปลง: สร้างตารางสำหรับ weak entity ที่มีpartial key ของมันและforeign key ไปยัง owner entity

-- ER: Employee(EmpID, Name) --- Owns --- Dependent(Name, Relationship)
Employee(EmpID, Name)
Dependent(EmpID, Name, Relationship)

CREATE TABLE Dependent (
    EmpID        INT,
    Name         VARCHAR(50),
    Relationship VARCHAR(30),
    PRIMARY KEY (EmpID, Name),
    FOREIGN KEY (EmpID) REFERENCES Employee(EmpID)
);

primary key ของ Dependent คือการรวมกันของ partial key (Name) กับ foreign key ไปยัง owner (EmpID)

12.7Relationship Attributes และ Denormalization

ถ้า relationship มี attribute ของตัวเอง ให้ใส่ไว้คู่กับ foreign key

-- ER: Employee --- (N:1, JoinDate) --- Department
Employee(EmployeeID, Name, DepartmentID, JoinDate)
Department(DepartmentID, DeptName)

Employee(EmployeeID, Name, DepartmentID, DeptName)
Department(DepartmentID, DeptName)

12.8เฉลยเต็ม — คลินิกสัตวแพทย์ (20 คะแนน)

โจทย์: จากไดอะแกรมคลินิกสัตวแพทย์ที่ให้มา จงแปลงเป็น relational schema โดยเขียน ชื่อตาราง, attribute พร้อมชนิดข้อมูล (MySQL), PK, FK และ constraints ให้ครบทุกตาราง

เฉลยเต็มทั้ง 5 ตาราง

CREATE TABLE Owner (
    owner_id INT          PRIMARY KEY,
    name     VARCHAR(100) NOT NULL,
    street   VARCHAR(100),          -- composite attribute แตกเป็น 3 คอลัมน์
    city     VARCHAR(50),
    zip      VARCHAR(10)
);

CREATE TABLE Pet (
    pet_id   INT         PRIMARY KEY,
    pet_name VARCHAR(50) NOT NULL,
    species  VARCHAR(50),
    owner_id INT         NOT NULL,   -- total participation → NOT NULL
    FOREIGN KEY (owner_id) REFERENCES Owner(owner_id)
);

CREATE TABLE Vet (
    vet_id    INT          PRIMARY KEY,
    vet_name  VARCHAR(100) NOT NULL,
    specialty VARCHAR(50)
);

-- M:N ระหว่าง Pet กับ Vet + relationship attributes
CREATE TABLE Treatment (
    pet_id     INT,
    vet_id     INT,
    treat_date DATE,
    diagnosis  VARCHAR(255),
    PRIMARY KEY (pet_id, vet_id, treat_date),
    FOREIGN KEY (pet_id) REFERENCES Pet(pet_id),
    FOREIGN KEY (vet_id) REFERENCES Vet(vet_id)
);

-- weak entity ที่ Pet เป็นเจ้าของ
CREATE TABLE Vaccination (
    pet_id       INT,
    dose_no      INT,               -- discriminator (partial key)
    vaccine_name VARCHAR(50) NOT NULL,
    PRIMARY KEY (pet_id, dose_no),
    FOREIGN KEY (pet_id) REFERENCES Pet(pet_id)
);

รายการจุดหักคะแนนที่พบบ่อย — อ่านก่อนเข้าห้องสอบ

12.9ขั้นตอนทำข้อสอบแปลง ER → Schema