PR0303 — Implementació de BD amb Docker

Informació de la pràctica

Camp	Valor
Codi	PR0303
Mòdul	M0372 — Gestió de Bases de Dades
RA	RA3 — DDL: Definició de dades
Durada estimada	4 hores
Modalitat	Individual
Lliurament	Fitxers SQL + informe PDF a Aules
Qualificació	10 punts (rúbrica adjunta)

Objectius

• Implementar l'esquema dissenyat a PR0302 usant DDL.
• Crear la BD als quatre motors (PostgreSQL, MySQL, SQL Server, Oracle) amb contenidors Docker.
• Verificar que totes les restriccions funcionen correctament.
• Crear índexs i vistes addicionals.
• Documentar el procés i les diferències entre motors.

Prerequisit: PR0302

Aquesta pràctica implementa el disseny de la pràctica anterior (PR0302). Si no heu completat PR0302, contacteu el professor per acordar un enunciat alternatiu.

---

Apartat 1 — Implementació a PostgreSQL (4 punts)

1.1 Configuració del contenidor

docker run -d \
  --name postgres-nom-cognom \
  -e POSTGRES_PASSWORD=gbd2025 \
  -e POSTGRES_USER=gbd_user \
  -e POSTGRES_DB=gbd_nom_cognom \
  -p 5432:5432 \
  postgres:16

1.2 Creació de l'esquema complet

Creeu un fitxer schema_postgresql.sql amb:

1. CREATE DATABASE amb les opcions adequades (encoding UTF8).
2. Tots els CREATE TABLE del vostre disseny (mínim 5 taules), en l'ordre correcte (respectant les FK).
3. Totes les restriccions d'integritat: PK, FK amb accions referencials, UNIQUE, NOT NULL, CHECK.
4. Mínim 3 índexs addicionals sobre columnes de cerca freqüent (justificats).
5. Mínim 2 vistes útils per a l'aplicació.

1.3 Verificació de les restriccions

Demostreu que les restriccions funcionen amb sentències que les provin:

-- Exemple de proves per a una BD de reserves:

-- Prova 1: INSERT vàlid
INSERT INTO usuaris (dni, nom, cognoms, email) VALUES ('12345678A', 'Joan', 'Garcia', 'joan@test.com');

-- Prova 2: Violació de UNIQUE (hauria de fallar)
INSERT INTO usuaris (dni, nom, cognoms, email) VALUES ('99999999Z', 'Maria', 'Lopez', 'joan@test.com');

-- Prova 3: Violació de FK (hauria de fallar)
INSERT INTO reserves (id_usuari, id_instal·lacio, hora_inici, hora_fi)
VALUES (999, 1, '10:00', '11:00');

-- Prova 4: Violació de CHECK (hauria de fallar)
INSERT INTO instal·lacions (nom, preu_hora) VALUES ('Pista', -10.00);

Documenteu el resultat (error esperat o èxit) de cadascuna.

---

Apartat 2 — Implementació a MySQL (3 punts)

Adapteu el vostre esquema per a MySQL. Creeu un fitxer schema_mysql.sql que:

1. Crei la base de dades amb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci.
2. Implementi totes les taules amb les diferències sintàctiques de MySQL:
3. AUTO_INCREMENT en lloc de SERIAL.
4. TINYINT(1) per a booleans.
5. ENGINE=InnoDB en cada taula.
6. Dates amb DEFAULT (CURRENT_DATE) (amb parèntesis a MySQL 8.0).
7. Inclogui els mateixos índexs i vistes que a PostgreSQL.

Taula de diferències

Incloeu al vostre informe una taula que compari les diferències sintàctiques entre PostgreSQL i MySQL per a cada element de l'esquema:

Element	PostgreSQL	MySQL
Autonumèric	SERIAL o GENERATED ALWAYS AS IDENTITY	INT AUTO_INCREMENT
Booleà	BOOLEAN	TINYINT(1)
Data actual per defecte	DEFAULT CURRENT_DATE	DEFAULT (CURRENT_DATE)
Text unicode	VARCHAR(n)	VARCHAR(n) CHARACTER SET utf8mb4
...	...	...

---

Apartat 3 — Índexs i vistes avançades (2 punts)

3.1 Índexs amb justificació

Per a cada índex que heu creat, justifiqueu:

• Per quina raó s'ha creat (quina consulta accelera).
• Per que la columna té alta selectivitat (molts valors distints).
• Quin seria el cost d'una consulta sense l'índex vs amb l'índex (ús de EXPLAIN).

Incloeu captures de pantalla de EXPLAIN ANALYZE a PostgreSQL, mostrant la diferència de cost.

3.2 Vistes de seguretat

Creeu una vista que oculti dades sensibles (per exemple, el DNI complet, mostrant només els últims 4 dígits):

-- Exemple per a una BD de clients
CREATE VIEW clients_public AS
SELECT
    id_client,
    nom,
    cognoms,
    '****' || SUBSTRING(dni FROM 6) AS dni_enmascarat,
    email,
    data_alta
FROM clients
WHERE actiu = TRUE;

Demostreu que la vista no exposa les dades sensibles.

---

Apartat 4 — Reflexió i documentació (1 punt)

Responeu les seguents preguntes a l'informe:

1. Quina va ser la dificultat principal en adaptar l'esquema de PostgreSQL a MySQL? Com la vau resoldre?
2. Quins índexs no havieu previst a PR0302 i heu hagut de crear durant la implementació? Per que?
3. Si l'aplicació necessités suportar 1 milió de reserves, quins aspectes del vostre disseny canviaríeu per mantenir el rendiment?

---

Entrega

• schema_postgresql.sql — DDL complet per a PostgreSQL (amb comentaris explicatius).
• schema_mysql.sql — DDL adaptat per a MySQL (amb comentaris on hi ha diferències).
• proves_restriccions.sql — Sentències de prova de restriccions amb comentaris indicant el resultat esperat.
• Informe PDF amb captures de pantalla, taula de diferències i respostes de la reflexió.