create table DEPTO

(

 CODDEPTO CHAR(5)  not null,

 NOMEDEPTO VARCHAR(40) not null,

 constraint P_IDENTIFIER_1 primary key (CODDEPTO));



create table DISCIPLINA( 

CODDEPTO CHAR(5)  not null, 

NUMDISC INTEGER  not null, 

NOMEDISC VARCHAR(40) not null, 

CREDITOSDISC INTEGER  not null, 

constraint P_IDENTIFIER_2 primary key (CODDEPTO, NUMDISC));



create  index RELATION_20_FK on DISCIPLINA ( CODDEPTO );



create table HORARIO(

 CODDEPTO CHAR(5)  not null,

 NUMDISC INTEGER  not null,

 ANOSEM INTEGER  not null,

 SIGLATUR CHAR(2)  not null,

 DIASEM INTEGER  not null,

 HORAINICIO INTEGER  not null,

 CODPREDIO INTEGER, NUMSALA INTEGER,

 NUMHORAS INTEGER  not null,

 constraint P_IDENTIFIER_3
   primary key

    (CODDEPTO, NUMDISC, ANOSEM, SIGLATUR, DIASEM, HORAINICIO));



create  index RELATION_32_FK on HORARIO

 ( CODDEPTO, NUMDISC, ANOSEM, SIGLATUR);



create  index RELATION_50_FK on HORARIO ( CODPREDIO, NUMSALA);



create table PREREQ(

 CODDEPTO CHAR(5)  not null,

 NUMDISC INTEGER  not null,

 CODDEPTOPREREQ CHAR(5)  not null,

 NUMDISCPREREQ INTEGER  not null,

 constraint P_IDENTIFIER_4 primary key (CODDEPTOPREREQ,

 CODDEPTO, NUMDISC, NUMDISCPREREQ));



create  index TEM_PRE_FK on PREREQ ( CODDEPTO, NUMDISC);



create  index EH_PRE_FK on PREREQ ( CODDEPTOPREREQ, NUMDISCPREREQ);



create table PROFESSOR( CODPROF INTEGER  not null,

 CODDEPTO CHAR(5)  not null,

 CODTIT INTEGER,

 NOMEPROF VARCHAR(40),

 constraint P_IDENTIFIER_5 primary key (CODPROF));



create  index RELATION_56_FK on PROFESSOR ( CODDEPTO);



create  index RELATION_58_FK on PROFESSOR ( CODTIT);



create table PROFTURMA( CODDEPTO CHAR(5)  not null,

 NUMDISC INTEGER  not null,

 ANOSEM INTEGER  not null,

 SIGLATUR CHAR(2)  not null,

 CODPROF INTEGER  not null,

 constraint P_IDENTIFIER_6 primary key (CODDEPTO, NUMDISC, ANOSEM, SIGLATUR, CODPROF));



create  index PROFTURMA_FK on PROFTURMA ( CODDEPTO , NUMDISC , ANOSEM , SIGLATUR );



create  index PROFTURMA2_FK on PROFTURMA ( CODPROF );



create table SALA(CODPREDIO INTEGER  not null,

 NUMSALA INTEGER  not null,

 DESCRICAOSALA VARCHAR(40) not null,

 CAPACIDADE INTEGER,

 constraint P_IDENTIFIER_7 primary key (CODPREDIO, NUMSALA));



create  index RELATION_49_FK on SALA ( CODPREDIO );



create table PREDIO( CODPREDIO INTEGER  not null,

 DESCRICAOPREDIO VARCHAR(40) not null,

 constraint P_IDENTIFIER_8 primary key (CODPREDIO));



create table TITULACAO( CODTIT INTEGER  not null,

 NOMETIT VARCHAR(40) not null,

 constraint P_IDENTIFIER_9 primary key (CODTIT));



create table TURMA( CODDEPTO CHAR(5)  not null,

 NUMDISC INTEGER  not null,

 ANOSEM INTEGER  not null,

 SIGLATUR CHAR(2)  not null,

 CAPACIDADE INTEGER,

 constraint P_IDENTIFIER_10 primary key (CODDEPTO, NUMDISC, ANOSEM, SIGLATUR));



create  index RELATION_31_FK on TURMA ( CODDEPTO , NUMDISC );


alter table DISCIPLINA

 add constraint F_DISC_DEPTO

 foreign key (CODDEPTO) references DEPTO (CODDEPTO)

 on delete restrict;



alter table HORARIO

 add constraint F_HOR_TURMA

 foreign key (CODDEPTO, NUMDISC, ANOSEM, SIGLATUR) references TURMA (CODDEPTO, NUMDISC, ANOSEM, SIGLATUR)

 on delete cascade;



alter table HORARIO

 add constraint F_SALA_TURMA

 foreign key (CODPREDIO, NUMSALA) references SALA (CODPREDIO, NUMSALA)

 on delete restrict;



alter table PREREQ

 add constraint F_EH_PRE

 foreign key (CODDEPTOPREREQ, NUMDISCPREREQ) references DISCIPLINA (CODDEPTO, NUMDISC)

 on delete restrict;



alter table PREREQ

 add constraint F_TEM_PRE

 foreign key (CODDEPTO, NUMDISC) references DISCIPLINA (CODDEPTO, NUMDISC)

 on delete restrict;



alter table PROFESSOR

 add constraint F_DEPTO_PROF

 foreign key (CODDEPTO) references DEPTO (CODDEPTO)

 on delete restrict;



alter table PROFESSOR

 add constraint F_PROF_TIT

 foreign key (CODTIT) references TITULACAO (CODTIT)

 on delete restrict;



alter table PROFTURMA

 add constraint F_PROFTURMA_PROF

 foreign key (CODPROF) references PROFESSOR (CODPROF)

 on delete restrict;



alter table PROFTURMA

 add constraint F_PROFTURMA_TUR

 foreign key (CODDEPTO, NUMDISC, ANOSEM, SIGLATUR) references TURMA (CODDEPTO, NUMDISC, ANOSEM, SIGLATUR)

 on delete restrict;



alter table TURMA

 add constraint F_TURMA_DISC
 foreign key (CODDEPTO, NUMDISC) references DISCIPLINA (CODDEPTO, NUMDISC)

 on delete restrict;