Exercício de Banco de Dados

Enunciado do Exercício

Considerando o seguinte banco de dados:

PROFESSOR
matrícula; nome; idade; salário; departamento
ALUNO
matrícula; nome; idade; endereço; status
DISCIPLINA
codigo; nome; departamento; ch
HISTÓRICO
matriculaAluno; codigo; semestre; nota
TURMA
codigo; semestre; matriculaProf; (só há uma turma de cada disciplina por semestre)

Chaves:

PROFESSOR
PK matrícula

ALUNO
PK matrícula

DISCIPLINA
PK codigo

HISTÓRICO
PK matriculaAluno; codigo; semestre
FK matriculaAluno REFERENCIA ALUNO
codigo; semestre REFERENCIA TURMA
TURMA
PK codigo; semestre
FK MatriculaProf REFERENCIA PROFESSOR
codigo REFERENCIA DISCIPLINA

Observações:
Tabela PROFESSOR:
idade tem valor máximo de 70.
Tabela ALUNO status representa a situação do vínculo do aluno com a Universidade; os valores possíveis são “matriculado”, “não matriculado“ ou ”evadido”.
Tabela DISCIPLINA: ch é carga horária da disciplina; os valores possíveis são 30, 60, ou 120. O valor mais comum para carga horária é 60.
Tabela HISTÓRICO: nota pode ter valores entre 0 e 10.

QUESTÃO 1:
1. Crie as tabelas que farão parte do banco de dados. Além dos atributos de cada tabela, defina também:
1.1. chaves primárias apropriadas; utilize a cláusula CONSTRAINT para isso.
1.2. chaves estrangeiras apropriadas; utilize a cláusula CONSTRAINT para isso.
1.3. algumas restrições de integridade para o banco de dados acima. As restrições podem ser criadas através uma CONSTRAINT (na instrução CREATE TABLE) da seguinte forma:

CONSTRAINT CHECK ()

onde é alguma expressão envolvendo o atributo relacionado na restrição. Ela pode envolver os operadores IN e BETWEEN.

1.4. alguns valores default para atributos de alguma(s) tabela(s). Valores default podem ser criados através da cláusula DEFAULT (na instrução CREATE TABLE) da seguinte forma:

DEFAULT , ao lado da definição do atributo na criação da tabela.

QUESTÃO 2:

2. Popule as tabelas do banco de dados. Teste a inserção de alguns valores de forma que você verifique a aplicação das restrições de integridade definidas para a tabela. Utilize a instrução
INSERT INTO VALUES ()

QUESTÃO 3:

3. Realize as seguintes consultas através de SQL:
3.1. Qual a idade média dos estudantes que obtiveram nota 10 em alguma disciplina?
3.2. Liste a carga horária média das disciplinas de cada departamento.
3.3. Liste o código das disciplinas que pertencem aos departamentos de “Informática” e “Matemática”.
3.4. Liste o nome das disciplinas que já foram ministradas tanto por professores do departamento de “Informática” como por professores do departamento de “Matemática”?
3.5. Liste o nome dos professores que têm o valor de seu salário 10% maior do que a média salarial dos professores do seu departamento.
3.6. Liste os nomes das disciplinas, do departamento de “Informática”, que foram cursadas por todos os estudantes cadastrados no banco de dados (ou seja, todos os alunos devem ter cursado a disciplina para que ela apareça na resposta).


Create table PROFESSOR (
matricula integer not null,
nome varchar(50) null,
idade number,
salario integer not null,
departamento varchar(50) null,
constraint pk_professor Primary key(matricula),
CONSTRAINT ck_idade CHECK (idade <>
);
Create table ALUNO (
matricula integer not null,
nome varchar(50) null,
idade number,
endereco varchar(100) null,
status varchar(50),
constraint pk_aluno Primary key(matricula),
constraint ck_aluno check(status in ('matriculado','nao-matriculado','evadido'))
);
Create table DISCIPLINA (
codigo integer not null,
nome varchar(50) null,
departamento varchar(100) null,
ch integer default 60,
constraint pk_disciplina Primary key(codigo),
constraint ck_disciplina check(ch in (30,60,120))
);
Create table HISTORICO(
matriculaAluno integer not null,
codigo integer not null,
semestre integer not null,
nota integer not null,
constraint pk_historico Primary key(matriculaAluno, codigo, semestre),
constraint ck_historico check(nota between 0 and 10),
constraint fk_historico_aluno foreign key(matriculaAluno) references ALUNO(matricula)
);
Create table TURMA(
codigo integer not null,
semestre integer not null,
matriculaProf integer not null,
constraint pk_turma Primary key(codigo, semestre),
constraint fk_turma_disciplina foreign key(codigo) references DISCIPLINA(codigo),
constraint fk_turma_prof foreign key(matriculaProf) references PROFESSOR(matricula)
);
alter table historico add constraint fk_historico_codturma foreign key(codigo, semestre) references TURMA(codigo, semestre)

INSERT INTO DISCIPLINA VALUES (1, 'Banco de Dados', 'Informática', 60);
INSERT INTO DISCIPLINA VALUES (2, 'Estruturas', 'Informática', 120);
INSERT INTO DISCIPLINA VALUES (3, 'Calculo A', 'Matemática', 60);
INSERT INTO DISCIPLINA VALUES (4, 'Calculo B', 'Matemática', 60);
INSERT INTO ALUNO VALUES (100, 'André',20, 'Rua A', 'matriculado');
INSERT INTO ALUNO VALUES (200, 'Maria',23, 'Rua B', 'matriculado');
INSERT INTO ALUNO VALUES (300, 'Clara',18, 'Rua C', 'evadido');
INSERT INTO DISCIPLINA (cOdigo, nome, departamento) VALUES (5, 'Calculo C', 'Matemática');INSERT INTO PROFESSOR VALUES (10, 'Pedro', 35, 3000, 'Informática'); I
NSERT INTO PROFESSOR VALUES (20, 'Denise', 40, 4000, 'Informática');
INSERT INTO PROFESSOR VALUES (30, 'Ana', 70, 2000, 'Matemática');
INSERT INTO TURMA VALUES (1, 2, 20);
INSERT INTO TURMA VALUES (1, 1, 20);
INSERT INTO TURMA VALUES (2, 2, 30);
INSERT INTO HISTORICO VALUES (100, 1, 2, 10);
INSERT INTO HISTORICO VALUES (200, 1, 2, 5);
INSERT INTO HISTORICO VALUES (300, 1, 1, 5);
INSERT INTO HISTORICO VALUES (300, 1, 2, 10);
INSERT INTO HISTORICO VALUES (100, 2, 2, 10);
SELECT AVG(aluno.idade) FROM aluno, historico WHERE historico.nota = 10
SELECT disciplina.departamento, AVG(disciplina.ch) FROM disciplina GROUP BY disciplina.departamento
SELECT disciplina.codigo FROM disciplina WHERE disciplina.departamento IN ('Matemática', 'Informática')
SELECT disciplina.nome FROM disciplina, turma, professor WHERE turma.matriculaprof = professor.matricula AND turma.codigo = disciplina.codigo AND professor.departamento IN ('Matemática', 'Informática')
SELECT nome FROM professor WHERE salario >(SELECT AVG(salario) * 1.10 FROM professor)GROUP BY nome, departamento
SELECT disciplina.nomeFROM disciplinaWHERE disciplina.departamento='Informática' AND NOT EXISTS ( SELECT * FROM aluno WHERE NOT EXISTS ( SELECT * FROM historico WHERE historico.matriculaAluno = aluno.matricula))