instituto politécnico do porto - Dei-Isep

INSTITUTO POLITÉCNICO DO PORTO
INSTITUTO SUPERIOR DE ENGENHARIA DO PORTO
DEPARTAMENTO ENGª INFORMÁTICA
EXERCÍCIO 01 DE BDDDA-2006-2007 - 2ºS.
Será realizado nas aulas práticas de 30 de Abril a 08 de Junho de 2007.
A avaliação será efectuada entre 11 e 15 de Junho de 2007.
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 contructores da linguagem.
• Criar
• Criar
• Criar
• Criar
• Criar
views
procedimentos armazenados e funções definidas pelo utilizador
triggers e garantir o seu cumprimento
listagens
um menu de entrada (valorização).
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.
EMPRESA DE ALUGUER DE AUTOMÓVEIS
Uma empresa de aluguer de automóveis e reboques possui 45 escritórios espalhados pelo país, e
uma frota total de 1.180 veículos distribuídos por estes escritórios.
A empresa necessita de uma base de dados que permita o desenvolvimento de uma aplicação para
o controlo dos veículos e dos contratos de aluguer, mantendo-se o registo histórico de todas as
operações realizadas.
Cada veículo disponível para aluguer está associado a um único escritório, que pode ter vários
veículos disponíveis.
Na mesma cidade podem existir vários escritórios (Porto tem três escritórios), mas em cada
cidade onde a empresa está presente existe sempre um escritório que é designado como escritório
principal.
Cada contrato de aluguer deve conter os dados do cliente e do veículo, identificando sempre o
escritório onde o veículo foi alugado e o escritório onde o veículo foi devolvido. Esta última
informação não é conhecida no momento da assinatura do contrato, sendo actualizada no
momento da devolução. Para beneficiar os clientes, um veículo pode ser devolvido em qualquer
escritório. Após a devolução, não é obrigatório que o veículo seja enviado para o escritório de
origem. Da mesma forma, quando é efectuado um aluguer num determinado escritório, não é
requisito obrigatório que o veículo a ele pertença. Concluindo, em qualquer escritório é possível
alugar e devolver veículos de qualquer outro escritório.
A transferência de veículos de escritório para escritório tem custos associados. Esse custo é
função da distância entre o escritório de saída e o de depósito. Para um determinado aluguer, o
lucro da empresa é o valor pago pelo cliente deduzido dos custos de transferência para o
escritório onde o veículo é levantado, obviamente apenas se houver a necessidade de a fazer.
A distância entre escritórios é definida considerando que entre dois escritórios em particular
ligados directamente, apenas existe um percurso com uma determinada distância. Por exemplo,
entre os escritórios e1 e e2 há apenas um percurso que dista 100Km. Poderão existir outros
percursos entre e1 e e2, mas havendo, terão obrigatoriamente de passar por outros escritórios.
Embora um cliente possa alugar mais do que um veículo, é sempre realizado um contrato em
separado para cada veículo alugado. Cada contrato é realizado por um único cliente, que pode ser
particular ou empresa.
Informações complementares:
 Cada escritório tem um código que o identifica, além de um nome, endereço e um gerente.
 Cada veículo tem um código identificador, a cidade onde foi registado, a matrícula, a
marca, o modelo e a categoria. Para todos os veículos é mantida a informação sobre a data
em que foi realizada a última manutenção. Para os automóveis é necessário registar a
quilometragem actual e o nível do depósito de gasolina/gasóleo.
 A empresa actualmente classifica seus veículos em cinco categorias: A (luxo, capacidade
6 passageiros), B (luxo, capacidade 5 passageiros), C (passeio, capacidade 5 passageiros),
D (popular, capacidade 4 passageiros) e R (reboque).
 Para cada pessoa jurídica é atribuído um código único, além de manter informações sobre
nome da empresa, endereço e pessoa de contacto. Para cada pessoa física regista-se o
nome do cliente, telefone, telemóvel, endereço e detalhes sobre sua carta de condução,
como número e data de validade. Caso um cliente danifique ou abandone um veículo ou
não pague o aluguer, o mesmo será registado como cliente de risco e não poderá mais
alugar veículos na empresa.
 Um contrato tem um número que é único dentro do escritório. Entretanto cada escritório
local tem uma numeração independente para os contratos de aluguer, e um mesmo
número de contrato pode ser repetido para alugueres efectuados noutros escritórios.
Os valores de aluguer são determinados pelo tipo do veículo, e são compostos por valor da
diária e valor por quilómetro andado, sendo que para o caso de reboques é cobrado apenas o
valor da diária.
Requisitos globais
Esquema relacional desenvolvido na ferramenta MS-Visio
Script de criação do modelo de dados
Script para inserir valores (além dos valores base especificados)
Requisitos particulares
Implementação de toda a lógica do negócio em Oracle PL/Sql. Não é obrigatório fazer
uma interface gráfica. Todo o trabalho poderá ser testado executando blocos de código
PL/Sql e analisando a saída para o ecrã.
 Função que devolva a menor distância entre dois escritórios.
f_menor_dist(ex integer, ey integer) : integer
É uma função recursiva. Deverá percorrer todas as rotas possíveis desde ex até ey, e
devolver a menor das distâncias.
 Procedimento para criar a rede de escritórios
p_cria_rede(e1 integer, e2 integer, dist integer)
Este procedimento deve fazer uso da função anterior. Antes de criar a rota deve verificar
se já existe uma rota entre os dois escritórios especificados, ou se já existe uma rota
mais curta que passe por outros escritórios. Em caso afirmativo não criará a nova rota,
apresentando no ecrã uma mensagem de aviso.
 Procedimento para listar veículos disponíveis para aluguer
p_lista_veic_aluguer(escritório int, categoria char, data_ini date, data_fim date)
Mostra no ecrã os veículos que estão disponíveis para aluguer, no período especificado,
considerando todas as viaturas que estejam disponíveis, independentemente do
escritório ao qual pertencem. Para esse período, deve excluir do resultado as viaturas
que estão alugadas e aquelas que têm manutenção programada.
Deve ordenar o resultado por ordem crescente de custo para a empresa. O custo depende
da distância relativa do escritório onde se encontra o veículo até ao escritório
especificado no aluguer.
 Procedimento para registo do contrato de aluguer (reserva)
p_regista_contrato(escritório int, cliente integer, categoria char, data_ini date,
data_fim date)
Este procedimento permite registar o aluguer de uma viatura da categoria especificada.
Gera automaticamente um número de reserva.
 Procedimento para listar a necessidade de veículos, por escritórios e
numa determinada data
p_lista_necessidades(data date)
Lista no ecrã, por escritório, quais os veículos que terão obrigatoriamente de estar
disponíveis na data especificada.
 Procedimento para registo de transferência de veículos entre escritórios.
p_tranf_veiculo(matricula char, ex integer, ey integer)
Altera a localização actual da viatura. Implica a actualização do nº de quilómetros da
mesma.
 Procedimento para levantamento de uma reserva
p_levanta_reserva(reserva integer)
Torna efectivo o aluguer. Nesta altura o veículo já tem de estar no escritório respectivo,
e o seu estado passa a “Alugado”. Os estados possíveis são: “Disponível”,
“Manutenção” e “Alugado”.
 Procedimento para entrega de uma reserva
p_entrega_reserva(escritório int, matricula char, km_efectuados int)
É gerada a factura a entregar ao cliente, e a quilometragem da viatura é actualizada.
 Procedimento para registo da manutenção das viaturas
p_manutencao(matricula char, entrada_saida integer)
Permite dar entrada ou saída de uma viatura na manutenção. Entrada (entrada_saida =
0). Saída (entrada_saida = 1). O estado da viatura tem de ser actualizado.
Nota:
Há processos que podem ser implementados usando triggers. Por exemplo, a alteração
do estado de uma viatura pode ser feita através de triggers, já que ao fazer o
levantamento de uma reserva ou ao registar manutenções, podemos automaticamente
actualizar o estado da referida viatura.
Há outras situações que não foram pedidas mas que são importantes. Por exemplo,
listagem de viaturas que excederam a quilometragem e em consequência devem ir para
a manutenção, etc.
Todas as 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 se tudo aquilo que
foi pedido já estiver feito.