FACULDADE DE ENGENHARIA DA UNIVERSIDADE DO PORTO
Licenciatura em Engenharia Electrotécnica e de Computadores
5º Ano, 1º Semestre
Sistemas de Informação - 2000/2001
TRABALHO PRÁTICO
Sistema de Gestão de Bases de Dados
Doenças
Alunos:
•
•
Filipe Alexandre da Silva Vila Real
Nuno José Morais Felicio
Data:
15 de Dezembro de 2000
Introdução
•
Objectivo
Pretende-se com este trabalho a experimentação das matérias expostas na disciplina e a
familiarização com o uso de SGBDs comerciais (Oracle) e ferramentas de desenvolvimento de
aplicações (Designer 2000).
•
Âmbito
Este relatório surge da necessidade de proceder à analise das diversas fases de
desenvolvimento da criação de um SGBDs, englobado no trabalho prático da disciplina de
Sistemas de Informação.
•
Organização
O trabalho é constituído por 6 partes distintas.
Inicia-se com uma breve descrição do problema em análise, sendo de seguida
apresentado o modelo entidade-associação e uma breve descrição dos seus componentes.
Seguem-se o esquema relacional e as instâncias exemplo criadas, para posteriormente
serem elaboradas 5 perguntas à base de dados, em álgebra relacional e em SQL, sendo também
apresentados os respectivos resultados.
O relatório termina com um breve estudo da normalização do esquema obtido.
Descrição do Problema
Um médico actualizado precisa de ter o tratamento da informação clínica dos seus
pacientes automatizado. Estes têm ou tiveram doenças, fizeram exames, foram sujeitos a
tratamentos, apresentam contra-indicações para determinados medicamentos, marcam consultas.
Modelo entidade-associação
É o modelo que representa a política de uma organização à cerca dos dados referentes
ao sistema de informação.
A modelização da informação apresenta o mérito de:
•
levantar questões sobre as regras da organização, obrigando à definição clara da
política para os dados;
•
salientar as novas necessidades de informação, de acordo com o que se espera do
sistema para satisfazer as necessidades dos utilizadores;
•
resolução de incoerências da política actual, devido a tratamentos deficientes e por
vezes contraditórios.
Podemos salientar duas grandes vantagens do modelo entidade-associação:
•
permite a construção do modelo por fases, partindo dos grandes objectos de informação
(entidades) e identificando as suas inter-relações (associações);
•
produz bons elementos de comunicação com os utilizadores.
Analisando o nosso problema, começamos por identificar quatro entidades -
objectos/conceitos com interesse para a organização sobre os quais se pretende guardar
informação - EXAME, CONSULTA, PACIENTE, TRATAMENTO.
Para cada entidade é necessário conhecer as suas propriedades relevantes para o
sistema. Atributo é qualquer propriedade de uma entidade. São sempre elementos indivisíveis
de informação e assumem diferentes valores de um domínio.
É necessário distinguir entre dois tipos de atributos: Identificadores ou Chaves
Candidatas e Descritores.
Os Identificadores (#) são os atributos de uma entidade que identificam uma ocorrência
específica dessa entidade, distinguindo-a das restantes. Para que um atributo seja identificador é
necessário que não existam duas ocorrências distintas dessa entidade em que esse atributo tenha
o mesmo valor. Nalguns casos o Identificador pode ser a combinação de dois ou mais atributos,
como é o caso da entidade Exame que possuí como chaves candidatas os atributos data e tipo.
Os Descritores (∗) são os atributos que apenas descrevem ou caracterizam as
ocorrências de um entidade. Estes podem ainda assumir um carácter opcional (ο).
As associações representam as interligações relevantes entre as entidades do sistema.
Cada um dos seus extremos caracteriza-se por: um nome, um grau, um tipo de associação.
Tomando como exemplo a associação entre as entidades PACIENTE, EXAME: um paciente fez
vários exames (eventualmente só um ou mesmo nenhum), e um exame foi obrigatoriamente
feito apenas por um e só um paciente.
Uma entidade pode assim participar numa associação de duas formas:
Obrigatória, não pode existir nenhuma ocorrência dessa entidade que não esteja
associada a alguma ocorrência da outra entidade que participa na associação;
Não Obrigatória, podem existir ocorrências dessa entidade que não estejam associadas a
alguma ocorrência da outra entidade que participa na associação.
De referir ainda que EXAME e CONSULTA são entidades fracas. Uma entidade E1
diz-se fraca em relação à entidade E2 se qualquer chave candidata de E1 é composta por uma
chave candidata de E2.
Existem certas restrições ao domínio dos atributos que poderão ser garantidas pelo
SGBD. No esquema em anexo são visíveis algumas dessas restrições.
EXAME
# DATA
# TIPO
o LOCAL
feito
fez
TRATAMENTO
PACIENTE
#
*
*
*
o
BI
MORADA
NOME
TELEFONE
OBS
sujeito
realizado
marca
para
CONSULTA
# DATA
* HORA
Modelo Entidade-Associação
Base de dados Doentes
#
*
*
*
*
*
o
TRAT_ID
DESCRIÇÃO
DIAGNOSTICO
DURAÇÃO
EVOLUÇÃO
INICIO
CONTRA_INDIC
Esquema Relacional
Depois de implementado o modelo entidade-associação foi gerado o respectivo esquema
relacional.
Cada entidade dá normalmente origem a uma tabela, com:
•
Identificador da Entidade → Chave da Tabela
•
Descritores da Entidade → Outros Atributos da Tabela
•
Identificadores de outras entidades que lhe estejam associadas → Chaves
Estrangeiras
Exemplo: Paciente(bi, nome, morada, telefone, obs)
De entre as chaves candidatas de um relação, escolhe-se uma para ser a chave efectiva
da mesma, a essa chave dá-se o nome de Chave Primária.
Em algumas relações, temos um atributo (ou conjunto de atributos) cujas ocorrências
são referências a uma chave primária de uma outra relação. A esses atributos damos o nome de
Chaves Estrangeiras. A existência de uma chave estrangeira prende-se com a necessidade de
manter a interligação entre essa relação e a relação onde esse conjunto de atributos é chave
principal.
Instâncias Exemplo
Paciente
B.I.
Nome
Morada
Telefone
11249968 Filipe Vila Real
Rua Santos Pousada 1155 4ºandar 225373022
10870649 Nuno Felicio
Rua Bernardim Ribeiro 203 2ET
Obs.
229534360
10256988 José Augusto Silva Rua Almeida Garrett 75
225368978
diabético
11365874 Alberto Correia
229654231
asmático
Rua de Camões 78 1º esq
10265998 Mário Silva Martins Av. dos Combatentes 23
1025698 Maria Clara Campos Rua Marco Moita bl.3 apart.5
225036948
222084657
Exame
B.I.
Data
Tipo
Local
11249968
12-Nov-98
Radiografia ao braço
11249968
5-Mar-99
Análises ao sangue
10256988
25-Sep-96
1025698
4-Apr-95
Ressonância magnética
Laboratório das Antas
10265998
13-Oct-97
Análises à urina
Clínica Bom Jesus
1025698
18-Nov-00
Mamografia
Hosp. S.João
Clínica Bom Jesus
Ecografia
Consulta
B.I.
Data
Hora
11249968
5-Nov-98
17:30
10870649
5-Mar-99
16:00
10256988
25-Sep-96
15:30
11365874
4-Apr-95
16:00
10265998
13-Oct-97
15:00
1025698
2-Nov-00
14:30
11249968
30-Mar-98
17:00
1025698
30-Nov-00
14:30
Tratamento
Trat_ID
Descrição
4
1 emb. paramolan
5
Diagnóstico Duração (dias)
gripe
2 emb. Clinex, 1 emb. Romanon hepatite H
10
20
Ínicio
Evolução
B.I.
15-Nov-00
boa
10870649
6-Jan-95 má reacção ao romanon 1025698
6
3 emb. Roacotan
acne facial
30
25-Feb-00
7
1 frasco de xonax,2 emb. Valium
stress
5
6-Jul-97
boa
11249968
má reacção ao xonax 11365874
Contra_Indic
Perguntas
1- Listar os Tratamentos para cada Paciente
2- Duração média do Tratamento para cada Doença
3- Quantos pacientes tiveram consulta e não se sujeitaram a tratamento.
4- Dias em que aconteceram Consultas, Exames e Tratamentos
5- Nome(s) do Paciente(s) cujo telefone começa por ‘225...’ cuja data de
consulta é anterior a 10-Jan-2000 e a duração de tratamento foi superior a 10 dias
Descrição em Algebra Relacional
1)
Π Descrição,Diagonóstico [σ BI
2)
Π Diagnóstico, Média = AVG(Duração) (Tratamentos)
3)
Π
4)
Π Data (Consulta) ∩ Π Data (Exame) ∩ Π Data (Tratamento)
5)
Π Nome [σ Telefone = ‘225*’ (Paciente) ∞ σ Data < ’10-JAN-2000’ (Consulta) ∞
= ‘10870649’(Tratamento)]
Nº = CNT (BI) (Consulta)
- Π Nº = CNT
(BI) (Tratamento)
σ Duração > ‘10’ (Tratamento) ]
Descrição em SQL
1)
Select Descrição, Diagonóstico
From Tratamento
Where BI =’10870649’;
2)
Select distinct Diagonóstico, avg(Duração)
From Tratamento
Group by Diagonóstico;
3)
Select count(BI)
from Consulta
Where BI not in
(Select distinct BI
from Tratamento)
Group by BI
4)
(Select Data
From Consulta)
Intersect
(Select Data
From Exame)
Intersect
(Select Data
From Tratamento);
5)
Select Nome
From Paciente, Tratamento, Consulta
Where Telefone like ‘225*’ and Data < ’10-JAN-2000’ and Duração > 10;
Estudo da Normalização
Após a construção do modelo conceptual dos dados (Modelo entidade-associação) é
feita a transformação para um modelo lógico (Esquema Relacional).
O conjunto de tabelas obtido representa a estrutura da informação de um modo natural e
completo.
A normalização tem como objectivo modificar o conjunto de tabelas obtido por
transformação do modelo conceptual, num outro conjunto de tabelas equivalente menos
redundante e mais estável.
Porém a redundância entre os dados não pode ser completamente eliminada. De facto,
as chaves estrangeiras são também uma forma de redundância que decorrem, directamente, da
forma própria de associar os dados no modelo relacional.
Comecemos então por analisar o processo de normalização relativamente à relação
Paciente(BI,nome,morada,telefone,obs). Esta estrutura encontra-se já na 1FN, uma vez que não
se encontram grupos de valores repetidos, cada cliente novo não apresenta valores de vários
atributos que já existentes. Excepção seria se tivéssemos graus de parentesco entre clientes e
coincidissem as respectivas moradas e números de telefone. Porém, mesmo neste caso a
redundância não seria significativa. Esta relação está na 2FN uma vez que a chave primária não
é composta, ou seja todos os atributos dependem na totalidade da chave. Está na 3FN porque
cada atributo depende apenas da chave primária da relação, não há dependências funcionais
entre os atributos não-chave.
A relação Exame(BI,tipo,data,local) está na 3FN uma vez que está na 2FN e na 1FN e
dado que só existe um atributo não-chave. Nesta relação o atributo “local” é optativo, sendo que
também não há a obrigatoriedade que um respectivo tipo de exame seja feito sempre no mesmo
local, o que leva a que não haja valores de vários atributos repetidos.
A relação Consulta(BI,data,hora) está na 1FN uma vez que não existem grupos de
valores repetidos, só o atributo hora é que não é chave. Está na 2FN uma vez que o atributo
“hora” depende da totalidade da chave (BI,data→hora). A 3FN é garantida uma vez que só
existe um atributo não-chave, logo não existirem dependências funcionais entre atributos nãochave.
A relação Tratamento(Trat_ID,BI,diagnostico,descrição,...) foi tratada de tal forma que
se pode considerar na 3FN. A nossa ideia é que cada tratamento é diferente uma vez que se
adapta a cada paciente em particular. Podemos então ter iguais diagnósticos para diferentes
pacientes mas que não tenham necessariamente as mesmas descrições, a mesma duração e a
mesma evolução. Poderá haver casos em que haja redundância na “descrição, diagnóstico” mas
pensamos ser num nível aceitável, uma vez que não nos interessa manter uma entidade Doença
na qual poderiam ser armazenados todas as descrições para um respectivo diagnóstico. Na nossa
forma de ver o que se justifica é manter uma “descrição” que seja baseada em cada caso
específico.
Por definição, uma relação está na BCNF se todos os atributos são funcionalmente
dependentes da chave, de toda a chave e nada mais que a chave.
A 3FN é aquela em que, na maioria dos casos, termina o processo de normalização,
contudo em alguns casos muito específicos, a 3FN ainda transporta alguns problemas.
Em casos muito raros, surge ainda a necessidade de verificar a 4FN. Relativamente a
esta diz-se de uma relação que está na BCFN a não existem dependências multivalor.
Nas relações em causa não consideramos que houvesse necessidade em prosseguir com
o processo de normalização.
A normalização remete-nos assim para dois objectivos, em geral conflituosos: por um
lado pretendem-se sistemas flexíveis, sem problemas de redundância; por outro lado exigem-se
sistemas com alto desempenho. Torna-se portanto necessário chegar a um compromisso, o ideal
seria um esquema equilibrado que nunca ponha em risco a integridade da base de dados mas
que, ao mesmo tempo, tenha um desempenho aceitável pois só assim será utilizado.