Cơ sở dữ liệu quan hệ - Phần 6 - Bài thực hành: Tạo các bảng trong CSDL Quản lý thư viện

Script trong bài:

USE QUANLYTHUVIEN -- This part is a comment

/*
TAILIEU {
int MaTL PK
string TieuDe
int NamXB
string NhaXuatBan
string LoaiTL
}
*/

CREATE TABLE TaiLieu (
MaTL INT PRIMARY KEY,
TieuDe NVARCHAR(200) NOT NULL,
NamXB SMALLINT NOT NULL,
NhaXuatBan NVARCHAR(50) NOT NULL,
LoaiTaiLieu NVARCHAR(20) NOT NULL
)

CREATE TABLE Sach (
MaTL INT PRIMARY KEY,
SoTrang INT NOT NULL,
ISBN NVARCHAR(20),

FOREIGN KEY (MaTL) REFERENCES TaiLieu(MaTL)
)

CREATE TABLE LuanVan (
MaTL INT PRIMARY KEY REFERENCES TaiLieu(MaTL),
CapDo NVARCHAR(10) NOT NULL -- DH, THS, TS
)

CREATE TABLE DocGia (
MaDocGia INT PRIMARY KEY,
HoTen NVARCHAR(100) NOT NULL,
NgaySinh DATE,
DiaChi NVARCHAR(200) NOT NULL,
Email NVARCHAR(60) NOT NULL,
DienThoai NVARCHAR(20) NOT NULL,
LoaiDocGia NVARCHAR(10) NOT NULL -- SV, GV
)

CREATE TABLE SinhVien (
MaDocGia INT PRIMARY KEY,
MSSV NVARCHAR(20) NOT NULL,
NganhHoc NVARCHAR(20) NOT NULL,
KhoaHoc NVARCHAR(20) NOT NULL,

FOREIGN KEY (MaDocGia) REFERENCES DocGia(MaDocGia)
)

CREATE TABLE GiangVien (
MaDocGia INT PRIMARY KEY,
MaGV NVARCHAR(20) NOT NULL,
KhoaCongTac NVARCHAR(20) NOT NULL,

FOREIGN KEY (MaDocGia) REFERENCES DocGia(MaDocGia)
)

CREATE TABLE TacGia (
MaTG INT PRIMARY KEY,
TenTacGia NVARCHAR(100) NOT NULL,
)

CREATE TABLE Tacgia_TaiLieu (
MaTG INT NOT NULL,
MaTL INT NOT NULL,
VaiTro NVARCHAR(100) NOT NULL,

PRIMARY KEY (MaTG, MaTL),
FOREIGN KEY (MaTG) REFERENCES TacGia(MaTG),
FOREIGN KEY (MaTL) REFERENCES TaiLieu(MaTL)
)

CREATE TABLE BanSao (
MaBanSao INT PRIMARY KEY,
MaTL INT NOT NULL,
TinhTrang NVARCHAR(20) NOT NULL,
ViTriKe NVARCHAR(20) NOT NULL,

FOREIGN KEY (MaTL) REFERENCES TaiLieu(MaTL)
)

CREATE TABLE MuonTra (
MaMuonTra INT NOT NULL,
MaDocGia INT NOT NULL,
MaBanSao INT NOT NULL,
NgayMuon DATETIME NOT NULL,
HanTra DATETIME NOT NULL,
NgayTra DATETIME,
TienPhat FLOAT NOT NULL,

CONSTRAINT PK_MuonTra PRIMARY KEY (MaMuonTra),
CONSTRAINT FK_DocGia_MaDocGia FOREIGN KEY (MaDocGia) REFERENCES DocGia(MaDocGia),
CONSTRAINT FK_DocGia_MaBanSao FOREIGN KEY (MaBanSao) REFERENCES BanSao(MaBanSao)
)