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:

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:

  1. 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)?

  2. 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'.

  1. 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)
  2. 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)
  3. Proceda de forma análoga aos passos anteriores para:

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:

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):

  1. Faça com que o campo Email seja único para todos os utilizadores (i.e. UNIQUE).

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

  3. Remova o campo Phone de USER.

5. Consultas com agregação

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

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

    Resultado esperado:

    +----------+
    | COUNT(*) |
    +----------+
    |       12 |
    +----------+
    1 row in set (0.00 sec)
  2. O número total de autores distintos de posts - use COUNT(DISTINCT(Author)) sobre POST.

    Resultado esperado:

    +-------------------------+
    | COUNT(DISTINCT(Author)) |
    +-------------------------+
    |                       5 |
    +-------------------------+
  3. 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 |
    +----------+----------------------+
  4. 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  |
    +-------------+-------------+
  5. 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:

  1. 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)
  2. 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)
  3. 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)
  4. 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)
  5. 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)