PROVA GABARITO - P1

Prévia do material em texto

PROVA – BANCO DE DADOS [10,0 PONTOS]
PROFESSOR – LUCAS 
NOME:_________________________________________________________________________
1) Com base na figura abaixo, descreva os itens que precisam ser preenchidos para 
completar o “Diagrama simplificado de um sistema de Banco de Dados” ? (1,0)
2) Com base na AULA 01, defina com suas palavras quando não usar um SGDB? (1,0)
RESPOSTA: 
Mais desejável usar arquivos comuns sob as seguintes circunstâncias: 
 Aplicações de banco de dados simples e bem definidas, para as quais não se 
espera muitas mudanças. 
 Requisitos rigorosos, de tempo real, que podem não ser atendidos devido as 
operações extras executadas pelo SGBD (programas CAD, etc.). 
 Sistemas embarcados com capacidade de armazenamento limitada. 
 Nenhum acesso de múltiplos usuários aos dados. 
3) Um livro tem vários exemplares na biblioteca, como por exemplo, Liv1-Ex1, Liv1-
Ex2, Liv2-Ex1. Exemplares estão dispostos em estantes. São mantidos dados
detalhados sobre autores e editoras dos livros para fins de consulta. Na biblioteca
trabalham bibliotecárias. Cada estante é periodicamente organizada por uma única
bibliotecária. Bibliotecárias também realizam empréstimos de exemplares para
clientes. Empréstimos cadastrados no BD devem conter a data da devolução e o valor
diário da multa, permanecendo no BD até o cliente entregar o exemplar. A
bibliotecária que realizou o empréstimo também é relevante de ser mantido no BD.
Software para processar
consultas/ programas
Software para acessar
dados armazenados
Algumas bibliotecárias são estagiárias. Uma bibliotecária estagiária está sempre
sob a responsabilidade de uma bibliotecária efetiva. Deve-se saber também a
instituição de ensino da qual a estagiária vem. Defina os atributos que julgar
relevante e realize o MER completo para este problema. (2,5)
RESPOSTA: PDF EM ANEXO.
4) Eloísa foi contratada para elaborar o DER (Diagrama Entidade Relacionamento) de 
um sistema para a Escola de Idiomas. O dono da escola explicou que a escola tem 
turmas de Inglês, Espanhol e Alemão, e que cada turma é acompanhada por um único 
professor. Ao longo do semestre, cada aluno terá 3 notas que serão usadas no 
cálculo de sua média. O sistema deve registrar o nome, a matrícula e o endereço 
(rua, número, bairro) de cada aluno, assim como o nome, o salário, a data de 
nascimento e a titulação (doutor, mestre, especialista ou graduado) de cada 
professor. Após coletar essas informações, Eloísa montou o seguinte DER: (1,0)
Analise o diagrama e responda se as notações dos atributos e as cardinalidades dos 
relacionamentos estão corretas. Corrija as possíveis falhas, e justifique as 
mudanças.
RESPOSTA: Estes são alguns dos problemas, podendo ser encontrados outros:
Problema 1: A entidade Aluno possui um atributo 'nome' que está sendo utilizado 
como chave. Uma das restrições fundamentais de um atributo chave é que ele seja 
ÚNICO, ou seja, seu valor não se repita nas demais ocorrências da entidade. O 
grande problema é que há a possibilidade de existirem alunos com o mesmo nome, por 
isso, o atributo 'nome' não é indicado para ser utilizado como chave. 
Solução: Utilizar o atributo 'matricula' como atributo chave, tendo em vista que o 
número da matricula é ÚNICO e EXCLUSIVO de cada aluno, sendo possível a 
identificação de um Aluno apenas pelo seu número da matrícula. 
__________________________________________________________________________________
Problema 2: Na entidade Aluno, o atributo 'endereco' foi modelado como um atributo 
simples. Portanto, o nome da rua, o bairro e o número da residência onde o aluno 
mora serão armazenados neste atributo 'endereco' de forma homogênea. Neste caso, 
fica difícil saber a informação de quantos alunos moram em um determinado bairro, 
por exemplo. 
OBS: A especificação do sistema pede que o nome da rua, o bairro e o número da 
residência sejam modelados separadamente. 
Solução A: Modelar o atributo 'endereco' como um atributo composto pelos atributos 
'rua', 'numero' e 'bairro'. Neste caso, se houver mais de um Aluno morando em um 
mesmo endereço haverá repetição de informação. 
Solução B: Modelar o atributo 'endereco' como uma Entidade que possui atributos 
'rua', 'número' e 'bairro'. Neste caso, haveria um relacionamento entre as 
entidades Aluno e Endereço e a cardinalidade seria definida de acordo com
as necessidades de negócio. 
Problema 3: No relacionamento entre Aluno e Turma, o atributo 'notas' foi modelado 
como um atributo simples. As três notas do Aluno seriam armazenadas nesse atributo 
de forma homogênea. Neste caso, fica difícil de saber qual a maior nota do aluno, 
por exemplo. 
Solução: Modelar o atributo 'notas' como um atributo composto pelos atributos 
'nota1', 'nota2' e 'nota3'. Neste caso, a repetição de informação de informação não
é um problema. 
Problema 4: A especificação diz que “Cada turma é acompanhada por um único 
Professor”, porém, a cardinalidade do relacionamento entre Turma e Professor não 
reflete essa necessidade de negócio. A cardinalidade (0:N) permite que uma Turma 
seja acompanhada por vários Professores (inclusive nenhum) 
Solução: Alterar a cardinalidade para (1:1). 
Problema 5: A entidade Turma não possui um atributo chave. 
Solução: Criar um atributo chave para a entidade Turma. 
Problema 6: A entidade Professor possui um atributo chamado 'salário' que foi 
modelado como atributo chave. Porém, pode existir vários Professores com o mesmo 
salário, o que torna inviável a utilização deste atributo como chave. 
Solução: Criar um atributo chave para a entidade Professor. 
5) Crie uma base de dados Universidade com as tabelas a seguir:
Alunos (RA, nome, endereço, cidade)
Disciplinas (COD_DISC, nome_disc, carga_hor)
Professores (COD_PROF, nome, endereço, cidade)
Turma (COD_DISC, COD_TURMA, COD_PROF, ANO, horário)
Histórico (RA, COD_DISC, COD_TURMA, COD_PROF, ANO, freqüência, nota)
Create Table Aluno (
RA integer not null,
nome varchar (50),
endereco varchar (100),
cidade varchar (50),
primary key (RA)
);
Create Table Disciplina (
COD_DISC integer not null,
nome_disc varchar (50),
carga_hor integer,
primary key (COD_DISC)
);
Create Table Professores (
COD_PROF integer not null,
nome varchar (50),
endereco varchar (100),
cidade varchar (50),
primary key (COD_PROF)
);
Create Table Turma (
COD_TURMA integer not null,
COD_DISC integer not null,
COD_PROF integer not null,
ANO date,
horario varchar (10),
primary key (COD_TURMA),
foreign key (COD_DISC) references Disciplina (COD_DISC),
foreign key (COD_PROF) references Professores (COD_PROF),
foreign key (ANO) references Histórico (ANO)
);
Create Table Historico (
RA integer not null,
COD_TURMA integer not null,
COD_DISC integer not null,
COD_PROF integer not null,
ANO date not null,
frequencia integer,
nota integer
primary key (ANO),
foreign key (RA) references Alunos (RA),
foreign key (COD_DISC) references Disciplina (COD_DISC),
foreign key (COD_PROF) references Professores (COD_PROF),
foreign key (COD_TURMA) references Turma (COD_TURMA),
);
Realiza as operações abaixo via SQL: (4,5)
a)Encontre o RA dos alunos com nota em BD em 2010 maior que 5. 
SELECT ra
FROM HISTORICO
WHERE cod_disc='BD' AND ano=2010 AND nota <5
b)Forneça o RA, nome e nota dos alunos com nota menor que 5 em BD em 2010. 
SELECT A.RA, A.NOME, A.NOME
FROM HISTORICO H JOIN ALUNOS A
ON A.RA = H.RA
WHERE H.COD_DISC='BD' 
AND H.ANO=2010 
AND H.NOTA <5 
c)Forneça o nome dos professores de BD em 2010.
SELECT P.NOME
FROM PROFESSORES P JOIN TURMA T
ON P.COD_PROF=T.COD_PROF
WHERE T.ANO=2010 and T.COD_DISC='BD'
d) Localize o nome e RA dos alunos do professor JOSÉ em 2010 ou 2009 que tiveram
aulas com o professor MARCOS em algum ano.
SELECT A.NOME, A.RA
FROM ALUNOS A JOIN PROFESSORES P JOIN HISTORICO H 
ON H.RA=A.RA and H.COD_PROF = P.COD_PROF
WHERE P.NOME = 'JOSÉ'
AND (H.ANO = 2010 OR H.ANO = 2009)
AND A.RA in( SELECT H.RA
FROM HISTORICO H JOINPROFESSORES P
ON H.COD_PROF = P.COD_PROF
WHERE AND P.NOME = 'MARCOS')
e) Encontre o nome e endereço dos alunos e professores de Campinas.
SELECT A.NOME ,A.ENDERECO, P.NOME ,P.ENDERECO,
FROM ALUNOS A JOIN PROFESSORES P JOIN HISTORICO H
ON A.RA=H.RA AND H.COD_PROF=P.COD_PROF
WHERE A.CIDADE='CAMPINAS'
AND P.CIDADE='CAMPINAS'
f) Forneça o nome dos alunos que cursaram disciplinas com carga horária menor que 
60 horas, bem como os respectivos professores que as lecionaram.
SELECT A.NOME, P.NOME
FROM ALUNOS A JOIN DISCIPLINAS D JOIN PROFESSORES P JOIN HISTORICO H
ON A.RA = H.RA 
AND D.COD_DISC = H.COD_DISC 
AND P.COD_PROF = H.COD_PROF 
WHERE D.CARGA_HOR < 60
g) Forneça o RA, nome e média das notas por alunos.
SELECT A.RA, A.NOME, AVG(H.NOTA)AS MEDIA
FROM ALUNOS A JOIN HISTORICO H
ON A.RA=H.RA
GROUP BY A.RA, A.NOME
h) Forneça o nome dos professores que somente lecionaram matérias com carga horária
foi inferior a 60 horas.
SELECT P.NOME
FROM PROFESSORES P JOIN TURMA T
ON P.COD_PROF = T.COD_PROF AND 
WHERE P.COD_PROF NOT IN ( SELECT T.COD_PROF
 FROM TURMA T JOIN DISCIPLINAS D
 ON T.COD_DISC = D.COD_DISC 
WHERE AND CARGA_HOR >=60)