Aulas Práticas

Aula 1: 26.09.2013 / 30.09.2013

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: 03.10.2013 / 07.10.2013

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: 10.10.2013 / 14.10.2013

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: 17.10.2013 / 21.10.2013

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: 24.10.2013 / 28.10.2013

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: 31.10.2013 / 04.11.2013

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.

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

Aula 7: 07.11.2013 / 11.11.2013

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

1. Selecionar o primeiro e último nome de todos os clientes e de todos os empregados, ordenado alfabeticamente pelo primeiro e último nome.
2. Selecionar os títulos dos filmes com maior duração, ordenado alfabeticamente pelo título.
3. Selecionar o título dos filmes que nunca foram alugados pelo cliente 'LEE HAWKS' mas que já foram alugados por outros clientes, ordenado alfabeticamente pelo título.
4. Selecionar o título dos filmes em que não participa nenhum ator, ordenado alfabeticamente pelo título.
5. Selecionar o título dos filmes, com existência de DVDs, que nunca foram alugados, ordenado alfabeticamente pelo título.
6. Selecionar o primeiro e último nome dos clientes que alugaram filmes de todas as categorias, com existência de DVDs, ordenado alfabeticamente pelo primeiro e último nome.
7. Selecionar o primeiro e último nome dos atores que já participaram em filmes de todas as categorias, com existência de filmes, ordenado alfabeticamente pelo primeiro e último nome.

Aula 8: 14.11.2013 / 18.11.2013

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

1. Selecionar por país, o número de empregados que trabalham em lojas desse país, ordenado alfabeticamente pelo país.
2. Selecionar por título de filme, o número de DVDs que dele existem, ordenado alfabeticamente pelo título.
3. Selecionar o título dos filmes em que participa o actor 'HARRISON BALE' que já tiveram mais do que 20 alugueres, ordenado alfabeticamente pelo título.
4. Selecionar o título dos filmes dos quais há mais DVDs, ordenado alfabeticamente pelo título.
5. Selecionar o primeiro e último nome dos clientes que já fizeram devoluções tardias, ordenado alfabeticamente pelo primeiro e último nome.
6. Selecionar por nome de categoria, o número médio de actores por filme dessa categoria, ordenado alfabeticamente pelo nome.
7. Selecionar os pares de actores (primeiro e último nome de cada ator) que mais vezes contracenaram juntos, ordenado alfabeticamente pelo primeiro e último de ambos os atores.

Aula 9: 21.11.2013 / 25.11.2013

Treino Submissão SQL
Crie consultas SQL para responder às seguintes questões sobre a base de dados DVDs PIPOCAS EM CASA.
Para avaliar as suas respostas utilize o comando '

avaliabd
NumAula NumPerg RespostaEntreAspas

1. Selecionar o título dos filmes da categoria 'Sports', ordenado alfabeticamente pelo título.
2. Selecionar o primeiro e último nome dos atores que participam no filme 'WYOMING STORM', ordenado alfabeticamente pelo primeiro e último nome.
3. Selecionar o primeiro e último nome dos atores que participam em filmes da categoria 'Drama', ordenado alfabeticamente pelo primeiro e último nome.
4. Selecionar o título dos filmes que apenas podem ser alugados na loja número 1, ordenado alfabeticamente pelo título.
5. Selecionar o primeiro e último nome dos atores que já contracenaram com a actriz 'JULIA ZELLWEGER', ordenado alfabeticamente pelo primeiro e último nome.
6. Selecionar o número total de atores diferentes que entraram em filmes da categoria 'Action'.
7. Selecionar o primeiro e último nome dos clientes que já alugaram filmes em lojas situadas no mesmo país onde moram, ordenado alfabeticamente pelo primeiro e último nome.
8. Selecionar, por categoria, o nome da categoria e a média da duração dos filmes dessa categoria, ordenado alfabeticamente pelo nome.
9. Selecionar o nome das categorias para as quais existem mais filmes, ordenado alfabeticamente pelo nome.
10. Selecionar o primeiro e último nome dos clientes cujos alugueres de filmes totalizam a maior duração em minutos, ordenado alfabeticamente pelo primeiro e último nome.

Aula 10: 28.11.2013 / 02.12.2013

Submissão SQL

Aula 11: 05.12.2013 / 09.12.2013

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.

Aula 12: 12.12.2013 / 16.12.2013

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.