Departamento de Engenharia Electrotécnica e de Computadores
Licenciatura em Engenharia Electrotécnica e de Computadores
5.º ANO – 1.º SEMESTRE
SISTEMAS DE INFORMAÇÃO – 2001/2002
TRABALHO PRÁTICO
Implementação de uma base de dados na
Companhia de Seguros
PAGA & CALA
Trabalho elaborado por:
Joel Pedro Peixoto de Carvalho
Jorge Miguel Lopes Correia
Turma: 5EEC12(T)
Eng.ª Ana Paiva
Dezembro 2001
PAGA & CALA - Companhia de Seguros
Sistemas de Informação 2001/2002
INDÍCE :
I
Descrição do Problema
II
Modelo Entidade-Associação
III
Modelo Relacional
IV
Restrições de Integridade
V
Criação da Instância Exemplo
VI
Os Forms criados
VII
Os Webforms criados
VII
Os Reports criados
IX
Elaboração das Perguntas à Base de Dados
X
Estudo da Normalização
XI
Conclusões
Joel Carvalho e Jorge Correia
2 / 21
Sistemas de Informação 2001/2002
PAGA & CALA - Companhia de Seguros
I
DESCRIÇÃO DO PROBLEMA
A companhia de seguros Paga & Cala dedica-se única e exclusivamente à elaboração de
seguros no ramo automóvel.
O tipo de apólices celebradas entre a seguradora Paga & Cala e o segurado do seguinte
tipo:
Jota Bartolomeu, cliente do sexo Masculino, portador do B.I. nº 11439999 e do numero
de contribuinte 255366114, morador na Praceta Aqui do Lado n.º100, 4465 S. Mamede de
Infesta, telefone 229060505 segurou nesta companhia uma viatura automóvel de marca
Porsche modelo Carrera 4 em 11 de Dezembro de 2001. Esta apólice fica registada com nº
4473193.
O seguro é do tipo A, para um montante máximo de 100.000.000$00, a que corresponde
uma anuidade de 35.000$00.
Os 4 tipos de seguros praticados pela companhia, respectivo montante coberto e
anuidades a pagar encontram-se referidos na seguinte tabela:
Categoria
A
B
C
D
Montante Máximo
100.000.000$00
50.000.000$00
20.000.000$00
10.000.000$00
Anuidade
35.000$00
20.000$00
10.000$00
7.000$00
A Paga & Cala mantêm um registo actualizado dos acidentes que envolvem quaisquer
viaturas seguradas por si.
Esse registo assume a forma seguinte:
A data do acidente; o nome, a data de nascimento, a morada, o numero do BI, o código
postal, o número de contribuinte e o telefone do condutor da viatura na altura do acidente e o
montante pago pela companhia para compensar os estragos causados. É também registada
a matrícula da viatura.
Joel Carvalho e Jorge Correia
3 / 21
Sistemas de Informação 2001/2002
PAGA & CALA - Companhia de Seguros
II
MODELO ENTIDADE-ASSOCIAÇÃO
O primeiro passo da elaboração da base de dados da companhia de seguros Paga &
Cala
passa como é obvio pela criação do diagrama entidade associação no
Oracle
Designer 6i utilizando a ferramenta Entity Relationship Diagrammer.
Os tipos de dados de cada atributo foram especificados tendo sido também também
escolhidas as chaves de cada uma das entidades.
As entidades geradas e a sua função na base de dados são as seguintes:
TIPO1 - Entidade que especifica o tipo de apólice negociado pela companhia cujo
conteúdo foi já referido acima, a chave desta entidade é obviamente à categoria da apólice
(A,B,C ou D).
APOLICE1 - Entidade onde estão registadas todos as apólices de seguro automóvel
celebradas entre a Paga & Cala
e determinado cliente. O
numero de matricula
(MATRICULA) é portanto a chave desta entidade vez que é uma forma de identificação única
para um veiculo. È salientar o facto de uma apólice se referir apenas a um veículo, podendo
no entanto o mesmo segurado possuir várias apólices. Os campos adicionais especificam os
dados pessoais (NOME, MORADA, CODIGOPOSTAL, DATANASC,BI, NUMCONTRIB,
SEXO, TELEFONE), alguns dados adicionais do veiculo (MARCA, MODELO) bem como a
data de celebração de contrato (DATAINICIO).
REGISTO1 - Esta entidade contem o registo de acidentes que envolveram quaisquer
veículos segurados pela Paga & Cala. A data do acidente (DATA) é a chave, uma vez que
através dela juntamente com a matricula do veículo, campo da entidade APOLICE1, fica
especificado de forma única um acidente que tenha envolvido um veículo segurado.
NOTA: De referir aqui uma das limitações da nossa base de dados, uma vez que assumimos que um veículo automóvel não sofre dois
acidentes no mesmo dia e hora (o que na verdade também é extremamente improvável).
Esta entidade contem uma outra série de campos que especificamos dados pessoais do
condutor do veículo segurado (NOME, MORADA, CODPOSTAL, DATANASC, SEXO,
TELEFONE, NUMCONTRIB, BI). Note-se que não necessariamente tem de ser o possuidor
da apólice o condutor do automóvel segurado. Como era de esperar é também esta entidade
que
conterá
o
valor
de
compensação
a
atribuir
ao/s
veiculo/s
danificado/s
(MONTANTECOMP).
PAGAMENTO1 – Esta última entidade aqui referida refere-se aos pagamentos por parte
dos segurados das respectivas apólices. A data (DATA) do saldar da divida para com a Paga
& Cala, juntamente com a matrícula do veículo, campo da entidade APOLICE1, especificam
univocamente o pagamento de uma apólice, daí que este campo seja a chave desta
entidade.
Para facilitar a compreensão das relações entre entidades é apresentado desde já o
modelo entidade-associação que caracteriza a base de dados:
Joel Carvalho e Jorge Correia
4 / 21
Sistemas de Informação 2001/2002
PAGA & CALA - Companhia de Seguros
TIPO1
# CATEGORIA
* ANUIDADE
* MONTMAX
a que pertence a
do
APOLICE1
REGISTO1
#
*
*
*
*
*
*
*
*
*
DATA
BI
CODIGOPOSTAL
DATANASC
MONTANTECOMP
MORADA
NOME
NUMCONTRIB
SEXO
TELEFONE
que se refere a
onde fica o
#
*
*
*
*
*
*
*
*
*
*
o
o
MATRICULA
BI
CODIGOPOSTAL
DATAINICIO
DATANASC
MARCA
MODELO
MORADA
NOME
NUMCONTRIB
SEXO
NUMACID
TELEFONE
a que se refere o
da
PAGAMENTO1
# DATA
o PERCENTAGEMDESC
o VALOR
Os diversos tipos de atributos de cada instancia encontram-se referidos na tabela abaixo
TIPO1
ATRIBUTOS
TIPO
CATEGORIA
CHAR
ANUIDADE
NUMBER
MONTMAX
NUMBER
APOLICE1
ATRIBUTOS
TIPO
MATRICULA
CHAR
BI
NUMBER
CODIGOPOSTAL
NUMBER
DATAINICIO
DATE
DATANASC
DATE
MARCA
CHAR
MODELO
CHAR
MORADA
CHAR
NOME
CHAR
NUMCONTRIB
NUMBER
SEXO
CHAR
NUMACID
NUMBER
TELEFONE
NUMBER
REGISTO1
ATRIBUTOS
TIPO
DATA
DATE
BI
NUMBER
CODIGOPOSTAL
NUMBER
DATANASC
DATE
MONTANTECOMP NUMBER
MORADA
CHAR
NOME
CHAR
NUMCONTRIB
NUMBER
SEXO
CHAR
TELEFONE
NUMBER
PAGAMENTO1
ATRIBUTOS
TIPO
DATA
DATE
PERCENTAGEMDESC CHAR
VALOR
NUMBER
Note-se que a designação not null à frente de cada atributo define o carácter de
obrigatoriedade do atributo, isto é, todos os atributos distinguidos com not null devem ser
obrigatoriamente preenchidos antes de serem inseridos na base de dados.
Através do esquema entidade associação facilmente se verifica que todas as nossas
relações entre entidades são de 1 para M (um para muitos). Um exemplo rápido explicita isto
claramente:
-
Existem muitas apólices, mas apenas um tipo* é especificada por cada apólice**.
-
Existem muitos registos de acidentes*** mas cada registo só diz respeito a uma só
apólice.
-
Os pagamentos**** também são inúmeros no entanto cada pagamento diz respeito a
apenas uma apólice.
Joel Carvalho e Jorge Correia
5 / 21
PAGA & CALA - Companhia de Seguros
-
Sistemas de Informação 2001/2002
O facto de alguns campos serem iguais deve-se a servirem para trabalharem com os
triggers e serem de informação, com excepção da PERCENTAGEMDESC que foi
introduzido mas não chegou a ser utilizado.
Notas:
*Tipo:
Um tipo pode ter várias apólices, porém não é obrigatório que isso aconteça.
**Apólice:
Uma apólice tem muitos pagamentos e refere-se a vários tipos. Porém esta relação é obrigatória, ou seja, uma apólice não tem
que ter obrigatoriamente registos assim como não tem que ter obrigatoriamente pagamentos.
***Registo:
Um registo tem que ter obrigatoriamente uma apólice. A relação entre a associação “registo” e a entidade “apólice” é M para 1.
****Pagamento:
Um pagamento é feito obrigatoriamente para uma apólice. A relação de M to 1 de “pagamento” para “apólice” representa isso
mesmo.
Joel Carvalho e Jorge Correia
6 / 21
PAGA & CALA - Companhia de Seguros
III
Sistemas de Informação 2001/2002
MODELO RELACIONAL
Este modelo relacional é gerado através do Design Editor do Oracle Designer 6i.
Pela análise do esquema relacional podemos verificar que a transformação do modelo
entidade-associação num modelo relacional resulta na “herança” de
atributos por parte
algumas entidades. Este fenómeno resulta das relações que existem entre as entidades.
A base de dados referente á companhia de seguros Paga & Cala é então composta
pelas seguintes tabelas:
-
TIPOS1 (CATEGORIA, ANUIDADE, MONTMAX)
-
APOLICES1 (MATRICULA, TIP1_CATEGORIA, BI, CODIGOPOSTAL, DATAINICIO, DATANASC,
MARCA, MODELO, NUMCONTRIB, SEXO, NUMACID, TELEFONE)
-
REGISTOS1 (APO1_MATRICULA, DATA, BI, CODIGOPOSTAL, DATANASC, MONTANTECOMP,
MORADA, NOME, NUMCONTRIB, SEXO, TELEFONE)
-
PAGAMENTOS1 (DATA, AP01_MATRICULA, VALOR, PERCENTAGEMDESC)
Torna-se então bem evidente, através do modelo relacional criado, o facto dos atributos
chave de uma entidade serem nalguns casos parcialmente herdados doutra entidade. No
nosso caso:
-
a chave de APOLICES1 é (MATRICULA, TIP1_CATEGORIA) é parcialmente
herdada de TIPOS1
-
a chave de REGISTOS1 é (APO1_MATRICULA, DATA) é parcialmente herdada de
APOLICES1
Joel Carvalho e Jorge Correia
7 / 21
PAGA & CALA - Companhia de Seguros
-
Sistemas de Informação 2001/2002
a chave de PAGAMENTOS1 (DATA, AP01_MATRICULA) é parcialmente herdada de
APOLICES1
Com este modelo todas as entidades se encontram relacionadas, ou seja, através de
alguns dos seus atributos podemos alcançar a informação contida noutras as tabelas.
Joel Carvalho e Jorge Correia
8 / 21
PAGA & CALA - Companhia de Seguros
IV
Sistemas de Informação 2001/2002
RESTRIÇÕES DE INTEGRIDADE
As restrições de integridade podem ser obtidas de várias formas, como por exemplo
através de triggers ou através da implementação de chaves primárias ou chaves
estrangeiras.
Todas as restrições de integridade foram obtidas recorrendo a triggers. De seguida são
apresentados os códigos fonte dos 5 triggers que foram implementados: trigger 10, trigger
12, trigger 13, trigger 14, trigger 15, trigger 19, trigger 23, trigger 25.
EVITA A INSERÇÃO DE MONTANTES COMPENSADOS COM VALORES NEGATIVOS
CREATE or replace TRIGGER trigger10 BEFORE INSERT or UPDATE ON registos1
for each row
BEGIN
IF (:new.montantecomp <= 0) THEN
RAISE_APPLICATION_ERROR(-20210,'O VALOR DO MONTANTE COMPENSADO NÃO PODE TOMAR
VALORES NEGATIVOS!!!');
END IF;
END;
EVITA A INSERÇÃO DE REGISTOS DE ACIDENTES COM DATA INFERIOR À DE HOJE
create or replace trigger trigger12
BEFORE INSERT OR UPDATE on registos1
for each row
begin
if (:new.data < sysdate)
then
raise_application_error
(-20210,'Inseriu uma data inválida');
end if;
end;
EVITA A INSERÇÃO DE APOLICES COM DATA INFERIOR À DE HOJE
create or replace trigger trigger13
BEFORE INSERT OR UPDATE on apolices1
for each row
begin
if (:new.datainicio < sysdate)
then
raise_application_error
(-20210,'Inseriu uma data inválida');
end if;
end;
EVITA A INSERÇÃO DE PAGAMENTOS COM DATA INFERIOR À DE HOJE
create or replace trigger trigger14
BEFORE INSERT OR UPDATE on pagamentos1
for each row
begin
if (:new.data < sysdate)
then
raise_application_error
(-20210,'Inseriu uma data inválida');
end if;
end;
Joel Carvalho e Jorge Correia
9 / 21
PAGA & CALA - Companhia de Seguros
Sistemas de Informação 2001/2002
EVITAR A INTRODUÇÃO DE APOLICES COM MATRICULAS IGUAIS
CREATE or replace TRIGGER trigger15
BEFORE INSERT or UPDATE ON apolices1
FOR EACH ROW
DECLARE
times number(3);
BEGIN
times:= 0;
SELECT COUNT(*) INTO times
FROM apolices1
WHERE matricula = :new.matricula;
IF (times != 0) THEN
RAISE_APPLICATION_ERROR(-20210,'Matricula já tem apólice!');
END IF;
END;
NAO PODE HAVER MAIS QUE UM PAGAMENTO POR ANO
CREATE or replace TRIGGER trigger21
before INSERT or UPDATE ON pagamentos1
FOR EACH ROW
DECLARE
contagem number(1);
BEGIN
select count(data) into contagem from pagamentos1
where to_char(pagamentos1.data,'YY')=to_char(:NEW.data,'YY')
and apo1_matricula=:NEW.apo1_matricula;
if(contagem=1)
then raise_application_error(-20210,'Já pagou este ano');
end if;
END;
ACTUALIZA OS VALORES SEGURADOS E A PAGAR AQUANDO DE NOVA APOLICE
CREATE or replace TRIGGER trigger19
AFTER INSERT or UPDATE ON apolices1
FOR EACH ROW
BEGIN
if :new.tip1_categoria='A'
if :new.tip1_categoria='B'
if :new.tip1_categoria='C'
if :new.tip1_categoria='D'
END;
then
update tipos1 set anuidade=35 where categoria='A';
update tipos1 set montmax=100000 where categoria='A';
end if;
then
update tipos1 set anuidade=20 where categoria='B';
update tipos1 set montmax=50000 where categoria='B';
end if;
then
update tipos1 set anuidade=10 where categoria='C';
update tipos1 set montmax=20000 where categoria='C';
end if;
then
update tipos1 set anuidade=7 where categoria='D';
update tipos1 set montmax=10000 where categoria='D';
end if;
CALCULA O VALOR DE ANUIDADE A PAGAR DE ACORDO COM OS ACIDENTES EXISTENTES OU NÃO
CREATE or replace TRIGGER trigger25
BEFORE INSERT or UPDATE ON pagamentos1
FOR EACH ROW
DECLARE
times number(3);
times2 number(3);
xpto number(15);
xpto2 number(15);
BEGIN
times:= 0;
Joel Carvalho e Jorge Correia
10 / 21
Sistemas de Informação 2001/2002
PAGA & CALA - Companhia de Seguros
times2:=0;
SELECT COUNT(*) INTO times
FROM registos1
WHERE
to_number(to_char(data,'YY'))=to_number(to_char(:new.data))-1
:new.apo1_matricula=apo1_matricula;
SELECT COUNT(*) INTO times2
FROM registos1
WHERE
to_number(to_char(data,'YY'))=to_number(to_char(:new.data))-2
:new.apo1_matricula=apo1_matricula;
select tip1_categoria into xpto2 from apolices1 where matricula=:new.apo1_matricula;
select anuidade into xpto from tipos1 where categoria=xpto2;
if (times=0) then
and
update pagamentos1 set valor=xpto;
elsif
(times=1) then
update pagamentos1 set valor=xpto+15000;
elsif
(times=2) then
update pagamentos1 set valor=xpto+35000;
elsif
(times=3) then
update pagamentos1 set valor=xpto+65000;
elsif
(times>3) then
and
raise_application_error(-20210,'Comece a deixar o carro em casa...');
update pagamentos1 set valor=xpto+100000;
end if;
end if;
if (times2=0) then
update pagamentos1 set valor=xpto-2000;
END;
Joel Carvalho e Jorge Correia
11 / 21
PAGA & CALA - Companhia de Seguros
V
Sistemas de Informação 2001/2002
SCRIPTS DE INICIALIZAÇÃO DAS INSTANCIAS
•
Instancia TIPOS1
insert into tipos1 values ('A', '100000000', '35000');
insert into tipos1 values ('B', '50000000', '20000');
insert into tipos1 values ('C', '20000000', '10000');
insert into tipos1 values ('D', '10000000', '7000');
•
Instancia APOLICES1*
insert into apolices1
(MATRICULA,TIP1_CATEGORIA,CODIGOPOSTAL,DATANASC,BI,MODELO,MORADA,MARCA,NUMCONTRIB,D
ATAINICIO,SEXO,NOME,TELEFONE) values ('15-25-AB','C','4465','11-12-80','12457899','127' ,'rua das couves 11'
,'fiat'
,'123456789','04-08-96','Masculino','Chico Santola' ,'225689759');
insert into apolices1
(MATRICULA,TIP1_CATEGORIA,CODIGOPOSTAL,DATANASC,BI,MODELO,MORADA,MARCA,NUMCONTRIB,D
ATAINICIO,SEXO,NOME,TELEFONE) values ('25-25-AD','A','4465','12-12-80','12457898','wdm' ,'rua dos nabos 12'
,'porsche' ,'123456788','05-08-96','Feminino','Xuana Molinha' ,'225689758');
insert into apolices1
(MATRICULA,TIP1_CATEGORIA,CODIGOPOSTAL,DATANASC,BI,MODELO,MORADA,MARCA,NUMCONTRIB,D
ATAINICIO,SEXO,NOME,TELEFONE) values ('35-25-AG','D','4465','13-12-79','12457897','xl' ,'rua das pencas 13'
,'fiat'
,'123456787','06-08-96','Masculino','Bigodalho Sapudo' ,'225689757');
insert into apolices1
(MATRICULA,TIP1_CATEGORIA,CODIGOPOSTAL,DATANASC,BI,MODELO,MORADA,MARCA,NUMCONTRIB,D
ATAINICIO,SEXO,NOME,TELEFONE) values ('45-25-AV','A','4466','14-12-79','12457896','xs' ,'rua das cenouras 14'
,'ferrari' ,'123456786','07-09-97','Feminino','Carlota das Neves','225689756');
insert into apolices1
(MATRICULA,TIP1_CATEGORIA,CODIGOPOSTAL,DATANASC,BI,MODELO,MORADA,MARCA,NUMCONTRIB,D
ATAINICIO,SEXO,NOME,TELEFONE) values ('55-25-AK','A','5466','15-12-78','12457895','hh' ,'rua dos tomates 15'
,'bmw'
,'123456785','08-08-97','Masculino','Rain Men'
,'225689755');
insert into apolices1
(MATRICULA,TIP1_CATEGORIA,CODIGOPOSTAL,DATANASC,BI,MODELO,MORADA,MARCA,NUMCONTRIB,D
ATAINICIO,SEXO,NOME,TELEFONE) values ('65-25-AL','D','6479','16-12-78','12457894','pico' ,'praceta do
manjericao 16' ,'ford'
,'123456784','09-10-98','Feminino','Urina Pacova' ,'225689754');
insert into apolices1
(MATRICULA,TIP1_CATEGORIA,CODIGOPOSTAL,DATANASC,BI,MODELO,MORADA,MARCA,NUMCONTRIB,D
ATAINICIO,SEXO,NOME,TELEFONE) values ('75-25-AE','B','7489','17-12-77','12457893','mk' ,'praceta dos
espinafres 17' ,'citroen' ,'123456783','08-11-99','Masculino','Jota Carvalho' ,'225689753');
insert into apolices1
(MATRICULA,TIP1_CATEGORIA,CODIGOPOSTAL,DATANASC,BI,MODELO,MORADA,MARCA,NUMCONTRIB,D
ATAINICIO,SEXO,NOME,TELEFONE) values ('85-26-AQ','D','8455','18-12-77','12457892','xpto' ,'avenida dos
agrioes 18' ,'ford'
,'123456782','07-12-00','Masculino','Faria Asakebrada' ,'225689752');
insert into apolices1
(MATRICULA,TIP1_CATEGORIA,CODIGOPOSTAL,DATANASC,BI,MODELO,MORADA,MARCA,NUMCONTRIB,D
ATAINICIO,SEXO,NOME,TELEFONE) values ('95-17-AP','A','9433','19-12-76','12457891','toto' ,'travessa das
ervilhas 19' ,'vw'
,'123456781','06-08-99','Masculino','Postas de Pescada','225689751');
insert into apolices1
(MATRICULA,TIP1_CATEGORIA,CODIGOPOSTAL,DATANASC,BI,MODELO,MORADA,MARCA,NUMCONTRIB,D
ATAINICIO,SEXO,NOME,TELEFONE) values ('15-28-AU','A','4132','20-12-76','12457890','mac' ,'rua da beterraba
20'
,'vw'
,'123456709','05-08-98','Feminino','Susanita Santos' ,'215689750');
insert into apolices1
(MATRICULA,TIP1_CATEGORIA,CODIGOPOSTAL,DATANASC,BI,MODELO,MORADA,MARCA,NUMCONTRIB,D
ATAINICIO,SEXO,NOME,TELEFONE) values ('15-29-GG','D','4212','21-12-75','22407896','ttl' ,'rua das vagens 21'
,'fiat'
,'123456719','04-08-97','Feminino','Little Debbie' ,'225689704');
insert into apolices1
(MATRICULA,TIP1_CATEGORIA,CODIGOPOSTAL,DATANASC,BI,MODELO,MORADA,MARCA,NUMCONTRIB,D
ATAINICIO,SEXO,NOME,TELEFONE) values ('15-35-JK','D','4211','21-11-75','22417896','tt' ,'rua das vagens 22'
,'vw'
,'123456729','03-08-96','Feminino','Marina Xau'
,'235689704');
insert into apolices1
(MATRICULA,TIP1_CATEGORIA,CODIGOPOSTAL,DATANASC,BI,MODELO,MORADA,MARCA,NUMCONTRIB,D
ATAINICIO,SEXO,NOME,TELEFONE) values ('15-45-AH','D','4245','21-10-75','22427896','sos' ,'rua das vagens 23'
,'opel'
,'123456739','02-08-95','Masculino','Joalho Pi'
,'245689704');
insert into apolices1
(MATRICULA,TIP1_CATEGORIA,CODIGOPOSTAL,DATANASC,BI,MODELO,MORADA,MARCA,NUMCONTRIB,D
ATAINICIO,SEXO,NOME,TELEFONE) values ('15-75-AK','A','4245','21-09-75','22437896','civic','rua das vagens 24'
,'honda' ,'123456749','01-08-96','Feminino','Martina Silves' ,'255689704');
insert into apolices1
(MATRICULA,TIP1_CATEGORIA,CODIGOPOSTAL,DATANASC,BI,MODELO,MORADA,MARCA,NUMCONTRIB,D
ATAINICIO,SEXO,NOME,TELEFONE) values ('15-85-AL','D','4245','21-08-75','22447896','hh' ,'rua das couves 21'
,'bmw'
,'123456759','01-08-96','Masculino','Tó Carro'
,'265689704');
Joel Carvalho e Jorge Correia
12 / 21
PAGA & CALA - Companhia de Seguros
Sistemas de Informação 2001/2002
insert into apolices1
(MATRICULA,TIP1_CATEGORIA,CODIGOPOSTAL,DATANASC,BI,MODELO,MORADA,MARCA,NUMCONTRIB,D
ATAINICIO,SEXO,NOME,TELEFONE) values ('15-95-AZ','C','4246','21-07-75','22457896','vi' ,'rua das pencas 21'
,'seat'
,'123456769','01-08-96','Feminino','Lia Ontem'
,'275689704');
insert into apolices1
(MATRICULA,TIP1_CATEGORIA,CODIGOPOSTAL,DATANASC,BI,MODELO,MORADA,MARCA,NUMCONTRIB,D
ATAINICIO,SEXO,NOME,TELEFONE) values ('15-15-AX','D','4744','21-06-75','22467896','clio' ,'rua das tomates 21'
,'renault' ,'123456779','02-08-96','Masculino','Lio travasso' ,'285689704');
insert into apolices1
(MATRICULA,TIP1_CATEGORIA,CODIGOPOSTAL,DATANASC,BI,MODELO,MORADA,MARCA,NUMCONTRIB,D
ATAINICIO,SEXO,NOME,TELEFONE) values ('15-25-AC','C','4744','21-05-75','22477896','vi' ,'rua das nabos 21'
,'seat'
,'123456789','03-08-97','Feminino','Andrea Kappa' ,'295689704');
insert into apolices1
(MATRICULA,TIP1_CATEGORIA,CODIGOPOSTAL,DATANASC,BI,MODELO,MORADA,MARCA,NUMCONTRIB,D
ATAINICIO,SEXO,NOME,TELEFONE) values ('15-35-AV','D','4944','21-04-75','22487896','sos' ,'rua das jarras 21'
,'opel'
,'123456799','04-08-98','Masculino','Marito Jó'
,'220689704');
insert into apolices1
(MATRICULA,TIP1_CATEGORIA,CODIGOPOSTAL,DATANASC,BI,MODELO,MORADA,MARCA,NUMCONTRIB,D
ATAINICIO,SEXO,NOME,TELEFONE) values ('18-38-BV','D','4944','22-04-75','25487396','sos' ,'rua das jarras 22'
,'opel'
,'123456799','05-08-99','Masculino','Marisa Chata' ,'220689704');
•
Instancia REGISTOS1
insert into registos1 values ('15-25-AB','09-12-98','1444','11-12-80','100000' ,'Masculino','225689759','12457899','rua
das couves 11' ,'Chico Santola','123456789');
insert into registos1 values ('15-25-AB','11-12-00','1474','04-08-77','400000' ,'Feminino','225689756','12457555','rua
do xilindro' ,'Gina da Bouça','123456755');
insert into registos1 values ('15-25-AB','11-12-01','1445','11-12-80','250000' ,'Masculino','225689759','12457899','rua
das couves 11' ,'Chico Santola','123456789');
insert into registos1 values ('15-95-AZ','11-12-01','4246','21-07-75','250000' ,'Feminino','275689704','22457896','rua
das pencas 21' ,'Lia Ontem' ,'123456769');
insert into registos1 values ('15-15-AX','11-12-00','4744','21-06-75','400000' ,'Masculino','285689704','22467896','rua
das tomates 21' ,'Lio travasso','123456779');
insert into registos1 values ('15-25-AC','11-12-99','4744','21-05-75','500000' ,'Feminino','295689704','22477896','rua
das nabos 21' ,'Andrea Kappa','123456789');
insert into registos1 values ('15-35-AV','11-12-98','4944','21-04-75','100000' ,'Masculino','220689704','22487896','rua
das jarras 21' ,'Marito Jó','123456799');
•
Instancia PAGAMENTOS1*
insert into pagamentos1 (DATA,APO1_MATRICULA,VALOR) values ('05-06-01','15-25-AB','4465');
insert into pagamentos1 (DATA,APO1_MATRICULA,VALOR) values ('05-07-01','25-25-AD','4465');
insert into pagamentos1 (DATA,APO1_MATRICULA,VALOR) values ('09-10-01','15-25-AC','4744');
insert into pagamentos1 (DATA,APO1_MATRICULA,VALOR) values ('09-11-01','15-35-AV','4944');
insert into pagamentos1 (DATA,APO1_MATRICULA,VALOR) values ('05-09-01','18-38-BV','4944');
insert into pagamentos1 (DATA,APO1_MATRICULA,VALOR) values ('06-09-01','15-15-AX','4744');
insert into pagamentos1 (DATA,APO1_MATRICULA,VALOR) values ('07-09-01','15-95-AZ','4246');
Nota: Tanto a entidade APOLICES1 como PAGAMENTOS1 necessitam aquando da geração de dados que se mencionem os nomes dos seus
campos uma vez que alguns destes são optativos.
Joel Carvalho e Jorge Correia
13 / 21
PAGA & CALA - Companhia de Seguros
VI
Sistemas de Informação 2001/2002
OS FORMS CRIADOS
Joel Carvalho e Jorge Correia
14 / 21
PAGA & CALA - Companhia de Seguros
VII
Sistemas de Informação 2001/2002
OS WEBFORMS CRIADOS
Joel Carvalho e Jorge Correia
15 / 21
PAGA & CALA - Companhia de Seguros
VIII
Sistemas de Informação 2001/2002
OS REPORTS CRIADOS
Joel Carvalho e Jorge Correia
16 / 21
Sistemas de Informação 2001/2002
PAGA & CALA - Companhia de Seguros
IX
ELABORAÇÃO DAS PERGUNTAS A BASE DE DADOS
1.
Indique os nomes dos condutores que não têm acidentes há 2 anos ou mais.
Álgebra relacional:
∏
NOME
[∏
SYSDATE (YY )− DATA(YY )> 2 , AP 01 _ MATRICULA
]
( REGISTOS1) >< APOLICES1
Linguagem SQL:
select nome from apolices1
where matricula in
(
select apo1_matricula
from registos1
where to_number(to_char(data,'YY'))
not in
( to_number(to_char(sysdate,'YY')),to_number(to_char(sysdate,'YY'))-1,to_number(to_char(sysdate,'YY'))-2)
);
Resposta da BD:
NOME
-------------------Chico Santola
Andrea Kappa
Marito Jó
2.
Qual o nome e o valor do último pagamento de apólice efectuado, pelo
possuidor da apólice do último veículo a ter um acidente?
Álgebra relacional:
∏
NOME ,VALOR
[σ
MAX ( DATA )
]
( PAGAMENTOS1) >< APOLICES1
Linguagem SQL:
select nome,valor,data
from apolices1,pagamentos1
where matricula=apo1_matricula and data in
(select max(data) from pagamentos1);
Resposta da BD:
NOME
VALOR
DATA
-----------------------------------------------------------------Marito Jó
4944
09-11-01
3.
Qual foi o lucro (ou prejuízo, se negativo) da seguradora com os seguros
efectuados no ano 2000?
4.
Álgebra relacional:
∏
SUM ( − MONTANTECOMP ,VALOR )
Joel Carvalho e Jorge Correia
[σ
DATA (YY ) = 00
( REGISTOS1) >< σ DATA(YY )=01 ( PAGAMENTOS1)
]
17 / 21
Sistemas de Informação 2001/2002
PAGA & CALA - Companhia de Seguros
Linguagem SQL:
select sum(-soma_comp+soma_pagamentos) Balanço2000
from (
select sum(montantecomp) soma_comp,sum(valor) soma_pagamentos
from registos1 r,pagamentos1 p
where to_number(to_char(r.data,'YY'))=00 and to_number(to_char(p.data,'YY'))=01
);
Resposta da BD:
BALANÇO2000
----------------------5534896
4.
Qual a matrícula, marca e modelo do veículo que regista mais acidentes no
último ano?
∏
Álgebra relacional:
MATRICULA , MARCA , MODELO
[σ
MAX ( COUNT ( AP 01 _ MATRICULA ))
( REGISTOS 1) >< σ DATA (YY )= SYSDATE (YY )−1 ( REGISTOS 1)
Linguagem SQL:
select matricula,marca,modelo from apolices1 where matricula=(
select apo1_matricula
from (select apo1_matricula,count(*) n
from registos1 where to_number(to_char(data,'YY'))=(select (to_number(to_char(sysdate,'YY')))-1 from dual)
group by apo1_matricula)
where n=(select max(n)
from (
select apo1_matricula,count(*) n
from registos1
group by apo1_matricula
))
);
Resposta da BD:
não foram seleccionadas linhas
Nota:
por lapso esquecemo-nos de inserir valores que nos permitissem obter resposta obvia a esta pergunta. No entanto parece-nos
obvia a boa formulação da mesma, não tendo sido obtido qualquer resultado uma vez que na nossa base de dados não existem veículos que
tenham sofrido ou estado envolvidos em mais do que um acidente durante o ultimo ano
5.
Quais os veículos que tiveram um acidente no ano de 1999?
Álgebra relacional:
∏
MATRIOCULA
[σ
DATA(YY ) =99 ,COUNT ( DATA ) =1
( REGISTO1)
]
Linguagem SQL:
select apo1_matricula from (select apo1_matricula,data,count(*) n
from registos1
group by apo1_matricula,data)
where to_number(to_char(data,'YY'))=99 and n=1;
Resposta da BD:
APO1_MATRICULA
---------------------------15-25-AC
Joel Carvalho e Jorge Correia
18 / 21
]
PAGA & CALA - Companhia de Seguros
X
Sistemas de Informação 2001/2002
NORMALIZAÇÃO
Ao construirmos o modelo entidade-associação, tivemos o cuidado de não termos
redundância. O esquema está na forma normalizada. Vamos seguir os passos necessários
para o estudo da normalização.
O método da normalização compreende 5 passos:
1- Determinar as dependências funcionais aplicáveis na relação.
2- Determinar as chaves a partir das dependências funcionais.
3- Reduzir o conjunto de dependências funcionais a uma forma minimal.
4- Verificar se a relação está normalizada ou, se pelo contrário, tem dependências
funcionais que causam redundância.
5- No caso de não estar normalizado, decompor a relação em duas ou mais relações
normalizadas.
De seguida seguiremos estes passos para as nossas tabelas:
Tabela TIPOS1
TIPOS1 (CATEGORIA,ANUIDADE,MONTMAX)
1- CATEGORIA → ANUIDADE MONTMAX
2- Chave CATEGORIA, como é evidente.
3- O conjunto de dependências funcionais já esta na forma minimal (ver ponto 1)
4- Verificação da forma normal de Boyce-Cood (BCNF), que diz que uma relação R está
na BCNF quando não tem nenhuma DF não trivial X → A, em que X não contém uma chave.
Como podemos constatar a nossa relação esta na forma BCNF.
CATEGORIA → ANUIDADE MONTMAX
5- Visto que a relação esta na forma BCNF, não é necessário decompor-se em várias
relações. Assim este ponto não se pode aplicar.
Tabela PAGAMENTOS1
PAGAMENTOS1 (DATA, APO1_MATRICULA, PERCENTAGEMDESC, VALOR)
1- DATA → APO1_MATRICULA, PERCENTAGEMDESC, VALOR
2- Chave DATA, como é evidente.
3- O conjunto de dependências funcionais já esta na forma minimal (ver ponto 1)
4- Verificação da forma normal de Boyce-Cood (BCNF), que diz que uma relação R está
na BCNF quando não tem nenhuma DF não trivial X → A, em que X não contém uma chave.
Como podemos constatar a nossa relação esta na forma BCNF.
Joel Carvalho e Jorge Correia
19 / 21
Sistemas de Informação 2001/2002
PAGA & CALA - Companhia de Seguros
DATA → APO1_MATRICULA, PERCENTAGEMDESC, VALOR: Obedece à BCNF
5- Visto que a relação esta na forma BCNF, não é necessário decompor-se em várias
relações. Assim este ponto não se pode aplicar.
Tabela REGISTOS1
REGISTOS1
(DATA,
APO1_MATRICULA,
BI,
CODIGOPOSTAL,
DATANASC,
MONTANTECOMP, MORADA, NOME, NUMCONTRIB, SEXO ,TELEFONE)
1- DATA MATRICULA → BI CODIGOPOSTAL DATANASC MONTANTECOMP
MORADA NOME NUMCONTRIB SEXO TELEFONE
2- Chave DATA MATRICULA, como é evidente.
3- O conjunto de dependências funcionais já esta na forma minimal.
DATA
MATRICULA
→ BI CODIGOPOSTAL DATANASC MONTANTECOMP
MORADA NOME NUMCONTRIB SEXO TELEFONE
4- Verificação da forma normal de Boyce-Cood (BCNF), que diz que uma relação R está
na BCNF quando não tem nenhuma DF não trivial X → A, em que X não contém uma chave.
Como podemos constatar a nossa relação esta na forma BCNF.
COD_CUR → TIPO_CUR DESIGN_CUR: Obedece à BCNF
5- Visto que a relação esta na forma BCNF, não é necessário decompor-se em várias
relações. Assim este ponto não se pode aplicar.
Tabela APOLICES1
APOLICES1 (MATRICULA, TIP1_CATEGORIA, BI, CODIGOPOSTAL, DATAINICIO,
DATANASC, MARCA, MODELO, MORADA, NOME, NUMCONTRIB, SEXO, NUMACID,
TELEFONE)
1- MATRICULA → TIP1_CATEGORIA BI CODIGOPOSTAL DATAINICIO DATANASC
MARCA MODELO MORADA NOME NUMCONTRIB SEXO NUMACID TELEFONE
2- Chave MATRICULA, como é evidente.
3- O conjunto de dependências funcionais já esta na forma minimal.
MATRICULA → TIP1_CATEGORIA BI CODIGOPOSTAL DATAINICIO DATANASC
MARCA MODELO MORADA NOME NUMCONTRIB SEXO NUMACID TELEFONE
4- Verificação da forma normal de Boyce-Cood (BCNF), que diz que uma relação R está
na BCNF quando não tem nenhuma DF não trivial X → A, em que X não contém uma chave.
Como podemos constatar a nossa relação esta na forma BCNF.
MATRICULA → TIP1_CATEGORIA BI CODIGOPOSTAL DATAINICIO DATANASC
MARCA MODELO MORADA NOME NUMCONTRIB SEXO NUMACID TELEFONE
5- Visto que a relação esta na forma BCNF, não é necessário decompor-se em várias
relações. Assim este ponto não se pode aplicar.
Joel Carvalho e Jorge Correia
20 / 21
Sistemas de Informação 2001/2002
PAGA & CALA - Companhia de Seguros
XI
CONCLUSÕES
Todo o trabalho efectuado na elaboração da base de dados da seguradora Paga &
Cala permitiu-nos adquirir alguma experiência na formulação e técnicas de projecto de uma
base de dados.
Devemos no entanto reconhecer que o nosso trabalho contem algumas limitações,
sendo que uma verdadeira base de dados de uma também verdadeira companhia de
seguros devesse ser manifestamente mais complexa.
Um esquema entidade-associação anterior da nossa base de dados era o seguinte:
TIPO
# CATEGORIA
* ANUIDADE
* MONTMAX
do
a que pertence a
APOLICE
onde fica o
#
*
*
*
o
MATRICULA
DATAINICIO
MARCA
MODELO
NUMACID
a que se refere o
que se refere a
da
REGISTO
# DATA
* MONTANTECOMP
que pertence a
do
CONDUTOR
que efectuou
#
*
*
*
*
*
BI
DATANASC
MORADA
NOME
SEXO
TELEFONE
PAGAMENTO
* DATA
* PERCENTAGEMDESC
o VALOR
efectuado pelo
titular da
SEGURADO
a que se refere o
# NUMCONTRIB
* CODIGOPOSTAL
No entanto, já numa fase avançada do nosso projecto não nos foi possível garantir,
inexplicavelmente, a manutenção da relação ISA entre CONDUTOR e SEGURADO. Apartir
daí(noite de terça-feira_11.12) refizemos a base de dados optando por um esquema mais
simples mas adequado ao que era pretendido por este projecto.
Os constantes erros do servidor blaster_db limitaram em muito o percurso normal
deste projecto, a criação por vezes inexplicável de tabelas desconhecidas é um exemplo
claro disso. No entanto a perseverança em busca de um resultado satisfatório, que fomos
obrigados a demonstrar, permitiu-nos obter alguma prática de manuseamento do Oracle 6i.
O nosso login
sinf24
A nossa password
jota
Joel Carvalho - [email protected]
Jorge Correia - [email protected]
Dezembro 2001
FEUP
Joel Carvalho e Jorge Correia
21 / 21