Sistemas de Bases de Dados Relacionais Casos para Estudo

Sistemas de Bases de Dados Relacionais
Casos para Estudo
Facturas
Considere o seguinte exemplo de um modelo de uma factura comercial:
Factura Nr:
Data
:
Cliente
:
Designação:
Morada
:
Agente
Linha
1
2
3
1
96/Abr/01
C0001
Nome do Cliente C0001
Rua do Cliente C0001,
Localidade do Cliente C0001
Código Postal do Cliente C0001
: Agente da empresa que fez a venda
Artigo
A001
B002
C005
Descrição
Artigo A001
Artigo B002
Artigo C005
Preço
100
200
500
Qt
10
20
10
Valor
1000
4000
5000
Total s/ IVA : 10000
Valor IVA 17%: 1700
Total c/ IVA : 11700
Com base no caso de estudo enunciado pretende-se que:
1. Apresente a análise de requisitos complementar que ache necessária e suficiente de
forma a justificar e suportar o desenvolvimento do esquema conceptual requerido na
alínea 3.
2. Com base na análise realizada na alínea anterior, desenhe um diagrama E/R que
permita dar uma "primeira visão" das entidades e relacionamentos que a futura base de
dados do caso em estudo possa envolver.
3. Desenvolva o modelo para um esquema conceptual de uma base de dados relacional,
normalizado até à terceira forma normal (3FN), que permita acolher a informação do
caso de estudo apresentado.
*****
2003, O.Belo, DI, UM
Sistemas de Bases de Dados Relacionais
Casos para Estudo
Gestão de Recursos Humanos
O gestor de recursos humanos de uma empresa mantém numa pequena base de dados toda a
informação relativa aos funcionários da empresa e suas actividades diárias. Para cada
funcionário ele costuma armazenar o seu número, nome, função, data de nascimento,
habilitações literárias e um lista dos cursos frequentados pelo funcionário pagos pela empresa.
Adicionalmente, o gestor organiza a informação de cada funcionário segundo a função que
ocupa: responsável de serviço, operador ou secretário, dado necessitar de armazenar
diferentes itens de informação para cada um deles. Basicamente, para um responsável de
serviço ele armazena a relação dos funcionários que estão sobre sua supervisão, para um
operador a relação dos equipamentos com que costuma trabalhar e para um secretário uma
relação das línguas que é capaz de falar.
Com base no texto acima apresentado pretende-se que desenvolva um diagrama ER que
traduza a situação que o texto apresenta e que integre os requisitos nele enunciados.
*****
2003, O.Belo, DI, UM
Sistemas de Bases de Dados Relacionais
Casos para Estudo
Identificação de Pacientes num Hospital
Num hospital, os pacientes ao serem admitidos é-lhes atribuído um número de identificação
único que permitirá a qualquer um dos seus funcionários (médicos, enfermeiros, vigilantes,
recepcionistas, etc.)
identificar o paciente sem qualquer ambiguidade. A atribuição desse
número é o primeiro passo para a criação da ficha do paciente no hospital. Nessa ficha
constará também a informação do paciente relativa ao seu nome, endereço, data de
nascimento, sexo e data de admissão. Durante o processo de admissão são associados, a
cada paciente, um vigilante e um médico. Após ter terminado o processo de admissão, passarse-á a uma fase de observação e análise com o objectivo de fazer um diagnóstico ao paciente.
Nessa fase poderão ser registadas na ficha do paciente uma lista de observações e condições.
Enquanto estiver no hospital o paciente poderá ser sujeito a vários tratamentos. Cada
tratamento terá associado uma data e os detalhes relacionados com de administração do
tratamento, os medicamentos que foram utilizados e respectivas dosagens. Todos os
medicamentos
possuem
identificadores
únicos,
uma
designação
e
uma
dosagem
recomendada, que poderá não ser a mesma a utilizar nos tratamentos. Todos os funcionários,
independentemente da sua categoria, possuem um identificador único e um nome. Os
enfermeiros estão sempre associados com um vigilante.
Com base no caso de estudo apresentado, pretende-se que:
1. Identifique as diferentes dependências funcionais no conjunto de dados que o texto
considera.
2. Com base no conjunto de dependências identificado na alínea anterior, apresente um
esquema lógico para uma base de dados relacional, na terceira forma normal, que
permita acolher a informação do caso apresentado.
3. Que tipo de modificações necessitaria de realizar se: i) os medicamentos fossem
conhecidos por mais do que uma designação; e ii) os pacientes pudessem estar
associados com mais do que um médico. Apresente o novo esquema obtido.
*****
2003, O.Belo, DI, UM
Sistemas de Bases de Dados Relacionais
Casos para Estudo
Colecção de Obras Musicais
O Sr. SoliDó é um grande apreciador de música. Ao longo dos anos ele tem recolhido uma
enorme e muito diversificada quantidade de obras musicais. Na sua colecção, podemos
encontrar Jazz, Rock Sinfónico, Clássica, Rap, Funky ou Hard-Rock. Enfim, um pouco de tudo.
É impressionante a variedade de géneros musicais que integram a sua colecção privada. Ao
fim destes anos, o Sr. SoliDó começa a ter alguma dificuldade em gerir a sua colecção. As
coisas complicaram-se um pouco mais quando adquiriu uma grande colecção de vídeos
musicais (VHS e DVD) a um seu amigo. Já não sabe precisamente quais os títulos que hoje
possui. Isso incomoda-o. Assim, decidiu pedir a um dos seus sobrinhos – um curioso por
tecnologias de informação – que lhe desenhasse e implementasses uma base de dados que
lhe permitisse, mais tarde, gerir com facilidade a sua colecção. O seu sobrinho, de imediato,
decidiu ajudá-lo. Disse-lhe, logo de seguida, que precisava de alguma informação acerca da
sua colecção actual, de forma a poder realizar correctamente a tarefa que o tio lhe solicitava. O
tio prontamente acedeu e, numa conversa que mais tarde tiveram, comunicou-lhe os seguintes
dados:
-
A colecção actual tem cerca de 15000 títulos, sendo um terço constituído por vídeos
VHS e DVD.
-
Os títulos estão organizados por género musical e, dentro deste, pelo seu primeiro
autor. Esta ordenação define o lugar do título no sistema de arquivo da colecção que
possui na sua biblioteca.
-
Cada título está acompanhado por uma ficha (técnica) que possui vários itens de
informação. De referir: código, título, preço de aquisição, data de publicação, editora,
autores, tipo de suporte (CD, VHS ou DVD), género musical, nomes das músicas, seus
autores letras e duração, e tipo (original, compilação, etc.). Nos últimos dois anos esta
ficha tem sido complementada com algumas anotações contendo críticas recolhidas na
imprensa especializada.
Com base no texto apresentado, pretende-se que:
1. Identifique as diferentes dependências funcionais no conjunto de dados que o texto
considera.
2. Desenhe um Diagrama ER capaz de caracterizar a informação disponibilizada pelo
caso de estudo para o desenvolvimento da base de dados solicitada.
3. Apresente um esquema lógico para uma base de dados relacional, na terceira forma
normal, que permita acolher a informação do caso apresentado.
2003, O.Belo, DI, UM
Sistemas de Bases de Dados Relacionais
Casos para Estudo
Manutenção de Equipamento
Pretende-se que defina e normalize o esquema de uma base de dados relacional relativo à
informação envolvida com a gestão de serviços de reparação de avarias de equipamentos de
uma empresa.
As folhas de serviço de reparação da empresa são constituídas pelos items de informação
apresentados na tabela seguinte:
Nr
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
Designação
Código da reparação
Data da reparação
Código do cliente
Designação do cliente
Rua
Localidade
Código postal
Telefone
Contribuinte
Código da zona do cliente
Designação da zona
Equipamento a reparar
Código do artigo
Designação do artigo
Unidade do Artigo
Quantidade
Preço unitário do artigo
Valor do artigo
Número de artigos
Nr
20
21
22
23
24
25
26
27
28
29
30
Designação
Código da operação
Designação da operação
Unidade
Quantidade
Preço unitário da operação
Valor da operação
Valor total dos artigos
Valor total das operações
Valor total do serviço
IVA
Valor final do serviço
De referir, que uma folha de serviço poderá ter um número não determinado à partida de
referências de artigos e de operações associadas. A uma folha de serviço só poderá estar
associado um cliente e um equipamento.
De acordo com o esquema obtido para o caso de estudo, defina um conjunto de expressões
em Álgebra Relacional e SQL que permitam dar resposta às seguintes questões:
1. Em que datas é que o cliente 'X' levou à empresa equipamento para reparar;
2. Quais foram as reparações cujos valores dos artigos ultrapassaram os 50.000$00;
3. Quais foram os clientes que requereram a realização da operação 'X' num
equipamento 'Y'; e
4. Quais os clientes da zona 'Z' que repararam no dia 'N' algum tipo de equipamento.
2003, O.Belo, DI, UM
Sistemas de Bases de Dados Relacionais
Casos para Estudo
Gestão de um Restaurante
Durante os últimos dois anos, a gerência de um restaurante verificou que o nível dos serviços
prestados estava a decrescer em termos de qualidade: as ementas eram mal preparadas e
apresentadas, os pedidos dos clientes demoravam a ser processados, as contas demoravam a
ser calculadas e em algumas situações apresentavam erros, etc. Tais situações levaram a
gerência a tomar medidas fortes ao nível da gestão e do tratamento dos processos do
restaurante. Assim, com base num processo de análise executado por uma empresa de
consultoria, decidiram informatizar os seguintes serviços:
-
Elaboração e impressão das ementas diárias – todos os dias são elaboradas ementas
diferentes de acordo com produtos frescos encomendados no mercado local; uma ementa
é constituída por várias secções: entradas, sopas, pratos de carne, pratos de peixe,
sobremesas e bebidas; cada uma das secções das ementas pode ser constituída por uma
ou mais referências de pratos ou outros tipos de artigos, que incluem além do seu código
de referência, o nome do prato/artigo e o seu preço em escudos e em euros.
-
Pedidos dos clientes – cada pedido do cliente considera: o número da mesa onde está
sentado, a lista dos pratos/artigos que deseja consumir, a identificação do funcionário que
recebeu o pedido e um conjunto de observações.
-
Confecção dos pratos das ementas – dado as ementas serem diferentes todos os dias e
para que se possa fazer um planeamento adequado, a cozinha para satisfazer os pratos
das ementas precisa de elaborar previamente uma lista de necessidades pormenorizada
com os produtos necessários à confecção da ementa do dia seguinte; com base na
definição das ementas essa lista poderá ser gerada automaticamente.
-
Gestão e pagamentos das contas – com base nos pedidos efectuados pelos clientes as
contas são automaticamente calculadas o que permite elaborar o talão de cobrança; com o
pagamento do cliente é emitido o respectivo recibo com a discriminação de todos os artigos
e serviços pagos.
Através da criação de uma base de dados para suporte à informação necessária aos
processos acima descritos, a gerência do restaurante também espera conseguir obter
rapidamente elementos que lhe permita responder às seguintes questões:
1.
2.
3.
4.
qual é a ementa de hoje e quais os pratos que nele figuram;
quais os produtos que são necessários para cumprir a ementa de amanhã;
quais foram os pratos de carne servidos durante o mês de Agosto;
em que dias é que foi servido o prato de peixe “P” juntamente com o prato de carne
“C”.
Com base no caso de estudo apresentado, pretende-se que:
a) Apresente a análise de requisitos que ache necessária e suficiente de forma a justificar e
suportar o desenvolvimento do esquema conceptual requerido na alínea c.
b) Com base na análise realizada na alínea anterior, desenhe um diagrama E/R que permita
dar uma "primeira visão" das entidades e relacionamentos que a futura base de dados do
caso em estudo possa envolver.
c) Desenvolva o modelo para um esquema conceptual de uma base de dados relacional
normalizado que permita acolher a informação do caso de estudo apresentado.
Complementarmente, apresente as diversas dependências funcionais estabelecidas nas
2003, O.Belo, DI, UM
relações e uma caracterização (nome, tipo e tamanho) para os atributos das relações que
apresentou, indicando de que tipo são as chaves que definiu.
d) Apresente um conjunto de expressões em Álgebra Relacional que permitam responder às
questões acima apresentadas.
e) Apresente um conjunto de expressões em SQL que permitam responder ao mesmo grupo
de questões da alínea anterior.
*****
2003, O.Belo, DI, UM
Sistemas de Bases de Dados Relacionais
Casos para Estudo
Distribuição de Água
Uma empresa de fornecimento de água possui quatro instalações de reservatórios localizadas em
diferentes regiões. É com base nesses reservatórios que a empresa fornece água à população de 10
das maiores vilas da região onde os reservatórios estão situados. Por forma a poder controlar
correctamente e garantir - 24 horas por dia, 365 dias por ano - o fornecimento da água e serviços
relacionados, assim como a qualidade da água, a empresa mantém permanentemente nas instalações
de cada um dos reservatórios uma equipa de pessoal técnico. Cada uma destas equipas pode integrar
até 8 pessoas, normalmente constituídas por 2 analistas de qualidade da água, 2 controladores de
tanques, 3 técnicos de manutenção e um gestor local.
Os serviços de fornecimento de água são controlados, em condições normais de funcionamento, de
meia em meia hora, através da medição do volume de água contidos nos reservatórios efectuadas por
aparelhos especializados. Posteriormente, após verificação, os resultados dessas medições são
armazenados numa base de dados (BDR) que cada uma das instalações mantém localmente. Os
registos de medição de volume de água nos tanques integram informação relacionada com: a data e a
hora da medição, o técnico que verificou a medição e, naturalmente, o volume de água medido. Para
cada um dos tanques está definido um volume de água mínimo que, quando detectado por um dos
sensores responsáveis por esse serviço, coloca em funcionamento as bombas de abastecimento dos
tanques. Independentemente destas medições, requeridas pelos gestores da empresa, o volume de
água de cada um dos tanques é permanentemente controlado, permitindo aos funcionários saber a
cada momento qual o volume de água dos tanques. Todos os eventos relacionados com estes serviços
de controlo são automaticamente registados numa logfile específica na base de dados anteriormente
referida.
Actualmente, e por imperativos operacionais e de gestão da rede informática da empresa, os registos
de medição, efectuados sobre os tanques de água de cada uma das quatro instalações, apenas são
comunicados aos serviços da base de dados central da empresa (BDCE) de quatro em quatro horas.
Após a sua boa recepção, os registos são integrados em tabelas específicas que reúnem todas as
medições efectuadas nos tanques de água das instalações de reservatórios da empresa. A BDCE
armazena também toda a informação contida nas BDR, relativa às instalações, tanques, funcionários e
informação relacionada. Adicionalmente, armazena os dados relativos aos consumos mensais dos
clientes de cada uma das vilas às quais a empresa fornece água. Os registos dos consumos são
introduzidos sempre no dia 28 de cada mês por cada um dos funcionários responsáveis pelos serviços
de cobrança. É a partir destes dados que a empresa emite os talões de cobrança de água a cada um
dos seus clientes, enviando-os por correio no dia 1 de cada mês.
Com base no caso de estudo apresentado, pretende-se que:
1. Apresente a análise de requisitos que ache necessária por forma a complementar e suportar o
desenvolvimento dos esquema conceptuais requeridos na alínea 3.
2. Tomando em consideração a análise realizada na alínea anterior, desenhe um diagrama ER
que permita dar uma “visão” das entidades e relacionamentos de cada uma das bases de
dados referidas no caso em estudo.
3. Desenvolva um modelo de um esquema conceptual para cada uma das bases de dados
referidas no caso em estudo. Os esquemas relacionais deverão estar normalizados até à
terceira forma normal. Complementarmente, apresente as diversas dependências funcionais
estabelecidas nas relações de cada um dos esquemas, assim como uma caracterização
(nome, tipo e tamanho) para os diversos atributos que constituem as relações que apresentou.
4. Apresente um conjunto de expressões em SQL que permitam responder às seguintes questões
sobre a base de dados central da empresa:
a) Quais são os tanques das instalações de reservatórios 'A' e 'B' com capacidades
superiores a 10.000.000 litros?
2003, O.Belo, DI, UM
b) Quais são os elementos das equipas de técnicas das instalações de reservatórios que são
'Controladores de Tanques'?
c) Quais são os nomes e as funções dos funcionários que efectuaram o controlo das
medições dos tanques nas instalações de reservatórios de códigos 'A' e 'B' no dia '2000-0204'?
d) Qual o valor médio do volume de água de cada um dos tanques da instalação de
reservatórios 'A'?
5. Defina um serviço de transformação de dados que permita executar o processo de migração e
integração dos registos de medições dos reservatórios na base de dados central da empresa.
Apresente
as
expressões
SQL
necessárias
para
realizar
as
tarefas
de
transformação/integração de dados em cada um dos processos que definiu. Desenhe um
diagrama representativo do serviço de transformação de dados com a representação de cada
um dos seus processos e respectiva sequência de execução.
*****
2003, O.Belo, DI, UM
Sistemas de Bases de Dados Relacionais
Casos para Estudo
Comunicações Telefónicas
Considere o seguinte modelo de uma factura de comunicações telefónicas e de dados de um
cliente de uma empresa de telecomunicações:
Factura nº: FA-2001-7865423
Data de Emissão: 10/Setembro/2001
Cliente Nº: RN-6754653NP
Contribuinte Nº: 6543162546
Exmo. Senhor
Joãozinho da Muralha do Castelo de Cima
Rua da Costa do Castelo, 99, 8ºesq
Casa Forte da Ribeira
9999-999 Forças Medievais
Encargos Fixos Mensais
Linha telefónica: 1000$00
Equipamento: 1500$00
Comunicações
(Data/hora/telefone/custo)
Locais
01-08-2001/10:00/999999999/50$00
03-08-2001/12:00/888888888/50$00
08-08-2001/20:00/999999999/50$00
21-08-2001/13:00/777777777/60$00
Nacionais
12-08-2001/23:00/557777777/400$00
15-08-2001/09:00/556666666/100$00
Internacionais
(nenhum registo)
Móveis
28-08-2001/13:00/337777777/800$00
Resumo da Factura
Encargos Fixos: 2500$00
Comunicações: 1510$00
Total: 4010$00
Total(€): 20
Com base no caso de estudo enunciado pretende-se que:
1. Apresente a análise de requisitos complementar que ache necessária e suficiente de
forma a justificar e suportar o desenvolvimento do esquema conceptual requerido na
alínea 3.
2. Com base na análise realizada na alínea anterior, desenhe um diagrama E/R que
permita dar uma "primeira visão" das entidades e relacionamentos que a futura base de
dados do caso em estudo possa envolver.
2003, O.Belo, DI, UM
3. Desenvolva o modelo para um esquema conceptual de uma base de dados relacional,
normalizado até à terceira forma normal (3FN), que permita acolher a informação do
caso de estudo apresentado.
4. Apresente em Álgebra Relacional (com as respectivas árvores) e em SQL as
instruções necessárias para satisfazer as seguintes interrogações:
i. Quais foram as chamadas locais realizadas pelo cliente C entre os dias 01/07/2001
e 01/08/2001?
ii. Quanto é que o cliente C pagou de chamadas nacionais durante o mês de Julho de
2001?
*****
2003, O.Belo, DI, UM