Modificant les dades: insert, update, delete

De wikijoan
Salta a la navegació Salta a la cerca

Introducció

És la secció 11 que estem seguint del tutorial:

T1. Previ: regeneració de les dades de la base de dades HR

Dins del docker

Dins del docker tenim el script /etc/HR.sql que és l'estructura i les dades de la nostra base de dades. Per executar aquest script i regenerar les dades ho podem fer de dues maneres: des de la consola, i des de dins del mysql

Entrem a la consola del docker:

$ docker exec -it mysql_m02bd /bin/bash
bash-4.4# 

bash-4.4# cd /etc
bash-4.4# ls HR.sql

El primer que hem de fer és entrar amb un editor de text (nano) al script HR.sql, i comentar les dues primeres línies:

bash-4.4# nano HR.sql

#CREATE DATABASE HR CHARACTER SET utf8 COLLATE utf8_general_ci;
#use HR;

NOTA: el caràcter coixinet (#) és un comentari.

Mira quin és el contingut del script: veuràs fàcilment tots els creates i els inserts.

I ara ja podem executar el script:

# mysql -u alumne -pkeiL2lai HR -f < /etc/HR.sql > /etc/control_errors.log 2>&1

# cat control_errors.log

La segona manera de fer-ho:

$ docker exec -it mysql_m02bd mysql -u alumne -pkeiL2lai HR

mysql> \. /etc/HR.sql
o bé
mysql> source /etc/HR.sql

Per tant, podem potinejar amb les dades tant com vulguem, que sempre podrem regenerar la base de dades. I com a última solució, recorda que sempre et pots tornar a descarregar el docker.

Des del mysql-workbench

Carrega scripts workbench.png

Més avall està explicada la instal·lació del mysql-workbenck. Aquest programa té l'opció File > Open SQL script, amb la qual pots carregar un script que conté comandes SQL. Així doncs, pots executar els scripts de regeneració de les bd en qualsevol moment.

Carpeta que conté tots els scripts de les bases de dades que treballem a classe: Fitxer:Mysql dbs classe.zip

La única cosa que has de tenir en compte (veure la imatge) és que, si el script es troba amb una comanda que dóna error, és necessari que el script continuï i tiri endavant. Per fer-ho tens l'opció Toggle whether execution of SQL script should continue after failed statements. Quan acaba l'execució del script has d'observar el missatge Query completed en comptes de Query interrupted (a baix a l'esquerra).

Aquesta opció és necessària perquè la primera comanda que s'executa (CREATE DATABASE HR...) de fet ja dóna un error. Si la base de dades HR ja existeix, tornar-la a crear dóna un error i s'interrompeix l'execució del script, a no ser que tinguis l'opció de tirar endavant a pesar dels errors.

T2. Modificació de les dades

Insert

INSERT INTO dependents( first_name, last_name, relationship, employee_id) VALUES ( 'Dustin', 'Johnson', 'Child', 178 );

# comprovació:
SELECT * FROM dependents WHERE employee_id = 178;

INSERT INTO dependents( first_name, last_name, relationship, employee_id) VALUES 
( 'Cameron', 'Bell', 'Child', 192 ), 
( 'Michelle', 'Bell', 'Child', 192 );

SELECT * FROM dependents WHERE employee_id = 192;

Per fer aquestes comandes, els usuaris 178 i 192 han d'existir en la taula employees. Si no donaria un error de clau forània (veure més endavant quan parlem de restriccions).

Podem copiar una taula sencera dins d'una altra taula. Per tal de fer-ho necessitem la taula dependents_archive que tingui la mateixa estructura que dependents.

create table dependents_archive(
dependent_id int,
first_name varchar(50),
last_name varchar(50),
relationship varchar(50),
employee_id int
);

I ara ja podem omplir tota la taula, que funcionaria com una còpia de seguretat:

INSERT INTO dependents_archive SELECT * FROM dependents;
Query OK, 33 rows affected (0.02 sec)
Records: 33  Duplicates: 0  Warnings: 0

Update

UPDATE employees SET last_name = 'Lopez' WHERE employee_id = 192;

# comprovació:
SELECT employee_id, first_name, last_name FROM employees WHERE employee_id = 192;

UPDATE dependents SET last_name = 'Lopez' WHERE employee_id = 192;

# fem un update amb una subquery

UPDATE dependents SET last_name =
( SELECT last_name FROM employees WHERE employee_id = dependents.employee_id);

Delete

# comprovació:
SELECT count(*) FROM dependents WHERE dependent_id = 16;

DELETE FROM dependents WHERE dependent_id = 16;

# comprovació:
SELECT count(*) FROM dependents WHERE dependent_id = 16;


DELETE FROM dependents WHERE employee_id IN(100 , 101, 102);

Tenim un empleat amb id=192, i dos fills d'aquest empleat. Primer esborrem l'empleat:

DELETE FROM employees WHERE employee_id = 192;

Si ara borrem els seus fills, veurem que surt: 0 rows affected. No s'han esborrat, perquè ja s'havien esborrat quan hem esborrat el pare. D'aquesta manera es manté la integritat de les dades.

DELETE FROM dependents WHERE employee_id = 192;

Cançó de Youtube

No te olvides de poner el Where en el Delete From (Una canción para programadores)

T3. Transaccions bàsiques. Insercions amb apòstrofs

En aquest apartat aprendrem dues coses:

  1. fer petites transaccions per tal de fer proves modificant les dades, i després ho tornem a deixar tot en el seu estat original. Farem servir 'start transaction; i al final farem un rollback;.
  2. com introduïm dades que contenen apòstrofs?. Farem servir preferentment el doble apòstrof (), que no és el mateix que les cometes dobles (").

Anem a veure un exemple. Treballem amb la base de dades de municipis, i comencem fent un start transaction.

mysql> use municipis;
mysql> start transaction;

mysql> select max(id_mun) from municipis;
+-------------+
| max(id_mun) |
+-------------+
|        8131 |
+-------------+

# no funciona:
#insert into municipis(id_mun,id_prov,municipi) values (8132, 8,'L'Hospitalet');

# sí que funciona, dues maneres de fer-ho:
insert into municipis(id_mun,id_prov,municipi) values (8132, 8,'L''Hospitalet');
insert into municipis(id_mun,id_prov,municipi) values (8133, 8,'L\'Hospitalet');

# comprovació

mysql> select * from municipis where id_mun>=8132;
+--------+---------+---------+--------------+------------+-----------+
| id_mun | cod_ine | id_prov | municipi     | superficie | habitants |
+--------+---------+---------+--------------+------------+-----------+
|   8132 |    NULL |       8 | L'Hospitalet |       NULL |      NULL |
|   8133 |    NULL |       8 | L'Hospitalet |       NULL |      NULL |
+--------+---------+---------+--------------+------------+-----------+

# fem un rollback per tal de desfer els canvis:
mysql> rollback;

# comprovem que s'han desfet els canvis:
mysql> select max(id_mun) from municipis;
+-------------+
| max(id_mun) |
+-------------+
|        8131 |
+-------------+

T4. Restrictions: clau primària, clau forànea, NULL, UNIQUE, CHECK

Restricció de clau primària

De la base de dades HR: fixem-nos amb la definició de la taula jobs:

CREATE TABLE jobs (
	job_id INT (11) AUTO_INCREMENT PRIMARY KEY,
	job_title VARCHAR (35) NOT NULL,
	min_salary DECIMAL (8, 2) DEFAULT NULL,
	max_salary DECIMAL (8, 2) DEFAULT NULL
);
insert into jobs values(1,'programador', 2000, 5000);
ERROR 1062 (23000): Duplicate entry '1' for key 'jobs.PRIMARY'

insert into jobs values(20,'programador', 2000, 5000);
Query OK, 1 row affected (0.01 sec)

Restricció de clau forànea

De la base de dades HR: fixem-nos amb la definició de la taula employees:

CREATE TABLE employees (
	employee_id INT (11) AUTO_INCREMENT PRIMARY KEY,
	first_name VARCHAR (20) DEFAULT NULL,
	last_name VARCHAR (25) NOT NULL,
	email VARCHAR (100) NOT NULL,
	phone_number VARCHAR (20) DEFAULT NULL,
	hire_date DATE NOT NULL,
	job_id INT (11) NOT NULL,
	salary DECIMAL (8, 2) NOT NULL,
	manager_id INT (11) DEFAULT NULL,
	department_id INT (11) DEFAULT NULL,
	FOREIGN KEY (job_id) REFERENCES jobs (job_id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (department_id) REFERENCES departments (department_id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (manager_id) REFERENCES employees (employee_id)
);
mysql> select employee_id,first_name,job_id from employees;
...
|         206 | William     |      1 
...

mysql> update employees set job_id=20 where employee_id=206;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update employees set job_id=25 where employee_id=206;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`HR`.`employees`, CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`) ON DELETE CASCADE ON UPDATE CASCADE)

mysql> update employees set job_id=1 where employee_id=206;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Restricció de NULL

De la taula job, està clar que la clau primaria és job_id. Si ens fixem en la definició de la taula, el job_title no pot ser NUL, i en canvi els salaris (màxim i mínim) sí que poden ser nuls.

mysql> update jobs set max_salary=6000 where job_id=20;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update jobs set min_salary=NULL where job_id=20;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update jobs set job_title=NULL where job_id=20;
ERROR 1048 (23000): Column 'job_title' cannot be null

Restricció de UNIQUE

Per trobar una restricció UNIQUE hem d'anar a la base de dades sakila. En el script sakila-schema.sql trobem la definició de la taula store:

CREATE TABLE store (
  store_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  manager_staff_id TINYINT UNSIGNED NOT NULL,
  address_id SMALLINT UNSIGNED NOT NULL,
  last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (store_id),
  UNIQUE KEY idx_unique_manager (manager_staff_id),
  KEY idx_fk_address_id (address_id),
  CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_store_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Fixem-nos en les dues línies:

...
  manager_staff_id TINYINT UNSIGNED NOT NULL,
...
  UNIQUE KEY idx_unique_manager (manager_staff_id)
...

S'hagués pogut posar de forma més simplificada en una sola línia:

  manager_staff_id TINYINT UNSIGNED NOT NULL UNIQUE,

Aquesta restricció ens està dient que el manager_staff_id no es pot repetir en aquesta taula. És a dir, que no pot haver-hi dues botigues amb el mateix manàger.

Anem a fer saltar aquesta restricció:

Només hi ha dues botigues i només hi ha dues persones en el staff:

mysql> select store_id, manager_staff_id from store;
+----------+------------------+
| store_id | manager_staff_id |
+----------+------------------+
|        1 |                1 |
|        2 |                2 |
+----------+------------------+

mysql> select staff_id, first_name from staff;
+----------+------------+
| staff_id | first_name |
+----------+------------+
|        1 | Mike       |
|        2 | Jon        |
+----------+------------+

No podem fer que el manager_staff_id=1 sigui el manager de la botiga 1:

mysql> update store set manager_staff_id=1 where store_id=2;
ERROR 1062 (23000): Duplicate entry '1' for key 'store.idx_unique_manager'

Restricció de CHECK

Treballem amb la base de dades HR, i creem la taula parts amb 4 restriccions tipus CHECK:

DROP TABLE IF EXISTS parts;

CREATE TABLE parts (
    part_no VARCHAR(18) PRIMARY KEY,
    description VARCHAR(40),
	type VARCHAR(15) NULL CHECK (type IN ('hardware','software','electronics')),
    cost DECIMAL(10,2) NOT NULL CHECK (cost >= 0),
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
	CONSTRAINT parts_chk_price_gt_cost CHECK(price >= cost)
);

Veiem que hi ha 4 restriccions:

  • type pot ser un d'aquests valors: hardware,software,electronics
  • cost > 0 (ha de ser un valor positiu)
  • price > 0 (ha de ser un valor positiu)
  • una altra restricció: el preu ha de ser més gran que el cost (price >= cost)
mysql> desc parts;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| part_no     | varchar(18)   | NO   | PRI | NULL    |       |
| description | varchar(40)   | YES  |     | NULL    |       |
| type        | varchar(15)   | YES  |     | NULL    |       |
| cost        | decimal(10,2) | NO   |     | NULL    |       |
| price       | decimal(10,2) | NO   |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+

o bé:

mysql> SHOW CREATE TABLE parts;

...
CREATE TABLE `parts` (
  `part_no` varchar(18) NOT NULL,
  `description` varchar(40) DEFAULT NULL,
  `type` varchar(15) DEFAULT NULL,
  `cost` decimal(10,2) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`part_no`),
  CONSTRAINT `parts_chk_1` CHECK ((`type` in (_utf8mb4'hardware',_utf8mb4'software',_utf8mb4'electronics'))),
  CONSTRAINT `parts_chk_2` CHECK ((`cost` >= 0)),
  CONSTRAINT `parts_chk_3` CHECK ((`price` >= 0)),
  CONSTRAINT `parts_chk_price_gt_cost` CHECK ((`price` >= `cost`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
...

Veiem com es violen aquestes restriccions:

mysql> INSERT INTO parts(part_no, description,cost,price) VALUES('A-001','Cooler',200,100);
ERROR 3819 (HY000): Check constraint 'parts_chk_price_gt_cost' is violated.

mysql> INSERT INTO parts(part_no, description,cost,price) VALUES('A-001','Cooler',100,-200);
ERROR 3819 (HY000): Check constraint 'parts_chk_3' is violated.

mysql> INSERT INTO parts(part_no, description,cost,price) VALUES('A-001','Cooler',-100,200);
ERROR 3819 (HY000): Check constraint 'parts_chk_2' is violated.

mysql> INSERT INTO parts(part_no, description,type,cost,price) VALUES('A-001','Cooler','tipus no vàlid',100,200);
ERROR 3819 (HY000): Check constraint 'parts_chk_1' is violated.

I finalment una inserció correcta:

mysql> INSERT INTO parts(part_no, description,type,cost,price) VALUES('A-001','Cooler','hardware',100,200);

mysql> select * from parts;
+---------+-------------+----------+--------+--------+
| part_no | description | type     | cost   | price  |
+---------+-------------+----------+--------+--------+
| A-001   | Cooler      | hardware | 100.00 | 200.00 |
+---------+-------------+----------+--------+--------+

En la base de dades vestuari (script vestuari_v5.sql) tenim un exemple de restricció CHECK:

CREATE TABLE LOCALITZACIO (
	id_loc smallint PRIMARY KEY,
	loc VARCHAR(50) NOT NULL,
	tipus VARCHAR(20) NULL CHECK (TIPUS IN ('armari', 'capsa', 'guarda-roba', 'penjador', 'sabater', 'altell')),
	hab CHAR(5) NOT NULL CHECK (hab IN ('HAB-1','HAB-2','HAB-3','HAB-4','HAB-5')),
	notes VARCHAR(255) NULL
);

T5. Restrictions en cascada

vestuari: base de dades sense restriccions en cascada

Fixa't en aquestes dues taules que pots veure en el script /etc/vestuari.sql:

#OBRA (id_obra, obra, year, notes)
CREATE TABLE OBRA (
	id_obra smallint PRIMARY KEY,
	obra VARCHAR(50) NOT NULL,
	year SMALLINT NOT NULL,
	notes VARCHAR(255)
);

#PERSONATGE (id_pers, pers, rol, id_obra (FK))
CREATE TABLE PERSONATGE (
	id_pers smallint PRIMARY KEY,
	pers VARCHAR(50) NOT NULL,
	rol VARCHAR(50) NULL,
	id_obra SMALLINT NOT NULL,
	FOREIGN KEY (id_obra) REFERENCES OBRA(id_obra)
);

Miro quines obres tenim:

mysql> select * from OBRA;
+---------+----------------------------+------+---------------------------------------------+
| id_obra | obra                       | year | notes                                       |
+---------+----------------------------+------+---------------------------------------------+
|       1 | La Caputxeta Vermella 2018 | 2018 | versió clàssica                             |
|       2 | La Caputxeta Vermella 2021 | 2021 | versió moderna i ambientada a l'actualitat  |
+---------+----------------------------+------+---------------------------------------------+

Vull eliminar tota referència a l'obra 1. Però haurem de mantenir la integritat:

DELETE FROM OBRA WHERE id_obra=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`vestuari`.`PERSONATGE`, CONSTRAINT `PERSONATGE_ibfk_1` FOREIGN KEY (`id_obra`) REFERENCES `OBRA` (`id_obra`))

No ho podem fer perquè tenim personatges en aquesta obra. Hem d'eliminar el vestuari d'aquesta obra:

DELETE FROM PERSONATGE WHERE id_obra=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`vestuari`.`VEST_PERS`, CONSTRAINT `VEST_PERS_ibfk_2` FOREIGN KEY (`id_pers`) REFERENCES `PERSONATGE` (`id_pers`))

No ho podem fer perquè aquests personatges estan referenciats a la taula VEST_PERS. I alhora, VEST_PERS fa referència a la taula vestuari. Per tant, ho hauré de fer manualment, i per ordre.

Quines són les referències del vestuari de l'obra id_obra=1?

SELECT p.id_obra, ref FROM PERSONATGE p
INNER JOIN VEST_PERS USING(id_pers)
WHERE p.id_obra=1;

+---------+-----+
| id_obra | ref |
+---------+-----+
|       1 |   1 |
|       1 |   2 |
|       1 |   3 |
|       1 |   4 |
|       1 |   5 |
|       1 |   6 |
|       1 |   7 |
|       1 |   8 |
|       1 |   9 |
|       1 |  10 |
|       1 |  11 |
|       1 |  12 |
|       1 |  13 |
|       1 |  14 |
|       1 |  15 |
|       1 |  16 |
|       1 |  17 |
|       1 |  18 |
|       1 |  19 |
+---------+-----+

I per tant,

DELETE FROM VEST_PERS WHERE ref>=1 AND ref<=19;
Query OK, 19 rows affected (0.01 sec)


DELETE FROM PERSONATGE WHERE id_obra=1;
Query OK, 5 rows affected (0.02 sec)

(ara sí que funciona)

DELETE FROM OBRA WHERE id_obra=1;
Query OK, 1 row affected (0.01 sec)

(ara sí que funciona)

Fixem-nos que el vestuari que feia servir en l'obra 1 no l'he d'eliminar (aquest vestuari es podia fer servir en d'altres obres).

Per tant, si vull eliminar totes les dades de la base de dades, ho hauré de fer manualment i per ordre:

DROP TABLE IF EXISTS VEST_PERS;
DROP TABLE IF EXISTS VESTUARI;
DROP TABLE IF EXISTS PERSONATGE;
DROP TABLE IF EXISTS OBRA;

DROP TABLE IF EXISTS LOCALITZACIO;
DROP TABLE IF EXISTS AMBIENTACIO;
DROP TABLE IF EXISTS CATEGORIA;

Per exemple, no podria començar per CATEGORIA si hi ha obres que depenen d'aquesta categoria. No puc esborrar obra si hi ha personatges que depenen de l'obra. No puc esborrar personatge si hi ha vesturi que està associat al personatge.

Per tant, la primera taula que s'ha d'esborrar és VEST_PERS, que desassocia VESTUARI i PERSONTAGE.

Per fer tot això de forma automàtica tenim les opcions en 'CASCADE'. Però s'ha d'anar en compte perquè com el seu nom inidica fa els esborrats en cascada: si esborro una obra, esborrarà tots els personatges d'aquella obra, esborrarà a la taula VEST_PERS tota referència al personatge.

HR: base de dades amb restriccions en cascada

Fixa't en aquestes dues taules que pots veure en el script /etc/HR.sql:

CREATE TABLE employees (
	employee_id INT (11) AUTO_INCREMENT PRIMARY KEY,
	first_name VARCHAR (20) DEFAULT NULL,
	last_name VARCHAR (25) NOT NULL,
	email VARCHAR (100) NOT NULL,
	phone_number VARCHAR (20) DEFAULT NULL,
	hire_date DATE NOT NULL,
	job_id INT (11) NOT NULL,
	salary DECIMAL (8, 2) NOT NULL,
	manager_id INT (11) DEFAULT NULL,
	department_id INT (11) DEFAULT NULL,
	FOREIGN KEY (job_id) REFERENCES jobs (job_id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (department_id) REFERENCES departments (department_id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (manager_id) REFERENCES employees (employee_id)
);

CREATE TABLE dependents (
	dependent_id INT (11) AUTO_INCREMENT PRIMARY KEY,
	first_name VARCHAR (50) NOT NULL,
	last_name VARCHAR (50) NOT NULL,
	relationship VARCHAR (25) NOT NULL,
	employee_id INT (11) NOT NULL,
	FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE CASCADE ON UPDATE CASCADE
);

Anem a veure els empleats i els seus depenents (amb una inner join):

select e.employee_id, e.first_name, e.last_name, d.dependent_id, d.first_name, d.last_name, d.employee_id relationship  FROM
employees e INNER JOIN dependents d USING (employee_id)
order by e.employee_id;

...
|         203 | Susan       | Mavris     |           13 | Uma        | Mavris     |          203 |
|         204 | Hermann     | Baer       |           21 | Kirsten    | Baer       |          204 |
|         205 | Shelley     | Higgins    |            2 | Nick       | Higgins    |          205 |
|         206 | William     | Gietz      |            1 | Penelope   | Gietz      |          206 |
...

Veiem que la gent només té un fill. No passa res.

  • Puc canviar el id de l'empleat 206 (a 207), i també es canviarà a dependents, de manera que la integritat de la relació encara es mantindrà.
  • Puc eliminar l'empleat 205 o el 204, i també s'eliminarà el seu fill, de manera que la integritat de la relació encara es mantindrà.
update employees set employee_id=207 where employee_id=206;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
delete from employees where employee_id=205;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`HR`.`employees`, CONSTRAINT `employees_ibfk_3` FOREIGN KEY (`manager_id`) REFERENCES `employees` (`employee_id`))

No puc eliminar aquest empleat perquè precisament és manager d'altres empleats, i em salataria la integritat.

mysql> delete from employees where employee_id=204;
Query OK, 1 row affected (0.01 sec)

Tornem a fer la select:

|         203 | Susan       | Mavris     |           13 | Uma        | Mavris     |          203 |
|         205 | Shelley     | Higgins    |            2 | Nick       | Higgins    |          205 |
|         207 | William     | Gietz      |            1 | Penelope   | Gietz      |          207 |

Veiem com el empleat 204 no existeix (i també ha desaparegut el seu fill, el 21). I veiem també com l'empleat 206 ha passat a ser el 207 i la seva filla Penelope continua estan ben relacionada.

Per tant, les dades es poden actualitzar en cascada si així ho hem definit en les taules.

municipis: esborrat en cascada

DROP TABLE IF EXISTS municipis;
DROP TABLE IF EXISTS provincies;
DROP TABLE IF EXISTS comunitats;

CREATE TABLE comunitats(
id_com smallint PRIMARY KEY,
comunitat varchar(80) not null,
abr_com varchar(4)
);

CREATE TABLE provincies(
id_prov smallint PRIMARY KEY,
provincia varchar(30) not null,
id_com  smallint,
FOREIGN KEY (id_com) REFERENCES comunitats(id_com) ON DELETE CASCADE ON UPDATE CASCADE
);

create table municipis(
id_mun smallint primary key,
id_prov smallint,
municipi varchar(120),
superficie smallint,
habitants smallint,
FOREIGN KEY (id_prov) REFERENCES provincies(id_prov) ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO comunitats VALUES(1,'Catalunya','CAT');
INSERT INTO comunitats VALUES(2,'Aragón','ARG');
...

INSERT INTO provincies VALUES(1,'Álava',7);
INSERT INTO provincies VALUES(2,'Albacete',14);
INSERT INTO provincies VALUES(3,'Alacant',3);
...

INSERT INTO municipis values (1,1,'Agurain',NULL,NULL);
INSERT INTO municipis values (2,1,'Alegría-Dulantzi',NULL,NULL);
INSERT INTO municipis values (3,1,'Amurrio',NULL,NULL);
INSERT INTO municipis values (4,1,'Añana',NULL,NULL);
INSERT INTO municipis values (5,1,'Aramaio',NULL,NULL);
INSERT INTO municipis values (6,1,'Armiñón',NULL,NULL);
...

Tenim 17 comunitats, 52 províncies i més de 8000 municipis.

Si esborrem les comunitats, provoca que s'esborrin totes les províncies de les comunitats. I a l'hora, això provoca que s'esborrin tots els municipis que pengen d'aquestes províncies. El resultat: esborrem tota la base de dades:

mysql> delete from comunitats;
Query OK, 19 rows affected (0,02 sec)

mysql> SELECT COUNT(*) FROM municipis;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

Això és bo perquè d'aquesta manera ens assegurem la integritat de la informació: no pot haver municipis assignats a una província que no existeix; no pot haver províncies assignades a una comunitat que no existeix.

Bases de dades: en cascada o sense cascada

Les bases de dades que tenim d'exemple, classificades segons que l'actualització sigui en cascada o no, són:

Actualització en cascada:

  • langtrainer
  • HR
  • sakila
  • municipis

Actualització sense cascada:

  • bikeshop
  • classicmodels
  • empresa
  • englishresources
  • northwind
  • vestuari

Aquesta informació la podem veure en la definició de les taules dels scripts .sql que pots trobar en la carpeta /etc/ del docker.

T6. mysql-workbench

El mysql-workbench és una interfície gràfica per treballar amb bases de dades del MySQL. Té moltes opcions. Si volem fer servir una interfície gràfica que serveixi per diferents SGBD (MySQL, PostgreSQL, Oracle) podem fer servir TablePlus, encara que la interfície gràfica no sigui molt vistosa.

Instal·lació mysql-workbench

Ho tenim documentat aquí:

Configuració mysql-workbench

Els usuaris de Windows hauran d'haver arrencat el docker amb la següent comanda:

$ docker run --name=mysql_m02bd -d -p 127.0.0.1:3306:3306 joanillo/mysql_m02bd:5.0

Com bé saps a aquestes altures, això no cal fer-ho cada vegada, sinó només la primera vegada. En tot cas, si no et funciona el docker és perquè el deus tenir aturat, i això ho soluciones fent un start.

L'anterior comanda vol dir que el docker exposa la IP 127.0.0.1 (és a dir, el tractem com si fos una màquina local); i el port intern del mysql del docker (el 3306) l'exposem a la màquina local amb el mateix port (3306). Això funcionarà sempre i quan en la màquina local no hi hagi cap aplicació que utilitzi el port 3306 (és a dir, que no tinguem instal·lat el mysql localment). Si fos el cas, sempre pots utilitzar un altre port lliure, i posar, per exemple: 3307:3306.

En el mysql-workbench, crea la connexió municipis_docker amb la següent configuració (windows):

hostname: 127.0.0.1
Port: 3306
username: alumne
password: keiL2lai (store in keychain)
default schema: municipis

i pots fer el mateix amb cadascuna de les bases de dades (com es demana en el qüestionari).

mysql-workbench: mostrar el mapa de relacions de les taules

Diagrama municipis.png

Un cop hagis entrat a la base de dades de municipis, és molt fàcil el procediment a seguir:

  • Database > Reverse Engineer
  • selecciona la connexió emmagatzemada > Next
  • T'apareixen totes les bases de dades. És normal, estem connectats amb l'usuari alumne, i aquest usuari té permisos sobre totes les bases de dades amb què estem treballant.
  • Selecciona la base de dades municipis > Next > Execute > Next > Close
  • T'apareixeran totes les taules, un pèl desordenades, i amb les fletxes que relacionen les claus forànees entre les taules.

Ja pots ordenar les taules de manera que no es creuin les línies de relacions, i que quedi clar les relacions entre les taules.



creat per Joan Quintana Compte, desembre 2021