prova2-2004-1

Prévia do material em texto

Fundamentos de Bancos de Dados – Prova 2
Prof. Carlos A. Heuser
Junho de 2004
Duração: 2 horas – Prova com consulta
Para as consultas de SQL, considere o seguinte esquema de um banco de dados
de estradas (mesmo esquema da primeira prova):
Modelo(CodModelo,NomeModelo,CodCPU)
CodCPU referencia CPU
/* Tabela com os modelos de computadores que são
fabricadas */
CPU(CodCPU,NomeCPU,CodFabricante)
CodFabricante referencia Fabricante
/* Tabela com os modelos de CPUs que são usadas */
FabricanteCPU(CodFabricante,NomeFabricante)
/* Tabela com os fabricantes de CPUs */
Configuracao(CodModelo,NoConfig,NomeConfig)
CodModelo referencia Modelo
/* Tabela com as várias configurações nas quais um
modelo pode ser fabricado. Cada configuração tem
um nome */
PerifComp(CodPerifComp,NomePerifComp)
/* Tabela com os periféricos e componentes usados
na fabricação de computadores */
PerifCompConfig(CodPerifComp,CodModelo,NoConfig,Quantidade)
CodPerifComp referencia PerifComp
(CodModelo,NoConfig) referencia Configuração
/* Tabela informando que componentes e em que
quantidade são usados dentro de cada configuração*/
1
Expresse as seguintes consultas em SQL:
1. Obter os nomes dos modelos que obedecem a ambos os requisitos abaixo:
• o modelo utiliza uma CPU de um fabricante denominado ’Intel’;
• o modelo tem pelo menos uma configuração chamada ’Estação de tra-
balho gráfica’.
A consulta deve ser resolvida de duas formas:
a) no estilo álgebra relacional, isto é, sem uso de consultas aninhadas
com IN ou EXISTS, e
select NomeModelo
from Modelo M,
CPU,
FabricanteCPU F,
Configuracao Cf
where M.CodCPU=CPU.CodCPU and
CPU.CodFabricante=F.CodCPU and
M.CodModelo=Cf.CodModelo and
NomeFabricante=’Intel’ and
NomeConfig=’Estação de trabalho gráfica’
b) no estilo cálculo relacional, isto é, com uso de consultas aninhadas
com IN ou EXISTS.
select NomeModelo
from Modelo
where CodCPU in
(select CodCPU
from CPU
where CodFabricante in
(select CodFabricante
from FabricanteCPU
where NomeFabricante=’Intel’))
and
CodModelo in
(select CodModelo
from Configuracao
where NomeConfig=’Estação de trabalho gráfica’)
2. Obter uma tabela com três colunas: nome do modelo, nome da configuração
e nome do periférico/componente. A tabela deve conter uma linha para cada
2
periférico/componente que é usado em uma configuração do modelo em
questão. Somente deve ser exibida uma linha, quando mais que 10 unidades
do periférico/componente em questão são usadas na configuração.
Caso um modelo não tenha configurações, as colunas referentes ao nome da
configuração e ao nome do periférico/componente devem aparecer vazias
(valor NULL).
Já uma configuração sem periféricos/componentes, ou na qual não exista
nenhum periférico/componente com mais que 10 unidades, deve aparecer
com o nome do periférico/componente vazio.
select NomeModelo,NomeConfig,NomePerifComp
from
(( Modelo
left outer join
Configuracao
on Modelo.CodModelo=Configuracao.CodModelo
)
left outer join
PerifCompConfig
on Configuracao.CodModelo=PerifCompConfig.CodModelo AND
Configuracao.NoConfig=PerifCompConfig.NoConfig and
Quantidade>10)
left outer join
PerifComp
on PerifComp.CodPerifComp=PerifCompConfig.CodPerifComp
3. Obter uma tabela com duas colunas, a primeira com um nome de modelo e
a segunda com um nome de um outro modelo. O segundo modelo deve usar
pelo menos um periférico/componente que também é usado pelo primeiro
modelo. Modelos que não tem periféricos/componentes em comum não
aparecem na tabela. A tabela deve estar ordenada pela primeira coluna.
select M1.NomeModelo, M2.NomeModelo
from Modelo M1,
Modelo M2,
PerifCompConfig P1,
PerifCompConfig P2
where P1.CodPerifCompConfig=P2.CodPerifCompConfig and
P1.CodModelo=M1.CodModelo and
3
P2.CodModelo=M2.CodModelo and
M1.CodModelo<>M2.CodModelo
order by M1.NomeModelo
4. Obter os nomes dos periféricos/componentes que são utilizados em todos
modelos. (é o mesmo que: obter os nomes dos periféricos/componentes, tal
que não existe nenhum modelo para o qual eles não aparecem na configura-
ção)
select NomePerifComp
from PerifComp
where not exists
(select * from Modelo
where not exists
(select * from PerifCompConf
where CodModelo=Modelo.CodModelo and
CodPerifComp=PerifComp.CodPerifComp))
5. Para cada configuração que usa mais que três tipos (não unidades) diferentes
de periférico/componente, obter: o nome do modelo, o nome da configura-
ção e a quantidade média de periféricos/componentes usada no modelo.
select ModeloComMedia.NomeModelo,
NomeConfig,
ModeloComMedia.QuantMedModelo
from
(select M.CodModelo,
NomeModelo,
AVG(Quantidade) as QuantMedModelo
from Modelo M,
PerifCompConfig PCC
where M.CodModelo=PCC.CodModelo
group by M.CodModelo,NomeModelo) as ModeloComMedia
Configuracao C,
PerifCompConfig PCC
where ModeloComMedia.CodModelo=C.CodModelo and
C.CodModelo=PCC.CodModelo and
C.NoConfig=PCC.NoConfig
group by ModeloComMedia.NomeModelo,
NomeConfig,
ModeloComMedia.QuantMedModelo
having count(*)>3
4
aceitei também a solução abaixo (quantidade média por configuração apesar
de ela não corresponder exatamente ao enunciado (quantidade média por
modelo)
select NomeModelo,
NomeConfig,
AVG(Quantidade)
from
Modelo as M
Configuracao C,
PerifCompConfig PCC
where M.CodModelo=C.CodModelo and
C.CodModelo=PCC.CodModelo and
C.NoConfig=PCC.NoConfig
group by NomeModelo,
NomeConfig,
having count(*)>3
6. Obter os nomes do periféricos/componentes que são usados em maior quan-
tidade (maior valor de PerifCompConfig.Quantidade).
SELECT NomePerifComp
FROM PerifCompConfig PCC,
PerifComp PC
WHERE PC.CodPerifComp=PCC.CodPerifComp AND
Quantidade =
(SELECT MAX(Quantidade)
FROM PerifCompConfig)
5