· 8 years ago · May 01, 2018, 10:32 PM
1-- MySQL Script generated by MySQL Workbench
2-- Tue May 1 17:27:13 2018
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
9
10-- -----------------------------------------------------
11-- Schema default_schema
12-- -----------------------------------------------------
13-- -----------------------------------------------------
14-- Schema TutoDB
15-- -----------------------------------------------------
16DROP SCHEMA IF EXISTS `TutoDB` ;
17
18-- -----------------------------------------------------
19-- Schema TutoDB
20-- -----------------------------------------------------
21CREATE SCHEMA IF NOT EXISTS `TutoDB` DEFAULT CHARACTER SET utf8 ;
22USE `TutoDB` ;
23
24-- -----------------------------------------------------
25-- Table `TutoDB`.`Status`
26-- -----------------------------------------------------
27DROP TABLE IF EXISTS `TutoDB`.`Status` ;
28
29CREATE TABLE IF NOT EXISTS `TutoDB`.`Status` (
30 `idStatus` INT NOT NULL AUTO_INCREMENT,
31 `description` VARCHAR(45) NOT NULL,
32 PRIMARY KEY (`idStatus`))
33ENGINE = InnoDB;
34
35
36-- -----------------------------------------------------
37-- Table `TutoDB`.`User_Type`
38-- -----------------------------------------------------
39DROP TABLE IF EXISTS `TutoDB`.`User_Type` ;
40
41CREATE TABLE IF NOT EXISTS `TutoDB`.`User_Type` (
42 `idUser_Type` INT NOT NULL AUTO_INCREMENT,
43 `type` TINYINT NOT NULL,
44 `description` VARCHAR(45) NOT NULL,
45 PRIMARY KEY (`idUser_Type`))
46ENGINE = InnoDB;
47
48
49-- -----------------------------------------------------
50-- Table `TutoDB`.`Firebase`
51-- -----------------------------------------------------
52DROP TABLE IF EXISTS `TutoDB`.`Firebase` ;
53
54CREATE TABLE IF NOT EXISTS `TutoDB`.`Firebase` (
55 `idFirebase` INT NOT NULL,
56 `idRealTime` VARCHAR(45) NOT NULL,
57 PRIMARY KEY (`idFirebase`))
58ENGINE = InnoDB;
59
60
61-- -----------------------------------------------------
62-- Table `TutoDB`.`User`
63-- -----------------------------------------------------
64DROP TABLE IF EXISTS `TutoDB`.`User` ;
65
66CREATE TABLE IF NOT EXISTS `TutoDB`.`User` (
67 `idUser` INT NOT NULL AUTO_INCREMENT,
68 `email` VARCHAR(25) NOT NULL,
69 `password` VARCHAR(100) NOT NULL,
70 `name` VARCHAR(20) NOT NULL,
71 `lastName` VARCHAR(20) NOT NULL,
72 `username` VARCHAR(20) NOT NULL,
73 `userLiveLocationLatitude` VARCHAR(45) NULL,
74 `userLiveLocationLongitude` VARCHAR(45) NULL,
75 `User_Type_idUser_Type` INT NOT NULL,
76 `Firebase_idFirebase` INT NOT NULL,
77 PRIMARY KEY (`idUser`),
78 INDEX `fk_User_User_Type1_idx` (`User_Type_idUser_Type` ASC),
79 INDEX `fk_User_Firebase1_idx` (`Firebase_idFirebase` ASC),
80 CONSTRAINT `fk_User_User_Type1`
81 FOREIGN KEY (`User_Type_idUser_Type`)
82 REFERENCES `TutoDB`.`User_Type` (`idUser_Type`)
83 ON DELETE NO ACTION
84 ON UPDATE NO ACTION,
85 CONSTRAINT `fk_User_Firebase1`
86 FOREIGN KEY (`Firebase_idFirebase`)
87 REFERENCES `TutoDB`.`Firebase` (`idFirebase`)
88 ON DELETE NO ACTION
89 ON UPDATE NO ACTION)
90ENGINE = InnoDB;
91
92
93-- -----------------------------------------------------
94-- Table `TutoDB`.`Career`
95-- -----------------------------------------------------
96DROP TABLE IF EXISTS `TutoDB`.`Career` ;
97
98CREATE TABLE IF NOT EXISTS `TutoDB`.`Career` (
99 `idCareer` INT NOT NULL AUTO_INCREMENT,
100 `name` VARCHAR(45) NOT NULL,
101 `level` VARCHAR(45) NOT NULL,
102 PRIMARY KEY (`idCareer`))
103ENGINE = InnoDB;
104
105
106-- -----------------------------------------------------
107-- Table `TutoDB`.`Subject_Type`
108-- -----------------------------------------------------
109DROP TABLE IF EXISTS `TutoDB`.`Subject_Type` ;
110
111CREATE TABLE IF NOT EXISTS `TutoDB`.`Subject_Type` (
112 `idType` INT NOT NULL AUTO_INCREMENT,
113 `name` VARCHAR(45) NOT NULL,
114 `Career_idCareer` INT NOT NULL,
115 PRIMARY KEY (`idType`),
116 INDEX `fk_Subject_Type_Career1_idx` (`Career_idCareer` ASC),
117 CONSTRAINT `fk_Subject_Type_Career1`
118 FOREIGN KEY (`Career_idCareer`)
119 REFERENCES `TutoDB`.`Career` (`idCareer`)
120 ON DELETE NO ACTION
121 ON UPDATE NO ACTION)
122ENGINE = InnoDB;
123
124
125-- -----------------------------------------------------
126-- Table `TutoDB`.`Subject`
127-- -----------------------------------------------------
128DROP TABLE IF EXISTS `TutoDB`.`Subject` ;
129
130CREATE TABLE IF NOT EXISTS `TutoDB`.`Subject` (
131 `idSubject` INT NOT NULL AUTO_INCREMENT,
132 `name` VARCHAR(45) NOT NULL,
133 `Type_idType` INT NOT NULL,
134 PRIMARY KEY (`idSubject`),
135 INDEX `fk_Materia_Type1_idx` (`Type_idType` ASC),
136 CONSTRAINT `fk_Materia_Type1`
137 FOREIGN KEY (`Type_idType`)
138 REFERENCES `TutoDB`.`Subject_Type` (`idType`)
139 ON DELETE NO ACTION
140 ON UPDATE NO ACTION)
141ENGINE = InnoDB;
142
143
144-- -----------------------------------------------------
145-- Table `TutoDB`.`Topic`
146-- -----------------------------------------------------
147DROP TABLE IF EXISTS `TutoDB`.`Topic` ;
148
149CREATE TABLE IF NOT EXISTS `TutoDB`.`Topic` (
150 `idTopic` INT NOT NULL AUTO_INCREMENT,
151 `complexity` VARCHAR(45) NOT NULL,
152 `name` VARCHAR(45) NOT NULL,
153 `Subject_idSubject` INT NOT NULL,
154 PRIMARY KEY (`idTopic`),
155 INDEX `fk_Topic_Subject1_idx` (`Subject_idSubject` ASC),
156 CONSTRAINT `fk_Topic_Subject1`
157 FOREIGN KEY (`Subject_idSubject`)
158 REFERENCES `TutoDB`.`Subject` (`idSubject`)
159 ON DELETE NO ACTION
160 ON UPDATE NO ACTION)
161ENGINE = InnoDB;
162
163
164-- -----------------------------------------------------
165-- Table `TutoDB`.`Location`
166-- -----------------------------------------------------
167DROP TABLE IF EXISTS `TutoDB`.`Location` ;
168
169CREATE TABLE IF NOT EXISTS `TutoDB`.`Location` (
170 `idLocation` INT NOT NULL AUTO_INCREMENT,
171 `latitude` VARCHAR(45) NOT NULL,
172 `longitude` VARCHAR(45) NOT NULL,
173 `description` VARCHAR(45) NOT NULL,
174 PRIMARY KEY (`idLocation`))
175ENGINE = InnoDB;
176
177
178-- -----------------------------------------------------
179-- Table `TutoDB`.`Tutorship`
180-- -----------------------------------------------------
181DROP TABLE IF EXISTS `TutoDB`.`Tutorship` ;
182
183CREATE TABLE IF NOT EXISTS `TutoDB`.`Tutorship` (
184 `idTutorship` INT NOT NULL AUTO_INCREMENT,
185 `date` DATETIME NOT NULL,
186 `motive` VARCHAR(45) NOT NULL,
187 `latitude` VARCHAR(45) NOT NULL,
188 `longitude` VARCHAR(45) NOT NULL,
189 `cost` DOUBLE NOT NULL,
190 `rating` INT NULL DEFAULT NULL,
191 `timeStamp` DATETIME NULL DEFAULT NULL,
192 `Status_idStatus` INT NOT NULL,
193 `User_idUser` INT NOT NULL,
194 `Topic_idTopic` INT NOT NULL,
195 `Tuto_TutoId` INT NOT NULL,
196 PRIMARY KEY (`idTutorship`),
197 INDEX `fk_Tutorship_Status1_idx` (`Status_idStatus` ASC),
198 INDEX `fk_Tutorship_Student1_idx` (`User_idUser` ASC),
199 INDEX `fk_Tutorship_Topic1_idx` (`Topic_idTopic` ASC),
200 INDEX `fk_Tutorship_User1_idx` (`Tuto_TutoId` ASC),
201 CONSTRAINT `fk_Tutorship_Status1`
202 FOREIGN KEY (`Status_idStatus`)
203 REFERENCES `TutoDB`.`Status` (`idStatus`)
204 ON DELETE NO ACTION
205 ON UPDATE NO ACTION,
206 CONSTRAINT `fk_Tutorship_Student1`
207 FOREIGN KEY (`User_idUser`)
208 REFERENCES `TutoDB`.`User` (`idUser`)
209 ON DELETE NO ACTION
210 ON UPDATE NO ACTION,
211 CONSTRAINT `fk_Tutorship_Topic1`
212 FOREIGN KEY (`Topic_idTopic`)
213 REFERENCES `TutoDB`.`Topic` (`idTopic`)
214 ON DELETE NO ACTION
215 ON UPDATE NO ACTION,
216 CONSTRAINT `fk_Tutorship_User1`
217 FOREIGN KEY (`Tuto_TutoId`)
218 REFERENCES `TutoDB`.`User` (`idUser`)
219 ON DELETE NO ACTION
220 ON UPDATE NO ACTION)
221ENGINE = InnoDB;
222
223
224-- -----------------------------------------------------
225-- Table `TutoDB`.`Location_has_User`
226-- -----------------------------------------------------
227DROP TABLE IF EXISTS `TutoDB`.`Location_has_User` ;
228
229CREATE TABLE IF NOT EXISTS `TutoDB`.`Location_has_User` (
230 `idLocation_has_user` INT NOT NULL AUTO_INCREMENT,
231 `Location_idLocation` INT NOT NULL,
232 `User_idUser` INT NOT NULL,
233 INDEX `fk_Location_has_User_User1_idx` (`User_idUser` ASC),
234 INDEX `fk_Location_has_User_Location1_idx` (`Location_idLocation` ASC),
235 PRIMARY KEY (`idLocation_has_user`),
236 CONSTRAINT `fk_Location_has_User_Location1`
237 FOREIGN KEY (`Location_idLocation`)
238 REFERENCES `TutoDB`.`Location` (`idLocation`)
239 ON DELETE NO ACTION
240 ON UPDATE NO ACTION,
241 CONSTRAINT `fk_Location_has_User_User1`
242 FOREIGN KEY (`User_idUser`)
243 REFERENCES `TutoDB`.`User` (`idUser`)
244 ON DELETE NO ACTION
245 ON UPDATE NO ACTION)
246ENGINE = InnoDB;
247
248
249-- -----------------------------------------------------
250-- Table `TutoDB`.`User_has_Career`
251-- -----------------------------------------------------
252DROP TABLE IF EXISTS `TutoDB`.`User_has_Career` ;
253
254CREATE TABLE IF NOT EXISTS `TutoDB`.`User_has_Career` (
255 `idUser_has_Career` INT NOT NULL AUTO_INCREMENT,
256 `User_idUser` INT NOT NULL,
257 `Career_idCareer` INT NOT NULL,
258 INDEX `fk_User_has_Career_Career1_idx` (`Career_idCareer` ASC),
259 INDEX `fk_User_has_Career_User1_idx` (`User_idUser` ASC),
260 PRIMARY KEY (`idUser_has_Career`),
261 CONSTRAINT `fk_User_has_Career_User1`
262 FOREIGN KEY (`User_idUser`)
263 REFERENCES `TutoDB`.`User` (`idUser`)
264 ON DELETE NO ACTION
265 ON UPDATE NO ACTION,
266 CONSTRAINT `fk_User_has_Career_Career1`
267 FOREIGN KEY (`Career_idCareer`)
268 REFERENCES `TutoDB`.`Career` (`idCareer`)
269 ON DELETE NO ACTION
270 ON UPDATE NO ACTION)
271ENGINE = InnoDB;
272
273
274SET SQL_MODE=@OLD_SQL_MODE;
275SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
276SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;