Aulas Práticas

Aula 1: 27.09.2012 / 01.10.2012

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 respetivas 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 lecionada 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: 04.10.2012 / 08.10.2012

Os Modelos ER e EER
As lojas FNAQUE pretendem construir 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 atuais e as compras efetuadas. 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 atividade 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: 11.10.2012 / 15.10.2012

O Modelo Relacional

Converta os diagramas das Aulas 1 e 2 para o modelo relacional e indique os passos que efetua na conversão (veja aqui os diagramas ER/ERR da Aula 1 e da Aula 2).

Aula 4: 18.10.2012 / 22.10.2012

Os Modelos ER e EER / O Modelo Relacional
A empresa de aluguer de DVDs PIPOCAS EM CASA pretende criar uma base de dados para suportar a sua atividade. 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, segunda linha de morada, código postal, cidade, distrito e país) 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, segunda linha de morada, código postal, cidade, distrito e país), a sua fotografia, o seu telefone, a loja em que trabalha 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 atores, como sejam um número identificador do ator, o seu nome e o seu apelido. A base de dados deve registar ainda que atores 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 fez a inscrição, o seu nome, o seu apelido, a morada postal (decomposta em primeira linha de morada, segunda linha de morada, código postal, cidade, distrito e país), o número de telefone 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 (i.e., o conjunto de DVDs que existem nas várias lojas), como sejam o número identificador de cada DVD, o filme que reproduzem, e a loja a que pertencem atualmente. 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 efetuados por clientes, guardando-se o número identificador do pagamento, quem efetua 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.
Converta o diagrama para o modelo relacional e indique os passos que efetua na conversão.

Aula 5: 25.10.2012 / 29.10.2012

A Linguagem SQL
Tendo por referência o modelo relacional obtido para a base de dados DVDs PIPOCAS EM CASA, implemente a respetiva 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. Para isso utilize o sistema de gestão de base de dados MySQL que se encontra disponível nas máquinas dos laboratórios (clique aqui para consultar a documentação respetiva).

Seguem-se alguns comandos básicos de utilização do MySQL:

Para iniciar uma nova sessão: 'mysql -u guest guest'
Para terminar a sessão atual: 'exit' ou 'quit'
Para obter informações sobre a sessão atual: 'status'
Para obter ajuda: 'help' ou 'help contents'
Para listar as tabelas da base de dados atual: 'show tables;'
Para listar as definições de uma tabela: 'describe TABLE_XPTO;'

Aula 6: 05.11.2012 / 08.11.2012

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.

Selecionar o nome das categorias que começam pela letra 'S'.
Selecionar o primeiro e último nome de todos os atores, ordenados alfabeticamente pelo último nome.
Selecionar o nome de todos os filmes alugados pelo cliente número 258.
Selecionar o nome dos filmes em que participa o ator 'HARRISON BALE'.
Selecionar o nome dos clientes que têm atualmente DVDs alugados.
Selecionar o primeiro e último nome de cada empregado e o primeiro e último nome do respetivo gerente.
Selecionar o primeiro e último nome dos atores que participam em filmes da categoria 'Drama' (note que existem duas actrizes com o nome 'SUSAN DAVIS').
Inserir um novo DVD para o filme 'EYES DRIVING' na loja número 1.
Eliminar todos os pagamentos efetuados pela cliente 'LISA ANDERSON'.
Atualizar o telefone da cliente 'KAREN JACKSON' para '351212212212'.

Aula 7: 12.11.2012 / 15.11.2012

A Linguagem SQL
Crie consultas SQL para responder às seguintes questões sobre a base de dados DVDs PIPOCAS EM CASA.

Selecionar o primeiro e último nome de todos os clientes e de todos os empregados.
Selecionar os nomes dos filmes com maior duração.
Selecionar o nome dos filmes que nunca foram alugados pelo cliente 'LEE HAWKS' mas que já foram alugados por outros clientes.
Selecionar o nome dos filmes em que não participa nenhum ator.
Selecionar o nome dos filmes, com existência de DVDs, que nunca foram alugados.
Selecionar o nome dos clientes que alugaram filmes de todas as categorias.
Selecionar o nome dos atores que já participaram em filmes de todas as categorias.

Aula 8: 19.11.2012 / 22.11.2012

A Linguagem SQL
Crie consultas SQL para responder às seguintes questões sobre a base de dados DVDs PIPOCAS EM CASA.

Selecionar, por cidade, o número de empregados que trabalham em lojas dessa cidade.
Selecionar para cada filme o número de DVDs que dele existem.
Selecionar o nome dos filmes em que participa o ator 'HARRISON BALE' que já tiveram mais do que 20 alugueres.
Selecionar o nome dos filmes dos quais há mais DVDs.
Selecionar o nome dos clientes que já fizeram devoluções tardias, de forma ordenada decrescente no número total de devoluções tardias.
Selecionar, por nome de categoria, o número médio de atores por filme dessa categoria.
Selecionar os pares de atores que mais vezes contracenaram juntos.

Aula 9: 26.11.2012 / 29.11.2012

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.

Selecionar o primeiro e último nome de cada empregado e o primeiro e último nome do respetivo gerente, ordenado pelo atributo 'staff_id' do empregado.
Selecionar o primeiro nome dos atores que participam no filme 'WYOMING STORM', ordenando alfabeticamente pelo primeiro nome.
Selecionar o primeiro e último nome dos atores que participam em filmes da categoria 'Drama', ordenado decrescentemente pelo atributo 'actor_id'.
Selecionar 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.
Selecionar o primeiro e último nome dos atores que já contracenaram com a actriz 'JULIA ZELLWEGER', ordenado pelo atributo 'actor_id'.
Selecionar, por categoria, o nome da categoria e 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).
Selecionar 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).
Selecionar o nome das categorias para as quais existem mais filmes na base de dados, ordenado alfabeticamente pelo nome da categoria.

Aula 10: 06.12.2012

Submissão SQL

Aula 11: 10.12.2012 / 13.12.2012

Normalização

Considere as relações que se seguem, em que a relação RESERVA_1NF é a normalização da relação RESERVA para a primeira forma normal.
RESERVA(NumReserva, DataReserva, BICliente, NomeCliente, {NumQuarto, TipoQuarto, {DataEstadia, PreçoNoite}}, PreçoTotal)
RESERVA_1NF(NumReserva, DataReserva, BICliente, NomeCliente, NumQuarto, TipoQuarto, DataEstadia, PreçoNoite, PreçoTotal)
- Identifique as dependências funcionais existentes na relação RESERVA_1NF e indique justificando qual a chave primária (assuma que o preço por noite pode variar em função do tipo de quarto e da época do ano).
- Decomponha a relação RESERVA_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 BIBLIOTECA_1NF é a normalização da relação BIBLIOTECA para a primeira forma normal.
BIBLIOTECA(NumExemplar, DataAquisição, TítuloObra, {NomeAutor}, {NumUtilizador, NomeUtilizador, DataRequisição, DataEntega})
BIBLIOTECA(NumExemplar, DataAquisição, TítuloObra, NomeAutor, NumUtilizador, NomeUtilizador, DataRequisição, DataEntega)
- Identifique as dependências funcionais existentes na relação BIBLIOTECA_1NF e indique justificando qual a chave primária (assuma que uma obra pode ter vários exemplares e que um utilizador pode requisitar várias vezes o mesmo exemplar).
- Decomponha a relação BIBLIOTECA_1NF até à forma normal mais alta possível indicando cada passo de normalização que efectue.

Organização Física dos Dados

Considere a relação R(A,B,C,D,E) representada num ficheiro ordenado pelo atributo A com 40.000 registos guardado num disco com blocos de 4096 bytes. O atributo A é a chave primária da relação e os atributos B e C formam uma chave secundária. Os atributos A, B e C têm tamanhos de 10, 38 e 60 bytes respetivamente e os registos têm um tamanho fixo de 204 bytes e não atravessam blocos. Considere ainda a seguinte expressão SQL sobre R:
SELECT E, D FROM R WHERE C = 'x' AND B = 'y';
- Determine o número mínimo, médio e máximo de acessos a blocos do disco necessários para encontrar o registo que responde à expressão SQL (sem qualquer tipo de indexação).
- Determine o número mínimo, médio e máximo de acessos a blocos do disco necessários para encontrar o registo que responde à expressão SQL utilizando um índice de níveis múltiplos. Assuma que um apontador para blocos do disco ocupa 4 bytes.

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 respetivamente 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.