Exame 1/2/2002

LICENCIATURA EM ENGENHARIA ELECTROTÉCNICA E DE COMPUTADORES
Sistemas de Informação
1 de Fevereiro de 2002
Duração máxima 2 horas, com consulta
Problema 1: Modelação de dados (6 valores)
Suponha que se pretende informatizar os serviços prestados pelas Unidades de Socorro (US) da Cruz
Vermelha Portuguesa (CVP), de acordo com os seguintes pressupostos:

A Cruz Vermelha Portuguesa tem várias Unidades de Socorro Distritais (USD) e concelhias
(USC). As USD coordenam as actividades das USC do respectivo distrito. Cada US tem um
código (único) e um nome. Interessa saber a USD a que cada USC está subordinada.

As USC dispõem de colaboradores e viaturas. Relativamente a cada colaborador interessa
saber o seu número de colaborador (único em toda a CVP), o nome e o posto. Relativamente a
cada viatura interessa saber a matrícula e uma descrição.

As USC realizam serviços. Cada serviço tem um código (único), uma descrição, um tipo, uma
data de início e uma data de fim. Existem três tipos de serviços: 1- Normal, 2 – Emergência, 3
– Instrução.

Num serviço participam vários colaboradores. Cada colaborador assume uma ou mais funções
no serviço. As funções são: R - responsável, C - condutor, S - socorrista, E - enfermeiro, M médico. Em diferentes serviços, um mesmo colaborador pode assumir diferentes funções.

A realização de um serviço pode envolver várias saídas. Cada saída tem um código (único),
uma hora de partida, uma hora de chegada, uma referência à viatura utilizada e o n.º de
quilómetros percorridos.
1.1 (4.0)
Obtenha um diagrama Entidade-Associação relativo à base de dados para este sistema,
empregando a notação das aulas. Indique também restrições de integridade adicionais.
1.2 (2.0)
Converta o diagrama obtido em 1.1 para um esquema relacional. Utilize uma notação abreviada
da forma R1(A1,A2,A3,,...,An), em que R1 é o nome de uma relação, A1, ...,An são nomes de
atributos, e os atributos sublinhados constituem a chave primária. Indique à parte as chaves
estrangeiras.
Problema 2: Dependências funcionais e normalização (2 valores)
Considere uma relação
JogadorJogo(Jogador, Equipa, Adversário, Data)
com as seguintes dependências funcionais (utiliza-se apenas a primeira letra de cada atributo):
JE
(cada jogador joga sempre na mesma equipa)
EAD
(cada par de equipas só joga uma vez, numa determinada data)
EDA
(numa dada data, cada equipa só pode jogar contra uma equipa adversária)
ADE
(idem, de forma recíproca)
2.1 (1.0)
Determine a(s) chave(s) candidata(s) de R.
2.2 (1.0)
Determine se R obedece à forma normal de Boyce-Codd (BCNF) e, em caso negativo,
decomponha R em duas ou mais relações na BCNF.
Problema 3: Interrogação de dados em SQL e Álgebra Relacional (5 valores)
Considere uma base de dados com as seguintes tabelas:
Factura(numero, cliente, data, valor)
LinhaFactura(numFactura [ Factura], numLinha, artigo, quantidade, preço_unitário)
Para além das chaves primárias indicadas (a sublinhado) e das chaves estrangeiras indicadas
(com seta), suponha que se verificam as seguintes restrições de integridade:
R1: O valor da factura é o somatório dos valores das linhas (o valor de cada linha é o
produto da quantidade pelo preço unitário)
R2: O mesmo artigo não pode aparecer em duas linhas da mesma factura
3.1 (1.25) Escreva um comando em SQL para listar todas os artigos vendidos ao cliente 'José Sousa'.
3.2 (1.25) Escreva uma expressão em álgebra relacional com o mesmo objectivo da questão anterior.
3.3 (1.25) Escreva um comando em SQL para obter uma estatística do valor e quantidade total facturada
por artigo, excluindo os artigos com valor facturado abaixo de 1000 (euros).
3.4 (1.25) Escreva um comando em SQL para obter o cliente (ou clientes) com maior valor facturado.
Problema 4: Definição e manipulação de dados em SQL e PL/SQL (4 valores)
Considere de novo a base de dados do Problema 3.
4.1 (2.5)
Escreva comandos em SQL para criar as tabelas indicadas, com as chaves primárias e
estrangeiras indicadas, impondo também a restrição R2. O valor da factura deve ter o valor 0
por omissão.
4.2 (1.5)
Escreva um ou mais gatilhos em PL/SQL para impor a restrição R1. Supondo que o valor da
factura é 0 quando se insere um registo na tabela Factura e nunca é actualizado directamente,
basta reagir a eventos de inserção, eliminação ou modificação de linhas de facturas.
Problema 5: Concorrência, Recuperação e Segurança (3 valores)
5.1 (1.5)
Considere as seguintes transacções:
T1(in: K)
T2(out: S)
READ(A)
A:=A-K
READ(B)
B:=B+K
WRITE(A)
WRITE(B)
READ(A)
READ(B)
S:=A+B
Entre parênteses são indicados parâmetros de entrada e saída das transacções. Apresente um
escalonamento não serializável destas transacções, justificando porque é que não é serializável.
5.2 (1.5)
(Fim.)
Considere de novo a base de dados do problema 3. Escreva comandos em SQL para criar um
perfil de utilizador denominado "EMPREGADO" com permissão para consultar, inserir,
eliminar e actualizar facturas e linhas de facturas, impedindo, se possível, a manipulação directa
(inserção e actualização) do valor da factura.
Resolução (parcial)
2.1
R(J,E,A,D)
F={JE, EAD, EDA, ADE}
J não é determinado por nenhum atributo, logo tem de fazer parte de qualquer chave
J+ = JE , logo J não é chave (e JE também não)
(JA)+ = (JAE)+ =JAED , logo JA é chave
(JD)+ = (JAE)+ =JAED , logo JD é chave
R: K={JA, JD} (conjunto de chaves candidatas)
2.2
i) R(J,E,A,D)
F={JE, EAD, EDA, ADE} K={JA, JD}
O lado esquerdo de qualquer DF não contém uma chave, logo R não está na BCNF.
ii) Usando a DF JE decompõe-se R em:
R1(J,E), com F1={JE}
K1={J}, logo está na BCNF
R2(J,A,D), com F2={JAD, JDA }
K2={JA,JD}, logo está na BCNF
Nota: Em alternativa, podia-se pegar na DF EAD (ou outra), decompondo-se então R em:
R1(E,A,D), com F1={EAD, EDA, ADE }
R2(J,E,A), com F2={JE }
K1={EA,ED,AD}, logo está na BCNF
K2={JA }, logo NÃO está na BCNF
Decompondo R2:
R3(J,E), com F3={JE } K3={J}, logo está na BCNF
R4(E,A), com F4={ }
K3={EA}, logo está na BCNF
3.1
select distinct artigo
from LinhaFactura l, Factura f
where numFactura = numero and cliente = 'José Sousa'
3.3
select artigo, sum(quantidade * preço_unitário), sum(quantidade)
from LinhaFactura
group by artigo
having sum(quantidade * preço_unitário) >= 1000
3.4
select cliente
from Factura
group by cliente
having sum(valor) = (select max(sum(valor)) from Factura group by cliente)
5.1
Para não ser serializável basta que T2 use o valor antigo de A (antes de ser actualizado por T1) e o novo valor de
B (depois de ser actualizado por T1) ou vice-versa, porque o resultado final (valor de S) não é o mesmo que se
obteria executando T2 totalmente antes de T1 ou totalmente depois de T1.
Exemplos de escalonamentos não serializáveis:
T1(in: K)
T2(out: S)
READ(A)
A:=A-K
READ(B)
B:=B+K
READ(A)
WRITE(A)
WRITE(B)
READ(B)
S:=A+B
T1(in: K)
T2(out: S)
READ(A)
A:=A-K
READ(B)
B:=B+K
WRITE(A)
READ(A)
READ(B)
S:=A+B
WRITE(B)