Enunciado do problema

Base de Dados 2004/05
Enunciado do Projecto
Neste projecto pretende-se implementar uma base de dados e uma aplicação para
gerir stocks de uma empresa. Os alunos devem organizar-se em grupos de três
pessoas para a realização do projecto.
O projecto será avaliado por dois relatórios que deverão ser entregues em papel
segundo o template disponibilizado na página da disciplina. Junto com o segundo
relatório deverá ser entregue um CDROM ou disquete com todo o código
desenvolvido pelos alunos.
Para facilitar a correcção, os relatórios deverão ser apenas agrafados. Não utilize
capas rígidas ou transparentes, não encaderne o relatório (com argolas ou a
quente), nem intercale folhas em branco dentro do relatório. Em alternativa, os
relatórios e código podem ser enviados por email para o responsável da cadeira.
O nome dos ficheiros a enviar deve obedecer ao seguinte formato:

númerodogrupoReln.doc (n=1 para o 1º relatório e n= 2 para o 2º relatório)

númerodogrupoApl.rar ou númerodogrupoApl.zip (para o código da
aplicação da 2ª parte).
1 Primeira Parte
Na primeira parte do projecto, os alunos vão desenhar e implementar a base de
dados de acordo com os requisitos especificados. O primeiro relatório deverá ser
entregue até 9/11/04 e deverá conter, para além da capa formatada de acordo com
o template a fornecer, as seguintes secções:
1. Introdução – objectivo deste relatório, metodologia de trabalho, qual o SGBD
seleccionado, etc. (Máximo: 1 página.)
2. Modelo de classes UML – Converta o diagrama ER apresentado em 1.2
(inventário) no respectivo diagrama de classes de UML. Diga qual o conhecimento
que perdeu na conversão ER-> diagrama de classes. Depois, modifique e enriqueça
o diagrama de classes UML que apresentou em 1.2 com os requisitos adicionais do
problema apresentados em 1.3 (facturação). Todas as restrições de integridade que
não podem ser modeladas no diagrama de classes devem se enumeradas. Pode
utilizar, se quiser, uma ferramenta de modelação como o Visio ou o PowerDesigner.
(Máximo: 2 páginas).
3. Modelo Relacional – Modelo relacional simplificado (apenas os nomes das
relações e os seus campos), convertido segundo as regras a partir do modelo de
classes UML do domínio do problema completo (cujos requisitos estão especificados
em 1.1 e 1.3). Todas as restrições de integridade que não podem ser captadas no
modelo relacional devem ser listadas. (Máximo: 2 páginas).
4. Tabelas em SQL – Instruções SQL necessárias para criar as tabelas respectivas
na base de dados. Não se esqueça de especificar todas as restrições de integridade
(chave, integridade referencial, e outras) suportadas pelo SGBD escolhido, assim
como todas as validações e valores por omissão. Deve inserir alguns registos de
1
teste nas tabelas embora estas instruções não devam aparecer no relatório.
(Máximo: 6 páginas).
5. Interrogações, Vistas e Segurança em SQL – Implementação em SQL das
interrogações listadas em 1.4. Teste as interrogações com os registos de teste e
apresente no relatório o código SQL correspondente. (Máximo: 2 páginas.)
1.1 Problema base
Pretende-se desenhar e implementar uma base de dados para gerir o inventário de
uma empresa. O inventário é um catálogo com todos os produtos que a empresa
comercializa. Neste caso, a empresa vende livros e CDs. O mercado de negócio
desta empresa já é suficientemente grande para justificar a existência de vários
armazéns espalhados pelo país. Cada um destes armazéns tem diferentes
quantidades em stock de cada um dos produtos que a empresa vende.
A base de dados deve guardar a seguinte informação:
Cada livro tem um título, um ou mais autores, e um ISBN (número único que é usado
para identificar o livro). Um livro tem um preço, um valor de IVA e pode ter um
desconto.
Os livros estão organizados em categorias (por exemplo, ficção, drama, fantasia,
banda desenhada, etc) de modo a facilitar a sua procura. Um livro tem tipicamente
um conjunto de palavras chave associadas que descrevem o conteúdo do livro.
Cada CD tem um nome de album, um ou mais artistas, uma editora discográfica,
uma identificação única (como o ISBN para os livros), um preço, um valor de IVA e
pode ter um desconto. Tal como os livros, os CDs estão organizados por categorias
(rock, jazz, clássico, etc). Os CDs pertencem a um tipo: single, double, edição
comemorativa, etc.
Um livro ou CD pode estar ou não disponível em vários armazéns. É importante
guardar a quantidade em stock de cada produto nos diferentes armazéns.
Cada armazém tem uma localização (constituída pelo nome da rua, cidade e código
postal), uma informação de contacto (número de telefone, número de fax, endereço
de mail) e um responsável sobre o qual se pretende guardar o nome e o contacto.
1.2 Modelo ER do problema base
Um modelo ER possível que corresponde aos requisitos descritos em 1.1 é o
seguinte:
2
Algumas das restrições de integridade (podem existir outras) que este modelo ER
não consegue captar são as seguintes:
RI1: Livro tem duas chaves candidatas: o id do produto e o ISBN
RI2: CD tem chaves candidatas: o id do produto e o código.
RI3: O tipo do CD pode ser: single, double, edição comemorativa, etc.
Pretende-se o diagrama de classes UML e todas as restrições de integridade que o
diagrama não consegue captar.
1.3 Requisitos do problema a resolver
A mesma empresa de comercialização de livros e CDs pretende um sistema de
facturação. Cada factura é um registo de uma transacção de venda. Pretende-se
armazenar todas as transacções de venda realizadas. Para cada encomenda feita
pelo cliente, existe normalmente uma factura. Esta factura guarda toda a informação
que diz respeito aos artigos comprados assim como informação sobre o cliente.
Aos requisitos da base de dados descritos em 1.1., deve acrescentar os seguintes:
Cada factura é identificada por um número único. A factura tem uma data de
encomenda associada. Uma factura pode incluir um ou mais produtos distintos
(livros e/ou CDs). Cada factura tem associado um comprador e um destinatário que
pode ser diferente do comprador (no caso de ser um presente). Para cada factura
existe um método de envio associado (expresso, normal, etc) com um preço distinto.
3
A factura inclui também o preço total que é calculado pela soma dos preços de todos
os produtos comprados acrescidos de IVA e do custo do envio. Deve existir também
informação sobre o método de pagamento da factura (cheque, cartão de crédito ou à
cobrança). Se o pagamento fôr feito por cartão de crédito, devem ser guardados os
detalhes do cartão (número e data de validade).
Como o cliente pode adquirir várias cópias do mesmo artigo, a quantidade adquirida
deve também constar da factura. Além desta informação, para cada produto que
consta numa factura, deve ser guardado o armazém do qual as unidades vão ser
retiradas (potencialmente provenientes de diferentes armazéns) e a data esperada
de entrega. A quantidade adquirida de cada produto tem que existir no armazém de
onde o produto é retirado.
Cada cliente (comprador e/ou destinatário) tem um nome e um endereço. O
endereço pode ser dividido em nome da rua, cidade e código postal. Adicionalmente,
pode existir informação de contacto como o telefone e endereço de mail.
1.4 Interrogações
Escreva em SQL (e teste com alguns registos) as seguintes interrogações:
A. Quais os nomes dos CDs duplos de rock?
B. Quais os produtos que não estão disponíveis em nenhum armazém (isto é, a
quantidade em stock é zero)?
C. Quais os armazéns que têm disponíveis todos os produtos comercializados
pela empresa?
D. Quais os autores que não têm livros na base de dados?
E. Quais as facturas que tem menor número de produtos?
F. Quais as facturas que têm produtos provenientes de diferentes armazéns?
G. Para uma dada cidade, quantas cópias de cada produto que consta na base
de dados, vão ser enviadas para essa cidade?
H. Suponha que existe um utilizador da base de dados que é o cliente (existente
em todos os esquemas de BD Oracle de cada grupo). O cliente só pode
consultar informação sobre os produtos, isto é a identificação, o nome, os
autores ou artistas (para livro ou CD), o preço, desconto (se existir), a
categoria, o tipo (se CD) e a quantidade existente em stock.
Utilizando os mecanismos de segurança do SQL e suportados pelo SGBD,
garanta que o cliente só pode visualizar esta informação, não a podendo
obviamente modificar.
Antes de testar as interrogações não se esqueça de inserir registos nas tabelas que
permitam obter resultados. Pode usar um utilitário de importação/exportação para
carregar directamente os registos a partir de ficheiros de texto ou escrever um
programa que cria registos com valores aleatórios.
4