· 8 years ago · May 03, 2018, 12:32 AM
1
2-- -----------------------------------------------------
3-- Table `mydb`.`Alunos`
4-- -----------------------------------------------------
5CREATE TABLE IF NOT EXISTS `mydb`.`Alunos` (
6 `MAT` VARCHAR(10) NOT NULL,
7 `nome` VARCHAR(45) NULL,
8 `endereco` VARCHAR(45) NULL,
9 `cidade` VARCHAR(45) NULL,
10 PRIMARY KEY (`MAT`))
11ENGINE = InnoDB;
12
13
14-- -----------------------------------------------------
15-- Table `mydb`.`Disciplinas`
16-- -----------------------------------------------------
17CREATE TABLE IF NOT EXISTS `mydb`.`Disciplinas` (
18 `COD_DISC` VARCHAR(10) NOT NULL,
19 `nome_disc` VARCHAR(45) NULL,
20 `carga_hor` VARCHAR(45) NULL,
21 PRIMARY KEY (`COD_DISC`))
22ENGINE = InnoDB;
23
24
25-- -----------------------------------------------------
26-- Table `mydb`.`Professores`
27-- -----------------------------------------------------
28CREATE TABLE IF NOT EXISTS `mydb`.`Professores` (
29 `COD_PROF` VARCHAR(10) NOT NULL,
30 `nome` VARCHAR(45) NULL,
31 `endereco` VARCHAR(45) NULL,
32 `carga_horaria` VARCHAR(45) NULL,
33 PRIMARY KEY (`COD_PROF`))
34ENGINE = InnoDB;
35
36
37-- -----------------------------------------------------
38-- Table `mydb`.`Turma`
39-- -----------------------------------------------------
40CREATE TABLE IF NOT EXISTS `mydb`.`Turma` (
41 `COD_DISC` VARCHAR(10) NULL,
42 `COD_TURMA` VARCHAR(45) NULL,
43 `COD_PROF` VARCHAR(45) NULL,
44 `ANO` VARCHAR(45) NULL,
45 `HORARIO` VARCHAR(45) NULL,
46 INDEX `COD_PROF_FK_idx` (`COD_PROF` ASC),
47 INDEX `COD_DISC_FK_idx` (`COD_DISC` ASC),
48 CONSTRAINT `COD_PROF_FK`
49 FOREIGN KEY (`COD_PROF`)
50 REFERENCES `mydb`.`Professores` (`COD_PROF`)
51 ON DELETE NO ACTION
52 ON UPDATE NO ACTION,
53 CONSTRAINT `COD_DISC_FK`
54 FOREIGN KEY (`COD_DISC`)
55 REFERENCES `mydb`.`Disciplinas` (`COD_DISC`)
56 ON DELETE NO ACTION
57 ON UPDATE NO ACTION)
58ENGINE = InnoDB;
59
60
61-- -----------------------------------------------------
62-- Table `mydb`.`Historico`
63-- -----------------------------------------------------
64CREATE TABLE IF NOT EXISTS `mydb`.`Historico` (
65 `MAT` VARCHAR(10) NULL,
66 `COD_DISC` VARCHAR(45) NULL,
67 `COD_TURMA` VARCHAR(45) NULL,
68 `COD_PROF` VARCHAR(45) NULL,
69 `ANO` VARCHAR(45) NULL,
70 `FREQUENCIA` INT NULL,
71 `NOTA` INT NULL,
72 INDEX `MAT_FK_idx` (`MAT` ASC),
73 INDEX `COD_DISC_FK_idx` (`COD_DISC` ASC),
74 INDEX `COD_TURMA_FK_idx` (`COD_TURMA` ASC),
75 CONSTRAINT `MAT_FK`
76 FOREIGN KEY (`MAT`)
77 REFERENCES `mydb`.`Alunos` (`MAT`)
78 ON DELETE NO ACTION
79 ON UPDATE NO ACTION,
80 CONSTRAINT `COD_DISC_FK`
81 FOREIGN KEY (`COD_DISC`)
82 REFERENCES `mydb`.`Disciplinas` (`COD_DISC`)
83 ON DELETE NO ACTION
84 ON UPDATE NO ACTION,
85 CONSTRAINT `COD_TURMA_FK`
86 FOREIGN KEY (`COD_TURMA`)
87 REFERENCES `mydb`.`Turma` (`COD_DISC`)
88 ON DELETE NO ACTION
89 ON UPDATE NO ACTION)
90ENGINE = InnoDB;
91
92USE `universidade` ;
93
94-- -----------------------------------------------------
95-- Table `universidade`.`alunos`
96-- -----------------------------------------------------
97CREATE TABLE IF NOT EXISTS `universidade`.`alunos` (
98 `Mat` VARCHAR(10) NOT NULL,
99 `nome` VARCHAR(45) NOT NULL,
100 `endereco` VARCHAR(80) NOT NULL,
101 `cidade` VARCHAR(45) NOT NULL,
102 PRIMARY KEY (`Mat`))
103ENGINE = InnoDB
104DEFAULT CHARACTER SET = latin1;
105
106
107-- -----------------------------------------------------
108-- Table `universidade`.`anos`
109-- -----------------------------------------------------
110CREATE TABLE IF NOT EXISTS `universidade`.`anos` (
111 `ANO` VARCHAR(10) NOT NULL,
112 PRIMARY KEY (`ANO`))
113ENGINE = InnoDB
114DEFAULT CHARACTER SET = latin1;
115
116
117-- -----------------------------------------------------
118-- Table `universidade`.`cod_turma`
119-- -----------------------------------------------------
120CREATE TABLE IF NOT EXISTS `universidade`.`cod_turma` (
121 `COD_TURMA` VARCHAR(10) NOT NULL,
122 PRIMARY KEY (`COD_TURMA`))
123ENGINE = InnoDB
124DEFAULT CHARACTER SET = latin1;
125
126
127-- -----------------------------------------------------
128-- Table `universidade`.`disciplinas`
129-- -----------------------------------------------------
130CREATE TABLE IF NOT EXISTS `universidade`.`disciplinas` (
131 `COD_DISC` VARCHAR(10) NOT NULL,
132 `nome_disc` VARCHAR(45) NOT NULL,
133 `carga_horaria` INT(11) NOT NULL,
134 PRIMARY KEY (`COD_DISC`))
135ENGINE = InnoDB
136DEFAULT CHARACTER SET = latin1;
137
138
139-- -----------------------------------------------------
140-- Table `universidade`.`professores`
141-- -----------------------------------------------------
142CREATE TABLE IF NOT EXISTS `universidade`.`professores` (
143 `COD_PROF` VARCHAR(10) NOT NULL,
144 `Nome` VARCHAR(45) NOT NULL,
145 `endereco` VARCHAR(70) NOT NULL,
146 `cidade` VARCHAR(45) NOT NULL,
147 PRIMARY KEY (`COD_PROF`))
148ENGINE = InnoDB
149DEFAULT CHARACTER SET = latin1;
150
151
152-- -----------------------------------------------------
153-- Table `universidade`.`turma`
154-- -----------------------------------------------------
155CREATE TABLE IF NOT EXISTS `universidade`.`turma` (
156 `COD_DISC` VARCHAR(10) NOT NULL,
157 `COD_TURMA` VARCHAR(45) NOT NULL,
158 `COD_PROF` VARCHAR(45) NOT NULL,
159 `ANO` VARCHAR(45) NOT NULL,
160 `Horario` VARCHAR(45) NOT NULL,
161 INDEX `COD_DISC_idx` (`COD_DISC` ASC),
162 INDEX `COD_PROF_idx` (`COD_PROF` ASC),
163 INDEX `ANO_idx` (`ANO` ASC),
164 INDEX `COD_TURMA_idx` (`COD_TURMA` ASC),
165 CONSTRAINT `ANO`
166 FOREIGN KEY (`ANO`)
167 REFERENCES `universidade`.`anos` (`ANO`)
168 ON DELETE NO ACTION
169 ON UPDATE NO ACTION,
170 CONSTRAINT `COD_DISC`
171 FOREIGN KEY (`COD_DISC`)
172 REFERENCES `universidade`.`disciplinas` (`COD_DISC`)
173 ON DELETE NO ACTION
174 ON UPDATE NO ACTION,
175 CONSTRAINT `COD_PROF`
176 FOREIGN KEY (`COD_PROF`)
177 REFERENCES `universidade`.`professores` (`COD_PROF`)
178 ON DELETE NO ACTION
179 ON UPDATE NO ACTION,
180 CONSTRAINT `COD_TURMA`
181 FOREIGN KEY (`COD_TURMA`)
182 REFERENCES `universidade`.`cod_turma` (`COD_TURMA`)
183 ON DELETE NO ACTION
184 ON UPDATE NO ACTION)
185ENGINE = InnoDB
186DEFAULT CHARACTER SET = latin1;
187
188
189-- -----------------------------------------------------
190-- Table `universidade`.`historico`
191-- -----------------------------------------------------
192CREATE TABLE IF NOT EXISTS `universidade`.`historico` (
193 `MAT` VARCHAR(10) NOT NULL,
194 `COD_DISC` VARCHAR(10) NOT NULL,
195 `COD_TURMA` VARCHAR(45) NOT NULL,
196 `COD_PROF` VARCHAR(45) NOT NULL,
197 `ANO` VARCHAR(45) NOT NULL,
198 `Frequencia` INT(11) NOT NULL,
199 `Nota` INT(11) NOT NULL,
200 INDEX `MAT_idx` (`MAT` ASC),
201 INDEX `COD_TURMA_idx` (`COD_TURMA` ASC),
202 INDEX `COD_DISC_FK_idx` (`COD_DISC` ASC),
203 INDEX `ANO_FK_idx` (`ANO` ASC),
204 INDEX `COD_PROF_FK_idx` (`COD_PROF` ASC),
205 CONSTRAINT `ANO_FK`
206 FOREIGN KEY (`ANO`)
207 REFERENCES `universidade`.`turma` (`ANO`)
208 ON DELETE NO ACTION
209 ON UPDATE NO ACTION,
210 CONSTRAINT `COD_DISC_FK`
211 FOREIGN KEY (`COD_DISC`)
212 REFERENCES `universidade`.`turma` (`COD_DISC`)
213 ON DELETE NO ACTION
214 ON UPDATE NO ACTION,
215 CONSTRAINT `COD_PROF_FK`
216 FOREIGN KEY (`COD_PROF`)
217 REFERENCES `universidade`.`turma` (`COD_PROF`)
218 ON DELETE NO ACTION
219 ON UPDATE NO ACTION,
220 CONSTRAINT `COD_TURMA_FK`
221 FOREIGN KEY (`COD_TURMA`)
222 REFERENCES `universidade`.`turma` (`COD_TURMA`)
223 ON DELETE NO ACTION
224 ON UPDATE NO ACTION,
225 CONSTRAINT `MAT_FK`
226 FOREIGN KEY (`MAT`)
227 REFERENCES `universidade`.`alunos` (`Mat`)
228 ON DELETE NO ACTION
229 ON UPDATE NO ACTION)
230ENGINE = InnoDB
231DEFAULT CHARACTER SET = latin1;
232
233USE `test` ;
234
235-- -----------------------------------------------------
236-- Table `test`.`produtos`
237-- -----------------------------------------------------
238CREATE TABLE IF NOT EXISTS `test`.`produtos` (
239 `ID_NF` INT(11) NOT NULL,
240 `ID_ITEM` INT(11) NOT NULL,
241 `COD_PROD` INT(11) NOT NULL,
242 `VALOR_UNIT` FLOAT NOT NULL,
243 `QUANTIDADE` INT(11) NOT NULL,
244 `DESCONTO` INT(11) NULL DEFAULT NULL)
245ENGINE = InnoDB
246DEFAULT CHARACTER SET = latin1;
247
248
249SET SQL_MODE=@OLD_SQL_MODE;
250SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
251SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;