1º teste 2003/04 (resolução)

Nome:
Número:
INSTITUTO SUPERIOR TÉCNICO
DEPARTAMENTO DE ENGENHARIA INFORMÁTICA
Bases de Dados
1º Teste (LEIC Tagus)
Não se esqueçam de identificar todas as folhas!
10 de Novembro de 2002 – 8H30
Duração: 1H30
Grupo 1 - Modelos ER e Relacional (7 valores)
1. Considere o domínio do problema proposto na 1ª série de problemas e algumas
extensões. Pretende-se guardar informação sobre os alunos, docentes, licenciaturas,
departamentos e licenciaturas do IST.
Os alunos são identicados pelo número de aluno, tem um nome e uma data de
nascimento. Cada disciplina é identificada unívocamente por um código de disciplina e
tem associado um nome, o programa e o número de créditos (ex: Bases de Dados no
Tagus Park tem o código ‘IV’, o programa “XXXX” e 5 créditos). Um aluno inscreve-se
na ocorrência de uma disciplina para um ano lectivo e um semestre (ex: Bases de
Dados no 1º semestre de 2003/04). Podem existir várias inscrições para o mesmo
aluno, mas cada aluno não se pode inscrever na mesma disciplina no mesmo semestre
e ano lectivo (obviamente!).
Cada disciplina pertence a uma licenciatura e asume-se que cada licenciatura é da
responsabilidade de um departamento (ex: Bases de Dados é uma disciplina da LEIC
do DEI). Cada licenciatura tem um identificador e um nome e cada departamento tem
um identificador e um nome. A oferta de uma disciplina num determinado ano lectivo e
semestre é da responsabilidade de um docente que tem que ser professor. Uma
ocorrência de disciplina pode ter associados outros docentes (ex: professores,
assistentes, monitores) que asseguram as aulas práticas e/ou laboratório. Pretende-se
saber, para cada oferta de disciplina, qual o responsável e o resto do corpo docente
associado.
Uma licenciatura tem um coordenador de licenciatura que é um professor. Cada
departamento tem um presidente que é um professor catedrático. Qualquer docente é
identificado pelo seu número mecanográfico, tem um nome e pertence a um
departamento.
1
Nome:
Número:
O corpo docente de uma disciplina de uma licenciatura tem que pertencer ao mesmo
departamento a que pertence a licenciatura. Na mesma ordem de ideias, o presidente
de cada departamento tem que pertencer a esse departamento, bem como os
coordenadores das licenciaturas desse departamento.
Obtenha o diagrama ER para este problema. Tente modelizar o maior conhecimento
possível usando as primitivas do ER. Escreva em texto todo o conhecimento que não
conseguiu captar no modelo ER obtido.
2. Considere o seguinte esquema ER que modeliza informação sobre as equipas do
EURO2004. Assuma que cada equipa se pode identificar univocamente de duas
maneiras alternativas: i) pelo código da equipa dentro do grupo, e ii) pela sua
nacionalidade.
perte
nce
Grupo
Equipa
id
pertence
códigoEquipa
pontos
Nacionalidade
País
a) O esquema ER em cima representa todo o conhecimento que se pretende?
Justifique.
b) Obtenha o esquema relacional correspondente. Não se esqueça de especificar
quais as chaves primárias e estrangeiras. Indique, se existirem as restrições de
integridade que não estão expressas no esquema relacional obtido.
Grupo 1 – Resposta
1.
2
Nome:
Número:
semestre
N_aluno
nome
Data_nascimento
Aluno
ano
inscrição
Ocorrência
tem
Nome
tem
código
Docente
tem
El_Corpo_docente
nome
Disciplina
N_mec
responsá
vel
Nr_Seq
programa
ISA
pertence
créditos
id
Professor
Coorden.
Licenciatura
pertence
nome
ISA
respons
Catedrático
id
Presid.
Departamento
nome
Restrições de integridade:
RI1: O corpo docente de uma ocorrência de disciplina tem que pertencer ao mesmo
departamento que a disciplina.
RI2: O professor coordenador de uma licenciatura tem que pertencer ao mesmo departamento
que a licenciatura
RI3: O presidente de um departamento tem que pertencer ao mesmo departamento.
2.
a) Não representa o facto de cada equipa se identificar de duas maneiras
diferentes.
b)
Grupo(id)
Equipa (id, códigoEquipa, País, pontos) |
3
Nome:
Número:
id: FK(Grupo) País: FK(Nacionalidade)
Nacionalidade(País)
RI: Equipa tem 2 chaves candidatas: id, códigoEquipa e pais
1.
4
Nome:
Número:
Grupo 1 - Resposta
5
Nome:
Número:
Grupo 2 – Álgebra Relacional e SQL (6 valores)
Considere o seguinte esquema relacional:
Factura (nFact, nProd, data, comprador, vendedor)
Produto (nProd, nome, preço, categoria)
1. Escreva uma interrogação em álgebra relacional que retorne o conjunto de
electrodomésticos (produtos cuja categoria é “electrodomésticos”) que não foram
vendidos pelo José Santos. A resposta deve ser uma relação com uma única coluna
com os nomes dos produtos.
2. Escreva uma vista SQL que retorne todos os produtos que custam mais do que o
preço médio de um electrodoméstico. A vista tem uma única coluna.
3. Escreva uma interrogação SQL que retorne o número total de vendas por categoria,
mas só para as datas em que o número de vendas excedeu o número de vendas em
12/02/2003. Por exemplo, retorna o total de vendas de electrodomésticos para
1/03/2003 se e só se o número de elctrodomésticos vendidos nesse dia foi superior ao
número de electrodomésticos vendidos em 12/2/2003. A resposta é uma relação com
três colunas: categoria, data e número total de produtos vendidos.
4. Diga como pode garantir e escreva em SQL, que:
a) O preço de cada produto é um valor real não negativo.
b) As categorias possíveis para cada produto pertencem a um conjunto
enumerado. Exemplifique.
Grupo 2 - Resposta
1.
(


nome
(




( categoria=electrodomésticos Produto) - nProd, nome
Produto| | vendedor=”José Santos” Factura ))
nProd,nome
categoria=electrodomésticos
2.
CREATE VIEW ProdMassMedia as
SELECT P.nProd
FROM Produto P
WHERE P.preço > ( SELECT AVG(preço)
FROM Produto P1
WHERE P1.categoria=”electrodomésticos”)
6
Nome:
Número:
Grupo 2 – Resposta
3.
SELECT categoria, data, CONT(*)
FROM Factura F, Produto P
WHERE F.nProd = P.nProd
GROUP BY categoria
HAVING COUNT (*) > (SELECT COUNT (*)
FROM Factura F, Produto P1
WHERE F.nProd = P1.nProd
AND P1.categoria = P.categoria
AND F1.data = ‘12/02/2003’)
4.
a)
CREATE TABLE Produtos(
nProd INTEGER,
nome CHAR(20),
preço REAL,
categoria CHAR(20),
PRIMARY KEY (nProd),
CHECK (preço > 0.0))
b)
CREATE TABLE Produtos(
nProd INTEGER,
nome CHAR(20),
preço REAL,
categoria CHAR(20),
PRIMARY KEY (nProd),
CHECK categoria IN (“televisões”, “hi-fi”, “cozinha”))
ou com trigger
ou com uma tabela com categorias dos produtos e uma chave estrangeira da
tabela Produtos para a de categorias.
7
Nome:
Número:
Grupo 3 - XML (3 valores)
Considere os seguintes dados sob o formato relacional:
Id Duração
Título
Compositor
Maestro
3
1:01
Mad Rush
J. Sibelius
L.
Bernstein
4
1:47
Andante
L.
Beethoven
NULL
1:57
Upon
Enchanted
Ground
A.
Hovhaness
NULL
5
Id
13
55
56
57
Pai
3
5
5
5
Cantor
A. Karis
Y. Kondonassis
F. Hendrickx
H. Coryn
Escreva o documento XML obras.xml obtido depois de exportar os dados
relacionais acima para a seguinte DTD:
<!ELEMENT obras (peça*)>
<!ELEMENT peça (duração, título, compositor, maestro?, cantor*>
<!ELEMENT duração (#PCDATA)>
<!ELEMENT título (#PCDATA)>
<!ELEMENT compositor (#PCDATA)>
<!ELEMENT maestro (#PCDATA)>
<!ELEMENT cantor (#PCDATA)>
<!ATTLIST peça
id ID REQUIRED>
8
Nome:
Número:
Grupo 3 - Resposta
<!DOCUMENT OBRAS [
<obras>
<peça id=3>
<duração>1:01</duração>
<título>Mad Rush</título>
<compositor>J. Sibelius</compositor>
<maestro>L. Bernstein</compositor>
<cantor>A. Karis</cantor>
</peça>
<peça id=4>
<duração>1:47</duração>
<título>Andante</título>
<compositor>L. Beethoven</compositor>
</peça>
<peça id=5>
<duração>1:57</duração>
<título>Upon Enchanted Ground</título>
<compositor> A. Hovhaness</compositor>
<cantor>Y. Kondonassis</cantor>
<cantor>F. Hendrickx</cantor>
<cantor>H. Coryn</cantor>
</peça>
</obras>
]
9
Nome:
Número:
Grupo 4 – Organização de ficheiros e Índices (4 valores)
1. Diga qual a melhor organização de ficheiros para cada um dos seguintes tipos de
interrogações frequentes:
a) Procura de registos baseada num intervalo de valores.
b) Inserções e pesquisas onde a ordem dos registos não interessa.
c) Procura de um registo baseada no valor de um dado campo.
2. Qual a diferença entre um índice clustered e um índice unclustered. Dê um exemplo
de cada.
10
Nome:
Número:
Grupo 4 - Resposta
1.
a)
Sorted file
b)
Heap File
c)
Hash-Based
2.
Clustered: a ordem das entradas do ficheiro de indíce é a mesma da das
entradas do ficheiro de dados.
Unclustered: a ordem não é a mesma
Exemplo:
Ficheiro de dados
Id
1
2
3
4
Nome
Ana
Manuel
Bernardo
Amália
Ficheiro de índice
Idade
35
36
37
30
Nome
Amália
Ana
Bernardo
Manuel
Clustered index sobre idade
Id
4
1
3
2
Unclustered index sobre nome
11