· 7 years ago · Nov 01, 2018, 08:16 PM
1-- MySQL Script generated by MySQL Workbench
2-- Thu Nov 1 21:12: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 mydb
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema mydb
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
18USE `mydb` ;
19
20-- -----------------------------------------------------
21-- Table `mydb`.`timeGroup`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `mydb`.`timeGroup` (
24 `idtimeGroup` INT NOT NULL AUTO_INCREMENT,
25 `hint` VARCHAR(256) NULL,
26 PRIMARY KEY (`idtimeGroup`),
27 UNIQUE INDEX `idtimeGroup_UNIQUE` (`idtimeGroup` ASC))
28ENGINE = InnoDB;
29
30
31-- -----------------------------------------------------
32-- Table `mydb`.`teacher`
33-- -----------------------------------------------------
34CREATE TABLE IF NOT EXISTS `mydb`.`teacher` (
35 `idteacher` INT NOT NULL AUTO_INCREMENT,
36 `firstname` VARCHAR(45) NOT NULL,
37 `lastname` VARCHAR(45) NOT NULL,
38 `location` VARCHAR(45) NOT NULL,
39 `mail` VARCHAR(45) NOT NULL,
40 `hint` VARCHAR(256) NULL,
41 `timeGroup_id` INT NOT NULL,
42 `md5_hash` VARCHAR(45) NOT NULL,
43 `image_url` VARCHAR(45) NOT NULL,
44 PRIMARY KEY (`idteacher`),
45 UNIQUE INDEX `idteacher_UNIQUE` (`idteacher` ASC),
46 INDEX `timeGroup_id_idx` (`timeGroup_id` ASC),
47 UNIQUE INDEX `mail_UNIQUE` (`mail` ASC),
48 CONSTRAINT `timeGroup_idaa`
49 FOREIGN KEY (`timeGroup_id`)
50 REFERENCES `mydb`.`timeGroup` (`idtimeGroup`)
51 ON DELETE NO ACTION
52 ON UPDATE NO ACTION)
53ENGINE = InnoDB;
54
55
56-- -----------------------------------------------------
57-- Table `mydb`.`student`
58-- -----------------------------------------------------
59CREATE TABLE IF NOT EXISTS `mydb`.`student` (
60 `idstudent` INT NOT NULL AUTO_INCREMENT,
61 `firstname` VARCHAR(45) NOT NULL,
62 `lastname` VARCHAR(45) NOT NULL,
63 `mail` VARCHAR(45) NOT NULL,
64 `hint` VARCHAR(256) NULL,
65 `md5_hash` VARCHAR(45) NOT NULL,
66 PRIMARY KEY (`idstudent`),
67 UNIQUE INDEX `idstudent_UNIQUE` (`idstudent` ASC),
68 UNIQUE INDEX `mail_UNIQUE` (`mail` ASC))
69ENGINE = InnoDB;
70
71
72-- -----------------------------------------------------
73-- Table `mydb`.`time`
74-- -----------------------------------------------------
75CREATE TABLE IF NOT EXISTS `mydb`.`time` (
76 `idtime` INT NOT NULL AUTO_INCREMENT,
77 `time_from` TIME(0) NOT NULL,
78 `time_to` TIME(0) NOT NULL,
79 `timeGroup_id` INT NOT NULL,
80 PRIMARY KEY (`idtime`),
81 UNIQUE INDEX `idtimes_UNIQUE` (`idtime` ASC),
82 INDEX `timeGroup_id_idx` (`timeGroup_id` ASC),
83 CONSTRAINT `timeGroup_id1sss`
84 FOREIGN KEY (`timeGroup_id`)
85 REFERENCES `mydb`.`timeGroup` (`idtimeGroup`)
86 ON DELETE NO ACTION
87 ON UPDATE NO ACTION)
88ENGINE = InnoDB;
89
90
91-- -----------------------------------------------------
92-- Table `mydb`.`appointment`
93-- -----------------------------------------------------
94CREATE TABLE IF NOT EXISTS `mydb`.`appointment` (
95 `idappointment` INT NOT NULL AUTO_INCREMENT,
96 `teacher_id` INT NOT NULL,
97 `student_id` INT NOT NULL,
98 `time_id` INT NOT NULL,
99 PRIMARY KEY (`idappointment`),
100 UNIQUE INDEX `idtermine_UNIQUE` (`idappointment` ASC),
101 INDEX `teacher_id_idx` (`teacher_id` ASC),
102 INDEX `student_id_idx` (`student_id` ASC),
103 INDEX `event_model_id_idx` (`time_id` ASC),
104 CONSTRAINT `teacher_idaa`
105 FOREIGN KEY (`teacher_id`)
106 REFERENCES `mydb`.`teacher` (`idteacher`)
107 ON DELETE NO ACTION
108 ON UPDATE NO ACTION,
109 CONSTRAINT `student_idaa`
110 FOREIGN KEY (`student_id`)
111 REFERENCES `mydb`.`student` (`idstudent`)
112 ON DELETE NO ACTION
113 ON UPDATE NO ACTION,
114 CONSTRAINT `event_model_idaa`
115 FOREIGN KEY (`time_id`)
116 REFERENCES `mydb`.`time` (`idtime`)
117 ON DELETE NO ACTION
118 ON UPDATE NO ACTION)
119ENGINE = InnoDB;
120
121
122-- -----------------------------------------------------
123-- Table `mydb`.`event`
124-- -----------------------------------------------------
125CREATE TABLE IF NOT EXISTS `mydb`.`event` (
126 `idevent` INT NOT NULL AUTO_INCREMENT,
127 `name` VARCHAR(45) NOT NULL,
128 `description` VARCHAR(45) NOT NULL,
129 `time_id` INT NOT NULL,
130 PRIMARY KEY (`idevent`),
131 UNIQUE INDEX `idshow_UNIQUE` (`idevent` ASC),
132 INDEX `time_id10_idx` (`time_id` ASC),
133 CONSTRAINT `time_id10aa`
134 FOREIGN KEY (`time_id`)
135 REFERENCES `mydb`.`time` (`idtime`)
136 ON DELETE NO ACTION
137 ON UPDATE NO ACTION)
138ENGINE = InnoDB;
139
140
141-- -----------------------------------------------------
142-- Table `mydb`.`administrator`
143-- -----------------------------------------------------
144CREATE TABLE IF NOT EXISTS `mydb`.`administrator` (
145 `idadministrator` INT NOT NULL AUTO_INCREMENT,
146 `username` VARCHAR(45) NOT NULL,
147 `md5_hash` VARCHAR(45) NOT NULL,
148 PRIMARY KEY (`idadministrator`),
149 UNIQUE INDEX `idadministrator_UNIQUE` (`idadministrator` ASC),
150 UNIQUE INDEX `username_UNIQUE` (`username` ASC))
151ENGINE = InnoDB;
152
153
154-- -----------------------------------------------------
155-- Table `mydb`.`session`
156-- -----------------------------------------------------
157CREATE TABLE IF NOT EXISTS `mydb`.`session` (
158 `idsession` INT NOT NULL AUTO_INCREMENT,
159 `key` VARCHAR(45) NOT NULL,
160 `time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
161 `permissions` VARCHAR(45) NOT NULL,
162 PRIMARY KEY (`idsession`),
163 UNIQUE INDEX `idsession_UNIQUE` (`idsession` ASC),
164 UNIQUE INDEX `key_UNIQUE` (`key` ASC))
165ENGINE = InnoDB;
166
167
168SET SQL_MODE=@OLD_SQL_MODE;
169SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
170SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;