Prova - Banco de Dados - Modelo Relacional

Prévia do material em texto

Questão 1: 
 
Considere o diagrama ER presente na figura em anexo a essa prova (ER-Q1-P2.png). 
Construa um esquema relacional equivalente a este diagrama ER. 
 
Resposta: 
 
Correntis (CPF, Nome) 
Agen (NumAgen, Nome) 
Corrente Cont (CPF, Num Cont) 
CPF referência Correntis 
NumCont referencia Cont 
Cont (NumCont, Sal, NumAgen, Juros, Aniver, LimCheqEsp) 
NumAgen referencia Agen 
Cheq (NumCheq, NumCont,) 
NumCont referencia Cont 
 
Questão 2 – SQL (3,0 pontos) 
 
Considere o esquema relacional abaixo. As chaves primárias se encontram sublinhadas. 
 
 
Cliente (CodCli, nome, idade) 
Produto (CodProd, descrição) 
Pedido (CodCli, numPedido, data) 
CodCli referencia Cliente 
 
ItemPedido (numPedido, numItem, CodProd, Quant, PrecoUnit) 
numPedido referencia Pedido 
CodProd referencia Produto 
● Escreva os comandos SQL para criar as tabelas ItemPedido, Produto e Pedido, incluindo 
as restrições de integridade que se aplicam. Assuma que, ao excluir um pedido, todos os 
itens de pedido relacionados devem ser excluídos automaticamente. Assuma também 
que um produto não pode ser excluído caso exista algum item de pedido relacionado a 
ele. Além disso, ao alterar o código de um produto, todos os itens de pedido 
relacionados devem ser alterados automaticamente. Em um pedido, assuma que a 
quantidade não pode ser nula. Se, por algum motivo, não for possível definir alguma 
restrição de integridade, justifique. 
● Escreva comandos SQL para incluir um novo pedido no banco de dados, com os 
seguintes dados: Pedido número 123, do cliente 02 (assuma que o cliente já está 
cadastrado). Os itens do pedido são: (a) 2 unidades do produto de código 02, custando 
1,00 cada unidade e (b) 3 unidades do produto de código 03, custando 2,30 cada unidade 
● Escreva um comando SQL para excluir todos os pedidos do cliente João da Silva. 
● Faça uma consulta SQL que seleciona os nomes dos clientes com mais de 30 anos que 
fizeram algum pedido entre 01/01/2000 e 01/01/2002. 
● Faça uma consulta SQL que retorna o valor total do pedido número 111. 
● Faça uma consulta SQL que retorna o nome do cliente e a descrição de produtos que o 
cliente pediu em quantidade superior a 10 unidades. 
● Faça uma consulta SQL que retorne os nomes dos clientes que nunca fizeram nenhum 
pedido 
● Faça uma consulta SQL que mostre o número do pedido, o código do produto e a 
quantidade solicitada de produtos cuja descrição contenha a palavra “novidade”. 
 
Resposta: 
 
Tabela ItemPedido 
 
CREATE TABLE IF NOT EXISTS "ProvaQuestão2"."itempedido" 
( 
"numpedido" integer NOT NULL, 
"numitem" integer NOT NULL, 
"codprod" integer NOT NULL, 
"quant" integer NOT NULL, 
"precounit" double precision NOT NULL, 
 
CONSTRAINT "pkItempedido" PRIMARY KEY ("numpedido", "numitem"), 
CONSTRAINT "fk_codprod" FOREIGN KEY ("codprod") 
REFERENCES "ProvaQuestão2"."produto" ("codprod") MATCH SIMPLE 
ON UPDATE CASCADE 
ON DELETE NO ACTION, 
 
CONSTRAINT fk_numpedido FOREIGN KEY ("numpedido") 
REFERENCES "ProvaQuestão2"."pedido" ("numpedido") MATCH SIMPLE 
ON UPDATE NO ACTION 
ON DELETE CASCADE 
) 
 
Tabela Produto 
 
CREATE TABLE IF NOT EXISTS "ProvaQuestão2"."produto" 
( 
"codprod" integer NOT NULL, 
 
descricao character varying(200) COLLATE pg_catalog."default" NOT NULL, 
CONSTRAINT "pk_cod_prod" PRIMARY KEY ("codprod") 
) 
 
Tabela Pedido 
 
CREATE TABLE IF NOT EXISTS "ProvaQuestão2"."pedido" 
( 
"fk_codcli" integer NOT NULL, 
"numpedido" integer NOT NULL, 
data date, 
CONSTRAINT "pedido_pkey" PRIMARY KEY ("numpedido"), 
CONSTRAINT "fk_codcli" FOREIGN KEY ("fk_codcli") 
REFERENCES "ProvaQuestão2"."cliente" ("codcli") MATCH SIMPLE 
ON UPDATE NO ACTION 
ON DELETE NO ACTION 
 
) 
 
Comando para inserir os dados do pedido 
 
INSERT INTO provaquestao2.pedido (numpedido, fk_codcli) 
VALUES (123,2); 
Comando para inserir os itens do pedido 
 
INSERT INTO provaquestao2.itempedido (numpedido, numitem, codprod, quant, precounit) 
VALUES (123,1,2,2,1.00); 
INSERT INTO provaquestao2.itempedido (numpedido, numitem, codprod, quant, precounit) 
VALUES (123,2,3,3,2.30); 
Comando para excluir os pedidos do cliente João da Silva 
 
DELETE 
 
FROM provaquestao2.pedido p 
 
WHERE p.fk_codcli IN (SELECT c.codcli 
 
FROM provaquestao2.cliente c 
WHERE c.nome = ‘João da Silva’) 
Comando para fazer uma consulta SQL que seleciona os nomes dos clientes com mais de 30 
anos que fizeram algum pedido entre 01/01/2000 e 01/01/2002. 
 
SELECT c.nome 
 
FROM provaquestao2.cliente c 
INNER JOIN pedido p 
ON c.codcli= p.fk_codcli 
 
WHERE c.idade > 30 AND p.data BETWEEN '2000-01-01' AND '2002-01-01'; 
 
Comando que retorna uma consulta SQL que retorna o valor total do pedido número 111 
 
SELECT SUM(ip.precounit * ip.quant) 
FROM provaquestao2.itempedido ip 
WHERE ip.numpedido = '111'; 
Comando retorna uma consulta SQL que retorna o nome do cliente e a descrição de 
produtos que o cliente pediu em quantidade superior a 10 unidades. 
 
SELECT c.nome,p.descricao 
FROM provaquestao2.cliente c 
INNER JOIN provaquestao2.pedido pe 
ON c.codcli = pe.fk_codcli 
INNER JOIN provaquestao2.itempedido ip 
ON pe.numpedido = ip.numpedido 
INNER JOIN provaquestao2.produto pr 
ON ip.codprod = pr.codprod 
WHERE ip.quant > 10; 
 
Faça uma consulta SQL que retorne os nomes dos clientes que nunca fizeram nenhum pedido 
 
SELECT c.nome 
 
FROM provaquestao2.cliente c 
WHERE c.codcli 
NOT IN (SELECT p.fk_codcli FROM pedido p) 
 
Faça uma consulta SQL que mostre o número do pedido, o código do produto e a quantidade 
solicitada de produtos cuja descrição contenha a palavra “novidade”. 
 
SELECT ip.numpedido,ip.codprod,ip.quant 
FROM provaquestao2.itempedido ip 
INNER JOIN produto pr 
ON ip.codprod = pr.codprod 
WHERE pr.descricao LIKE '%novidade%'; 
 
Questão 3 - Álgebra Relacional (2,0 pontos) 
 
Considere o esquema relacional da Questão 2. Apresente as respostas em álgebra 
relacional para as seguintes consultas: 
 
● Faça duas expressões algébricas equivalentes que selecionam os nomes dos 
clientes com mais de 30 anos que fizeram algum pedido entre 01/01/2000 e 
01/01/2002. 
● Apresente a expressão algébrica da consulta que mostra os nomes dos clientes que 
não possuem pedidos registrados. 
● Mostre a expressão que apresenta a descrição de produtos que constam de algum 
pedido. 
 
Resposta: 
 
Faça duas expressões algébricas equivalentes que selecionam os nomes dos clientes com 
mais de 30 anos que fizeram algum pedido entre 01/01/2000 e 01/01/2002. 
 
A’ <- Π nome (σ idade > 30 ^ (data >= 01/01/2000 ^ data >= 01/01/2000(A))) 
Assim como na questão 2 
WHERE c.idade > 30 AND p.data BETWEEN '2000-01-01' AND '2002-01-01'; 
 
Apresente a expressão algébrica da consulta que mostra os nomes dos clientes que 
não possuem pedidos registrados. 
 
B’ <- Π nome (σ numpedido = NULL (B)) 
Assim como na questão 2 
 
WHERE c.codcli NOT IN (SELECT p.fk_codcli FROM pedido p) 
 
Mostre a expressão que apresenta a descrição de produtos que constam de algum 
pedido. 
 
C’ <- Π descricao (itempedido ⋈ produto) 
 
A descrição vai aparecer da interseção de produto com pedido 
 
 
Questão 4 - Normalização (1,0 ponto) 
 
Seja R(A,B,C) uma relação na 1FN. Sejam A, B e C atributos do tipo INT. Considere que o 
atributo A forme a chave primária de R. Apresente uma possível instância de R (conjunto de 
tuplas de R), composta por apenas duas tuplas, que mostre que se A→B e A→C são 
dependências funcionais de R, então não necessariamente B→C também é uma 
dependência funcional de R. 
 
Resposta: 
 
Um jogo de número 1 pode custar 250 reais e ter 500 horas de jogo, onde ao mesmo tempo 
um jogo de número 2 pode custar 250 reais e ter 10 horas de jogo. O fator tempo não é 
dependente do fator valor. 
 
R = {(1,250,500),(2,250,10)} 
 
 
Questão 5 - Álgebra Relacional (1,0 ponto) 
 
Seja A ={(x,1),(x,2),(x,3),(y,1),(y,2),(y,4),(z,1),(z,3),(z,4),(w,2),(w,3),(w,4)} Se A/B (A dividido 
por B) for a relação{(y),(w)}, qual é o conteúdo da relação B? 
 
Resposta: Como em y e w os únicos números em comum são 2 e 4, logo B = {(2,4)} 
 
 
Questão 6 - SQL (1,0 ponto) 
 
Considere que uma tabela chamada CLIENTES possui o campo NOME. Apresente um 
comando SQL que retorna os clientes que possuem os sobrenomes CARDOSO e 
OLIVEIRA, sendo que OLIVEIRA deve ser o último sobrenome. 
 
Resposta: 
 
SELECT NOME 
 
FROM CLIENTES 
 
WHERE NOME LIKE ‘_%CARDOSO%’ AND ‘%OLIVEIRA’