Aulas práticas - Ficha 5

Bases de Dados (CC2005), Dep. Ciência de Computadores, FCUP

Eduardo R. B. Marques, DCC/FCUP

Objectivos: introdução ao uso de SQL usando uma pequena base de dados como exemplo (cont.); consultas agregadas.

Referências:

BD de referência

Iremos usar a mesma BD da aula anterior disponível aqui: sn.sql

1. Criação de tabelas

1.1

Crie uma tabela para um grupo na rede social "Livro das Caras".

DROP TABLE IF EXISTS USER_GROUP;

CREATE TABLE IF NOT EXISTS USER_GROUP
(
   Num ... ,
   Creation ... ,
   Name ... ,
   Description ...
);

Os campos listados acima deverão obedecer às seguintes restrições:

Num: a chave primária da tabela (PRIMARY KEY), de tipo INT e com o modificador AUTO_INCREMENT;
Creation: campo do tipo DATETIME e valores não-nulos (NOT NULL) com valor por omissão (DEFAULT) igual a NOW() (função que retorna o tempo corrente).
Name: uma chave secundária da tabela (UNIQUE), campo de valores não nulos que tenha por tipo uma sequência de caracteres VARCHAR de tamanho máximo 64;
Description: campo de tipo TEXT com valores possivelmente nulos.

Deverá poder obter a seguinte descrição do seu esquema na consola mysql, se esta tiver sido bem definida:

mysql> desc USER_GROUP;
+-------------+-------------+------+-----+-------------------+----------------+
| Field       | Type        | Null | Key | Default           | Extra          |
+-------------+-------------+------+-----+-------------------+----------------+
| Num         | int(11)     | NO   | PRI | NULL              | auto_increment |
| Creation    | datetime    | NO   |     | CURRENT_TIMESTAMP |                |
| Name        | varchar(64) | NO   | UNI | NULL              |                |
| Description | text        | YES  |     | NULL              |                |
+-------------+-------------+------+-----+-------------------+----------------+
4 rows in set (0.00 sec)

1.2

Insira 3 registos em USER_GROUP com o seguinte comando:

INSERT INTO USER_GROUP(Creation, Name,Description)
VALUES
('2020-01-01 12:15:23', 'Azul e branco', 'Adeptos do FCP - moderado por João Pinto'),
('2020-02-20 14:30:00','Superheroes', 'Here you will find Batman, Catwoman, ...'),
('2020-02-21 23:59:59', 'Lost Souls',  NULL);

Se ocorreu algum erro, reveja a definição da tabela (alínea anterior 1.1). Consulte as entradas inseridas (SELECT * FROM USER_GROUP). Se tudo foi bem feito, deverá obter o seguinte resultado:

+-----+---------------------+---------------+-------------------------------------------+
| Num | Creation            | Name          | Description                               |
+-----+---------------------+---------------+-------------------------------------------+
|   1 | 2020-01-01 12:15:23 | Azul e branco | Adeptos do FCP - moderado por João Pinto  |
|   2 | 2020-02-20 14:30:00 | Superheroes   | Here you will find Batman, Catwoman, ...  |
|   3 | 2020-02-21 23:59:59 | Lost Souls    | NULL                                      |
+-----+---------------------+---------------+-------------------------------------------+

1.3

Insira agora um grupo sem especificar um valor para o campo Creation, i.e., apenas especificando valores para Name e Description. Verifique que a entrada inserida fica com um valor para a data/hora actual no campo Creation, dado que o valor DEFAULT é preenchido com uma chamada à função NOW().

1.4

Verifique que as seguintes inserções falham na tabela. Justifique porquê. Se alguma inserção suceder reveja a definição da tabela (1.1.).

INSERT INTO USER_GROUP(Num, Creation, Name, Description)
VALUES (1, '2020-01-01 12:34:56', 'Horror Movies', NULL);

INSERT INTO USER_GROUP(Name,Description)
VALUES ('Azul e branco', 'Adeptos do FCP - grupo alternativo');

INSERT INTO USER_GROUP(Creation, Name,Description)
VALUES (NULL, 'Horror Movies', 'A group about horror movies');

INSERT INTO USER_GROUP(Creation, Name,Description)
VALUES ('2020-02-31 12:34:56', 'Horror Movies', 'A group about horror movies');

2. Criação de tabelas (cont.)

2.1

Crie uma tabela para manter registos dos membros de um grupo. Complete o esqueleto dado abaixo considerando que:

Como é natural numa rede social, um utilizador deve poder ser membro de vários grupos, e um grupo pode ter vários membros - deverá entender a tabela em correspondência a um relacionamento M:N ("muitos para muitos") entre USER e USER_GROUP. Qual é a chave primária da tabela (PRIMARY KEY)?

UNum e GNum referem-se às chaves primárias das tabelas USER e USER_GROUP, i.e., USER(Num) e USER_GROUP(Num). Complete as definições das chaves externas (FOREIGN KEY).

DROP TABLE IF EXISTS GROUP_MEMBER;
CREATE TABLE IF NOT EXISTS GROUP_MEMBER
(
UNum INT NOT NULL,
GNum INT NOT NULL,
Role ENUM('Admin','Moderator','Member') NOT NULL,
PRIMARY KEY(...),
FOREIGN KEY(...) REFERENCES ...,
FOREIGN KEY(...) REFERENCES ...
);

2.2

Suponha que pretendemos associar utilizadores 'joao.pinto', 'semedo' e 'catwoman' ao grupo 'Azul e branco'.

As instruções a seguir permitem obter os números de utilizadores e o número do grupo em variáveis de sessão (@u1, @u2, @u3, e @group) para auxiliar a inserção:

SELECT Num INTO @u1 FROM USER WHERE Login='joao.pinto';
SELECT Num INTO @u2 FROM USER WHERE Login='semedo';
SELECT Num INTO @u3 FROM USER WHERE Login='catwoman';
SELECT Num INTO @group FROM USER_GROUP WHERE Name='Azul e Branco';
SELECT @u1, @u2, @u3, @group;

+------+------+------+--------+
| @u1  | @u2  | @u3  | @group |
+------+------+------+--------+
|    1 |    2 |    9 |      1 |
+------+------+------+--------+
1 row in set (0.00 sec)

Insira agora as entradas em GROUP_MEMBER da seguinte forma:

INSERT INTO GROUP_MEMBER(UNum,GNum,Role)
VALUES 
(@u1, @group, 'Admin'), 
(@u2, @group, 'Moderator'), 
(@u3, @group, 'Member');

Verifique os resultados:

SELECT * FROM GROUP_MEMBER WHERE GNum=@group;

+------+------+-----------+
| UNum | GNum | Role      |
+------+------+-----------+
|    1 |    1 | Admin     |
|    2 |    1 | Moderator |
|    9 |    1 | Member    |
+------+------+-----------+
3 rows in set (0.0053 sec)

Proceda de forma análoga aos passos anteriores para:
- associar os utilizadores 'batman', 'catwoman', e 'ziggy' ao grupo 'Superheroes', todos com Role='Admin'.
- associar o utilizador 'lady.z' aos grupos 'Superheroes' e 'Lost Souls com Role='Member'

3. Alterações a tabelas

Tendo as tabelas USER_GROUP e GROUP_MEMBER definidas, suponha que queremos distinguir "posts" feitos em grupos de utilizadores. Para tal precisamos de ter em consideração:

a necessidade de um campo por exemplo chamado GNum em POST para indicar que um "post" foi feito num grupo;
este campo deverá poder tomar valores NULL, em particular para os "posts" já existentes que não ficarão associados a grupos;
GNum tem de ser uma chave externa para USER_GROUP(Num).

Usando instruções ALTER TABLE faça as alterações necessárias à tabela POST. Veja o exemplo dado nas aulas teóricas (slide 61 em particular).

Insira alguns "posts" em associações a grupos existentes para verificar a correcção da sua definição. Tente também inserções com números de grupos inválidos para confirmar que a integridade referencial está a ser preservada.

4. Alterações a tabelas (cont.)

Usando novamente instruções ALTER TABLE, faça agora as seguintes alterações, verificando as suas alterações em cada caso (com inserções válidas/inválidas e/ou usando o comando desc ; veja slide 58):

Faça com que o campo Email seja único para todos os utilizadores (i.e. UNIQUE).
Altere o tipo de Role em GROUP_MEMBER para permitir ter o "role" de 'Guest', isto é, altere o tipo para ENUM('Admin','Moderator','Member','Guest') NOT NULL.
Remova o campo Phone de USER.

5. Consultas com agregação

Escreva consultas SQL (instruções SELECT) para obter:

O número total de posts (nº de entradas na tabela POST).

Resultado esperado:

+----------+
| COUNT(*) |
+----------+
|       12 |
+----------+
1 row in set (0.00 sec)

O número total de autores distintos de posts - use COUNT(DISTINCT(Author)) sobre POST.

Resultado esperado:

+-------------------------+
| COUNT(DISTINCT(Author)) |
+-------------------------+
|                       5 |
+-------------------------+

O número total de posts cujo conteúdo contenha a palavra 'batman' ou a palavra 'joker', e a soma do tamanho desses posts - use SUM sobre LENGTH(Content).

Resultado esperado:

+----------+----------------------+
| COUNT(*) | SUM(LENGTH(Content)) |
+----------+----------------------+
|        5 |                  235 |
+----------+----------------------+

As alturas em que o primeiro e último utilizador (USER) do sexo masculino se juntou à rede social segundo o campo Joined (use MIN e MAX).

Resultado esperado:
```
+-------------+-------------+
| MIN(Joined) | MAX(Joined) |
+-------------+-------------+
| 2019-12-01  | 2019-12-31  |
+-------------+-------------+
```
A idade média, mínima e máxima de todos os utilizadores à data de 2020-03-17, e a diferença entre a idade máxima e mínima; a idade de um utilizador pode ser calculada com TIMESTAMPDIFF(YEAR, BirthDate, '2021-03-17').

Resultado esperado (o nome das colunas definido com AS é irrelevante, mas ajuda a tornar os resultados mais legíveis):
```
+------+------+---------+------+
| Min  | Max  | Media   | Diff |
+------+------+---------+------+
|   15 |   82 | 44.0909 |   67 |
+------+------+---------+------+
1 row in set (0.00 sec)
```

6. Consultas com agregação agrupada (GROUP BY)

Escreva consultas (instruções SELECT) para obter:

O número total de comentários a posts por autor distinto - GROUP BY Author- ordenados pelo número de comentários de forma descendente, e depois pelo número do autor também de forma descendente (para autores com o mesmo nº de posts).

Resultado esperado:
```
+--------+----------+
| Author | NumPosts |
+--------+----------+
|      2 |        2 |
|     11 |        1 |
|      9 |        1 |
|      3 |        1 |
|      1 |        1 |
+--------+----------+
5 rows in set (0.00 sec)
```

Os mesmos dados da consulta (1), acrescidos da soma do tamanho dos comentários - use SUM(LENGTH(Content)) - e por forma que os resultados sejam ordenados primeiro pelo número de comentários de forma descendente, e depois pela soma do tamanho dos conteúdos também de forma ascendente (para autores com o mesmo nº de posts).

Resultado esperado:

+--------+----------+---------------+
| Author | NumPosts | ContentLength |
+--------+----------+---------------+
|      2 |        2 |            30 |
|      3 |        1 |             4 |
|     11 |        1 |            10 |
|      1 |        1 |            17 |
|      9 |        1 |            19 |
+--------+----------+---------------+
5 rows in set (0.00 sec)

O número de utilizadores agrupados por ano e mês de nascimento, ordenados por ano e depois por mês.

Resultado esperado:

+------+------+---+
| Y    | M    | N |
+------+------+---+
| 1939 |    1 | 1 |
| 1940 |    1 | 1 |
| 1972 |    1 | 2 |
| 1975 |   11 | 1 |
| 1976 |   12 | 1 |
| 1982 |    1 | 1 |
| 1985 |    6 | 1 |
| 1993 |    2 | 1 |
| 2002 |    5 | 1 |
| 2005 |   11 | 1 |
+------+------+---+
10 rows in set (0.00 sec)

O número de seguidores por utilizador (na tabela FOLLOWER) para utilizadores com 3 ou mais seguidores (será conveniente expressar esta condição com HAVING), ordenados de forma descendente pelo número de seguidores e depois pelo número de utilizador.

Resultado esperado:
```
+------+---+
| User | N |
+------+---+
|    9 | 5 |
|    8 | 4 |
|    1 | 3 |
|    3 | 3 |
|    7 | 3 |
|   10 | 3 |
+------+---+
6 rows in set (0.00 sec)
```

Os 3 seguidores que seguem mais utilizadores e respectivo número de utilizadores seguidos (use LIMIT).

Resultado esperado:

+----------+---+
| Follower | N |
+----------+---+
|        1 | 6 |
|        3 | 4 |
|       11 | 4 |
+----------+---+
3 rows in set (0.00 sec)