-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathData_Full_GPM.sql
240 lines (214 loc) · 9.95 KB
/
Data_Full_GPM.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
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
CREATE DATABASE QLDT
USE QLDT
--Tao DB--
CREATE TABLE SINHVIEN (
MSSV CHAR(8) PRIMARY KEY,
TENSV VARCHAR(30) NOT NULL,
SODT VARCHAR(10) ,
LOP CHAR(10) NOT NULL,
DIACHI CHAR(50) NOT NULL
);
CREATE TABLE DETAI (
MSDT CHAR(6) PRIMARY KEY,
TENDT VARCHAR(30) NOT NULL
);
CREATE TABLE SV_DETAI (
MSSV CHAR (8),
MSDT CHAR(6),
PRIMARY KEY (MSSV, MSDT)
);
CREATE TABLE GIAOVIEN (
MSGV INT PRIMARY KEY,
TENGV VARCHAR(30) NOT NULL,
DIACHI VARCHAR(50) NOT NULL,
SODT VARCHAR(10) NOT NULL,
MSHH INT ,
NAMHH DATE NOT NULL
);
ALTER TABLE GIAOVIEN
MODIFY NAMHH YEAR;
CREATE TABLE HOCVI (
MSHV INT PRIMARY KEY,
TENHV VARCHAR(20) NOT NULL
);
CREATE TABLE CHUYENNGANH (
MSCN INT PRIMARY KEY,
TENCN VARCHAR(30) NOT NULL
);
CREATE TABLE GV_HV_CN (
MSGV INT,
MSHV INT,
MSCN INT,
NAM DATE NOT NULL,
PRIMARY KEY(MSGV,MSHV,MSCN)
);
ALTER TABLE GV_HV_CN
MODIFY NAM YEAR;
CREATE TABLE HOCHAM (
MSHH INT PRIMARY KEY,
TENHH VARCHAR(20) NOT NULL
);
CREATE TABLE GV_HDDT (
MSGV INT ,
MSDT CHAR(6),
DIEM FLOAT NOT NULL,
PRIMARY KEY(MSGV, MSDT)
);
CREATE TABLE GV_PBDT (
MSGV INT ,
MSDT CHAR(6),
DIEM FLOAT NOT NULL,
PRIMARY KEY (MSGV, MSDT)
);
CREATE TABLE GV_UVDT (
MSGV INT ,
MSDT CHAR(6),
DIEM FLOAT NOT NULL,
PRIMARY KEY (MSGV, MSDT)
);
CREATE TABLE HOIDONG (
MSHD INT PRIMARY KEY,
PHONG INT,
TGBD DATE,
NGAYHD DATE NOT NULL,
TINGTRANG NVARCHAR(30) NOT NULL,
MSGV INT
);
ALTER TABLE HOIDONG
MODIFY TGBD TIME;
ALTER TABLE HOIDONG
MODIFY NGAYHD DATE;
CREATE TABLE HOIDONG_GV (
MSHD INT,
MSGV INT,
PRIMARY KEY(MSHD, MSGV)
);
CREATE TABLE HOIDONG_DT (
MSHD INT,
MSDT CHAR(6),
QUYETDINH CHAR(10),
PRIMARY KEY(MSHD, MSDT)
);
ALTER TABLE SV_DETAI
ADD CONSTRAINT FK_SV_DETAI_MSSV FOREIGN KEY (MSSV) REFERENCES SINHVIEN(MSSV)
ALTER TABLE SV_DETAI
ADD CONSTRAINT FK_SV_DETAI_MSDT FOREIGN KEY (MSDT) REFERENCES DETAI(MSDT)
ALTER TABLE GIAOVIEN
ADD CONSTRAINT FK_GIAOVIEN_MSHH FOREIGN KEY (MSHH) REFERENCES HOCHAM(MSHH)
ALTER TABLE GV_HV_CN
ADD CONSTRAINT FK_GV_HV_CN_MSGV FOREIGN KEY (MSGV) REFERENCES GIAOVIEN(MSGV)
ALTER TABLE GV_HV_CN
ADD CONSTRAINT FK_GV_HV_CN_MSHV FOREIGN KEY (MSHV) REFERENCES HOCVI(MSHV)
ALTER TABLE GV_HDDT
ADD CONSTRAINT FK_GV_HDDT_MSGV FOREIGN KEY (MSGV) REFERENCES GIAOVIEN(MSGV)
ALTER TABLE GV_HDDT
ADD CONSTRAINT FK_GV_HDDT_MADT FOREIGN KEY (MSDT) REFERENCES DETAI(MSDT)
ALTER TABLE GV_PBDT
ADD CONSTRAINT FK_GV_PBDT_MSGV FOREIGN KEY (MSGV) REFERENCES GIAOVIEN(MSGV)
ALTER TABLE GV_PBDT
ADD CONSTRAINT FK_GV_PBDT_MSDT FOREIGN KEY (MSDT) REFERENCES DETAI(MSDT)
ALTER TABLE GV_UVDT
ADD CONSTRAINT FK_GV_UVDT_MSGV FOREIGN KEY (MSGV) REFERENCES GIAOVIEN(MSGV)
ALTER TABLE GV_UVDT
ADD CONSTRAINT FK_GV_UVDT_MSDT FOREIGN KEY (MSDT) REFERENCES DETAI(MSDT)
ALTER TABLE HOIDONG
ADD CONSTRAINT FK_HOIDONG_MSGV FOREIGN KEY (MSGV) REFERENCES GIAOVIEN(MSGV)
ALTER TABLE HOIDONG_GV
ADD CONSTRAINT FK_HOIDONG_GV_MSHD FOREIGN KEY (MSHD) REFERENCES HOIDONG(MSHD)
ALTER TABLE HOIDONG_GV
ADD CONSTRAINT FK_HOIDONG_GV_MSGV FOREIGN KEY (MSGV) REFERENCES GIAOVIEN(MSGV)
ALTER TABLE HOIDONG_DT
ADD CONSTRAINT FK_HOIDONG_DT_MSHD FOREIGN KEY (MSHD) REFERENCES HOIDONG(MSHD)
ALTER TABLE HOIDONG_DT
ADD CONSTRAINT FK_HOIDONG_DT_MSDT FOREIGN KEY (MSDT) REFERENCES DETAI(MSDT)
SET DATEFORMAT DMY
INSERT INTO SINHVIEN (MSSV, TENSV, SODT, LOP, DIACHI) VALUES ('13520001', 'Nguyen Van An', '0906762255', 'SE103.U32', 'Thu Duc');
INSERT INTO SINHVIEN (MSSV, TENSV, SODT, LOP, DIACHI) VALUES ('13520002', 'Phan Tan Dat', '0975672350', 'IE204.T21', 'Quan 1');
INSERT INTO SINHVIEN (MSSV, TENSV, SODT, LOP, DIACHI) VALUES ('13520003', 'Nguyen Anh Hai', '0947578688', 'IE205.R12', 'Quan 9');
INSERT INTO SINHVIEN (MSSV, TENSV, SODT, LOP, DIACHI) VALUES ('13520004', 'Pham Tai', '0956757869', 'IE202.A22', 'Quan 1');
INSERT INTO SINHVIEN (MSSV, TENSV, SODT, LOP, DIACHI) VALUES ('13520005', 'Le Thuy Hang', '0976668688', 'SE304.E22', 'Thu Duc');
INSERT INTO SINHVIEN (MSSV, TENSV, SODT, LOP, DIACHI) VALUES ('13520006', 'Ung Hong An', '0957475898', 'IE208.F33', 'Quan 2');
INSERT INTO DETAI (MSDT, TENDT) VALUES ('97001', 'Quan ly thu vien');
INSERT INTO DETAI (MSDT, TENDT) VALUES ('97002', 'Nhan dang van tay');
INSERT INTO DETAI (MSDT, TENDT) VALUES ('97003', 'Ban dau gia tren mang');
INSERT INTO DETAI (MSDT, TENDT) VALUES ('97004', 'Quan ly sieu thi');
INSERT INTO DETAI (MSDT, TENDT) VALUES ('97005', 'Xu ly anh');
INSERT INTO DETAI (MSDT, TENDT) VALUES ('97006', 'He giai toan thong minh');
INSERT INTO SV_DETAI (MSSV, MSDT) VALUES ('13520001', '97004');
INSERT INTO SV_DETAI (MSSV, MSDT) VALUES ('13520002', '97005');
INSERT INTO SV_DETAI (MSSV, MSDT) VALUES ('13520003', '97001');
INSERT INTO SV_DETAI (MSSV, MSDT) VALUES ('13520004', '97002');
INSERT INTO SV_DETAI (MSSV, MSDT) VALUES ('13520005', '97003');
INSERT INTO SV_DETAI (MSSV, MSDT) VALUES ('13520006', '97005');
INSERT INTO HOCHAM (MSHH, TENHH) VALUES ('1', 'PHO GIAO SU');
INSERT INTO HOCHAM (MSHH, TENHH) VALUES ('2', 'GIAO SU');
INSERT INTO GIAOVIEN (MSGV, TENGV, DIACHI, SODT, MSHH, NAMHH) VALUES ('00201', 'Tran Trung', 'Ben Tre', '35353535','1', '1996');
INSERT INTO GIAOVIEN (MSGV, TENGV, DIACHI, SODT, MSHH, NAMHH) VALUES ('00202', 'Nguyen Van An', 'Tien Giang', '67868688','1', '1996');
INSERT INTO GIAOVIEN (MSGV, TENGV, DIACHI, SODT, MSHH, NAMHH) VALUES ('00203', 'Tran Thu Trang', 'Can Tho', '74758687','1', '1996');
INSERT INTO GIAOVIEN (MSGV, TENGV, DIACHI, SODT, MSHH, NAMHH) VALUES ('00204', 'Nguyen Thi Loan', 'TP.HCM', '56575868','2', '2005');
INSERT INTO GIAOVIEN (MSGV, TENGV, DIACHI, SODT, MSHH, NAMHH) VALUES ('00205', 'Chu Tien', 'Ha Noi', '46466646','2', '2005');
INSERT INTO HOCVI (MSHV, TENHV) VALUES ('1', 'Ky su');
INSERT INTO HOCVI (MSHV, TENHV) VALUES ('2', 'Cu nhan');
INSERT INTO HOCVI (MSHV, TENHV) VALUES ('3', 'Thac si');
INSERT INTO HOCVI (MSHV, TENHV) VALUES ('4', 'Tien si');
INSERT INTO HOCVI (MSHV, TENHV) VALUES ('5', 'Tien si Khoa hoc');
INSERT INTO CHUYENNGANH (MSCN, TENCN) VALUES ('1', 'Cong nghe Web');
INSERT INTO CHUYENNGANH (MSCN, TENCN) VALUES ('2', 'Mang xa hoi');
INSERT INTO CHUYENNGANH (MSCN, TENCN) VALUES ('3', 'Quan ly CNTT');
INSERT INTO CHUYENNGANH (MSCN, TENCN) VALUES ('4', 'GIS');
INSERT INTO GV_HV_CN (MSGV, MSHV, MSCN, NAM) VALUES ('00201', '1', '1', '2013');
INSERT INTO GV_HV_CN (MSGV, MSHV, MSCN, NAM) VALUES ('00201', '1', '2', '2013');
INSERT INTO GV_HV_CN (MSGV, MSHV, MSCN, NAM) VALUES ('00201', '2', '1', '2014');
INSERT INTO GV_HV_CN (MSGV, MSHV, MSCN, NAM) VALUES ('00202', '3', '2', '2013');
INSERT INTO GV_HV_CN (MSGV, MSHV, MSCN, NAM) VALUES ('00203', '2', '4', '2014');
INSERT INTO GV_HV_CN (MSGV, MSHV, MSCN, NAM) VALUES ('00204', '3', '2', '2014');
INSERT INTO GV_HDDT (MSGV, MSDT, DIEM) VALUES ('00201', '97001', '8');
INSERT INTO GV_HDDT (MSGV, MSDT, DIEM) VALUES ('00202', '97002', '7');
INSERT INTO GV_HDDT (MSGV, MSDT, DIEM) VALUES ('00205', '97001', '9');
INSERT INTO GV_HDDT (MSGV, MSDT, DIEM) VALUES ('00204', '97004', '7');
INSERT INTO GV_HDDT (MSGV, MSDT, DIEM) VALUES ('00203', '97005', '9');
INSERT INTO GV_PBDT (MSGV, MSDT, DIEM) VALUES ('00201', '97005', '8');
INSERT INTO GV_PBDT (MSGV, MSDT, DIEM) VALUES ('00202', '97001', '7');
INSERT INTO GV_PBDT (MSGV, MSDT, DIEM) VALUES ('00205', '97004', '9');
INSERT INTO GV_PBDT (MSGV, MSDT, DIEM) VALUES ('00204', '97003', '7');
INSERT INTO GV_PBDT (MSGV, MSDT, DIEM) VALUES ('00203', '97002', '9');
INSERT INTO GV_UVDT(MSGV, MSDT, DIEM) VALUES ('00205', '97005', '8');
INSERT INTO GV_UVDT(MSGV, MSDT, DIEM) VALUES ('00202', '97005', '7');
INSERT INTO GV_UVDT(MSGV, MSDT, DIEM) VALUES ('00204', '97005', '9');
INSERT INTO GV_UVDT(MSGV, MSDT, DIEM) VALUES ('00203', '97001', '7');
INSERT INTO GV_UVDT(MSGV, MSDT, DIEM) VALUES ('00204', '97001', '9');
INSERT INTO GV_UVDT(MSGV, MSDT, DIEM) VALUES ('00205', '97001', '8');
INSERT INTO GV_UVDT(MSGV, MSDT, DIEM) VALUES ('00203', '97003', '7');
INSERT INTO GV_UVDT(MSGV, MSDT, DIEM) VALUES ('00201', '97003', '9');
INSERT INTO GV_UVDT(MSGV, MSDT, DIEM) VALUES ('00202', '97003', '7');
INSERT INTO GV_UVDT(MSGV, MSDT, DIEM) VALUES ('00201', '97004', '9');
INSERT INTO GV_UVDT(MSGV, MSDT, DIEM) VALUES ('00202', '97004', '8');
INSERT INTO GV_UVDT(MSGV, MSDT, DIEM) VALUES ('00203', '97004', '7');
INSERT INTO GV_UVDT(MSGV, MSDT, DIEM) VALUES ('00201', '97002', '9');
INSERT INTO GV_UVDT(MSGV, MSDT, DIEM) VALUES ('00204', '97002', '7');
INSERT INTO GV_UVDT(MSGV, MSDT, DIEM) VALUES ('00205', '97002', '9');
INSERT INTO GV_UVDT(MSGV, MSDT, DIEM) VALUES ('00201', '97006', '9');
INSERT INTO GV_UVDT(MSGV, MSDT, DIEM) VALUES ('00202', '97006', '7');
INSERT INTO GV_UVDT(MSGV, MSDT, DIEM) VALUES ('00204', '97006', '9');
INSERT INTO HOIDONG (MSHD, PHONG, TGBD, NGAYHD, TINGTRANG, MSGV) VALUES ('1', '002', '07:00', '2014-11-29', 'That', '00201');
INSERT INTO HOIDONG (MSHD, PHONG, TGBD, NGAYHD, TINGTRANG, MSGV) VALUES ('2', '102', '07:00', '2014-12-05', 'That', '00202');
INSERT INTO HOIDONG (MSHD, PHONG, TGBD, NGAYHD, TINGTRANG, MSGV) VALUES ('3', '003', '08:00', '2014-12-06', 'That', '00203');
INSERT INTO HOIDONG_GV (MSHD, MSGV) VALUES ('1', '00201');
INSERT INTO HOIDONG_GV (MSHD, MSGV) VALUES ('1', '00202');
INSERT INTO HOIDONG_GV (MSHD, MSGV) VALUES ('1', '00203');
INSERT INTO HOIDONG_GV (MSHD, MSGV) VALUES ('1', '00204');
INSERT INTO HOIDONG_GV (MSHD, MSGV) VALUES ('2', '00203');
INSERT INTO HOIDONG_GV (MSHD, MSGV) VALUES ('2', '00202');
INSERT INTO HOIDONG_GV (MSHD, MSGV) VALUES ('2', '00205');
INSERT INTO HOIDONG_GV (MSHD, MSGV) VALUES ('2', '00204');
INSERT INTO HOIDONG_GV (MSHD, MSGV) VALUES ('3', '00201');
INSERT INTO HOIDONG_GV (MSHD, MSGV) VALUES ('3', '00202');
INSERT INTO HOIDONG_GV (MSHD, MSGV) VALUES ('3', '00203');
INSERT INTO HOIDONG_GV (MSHD, MSGV) VALUES ('3', '00204');
INSERT INTO HOIDONG_DT (MSHD, MSDT, QUYETDINH) VALUES ('1', '97001', 'Duoc');
INSERT INTO HOIDONG_DT (MSHD, MSDT, QUYETDINH) VALUES ('1', '97002', 'Duoc');
INSERT INTO HOIDONG_DT (MSHD, MSDT, QUYETDINH) VALUES ('2', '97001', 'Khong');
INSERT INTO HOIDONG_DT (MSHD, MSDT, QUYETDINH) VALUES ('2', '97004', 'Khong');
INSERT INTO HOIDONG_DT (MSHD, MSDT, QUYETDINH) VALUES ('2', '97005', 'Duoc');
INSERT INTO HOIDONG_DT (MSHD, MSDT, QUYETDINH) VALUES ('3', '97001', 'Khong');
INSERT INTO HOIDONG_DT (MSHD, MSDT, QUYETDINH) VALUES ('3', '97002', 'Duoc');