Banco de Dados III - Av1

Prévia do material em texto

BD III AV1 Outubro 2013
Banco de Dados III – AV1
• A avaliação deve ser entregue até o dia 18/10/2013, as 23:59:59. A data e hora que serão
consideradas são as de recebimento. Sendo assim, não deixe para enviar na última hora.
Avaliações entregues após esta data e hora serão penalizadas com 1 ponto. Cada novo dia
de atraso será penalizado com mais 1 ponto. O objetivo disto é garantir que as pessoas que
se esforçaram para entregar na data certa sejam valorizadas por isto.
• As avaliações poderão ser entregues por email ou pessoalmente. Para entregar pessoalmente,
deixe a prova resolvida comigo no LIRA em formato digital. Os melhores dias para isto são
quarta ou quinta-feira a tarde. Para entregar por email, siga cuidadosamente e com
atenção as instruções abaixo:
– Envie a mensagem para ac4791@gmail.com
– Utilize o assunto “AV1BD3”. Não utilize outro assunto! O email está configurado
para confirmar o recebimento e separar a mensagem com o anexo através de um fil-
tro automático. Se você utilizar outro assunto o recebimento não será confirmado e,
eventualmente, eu posso não ver a mensagem.
– Não escreva nada na mensagem. Como a mensagem será recebida por um filtro au-
tomático, eu não vou ler. Se tiver alguma dúvida, mande uma outra mensagem, com
outro assunto, separadamente.
– O formato para entregar o trabalho é em um arquivo com extensão SQL. Utilize um
arquivo para cada questão. O nome do arquivo deve respeitar o seguinte formato:
matricula_numquestao.sql, onde matricula é o seu número de matrícula e numquestao
é o número da questão que você está resolvendo com aquele arquivo. Não mande
todas as questões em um mesmo arquivo!
• A responsabilidade pela entrega do trabalho é sua. Caso o arquivo não possa ser aberto ou
caso eu não receba o email com o seu trabalho a sua nota será zero. Caso você não receba a
confirmação por email é possível que algo tenha dado errado. Neste caso, me procure e me
entregue pessoalmente. Por essa razão, vale à pena lembrar: não deixa para a última hora.
• A avaliação deve ser feita individualmente. Este assunto gera, de modo geral, respostas bem
pessoais. Trabalhos iguais, semelhantes, com erros similares, com lógica de desenvolvimento
parecida ou qualquer coisa do gênero, terão a nota final dividida pelo número de trabalhos
semelhantes.
• Qualquer dúvida, me procure pessoalmente no LIRA ou envie emails com dúvidas para
ac4791@gmail.com.
• A avaliação vale 10 pontos.
UEZO – Centro Universitário Estadual da Zona Oeste 1
BD III AV1 Outubro 2013
Faça todas as questões abaixo utilizando o SQL Server 2008, e o banco de dados AdventureWorksLT. Você
pode ver um modelo de ER deste banco no link http://cs.fit.ba/photos/jasmins_galerija_slika/
picture49492.aspx.
1. Faça uma stored procedure que receba os seguintes parâmetros:
• Um identificador ProductID igual ao que está na tabela SalesOrderDetail
• Um número inteiro que identifica uma quantidade, igual ao que está na tabela SalesOrderDetail,
coluna OrderQty
• Um valor em dinheiro semelhante ao que está na tabela SalesOrderHeader, coluna TotalDue
Esta SP deve então fazer o seguinte:
• Analisar a tabela SalesOrderDetail e identificar os clientes que compraram uma certa quantidade
de um mesmo produto (com código igual ao especificado no primeiro parâmetro da SP) igual ou
maior do que a especificada no segundo parâmetro da SP
• Caso o valor total da compra feita por eles seja maior ou igual ao valor especificado no terceiro
parâmetro da SP, este cliente deverá receber um brinde da loja
Como resultado, a SP deverá exibir uma lista contendo:
• O nome completo do cliente (que está na tabela Customer em FirstName, MiddleName, LastName)
• O CustomerID do cliente (que também está na tabela Customer)
• O valor total da compra do cliente
• A quantidade que o cliente comprou do produto especificado no primeiro parâmetro da SP
• Uma frase que indique uma das quatro condições abaixo:
– “Cliente recebe brinde”
– “Cliente não recebe brinde porque não comprou o produto”
– “Cliente não recebe brinde porque comprou quantidade insuficiente”
– “Cliente não recebe brinde porque sua compra teve valor menor do que o especificado”
Basta exibir uma das mensagens. Ou seja, se o cliente não ganha o brinde porque não teve quantidade
suficiente e porque o valor foi menor do que o especificado, basta exibir uma das duas mensagens.
UEZO – Centro Universitário Estadual da Zona Oeste 2
BD III AV1 Outubro 2013
2. Faça uma stored procedure que ofereça descontos para todos os clientes do Canadá (o país de origem
do cliente está na tabela Address na coluna CountryRegion) que fizeram alguma compra. Este
desconto deve ser dado sobre o preço mostrado na coluna SubTotal da tabela SalesOrderHeader.
Os impostos (coluna TaxAmt da tabela SalesOrderHeader) devem ser recalculados como sendo 8%
do novo SubTotal, mas o preço do frete (coluna Freight da tabela SalesOrderHeader) não deve ser
alterado. O valor final então (originalmente na coluna TotalDue da tabela SalesOrderHeader deve
ser recalculado como a soma do novo SubTotal, do novo valor dos impostos e do antigo frete. Além
disso, o desconto deve ser concedido de acordo com as seguintes regras:
• 5% para compras com SubTotal abaixo de 700,00
• 10% para compras com SubTotal entre 700,00 e 1000,00
• 15% para compras com SubTotal acima de 1000,00
A SP deve então exibir uma tabela com os seguintes informações:
• SalesOrderID igual ao da tabela SalesOrderHeader
• Uma coluna indicando se o desconto foi dado ou não (ou seja, eu quero a informação para todos
os clientes)
• Qual o percentual de desconto concedido
• Uma coluna indicando o novo SubTotal
• Uma coluna indicando o novo valor dos impostos
• Uma coluna mostrando o valor do frete (que não muda)
• Uma coluna mostrando o novo valor total
UEZO – Centro Universitário Estadual da Zona Oeste 3