Implementação de uma base de dados para - Dei-Isep

INSTITUTO POLITÉCNICO DO PORTO
INSTITUTO SUPERIOR DE ENGENHARIA DO PORTO
DEPARTAMENTO ENGª INFORMÁTICA
TRABALHO PRÁTICO DE BDDAD - 2009-2010 - 1ºS.
Será realizado nas aulas práticas de 9 de Novembro a 6 de Dezembro de 2009.
A avaliação será efectuada entre 7 a 12 de Dezembro nas aulas práticas da disciplina,
segundo calendarização de apresentações que será decidida e comunicada pela docente
das aulas práticas.
O relatório deverá ser entregue até 14 de Dezembro de 2009.
A componente do trabalho prático da disciplina corresponde a 40% da nota final.
Cada aluno pode ter uma classificação diferente da dos colegas de grupo, reflectindo
deste modo o seu desempenho no trabalho e na discussão do mesmo.
O trabalho será dividido em 3 fases:
1. Modelação e Implementação da BD
a. Elaborar o esquema relacional: envolve a identificação das entidades e
relações relativamente ao problema descrito e que poderá ser completado
e trabalhado pelos alunos. O modelo deve estar normalizado e deve
indicar explicitamente as escolhas efectuadas.
b. Criação da BD relacional: criar tabelas e restrições tendo em conta o
modelo de dados desenvolvido. Introdução de dados para exemplificação
de funcionamento. (utilização do SQL para criação das tabelas e inserção
dos dados).
2. Produzir scripts em PL/SQL para questões com diferentes graus de
complexidade e exemplificativas do uso de todos os construtores da linguagem.
• Criar
• Criar
• Criar
• Criar
views
procedimentos armazenados e funções definidas pelo utilizador
triggers e garantir o seu cumprimento
listagens
3. Relatório final
O relatório deverá conter uma capa contendo a seguinte informação:



Número de identificação do grupo
Tema do trabalho
Número e nome de todos os alunos do grupo
O relatório deverá constar, pelo menos:




Uma secção com a descrição do tema e objectivos da base de dados;
Uma secção com o modelo de dados, contendo as opções tomadas;
Uma secção com o código SQL que criou as tabelas e restrições;
Uma secção com o código SQL usado para as views, triggers, stored
procedures e functions;
Uma secção contendo uma discussão de limitações/opções tomadas para a
implementação da BD.
GESTÃO DE IMOBILIÁRIA
Implementação de uma base de dados para informatização da empresa ISEPIMOB. A
ISEPIMOB actua no mercado imobiliário (moradias, apartamentos e terrenos), tendo
basicamente três processos:
1. Angariação de imóveis: Processo que permite o registo de informação sobre
imóveis. No processo de angariação fica acordado o lucro, ou seja, a
percentagem negociada sobre o valor da venda.
2. Angariação de clientes: Processo de registo de potenciais compradores, sendo
guardadas as suas preferências (por exemplo, tipologia T3, andar ou moradia,
preço máximo, localização, etc.).
3. Venda: Processo de venda de imóveis. Na estrutura organizacional de
colaboradores da empresa, cada nó superior tem uma percentagem financeira
sobre o nó inferior (ver exemplo), ou seja, o vendedor, o angariador, assim como
todos os colaboradores hierarquicamente superiores, beneficiam de comissões
sobre as vendas efectuadas. As vendas e as angariações só podem ser efectuadas
pelos elementos que não têm inferiores hierárquicos. Quando se efectiva uma
venda, o funcionário responsável pela mesma e o angariador do imóvel (assim
como todos os respectivos superiores) têm uma comissão percentual sobre o
lucro negociado.
António Tavares
10%
Afonso Santos
25%
Jorge Manuel
10%
Afonso Manuel
50%
Angariação 5%
José Jota
30%
Angariação 5%
Susana Reis
3%
Manuela Mota
12%
Maria Oliveira
40%
Angariação 5%
Marta Silva
30%
Angariação 5%
Rui Nuno
35%
Angariação 5%
Marco Manuel
30%
Angariação 5%
No exemplo anterior, para uma venda de um imóvel com um lucro de 1000€, tendo o
imóvel sido angariado pela Marta Silva e a venda efectuada pelo José Jota, as
percentagens de comissões serão:
José Jota:
30% * 1000
= 300€
Jorge Manuel:
10% * 300
= 30€
Afonso Santos:
25% * 30
= 7.5€
Marta Silva:
5% * 1000
= 50€
Susana Reis:
3% * 50
= 1.5€
António Tavares:
10% * 7.5 + 10% * 1.5
= 0.9€
Total a pagar a funcionários:
= 392.9€
A empresa pretende que o seu novo sistema informático, para além de suportar os
processos descritos anteriormente, consiga dar resposta aos seguintes requisitos:
a) TRIGGER
Sempre que for efectuado o registo de um imóvel, devem ser notificados
todos os clientes cujas preferências individuais anteriormente definidas se
enquadram nas características do imóvel angariado (basta ter pelo menos 3
requisitos compatíveis, por exemplo, tipologia, gama de preços, cidade). De
igual modo, os angariadores desses clientes também devem ser notificados.
As notificações devem ser simuladas com a inserção de um registo
(mensagem) numa tabela.
b) FUNCTION fnc_percentagem(func_id integer, vend_id integer, ang_id integer)
Função que devolva a percentagem a que um determinado funcionário tem
direito, quando especificados dois funcionários (vendedor e angariador).
Note-se que, nada impede que possam ser ambos o mesmo funcionário.
c) PROCEDURE prc_comissoes(nr_venda integer)
O cálculo das comissões a pagar aos funcionários é feito a pedido do
utilizador. O cálculo não implica o pagamento automático (os funcionários
só recebem no final do mês), no entanto, as comissões a pagar devem ficar
numa conta corrente dos respectivos funcionários. Não deve ser possível
voltar a processar o cálculo das comissões para uma determinada venda se
esta já tiver sido paga. Em todos os outros casos, um novo processamento
implica a eliminação prévia dos registos da conta corrente do funcionário
(para essa venda).
d) PROCEDURE prc_validacao
Deve haver um processo de validação das comissões. Não deverá ser
permitido que o total de comissões a pagar seja superior a 60% do lucro das
vendas, de modo a que a empresa também tenha o seu lucro.
e) PROCEDURE prc_prospects
Pretende-se com esta funcionalidade obter uma relação dos imóveis que
verificam determinadas características, definidas por intermédio dos
seguintes atributos:
1. Preço
2. Área (m2)
3. Localização (baixa, centro, periferia, …)
4. Região (Porto, Maia, …)
5. Tipologia (Terreno, V1, V2, …, T1, T2, …)
6. Acabamentos (regulares, luxo, …)
7. Estado (novo, usado, mais de 30 anos, …)
Esta relação deve ser ordenada da seguinte forma. Primeiro são apresentados
os imóveis que verificam todas as características, em seguida, os que
verificam todas menos o preço, depois os que verificam todas menos o preço
e a área, e assim sucessivamente.
Requisitos globais
Esquema relacional (Visio, jpg, etc.).
Script (SQL_DDL) para criação do esquema relacional, incluindo todas as restrições de
integridade.
Script (SQL-DML) para inserir alguns registos básicos nas tabelas.
Requisitos particulares
Implementação de toda a lógica em Oracle PL/Sql.
Todo o trabalho poderá ser testado executando blocos de código PL/Sql e analisando a
saída para o ecrã.
Nota:
São permitidas alterações à descrição efectuada desde que seja no sentido de
clarificação do funcionamento do modelo.
Implementações extra ficarão ao critério de cada grupo de trabalho e serão devidamente
valorizadas. No entanto, só deverão ser implementadas após o que foi solicitado.