Student Exam Database Part 1 - Create Tables in MySQL (Video in Khmer)

Hello my friend! Today we start a new real-world small sample project call Student Exam Database!

In this Part 1, we will write SQL statements to create 4 tables (Students, Teachers, Courses, Exam), set up Foreign Key relationships, and generate EER Diagram. Video tutorial is in Khmer language, very easy to understand! Now let get start!

---

📐 ERD Diagram Reference (Table Relationship)

Here is the ER Diagram of our Student Exam Database. It show how 4 tables connect together:

📑 Explanation of 4 Tables:

• Students — store student personal information (ID, Name, Gender, Date of Birth, City)
• Teachers — store teacher information (ID, Name, Title)
• Courses — store course info and link to teacher who teach that course (has Foreign Key teacherID)
• Exam — junction table to store student exam score for each course (has Foreign Keys studentID, courseID and Composite Primary Key)

---

💻 Write SQL Statements to Create Tables

1. Create Students Table

Independent table to store student details:

CREATE TABLE Students(
    studentID INT PRIMARY KEY NOT NULL,
    name VARCHAR(25) NULL,
    gender VARCHAR(1) NULL,
    dateOfBirth DATE NULL,
    city VARCHAR(30) NULL
);

---

2. Create Teachers Table

Independent table to store teacher details:

CREATE TABLE Teachers(
    teacherID SMALLINT PRIMARY KEY NOT NULL,
    name VARCHAR(25) NULL,
    title VARCHAR(30) NULL
);

---

3. Create Courses Table (With 1 Foreign Key)

Relates each course to 1 teacher via teacherID:

CREATE TABLE Courses(
    courseID SMALLINT PRIMARY KEY NOT NULL,
    courseName VARCHAR(30) NULL,
    teacherID SMALLINT NULL,
    CONSTRAINT FKtID FOREIGN KEY (teacherID) REFERENCES Teachers(teacherID)
);

---

4. Create Exam Table (With 2 Foreign Keys & Composite Primary Key)

Junction table connecting Students and Courses to store exam scores:

CREATE TABLE Exam(
    studentID INT NOT NULL,
    courseID SMALLINT NOT NULL,
    score SMALLINT NULL,
    CONSTRAINT FKsID FOREIGN KEY (studentID) REFERENCES Students(studentID),
    CONSTRAINT FKcID FOREIGN KEY (courseID) REFERENCES Courses(courseID),
    CONSTRAINT PK_Exam PRIMARY KEY (studentID, courseID)
);

---

📺 Video Tutorials (In Khmer)

🎬 Part 1: Create Tables Video Tutorial

Watch this video to learn how to write SQL queries and create tables step-by-step in MySQL Workbench:

---

🎬 Part 2: Generate EER Diagram in MySQL Workbench

Watch this video to learn how to reverse engineer your database tables into a visual EER Diagram automatically:

---

💡 Tip for Database Beginner: In the Exam table, (studentID, courseID) form a Composite Primary Key! This ensures a student cannot have duplicate exam records for the exact same course twice, keeping your data clean and accurate!