PID_BD_SCRIPT_CRIACAO_BASE

•

UNOESTE

0

Renato Dolce

05/09/2013

Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original

CREATE TABLE ALUNO (
 RA VARCHAR2(20) NOT NULL,
 PES_COD NUMBER(6) NOT NULL,
 CUR_COD_INICIAL NUMBER(4) NOT NULL,
 TERMO_INICIAL NUMBER(2) NOT NULL,
 SEMESTRE_INICIAL CHAR(6) NOT NULL,
 DTCAD DATE NOT NULL
);
ALTER TABLE ALUNO
 ADD ( PRIMARY KEY (RA) ) ;
CREATE TABLE ALUNO_TURMA_TERMO_DISC (
 CUR_COD NUMBER(4) NOT NULL,
 SEMESTRE CHAR(6) NOT NULL,
 TERMO NUMBER(2) NOT NULL,
 DIS_COD NUMBER(4) NOT NULL,
 LETRA_TURMA CHAR(1) NOT NULL,
 MAT_COD NUMBER(7) NOT NULL
);
ALTER TABLE ALUNO_TURMA_TERMO_DISC
 ADD ( PRIMARY KEY (CUR_COD, TERMO, SEMESTRE, LETRA_TURMA, 
 DIS_COD, MAT_COD) ) ;
CREATE TABLE AREAS (
 AREA_COD NUMBER(2) NOT NULL,
 AREA_NOME VARCHAR2(40) NOT NULL
);
ALTER TABLE AREAS
 ADD ( PRIMARY KEY (AREA_COD) ) ;
CREATE TABLE AREAS_FUNC_PROF (
 AREA_COD NUMBER(2) NOT NULL,
 FUN_COD NUMBER(7) NOT NULL
);
ALTER TABLE AREAS_FUNC_PROF
 ADD ( PRIMARY KEY (AREA_COD, FUN_COD) ) ;
CREATE TABLE CAMPUS_POLO (
 CP_COD NUMBER(2) NOT NULL,
 CP_NOME VARCHAR2(40) NOT NULL,
 CP_TIPO CHAR(1) NOT NULL
 CHECK (CP_TIPO IN ('C','P'))
);
ALTER TABLE CAMPUS_POLO
 ADD ( PRIMARY KEY (CP_COD) ) ;
CREATE TABLE CIDADE (
 CID_COD NUMBER(5) NOT NULL,
 CID_NOME VARCHAR2(40) NOT NULL,
 CID_UF CHAR(2) NOT NULL
);
ALTER TABLE CIDADE
 ADD ( PRIMARY KEY (CID_COD) ) ;
CREATE TABLE CURSO (
 CUR_COD NUMBER(4) NOT NULL,
 FAC_COD NUMBER(2) NOT NULL,
 CUR_NOME VARCHAR2(100) NOT NULL
);
ALTER TABLE CURSO
 ADD ( PRIMARY KEY (CUR_COD) ) ;
CREATE TABLE DISCIPLINA (
 DIS_COD NUMBER(4) NOT NULL,
 DIS_NOME VARCHAR2(60) NOT NULL
);
ALTER TABLE DISCIPLINA
 ADD ( PRIMARY KEY (DIS_COD) ) ;
CREATE TABLE DISCIPLINA_AREAS (
 DIS_COD NUMBER(4) NOT NULL,
 AREA_COD NUMBER(2) NOT NULL
);
ALTER TABLE DISCIPLINA_AREAS
 ADD ( PRIMARY KEY (DIS_COD, AREA_COD) ) ;
CREATE TABLE ENDERECO (
 PES_COD NUMBER(6) NOT NULL,
 END_SEQ NUMBER(2) NOT NULL,
 TPE_COD NUMBER(1) NOT NULL,
 CID_COD NUMBER(5) NOT NULL,
 END_LOGRADOURO VARCHAR2(100) NOT NULL,
 END_BAIRRO VARCHAR2(40) NOT NULL,
 END_CEP VARCHAR2(8) NOT NULL
);
ALTER TABLE ENDERECO
 ADD ( PRIMARY KEY (PES_COD, END_SEQ) ) ;
CREATE TABLE FACULDADE (
 FAC_COD NUMBER(2) NOT NULL,
 FAC_NOME VARCHAR2(100) NOT NULL
);
ALTER TABLE FACULDADE
 ADD ( PRIMARY KEY (FAC_COD) ) ;
CREATE TABLE FALTAS (
 CUR_COD NUMBER(4) NOT NULL,
 TERMO NUMBER(2) NOT NULL,
 DIS_COD NUMBER(4) NOT NULL,
 LETRA_TURMA CHAR(1) NOT NULL,
 SEMESTRE CHAR(6) NOT NULL,
 DIA_SEM NUMBER(1) NOT NULL
 CHECK (DIA_SEM BETWEEN 1 AND 7),
 MAT_COD NUMBER(7) NOT NULL,
 DATA_FALTA DATE NOT NULL
);
ALTER TABLE FALTAS
 ADD ( PRIMARY KEY (CUR_COD, TERMO, DIS_COD, LETRA_TURMA, 
 SEMESTRE, DIA_SEM, MAT_COD) ) ;
CREATE TABLE FONE (
 PES_COD NUMBER(6) NOT NULL,
 F_SEQ NUMBER(2) NOT NULL,
 TPF_COD NUMBER(1) NOT NULL,
 F_DDI NUMBER(2) NULL,
 F_DDD NUMBER(2) NULL,
 F_NUMERO VARCHAR2(12) NOT NULL,
 F_OBS VARCHAR2(20) NULL
);
ALTER TABLE FONE
 ADD ( PRIMARY KEY (PES_COD, F_SEQ) ) ;
CREATE TABLE FUNCAO (
 FC_COD NUMBER(3) NOT NULL,
 FC_DESC VARCHAR2(30) NOT NULL
);
ALTER TABLE FUNCAO
 ADD ( PRIMARY KEY (FC_COD) ) ;
CREATE TABLE HORARIOS (
 CUR_COD NUMBER(4) NOT NULL,
 TERMO NUMBER(2) NOT NULL,
 SEMESTRE CHAR(6) NOT NULL,
 DIS_COD NUMBER(4) NOT NULL,
 LETRA_TURMA CHAR(1) NOT NULL,
 DIA_SEM NUMBER(1) NOT NULL
 CHECK (DIA_SEM BETWEEN 1 AND 7),
 HORA_INI CHAR(5) NOT NULL
 CHECK (HORA_INI BETWEEN '00:00' AND '23:59'),
 HORA_FIM CHAR(5) NOT NULL
 CHECK (HORA_FIM BETWEEN '00:00' AND '23:59')
);
ALTER TABLE HORARIOS
 ADD ( PRIMARY KEY (CUR_COD, TERMO, SEMESTRE, DIS_COD, 
 LETRA_TURMA, DIA_SEM) ) ;
CREATE TABLE LOCAL (
 LOC_COD NUMBER(3) NOT NULL,
 CP_COD NUMBER(2) NOT NULL,
 TL_COD NUMBER(2) NOT NULL,
 LOC_DESC VARCHAR2(60) NOT NULL,
 LOC_CAPACIDADE NUMBER(4) NOT NULL
);
ALTER TABLE LOCAL
 ADD ( PRIMARY KEY (CP_COD, LOC_COD) ) ;
CREATE TABLE MATRICULA (
 MAT_COD NUMBER(7) NOT NULL,
 CUR_COD NUMBER(4) NOT NULL,
 RA VARCHAR2(20) NOT NULL,
 TERMO NUMBER(2) NOT NULL,
 SEMESTRE CHAR(6) NOT NULL,
 MAT_DATA DATE NOT NULL,
 MAT_VALOR NUMBER(12,2) NOT NULL
);
ALTER TABLE MATRICULA
 ADD ( PRIMARY KEY (MAT_COD) ) ;
CREATE TABLE MENSALIDADE (
 MAT_COD NUMBER(7) NOT NULL,
 MEN_REF_MES CHAR(7) NOT NULL,
 MEN_VALOR NUMBER(12,2) NOT NULL,
 MEN_DTVENCTO DATE NOT NULL,
 MEN_DTPAGTO DATE NULL,
 MEN_VLPAGO NUMBER(12,2) NULL
);
ALTER TABLE MENSALIDADE
 ADD ( PRIMARY KEY (MAT_COD, MEN_REF_MES) ) ;
CREATE TABLE NOTAS (
 CUR_COD NUMBER(4) NOT NULL,
 TERMO NUMBER(2) NOT NULL,
 DIS_COD NUMBER(4) NOT NULL,
 LETRA_TURMA CHAR(1) NOT NULL,
 SEMESTRE CHAR(6) NOT NULL,
 BIMESTRE NUMBER(1) NOT NULL,
 MAT_COD NUMBER(7) NOT NULL,
 NOTA NUMBER(3,1) NOT NULL
);
ALTER TABLE NOTAS
 ADD ( PRIMARY KEY (CUR_COD, TERMO, DIS_COD, LETRA_TURMA, 
 SEMESTRE, BIMESTRE, MAT_COD) ) ;
CREATE TABLE PESFIS (
 PES_COD NUMBER(6) NOT NULL,
 PES_RG VARCHAR2(20) NULL,
 PES_CPF CHAR(11) NULL,
 PES_DTNASCTO DATE NOT NULL
);
ALTER TABLE PESFIS
 ADD ( PRIMARY KEY (PES_COD) ) ;
CREATE TABLE PESJUR (
 PES_COD NUMBER(6) NOT NULL,
 PES_CNPJ CHAR(14) NOT NULL,
 PES_IE VARCHAR2(25) NULL
);
ALTER TABLE PESJUR
 ADD ( PRIMARY KEY (PES_COD) ) ;
CREATE TABLE PESSOA (
 PES_COD NUMBER(6) NOT NULL,
 PES_NOME VARCHAR2(100) NOT NULL
);
ALTER TABLE PESSOA
 ADD ( PRIMARY KEY (PES_COD) ) ;
CREATE TABLE REGISTRO_FUNC_PROF (
 FUN_COD NUMBER(7) NOT NULL,
 PES_COD NUMBER(6) NOT NULL,
 FC_COD NUMBER(3) NOT NULL,
 FUN_DTADMISSAO DATE NOT NULL,
 FUN_DTDEMISSAO DATE NULL
);
ALTER TABLE REGISTRO_FUNC_PROF
 ADD ( PRIMARY KEY (FUN_COD) ) ;
CREATE TABLE TERMO (
 CUR_COD
NUMBER(4) NOT NULL,
 TERMO NUMBER(2) NOT NULL,
 SEMESTRE CHAR(6) NOT NULL,
 TER_DTINICIO DATE NOT NULL,
 TER_DTFIM DATE NOT NULL
);
ALTER TABLE TERMO
 ADD ( PRIMARY KEY (CUR_COD, TERMO, SEMESTRE) ) ;
CREATE TABLE TERMO_DISC (
 CUR_COD NUMBER(4) NOT NULL,
 TERMO NUMBER(2) NOT NULL,
 DIS_COD NUMBER(4) NOT NULL,
 SEMESTRE CHAR(6) NOT NULL
);
ALTER TABLE TERMO_DISC
 ADD ( PRIMARY KEY (CUR_COD, TERMO, SEMESTRE, DIS_COD) ) ;
CREATE TABLE TIPO_LOCAL (
 TL_COD NUMBER(2) NOT NULL,
 TL_DESC VARCHAR2(40) NOT NULL
);
ALTER TABLE TIPO_LOCAL
 ADD ( PRIMARY KEY (TL_COD) ) ;
CREATE TABLE TPEND (
 TPE_COD NUMBER(1) NOT NULL,
 TPE_DESC VARCHAR2(20) NOT NULL
);
ALTER TABLE TPEND
 ADD ( PRIMARY KEY (TPE_COD) ) ;
CREATE TABLE TPFONE (
 TPF_COD NUMBER(1) NOT NULL,
 TPF_DESC VARCHAR2(20) NOT NULL
);
ALTER TABLE TPFONE
 ADD ( PRIMARY KEY (TPF_COD) ) ;
CREATE TABLE TURMA_FUNC_PROF (
 CUR_COD NUMBER(4) NOT NULL,
 TERMO NUMBER(2) NOT NULL,
 DIS_COD NUMBER(4) NOT NULL,
 SEMESTRE CHAR(6) NOT NULL,
 LETRA_TURMA CHAR(1) NOT NULL,
 FUN_COD NUMBER(7) NOT NULL
);
ALTER TABLE TURMA_FUNC_PROF
 ADD ( PRIMARY KEY (CUR_COD, TERMO, SEMESTRE, LETRA_TURMA, 
 DIS_COD, FUN_COD) ) ;
CREATE TABLE TURMA_TERMO_DISC (
 CUR_COD NUMBER(4) NOT NULL,
 TERMO NUMBER(2) NOT NULL,
 DIS_COD NUMBER(4) NOT NULL,
 SEMESTRE CHAR(6) NOT NULL,
 LETRA_TURMA CHAR(1) NOT NULL,
 LOC_COD NUMBER(3) NULL,
 CP_COD NUMBER(2) NULL,
 CAPACIDADE NUMBER(4) NOT NULL
);
ALTER TABLE TURMA_TERMO_DISC
 ADD ( PRIMARY KEY (CUR_COD, TERMO, SEMESTRE, LETRA_TURMA, 
 DIS_COD) ) ;
ALTER TABLE ALUNO
 ADD ( FOREIGN KEY (CUR_COD_INICIAL, TERMO_INICIAL, 
 SEMESTRE_INICIAL)
 REFERENCES TERMO ) ;
ALTER TABLE ALUNO
 ADD ( FOREIGN KEY (PES_COD)
 REFERENCES PESFIS ) ;
ALTER TABLE ALUNO_TURMA_TERMO_DISC
 ADD ( FOREIGN KEY (MAT_COD)
 REFERENCES MATRICULA ) ;
ALTER TABLE ALUNO_TURMA_TERMO_DISC
 ADD ( FOREIGN KEY (CUR_COD, TERMO, SEMESTRE, LETRA_TURMA, 
 DIS_COD)
 REFERENCES TURMA_TERMO_DISC ) ;
ALTER TABLE AREAS_FUNC_PROF
 ADD ( FOREIGN KEY (FUN_COD)
 REFERENCES REGISTRO_FUNC_PROF ) ;
ALTER TABLE AREAS_FUNC_PROF
 ADD ( FOREIGN KEY (AREA_COD)
 REFERENCES AREAS ) ;
ALTER TABLE CURSO
 ADD ( FOREIGN KEY (FAC_COD)
 REFERENCES FACULDADE ) ;
ALTER TABLE DISCIPLINA_AREAS
 ADD ( FOREIGN KEY (AREA_COD)
 REFERENCES AREAS ) ;
ALTER TABLE DISCIPLINA_AREAS
 ADD ( FOREIGN KEY (DIS_COD)
 REFERENCES DISCIPLINA ) ;
ALTER TABLE ENDERECO
 ADD ( FOREIGN KEY (TPE_COD)
 REFERENCES TPEND ) ;
ALTER TABLE ENDERECO
 ADD ( FOREIGN KEY (CID_COD)
 REFERENCES CIDADE ) ;
ALTER TABLE ENDERECO
 ADD ( FOREIGN KEY (PES_COD)
 REFERENCES PESSOA ) ;
ALTER TABLE FALTAS
 ADD ( FOREIGN KEY (CUR_COD, TERMO, SEMESTRE, LETRA_TURMA, 
 DIS_COD, MAT_COD)
 REFERENCES ALUNO_TURMA_TERMO_DISC ) ;
ALTER TABLE FALTAS
 ADD ( FOREIGN KEY (CUR_COD, TERMO, SEMESTRE, DIS_COD, 
 LETRA_TURMA, DIA_SEM)
 REFERENCES HORARIOS ) ;
ALTER TABLE FONE
 ADD ( FOREIGN KEY (TPF_COD)
 REFERENCES TPFONE ) ;
ALTER TABLE FONE
 ADD ( FOREIGN KEY (PES_COD)
 REFERENCES PESSOA ) ;
ALTER TABLE HORARIOS
 ADD ( FOREIGN KEY (CUR_COD, TERMO, SEMESTRE, LETRA_TURMA, 
 DIS_COD)
 REFERENCES TURMA_TERMO_DISC ) ;
ALTER TABLE LOCAL
 ADD ( FOREIGN KEY (CP_COD)
 REFERENCES CAMPUS_POLO ) ;
ALTER TABLE LOCAL
 ADD ( FOREIGN KEY (TL_COD)
 REFERENCES TIPO_LOCAL ) ;
ALTER TABLE MATRICULA
 ADD ( FOREIGN KEY (RA)
 REFERENCES ALUNO ) ;
ALTER TABLE MATRICULA
 ADD ( FOREIGN KEY (CUR_COD, TERMO, SEMESTRE)
 REFERENCES TERMO ) ;
ALTER TABLE MENSALIDADE
 ADD ( FOREIGN KEY (MAT_COD)
 REFERENCES MATRICULA ) ;
ALTER TABLE NOTAS
 ADD ( FOREIGN KEY (CUR_COD, TERMO, SEMESTRE, LETRA_TURMA, 
 DIS_COD, MAT_COD)
 REFERENCES ALUNO_TURMA_TERMO_DISC ) ;
ALTER TABLE PESFIS
 ADD ( FOREIGN KEY (PES_COD)
 REFERENCES PESSOA
 ON DELETE CASCADE ) ;
ALTER TABLE PESJUR
 ADD ( FOREIGN KEY (PES_COD)
 REFERENCES PESSOA
 ON DELETE CASCADE ) ;
ALTER TABLE REGISTRO_FUNC_PROF
 ADD ( FOREIGN KEY (FC_COD)
 REFERENCES FUNCAO ) ;
ALTER TABLE REGISTRO_FUNC_PROF
 ADD ( FOREIGN KEY (PES_COD)
 REFERENCES PESFIS ) ;
ALTER TABLE TERMO
 ADD ( FOREIGN KEY (CUR_COD)
 REFERENCES CURSO ) ;
ALTER TABLE TERMO_DISC
 ADD ( FOREIGN KEY (CUR_COD, TERMO, SEMESTRE)
 REFERENCES TERMO ) ;
ALTER TABLE TERMO_DISC
 ADD ( FOREIGN KEY (DIS_COD)
 REFERENCES DISCIPLINA ) ;
ALTER TABLE TURMA_FUNC_PROF
 ADD ( FOREIGN KEY (FUN_COD)
 REFERENCES REGISTRO_FUNC_PROF ) ;
ALTER TABLE TURMA_FUNC_PROF
 ADD ( FOREIGN KEY (CUR_COD, TERMO, SEMESTRE, LETRA_TURMA, 
 DIS_COD)
 REFERENCES TURMA_TERMO_DISC ) ;
ALTER TABLE TURMA_TERMO_DISC
 ADD ( FOREIGN KEY (CP_COD, LOC_COD)
 REFERENCES LOCAL ) ;
ALTER TABLE TURMA_TERMO_DISC
 ADD ( FOREIGN KEY (CUR_COD, TERMO, SEMESTRE, DIS_COD)
 REFERENCES TERMO_DISC ) ;