Aulas práticas - Ficha 4

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

BD de referência

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

Relembrando, pode iniciar a consola MySQL no laboratório do DCC com:

$ mysql -uguest

e inicializar (ou re-inicializar) a BD com

mysql> source sn.sql;

Outros comandos na consola MySQL são sumariados na Ficha 3.

1. Criação de tabelas

Nota: para este exercício será conveniente a criação de um ficheiro (ex. ex1.sql) para termos os comandos SQL.

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, 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 especicar 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:

mysql> SELECT * FROM GROUP_MEMBER WHERE GNum=@group;
+------+------+-----------+
| UNum | GNum | Role      |
+------+------+-----------+
|    2 |    2 | Moderator |
|    8 |    2 | Admin     |
|    9 |    2 | Member    |
+------+------+-----------+

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

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

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 ORDER BY, LIMIT, e DISTINCT

ORDER BY - especifica critério(s) de ordenação para resultados devolvidos por uma consulta

SELECT  <Campo 1>,..., <Campo n>     
FROM <TABELA> 
...
ORDER BY <Criterio Ord. 1> [ASC|DESC], ..., <Criterio Ord. k> [ASC|DESC]
...

LIMIT - limita número de resultados devolvidos por uma consulta.

SELECT  <Campo 1>,..., <Campo n>     
FROM <TABELA> 
...
LIMIT <Número de resultados>;

DISTINCT - filtra dados duplicados nos resultados devolvidos por uma consulta:
```
SELECT  DISTINCT <Campo 1>,..., <Campo n>     
FROM <TABELA> 
...
```

Efectue consultas para obter (os resultados esperados são listados para cada consulta):

O "login" e o nome de utilizadores, ordenados por "login";

+------------+----------------+
| Login      | Name           |
+------------+----------------+
| batman     | Bruce Wayne    |
| catwoman   | Selina Kyle    |
| eva        | Eva Mendes     |
| joao.pinto | João Pinto     |
| lady.z     | Lady Stardust  |
| maria      | Maria Silva    |
| mendocas   | Filipa Mendes  |
| pedro      | Pedro Costa    |
| pedro2     | Pedro Simões   |
| semedo     | Carlos Semedo  |
| ziggy      | Ziggy Stardust |
+------------+----------------+
11 rows in set (0.01 sec)

O "login", nome, e data de nascimento de utilizadores ordenados primeiro por data de nascimento de forma decrescente, e depois (como segundo critério) por "login" (de forma crescente).

+------------+----------------+------------+
| Login      | Name           | BirthDate  |
+------------+----------------+------------+
| maria      | Maria Silva    | 2005-11-17 |
| mendocas   | Filipa Mendes  | 2002-05-03 |
| pedro2     | Pedro Simões   | 1993-02-22 |
| semedo     | Carlos Semedo  | 1985-06-02 |
| pedro      | Pedro Costa    | 1982-01-03 |
| joao.pinto | João Pinto     | 1976-12-19 |
| eva        | Eva Mendes     | 1975-11-18 |
| lady.z     | Lady Stardust  | 1972-01-01 |
| ziggy      | Ziggy Stardust | 1972-01-01 |
| catwoman   | Selina Kyle    | 1940-01-01 |
| batman     | Bruce Wayne    | 1939-01-01 |
+------------+----------------+------------+
11 rows in set (0.00 sec)

Os mesmos dados da consulta 2, mas restritos a utilizadores do sexo masculino (use WHERE).

+------------+----------------+------------+
| Login      | Name           | BirthDate  |
+------------+----------------+------------+
| pedro2     | Pedro Simões   | 1993-02-22 |
| semedo     | Carlos Semedo  | 1985-06-02 |
| pedro      | Pedro Costa    | 1982-01-03 |
| joao.pinto | João Pinto     | 1976-12-19 |
| ziggy      | Ziggy Stardust | 1972-01-01 |
| batman     | Bruce Wayne    | 1939-01-01 |
+------------+----------------+------------+
6 rows in set (0.00 sec)

Os mesmos dados da consulta 2, mas limitados a 3 resultados (use LIMIT).

+----------+---------------+------------+
| Login    | Name          | BirthDate  |
+----------+---------------+------------+
| maria    | Maria Silva   | 2005-11-17 |
| mendocas | Filipa Mendes | 2002-05-03 |
| pedro2   | Pedro Simões  | 1993-02-22 |
+----------+---------------+------------+
3 rows in set (0.00 sec)

O número, autor, conteúdo, e data de criação dos 5 posts mais recentes, ordenados de forma decrescente por data de criação.

+-----+--------+----------------------------------------------------------------------+---------------------+
| Num | Author | Content                                                              | Creation            |
+-----+--------+----------------------------------------------------------------------+---------------------+
|  11 |      9 | Joker is a busy man, he has no time for social networking.           | 2020-02-18 17:00:30 |
|  10 |      9 | ... we ran away on the Joker-mobile!                                 | 2020-02-17 23:51:12 |
|   9 |      9 | Joker and I went out last night ... but pesky Batman had to show up. | 2020-02-17 23:51:00 |
|   3 |      1 | Não foi nada de especial, chutei com o pé que estava mais a mão!     | 2020-02-13 12:31:59 |
|   8 |      9 | Batman is a troll! Joker is my sweetheart these days!                | 2020-02-10 23:41:00 |
+-----+--------+----------------------------------------------------------------------+---------------------+

As "hash-tags" usadas em "posts" (conforme a tabela HASHTAG) ordenadas por "post" (primeiro) e (depois) por "hash tag".

+------+-------------+
| Post | Tag         |
+------+-------------+
|    1 | fcp         |
|    2 | azulibranco |
|    2 | fcp         |
|    3 | fcp         |
|    4 | lonely      |
|    5 | lonely      |
|    6 | joker       |
|    7 | kitty       |
|    8 | batman      |
|    8 | troll       |
|    9 | batman      |
|    9 | joker       |
|    9 | troll       |
|   10 | batman      |
|   10 | joker       |
|   10 | troll       |
|   11 | joker       |
|   12 | 2020        |
+------+-------------+
18 rows in set (0.00 sec)

As "hash-tags" distintas usadas em associação a posts , ordenadas por "hashtag" (use DISTINCT além de ORDER BY).

+-------------+
| Tag         |
+-------------+
| 2020        |
| azulibranco |
| batman      |
| fcp         |
| joker       |
| kitty       |
| lonely      |
| troll       |
+-------------+
8 rows in set (0.00 sec)

Os números distintos de "posts" que têm algum comentário associado (na tabela COMMENT), ordenados de forma decrescente.
```
+------+
| Post |
+------+
|   12 |
|    8 |
|    1 |
+------+
3 rows in set (0.00 sec)
```

O login, e o nome de todos os utilizadores do sexo feminino que tenham um 'y' no seu nome (use o operador LIKE), ordenados por "login".

+----------+---------------+
| Login    | Name          |
+----------+---------------+
| catwoman | Selina Kyle   |
| lady.z   | Lady Stardust |
+----------+---------------+

O número, altura de criação e o texto de todos os "posts" feitos entre as 05:00 e as 18:00, ordenados pela altura de criação descendente.

+-----+---------------------+---------------------------------------------------------------------+
| Num | Creation            | Content                                                             |
+-----+---------------------+---------------------------------------------------------------------+
|  11 | 2020-02-18 17:00:30 | Joker is a busy man, he has no time for social networking.          |
|   3 | 2020-02-13 12:31:59 | Não foi nada de especial, chutei com o pé que estava mais a mão!    |
|   1 | 2020-01-13 12:31:59 | Comigo, ou sem-migo, o Porto vai ser campeão!                       |
|   6 | 2020-01-01 11:23:56 | Joker, you are dead!                                                |
|   5 | 2019-12-24 05:00:00 | I guess nobody is online!                                           |
+-----+---------------------+---------------------------------------------------------------------+
5 rows in set (0.00 sec)

O "login" e a data de nascimento dos 3 utilizadores do sexo masculino mais velhos, ordenados por data de nascimento.

+------------+------------+
| Login      | BirthDate  |
+------------+------------+
| batman     | 1939-01-01 |
| ziggy      | 1972-01-01 |
| joao.pinto | 1976-12-19 |
+------------+------------+

O "login" de utlizadores, a altura de entrada na rede social (valor de Joined), e o tempo há que faziam parte da rede social à data de '2020-01-10' na escala de dias, ordenados por este último valor.

Use TIMESTAMP_DIFF (consulte a documentação MySQL) e yse TIMESTAMPDIFF(...) AS Days para obter o campo (derivado) correspondente chamado Days na resposta.

+------------+------------+------+
| Login      | Joined     | Days |
+------------+------------+------+
| lady.z     | 2020-01-01 |    9 |
| ziggy      | 2019-12-31 |   10 |
| pedro2     | 2019-12-16 |   25 |
| batman     | 2019-12-16 |   25 |
| catwoman   | 2019-12-16 |   25 |
| eva        | 2019-12-15 |   26 |
| mendocas   | 2019-12-11 |   30 |
| pedro      | 2019-12-10 |   31 |
| semedo     | 2019-12-02 |   39 |
| maria      | 2019-12-02 |   39 |
| joao.pinto | 2019-12-01 |   40 |
+------------+------------+------+
11 rows in set (0.00 sec)

De forma análoga, obtenha o "login", data de nascimento, e idade dos 4 utilizadores mais novos à data de '2020-01-10', ordenados por idade.

+----------+------------+------+
| Login    | BirthDate  | Age  |
+----------+------------+------+
| maria    | 2005-11-17 |   14 |
| mendocas | 2002-05-03 |   17 |
| pedro2   | 1993-02-22 |   26 |
| semedo   | 1985-06-02 |   34 |
+----------+------------+------+
4 rows in set (0.00 sec)