Y8PRrNY7

1CREATE SCHEMA IF NOT EXISTS 'Teaching' DEFAULT CHARACTER SET utf8 ;
2USE 'Teaching' ;
3
4-- -----------------------------------------------------
5-- Table 'Teaching'.'USUARIO'
6-- -----------------------------------------------------
7CREATE TABLE IF NOT EXISTS 'Teaching'.'USUARIO' (
8  'idusuario' INT NOT NULL AUTO_INCREMENT,
9  'nome' VARCHAR(255) NULL,
10  'cpf' VARCHAR(11) NULL,
11  'email' VARCHAR(255) NULL,
12  'senha' VARCHAR(255) NULL,
13  PRIMARY KEY ('idusuario'))
14ENGINE = InnoDB
15DEFAULT CHARACTER SET = utf8;
16
17
18-- -----------------------------------------------------
19-- Table 'Teaching'.'TURMA'
20-- -----------------------------------------------------
21CREATE TABLE IF NOT EXISTS 'Teaching'.'TURMA' (
22  'idturma' INT NOT NULL AUTO_INCREMENT,
23  'serie' CHAR(2) NULL,
24  PRIMARY KEY ('idturma'))
25ENGINE = InnoDB
26DEFAULT CHARACTER SET = utf8;
27
28
29-- -----------------------------------------------------
30-- Table 'Teaching'.'ALUNO'
31-- -----------------------------------------------------
32CREATE TABLE IF NOT EXISTS 'Teaching'.'ALUNO' (
33  'idaluno' INT NOT NULL AUTO_INCREMENT,
34  'matricula' TINYINT(4) NULL,
35  'ativo' ENUM('ativo', 'inativo') NULL,
36  'id_usuario' INT NOT NULL,
37  'id_turma' INT NOT NULL,
38  PRIMARY KEY ('idaluno'),
39  INDEX 'fk_aluno_usuario_idx' ('id_usuario' ASC),
40  INDEX 'fk_aluno_turma_idx' ('id_turma' ASC),
41  CONSTRAINT 'fk_Aluno_USUARIO'
42    FOREIGN KEY ('id_usuario')
43    REFERENCES 'Teaching'.'USUARIO' ('idusuario')
44    ON DELETE NO ACTION
45    ON UPDATE NO ACTION,
46  CONSTRAINT 'fk_ALUNO_TURMA'
47    FOREIGN KEY ('id_turma')
48    REFERENCES 'Teaching'.'TURMA' ('idturma')
49    ON DELETE NO ACTION
50    ON UPDATE NO ACTION)
51ENGINE = InnoDB
52DEFAULT CHARACTER SET = utf8;
53
54
55-- -----------------------------------------------------
56-- Table 'Teaching'.'PROFESSOR'
57-- -----------------------------------------------------
58CREATE TABLE IF NOT EXISTS 'Teaching'.'PROFESSOR' (
59  'idprofessor' INT NOT NULL AUTO_INCREMENT,
60  'especializacao' VARCHAR(255) NULL,
61  'id_usuario' INT NOT NULL,
62  'ativo' ENUM('ativo', 'inativo') NULL,
63  PRIMARY KEY ('idprofessor'),
64  INDEX 'fk_professor_usuario_idx' ('id_usuario' ASC),
65  CONSTRAINT 'fk_PROFESSOR_USUARIO1'
66    FOREIGN KEY ('id_usuario')
67    REFERENCES 'Teaching'.'USUARIO' ('idusuario')
68    ON DELETE NO ACTION
69    ON UPDATE NO ACTION)
70ENGINE = InnoDB
71DEFAULT CHARACTER SET = utf8;
72
73
74-- -----------------------------------------------------
75-- Table 'Teaching'.'DISCIPLINA'
76-- -----------------------------------------------------
77CREATE TABLE IF NOT EXISTS 'Teaching'.'DISCIPLINA' (
78  'iddisciplina' INT NOT NULL AUTO_INCREMENT,
79  'nome' VARCHAR(255) NULL,
80  'observacao' TEXT CHARACTER SET 'utf8' NULL,
81  PRIMARY KEY ('iddisciplina'))
82ENGINE = InnoDB
83DEFAULT CHARACTER SET = utf8;
84
85
86-- -----------------------------------------------------
87-- Table 'Teaching'.'PROFESSOR_DISCIPLINA'
88-- -----------------------------------------------------
89CREATE TABLE IF NOT EXISTS 'Teaching'.'PROFESSOR_DISCIPLINA' (
90  'id_professor' INT NOT NULL,
91  'id_disciplina' INT NOT NULL,
92  PRIMARY KEY ('id_professor', 'id_disciplina'),
93  INDEX 'fk_disciplinaProfessor_disciplina_idx' ('id_disciplina' ASC),
94  INDEX 'fk_disciplinaProfessor_professor_idx' ('id_professor' ASC),
95  CONSTRAINT 'fk_PROFESSOR_has_DISCIPLINA_PROFESSOR1'
96    FOREIGN KEY ('id_professor')
97    REFERENCES 'Teaching'.'PROFESSOR' ('idprofessor')
98    ON DELETE NO ACTION
99    ON UPDATE NO ACTION,
100  CONSTRAINT 'fk_PROFESSOR_has_DISCIPLINA_DISCIPLINA1'
101    FOREIGN KEY ('id_disciplina')
102    REFERENCES 'Teaching'.'DISCIPLINA' ('iddisciplina')
103    ON DELETE NO ACTION
104    ON UPDATE NO ACTION)
105ENGINE = InnoDB
106DEFAULT CHARACTER SET = utf8;
107
108
109-- -----------------------------------------------------
110-- Table 'Teaching'.'DISCIPLINA_TURMA'
111-- -----------------------------------------------------
112CREATE TABLE IF NOT EXISTS 'Teaching'.'DISCIPLINA_TURMA' (
113  'id_disciplina' INT NOT NULL,
114  'id_turma' INT NOT NULL,
115  PRIMARY KEY ('id_disciplina', 'id_turma'),
116  INDEX 'fk_disciplinaturma_turma_idx' ('id_turma' ASC),
117  INDEX 'fk_disciplinaturma_disciplina_idx' ('id_disciplina' ASC),
118  CONSTRAINT 'fk_DISCIPLINA_has_TURMA_DISCIPLINA1'
119    FOREIGN KEY ('id_disciplina')
120    REFERENCES 'Teaching'.'DISCIPLINA' ('iddisciplina')
121    ON DELETE NO ACTION
122    ON UPDATE NO ACTION,
123  CONSTRAINT 'fk_DISCIPLINA_has_TURMA_TURMA1'
124    FOREIGN KEY ('id_turma')
125    REFERENCES 'Teaching'.'TURMA' ('idturma')
126    ON DELETE NO ACTION
127    ON UPDATE NO ACTION)
128ENGINE = InnoDB
129DEFAULT CHARACTER SET = utf8;