forked from cmzalvin/giit
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathcreate.sql
More file actions
182 lines (165 loc) · 4.3 KB
/
create.sql
File metadata and controls
182 lines (165 loc) · 4.3 KB
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
########################
# Drop all table
########################
DROP TABLE IF EXISTS book, class, course, department, order_book, resource, role, section, speciality, staff, student, takes, timetable, user;
CREATE TABLE book
(
book_title VARCHAR(30),
isbn VARCHAR(20),
date_of_printing VARCHAR(20),
author VARCHAR(15),
press VARCHAR(15),
category CHAR,
unit_price FLOAT,
PRIMARY KEY (book_title, isbn)
)
DEFAULT CHARSET = utf8,
ENGINE = InnoDB;
CREATE TABLE class
(
class_id VARCHAR(30),
class_name VARCHAR(10),
year VARCHAR(20),
spec_name VARCHAR(15),
PRIMARY KEY (class_id)
)
DEFAULT CHARSET = utf8,
ENGINE = InnoDB;
CREATE TABLE course
(
course_title VARCHAR(30),
type VARCHAR(10),
credits FLOAT,
speciality VARCHAR(15),
PRIMARY KEY (course_title)
)
DEFAULT CHARSET = utf8,
ENGINE = InnoDB;
CREATE TABLE department
(
dept_id INT AUTO_INCREMENT,
dept_name VARCHAR(15),
PRIMARY KEY (dept_id)
)
DEFAULT CHARSET = utf8,
ENGINE = InnoDB;
CREATE TABLE order_book
(
staff_id VARCHAR(30),
sec_id INT,
book_title VARCHAR(30),
isbn VARCHAR(20),
remark VARCHAR(30),
approval TINYINT(1),
PRIMARY KEY (staff_id, sec_id, book_title, isbn)
)
DEFAULT CHARSET = utf8,
ENGINE = InnoDB;
CREATE TABLE resource (
id BIGINT AUTO_INCREMENT,
name VARCHAR(100),
type VARCHAR(50),
url VARCHAR(200),
parent_id BIGINT,
parent_ids VARCHAR(100),
permission VARCHAR(100),
available BOOL DEFAULT FALSE,
PRIMARY KEY (id)
)
CHARSET = utf8
ENGINE = InnoDB;
CREATE INDEX idx_resource_parent_id ON resource (parent_id);
CREATE INDEX idx_resource_parent_ids ON resource (parent_ids);
CREATE TABLE role (
id BIGINT AUTO_INCREMENT,
role VARCHAR(100),
description VARCHAR(100),
resource_ids VARCHAR(100),
available BOOL DEFAULT FALSE,
PRIMARY KEY (id)
)
CHARSET = utf8
ENGINE = InnoDB;
CREATE INDEX idx_sys_role_resource_ids ON role (resource_ids);
CREATE TABLE section
(
sec_id INT AUTO_INCREMENT,
course_title VARCHAR(15),
year VARCHAR(5),
limitCount INT,
staff_id VARCHAR(20),
PRIMARY KEY (sec_id)
)
DEFAULT CHARSET = utf8,
ENGINE = InnoDB;
CREATE TABLE speciality
(
spec_id INT AUTO_INCREMENT,
dept_name VARCHAR(10),
spec_name VARCHAR(15),
year VARCHAR(5),
PRIMARY KEY (spec_id)
)
DEFAULT CHARSET = utf8,
ENGINE = InnoDB;
CREATE TABLE staff
(
staff_id VARCHAR(30),
staff_name VARCHAR(20),
PRIMARY KEY (staff_id)
)
DEFAULT CHARSET = utf8,
ENGINE = InnoDB;
CREATE TABLE student
(
student_id VARCHAR(30),
student_name VARCHAR(20),
id_card VARCHAR(20),
year VARCHAR(5),
class_id VARCHAR(10),
telephone_number VARCHAR(20),
student_origin_base VARCHAR(10),
gender VARCHAR(4),
pic_path VARCHAR(45),
leave_of_absence BOOL,
leave_school BOOL,
PRIMARY KEY (student_id)
)
DEFAULT CHARSET = utf8,
ENGINE = InnoDB;
CREATE TABLE takes
(
student_id VARCHAR(30),
sec_id VARCHAR(30),
score FLOAT,
PRIMARY KEY (student_id, sec_id)
)
DEFAULT CHARSET = utf8,
ENGINE = InnoDB;
CREATE TABLE timetable
(
sec_id INT,
time VARCHAR(20),
weeks VARCHAR(20),
week VARCHAR(20),
classroom VARCHAR(10)
)
DEFAULT CHARSET = utf8,
ENGINE = InnoDB;
CREATE TABLE user
(
user_id VARCHAR(30),
password VARCHAR(50),
salt VARCHAR(100),
role_ids VARCHAR(100),
locked BOOL DEFAULT FALSE,
PRIMARY KEY (user_id)
)
DEFAULT CHARSET = utf8,
ENGINE = InnoDB;
CREATE UNIQUE INDEX idx_user_username ON user (user_id);
#ALTER TABLE staff ADD CONSTRAINT fk_user_staff FOREIGN KEY (staff_id) REFERENCES orders (user_id);
#ALTER TABLE order_book ADD CONSTRAINT fk_courses_user_books_classe FOREIGN KEY (staffs_id) REFERENCES staffs (staff_id),
#FOREIGN KEY (book_title,isbn) REFERENCES book (book_title,isbn),
#FOREIGN KEY (grade,professional,department) REFERENCES class (grade,professional,department),
#FOREIGN KEY (course_id) REFERENCES course (courses_id);