-
Notifications
You must be signed in to change notification settings - Fork 0
/
createDBQLTV.sql
203 lines (171 loc) · 6.32 KB
/
createDBQLTV.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
USE [master]
GO
WHILE EXISTS(select NULL from sys.databases where name='LIBMANAGEMENT')
BEGIN
DECLARE @SQL varchar(max)
SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(N'LIBMANAGEMENT') AND SPId <> @@SPId
EXEC(@SQL)
DROP DATABASE [LIBMANAGEMENT]
END
CREATE DATABASE [LIBMANAGEMENT]
GO
USE [LIBMANAGEMENT]
GO
CREATE TABLE SACH
(
MaSach varchar(30) PRIMARY KEY,
MaDauSach varchar(30),
NgayNhap datetime,
NXB varchar(50),
NamXuatBan datetime,
Gia money,
DaMuon bit
)
CREATE TABLE TACGIA
(
MaTacGia varchar(30) PRIMARY KEY,
HoTen varchar (50),
NgaySinh datetime,
)
CREATE TABLE THEDOCGIA
(
MaThe INT PRIMARY KEY,
HoTen varchar(50),
NgaySinh date,
DiaChi varchar(50),
Email varchar(30),
NgayLapThe datetime,
LoaiDocGia varchar (30)
)
CREATE TABLE LOAIDOCGIA
(
MaLoai varchar(30) PRIMARY KEY,
TenLoai varchar(30)
)
CREATE TABLE THELOAISACH
(
MaTheLoai varchar(30) PRIMARY KEY,
TenTheLoai varchar(50)
)
CREATE TABLE DAUSACH
(
MaDauSach varchar(30) PRIMARY KEY,
TenDauSach varchar(50),
TongSoLuong int,
SoLuongDaMuon int,
SoLuongConLai int
)
CREATE TABLE DAUSACH_TACGIA
(
MaDauSach varchar(30),
MaTacGia varchar(30),
PRIMARY KEY (MaDauSach, MaTacGia)
)
CREATE TABLE DAUSACH_THELOAI
(
MaDauSach varchar(30),
MaTheLoai varchar(30),
PRIMARY KEY (MaDauSach, MaTheLoai)
)
CREATE TABLE MUONSACH
(
MaThe INT,
MaNguoiDung varchar(30),
MaSach varchar(30),
MaMuonSach varchar(30),
NgayMuon datetime,
NgayTra datetime,
NgayTraThucTe datetime,
PRIMARY KEY (MaThe, MaNguoiDung, MaSach, MaMuonSach)
)
CREATE TABLE THAMSO
(
TuoiToiDaDocGia int,
TuoiToiThieuDocGia int,
ThoiHanThe int,
SoTheLoaiToiDa int,
SoTacGiaToiDa int,
NamXuatBanToiDa int, /*Give data about maxium year of book can be received */
SoSachMuonToiDa int,
SoNgayMuonToiDa int,
)
/*PK FOR TABLES*/
ALTER TABLE SACH ADD
CONSTRAINT SACH_MaDauSach_FK FOREIGN KEY (MaDauSach)
REFERENCES DAUSACH (MaDauSach)
ALTER TABLE DAUSACH_TACGIA ADD
CONSTRAINT DAUSACH_TACGIA_MaDauSach_FK FOREIGN KEY (MaDauSach)
REFERENCES DAUSACH (MaDauSach)
ALTER TABLE DAUSACH_TACGIA ADD
CONSTRAINT DAUSACH_TACGIA_MaTacGia_FK FOREIGN KEY (MaTacGia)
REFERENCES TACGIA (MaTacGia)
ALTER TABLE DAUSACH_THELOAI ADD
CONSTRAINT DAUSACH_THELOAI_MaDauSach_FK FOREIGN KEY (MaDauSach)
REFERENCES DAUSACH (MaDauSach)
ALTER TABLE DAUSACH_THELOAI ADD
CONSTRAINT DAUSACH_THELOAI_MaTheLoai_FK FOREIGN KEY (MaTheLoai)
REFERENCES THELOAISACH (MaTheLoai)
ALTER TABLE MUONSACH ADD
CONSTRAINT MUONSACH_MaThe_FK FOREIGN KEY (MaThe)
REFERENCES THEDOCGIA (MaThe)
ALTER TABLE MUONSACH ADD
CONSTRAINT MUONSACH_MaSach_FK FOREIGN KEY (MaSach)
REFERENCES SACH (MaSach)
/*INIT THAMSO TABLE */
INSERT INTO THAMSO VALUES ('55', '18', '6', '3', '100', '8', '5', '4')
/*INIT THELOAISACH*/
INSERT INTO THELOAISACH VALUES ('THELOAI01','KINH DI')
INSERT INTO THELOAISACH VALUES ('THELOAI02','HAI HUOC')
INSERT INTO THELOAISACH VALUES ('THELOAI03','LANG MAN')
/*INIT TACGIA*/
INSERT INTO TACGIA VALUES ('TACGIA01','NGUYEN VAN A','10/4/1999')
INSERT INTO TACGIA VALUES ('TACGIA02','TRAN VAN B','1/1/1999')
INSERT INTO TACGIA VALUES ('TACGIA03','NGUYEN TRAN VAN C','3/3/1999')
/*INIT DAUSACH*/
INSERT INTO DAUSACH(MaDauSach,TenDauSach) VALUES ('DS01', 'BO GIA')
INSERT INTO DAUSACH(MaDauSach,TenDauSach) VALUES ('DS02', 'NHA GIA KIM')
INSERT INTO DAUSACH(MaDauSach,TenDauSach) VALUES ('DS03', 'GIET CON CHIM NHAN')
INSERT INTO DAUSACH(MaDauSach,TenDauSach) VALUES ('DS04', 'CHU CHO BETO')
INSERT INTO DAUSACH(MaDauSach,TenDauSach) VALUES ('DS05', 'GIO HOA HONG')
INSERT INTO DAUSACH(MaDauSach,TenDauSach) VALUES ('DS06', 'HAI SO PHAN')
INSERT INTO DAUSACH(MaDauSach,TenDauSach) VALUES ('DS07', '7 HABITS')
/*INIT DAUSACH_THELOAI*/
INSERT INTO DAUSACH_THELOAI VALUES ('DS01','THELOAI01')
INSERT INTO DAUSACH_THELOAI VALUES ('DS02','THELOAI01')
INSERT INTO DAUSACH_THELOAI VALUES ('DS03','THELOAI01')
INSERT INTO DAUSACH_THELOAI VALUES ('DS04','THELOAI01')
INSERT INTO DAUSACH_THELOAI VALUES ('DS05','THELOAI02')
INSERT INTO DAUSACH_THELOAI VALUES ('DS06','THELOAI02')
INSERT INTO DAUSACH_THELOAI VALUES ('DS07','THELOAI03')
/*INIT DAUSACH_TACGIA*/
INSERT INTO DAUSACH_TACGIA VALUES ('DS01','TACGIA01')
INSERT INTO DAUSACH_TACGIA VALUES ('DS02','TACGIA01')
INSERT INTO DAUSACH_TACGIA VALUES ('DS03','TACGIA02')
INSERT INTO DAUSACH_TACGIA VALUES ('DS04','TACGIA02')
INSERT INTO DAUSACH_TACGIA VALUES ('DS05','TACGIA02')
INSERT INTO DAUSACH_TACGIA VALUES ('DS06','TACGIA03')
INSERT INTO DAUSACH_TACGIA VALUES ('DS07','TACGIA03')
/*INIT SACH*/
INSERT INTO SACH VALUES ('SACH01','DS01','5/7/2019','TRI THUC','5/7/2015','6.00','0')
INSERT INTO SACH VALUES ('SACH02','DS01','5/7/2019','KIM DONG','5/7/2015','7.00','0')
INSERT INTO SACH VALUES ('SACH03','DS01','5/7/2019','TRI THUC','5/7/2015','7.00','0')
INSERT INTO SACH VALUES ('SACH04','DS01','5/7/2019','TRI THUC','5/7/2015','6.00','0')
INSERT INTO SACH VALUES ('SACH05','DS01','5/7/2019','KIM DONG','5/7/2015','6.00','0')
INSERT INTO SACH VALUES ('SACH06','DS02','5/7/2019','TRI THUC','5/7/2015','8.00','0')
INSERT INTO SACH VALUES ('SACH07','DS02','5/7/2019','TRI THUC','5/7/2015','8.00','0')
INSERT INTO SACH VALUES ('SACH08','DS02','5/7/2019','TUOI TRE','5/7/2015','10.00','0')
INSERT INTO SACH VALUES ('SACH09','DS03','5/7/2019','TRI THUC','5/7/2015','10.00','0')
INSERT INTO SACH VALUES ('SACH010','DS03','5/7/2019','TUOI TRE','5/7/2015','12.00','0')
INSERT INTO SACH VALUES ('SACH011','DS04','5/7/2019','TRI THUC','5/7/2015','14.00','0')
INSERT INTO SACH VALUES ('SACH012','DS05','5/7/2019','KIM DONG','5/7/2015','7.00','0')
INSERT INTO SACH VALUES ('SACH013','DS06','5/7/2019','TUOI TRE','5/7/2015','5.00','0')
INSERT INTO SACH VALUES ('SACH014','DS07','5/7/2019','TRI THUC','5/7/2015','3.00','0')
/*INIT THEDOCGIA*/
INSERT INTO THEDOCGIA VALUES (01,'VO VAN A','10/4/1999','56 PHUC LONG','11@gmail.com','5/7/2019','X')
INSERT INTO THEDOCGIA VALUES (02,'VO VAN B','10/4/1999','QUAN 2','12@gmail.com','5/7/2019','Y')
INSERT INTO THEDOCGIA VALUES (03,'VO VAN C','10/4/1999','DA NANG','13@gmail.com','5/7/2019','X')
INSERT INTO THEDOCGIA VALUES (04,'NGUYEN THANH A','1/1/1999','QUAN 12','14@gmail.com','5/7/2019','Y')
INSERT INTO THEDOCGIA VALUES (05,'NGUYEN THANH B','1/1/1999','12 TRUONG CHINH','15@gmail.com','5/7/2019','X')
INSERT INTO THEDOCGIA VALUES (06,'NGUYEN THANH C','1/1/1999','HOC MON','16@gmail.com','5/7/2019','Y')