Sales Management Schema

OVERVIEW

The sales management database consists of the following relations

KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, DOANHSO, NGDK)

Definition: The customer relation stores information for member customers, including: customer ID, name, address, phone number, date of birth, registration date, and sales total (the total value of invoices for this member customer).

NHANVIEN (MANV, HOTEN, NGVL, SODT)

Definition: Each sales employee needs a recorded name, hire date, and contact phone number; employees are distinguished from one another by employee ID.

SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA)

Definition: Each product has a code, a name, a unit of measure, a country of manufacture, and a selling price.

HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA)

Definition: When making a purchase, each customer receives an invoice, which records the invoice number, purchase date, the employee who made the sale, the invoice value, and the customer's ID if they're a member customer.

CTHD (SOHD, MASP, SL)

Definition: Details the line items within each invoice - which products, and in what quantity.

Relationship Diagram

Relationship Description

KHACHHANG (1) ➜ HOADON (N): a member customer can have multiple invoices.

NHANVIEN (1) ➜ HOADON (N): an employee sells multiple invoices.

HOADON (1) ➜ CTHD (N): an invoice has multiple line items.

SANPHAM (1) ➜ CTHD (N): a product appears in multiple line items, across multiple different invoices.

Common mix-up: HOADON.MAKH can be left blank (NULL) - not every buyer is a member customer. See invoices 1022 and 1023 in the sample data; neither has a MAKH attached.

RELATION 1 · KHACHHANG

KHACHHANG

Stores information for member customers: customer ID, name, address, phone number, date of birth, membership registration date, and sales total (the total value of invoices this member customer has purchased).

Attribute	Data type	Description
`MAKH`	`char(4)`	Customer ID - primary key
`HOTEN`	`varchar(40)`	Full name
`DCHI`	`varchar(50)`	Address
`SODT`	`varchar(20)`	Phone number
`NGSINH`	`smalldatetime`	Date of birth
`NGDK`	`smalldatetime`	Membership registration date
`DOANHSO`	`money`	Total value of invoices purchased

SAMPLE DATA (all 10 customers)

MAKH	HOTEN	DCHI	SODT	NGSINH	NGDK	DOANHSO
KH01	Nguyen Van A	731 Tran Hung Dao, Q5, TpHCM	8823451	22/10/1960	22/07/2006	13.060.000
KH02	Tran Ngoc Han	23/5 Nguyen Trai, Q5, TpHCM	908256478	03/04/1974	30/07/2006	280.000
KH03	Tran Ngoc Linh	45 Nguyen Canh Chan, Q1, TpHCM	938776266	12/06/1980	08/05/2006	3.860.000
KH04	Tran Minh Long	50/34 Le Dai Hanh, Q10, TpHCM	917325476	09/03/1965	10/02/2006	250.000
KH05	Le Nhat Minh	34 Truong Dinh, Q3, TpHCM	8246108	10/03/1950	28/10/2006	21.000
KH06	Le Hoai Thuong	227 Nguyen Van Cu, Q5, TpHCM	8631738	31/12/1981	24/11/2006	915.000
KH07	Nguyen Van Tam	32/3 Tran Binh Trong, Q5, TpHCM	916783565	06/04/1971	12/01/2006	12.500
KH08	Phan Thi Thanh	45/2 An Duong Vuong, Q5, TpHCM	938435756	10/01/1971	13/12/2006	365.000
KH09	Le Ha Vinh	873 Le Hong Phong, Q5, TpHCM	8654763	03/09/1979	14/01/2007	70.000
KH10	Ha Duy Lap	34/34B Nguyen Trai, Q1, TpHCM	8768904	02/05/1983	16/01/2007	67.500

RELATION 2 · NHANVIEN

NHANVIEN

Each sales employee is recorded with: employee ID, name, hire date, and contact phone number.

Attribute	Data type	Description
`MANV`	`char(4)`	Employee ID - primary key
`HOTEN`	`varchar(40)`	Full name
`SODT`	`varchar(20)`	Phone number
`NGVL`	`smalldatetime`	Hire date

SAMPLE DATA (complete - only 5 employees)

MANV	HOTEN	SODT	NGVL
NV01	Nguyen Nhu Nhut	0927345678	13/04/2006
NV02	Le Thi Phi Yen	0987567390	21/04/2006
NV03	Nguyen Van B	0997047382	27/04/2006
NV04	Ngo Thanh Tuan	0913758498	24/06/2006
NV05	Nguyen Thi Truc Thanh	0918590387	20/07/2006

RELATION 3 · SANPHAM

SANPHAM

Each product has a code, name, unit of measure, country of manufacture, and selling price.

Attribute	Data type	Description
`MASP`	`char(4)`	Product code - primary key
`TENSP`	`varchar(40)`	Product name
`DVT`	`varchar(20)`	Unit of measure (piece, box, book, item...)
`NUOCSX`	`varchar(40)`	Country of manufacture
`GIA`	`money`	Selling price

SAMPLE DATA (all 24 products)

MASP	TENSP	DVT	NUOCSX	GIA
BC01	But chi	cay	Singapore	3.000
BC02	But chi	cay	Singapore	5.000
BC03	But chi	cay	Viet Nam	3.500
BC04	But chi	hop	Viet Nam	30.000
BB01	But bi	cay	Viet Nam	5.000
BB02	But bi	cay	Trung Quoc	7.000
BB03	But bi	hop	Thai Lan	100.000
TV01	Tap 100 giay mong	quyen	Trung Quoc	2.500
TV02	Tap 200 giay mong	quyen	Trung Quoc	4.500
TV03	Tap 100 giay tot	quyen	Viet Nam	3.000
TV04	Tap 200 giay tot	quyen	Viet Nam	5.500
TV05	Tap 100 trang	chuc	Viet Nam	23.000
TV06	Tap 200 trang	chuc	Viet Nam	53.000
TV07	Tap 100 trang	chuc	Trung Quoc	34.000
ST01	So tay 500 trang	quyen	Trung Quoc	40.000
ST02	So tay loai 1	quyen	Viet Nam	55.000
ST03	So tay loai 2	quyen	Viet Nam	51.000
ST04	So tay	quyen	Thai Lan	55.000
ST05	So tay mong	quyen	Thai Lan	20.000
ST06	Phan viet bang	hop	Viet Nam	5.000
ST07	Phan khong bui	hop	Viet Nam	7.000
ST08	Bong bang	cai	Viet Nam	1.000
ST09	But long	cay	Viet Nam	5.000
ST10	But long	cay	Trung Quoc	7.000

RELATION 4 · HOADON

HOADON

When making a purchase, a customer receives an invoice: invoice number, purchase date, selling employee, invoice value, and customer ID (if the buyer is a member customer).

Attribute	Data type	Description
`SOHD`	`int`	Invoice number - primary key
`NGHD`	`smalldatetime`	Invoice date
`MAKH`	`char(4)`	Which customer made the purchase - foreign key to KHACHHANG, can be NULL
`MANV`	`char(4)`	Which employee made the sale - foreign key to NHANVIEN
`TRIGIA`	`money`	Invoice value

SAMPLE DATA (all 23 invoices)

SOHD	NGHD	MAKH	MANV	TRIGIA
1001	23/07/2006	KH01	NV01	320.000
1002	12/08/2006	KH01	NV02	840.000
1003	23/08/2006	KH02	NV01	100.000
1004	01/09/2006	KH02	NV01	180.000
1005	20/10/2006	KH01	NV02	3.800.000
1006	16/10/2006	KH01	NV03	2.430.000
1007	28/10/2006	KH03	NV03	510.000
1008	28/10/2006	KH01	NV03	440.000
1009	28/10/2006	KH03	NV04	200.000
1010	01/11/2006	KH01	NV01	5.200.000
1011	04/11/2006	KH04	NV03	250.000
1012	30/11/2006	KH05	NV03	21.000
1013	12/12/2006	KH06	NV01	5.000
1014	31/12/2006	KH03	NV02	3.150.000
1015	01/01/2007	KH06	NV01	910.000
1016	01/01/2007	KH07	NV02	12.500
1017	02/01/2007	KH08	NV03	35.000
1018	13/01/2007	KH08	NV03	330.000
1019	13/01/2007	KH01	NV03	30.000
1020	14/01/2007	KH09	NV04	70.000
1021	16/01/2007	KH10	NV03	67.500
1022	16/01/2007	NULL	NV03	7.000
1023	17/01/2007	NULL	NV01	330.000

RELATION 5 · CTHD

CTHD (Invoice Line Items)

Details the line items within each invoice: which products it contains, and in what quantity. The primary key is the pair (SOHD, MASP) - since within a single invoice, a product can appear on at most 1 line.

Attribute	Data type	Description
`SOHD`	`int`	Invoice number - primary key, foreign key to HOADON
`MASP`	`char(4)`	Product code - primary key, foreign key to SANPHAM
`SL`	`int`	Quantity purchased

SAMPLE DATA (all 48 rows)

SOHD	MASP	SL
1001	TV02	10
1001	ST01	5
1001	BC01	5
1001	BC02	10
1001	ST08	10
1002	BC04	20
1002	BB01	20
1002	BB02	20
1003	BB03	10
1004	TV01	20
1004	TV02	10
1004	TV03	10
1004	TV04	10
1005	TV05	50
1005	TV06	50
1006	TV07	20

SOHD	MASP	SL
1006	ST01	30
1006	ST02	10
1007	ST03	10
1008	ST04	8
1009	ST05	10
1010	TV07	50
1010	ST07	50
1010	ST08	100
1010	ST04	50
1010	TV03	100
1011	ST06	50
1012	ST07	3
1013	ST08	5
1014	BC02	80
1014	BB02	100
1014	BC04	60

SOHD	MASP	SL
1014	BB01	50
1015	BB02	30
1015	BB03	7
1016	TV01	5
1017	TV02	1
1017	TV03	1
1017	TV04	5
1018	ST04	6
1019	ST05	1
1019	ST06	2
1020	ST07	10
1021	ST08	5
1021	TV01	7
1021	TV02	10
1022	ST07	1
1023	ST04	6

FULL SCRIPT

Create the database and load sample data

A script that creates the QLBH_2025 database, creates 5 tables with primary/foreign keys, and loads the full set of sample data (10 customers, 5 employees, 24 products, 23 invoices, 48 line items). Run it directly in SSMS before starting the exercises.

Sales Management Database Script (QLBH_2025) .sql file · creates tables, constraints, and the full sample data

Download ↓

Applies to: BTTH2 (Week 2), BTTH3 (Week 3), and the Trigger examples in BTTH5 (Week 5) all share this schema - you only need to run the script once.