Student Exam Database Part 1: Create Tables for Student Exam Database

Student Exam Database Part 1: Create Tables for Student Exam Database

This tutorial about Student Exam database working on MySQL in Khmer. Link to download example data is below.

I will show you step by step from Creating Tables, Table with Relationship, Insert data and query data from the database.

Here is an ER Diagram (Table Relationship) helping you to understand more about this sample database

◾Students: used to store student information
◾Teachers: used to store teacher information
◾Courses: used to store course information with who teach that course
◾Exam: used to store exam information with student information and course information

Learn More Introduction to Database (SQL) in Khmer with videos: Chapter 1 | Chapter 2 | Chapter 3

SQL statement to create Students Table

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

SQL statement to create Teachers Table

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

SQL statement to create Courses Table with one FOREIGN KEY

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

SQL statement to create Exam Table with two FOREIGN KEY

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

Free Khmer Ebook Download (PDF): Database | Microsoft Access | Python Programming

EP14 របៀបបង្កើត EER Diagram នៃ Student Exam Database
This video show step by step how to generate EER Diagram or Entity Relationship Diagram from a database tables you have created above.