· 8 years ago · May 14, 2018, 01:38 PM
1-- MySQL Script generated by MySQL Workbench
2-- Mon May 14 08:33:05 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 `gender` VARCHAR(1) NOT NULL,
74 `userLiveLocationLatitude` VARCHAR(45) NULL,
75 `userLiveLocationLongitude` VARCHAR(45) NULL,
76 `User_Type_idUser_Type` INT NULL,
77 `Firebase_idFirebase` INT NULL,
78 PRIMARY KEY (`idUser`),
79 INDEX `fk_User_User_Type1_idx` (`User_Type_idUser_Type` ASC),
80 INDEX `fk_User_Firebase1_idx` (`Firebase_idFirebase` ASC),
81 CONSTRAINT `fk_User_User_Type1`
82 FOREIGN KEY (`User_Type_idUser_Type`)
83 REFERENCES `TutoDB`.`User_Type` (`idUser_Type`)
84 ON DELETE NO ACTION
85 ON UPDATE NO ACTION,
86 CONSTRAINT `fk_User_Firebase1`
87 FOREIGN KEY (`Firebase_idFirebase`)
88 REFERENCES `TutoDB`.`Firebase` (`idFirebase`)
89 ON DELETE NO ACTION
90 ON UPDATE NO ACTION)
91ENGINE = InnoDB;
92
93
94-- -----------------------------------------------------
95-- Table `TutoDB`.`Career`
96-- -----------------------------------------------------
97DROP TABLE IF EXISTS `TutoDB`.`Career` ;
98
99CREATE TABLE IF NOT EXISTS `TutoDB`.`Career` (
100 `idCareer` INT NOT NULL AUTO_INCREMENT,
101 `name` VARCHAR(45) NOT NULL,
102 `level` VARCHAR(45) NOT NULL,
103 PRIMARY KEY (`idCareer`))
104ENGINE = InnoDB;
105
106
107-- -----------------------------------------------------
108-- Table `TutoDB`.`Subject_Type`
109-- -----------------------------------------------------
110DROP TABLE IF EXISTS `TutoDB`.`Subject_Type` ;
111
112CREATE TABLE IF NOT EXISTS `TutoDB`.`Subject_Type` (
113 `idType` INT NOT NULL AUTO_INCREMENT,
114 `name` VARCHAR(45) NOT NULL,
115 `Career_idCareer` INT NOT NULL,
116 PRIMARY KEY (`idType`),
117 INDEX `fk_Subject_Type_Career1_idx` (`Career_idCareer` ASC),
118 CONSTRAINT `fk_Subject_Type_Career1`
119 FOREIGN KEY (`Career_idCareer`)
120 REFERENCES `TutoDB`.`Career` (`idCareer`)
121 ON DELETE NO ACTION
122 ON UPDATE NO ACTION)
123ENGINE = InnoDB;
124
125
126-- -----------------------------------------------------
127-- Table `TutoDB`.`Subject`
128-- -----------------------------------------------------
129DROP TABLE IF EXISTS `TutoDB`.`Subject` ;
130
131CREATE TABLE IF NOT EXISTS `TutoDB`.`Subject` (
132 `idSubject` INT NOT NULL AUTO_INCREMENT,
133 `name` VARCHAR(45) NOT NULL,
134 `Type_idType` INT NOT NULL,
135 PRIMARY KEY (`idSubject`),
136 INDEX `fk_Materia_Type1_idx` (`Type_idType` ASC),
137 CONSTRAINT `fk_Materia_Type1`
138 FOREIGN KEY (`Type_idType`)
139 REFERENCES `TutoDB`.`Subject_Type` (`idType`)
140 ON DELETE NO ACTION
141 ON UPDATE NO ACTION)
142ENGINE = InnoDB;
143
144
145-- -----------------------------------------------------
146-- Table `TutoDB`.`Topic`
147-- -----------------------------------------------------
148DROP TABLE IF EXISTS `TutoDB`.`Topic` ;
149
150CREATE TABLE IF NOT EXISTS `TutoDB`.`Topic` (
151 `idTopic` INT NOT NULL AUTO_INCREMENT,
152 `complexity` VARCHAR(45) NOT NULL,
153 `name` VARCHAR(45) NOT NULL,
154 `Subject_idSubject` INT NOT NULL,
155 PRIMARY KEY (`idTopic`),
156 INDEX `fk_Topic_Subject1_idx` (`Subject_idSubject` ASC),
157 CONSTRAINT `fk_Topic_Subject1`
158 FOREIGN KEY (`Subject_idSubject`)
159 REFERENCES `TutoDB`.`Subject` (`idSubject`)
160 ON DELETE NO ACTION
161 ON UPDATE NO ACTION)
162ENGINE = InnoDB;
163
164
165-- -----------------------------------------------------
166-- Table `TutoDB`.`Location`
167-- -----------------------------------------------------
168DROP TABLE IF EXISTS `TutoDB`.`Location` ;
169
170CREATE TABLE IF NOT EXISTS `TutoDB`.`Location` (
171 `idLocation` INT NOT NULL AUTO_INCREMENT,
172 `latitude` VARCHAR(45) NOT NULL,
173 `longitude` VARCHAR(45) NOT NULL,
174 `description` VARCHAR(45) NOT NULL,
175 PRIMARY KEY (`idLocation`))
176ENGINE = InnoDB;
177
178
179-- -----------------------------------------------------
180-- Table `TutoDB`.`Tutorship`
181-- -----------------------------------------------------
182DROP TABLE IF EXISTS `TutoDB`.`Tutorship` ;
183
184CREATE TABLE IF NOT EXISTS `TutoDB`.`Tutorship` (
185 `idTutorship` INT NOT NULL AUTO_INCREMENT,
186 `date` DATETIME NOT NULL,
187 `motive` VARCHAR(45) NOT NULL,
188 `latitude` VARCHAR(45) NOT NULL,
189 `longitude` VARCHAR(45) NOT NULL,
190 `cost` DOUBLE NULL,
191 `rating` INT NULL DEFAULT NULL,
192 `timeStamp` DATETIME NULL DEFAULT NULL,
193 `Status_idStatus` INT NOT NULL,
194 `User_idUser` INT NOT NULL,
195 `Topic_idTopic` INT NOT NULL,
196 `Tuto_TutoId` INT NOT NULL,
197 PRIMARY KEY (`idTutorship`),
198 INDEX `fk_Tutorship_Status1_idx` (`Status_idStatus` ASC),
199 INDEX `fk_Tutorship_Student1_idx` (`User_idUser` ASC),
200 INDEX `fk_Tutorship_Topic1_idx` (`Topic_idTopic` ASC),
201 INDEX `fk_Tutorship_User1_idx` (`Tuto_TutoId` ASC),
202 CONSTRAINT `fk_Tutorship_Status1`
203 FOREIGN KEY (`Status_idStatus`)
204 REFERENCES `TutoDB`.`Status` (`idStatus`)
205 ON DELETE NO ACTION
206 ON UPDATE NO ACTION,
207 CONSTRAINT `fk_Tutorship_Student1`
208 FOREIGN KEY (`User_idUser`)
209 REFERENCES `TutoDB`.`User` (`idUser`)
210 ON DELETE NO ACTION
211 ON UPDATE NO ACTION,
212 CONSTRAINT `fk_Tutorship_Topic1`
213 FOREIGN KEY (`Topic_idTopic`)
214 REFERENCES `TutoDB`.`Topic` (`idTopic`)
215 ON DELETE NO ACTION
216 ON UPDATE NO ACTION,
217 CONSTRAINT `fk_Tutorship_User1`
218 FOREIGN KEY (`Tuto_TutoId`)
219 REFERENCES `TutoDB`.`User` (`idUser`)
220 ON DELETE NO ACTION
221 ON UPDATE NO ACTION)
222ENGINE = InnoDB;
223
224
225-- -----------------------------------------------------
226-- Table `TutoDB`.`Location_has_User`
227-- -----------------------------------------------------
228DROP TABLE IF EXISTS `TutoDB`.`Location_has_User` ;
229
230CREATE TABLE IF NOT EXISTS `TutoDB`.`Location_has_User` (
231 `idLocation_has_user` INT NOT NULL AUTO_INCREMENT,
232 `Location_idLocation` INT NOT NULL,
233 `User_idUser` INT NOT NULL,
234 INDEX `fk_Location_has_User_User1_idx` (`User_idUser` ASC),
235 INDEX `fk_Location_has_User_Location1_idx` (`Location_idLocation` ASC),
236 PRIMARY KEY (`idLocation_has_user`),
237 CONSTRAINT `fk_Location_has_User_Location1`
238 FOREIGN KEY (`Location_idLocation`)
239 REFERENCES `TutoDB`.`Location` (`idLocation`)
240 ON DELETE NO ACTION
241 ON UPDATE NO ACTION,
242 CONSTRAINT `fk_Location_has_User_User1`
243 FOREIGN KEY (`User_idUser`)
244 REFERENCES `TutoDB`.`User` (`idUser`)
245 ON DELETE NO ACTION
246 ON UPDATE NO ACTION)
247ENGINE = InnoDB;
248
249
250-- -----------------------------------------------------
251-- Table `TutoDB`.`User_has_Career`
252-- -----------------------------------------------------
253DROP TABLE IF EXISTS `TutoDB`.`User_has_Career` ;
254
255CREATE TABLE IF NOT EXISTS `TutoDB`.`User_has_Career` (
256 `idUser_has_Career` INT NOT NULL AUTO_INCREMENT,
257 `User_idUser` INT NOT NULL,
258 `Career_idCareer` INT NOT NULL,
259 INDEX `fk_User_has_Career_Career1_idx` (`Career_idCareer` ASC),
260 INDEX `fk_User_has_Career_User1_idx` (`User_idUser` ASC),
261 PRIMARY KEY (`idUser_has_Career`),
262 CONSTRAINT `fk_User_has_Career_User1`
263 FOREIGN KEY (`User_idUser`)
264 REFERENCES `TutoDB`.`User` (`idUser`)
265 ON DELETE NO ACTION
266 ON UPDATE NO ACTION,
267 CONSTRAINT `fk_User_has_Career_Career1`
268 FOREIGN KEY (`Career_idCareer`)
269 REFERENCES `TutoDB`.`Career` (`idCareer`)
270 ON DELETE NO ACTION
271 ON UPDATE NO ACTION)
272ENGINE = InnoDB;
273
274
275SET SQL_MODE=@OLD_SQL_MODE;
276SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
277SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;