Salta el contingut

PR0305 — Manipulació de dades amb Docker

Informació de la pràctica

Camp Valor
Codi PR0305
mòdul M0372 — Gestió de Bases de Dades
RA RA5 — DML: Manipulació de dades
Durada estimada 4 hores
Modalitat Individual
Lliurament Fitxers SQL + informe PDF a Moodle
Qualificació 10 punts (rúbrica adjunta)

Objectius

  • Poblar la BD de pràctiques amb dades coherents usant INSERT de forma massiva.
  • Aplicar UPDATE amb condicions de filtratge correctes.
  • Aplicar DELETE respectant la integritat referencial.
  • Gestionar operacions crítiques usant transaccions explícites.
  • Demostrar el comportament del ROLLBACK i dels SAVEPOINTs.

Apartat 1 — Carrega inicial de dades (3 punts)

1.1 Carrega de dades de referència

Creeu un fitxer inserts_dades_referencia.sql amb les dades bàsiques de la vostra BD (les que no canvien habitualment: categories, tipus, catàleg de productes o instal·lacions, etc.). Inseriu:

  • Mínim 10 files per a cada "taula mestra".
  • Useu INSERT multi-fila (una sola instruccio per a totes les files).
  • Les dades han de ser coherents i realistes (no "test1", "test2", etc.).

1.2 Carrega de dades transaccionals

Creeu un fitxer inserts_dades_transaccionals.sql amb:

  • Mínim 20 usuaris amb dades realistes.
  • Mínim 50 reserves (distribuïdes en el temps, en diversos estats).
  • Mínim 5 pagaments completats.

Per generar moltes files, podeu usar la funció generate_series de PostgreSQL:

-- Exemple: generar 20 usuaris de prova
INSERT INTO usuaris (nom, cognoms, email, dni, data_registre)
SELECT
    'Usuari_' || i,
    'Cognom_' || i,
    'usuari' || i || '@example.com',
    LPAD(i::TEXT, 8, '0') || 'Z',
    CURRENT_DATE - (i || ' days')::INTERVAL
FROM generate_series(1, 20) AS i;

Apartat 2 — Actualitzacions (3 punts)

Creeu un fitxer updates.sql amb les seguents operacions:

  1. Pujada de preus: Augmenteu el preu de les instal·lacions de tipus "padel" un 8%. Verificeu antes i després amb SELECT.

  2. Canvi d'estat: Marqueu com a "cancel·lades" totes les reserves de l'estat "pendent" que són de fa més de 30 dies.

  3. Actualització massiva: Un client ha canviat el seu email. Actualitzeu-lo per a l'usuari amb DNI 'XXXXXXXX'. Assegureu-vos que el canvi es reflecteix correctament.

  4. Correcció d'errors: Una de les instal·lacions s'ha registrat per error com "inactiva" quan hauria d'estar "activa". Corregiu-ho amb un UPDATE.

  5. Actualització condicional: Per a totes les reserves de l'any anterior que estan en estat "confirmada", afegiu un 5% al preu (si teniu un camp de preu calculat o a la taula de pagaments).

Per a cada UPDATE, incloeu: - El SELECT previ que mostra les files afectades. - L'UPDATE pròpiament dit. - El SELECT posterior que verifica el canvi.

Apartat 3 — Eliminació de dades (2 punts)

Creeu un fitxer deletes.sql:

  1. Eliminar reserves antigues: Elimineu les reserves en estat "cancel·lada" de fa més de 6 mesos. Verifiqueu l'ordre correcte d'eliminació respectant les FK.

  2. Eliminar usuari inactiu: Elimineu un usuari que no te cap reserva associada. Intenteu eliminar-ne un que SÍ te reserves i documenteu el missatge d'error.

  3. Usar transacció per a eliminació segura: Englobeu una sèrie d'eliminació en una transacció. Feu ROLLBACK després de veure els resultats intermedis, i després COMMIT.

Apartat 4 — Transaccions (2 punts)

Creeu un fitxer transaccions.sql amb els seguents escenaris:

Escenari 1: Reserva completa

Implementeu una transacció que crei una nova reserva de forma atòmica:

BEGIN;

-- 1. Crear la reserva
INSERT INTO reserves (id_usuari, id_instal·lacio, data_reserva, hora_inici, hora_fi)
VALUES (1, 2, CURRENT_DATE + 7, '10:00', '11:00');

-- 2. Crear el pagament associat
INSERT INTO pagaments (id_reserva, import, metode, estat)
VALUES (LASTVAL(), 15.00, 'targeta', 'pendent');

-- 3. Verificar que tot és correcte
SELECT r.id_reserva, u.nom, i.nom AS instal·lacio, p.import
FROM reserves r
JOIN usuaris u ON r.id_usuari = u.id_usuari
JOIN instal·lacions i ON r.id_instal·lacio = i.id_instal·lacio
JOIN pagaments p ON p.id_reserva = r.id_reserva
WHERE r.id_reserva = LASTVAL();

COMMIT;

Escenari 2: ROLLBACK per error

Demostreu que el ROLLBACK funciona correctament:

BEGIN;

-- Operació 1: OK
UPDATE usuaris SET punts_fidelitat = punts_fidelitat + 10 WHERE id_usuari = 1;
-- Veure l'estat intermedi
SELECT id_usuari, punts_fidelitat FROM usuaris WHERE id_usuari = 1;

-- Simular un error
-- Intentar inserir a una taula inexistent o violar una restricció
INSERT INTO taula_que_no_existeix VALUES (1);

-- El SGBD hauria de forçar el ROLLBACK automàticament (PostgreSQL)
-- O podeu fer-lo explícitament:
ROLLBACK;

-- Verificar que els punts NO han canviat
SELECT id_usuari, punts_fidelitat FROM usuaris WHERE id_usuari = 1;

Escenari 3: SAVEPOINT

BEGIN;
SAVEPOINT inici;

-- Operació 1
INSERT INTO reserves (...) VALUES (...);
SAVEPOINT despres_reserva;

-- Operació 2 que potser falla
INSERT INTO pagaments (...) VALUES (...);

-- Si pagament falla, tornar només al punt de la reserva
ROLLBACK TO SAVEPOINT despres_reserva;

-- Intentar el pagament de nou amb dades corregides
INSERT INTO pagaments (...) VALUES (...);

COMMIT;

Entrega

  • inserts_dades_referencia.sql
  • inserts_dades_transaccionals.sql
  • updates.sql (amb SELECT de verificació abans i després)
  • deletes.sql (amb SELECT de verificació i gestió d'errors FK)
  • transaccions.sql (amb els 3 escenaris)
  • Informe PDF amb captures de pantalla dels resultats clau i respostes de reflexió.