MySQL Part 2: Write SQL to Create Tables for Library Database with Data Download

Part 1: Write SQL to Create Tables for Library Database in MySQL Server speak Khmer

This part 2 of Library Database project working on MySQL Server with MySQL Workbench tool with creating tables, generate ER Diagram and insert data to the database. You can download all the data as sql insert statment below
This library database has 5 tables with relationship.
ERD Diagram - Relationship Diagram

- authors: table to store author information
- types: table to store types of books
- books: table to store books information
- students: table to store students information
- borrow: table to store all books that students borrowed

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

EP03 សរសេ SQL បង្កើតតារាងក្នុង MySQL Workbench ផ្នែកទី១
This part show you step by step how to work on MySQL Workbench and write SQL statements to create tables to specific database.

SQL for Table: students

CREATE TABLE students(
	studentID int PRIMARY KEY NOT NULL,
	name varchar(20) NULL,
	surname varchar(20) NULL,
	birthdate date NULL,
	gender varchar(10) NULL,
	class varchar(7) NULL,
	point int NULL
);

SQL for Table: authors

CREATE TABLE authors(
	authorID int primary key NOT NULL,
	name varchar(50) NULL,
	surname varchar(70) NULL
    );

SQL for Table: types

Create table types(
	typeID int primary key not null,
	name varchar(25)
);

EP04 សរសេ SQL បង្កើតតារាងជាមួយ Relationship ផ្នែកទី២
This part show how to write SQL statements to create table with relationship and set the foreign key that references to primary key of original table.

SQL for Table: books

CREATE TABLE books(
	bookID int primary key NOT NULL,
	name varchar(90) NULL,
	pagecount int NULL,
	point int NULL,
	authorID int, 
	typeID int,
    constraint FkauthorID foreign key(authorID) references authors(authorID),
    constraint FktypeID foreign key(typeID) references types(typeID)
    );

SQL for Table: borrows

CREATE TABLE borrows(
	borrowId int PRIMARY KEY NOT NULL,
	studentID int NULL,
	bookID int NULL,
	takenDate datetime NULL,
	broughtDate datetime NULL,
 	CONSTRAINT fkstudentID FOREIGN KEY(studentID) references students(studentID),
 	CONSTRAINT fkbookID FOREIGN KEY(bookID) references books(bookID)
 );

EP05 បង្ហាញតារាង Relationship Diagram (ER Digagram)
This part show how to generate tables relationship into ER Diagram as you show on the top earlier and how to insert data to the database with SQL statement.

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