Trabalho Universidade

1. Escrever as instruções SQL de criação das tabelas.

DROP TABLE IF EXISTS Depto;
DROP TABLE IF EXISTS PreReq;
DROP TABLE IF EXISTS Turma;
DROP TABLE IF EXISTS Horario;
DROP TABLE IF EXISTS Predio;
DROP TABLE IF EXISTS Sala;
DROP TABLE IF EXISTS CapacSala;
DROP TABLE IF EXISTS ProfTurma;
DROP TABLE IF EXISTS Titulacao;

Create table Depto( CodDepto int NOT NULL, NomeDepto varchar(50) NOT NULL, PRIMARY KEY (CodDepto)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Create table PreReq( CodDepto int NOT NULL, NumDisc int NOT NULL, CodDeptoPreReq int, NumDiscPreReq int, PRIMARY KEY (CodDepto,NumDisc), FOREIGN KEY (CodDepto) REFERENCES Depto(CodDepto)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Create table Turma( AnoSem datetime NOT NULL, CodDepto int NOT NULL, NumDisc int NOT NULL, SiglaTur varchar(10) NOT NULL, CapacOfer int, PRIMARY KEY (AnoSem,CodDepto,NumDisc,SiglaTur), FOREIGN KEY (CodDepto) REFERENCES Disciplina(CodDepto), FOREIGN KEY (NumDisc) REFERENCES Disciplina(NumDisc)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Create table Horario( AnoSem datetime NOT NULL, CodDepto int NOT NULL, NumDisc int NOT NULL, SiglaTur varchar(10) NOT NULL, DiaSem datetime, HoraIncio datetime, NumHoras int, CodPred int, NumSala int, PRIMARY KEY (AnoSem,CodDepto,NumDisc,SiglaTur), FOREIGN KEY (AnoSem) REFERENCES Turma(AnoSem), FOREIGN KEY (CodDepto) REFERENCES Turma(CodDepto), FOREIGN KEY (NumDisc) REFERENCES Turma(NumDisc), FOREIGN KEY (SiglaTur) REFERENCES Turma(SiglaTur), FOREIGN KEY (CodPred) REFERENCES Sala(CodPred), FOREIGN KEY (NumSala) REFERENCES Sala(NumSala)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Create table Predio( CodPred int NOT NULL, NomePred varchar(50) NOT NULL, PRIMARY KEY (CodPred)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Create table Sala( CodPred int NOT NULL, NumSala int NOT NULL, CapacSala int, PRIMARY KEY (CodPred, NumSala), FOREIGN KEY (CodPred) REFERENCES Predio(CodPred)
) ENGINE=InnoDB