Academic Affairs Schema

OVERVIEW

The academic affairs management database consists of the following relations

HOCVIEN (MAHV, HO, TEN, NGSINH, GIOITINH, NOISINH, MALOP)

Definition: Each student is distinguished by a student ID, and has a recorded name, date of birth, gender, place of birth, and the class they belong to.

LOP (MALOP, TENLOP, TRGLOP, SISO, MAGVCN)

Definition: Each class has a class code, class name, the student who serves as class monitor, class size, and a homeroom teacher.

KHOA (MAKHOA, TENKHOA, NGTLAP, TRGKHOA)

Definition: Each department records a department code, department name, founding date, and department head (who is also a teacher belonging to the department).

MONHOC (MAMH, TENMH, TCLT, TCTH, MAKHOA)

Definition: Each course records a course name, number of theory credits, number of practical credits, and which department is responsible for it.

DIEUKIEN (MAMH, MAMH_TRUOC)

Definition: Some courses require students to already have knowledge from certain prerequisite courses.

GIAOVIEN (MAGV, HOTEN, HOCVI, HOCHAM, GIOITINH, NGSINH, NGVL, HESO, MUCLUONG, MAKHOA)

Definition: A teacher ID distinguishes between teachers; each record also stores name, degree, academic title, gender, date of birth, hire date, salary coefficient, salary amount, and the department they belong to.

GIANGDAY (MALOP, MAMH, MAGV, HOCKY, NAM, TUNGAY, DENNGAY)

Definition: Each semester of the academic year has teaching assignments: which class takes which course, taught by which teacher.

KETQUATHI (MAHV, MAMH, LANTHI, NGTHI, DIEM, KQUA)

Definition: Stores students' exam results: which student took which course exam, which attempt number, the exam date, the score, and whether the result was a pass or fail.

Relationship Diagram

Relationship Description

KHOA ➜ GIAOVIEN (via TRGKHOA): each department has 1 department head, who is a teacher.

GIAOVIEN ➜ KHOA (via MAKHOA): each teacher belongs to exactly 1 department.

MONHOC ➜ KHOA (via MAKHOA): each course is managed by 1 department.

DIEUKIEN ➜ MONHOC (via MAMH and MAMH_TRUOC): each prerequisite row links 2 courses together - the course and the one that must be taken first.

LOP ➜ HOCVIEN (via TRGLOP): each class has 1 class monitor, who is a student of that same class.

HOCVIEN ➜ LOP (via MALOP): each student belongs to exactly 1 class.

LOP ➜ GIAOVIEN (via MAGVCN): each class has 1 homeroom teacher.

GIANGDAY ➜ LOP, MONHOC, GIAOVIEN: each teaching assignment records which class takes which course, taught by which teacher.

KETQUATHI ➜ HOCVIEN, MONHOC: each exam result is tied to 1 specific student and 1 specific course.

Common mix-up: KHOA.TRGKHOA and GIAOVIEN.MAKHOA reference each other in both directions (a department has 1 department head, a teacher belongs to 1 department) - the same is true of LOP.TRGLOP and HOCVIEN.MALOP. In the sample data, department KTMT doesn't have a department head yet (TRGKHOA is NULL).

RELATION 1 · HOCVIEN

HOCVIEN

Each student is distinguished by a student ID, with a recorded name, date of birth, gender, place of birth, and which class they belong to.

Attribute	Data type	Description
`MAHV`	`char(5)`	Student ID - primary key
`HO`	`varchar(40)`	Family name and middle name
`TEN`	`varchar(10)`	Given name
`NGSINH`	`smalldatetime`	Date of birth
`GIOITINH`	`varchar(3)`	Gender
`NOISINH`	`varchar(40)`	Place of birth
`MALOP`	`char(3)`	Class code - foreign key to LOP

SAMPLE DATA (all 35 students)

MAHV	HO	TEN	NGSINH	GIOITINH	NOISINH	MALOP
K1101	Nguyen Van	A	27/1/1986	Nam	TpHCM	K11
K1102	Tran Ngoc	Han	14/3/1986	Nu	Kien Giang	K11
K1103	Ha Duy	Lap	18/4/1986	Nam	Nghe An	K11
K1104	Tran Ngoc	Linh	30/3/1986	Nu	Tay Ninh	K11
K1105	Tran Minh	Long	27/2/1986	Nam	TpHCM	K11
K1106	Le Nhat	Minh	24/1/1986	Nam	TpHCM	K11
K1107	Nguyen Nhu	Nhut	27/1/1986	Nam	Ha Noi	K11
K1108	Nguyen Manh	Tam	27/2/1986	Nam	Kien Giang	K11
K1109	Phan Thi Thanh	Tam	27/1/1986	Nu	Vinh Long	K11
K1110	Le Hoai	Thuong	02/05/1986	Nu	Can Tho	K11
K1111	Le Ha	Vinh	25/12/1986	Nam	Vinh Long	K11
K1201	Nguyen Van	B	02/11/1986	Nam	TpHCM	K12
K1202	Nguyen Thi Kim	Duyen	18/1/1986	Nu	TpHCM	K12
K1203	Tran Thi Kim	Duyen	17/9/1986	Nu	TpHCM	K12
K1204	Truong My	Hanh	19/5/1986	Nu	Dong Nai	K12
K1205	Nguyen Thanh	Nam	17/4/1986	Nam	TpHCM	K12
K1206	Nguyen Thi Truc	Thanh	03/04/1986	Nu	Kien Giang	K12
K1207	Tran Thi Bich	Thuy	02/08/1986	Nu	Nghe An	K12

MAHV	HO	TEN	NGSINH	GIOITINH	NOISINH	MALOP
K1208	Huynh Thi Kim	Trieu	04/08/1986	Nu	Tay Ninh	K12
K1209	Pham Thanh	Trieu	23/2/1986	Nam	TpHCM	K12
K1210	Ngo Thanh	Tuan	14/2/1986	Nam	TpHCM	K12
K1211	Do Thi	Xuan	03/09/1986	Nu	Ha Noi	K12
K1212	Le Thi Phi	Yen	03/12/1986	Nu	TpHCM	K12
K1301	Nguyen Thi Kim	Cuc	06/09/1986	Nu	Kien Giang	K13
K1302	Truong Thi My	Hien	18/3/1986	Nu	Nghe An	K13
K1303	Le Duc	Hien	21/3/1986	Nam	Tay Ninh	K13
K1304	Le Quang	Hien	18/4/1986	Nam	TpHCM	K13
K1305	Le Thi	Huong	27/3/1986	Nu	TpHCM	K13
K1306	Nguyen Thai	Huu	30/3/1986	Nam	Ha Noi	K13
K1307	Tran Minh	Man	28/5/1986	Nam	TpHCM	K13
K1308	Nguyen Hieu	Nghia	04/08/1986	Nam	Kien Giang	K13
K1309	Nguyen Trung	Nghia	18/1/1987	Nam	Nghe An	K13
K1310	Tran Thi Hong	Tham	22/4/1986	Nu	Tay Ninh	K13
K1311	Tran Minh	Thuc	04/04/1986	Nam	TpHCM	K13
K1312	Nguyen Thi Kim	Yen	09/07/1986	Nu	TpHCM	K13

RELATION 2 · LOP

LOP

Each class has a class code, class name, the student who serves as class monitor, class size, and a homeroom teacher.

Attribute	Data type	Description
`MALOP`	`char(3)`	Class code - primary key
`TENLOP`	`varchar(40)`	Class name
`TRGLOP`	`char(5)`	Class monitor - foreign key to HOCVIEN
`SISO`	`tinyint`	Class size
`MAGVCN`	`char(4)`	Homeroom teacher - foreign key to GIAOVIEN

SAMPLE DATA (all 3 classes)

MALOP	TENLOP	TRGLOP	SISO	MAGVCN
K11	Lop 1 khoa 1	K1108	11	GV07
K12	Lop 2 khoa 1	K1205	12	GV09
K13	Lop 3 khoa 1	K1305	12	GV14

RELATION 3 · KHOA

KHOA

Each department records a department code, department name, founding date, and department head (who is also a teacher belonging to the department).

Attribute	Data type	Description
`MAKHOA`	`char(4)`	Department code - primary key
`TENKHOA`	`varchar(40)`	Department name
`NGTLAP`	`smalldatetime`	Department founding date
`TRGKHOA`	`char(4)`	Department head - foreign key to GIAOVIEN, can be NULL

SAMPLE DATA (all 5 departments)

MAKHOA	TENKHOA	NGTLAP	TRGKHOA
KHMT	Khoa hoc may tinh	06/07/2005	GV01
HTTT	He thong thong tin	06/07/2005	GV02
CNPM	Cong nghe phan mem	06/07/2005	GV04
MTT	Mang va truyen thong	20/10/2005	GV03
KTMT	Ky thuat may tinh	20/12/2005	NULL

RELATION 4 · MONHOC

MONHOC

Each course records a course name, number of theory credits, number of practical credits, and which department is responsible for it.

Attribute	Data type	Description
`MAMH`	`char(10)`	Course code - primary key
`TENMH`	`varchar(40)`	Course name
`TCLT`	`tinyint`	Number of theory credits
`TCTH`	`tinyint`	Number of practical credits
`MAKHOA`	`char(4)`	Which department the course belongs to - foreign key to KHOA

SAMPLE DATA (all 13 courses)

MAMH	TENMH	TCLT	TCTH	MAKHOA
THDC	Tin hoc dai cuong	4	1	KHMT
CTRR	Cau truc roi rac	5	0	KHMT
CSDL	Co so du lieu	3	1	HTTT
CTDLGT	Cau truc du lieu va giai thuat	3	1	KHMT
PTTKTT	Phan tich thiet ke thuat toan	3	0	KHMT
DHMT	Do hoa may tinh	3	1	KHMT
KTMT	Kien truc may tinh	3	0	KTMT
TKCSDL	Thiet ke co so du lieu	3	1	HTTT
PTTKHTTT	Phan tich thiet ke he thong thong tin	4	1	HTTT
HDH	He dieu hanh	4	0	KTMT
NMCNPM	Nhap mon cong nghe phan mem	3	0	CNPM
LTCFW	Lap trinh C for win	3	1	CNPM
LTHDT	Lap trinh huong doi tuong	3	1	CNPM

RELATION 5 · DIEUKIEN

DIEUKIEN

Some courses require students to already have knowledge from certain prerequisite courses. The primary key is the pair (MAMH, MAMH_TRUOC).

Attribute	Data type	Description
`MAMH`	`char(10)`	Course code - primary key, foreign key to MONHOC
`MAMH_TRUOC`	`char(10)`	Prerequisite course code - primary key, foreign key to MONHOC

SAMPLE DATA (all 8 rows)

MAMH	MAMH_TRUOC
CSDL	CTRR
CSDL	CTDLGT
CTDLGT	THDC
PTTKTT	THDC
PTTKTT	CTDLGT
DHMT	THDC
LTHDT	THDC
PTTKHTTT	CSDL

RELATION 6 · GIAOVIEN

GIAOVIEN

A teacher ID distinguishes between teachers; each record also stores name, degree, academic title, gender, date of birth, hire date, salary coefficient, salary amount, and the department the teacher belongs to.

Attribute	Data type	Description
`MAGV`	`char(4)`	Teacher ID - primary key
`HOTEN`	`varchar(40)`	Full name
`HOCVI`	`varchar(10)`	Degree (CN, KS, ThS, TS, PTS...)
`HOCHAM`	`varchar(10)`	Academic title (GV, PGS, GS...), can be NULL
`GIOITINH`	`varchar(3)`	Gender
`NGSINH`	`smalldatetime`	Date of birth
`NGVL`	`smalldatetime`	Hire date
`HESO`	`numeric(4,2)`	Salary coefficient
`MUCLUONG`	`money`	Salary amount
`MAKHOA`	`char(4)`	Which department - foreign key to KHOA

SAMPLE DATA (all 15 teachers)

MAGV	HOTEN	HOCVI	HOCHAM	GIOITINH	NGSINH	NGVL	HESO	MUCLUONG	MAKHOA
GV01	Ho Thanh Son	PTS	GS	Nam	05/02/1950	01/11/2004	5	2.250.000	KHMT
GV02	Tran Tam Thanh	TS	PGS	Nam	17/12/1965	20/4/2004	4.5	2.025.000	HTTT
GV03	Do Nghiem Phung	TS	GS	Nu	08/01/1950	23/9/2004	4	1.800.000	CNPM
GV04	Tran Nam Son	TS	PGS	Nam	22/2/1961	01/12/2005	4.5	2.025.000	KTMT
GV05	Mai Thanh Danh	ThS	GV	Nam	03/12/1958	01/12/2005	3	1.350.000	HTTT
GV06	Tran Doan Hung	TS	GV	Nam	03/11/1953	01/12/2005	4.5	2.025.000	KHMT
GV07	Nguyen Minh Tien	ThS	GV	Nam	23/11/1971	03/01/2005	4	1.800.000	KHMT
GV08	Le Thi Tran	KS	NULL	Nu	26/3/1974	03/01/2005	1.69	760.500	KHMT
GV09	Nguyen To Lan	ThS	GV	Nu	31/12/1966	03/01/2005	4	1.800.000	HTTT
GV10	Le Tran Anh Loan	KS	NULL	Nu	17/7/1972	03/01/2005	1.86	837.000	CNPM
GV11	Ho Thanh Tung	CN	GV	Nam	01/12/1980	15/5/2005	2.67	1.201.500	MTT
GV12	Tran Van Anh	CN	NULL	Nu	29/3/1981	15/5/2005	1.69	760.500	CNPM
GV13	Nguyen Linh Dan	CN	NULL	Nu	23/5/1980	15/5/2005	1.69	760.500	KTMT
GV14	Truong Minh Chau	ThS	GV	Nu	30/11/1976	15/5/2005	3	1.350.000	MTT
GV15	Le Ha Thanh	ThS	GV	Nam	05/04/1978	15/5/2005	3	1.350.000	KHMT

RELATION 7 · GIANGDAY

GIANGDAY

Each semester of the academic year has teaching assignments: which class takes which course, taught by which teacher. The primary key is the pair (MALOP, MAMH).

Attribute	Data type	Description
`MALOP`	`char(3)`	Class code - primary key, foreign key to LOP
`MAMH`	`char(10)`	Course code - primary key, foreign key to MONHOC
`MAGV`	`char(4)`	Teacher in charge - foreign key to GIAOVIEN
`HOCKY`	`tinyint`	Semester
`NAM`	`smallint`	Academic year
`TUNGAY`	`smalldatetime`	Course start date
`DENNGAY`	`smalldatetime`	Course end date

SAMPLE DATA (all 16 rows)

MALOP	MAMH	MAGV	HOCKY	NAM	TUNGAY	DENNGAY
K11	THDC	GV07	1	2006	01/02/2006	05/12/2006
K12	THDC	GV06	1	2006	01/02/2006	05/12/2006
K13	THDC	GV15	1	2006	01/02/2006	05/12/2006
K11	CTRR	GV02	1	2006	09/01/2006	17/5/2006
K12	CTRR	GV02	1	2006	09/01/2006	17/5/2006
K13	CTRR	GV08	1	2006	09/01/2006	17/5/2006
K11	CSDL	GV05	2	2006	06/01/2006	15/7/2006
K12	CSDL	GV09	2	2006	06/01/2006	15/7/2006
K13	CTDLGT	GV15	2	2006	06/01/2006	15/7/2006
K13	CSDL	GV05	3	2006	08/01/2006	15/12/2006
K13	DHMT	GV07	3	2006	08/01/2006	15/12/2006
K11	CTDLGT	GV15	3	2006	08/01/2006	15/12/2006
K12	CTDLGT	GV15	3	2006	08/01/2006	15/12/2006
K11	HDH	GV04	1	2007	01/02/2007	18/2/2007
K12	HDH	GV04	1	2007	01/02/2007	20/3/2007
K11	DHMT	GV07	1	2007	18/2/2007	20/3/2007

RELATION 8 · KETQUATHI

KETQUATHI

Stores students' exam results: which student took which course exam, which attempt number, the exam date, the score, and whether the result was a pass or fail. The primary key is the triple (MAHV, MAMH, LANTHI).

Attribute	Data type	Description
`MAHV`	`char(5)`	Student ID - primary key, foreign key to HOCVIEN
`MAMH`	`char(10)`	Course code - primary key, foreign key to MONHOC
`LANTHI`	`tinyint`	Attempt number - primary key
`NGTHI`	`smalldatetime`	Exam date
`DIEM`	`numeric(4,2)`	Score
`KQUA`	`varchar(10)`	Result (Dat / Khong Dat)

SAMPLE DATA (all 66 rows)

MAHV	MAMH	LANTHI	NGTHI	DIEM	KQUA
K1101	CSDL	1	20/7/2006	10	Dat
K1101	CTDLGT	1	28/12/2006	9	Dat
K1101	THDC	1	20/5/2006	9	Dat
K1101	CTRR	1	13/5/2006	9.5	Dat
K1102	CSDL	1	20/7/2006	4	Khong Dat
K1102	CSDL	2	27/7/2006	4.25	Khong Dat
K1102	CSDL	3	08/10/2006	4.5	Khong Dat
K1102	CTDLGT	1	28/12/2006	4.5	Khong Dat
K1102	CTDLGT	2	01/05/2007	4	Khong Dat
K1102	CTDLGT	3	15/1/2007	6	Dat
K1102	THDC	1	20/5/2006	5	Dat
K1102	CTRR	1	13/5/2006	7	Dat
K1103	CSDL	1	20/7/2006	3.5	Khong Dat
K1103	CSDL	2	27/7/2006	8.25	Dat
K1103	CTDLGT	1	28/12/2006	7	Dat
K1103	THDC	1	20/5/2006	8	Dat
K1103	CTRR	1	13/5/2006	6.5	Dat
K1104	CSDL	1	20/7/2006	3.75	Khong Dat
K1104	CTDLGT	1	28/12/2006	4	Khong Dat
K1104	THDC	1	20/5/2006	4	Khong Dat
K1104	CTRR	1	13/5/2006	4	Khong Dat
K1104	CTRR	2	20/5/2006	3.5	Khong Dat

MAHV	MAMH	LANTHI	NGTHI	DIEM	KQUA
K1104	CTRR	3	30/6/2006	4	Khong Dat
K1201	CSDL	1	20/7/2006	6	Dat
K1201	CTDLGT	1	28/12/2006	5	Dat
K1201	THDC	1	20/5/2006	8.5	Dat
K1201	CTRR	1	13/5/2006	9	Dat
K1202	CSDL	1	20/7/2006	8	Dat
K1202	CTDLGT	1	28/12/2006	4	Khong Dat
K1202	CTDLGT	2	01/05/2007	5	Dat
K1202	THDC	1	20/5/2006	4	Khong Dat
K1202	THDC	2	27/5/2006	4	Khong Dat
K1202	CTRR	1	13/5/2006	3	Khong Dat
K1202	CTRR	2	20/5/2006	4	Khong Dat
K1202	CTRR	3	30/6/2006	6.25	Dat
K1203	CSDL	1	20/7/2006	9.25	Dat
K1203	CTDLGT	1	28/12/2006	9.5	Dat
K1203	THDC	1	20/5/2006	10	Dat
K1203	CTRR	1	13/5/2006	10	Dat
K1204	CSDL	1	20/7/2006	8.5	Dat
K1204	CTDLGT	1	28/12/2006	6.75	Dat
K1204	THDC	1	20/5/2006	4	Khong Dat
K1204	CTRR	1	13/5/2006	6	Dat
K1301	CSDL	1	20/12/2006	4.25	Khong Dat

MAHV	MAMH	LANTHI	NGTHI	DIEM	KQUA
K1301	CTDLGT	1	25/7/2006	8	Dat
K1301	THDC	1	20/5/2006	7.75	Dat
K1301	CTRR	1	13/5/2006	8	Dat
K1302	CSDL	1	20/12/2006	6.75	Dat
K1302	CTDLGT	1	25/7/2006	5	Dat
K1302	THDC	1	20/5/2006	8	Dat
K1302	CTRR	1	13/5/2006	8.5	Dat
K1303	CSDL	1	20/12/2006	4	Khong Dat
K1303	CTDLGT	1	25/7/2006	4.5	Khong Dat
K1303	CTDLGT	2	08/07/2006	4	Khong Dat
K1303	CTDLGT	3	15/8/2006	4.25	Khong Dat
K1303	THDC	1	20/5/2006	4.5	Khong Dat
K1303	CTRR	1	13/5/2006	3.25	Khong Dat
K1303	CTRR	2	20/5/2006	5	Dat
K1304	CSDL	1	20/12/2006	7.75	Dat
K1304	CTDLGT	1	25/7/2006	9.75	Dat
K1304	THDC	1	20/5/2006	5.5	Dat
K1304	CTRR	1	13/5/2006	5	Dat
K1305	CSDL	1	20/12/2006	9.25	Dat
K1305	CTDLGT	1	25/7/2006	10	Dat
K1305	THDC	1	20/5/2006	8	Dat
K1305	CTRR	1	13/5/2006	10	Dat

FULL SCRIPT

Create the database and load sample data

A script that creates the QUANLIGIAOVU_2026 database, creates 8 tables with primary/foreign keys, and loads the full set of sample data (5 departments, 3 classes, 13 courses, 8 prerequisites, 15 teachers, 35 students, 16 teaching assignments, 66 exam results). Run it directly in SSMS before starting the exercises.

Academic Affairs Database Script (QUANLIGIAOVU_2026) .sql file · creates tables, constraints, and the full sample data

Download ↓

Applies to: BTTH4 (Week 4) - Part I questions 1, 3-8, 11-14 · Part II questions 1-4 · Part III questions 1-24 all share this schema; you only need to run the script once.

Academic Affairs Management Database