Aulas Práticas
Aula 1: 01.10.2010 / 04.10.2010
Os Modelos ER e EER
A FCUP pretende construir uma base de dados de apoio à gestão das
notas nos exames. A base de dados deve guardar informação relativa aos
alunos, como sejam, o número do BI, o nome, o sexo, a morada
(decomposta em localidade, rua e número), os telefones de contacto, os
cursos em que está inscrito, os exames que já realizou em cada curso e
as respectivas notas e a média de cada curso. Sempre que um aluno se
inscreve num determinado curso é-lhe atribuído um número mecanográfico
diferente, ou seja, um aluno pode ter vários números mecanográficos
que correspondem a diferentes cursos de licenciatura, mestrado e/ou
doutoramento.
A base de dados deve também guardar informação relativa aos cursos,
como sejam, o nome, o grau (licenciatura, mestrado ou doutoramento) e
o conjunto de disciplinas do curso. Uma disciplina pode ser leccionada
a vários cursos. Para as disciplinas deve ser guardado o código e o
nome da disciplina, bem como as datas (decompostas em dia e hora de
início) dos exames realizados à disciplina.
-
Desenhe um diagrama ER/EER para a base de dados descrita acima. Não
introduza atributos auxiliares na sua representação, ou seja,
considere apenas os atributos descritos no texto.
Aula 2: 08.10.2010 / 11.10.2010
Os Modelos ER e EER
As lojas FNAQUE pretendem contruir uma base de dados para guardar
informação sobre o sistema de pontos dos clientes. Os clientes
acumulam pontos à medida que vão fazendo compras e sempre que atingem
uma determinada pontuação podem trocar os seus pontos por vales de
compras. A base de dados deve guardar a seguinte informação sobre os
clientes: o nome, o endereço, o número de contribuinte, o número de
cliente, os pontos actuais e as compras efectuadas. Os clientes podem
ser pessoas ou empresas. No caso de pessoas deve ser ainda guardado o
BI e os telefones de contacto. No caso de empresas deve ser ainda
guardado o ramo de actividade e os telefones de contacto da empresa.
A base de dados deve também guardar informação relativa às compras de
cada cliente, como sejam, o valor e a data (decomposta em dia e hora)
da compra, os artigos adquiridos e o número de pontos obtidos na
compra. Para além de obterem pontos ao comprarem determinados artigos,
os clientes obtêm 3 pontos por cada dia que fizerem compras (se
fizerem duas ou mais compras num mesmo dia só obtêm 3 pontos). Para os
artigos deve ser guardada a seguinte informação: o código do artigo, a
descrição, o preço unitário e os pontos que este dá (pode não dar
nenhum). Os preços e os pontos de cada artigo podem variar ao longo do
tempo. A informação relativa à troca de pontos por vales de compras
por parte dos clientes deve ficar igualmente registada na base de
dados (a relação entre o valor do vale e o número de pontos
necessários para fazer a troca pode variar ao longo do tempo). Os
vales, para além de poderem ser trocados por pontos, também podem ser
comprados como qualquer outro artigo.
-
Desenhe um diagrama ER/EER para a base de dados descrita acima. Não
introduza atributos auxiliares na sua representação, ou seja,
considere apenas os atributos descritos no texto.
Aula 3: 15.10.2010 / 18.10.2010
O Modelo Relacional
-
Converta os diagramas das Aulas 1 e 2 para o modelo relacional e
indique os passos que efectua na conversão (veja aqui os diagramas
ER/ERR da Aula 1 e
da Aula 2).
Aula 4: 22.10.2010 / 25.10.2010
Os Modelos ER e EER
A empresa de aluguer de DVDs PIPOCAS EM CASA pretende criar uma base
de dados para suportar a sua actividade. A base de dados deve guardar
informação relativa às lojas da PIPOCAS EM CASA, que presentemente são
apenas duas, mas que poderão vir a ser mais. Para cada loja deve
guardar-se um número identificador de loja, o seu gerente, a morada
postal da loja (decomposta em primeira linha de morada e segunda linha
de morada), a cidade, o distrito, o país, o código postal e o
telefone. A base de dados deve guardar também informação relativa aos
funcionários, que neste momento são apenas dois, um em cada loja, da
qual são os gerentes, mas que poderão também vir a ser mais. Para cada
funcionário deve guardar-se o seu número de identificação, o seu nome,
o seu apelido, a morada postal (decomposta em primeira linha de morada
e segunda linha de morada), a cidade, o distrito, o país, o seu código
postal, a sua fotografia, o seu telefone, a loja a que pertencem e o
seu estado (que pode ser 1 para activo ou 0 para inactivo).
A base de dados deve guardar informação relativa aos filmes, como
sejam um número identificador do filme, o seu título, uma descrição do
filme, a duração do aluguer a que está sujeito, a tarifa de aluguer, o
custo em caso de perda, a duração do filme e o seu rating (que pode
ser G, PG, PG-13, R ou NC-17). Deve guardar também informação relativa
aos actores, como sejam um número identificador do actor, o seu nome e
o seu apelido. A base de dados deve registar ainda que actores entram
em que filmes. Os filmes pertencem a uma determinada categoria e cada
categoria tem um número identificador e um nome.
A base de dados deve guardar ainda informação relativa aos clientes,
como sejam um número identificador de cliente, a loja onde se
inscreveram, o seu nome, o seu apelido, a morada postal (decomposta em
primeira linha de morada e segunda linha de morada), a cidade, o
distrito, o país, o número de telefone, o código postal e o seu estado
(que pode ser 1 para activo ou 0 para inactivo). A base de dados deve
guardar também informação relativa ao inventário, como sejam o número
identificador de cada DVD, o filme que reproduzem, e a loja a que
pertencem. Devem ficar ainda registados todos os alugueres que são
feitos, guardando-se a data/hora do aluguer, o DVD alugado, o cliente
que o alugou, a data de devolução, bem como qual o empregado que
processou o aluguer. Devem guardar-se também todos os pagamentos
efectuados por clientes, guardando-se o número identificador do
pagamento, quem efectua e quem recebe o pagamento, a quantia e a
data/hora do pagamento.
-
Desenhe um diagrama ER/EER para a base de dados descrita acima. Não
introduza atributos auxiliares na sua representação, ou seja,
considere apenas os atributos descritos no texto.
Aula 5: 29.10.2010
O Modelo Relacional
-
Converta o diagrama da Aula 4 para o modelo relacional e indique os
passos que efectua na conversão.
A Linguagem SQL
-
Tendo por referência o modelo relacional obtido, implemente a
respectiva base de dados em SQL criando as tabelas necessárias,
indicando as chave primárias e externas e escolhendo os tipos de dados
apropriados para cada atributo. Em seguida, carregue as diferentes
tabelas com alguns dados.
Aula 6: 05.11.2010 / 08.11.2010
A Linguagem SQL
Faça download do
seguinte ficheiro e execute o
comando 'mysql -u guest guest < dvd.sql' para
carregar uma versão inicial da base de dados DVDs PIPOCAS EM CASA. Em
seguida, crie consultas SQL
(veja aqui o modelo relacional
da base de dados) para responder às seguintes questões sobre a base de
dados DVDs PIPOCAS EM CASA.
- Seleccionar o nome das categorias que começam pela letra 'S'.
- Seleccionar o primeiro e último nome de todos os actores,
ordenados alfabeticamente pelo último nome.
- Seleccionar o nome de todos os filmes alugados pelo cliente número
258.
- Seleccionar o nome dos filmes em que participa o actor 'HARRISON
BALE'.
- Seleccionar o primeiro e último nome dos actores que participam em
filmes da categoria 'Drama' (note que existem duas actrizes com o
nome 'SUSAN DAVIS').
- Seleccionar o nome dos clientes que têm actualmente DVDs
alugados.
- Seleccionar o primeiro e último nome de cada empregado e o
primeiro e último nome do respectivo gerente.
- Actualizar o telefone da cliente 'KAREN JACKSON' para
'351212212212'.
- Inserir um novo DVD para o filme 'EYES DRIVING' na loja número
1.
- Eliminar todos os pagamentos efectuados pela cliente 'LISA
ANDERSON'.
Aula 7: 12.11.2010 / 15.11.2010
A Linguagem SQL
Crie consultas SQL para responder às seguintes questões sobre a base
de dados DVDs PIPOCAS EM CASA.
- Seleccionar o primeiro e último nome de todos os clientes e de
todos os empregados.
- Seleccionar os nomes dos filmes com maior duração.
- Seleccionar o nome dos filmes que nunca foram alugados pelo
cliente 'LEE HAWKS' mas que já foram alugados por outros
clientes.
- Seleccionar o nome dos filmes em que não participa nenhum
actor.
- Seleccionar o nome dos filmes, com existência de DVDs, que nunca
foram alugados.
- Seleccionar o nome dos clientes que alugaram filmes de todas as
categorias.
- Seleccionar o nome dos actores que já participaram em filmes de
todas as categorias.
Aula 8: 19.11.2010 / 22.11.2010
A Linguagem SQL
Crie consultas SQL para responder às seguintes questões sobre a base
de dados DVDs PIPOCAS EM CASA.
- Seleccionar, por cidade, o número de empregados que trabalham em
lojas dessa cidade.
- Seleccionar para cada filme o número de DVDs que dele
existem.
- Seleccionar o nome dos filmes em que participa o actor 'HARRISON
BALE' que já tiveram mais do que 20 alugueres.
- Seleccionar o nome dos filmes dos quais há mais DVDs.
- Seleccionar o nome dos clientes que já fizeram devoluções tardias,
de forma ordenada decrescente no número total de devoluções
tardias.
- Seleccionar, por nome de categoria, o número médio de actores por
filme dessa categoria.
- Seleccionar os pares de actores que mais vezes contracenaram
juntos.
Aula 9: 26.11.2010 / 29.11.2010
Treino Submissão SQL
Faça download do
seguinte ficheiro e execute o
comando 'mysql -u guest guest < avalia.sql' para
carregar a versão de treino da base de dados DVDs PIPOCAS EM CASA. Em
seguida, faça download do
avaliador automático que lhe
permitirá executar comandos do tipo './avaliabd
NúmeroPergunta StringSQLEntreAspas' para avaliar as suas
respostas para as consultas SQL que respondem às seguintes questões
sobre a base de dados DVDs PIPOCAS EM CASA.
- Seleccionar o primeiro e último nome de cada empregado e o
primeiro e último nome do respectivo gerente, ordenado pelo
atributo 'staff_id' do empregado.
- Seleccionar o primeiro nome dos actores que participam no filme
'WYOMING STORM', ordenando alfabeticamente pelo primeiro
nome.
- Seleccionar o primeiro e último nome dos actores que participam em
filmes da categoria 'Drama', ordenado decrescentemente pelo
atributo 'actor_id'.
- Seleccionar o primeiro e último nome dos clientes que já alugaram
filmes em lojas que ficam em cidades diferentes da cidade onde
moram, ordenado pelo atributo 'customer_id' do cliente.
- Seleccionar o primeiro e último nome dos actores que já
contracenaram com a actriz 'JULIA ZELLWEGER', ordenado pelo
atributo 'actor_id'.
- Seleccionar, por categoria, a média da duração dos filmes dessa
categoria, ordenado alfabeticamente pelo nome da categoria
(considere apenas categorias para as quais existe pelo menos um
filme na base de dados).
- Seleccionar o nome dos filmes que já tiveram devoluções tardias
juntamente com o total dessas devoluções, ordenado
decrescentemente no número total de devoluções tardias (use
DATEDIFF(data1,data2) para obter a diferença em dias entre duas
datas).
- Seleccionar o nome das categorias para as quais existem mais
filmes na base de dados, ordenado alfabeticamente pelo nome da
categoria.
Aula 10: 03.12.2010
Submissão SQL
Aula 11: 10.12.2010 / 13.12.2010
Normalização
- Considere as relações que se seguem, em que a relação
RALLY_1NF é a normalização da relação RALLY para a
primeira forma normal.
RALLY(NumVeículo, Categoria, NumRodas, NumEixos, {NomeParticipante, País, DataNasc}, {NumEtapa, Origem, Destino, NumKms, Resultado})
RALLY_1NF(NumVeículo, Categoria, NumRodas, NumEixos, NomeParticipante, País, DataNasc, NumEtapa, Origem, Destino, NumKms, Resultado)
- Identifique as dependências funcionais existentes na relação
RALLY_1NF e indique justificando qual a chave primária
(assuma que cada participante só participa com um único
veículo e que podem existir participantes com o mesmo nome em
veículos diferentes).
- Decomponha a relação RALLY_1NF até à forma normal mais
alta possível indicando cada passo de normalização que
efectue.
- Considere as relações que se seguem, em que a relação
VEÍCULO_1NF é a normalização da relação VEÍCULO para
a primeira forma normal.
VEÍCULO(Matrícula, Marca, Modelo, {NomeProprietário, NumBI, DataAquisição}, {DataInfracção, {Hora, Local, Descrição}})
VEÍCULO_1NF(Matrícula, Marca, Modelo, NomeProprietário, NumBI, DataAquisição, DataInfracção, Hora, Local, Descrição)
- Identifique as dependências funcionais existentes na relação
VEÍCULO_1NF e indique justificando qual a chave
primária (assuma que um proprietário pode possuir vários
veículos).
- Decomponha a relação VEÍCULO_1NF até à forma normal
mais alta possível indicando cada passo de normalização que
efectue.
Organização Física dos Dados
- Considere um ficheiro ordenado por uma chave formada por dois
atributos A e B com 1.200.000 registos guardado num disco com
blocos de 2048 bytes. Os atributos A e B têm tamanhos de 8 e 12
bytes respectivamente e os registos têm um tamanho fixo de 256
bytes e não atravessam blocos.
- Determine o número máximo de acessos a blocos do disco
necessários para encontrar um registo utilizando os atributos
A e B (sem qualquer tipo de indexação).
- Determine o número de acessos a blocos do disco necessários
para encontrar um registo utilizando um índice de níveis
múltiplos sobre os atributos A e B implementado como uma
árvore B+. Assuma que um apontador para blocos do disco ocupa
4 bytes e que a ocupação média dos nós internos da árvore B+ é
de 60 apontadores para blocos e que a ocupação média dos nós
folha da árvore B+ é de 50 entradas.
