rem Descoberta de Conhecimento em Banco de Dados - prof. Duncan
rem aula pratica de modelagem OLTP
rem 
rem O objetivo desta aula pratica eh modelar e implementar um modelo OLAP
rem floco de neve em um SGBD
rem
rem Para tanto, sera utilizado o banco de dados 
rem Academico (alunos, disciplinas, professores, historico)
rem
rem Este arquivo contem a criacao das tabelas e a producao dos valores 
rem agregados nas mesmas
rem
rem No arquivo estao indicados os pontos onde devem ser feitas as quebras no
rem copy/paste do Notepad (Bloco de Notas) para o sqlplus
rem

prompt tabela disciplinas
Create table disciplinas(
Disciplina char(8),
Denominacao char(50) not null,
Nivel numeric(2),
MediaNotas numeric(3,1),
Primary key(Disciplina));

prompt tabela alunos
Create table alunos(
Matricula numeric(8),
Nome char(25) not null,
Sexo char(1) not null,
Idade numeric(2) not null,
Curso char(5) not null,
Coef_Rend numeric(3,1),
Primary key(Matricula));

prompt tabela professores
Create table professores(
Disciplina char(8),
Turma char(3),
Ano_Sem char(6),
Professor varchar(50) not null,
Num_Alunos numeric(3),
Media_Notas numeric(3,1),
Primary key(Disciplina,Turma,Ano_Sem),
foreign key (Disciplina) references disciplinas);

prompt tabela historico
Create table historico(
Matricula numeric(8),
Disciplina char(8),
Turma char(3) not null,
Ano_Sem char(6) not null,
Nota numeric(3,1) not null,
Primary key(Matricula,Disciplina,Ano_Sem),
Foreign key (Disciplina, Turma, Ano_Sem) references professores,
Foreign key (Matricula) references alunos);

-- ---------------------corte -----------------------

prompt carga de dados em disciplinas e alunos
Insert into disciplinas values('46250-02','Sistemas de Informacao',3,null);
Insert into disciplinas values('46251-04','Engenharia de Software I',4,null);
Insert into disciplinas values('46252-04','Engenharia de Software II',6,null);
Insert into disciplinas values('46256-04','Banco de Dados I',4,null);
Insert into disciplinas values('46257-04','Banco de Dados II',6,null);
Insert into disciplinas values('46258-04','Modelagem Conceitual de Sistemas',5,null);
Insert into disciplinas values('46266-04','Topicos Especiais em Bancos de Dados',7,null);
Insert into disciplinas values('46267-04','Topicos Especiais em Engenharia de Software I',7,null);
Insert into disciplinas values('46268-04','Topicos Especiais em Engenharia de Software II',8,null);
Insert into disciplinas values('46285-04','Engenharia de Software I',4,null);
Insert into disciplinas values('46286-04','Banco de Dados I',4,null);
Insert into alunos values(03106842,'Jovana Fagundes','F',26,'2/520',null);
Insert into alunos values(04103839,'Eduardo Fagundes','M',22,'4/601',null);
Insert into alunos values(04108293,'Marcelo Fagundes','M',22,'4/601',null);
Insert into alunos values(04112046,'Tiago Fagundes','M',22,'4/601',null);
Insert into alunos values(04112192,'Vanessa Fagundes','F',23,'4/601',null);
Insert into alunos values(04201018,'Carla Fagundes','F',21,'4/601',null);
Insert into alunos values(04206067,'Rogerio Fagundes','M',22,'4/601',null);
Insert into alunos values(05280018,'Solange Fagundes','F',26,'4/601',null);
Insert into alunos values(05280023,'Marcelo Fagundes','M',23,'4/601',null);
Insert into alunos values(05280027,'Katia Fagundes','F',20,'4/601',null);
Insert into alunos values(06104543,'Marcos Fagundes','M',26,'2/520',null);

-- ---------------------corte -----------------------

prompt carga de dados em professores
Insert into professores values('46250-02','128','2004/2','Ana Paula',null,null);
Insert into professores values('46250-02','128','2005/1','Ana Paula',null,null);
Insert into professores values('46250-02','128','2005/2','Ana Paula',null,null);
Insert into professores values('46250-02','128','2006/1','Ana Paula',null,null);
Insert into professores values('46250-02','128','2006/2','Ana Paula',null,null);
Insert into professores values('46250-02','128','2007/1','Ana Paula',null,null);
Insert into professores values('46250-02','138','2006/1','Ana Paula',null,null);
Insert into professores values('46250-02','138','2007/1','Ana Paula',null,null);
Insert into professores values('46251-04','128','2005/1','Yamaguti',null,null);
Insert into professores values('46251-04','128','2005/2','Yamaguti',null,null);
Insert into professores values('46251-04','128','2006/2','Bastos',null,null);
Insert into professores values('46251-04','128','2007/1','Bastos',null,null);
Insert into professores values('46251-04','138','2005/1','Afonso',null,null);
Insert into professores values('46251-04','138','2006/2','Yamaguti',null,null);
Insert into professores values('46251-04','138','2007/1','Yamaguti',null,null);
Insert into professores values('46252-04','128','2006/1','Afonso',null,null);
Insert into professores values('46252-04','128','2006/2','Afonso',null,null);
Insert into professores values('46252-04','128','2007/1','Afonso',null,null);
Insert into professores values('46256-04','128','2005/1','Rodrigo',null,null);
Insert into professores values('46256-04','128','2005/2','Rodrigo',null,null);
Insert into professores values('46256-04','128','2006/1','Rodrigo',null,null);
Insert into professores values('46256-04','128','2006/2','Rodrigo',null,null);
Insert into professores values('46256-04','128','2007/1','Rodrigo',null,null);
Insert into professores values('46256-04','138','2006/1','Duncan',null,null);
Insert into professores values('46256-04','138','2006/2','Duncan',null,null);
Insert into professores values('46256-04','138','2007/1','Duncan',null,null);
Insert into professores values('46257-04','128','2006/1','Arruda',null,null);
Insert into professores values('46257-04','128','2006/2','Arruda',null,null);
Insert into professores values('46257-04','128','2007/1','Arruda',null,null);
Insert into professores values('46258-04','128','2005/2','Julio',null,null);
Insert into professores values('46258-04','128','2006/1','Julio',null,null);
Insert into professores values('46258-04','128','2007/1','Julio',null,null);
Insert into professores values('46266-04','128','2006/2','Mangan',null,null);
Insert into professores values('46266-04','128','2007/1','Arruda',null,null);
Insert into professores values('46267-04','128','2006/2','Arruda',null,null);

-- ---------------------corte -----------------------

prompt carga de dados em historico
Insert into historico values(04108293,'46250-02','128','2004/2',6);
Insert into historico values(04112046,'46250-02','128','2004/2',6);
Insert into historico values(04112192,'46250-02','128','2004/2',8);
Insert into historico values(04103839,'46250-02','128','2005/1',9);
Insert into historico values(05280023,'46250-02','128','2005/1',9);
Insert into historico values(03106842,'46250-02','128','2005/2',8);
Insert into historico values(04206067,'46250-02','128','2005/2',9);
Insert into historico values(04201018,'46250-02','128','2006/1',8);
Insert into historico values(05280018,'46250-02','138','2006/1',9);
Insert into historico values(05280027,'46250-02','128','2006/1',4);
Insert into historico values(05280027,'46250-02','128','2006/2',7);
Insert into historico values(04108293,'46251-04','138','2005/1',6);
Insert into historico values(04112046,'46251-04','128','2005/1',5);
Insert into historico values(04112192,'46251-04','128','2005/1',7);
Insert into historico values(04103839,'46251-04','128','2005/2',6);
Insert into historico values(04201018,'46251-04','128','2006/2',8);
Insert into historico values(04206067,'46251-04','138','2006/2',8);
Insert into historico values(04108293,'46252-04','128','2006/1',9);
Insert into historico values(04112046,'46252-04','128','2006/2',9);
Insert into historico values(04112192,'46252-04','128','2006/2',8);
Insert into historico values(04108293,'46256-04','128','2005/1',8);
Insert into historico values(04112046,'46256-04','128','2005/1',8);
Insert into historico values(04112192,'46256-04','128','2005/1',3);
Insert into historico values(04103839,'46256-04','128','2005/2',5);
Insert into historico values(04112192,'46256-04','128','2005/2',7);
Insert into historico values(03106842,'46256-04','138','2006/1',7);
Insert into historico values(04206067,'46256-04','138','2006/1',9);
Insert into historico values(05280023,'46256-04','128','2006/1',7);
Insert into historico values(04201018,'46256-04','138','2006/2',8);
Insert into historico values(05280018,'46256-04','128','2006/2',8);
Insert into historico values(04108293,'46257-04','128','2006/1',7);
Insert into historico values(04112046,'46257-04','128','2006/1',9);
Insert into historico values(04112192,'46257-04','128','2006/2',6);
Insert into historico values(04108293,'46258-04','128','2005/2',8);
Insert into historico values(04112046,'46258-04','128','2005/2',7);
Insert into historico values(04103839,'46258-04','128','2006/1',7);
Insert into historico values(04112192,'46258-04','128','2006/1',5);
Insert into historico values(04108293,'46266-04','128','2006/2',7);
Insert into historico values(04112046,'46266-04','128','2006/2',6);
Insert into historico values(04108293,'46267-04','128','2006/2',8);

----------------------corte----------------------------------------

prompt comandos de atualizacao dos valores agregados

update disciplinas
set MediaNotas = (select avg(Nota) from historico 
                  where disciplinas.Disciplina = historico.Disciplina);

update alunos
set Coef_Rend = (select avg(Nota) from historico
                 where alunos.Matricula = historico.Matricula);

update professores
set (Num_ALunos, Media_Notas) = 
(select count(Nota), Avg(Nota) from historico
 where professores.Disciplina = historico.disciplina 
   and professores.Turma      = historico.Turma 
   and professores.Ano_Sem    = historico.Ano_Sem); 

----------------------corte----------------------------------------

prompt comandos de consulta aas tabelas
select * from alunos;
select * from disciplinas;
select * from professores;
select * from historico;

prompt >>>>>>>>>>>>>>>>>>>>>>>>fim<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<.


