22521543_BTTH1.sql

-- Ho va ten: Le Minh Tri
-- MSSV: 22521543

-- BAI THUC HANH: QLY_BANHANG
CREATE DATABASE QLY_BANHANG
GO

USE QLY_BANHANG

CREATE TABLE KHACHHANG
(
    MAKH CHAR(4) PRIMARY KEY NOT NULL,
    HOTEN VARCHAR(40) NOT NULL,
    DCHI VARCHAR(50) NOT NULL,
    SODT VARCHAR(20) NOT NULL,
    NGSINH SMALLDATETIME NOT NULL,
    DOANHSO MONEY NOT NULL,
    NGDK SMALLDATETIME NOT NULL
)

CREATE TABLE NHANVIEN
(
    MANV CHAR(4) PRIMARY KEY NOT NULL,
    HOTEN VARCHAR(40) NOT NULL,
    SODT VARCHAR(20) NOT NULL,
    NGVL SMALLDATETIME NOT NULL,
)

CREATE TABLE SANPHAM
(
    MASP CHAR(4) PRIMARY KEY NOT NULL,
    TENSP VARCHAR(40) NOT NULL,
    DVT VARCHAR(20) NOT NULL,
    NUOCSX VARCHAR(40) NOT NULL,
    GIA MONEY NOT NULL
)

CREATE TABLE HOADON
(
    SOHD INT PRIMARY KEY NOT NULL,
    NGHD SMALLDATETIME NOT NULL,
    MAKH CHAR(4) FOREIGN KEY REFERENCES KHACHHANG(MAKH),
    MANV CHAR(4) FOREIGN KEY REFERENCES NHANVIEN(MANV),
    TRIGIA MONEY NOT NULL,
)

CREATE TABLE CTHD
(
    SOHD INT FOREIGN KEY REFERENCES HOADON(SOHD),
    MASP CHAR(4) FOREIGN KEY REFERENCES SANPHAM(MASP),
    SL INT CONSTRAINT PK_CTHD PRIMARY KEY(SOHD,MASP)
)

ALTER TABLE SANPHAM ADD GHICHU VARCHAR(20) NOT NULL

ALTER TABLE KHACHHANG ADD LOAIKH TINYINT NOT NULL

ALTER TABLE SANPHAM ALTER COLUMN GHICHU VARCHAR(100) NOT NULL

ALTER TABLE KHACHHANG ADD CONSTRAINT GT_LOAIKH CHECK (LOAIKH='Vang lai' OR LOAIKH='Thuong xuyen' OR LOAIKH='Vip')

ALTER TABLE SANPHAM ADD CONSTRAINT GT_DVT CHECK (DVT='cay' OR DVT='hop' OR DVT='cai' OR DVT='quyen' OR DVT='chuc')


-- BAI THUC HANH: QLY_HOCVU

CREATE DATABASE QLY_HOCVU
GO
USE QLY_HOCVU

CREATE TABLE KHOA
(
    MAKHOA VARCHAR(4) PRIMARY KEY NOT NULL,
    TENKHOA VARCHAR(40) NOT NULL,
    NGTLAP SMALLDATETIME NOT NULL,
    TRGKHOA CHAR(4)
)

CREATE TABLE MONHOC
(
    MAMH VARCHAR(10) PRIMARY KEY NOT NULL,
    TENMN VARCHAR(40) NOT NULL,
    TCLT TINYINT NOT NULL,
    TCTH TINYINT NOT NULL,
    MAKHOA VARCHAR(4) FOREIGN KEY REFERENCES KHOA(MAKHOA),
)

CREATE TABLE DIEUKIEN
(
    MAMH VARCHAR(10) NOT NULL,
    MAMH_TRUOC VARCHAR(10) NOT NULL
)

ALTER TABLE DIEUKIEN ADD CONSTRAINT PK_DIEUKINE PRIMARY KEY(MAMH,MAMH_TRUOC)

CREATE TABLE GIAOVIEN
(
    MAGV CHAR(4) PRIMARY KEY NOT NULL,
    HOTEN VARCHAR(40) NOT NULL,
    HOCVI VARCHAR(10) NOT NULL,
    HOCHAM VARCHAR(10) NOT NULL,
    GIOITINH VARCHAR(3) NOT NULL,
    NGSINH SMALLDATETIME NOT NULL,
    NGVL SMALLDATETIME NOT NULL,
    HESO NUMERIC(4,2) NOT NULL,
    MUCLUONG MONEY NOT NULL,
    MAKHOA VARCHAR(4) FOREIGN KEY REFERENCES KHOA(MAKHOA)
)

ALTER TABLE KHOA ADD CONSTRAINT GV_TRUONGKHOA FOREIGN KEY (TRGKHOA) REFERENCES GIAOVIEN(MAGV) 

CREATE TABLE LOP
(
    MALOP CHAR(3) PRIMARY KEY NOT NULL,
    TENLOP VARCHAR(40) NOT NULL,
    TRGLOP CHAR(5) NOT NULL,
    SISO TINYINT NOT NULL,
    MAGVCN CHAR(4) FOREIGN KEY REFERENCES GIAOVIEN(MAGV)
)

CREATE TABLE HOCVIEN
(
    MAHV CHAR(5) PRIMARY KEY NOT NULL,
    HO VARCHAR(40) NOT NULL,
    TEN VARCHAR(10) NOT NULL,
    NGSINH SMALLDATETIME NOT NULL,
    GIOITINH VARCHAR(3) NOT NULL,
    NOISINH VARCHAR(40) NOT NULL,
    MALOP CHAR(3) FOREIGN KEY REFERENCES LOP(MALOP)
)

ALTER TABLE LOP ADD CONSTRAINT TRGLOP_LOP FOREIGN KEY (TRGLOP) REFERENCES HOCVIEN(MAHV)

CREATE TABLE GIANGDAY
(
    MALOP CHAR(3) FOREIGN KEY REFERENCES LOP(MALOP) NOT NULL,
    MAMH VARCHAR(10) FOREIGN KEY REFERENCES MONHOC(MAMH) NOT NULL,
    MAGV CHAR(4) FOREIGN KEY REFERENCES GIAOVIEN(MAGV),
    HOCKY TINYINT NOT NULL,
    NAM SMALLINT NOT NULL,
    TUNGAY SMALLDATETIME NOT NULL,
    DENNGAY SMALLDATETIME NOT NULL
)

ALTER TABLE GIANGDAY ADD CONSTRAINT PK_GIANGDAY PRIMARY KEY(MALOP, MAMH)

CREATE TABLE KETQUATHI 
(
    MAHV CHAR(5) FOREIGN KEY REFERENCES HOCVIEN(MAHV) NOT NULL,
    MAMH VARCHAR(10) FOREIGN KEY REFERENCES MONHOC(MAMH) NOT NULL,
    LANTHI TINYINT NOT NULL,
    NGTHI SMALLDATETIME NOT NULL,
    DIEM NUMERIC(4,2) NOT NULL,
    KQUA VARCHAR(10) NOT NULL
)
ALTER TABLE KETQUATHI ADD CONSTRAINT PK_KETQUATHI PRIMARY KEY(MAHV, MAMH, LANTHI)



ALTER TABLE HOCVIEN ADD GHICHU VARCHAR(100) NOT NULL
ALTER TABLE HOCVIEN ADD DIEMTB NUMERIC(4,2) NOT NULL
ALTER TABLE HOCVIEN ADD XEPLOAI VARCHAR(10) NOT NULL

ALTER TABLE GIAOVIEN ADD CONSTRAINT GV_GIOITINH CHECK (GIOITINH='Nam' OR GIOITINH='Nu')
ALTER TABLE HOCVIEN ADD CONSTRAINT HV_GIOITINH CHECK (GIOITINH='Nam' OR GIOITINH='Nu')

ALTER TABLE KETQUATHI ALTER COLUMN DIEM DECIMAL(2,2) 
ALTER TABLE KETQUATHI ADD CONSTRAINT DIEMSO_MLT CHECK (DIEM>=0 AND DIEM<=10)

ALTER TABLE HOCVIEN ALTER COLUMN DIEMTB DECIMAL(2,2) 
ALTER TABLE HOCVIEN ADD CONSTRAINT GT_DIEMTB CHECK (DIEMTB>=0 AND DIEMTB<=10)