EpqJnBnM

1use master
2go 
3if exists (select name from sysdatabases where name='QuanLyThuVien')
4		drop database QuanLyThuVien
5go
6create database QuanLyThuVien
7go
8use QuanLyThuVien
9go
10
11create table DocGia (
12	MaDocGia int not null primary key,
13	HoTen varchar(255),
14	NgaySinh date
15)
16
17create table LoaiSach (
18	MaLoaiSach int not null primary key,
19	TenLoaiSach varchar(255)
20)
21
22create table DauSach (
23	MaDauSach int not null,
24	TuaSach varchar(255),
25	TacGia varchar(255),
26	NgayXuatBan date,
27	SoLuong int,
28	GiaBan money,
29	MaLoaiSach int not null, 
30	primary key (MaDauSach)
31)
32
33create table CuonSach (
34	MaCuonSach int not null,
35	TinhTrang varchar(255),
36	MaDauSach int not null,
37	primary key (MaCuonSach, MaDauSach)
38)
39
40create table DangKy (
41	NgayDangKy date,
42	MaDocGia int not null,
43	MaDauSach int not null,
44	primary key (MaDocGia, MaDauSach)
45)
46
47create table MuonSach (
48	MaCuonSach int not null,
49	MaDocGia int not null,
50	MaDauSach int not null,
51	NgayMuon date,
52	NgayHetHan date,
53	primary key (MaCuonSach, MaDocGia, MaDauSach)
54)
55
56alter table DauSach add constraint FK_LoaiSach_DauSach
57foreign key (MaLoaiSach) references LoaiSach(MaLoaiSach)
58
59alter table CuonSach add constraint FK_DauSach_CuonSach
60foreign key (MaDauSach) references DauSach(MaDauSach)
61
62alter table DangKy add constraint FK_DauSach_DangKy
63foreign key (MaDauSach) references DauSach(MaDauSach)
64
65alter table DangKy add constraint FK_DocGia_DangKy
66foreign key (MaDocGia) references DocGia(MaDocGia)
67
68alter table MuonSach add constraint FK_DocGia_MuonSach
69foreign key (MaDocGia) references DocGia(MaDocGia)
70
71alter table MuonSach add constraint FK_DauSach_MuonSach
72foreign key (MaDauSach) references DauSach(MaDauSach)
73
74alter table MuonSach add constraint FK_CuonSach_MuonSach
75foreign key (MaCuonSach) references CuonSach(MaCuonSach)