---------------------------------------------
	-- Criar Tabelas e Relacionamentos
---------------------------------------------
CREATE TABLE DEPTO (
	CodDepto 		VARCHAR(50),
	NomeDepto 		VARCHAR(50),
	PRIMARY KEY		(CodDepto)
);

CREATE TABLE DISCIPLINA (
	CodDepto 		VARCHAR(50),
	NumDisc 		VARCHAR(50),
	CreditosDisc	INTEGER,
	NomeDisc 		VARCHAR(50),
	PRIMARY KEY		(CodDepto,NumDisc),
	FOREIGN KEY		(CodDepto) REFERENCES DEPTO (CodDepto)
);

CREATE TABLE PREREQ (
	CodDepto 		VARCHAR(50),
	NumDisc 		VARCHAR(50),
	NumDiscPreReq 	VARCHAR(50),
	CodDeptoPreReq 	VARCHAR(50),
	PRIMARY KEY		(CodDepto,NumDisc,NumDiscPreReq,CodDeptoPreReq),
	FOREIGN KEY		(CodDepto,NumDisc) REFERENCES DISCIPLINA (CodDepto,NumDisc)
);

CREATE TABLE TURMA (
	AnoSem 			VARCHAR(50),
	CodDepto 		VARCHAR(50),
	NumDisc 		VARCHAR(50),
	SiglaTur 		VARCHAR(50),
	CapacTur 		INTEGER,
	PRIMARY KEY		(AnoSem,CodDepto,NumDisc,SiglaTur),
	FOREIGN KEY		(CodDepto, NumDisc) REFERENCES DISCIPLINA (CodDepto,NumDisc)
);

CREATE TABLE PREDIO (
	CodPred 		INTEGER,
	NomePred 		VARCHAR(50),
	PRIMARY KEY		(CodPred)
);

CREATE TABLE SALA (
	CodPred 		INTEGER,
	NumSala 		INTEGER,
	CapacSala 		INTEGER,
	PRIMARY KEY		(CodPred,NumSala),
	FOREIGN KEY		(CodPred) REFERENCES PREDIO (CodPred)
);

CREATE TABLE HORARIO (
	AnoSem 			VARCHAR(50),
	CodDepto 		VARCHAR(50),
	NumDisc 		VARCHAR(50),
	SiglaTur 		VARCHAR(50),
	DiaSem 			INTEGER,
	HoraInicio 		VARCHAR(50),
	CodPred 		INTEGER,
	NumSala 		INTEGER,
	NumHoras 		VARCHAR(50),
	PRIMARY KEY		(AnoSem,CodDepto,NumDisc,SiglaTur,DiaSem,HoraInicio),
	FOREIGN KEY		(AnoSem,CodDepto,NumDisc,SiglaTur) REFERENCES TURMA (AnoSem,CodDepto,NumDisc,SiglaTur),
	FOREIGN KEY		(CodPred,NumSala) REFERENCES SALA (CodPred,NumSala)
);

CREATE TABLE TITULACAO (
	CodTit 			VARCHAR(50),
	NomeTit 		VARCHAR(50),
	PRIMARY KEY		(CodTit)
);

CREATE TABLE PROFESSOR (
	CodProf 		VARCHAR(50),
	CodTit 			VARCHAR(50),
	CodDepto 		VARCHAR(50),
	NomeProf 		VARCHAR(50),
	PRIMARY KEY		(CodProf),
	FOREIGN KEY		(CodTit) REFERENCES TITULACAO (CodTit),
	FOREIGN KEY		(CodDepto) REFERENCES DEPTO (CodDepto)
);

CREATE TABLE PROFTURMA (
	CodProf 		VARCHAR(50),
	NumDisc 		VARCHAR(50),
	SiglaTur 		VARCHAR(50),
	AnoSem 			VARCHAR(50),
	CodDepto 		VARCHAR(50),
	PRIMARY KEY		(CodProf,NumDisc,SiglaTur,AnoSem,CodDepto),
	FOREIGN KEY		(CodProf) REFERENCES PROFESSOR (CodProf),
	FOREIGN KEY		(AnoSem,CodDepto,NumDisc,SiglaTur) REFERENCES TURMA (AnoSem,CodDepto,NumDisc,SiglaTur)
);