PBD - Bases de Dados - SQL

Objectivos: introdução a SQL, uso da consola MySQL, sqlite3, e sqlitebrowser

Material de apoio: SQL ("slides")

1. Ambientação à consola MySQL

1.1. Inicie a consola

O MySQL está instalado nos computadores de laboratório.

Para se ligar ao servidor de MySQL através de uma consola de comandos execute:

$ mysql -uguest

Deverá obter em resposta algo similar a:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 8.0.15 MySQL Community Server - GPL
...
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

1.2. Comandos na consola mysql

A tabela seguinte resume alguns dos principais comandos que pode executar na consola. Experimente por exemplo os comandos help, e show databases;.

Para além dos comandos acima, pode ainda executar as instruções típicas de manipulação de esquema (ex. CREATE TABLE) ou dados (ex. INSERT, SELECT, UPDATE, e DELETE). Se estas forem complexas ou quiser executar várias instruções de uma só vez, será preferível colocar esseas intruções num ficheiro e carregá-los com o comando source descrito previamente.

1.3. Carregamento da BD biblioteca.

Descarregue o seguinte ficheiro da BD biblioteca: bd_bib.sql e carregue-o usando o comando source bd_bib.sql.

mysql> source bd_bib.sql;

Poderá depois verificar que tabelas compõem a BD, obter descrição do esquema ou consultar o conteúdo de uma tabela, por exemplo:

mysql> show tables;
+----------------------+
| Tables_in_biblioteca |
+----------------------+
| AUTORES              |
| CÓPIA                |
| ESTANTE              |
| LIVRO                |
| PRATELEIRA           |
| SECÇÃO               |
| UTENTE               |
+----------------------+
9 rows in set (0.00 sec)

mysql> desc LIVRO;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| ISBN    | bigint(20)  | NO   | PRI | NULL    |       |
| Título  | varchar(64) | NO   |     | NULL    |       |
| Editora | varchar(32) | NO   |     | NULL    |       |
| Ano     | year(4)     | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> select * from LIVRO;
+---------------+-----------------------------------------+-------------------+------+
| ISBN          | Título                                  | Editora           | Ano  |
+---------------+-----------------------------------------+-------------------+------+
| 9780131103627 | The C Programming Language, 2nd edition | Prentice Hall     | 1988 |
| 9780321356680 | Effective Java                          | Pearson Education | 2008 |
| 9789720049759 | A Mensagem                              | Porto Editora     | 2017 |
| 9789722526289 | Sonetos                                 | WOOK 11-17        | 2013 |
| 9789722709620 | Os Lusíadas                             | INCM              | 1999 |
| 9789724138695 | Astérix o Gaulês                        | Edições Asa       | 2004 |
| 9789724138947 | A Volta à Gália de Astérix              | Edições Asa       | 2005 |
+---------------+-----------------------------------------+-------------------+------+
7 rows in set (0.00 sec)

2. Operações de manipulação de dados

Nota: em qualquer altura pode executar novamente source bd_bib.sql para repôr o estado inicial da BD.

O diagrama abaixo sumariza o esquema relacional da BD. Consulte bd_bib.sql para mais detalhes.

2.1. Inserção em UTENTE

Insira um novo utente com os seguintes dados:

• Num: 8
• CC: 18848333
• Nome: 'Mariana Silva'
• DataNasc: '2000-01-23'
• Sexo: 'F'
• Telefone: 227348900
• Email: NULL

2.2. Consulta a UTENTE

Consulte agora a tabela UTENTE para ler os dados da Mariana Silva, inserida no passo anterior. Use uma cláusula WHERE na instrução SELECT que tenha em conta o nº de utente da Mariana Silva na cláusula WHERE.

2.3. Requisição de um livro

Suponha agora a utente Mariana Silva deseja requisitar uma cópia de 'Os Lusíadas'. Proceda nos seguintes passos:

• Consulte a tabela LIVRO para obter o ISBN do livro, tendo em conta o título do livro na cláusula WHERE.
• Consulte a tabela CÓPIA para saber que cópias do livro estão disponíveis. A cláusula WHERE deverá ter a forma ISBN = ... AND EmpUtente IS NULL.
• Actualize a tabela CÓPIA registando o empréstimo de uma das cópia disponíveis do livro à Mariana Silva com a data de hoje.

2.4. Devolução de um livro

Suponha que agora cópia nº 1 de 'Os Lusíadas' foi devolvida por um utente. Qual é o utente em causa? Actualize a tabela CÓPIA para registar a devolução.

2.5. Mudança de estantes de cópias de livros

A estante 'E12' está muito mau estado e vai ser retirada da biblioteca. Todos as cópias de livros nessa estante deverão ficar afectos à estante 'E13', mantendo-se o número da prateleira. Actualize a BD em conformidade nos seguintes passos:

• Actualize a tabela CÓPIA para reflectir a nova arrumação.
• Remova de PRATELEIRA todas os registos correspondentes à estante 'E12'.
• Remova de ESTANTE o registo da estante 'E12'.
• Será que bastaria remover o registo na tabela ESTANTE? Seria também possível remover primeiro as entradas de PRATELEIRA e ESTANTE e só em seguida actualizar CÓPIA? Reponha o estado da BD e teste estas duas opções.

2.6. Consulta de ISBNs

Usando consultas à BD, obtenha os ISBNs de livros:

• que tenham ano de edição posterior a 2010 (use o operador > na cláusula WHERE);
• que tenham como autor 'Fernando Pessoa' ou 'Luís de Camões' (use os operadores OR e =);
• que estejam arrumados na prateleira '3' da estante 'E99' e não se encontrem emprestados (use os operadores AND, IS NULL e =).

3. Criação de tabelas

3.1 Tabela FUNCIONÁRIO

Na BD de biblioteca será agora necessário representar funcionários da própria biblioteca.

Para esse efeito crie a tabela FUNCIONÁRIO em bd_bib.sql. A tabela deverá ter os mesmos atributos e restrições que tabela UTENTE, e ainda os seguintes atributos:

• Cargo: representável por uma "string" de 16 caracteres, não-opcional (ex. 'Bibliotecário', 'Director').
• Supervisor: nº de funcionário supervisor opcional. Para este atributo defina a restrição de chave externa (FOREIGN KEY) apropriada.

Insira de seguida 6 registos em FUNCIONÁRIO usando dados à sua escolha, por forma a que haja 2 supervisores de todos os outros funcionários (i.e., 2 supervisionados por supervisor). Use uma única instrução INSERT para o efeito.

No final, remova o tabela e os dados associados a esta usando uma instrução DROP TABLE.

3.2 Tabelas REVISTA e EDIÇÃO_DE_REVISTA

Considere agora que a biblioteca tem também disponíveis revistas para leitura na própria biblioteca.

Cria uma tabela REVISTA com os seguintes atributos, todos não opcionais (NOT NULL):

• Num: nº interno p/identificação da revista, que deverá ser a chave primária.
• Título: título da revista com até 64 caracteres. O título, embora não definido como chave primária, deverá ser único (use uma restrição UNIQUE).
• Periodicidade: um atributo enumerado com valores possíveis 'S' (semanal), 'M' (mensal), e 'A' (anual).
• Editora: nome da editora (até 32 caracteres).

De seguida crie uma tabela EDIÇÃO_DE_REVISTA que para registar edições de revistas com os seguintes atributos não-opcionais (NOT NULL):

• NumR: chave externa para nº da revista;
• NumEd: nº da edição;
• Data: data da edição.

A chave primária de REVISTA_EDIÇÃO deverá ser formada pelo par (NumR,NumEd).

4. Uso de operadores e funções SQL

Efectue as seguintes operações sobre a BD, empregando operadores e funções SQL.

Para resolver os exercícios poderá ser útil a consulta da secção "Functions and Operators" do manual de referência MySQL.

4.1

Faça consultas à BD para obter:

1. os nomes e anos de nascimento de cada utente usando a função YEAR sobre DataNasc;
2. os nomes de utentes , e mês e dia do seu aniversário que tenham nascido depois do ano 2000, usando MONTH e DAY analogamente a YEAR e o operador >;
3. os nomes de utentes começados por 'Pedro' ou terminados em 'Silva', usando os operadores LIKE e OR;
4. o ISBN, nº de cópia de livros que estejam emprestadas e respetivo utente, excepto aquelas emprestadas ao utente 1, usando os operadores AND, IS NOT NULL e <>.
5. informação respeitante a cada livro numa única string com o formato 'Título, Editora, Ano, ISBN' usando a função 'CONCAT'.

4.2

(Antes e depois de cada actualização pedida a seguir faça consultas à tabela em questão para verificar o efeito pretendido. Se errar pode retornar ao estado inicial da BD com source bd_bib.sql.)

Actualize a BD, alterando:

1. os emails de utentes para conterem apenas letras maiúsculas ("upper-case"), usando a função UPPER;

2. actualizando a arrumação de todas as cópias em estantes com código começado por 'E1' (usando LIKE) para a estante 'E99' e ainda incrementando o número da prateleira.

5. Alteração do esquema da BD

5.1

Edite o ficheiro bd_bib.sql para definir os seguintes valores de omissão usando o modificador DEFAULT:

1. NULL para Email em UTENTE ;
2. 'L' para Secção em ESTANTE ;
3. NULL para EmpData e EmpUtente em CÓPIA .

Insira de seguida um novo registo em cada uma das tabelas omitindo valores para os atributos em causa.

5.2

Novamente em bd_bib.sql adicione o modificador AUTO_INCREMENT a UTENTE.Num. No mesmo ficheiro edite as inserções em UTENTE por forma a omitir Num e verifique que os utentes ficam com numeração consecutiva recarregando a base de dados.

5.3

Usando o comando ALTER TABLE faça as seguintes alterações ao esquema da BD, verificando posteriormente os efeitos nas tabelas envolvidas (com consultas e desc TABELA):

1. remova o atributo Editora de LIVRO;
2. adicione o atributo 'Conservação' a LIVRO com tipo enumerado ENUM('Novo','Bom','Mau') NOT NULL DEFAULT 'Bom' .

5.4

Usando ON UPDATE CASCADE configure o esquema da BD por forma a que a actualização do ISBN de um livro seja propagada às tabelas AUTORES e CÓPIA.

De seguida actualize o ISBN de um livro e observe os efeitos em AUTORES e CÓPIA.

6. Consultas com ORDER BY, LIMIT e DISTINCT

Usando ORDER BY e/ou LIMIT e/ou DISTINCT consulte:

1. os nomes de autores por ordem alfabética;
2. os nomes de autores novamente mas eliminando duplicados;
3. os utentes do sexo masculino ordenados por data de nascimento descendente (utentes mais novos devem aparecer 1º);
4. a utente do sexo feminino mais nova;
5. as cópias de livros emprestadas ordenadas por ISBN (1º critério) e nº de utente (2º critério);
6. os ISBNs de livros com cópias emprestadas sem resultados duplicados;
7. as 3 cópias de livros com empréstimos mais recentes.

7. Consultas agregadas

Execute consultas agregadas para obter:

1. o nº total de cópias de livros;
2. o nº total de cópias emprestadas de livros;
3. o nº de cópias emprestadas de livros agrupadas por ISBN;
4. o nº total de cópias emprestadas de livros agrupadas por ISBN para livros com 2 ou mais cópias emprestadas;
5. as datas do último empréstimo de um livro (use MAX), agrupadas por ISBN, e ordenadas pela data do último empréstimo;
6. a média do nº de dias de empréstimos de cópias (de todos os livros) face à data corrente (use AVG, NOW e TIMESTAMPDIFF);
7. o resultado anterior agrupado por ISBN.

8. Consultas sobre múltiplas tabelas

Execute consultas sobre múltiplas tabelas, sem usar consultas encadeadas ou operadores de junção ("joins"), para obter a:

1. relação de cópias emprestadas em termos de nome do utente (que emprestou a cópia), ISBN, nº da cópia, e data de empréstimo (precisa de consultar as tabelas UTENTE e CÓPIA), ordenadas por nome do utente (1º critério).
2. a mesma relação da questão anterior, mas obtendo o título do livro em vez do ISBN (precisa de consultar adicionalmente a tabela LIVRO), ordenadas por nome do utente (1º critério), e título do livro (2º critério).
3. a relação de títulos de livros e correspondentes nº total de cópias, nº de total cópias emprestadas e nº total de cópias disponíveis, ordenada por nº total de cópias e título de livro.

9. Consultas encadeadas

Execute consultas encadeadas, sem usar operadores de junção ("joins"), para obter:

1. títulos de livros com alguma cópia emprestada (use por ex. IN ou EXISTS);
2. nºs e nomes de utentes com exactamente uma cópia de livro emprestada (use 1 = (SELECT ... FROM CÓPIA WHERE ...)).
3. nºs e nomes de utentes com mais de uma cópia de livro emprestada (use 1 < (SELECT ... FROM CÓPIA WHERE ...)).

4. os nomes de utentes e correspondentes nº total de cópias emprestadas, usando uma consulta com o seguinte esqueleto.

   SELECT 
     U.Nome, 
     (SELECT ... FROM CÓPIA WHERE ...) As NumCópias
   FROM UTENTE U;

10. Alterações à BD com consultas encadeadas

Usando consultas encadeadas, proceda às seguintes alterações à BD com uma única instrução UPDATE ou DELETE e usando os títulos de livros e nomes de autores ou utentes directamente quando necessário (sem recorrer a ISBNs ou nº de utente). Antes e depois das alterações, consulte o estado da tabela afectada para verificar a correção da operação.

1. Remova registos de UTENTE sem nenhuma cópia de livro emprestada.
2. Remova registos de CÓPIA para o livro 'Astérix o Gaulês' desde que se refiram a cópias não emprestadas.
3. Actualize CÓPIA de forma a registar a devolução de todos os livros por parte do utente com nome 'Pedro Costa'.
4. Actualize CÓPIA por forma a que todos os livros com autor 'Luís de Camões' ou 'Fernando Pessoa' sejam colocados na prateleira 3 da estante 'E12'.

11. Consultas com junções ("joins")

Nesta questão use o operador JOIN para efectuar consultas, depois averigue se é possível empregar antes NATURAL JOIN pelo menos em parte da consulta.

Efectue consultas com operadores de junção para obter:

1. a relação entre nomes de utentes e respectivos títulos de livros emprestados, ordenada primeiro por nome de utente e depois por título;
2. a relação entre nomes de utentes e correspondente total de cópias de livros emprestados, ordenada por nome de utente (note que é uma consulta agregada agrupada pelo nome de utente);
3. a relação entre nomes de autores e títulos de livros, ordenada primeiro por nome de autor e depois por título;
4. a relação entre nomes de autores, títulos de livros, e correspondente número de total de cópias de livros correspondentes na biblioteca (emprestadas ou não), ordenada primeiro por nome de autor e depois por título (note que é uma consulta agregada agrupada pelo nome do autor e título do livro);
5. a mesma relação anterior, acrescida da informação do nº de cópias emprestadas;

6. a relação entre o código da secções da biblioteca (tabela SECÇÃO) e o nº total de cópias de livros associadas a prateleiras nessa secção. O resultado esperado é:

   +---------+----------+
   | Código  | COUNT(*) |
   +---------+----------+ 
   | BD      |        6 |
   | L       |        9 |
   | T       |        3 |
   +---------+----------+

7. Use uma junção externa (OUTER JOIN) entre PRATELEIRA e CÓPIA para descobrir que prateleiras se encontram vazias, i.e., não terem cópias de livros associadas.

12. Vistas SQL ("views")

Usando a instrução CREATE VIEW defina as seguintes vistas SQL ("views"):

1. ARRUMAÇÃO(Título, NumCópia, Estante, Prateleira) para representar a arrumação de cópias de livros em termos do título de livro, nº de cópia, e a estante e prateleira de arrumação, de forma ordenada por título de livro e nº de cópia. Precisa de considerar as tabelas LIVRO e CÓPIA para a definição da vista. Espera-se o seguinte resultado para SELECT * FROM ARRUMAÇÃO:

   +-----------------------------------------+-----------+------------+---------+
   | Título                                  | NumCópia  | Prateleira | Estante |
   +-----------------------------------------+-----------+------------+---------+
   | A Mensagem                              |         1 | E13        |       2 |
   | A Mensagem                              |         2 | E13        |       2 |
   | A Mensagem                              |         3 | E13        |       2 |
   | A Volta à Gália de Astérix              |         1 | E14        |       2 |
   | A Volta à Gália de Astérix              |         2 | E14        |       2 |
   | Astérix o Gaulês                        |         1 | E14        |       1 |
   | Astérix o Gaulês                        |         2 | E14        |       1 |
   | Astérix o Gaulês                        |         3 | E14        |       1 |
   | Astérix o Gaulês                        |         4 | E14        |       1 |
   | Effective Java                          |         1 | E99        |       3 |
   | Os Lusíadas                             |         1 | E12        |       1 |
   | Os Lusíadas                             |         2 | E12        |       1 |
   | Os Lusíadas                             |         3 | E12        |       2 |
   | Os Lusíadas                             |         4 | E12        |       2 |
   | Sonetos                                 |         1 | E13        |       1 |
   | Sonetos                                 |         2 | E13        |       2 |
   | The C Programming Language, 2nd edition |         1 | E99        |       3 |
   | The C Programming Language, 2nd edition |         2 | E99        |       3 |
   +-----------------------------------------+-----------+------------+---------+

2. LIVROS_POR_SECÇÃO(SCódigo, SDesc, Total) para representar por cada secção da biblioteca (em termos do seu código e descrição na tabela SECÇÃO) o nº total de cópias associadas a prateleiras dessa secção, de forma ordenada por código de secção; precisa de considerar as tabelas SECÇÃO, ESTANTE e CÓPIA. Espera-se o seguinte resultado de SELECT * FROM LIVROS_POR_SECÇÃO:

   +----------+------------------+-------+
   | SCódigo  | SDesc            | Total |
   +----------+------------------+-------+
   | BD       | Banda Desenhada  |     6 |
   | L        | Literatura       |     9 |
   | T        | Livros Técnicos  |     3 |
   +----------+------------------+-------+
   ``