DESAFIO! Modelagem de DB da Escola Trevinho - DEVInHouse Teltec & BRy

01 - DDL - Criacao das tabelas iniciais (WIP 03-02-2022).sql

create type periodo_enum  as enum ('matutino', 'vespertino', 'noturno')

create table alunos (
	id serial primary key,
	nome varchar(80) not null,
	sobrenome varchar(80) not null,
	telefone varchar(15) not null,
	data_nascimento date not null,
	turma_id int not null references turmas
);

create table turmas (
	id serial primary key,
	periodo periodo_enum
);

create table materias (
	id serial primary key,
	titulo varchar(80) not null,
	professor_id int references professores on delete set null
);

create table professores (
	id serial primary key,
	nome varchar(80) not null,
	sobrenome varchar(80) not null,
);

create table gradedia (
	id serial primary key,
	dia_semana char(3) not null,
	turma_id int references turmas on delete cascade
);

create table aula (
	id serial primary key,
	gradedia_id int references gradedia on delete cascade,
	materia_id int references materias on delete cascade,
	hora_inicio time not null,
	hora_fim time not null
);

create table boletins (
	id serial primary key,
);