Lab: Schema Design

สถานการณ์

คุณได้รับมอบหมายให้ออกแบบ database สำหรับระบบโรงเรียนสอนเขียนโปรแกรม ระบบต้องเก็บข้อมูล:

นักเรียน — รหัส, ชื่อ, อีเมล, ค่าเรียน, วันสมัคร, สถานะ
คอร์สเรียน — รหัส, ชื่อคอร์ส, ราคา, จำนวนชั่วโมง
การลงทะเบียน — นักเรียนคนไหนลงคอร์สไหน เมื่อไหร่

โจทย์

สร้างตาราง students พร้อม constraints ที่เหมาะสม

ต้องมี: PRIMARY KEY, NOT NULL สำหรับ name, UNIQUE สำหรับ email, CHECK price >= 0, DEFAULT active = TRUE

Show Solution

DROP TABLE IF EXISTS students;

CREATE TABLE students (
    student_id  VARCHAR(6)    PRIMARY KEY,
    name        VARCHAR(100)  NOT NULL,
    email       VARCHAR(100)  UNIQUE,
    price       NUMERIC(10,2) CHECK (price >= 0) DEFAULT 0.00,
    enrolled_on DATE          DEFAULT CURRENT_DATE,
    active      BOOLEAN       DEFAULT TRUE
);

สร้างตาราง courses ที่มี course_id, course_name, price, hours

ต้องมี: PRIMARY KEY, NOT NULL สำหรับ course_name, CHECK hours > 0 และ price >= 0

Show Solution

DROP TABLE IF EXISTS courses;

CREATE TABLE courses (
    course_id   VARCHAR(6)    PRIMARY KEY,
    course_name VARCHAR(100)  NOT NULL,
    price       NUMERIC(10,2) CHECK (price >= 0) DEFAULT 0.00,
    hours       INTEGER       CHECK (hours > 0) NOT NULL
);

สร้างตาราง enrollments ที่เชื่อม students กับ courses

ต้องมี: SERIAL PRIMARY KEY, FOREIGN KEY ไปยัง students และ courses, DEFAULT วันที่ปัจจุบัน

Show Solution

DROP TABLE IF EXISTS enrollments;

CREATE TABLE enrollments (
    id          SERIAL PRIMARY KEY,
    student_id  VARCHAR(6) NOT NULL REFERENCES students(student_id),
    course_id   VARCHAR(6) NOT NULL REFERENCES courses(course_id),
    enrolled_on DATE       DEFAULT CURRENT_DATE,
    UNIQUE (student_id, course_id)  -- นักเรียน 1 คนลงคอร์สเดียวกันได้แค่ครั้งเดียว
);

INSERT ข้อมูลตัวอย่าง: 3 นักเรียน, 2 คอร์ส, 4 การลงทะเบียน

Show Solution

INSERT INTO students (student_id, name, email, price)
VALUES
    ('STD001', 'สมชาย', 'somchai@mail.com', 4500.00),
    ('STD002', 'สมหญิง', 'somying@mail.com', 3200.00),
    ('STD003', 'วิชัย', 'wichai@mail.com', 7800.00);

INSERT INTO courses (course_id, course_name, price, hours)
VALUES
    ('CS101', 'Python Basics', 2500.00, 30),
    ('CS201', 'SQL Fundamentals', 3500.00, 40);

INSERT INTO enrollments (student_id, course_id)
VALUES
    ('STD001', 'CS101'),
    ('STD001', 'CS201'),
    ('STD002', 'CS101'),
    ('STD003', 'CS201');

ทดสอบ constraints: ลอง INSERT ข้อมูลที่ผิดกฎ แล้วดูว่า error อะไร

ลอง: email ซ้ำ, price ติดลบ, student_id ที่ไม่มีอยู่ใน enrollments

Show Solution

-- 1) email ซ้ำ → unique violation
INSERT INTO students (student_id, name, email)
VALUES ('STD004', 'ทดสอบ', 'somchai@mail.com');

-- 2) price ติดลบ → check violation
INSERT INTO students (student_id, name, price)
VALUES ('STD004', 'ทดสอบ', -100);

-- 3) FK ไม่มี → foreign key violation
INSERT INTO enrollments (student_id, course_id)
VALUES ('STD999', 'CS101');

ทั้ง 3 คำสั่งจะ error — แสดงว่า constraints ทำงานถูกต้อง