-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathDatabase_Nhom2_1810374_1711861_1810555_1814801.sql
305 lines (276 loc) · 13.4 KB
/
Database_Nhom2_1810374_1711861_1810555_1814801.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
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
SET FOREIGN_KEY_CHECKS =0;
drop schema if exists Publication;
create schema Publication;
Use Publication;
SET FOREIGN_KEY_CHECKS =1;
-- ddl
create table scientist(
id char(10),
last_name varchar(255) not null,
middle_name varchar(255),
first_name varchar(255) not null,
phone char(11),
address varchar(255),
`organization` varchar(255),
job varchar(255),
primary key(id)
);
-- table account is used for web application
create table `account`(
id char(10) primary key,
username varchar(20),
`password` varchar(20),
foreign key (id) references scientist(id) ON DELETE CASCADE ON UPDATE CASCADE
);
create table book(
isbn char(13),
`name` varchar(255) not null,
publish_year year,
total_page int,
publisher varchar(255),
primary key(isbn)
);
create table reviewer(
id char(10),
business_email varchar(255),
personal_email varchar(255),
collaboration_date date,
qualification varchar(255),
primary key(id),
foreign key(id) references scientist(id) ON DELETE CASCADE ON UPDATE CASCADE
);
create table editor(
id char(10),
primary key(id),
foreign key(id) references scientist(id) ON DELETE CASCADE ON UPDATE CASCADE
);
create table author(
id char(10),
author_email varchar(255),
primary key(id),
foreign key(id) references scientist(id) ON DELETE CASCADE ON UPDATE CASCADE
);
create table paper(
paper_id char(10),
title varchar(255) not null,
summary varchar(255),
paper_file varchar(255),
post_date date,
`status` varchar(255) check (`status` in ('in review', 'response review', 'complete review', 'publishing', 'posted')),
after_review_result varchar(255),
final_result varchar(255) check(final_result in('rejection','minor revision','acceptance','major revision')),
announce_date date,
editor_id char(10) not null,
contact_author_id char(10) not null,
note_for_author varchar(255),
primary key(paper_id),
foreign key(editor_id) references editor(id) ON DELETE CASCADE ON UPDATE CASCADE,
foreign key(contact_author_id) references author(id) ON DELETE CASCADE ON UPDATE CASCADE
);
create table research_paper(
paper_id char(10),
research_page_number int not null check(research_page_number>=10 and research_page_number<=20),
primary key(paper_id),
foreign key(paper_id) references paper(paper_id) ON DELETE CASCADE ON UPDATE CASCADE
);
create table general_paper(
paper_id char(10),
general_page_number int not null check(general_page_number>=3 and general_page_number<=10),
primary key(paper_id),
foreign key(paper_id) references paper(paper_id) ON DELETE CASCADE ON UPDATE CASCADE
);
create table review_paper(
paper_id char(10),
review_page_number int not null check(review_page_number>=3 and review_page_number<=6),
isbn char(13) not null,
primary key(paper_id),
foreign key(paper_id) references paper(paper_id) ON DELETE CASCADE ON UPDATE CASCADE,
foreign key(isbn) references book(isbn) ON DELETE CASCADE ON UPDATE CASCADE
);
create table published_paper(
doi varchar(255),
published_type varchar(255) not null check (published_type in ('traditional','open access')),
paper_id char(10),
primary key(doi),
foreign key(paper_id) references paper(paper_id) ON DELETE CASCADE ON UPDATE CASCADE
);
create table author_names(
isbn char(13),
author_name varchar(255),
primary key(isbn,author_name),
foreign key(isbn) references book(isbn) ON DELETE CASCADE ON UPDATE CASCADE
);
create table keywords(
paper_id char(10),
keyword varchar(255),
primary key(paper_id, keyword),
foreign key(paper_id) references paper(paper_id) ON DELETE CASCADE ON UPDATE CASCADE
);
create table criteria(
criteria_id char(10),
content varchar(255),
primary key(criteria_id)
);
create table rating_level(
criteria_id char(10),
`description` varchar(255),
score float,
unique(criteria_id, score),
primary key(criteria_id,`description`,score),
foreign key(criteria_id) references criteria(criteria_id) ON DELETE CASCADE ON UPDATE CASCADE
);
create table evaluate(
paper_id char(10),
reviewer_id char(10),
criteria_id char(10),
primary key(paper_id,reviewer_id,criteria_id),
foreign key(paper_id) references paper(paper_id) ON DELETE CASCADE ON UPDATE CASCADE,
foreign key(reviewer_id) references reviewer(id) ON DELETE CASCADE ON UPDATE CASCADE,
foreign key(criteria_id) references criteria(criteria_id) ON DELETE CASCADE ON UPDATE CASCADE
);
create table assign(
paper_id char(10),
reviewer_id char(10),
editor_id char(10),
assign_date date,
review_expired_date date,
constraint check(assign_date<review_expired_date),
primary key(paper_id,reviewer_id),
foreign key(paper_id) references paper(paper_id) ON DELETE CASCADE ON UPDATE CASCADE,
foreign key(reviewer_id) references reviewer(id) ON DELETE CASCADE ON UPDATE CASCADE,
foreign key(editor_id) references editor(id) ON DELETE CASCADE ON UPDATE CASCADE
);
create table review(
paper_id char(10),
reviewer_id char(10),
note_for_author varchar(255),
note_for_editor varchar(255),
review_result varchar(255),
primary key(paper_id,reviewer_id),
foreign key(paper_id) references paper(paper_id) ON DELETE CASCADE ON UPDATE CASCADE,
foreign key(reviewer_id) references reviewer(id) ON DELETE CASCADE ON UPDATE CASCADE
);
create table `write`(
paper_id char(10),
author_id char(10),
primary key(paper_id,author_id),
foreign key(paper_id) references paper(paper_id) ON DELETE CASCADE ON UPDATE CASCADE,
foreign key(author_id) references author(id) ON DELETE CASCADE ON UPDATE CASCADE
);
-- ADD DATA
insert into scientist
values ('0000000001','Pham','Van','An','0854662611','Quan 1','ABC Company','Manager'),
('0000000002','Pham','Van','Ba','0854662612','Quan 2','BCD Company','Manager'),
('0000000003','Pham','Van','Cang','0854662613','Quan 3','Group2 Publication','Employee'),
('0000000004','Pham','Van','Dan','0854662614','Quan 4','Group2 Publication','Employee'),
('0000000005','Nguyen','Thi','Na','0854662615','Quan 5','Group2 Publication','Employee'),
('0000000006','Nguyen','Thi','Van','0854662616','Quan Thu Duc','Group2 Publication','Employee'),
('0000000007','Nguyen','Thi','Giang','0854662617','Quan Binh Thanh','Group2 Publication','Editor'),
('0000000008','Nguyen','Thi','Hop','0854662618','Quan Go Vap','Group2 Publication','Editor');
INSERT INTO account VALUES('0000000001','hello1','hello1');
INSERT INTO account VALUES('0000000002','hello2','hello2');
INSERT INTO account VALUES('0000000003','hello3','hello3');
INSERT INTO account VALUES('0000000004','hello4','hello4');
INSERT INTO account VALUES('0000000005','hello5','hello5');
INSERT INTO account VALUES('0000000006','hello6','hello6');
INSERT INTO account VALUES('0000000007','hello7','hello7');
INSERT INTO account VALUES('0000000008','hello8','hello8');
insert into author
values ('0000000001','an@gmail.com'),
('0000000002','ba@gmail.com'),
('0000000003','cang1@gmail.com');
insert into reviewer
values ('0000000003','cang1@gmail.com','cang2@gmail.com','2015-01-02','Graduated from BK University'),
('0000000004','dan1@gmail.com','dan2@gmail.com','2015-01-03','Graduated from KHTN University'),
('0000000005','na1@gmail.com','na2@gmail.com','2015-01-04','Graduated from XHNV University'),
('0000000006','van1@gmail.com','van2@gmail.com','2015-01-05','Graduated from QT University');
insert into editor
values ('0000000005'), ('0000000006'), ('0000000007'), ('0000000008');
insert into paper(paper_id,title,summary,paper_file,editor_id,contact_author_id,post_date,final_result,`status`)
values ('0000000001','Machine Learning of Tensorflow',
'Quantile regression is an indispensable tool for statistical learning. Traditional quantile regression methods consider vector-valued covariates and estimate the corresponding coefficient vector.',
'https://www.jmlr.org/papers/volume21/19-585/19-585.pdf','0000000005','0000000001','2020-12-5','rejection','in review'),
('0000000002','Healthy General','The aim of this study was to compare the defined indicators of tuberculosis (TB) control program in
the intervention and control prisons.','https://www.emerald.com/insight/content/doi/10.1108/JHR-04-2019-0074/full/pdf?title=comparison-of-tuberculosis-indicators-after-implementation-of-the-clinical-protocol-for-tuberculosis-and-hiv-management-in-iranian-prisons-a-quasi-experimental-study','0000000006','0000000002','2019-10-29','acceptance','posted'),
('0000000003','Review of Hello World','There has been a flowering of scholarly interest in the literature review as a research method in the information
systems discipline','https://core.ac.uk/download/pdf/301376904.pdf','0000000007','0000000001','2017-10-28','rejection',null),
('0000000004','Review of Alice in Wonderland','The aim of this review was to show everyone the thrilling of Alice in Wonderlan book.','http://www.ysgolgymraeg.ceredigion.sch.uk/gwaith_plant/Review%20Alice%20in%20Wonderland.pdf','0000000008','0000000002','2018-10-27','acceptance','posted'),
('0000000005','Deep Learning and AI', 'Introduction about deeplearning and AI', 'http://faculty.neu.edu.cn/yury/AAI/Textbook/Deep%20Learning%20with%20Python.pdf','0000000008','0000000001','2020-10-27',null,'response review');
insert into research_paper VALUES('0000000001',15);
insert into research_paper VALUES('0000000005',13);
insert into general_paper VALUES('0000000002',8);
insert into book
values('1231231231231','Hello World','2017',15,'Kim Dong');
insert into book
values('1231231231232','Alice in Wonderland', '2018',20,'Tuoi Tre');
insert into review_paper VALUES('0000000003',3,'1231231231231');
insert into review_paper VALUES('0000000004',4,'1231231231232');
insert into published_paper values('10.111/dome.12082', 'traditional','0000000004');
insert into published_paper values('10.111/dome.13444', 'open access','0000000002');
insert into criteria values('0000000001','Theo nội dung');
insert into criteria values('0000000002','Theo đề tài');
insert into rating_level values('0000000001','Ko hay',5);
insert into rating_level values('0000000001','Hay',10);
insert into rating_level values('0000000002','Đề tài dở',4);
insert into rating_level values('0000000002','Đề tài bình thường',6);
insert into rating_level values('0000000002','Đề tài ổn',8);
insert into rating_level values('0000000002','Đề tài tốt',10);
insert into `write` values('0000000001','0000000001');
insert into `write` values('0000000002','0000000001');
insert into `write` values('0000000002','0000000002');
insert into `write` values('0000000003','0000000001');
insert into `write` values('0000000003','0000000002');
insert into `write` values('0000000003','0000000003');
insert into `write` values('0000000004','0000000002');
insert into `write` values('0000000005','0000000001');
insert into `write` values('0000000005','0000000002');
insert into assign(paper_id,reviewer_id,editor_id, assign_date)
values('0000000001','0000000003', '0000000005', '2020-6-12');
insert into assign(paper_id,reviewer_id,editor_id,assign_date)
values('0000000002','0000000003', '0000000006','2019-10-30');
insert into assign(paper_id,reviewer_id,editor_id,assign_date)
values('0000000002','0000000004', '0000000006','2019-10-30');
insert into assign(paper_id,reviewer_id,editor_id,assign_date)
values('0000000003','0000000005', '0000000007', '2015-10-29');
insert into assign(paper_id,reviewer_id,editor_id,assign_date)
values('0000000004','0000000004', '0000000008','2018-10-30');
insert into assign(paper_id,reviewer_id,editor_id,assign_date)
values('0000000004','0000000005', '0000000008','2018-10-30');
insert into assign(paper_id,reviewer_id,editor_id,assign_date)
values('0000000004','0000000006', '0000000008','2018-10-30');
insert into assign(paper_id,reviewer_id,editor_id,assign_date)
values('0000000005','0000000005', '0000000008','2020-10-30');
insert into evaluate(paper_id,reviewer_id,criteria_id)
values('0000000001','0000000003', '0000000001');
insert into evaluate(paper_id,reviewer_id,criteria_id)
values('0000000002','0000000003', '0000000002');
insert into evaluate(paper_id,reviewer_id,criteria_id)
values('0000000002','0000000004', '0000000002');
insert into evaluate(paper_id,reviewer_id,criteria_id)
values('0000000003','0000000005', '0000000001');
insert into evaluate(paper_id,reviewer_id,criteria_id)
values('0000000003','0000000005', '0000000002');
insert into evaluate(paper_id,reviewer_id,criteria_id)
values('0000000004','0000000004', '0000000001');
insert into evaluate(paper_id,reviewer_id,criteria_id)
values('0000000004','0000000005', '0000000001');
insert into evaluate(paper_id,reviewer_id,criteria_id)
values('0000000004','0000000006', '0000000001');
insert into evaluate(paper_id,reviewer_id,criteria_id)
values('0000000005','0000000005', '0000000001');
insert into review(paper_id,reviewer_id,review_result)
values('0000000001','0000000003','Good');
insert into review(paper_id,reviewer_id,review_result)
values('0000000002','0000000003','Not Good');
insert into review(paper_id,reviewer_id,review_result)
values('0000000002','0000000004','Bad');
insert into review(paper_id,reviewer_id,review_result)
values('0000000003','0000000005','Very Good');
insert into review(paper_id,reviewer_id,review_result)
values('0000000004','0000000004','Normal');
insert into review(paper_id,reviewer_id,review_result)
values('0000000004','0000000005','Normal');
insert into review(paper_id,reviewer_id,review_result)
values('0000000004','0000000006','Normal');
insert into review(paper_id,reviewer_id,review_result)
values('0000000005','0000000005','Very Good');