· 4 years ago · Apr 18, 2021, 06:14 AM
1create database IF NOT EXISTS gtBD character set utf8 collate utf8_general_ci;
2USE gtBD;
3create table ROLES
4(
5 id INT PRIMARY KEY AUTO_INCREMENT,
6 baseon VARCHAR(20) NOT NULL,
7 privilege VARCHAR(20) UNIQUE NOT NULL,
8 announcement BOOLEAN default 0 NOT NULL,
9 Messenger BOOLEAN default 0 NOT NULL,
10 ownsched BOOLEAN default 1 NOT NULL,
11 createtutoring BOOLEAN default 1 NOT NULL,
12 crudstudent BOOLEAN default 0 NOT NULL,
13 denytutoring BOOLEAN default 0 NOT NULL,
14 jointutoring BOOLEAN default 1 NOT NULL,
15 foro BOOLEAN default 0 NOT NULL
16);
17
18
19CREATE TABLE CAMPUS
20(
21 id INT PRIMARY KEY AUTO_INCREMENT,
22 description VARCHAR(50)
23);
24
25CREATE TABLE CAREERS
26(
27 id INT PRIMARY KEY AUTO_INCREMENT,
28 description VARCHAR(100) NOT NULL
29);
30
31CREATE TABLE TUTORTYPES
32(
33 id INT PRIMARY KEY AUTO_INCREMENT,
34 description VARCHAR(50) NOT NULL,
35 hour_payment float(8, 2)
36);
37
38CREATE TABLE BINNACLE
39(
40 id INT PRIMARY KEY AUTO_INCREMENT,
41 typeevent VARCHAR(100),
42 description VARCHAR(500),
43 date_event VARCHAR(25) DEFAULT '',
44 hour_event VARCHAR(25) DEFAULT '',
45 username VARCHAR(25) DEFAULT 'Sistema',
46 ip_address VARCHAR(50)
47);
48
49
50CREATE TABLE COURSES
51(
52 id INT PRIMARY KEY AUTO_INCREMENT,
53 coursename VARCHAR(100) NOT NULL,
54 career INT NOT NULL,
55 FOREIGN KEY (career) REFERENCES CAREERS (id)
56);
57
58
59CREATE TABLE LOGINS
60(
61 id INT PRIMARY KEY AUTO_INCREMENT,
62 username VARCHAR(25) UNIQUE NOT NULL,
63 pass VARCHAR(200) NOT NULL,
64 userRole INT NOT NULL,
65 createOn VARCHAR(20) NOT NULL,
66 lastUpdate VARCHAR(20) NOT NULL,
67 fullname VARCHAR(50) NOT NULL,
68 alias VARCHAR(50) NOT NULL,
69 email VARCHAR(200) UNIQUE NOT NULL,
70 phone VARCHAR(50) NOT NULL,
71 campus INT NOT NULL,
72 career INT NOT NULL,
73 account VARCHAR(20) NOT NULL,
74 birthDate VARCHAR(20) NOT NULL,
75 admissionDate VARCHAR(25) NOT NULL,
76 photo VARCHAR(300) DEFAULT 'userdefault.png',
77 generalPoint INT DEFAULT 0,
78 observations VARCHAR(500),
79 tutorCategory INT DEFAULT 1 NOT NULL,
80 availability BOOLEAN DEFAULT 1 NOT NULL,
81 FOREIGN KEY (tutorCategory) REFERENCES TUTORTYPES (id),
82 FOREIGN KEY (userRole) REFERENCES ROLES (id),
83 FOREIGN KEY (career) REFERENCES CAREERS (id),
84 FOREIGN KEY (campus) REFERENCES CAMPUS (id)
85);
86
87
88CREATE TABLE SCHEDULES
89(
90 id INT PRIMARY KEY AUTO_INCREMENT,
91 starttime VARCHAR(10) NOT NULL,
92 finishtime VARCHAR(10) NOT NULL,
93 availability BOOLEAN DEFAULT 1 NOT NULL
94);
95
96
97CREATE TABLE SCH_TUT
98(
99 id INT PRIMARY KEY AUTO_INCREMENT,
100 tutor INT NOT NULL,
101 schedule INT NOT NULL,
102 course INT NOT NULL,
103 availability BOOLEAN DEFAULT 1 NOT NULL,
104 FOREIGN KEY (tutor) REFERENCES LOGINS (id),
105 FOREIGN KEY (schedule) REFERENCES SCHEDULES (id),
106 FOREIGN KEY (course) REFERENCES COURSES (id)
107);
108
109CREATE TABLE PERIODS
110(
111 id INT PRIMARY KEY AUTO_INCREMENT,
112 description VARCHAR(100) NOT NULL,
113 stardate VARCHAR(20) NOT NULL,
114 finishdate VARCHAR(20) NOT NULL
115);
116
117CREATE TABLE TUTORIALS
118(
119 id INT PRIMARY KEY AUTO_INCREMENT,
120 subject VARCHAR(100) NOT NULL,
121 details VARCHAR(500) NOT NULL,
122 reservdate VARCHAR(25) NOT NULL,
123 requestdate VARCHAR(25) NOT NULL,
124 filename varchar(100) DEFAULT '0',/*Empty*/
125 status INT DEFAULT -1 NOT NULL, /* -1 pendiente , 0 en proceso, 1 aprobado/programada ,2 finalizado,3 Cancelada */
126 score INT DEFAULT 0,
127 initialtime VARCHAR(25),#hora inicial programada
128 finaltime VARCHAR(25),#hora final programada
129 starttime VARCHAR(25),#hora en que inicio
130 finishtime VARCHAR(25),#hora en que finalizo
131 stucomment VARCHAR(500),
132 tutcomment VARCHAR(500),
133 space VARCHAR(500) DEFAULT 'No definido' NOT NULL,
134 period_ INT NOT NULL,
135 asignatura INT NOT NULL,
136 approvedby INT,
137 tutor INT NOT NULL,
138 petitioner INT NOT NULL,
139 modality TINYINT DEFAULT 0 NOT NULL, #0 presencial #1 virtual
140 FOREIGN KEY (asignatura) REFERENCES COURSES (id),
141 FOREIGN KEY (tutor) REFERENCES LOGINS (id),
142 FOREIGN KEY (petitioner) REFERENCES LOGINS (id),
143 FOREIGN KEY (approvedby) REFERENCES LOGINS (id),
144 FOREIGN KEY (period_) REFERENCES PERIODS (id)
145);
146
147
148CREATE TABLE MEMBERS_ASSISTANCE
149(
150 id INT PRIMARY KEY AUTO_INCREMENT,
151 tutorial INT NOT NULL,
152 student INT NOT NULL,
153 assistance BOOLEAN default 0,
154 FOREIGN KEY (tutorial) REFERENCES TUTORIALS (id),
155 FOREIGN KEY (student) REFERENCES LOGINS (id)
156);
157
158CREATE TABLE INSTITUTION(
159id INT PRIMARY KEY AUTO_INCREMENT,
160name VARCHAR(100) NOT NULL ,
161vision VARCHAR(500) NOT NULL,
162mission VARCHAR(500) NOT NULL,
163address VARCHAR(150) NOT NULL,
164telefone VARCHAR(50) NOT NULL,
165email VARCHAR(100) NOT NULL,
166logo VARCHAR(100) NOT NULL
167);
168
169CREATE TABLE SECTIONS
170(
171 id INT PRIMARY KEY AUTO_INCREMENT,
172 description VARCHAR(100) NOT NULL,
173 availability BOOLEAN DEFAULT 1 NOT NULL
174);
175
176
177CREATE TABLE NOTIFICATIONS(
178 id INT PRIMARY KEY AUTO_INCREMENT,
179 destinationid INT NOT NULL,
180 subject VARCHAR(50) NOT NULL ,
181 content VARCHAR(700) NOT NULL ,
182 status BOOLEAN DEFAULT 0 NOT NULL,
183 date VARCHAR(25) DEFAULT '' NOT NULL ,
184 FOREIGN KEY (destinationid) REFERENCES LOGINS(id)
185);
186
187CREATE TABLE BACKUPS
188(
189 id INT PRIMARY KEY AUTO_INCREMENT,
190 date VARCHAR(25) DEFAULT '' NOT NULL,
191 description VARCHAR(500) NOT NULL,
192 autor VARCHAR(50) NOT NULL,
193 filename VARCHAR(50) NOT NULL
194);