Exame-tipo

Sistemas de Informação
FACULDADE DE ENGENHARIA DA UNIVERSIDADE DO PORTO
Licenciatura em Engenharia Electrotécnica e de Computadores
Sistemas de Informação
Exame, 28 de Dezembro de 2001
DURAÇÃO MÁXIMA 2 horas e 30 minutos, com consulta
Problema 1: Modelação de dados (6 valores)
Suponha que se pretende projectar uma base de dados para um Sistema de Gestão de Projectos numa
Organização, de acordo com os seguintes pressupostos:
 Um projecto é caracterizado por: número (identifica o projecto), designação, início e fim.
 Um projecto é decomposto em tarefas. Cada tarefa é caracterizada por: número (sequencial e único
dentro do projecto), designação, início e fim.
 A cada tarefa são afectados recursos. Os recursos podem ser de dois tipos: colaboradores ou
equipamentos. Na afectação de um recurso a uma tarefa, indica-se a percentagem do recurso que
está afecta à tarefa. O mesmo recurso pode ser afectado a várias tarefas ao mesmo tempo, desde
que a soma das percentagens de afectação não exceda 100%.
 Os colaboradores são caracterizados por: número (identifica o colaborador), nome e salário.
 Os equipamentos são caracterizados por: número (identifica o equipamento), designação e custo.
 Cada projecto tem um chefe, que é um colaborador da Organização. O mesmo colaborador pode
ser chefe de mais do que um projecto.
1.1 (4.0) Obtenha um diagrama Entidade-Associação relativo a esta base de dados, empregando a notação das
aulas. Indique também chaves e restrições adicionais.
1.2 (2.0) Converta o diagrama obtido em 1.1 para um esquema relacional, seguindo a metodologia exposta nas
aulas. Justifique devidamente as opções tomadas. Utilize uma notação abreviada da forma
R1(A1,A2,...,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 alternativas (chaves
candidatas que não são chaves primárias) e as chaves estrangeiras.
Problema 2: Dependências funcionais e normalização (2 valores)
Considere uma relação Aluguer(codCassete, títuloCassete, numSócio, dataAluguer) na qual se
verificam as seguintes dependências funcionais CDN e CT. Utilizam-se as abreviaturas: C codCassete, T - títuloCassete, N - numSócio, D - dataAluguer.
2.1 (0.5) Determine a(s) chave(s) candidata(s) de R.
2.2 (1.5) Determine se R obedece à forma normal de Boyce-Codd (BCNF) e, no 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:
Departamento(sigla, nome, siglaChefe[Empregado])
Empregado(sigla, nome, dataNascimento, siglaDept [Departamento], dataEntrada, salario)
Para além das chaves primárias indicadas (a sublinhado) e das chaves estrangeiras indicadas (com
setas), suponha que se verificam as seguintes restrições de integridade:
R1: A data de entrada de um empregado tem de ser maior do que a sua data de nascimento.
R2: O chefe de um departamento tem de trabalhar para esse departamento (o departamento para que
um empregado trabalho é indicado pela coluna "siglaDept" da tabela "Empregado").
R3: O salário do chefe de um departamento não pode ser inferior ao salário dos empregados que
trabalham nesse departamento.
3.1 (1.0) Escreva um comando em SQL para listar os nomes dos empregados do departamento com sigla
"DEEC", por ordem alfabética. Nota: para obter esta informação basta aceder à tabela "Empregado".
3.2 (1.0) Escreva um comando em SQL para obter o nome do empregado que chefia o departamento com sigla
"DEEC".
João Pascoal Faria/Ana Paiva
28/DEZ/2001, página 1 de 2
Sistemas de Informação
3.3 (1.0) Escreva uma expressão em álgebra relacional com o mesmo objectivo da questão anterior.
3.4 (1.0) Escreva um comando em SQL para listar o número de empregados e o salário médio por
departamento, com a sigla do departamento, o nome do departamento, o número de empregados e o
salário médio.
3.5 (1.0) Escreva um comando em SQL para listar as violações da restrição R2, com o nome do departamento,
o nome do seu chefe, e o nome do departamento para que esse chefe trabalha.
Problema 4: Definição e manipulação de dados em SQL e PL/SQL (5 valores)
Considere de novo a base de dados do Problema 3.
4.1 (2.0) Escreva comandos em SQL para criar as tabelas indicadas, com as chaves primárias e estrangeiras
indicadas, impondo também a restrição R1.
4.2 (2.0) Escreva gatilhos em PL/SQL para impor a restrição R3, aumentando o salário do chefe do
departamento sempre que necessário. Mais precisamente, os gatilhos devem assegurar o seguinte:
-
quando é inserido um departamento ou é actualizado o chefe de um departamento, é necessário
verificar se o salário máximo dos empregados desse departamento excede o salário do chefe do
departamento e, em caso afirmativo, o salário do chefe deve ser actualizado para igualar esse
máximo;
-
quando é inserido um empregado ou é actualizado o departamento ou o salário de um empregado,
é necessário verificar se o salário do empregado excede o salário do chefe do respectivo
departamento e, em caso afirmativo, o salário do chefe deve ser actualizado para igualar o salário
do empregado em causa.
4.3 (1.0) Escreva um procedimento denominado "MovDep" em PL/SQL para passar todos os empregados de
um departamento de origem para um departamento de destino e de seguida eliminar o departamento
de origem, numa única transacção. O procedimento deve receber como argumentos as siglas dos
departamentos de origem e de destino.
Problema 5: Transacções, Concorrência e Recuperação (2 valores)
Considere as seguintes transacções, onde READ(X) e WRITE(X) são as primitivas de leitura e escrita
de valor de um item de dados X.
T1
READ(A)
WRITE(A)
READ(B)
WRITE(B)
COMMIT
T2
READ(A)
READ(B)
COMMIT
T3
WRITE(B)
WRITE(A)
COMMIT
5.1 (1.0) Apresente um escalonamento serializável destas 3 transacções, em que as mesmas sejam executadas
concorrentemente (se possível). Qual é o escalonamento série que produz o mesmo estado final que o
escalonamento serializável apresentado?
5.2 (1.0) Acrescente a cada uma das transacções apresentadas comandos do tipo RLOCK(X), WLOCK(X) e
UNLOCK(X) (bloqueio partilhado para leitura, bloqueio exclusivo para escrita, e libertação de
bloqueio, respectivamente) de acordo com o protocolo de bloqueio em duas fases estrito.
(Fim.)
João Pascoal Faria/Ana Paiva
28/DEZ/2001, página 2 de 2