El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència
HOME Els meus cursos Finalitzats
◄ Anterior
◄

Transformació de Diagrames ER a Esquemes Relacionals

Següent ►
►

A la lliçó anterior vam tancar el diagrama ER de BiblioRed ampliat: vint-i-una entitats, les seves relacions amb cardinalitat i participació, una jerarquia total i disjunta de materials, nou decisions registrades i una validació contra les dotze consultes del negoci. És un model conceptual complet i continua sense poder-se executar.

Aquesta lliçó cobreix el pont. La transformació d'un model ER a un esquema relacional és, a diferència de gairebé tota la resta en disseny, un algorisme: un conjunt de regles mecàniques que, aplicades en ordre, produeixen les taules. Hi ha deu regles. Vuit són deterministes —donat el diagrama, només hi ha una resposta correcta—, i dues (la 6, relacions 1:1, i la 10, jerarquies) presenten alternatives reals entre les quals cal triar amb criteri. Precisament per això són les dues que més espai ocupen.

L'entregable és l'script CREATE TABLE complet de l'ampliació de BiblioRed, encaixant amb les set taules existents, amb les seves claus foranes i les accions referencials raonades segons el que vam aprendre a 02-06. Un advertiment des d'ara, perquè és el ganxo de la lliçó següent: els tipus de dades que farem servir aquí són provisionals. VARCHAR(200), INTEGER, NUMERIC posats per defecte perquè l'script funcioni. La lliçó 04-04 els revisa un a un i hi afegeix el catàleg complet de restriccions.

Contingut

  1. Què és l'algorisme de transformació i què no resol
  2. Regla 1 — Entitat forta → taula
  3. Regla 2 — Atribut compost → columnes simples
  4. Regla 3 — Atribut multivaluat → taula a part
  5. Regla 4 — Atribut derivat → no es desa
  6. Regla 5 — Relació 1:N → clau forana al costat N
  7. Regla 6 — Relació 1:1 → tres opcions
  8. Regla 7 — Relació N:M → taula d'unió
  9. Regla 8 — Entitat feble → clau primària composta
  10. Regla 9 — Relació ternària → taula amb tres claus foranes
  11. Regla 10 — Jerarquia de generalització → tres estratègies
  12. Entregable: l'script complet de BiblioRed ampliat
  13. Revisió posterior: validar l'esquema resultant
  14. Errors Habituals i Consells
  15. Exercicis
  16. Conclusió

  1. Què és l'algorisme de transformació i què no resol

L'algorisme s'aplica en un ordre concret, i l'ordre importa perquè cada pas depèn que l'anterior hagi creat les taules a les quals apuntar:

flowchart TD
    A["1. Entitats fortes → taules amb la seva PK"]
    B["2-4. Atributs: compostos, multivaluats, derivats"]
    C["8. Entitats febles → PK composta amb la del propietari"]
    D["5. Relacions 1:N → FK al costat N"]
    E["6. Relacions 1:1 → decidir entre tres opcions"]
    F["7. Relacions N:M → taula d unio"]
    G["9. Relacions ternaries → taula amb tres FK"]
    H["10. Jerarquies → triar estrategia"]
    I["Revisio: consultes, taules sense clau, orfes"]
    A --> B --> C --> D --> E --> F --> G --> H --> I

El que l'algorisme garanteix: un esquema relacional correcte, sense pèrdua d'informació i sense relacions inventades. Si el diagrama era fidel al domini, l'esquema també ho serà.

El que l'algorisme NO resol —i convé tenir-ho clar per no esperar màgia—:

No resol On es resol
Un diagrama mal fet. Si la cardinalitat era incorrecta, la FK acabarà al costat equivocat Revisant el diagrama (04-02)
L'elecció de tipus de dades concrets Lliçó 04-04
Les regles de negoci que no són estructurals (RN1–RN10) Lliçó 04-04
Les anomalies de redundància que quedessin al disseny Mòdul 5, normalització
El rendiment Lliçó 06-03, índexs

Un matís important: el resultat d'aplicar l'algorisme a un diagrama ben fet acostuma a estar ja en tercera forma normal. No per casualitat: pensar en entitats i relacions és, informalment, aplicar els mateixos principis que la normalització formalitza. Al mòdul 5 ho verificarem amb instrumental.

  1. Regla 1 — Entitat forta → taula

Regla 1. Cada entitat forta es converteix en una taula. Els seus atributs simples es converteixen en columnes. El seu identificador es converteix en la clau primària.

És la regla més directa i la que crea l'esquelet. Aplicada a sales, tipus_esdeveniment, esdeveniments i ponents (les claus foranes encara no; arriben a la regla 5):

CREATE TABLE tipus_esdeveniment (
    tipus_esdeveniment_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
    codi                  VARCHAR(30)  NOT NULL,
    nom                   VARCHAR(80)  NOT NULL,
    descripcio            VARCHAR(500),
    durada_estandard_min  INTEGER,
    CONSTRAINT pk_tipus_esdeveniment PRIMARY KEY (tipus_esdeveniment_id),
    CONSTRAINT uq_tipus_esdeveniment_codi UNIQUE (codi)
);

Tres decisions ja preses i aplicades aquí:

  1. Clau subrogada tipus_esdeveniment_id, segons la regla que vam fixar a l'apartat 10 de 04-01: PK subrogada en tota entitat forta.
  2. UNIQUE sobre la clau natural codi. Això és el que impedeix que existeixin dues files 'taller'. Sense aquest UNIQUE, la clau subrogada hauria destruït una garantia que el model conceptual sí que donava.
  3. Restriccions anomenades (pk_, uq_), pel que veurem a 04-04 sobre els missatges d'error.

I ponents, amb la mateixa estructura:

CREATE TABLE ponents (
    ponent_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
    nom       VARCHAR(60)  NOT NULL,
    cognoms   VARCHAR(80)  NOT NULL,
    email     VARCHAR(120),
    biografia VARCHAR(1000),
    extern    BOOLEAN      NOT NULL DEFAULT TRUE,
    CONSTRAINT pk_ponents PRIMARY KEY (ponent_id),
    CONSTRAINT uq_ponents_email UNIQUE (email)
);

Observa que email no és NOT NULL però sí UNIQUE. És una combinació deliberada: hi ha ponents externs dels quals només es té el telèfon, però dos ponents no poden compartir adreça. Que PostgreSQL permeti diversos NULL en una columna UNIQUE és el que fa viable aquesta combinació, i ho explicarem a fons a 04-04.

  1. Regla 2 — Atribut compost → columnes simples

Regla 2. Un atribut compost es descompon: cada component és una columna. L'atribut compost en si desapareix; no en queda rastre a l'esquema.

R13 demanava descompondre l'adreça de les sucursals. La taula existent té:

-- ABANS
sucursals (sucursal_id, nom, adreca, telefon, data_obertura)

La transformació:

ALTER TABLE sucursals ADD COLUMN adr_carrer      VARCHAR(120);
ALTER TABLE sucursals ADD COLUMN adr_numero      VARCHAR(10);
ALTER TABLE sucursals ADD COLUMN adr_codi_postal VARCHAR(5);
ALTER TABLE sucursals ADD COLUMN adr_ciutat      VARCHAR(60);

I la migració de les dades existents, que és la part que ningú no explica i sempre costa:

-- Els quatre valors actuals, migrats a mà: són quatre files.
UPDATE sucursals SET adr_carrer = 'Plaça Major',      adr_numero = '3',
                     adr_codi_postal = '08820', adr_ciutat = 'Vallmar'
 WHERE sucursal_id = 1;
UPDATE sucursals SET adr_carrer = 'Avinguda del Nord', adr_numero = '112',
                     adr_codi_postal = '08821', adr_ciutat = 'Vallmar'
 WHERE sucursal_id = 2;
-- ... sucursals 3 i 4

ALTER TABLE sucursals DROP COLUMN adreca;
UPDATE 1
UPDATE 1
ALTER TABLE

Consell pràctic: quan l'atribut compost ja té dades en producció com a text lliure, la conversió automàtica amb expressions regulars falla més del que encerta. Amb quatre files es fa a mà. Amb quaranta mil, es fa per lots, es revisa el que no encaixa en el patró i es conserva la columna original reanomenada a adreca_original durant un parell de mesos.

Quan NO descompondre

La regla té una excepció important i amb freqüència s'aplica malament per excés de zel. No descomponguis si ningú no buscarà, filtrarà, ordenarà o agregarà per les parts.

Cas Descompondre? Motiu
Adreça de sucursal (R13) Sí Es busca per codi postal, es mostra la ciutat a part
Adreça postal d'un ponent extern No Només s'imprimeix en una carta; un VARCHAR n'hi ha prou
Nom i cognoms d'un soci Sí (ja ho està) S'ordenen llistats per cognoms
Observacions d'un informe d'esdeveniment No Text lliure per naturalesa
Durada hh:mm d'un DVD No: és un sol número de minuts Descompondre en hores i minuts complica tots els càlculs

El cost de descompondre de més és real: quatre columnes que cal omplir, validar i mantenir, per a una dada que només s'imprimeix sencera. El cost de descompondre de menys és pitjor —cal trossejar cadenes en cada consulta—, i per això davant del dubte es descompon; però el dubte ha d'existir.

  1. Regla 3 — Atribut multivaluat → taula a part

Regla 3. Un atribut multivaluat es converteix en una taula nova amb dues parts: una clau forana a l'entitat propietària i el mateix valor. La clau primària és la combinació de totes dues.

És la regla que fa desaparèixer per sempre els antipatrons telefon1, telefon2, telefon3 i subtitols = 'es,ca,en' que vam denunciar a 04-01.

Els telèfons d'un soci (R12)

CREATE TABLE telefons_soci (
    soci_id INTEGER      NOT NULL,
    numero  VARCHAR(20)  NOT NULL,
    tipus   VARCHAR(10)  NOT NULL,
    CONSTRAINT pk_telefons_soci PRIMARY KEY (soci_id, numero),
    CONSTRAINT fk_telefons_soci_soci
        FOREIGN KEY (soci_id) REFERENCES socis (soci_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

Anàlisi de les tres decisions:

  • La PK és (soci_id, numero). El valor forma part de la clau, i això és el que impedeix desar el mateix telèfon dues vegades per al mateix soci. És integritat gratuïta: la regla "no repeteixis un telèfon" no necessita codi.
  • ON DELETE CASCADE: un telèfon no té cap existència fora del seu soci. Si el soci s'esborra, els seus telèfons se n'han d'anar amb ell. És el cas de llibre per a CASCADE segons els criteris de 02-06.
  • El límit de tres telèfons de R12 no és aquí. Cap clau ni restricció de taula no l'expressa. És una regla de negoci pendent, i a 04-04 decidirem on viu.

Provem-ho:

INSERT INTO telefons_soci (soci_id, numero, tipus) VALUES
    (14, '600111222', 'mobil'),
    (14, '938880011', 'fix'),
    (15, '600333444', 'mobil');

INSERT INTO telefons_soci (soci_id, numero, tipus) VALUES (14, '600111222', 'feina');
INSERT 0 3
ERROR:  duplicate key value violates unique constraint "pk_telefons_soci"
DETAIL:  Key (soci_id, numero)=(14, 600111222) already exists.

Exactament l'error que volíem: el mateix número no es pot registrar dues vegades per al mateix soci, ni tan sols canviant-li el tipus.

Els subtítols d'un DVD (R1)

CREATE TABLE subtitols_dvd (
    material_id INTEGER     NOT NULL,
    idioma      VARCHAR(5)  NOT NULL,
    CONSTRAINT pk_subtitols_dvd PRIMARY KEY (material_id, idioma),
    CONSTRAINT fk_subtitols_dvd_material
        FOREIGN KEY (material_id) REFERENCES materials_dvd (material_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

Fixa't en el detall: la clau forana apunta a materials_dvd, no a materials. És la subentitat la que té subtítols, no qualsevol material. Un audiollibre no en pot tenir, i l'esquema ho fa impossible. Aquesta precisió és un dels avantatges de l'estratègia de jerarquia que triarem a la regla 10.

I ara la consulta C9 ("DVD amb subtítols en català") es respon amb un JOIN normal en lloc d'amb un LIKE '%ca%' que retornava brossa:

SELECT m.material_id, m.titol
  FROM materials m
  JOIN subtitols_dvd s ON s.material_id = m.material_id
 WHERE s.idioma = 'ca'
 ORDER BY m.titol;
 material_id |             titol
-------------+-------------------------------
        1204 | El bosc de les ombres
        1187 | La ciutat dels prodigis
(2 rows)

  1. Regla 4 — Atribut derivat → no es desa

Regla 4. Un atribut derivat no genera columna. Es calcula en el moment de consultar-lo.

Dels cinc derivats que vam anotar a 04-02, vegem el més usat: les places lliures d'un esdeveniment (C2).

SELECT e.esdeveniment_id,
       e.titol,
       e.places_ofertes,
       e.places_ofertes - COALESCE(SUM(1 + i.acompanyants), 0) AS places_lliures
  FROM esdeveniments e
  LEFT JOIN inscripcions i
         ON i.esdeveniment_id = e.esdeveniment_id
        AND i.estat = 'confirmada'
 WHERE e.esdeveniment_id = 47
 GROUP BY e.esdeveniment_id, e.titol, e.places_ofertes;
 esdeveniment_id |             titol              | places_ofertes | places_lliures
-----------------+--------------------------------+----------------+----------------
              47 | Club de lectura: novel·la negra |             20 |              6
(1 row)

Tres peces que val la pena assenyalar, totes ja conegudes de mòduls anteriors: el LEFT JOIN conserva els esdeveniments sense cap inscripció (02-04), el COALESCE converteix el NULL del SUM buit en 0 (02-01, lògica trivalent) i 1 + acompanyants implementa la decisió que un soci amb dos acompanyants ocupa tres places (ambigüitat resolta a 04-01).

La forma còmoda: una vista

Repetir aquesta consulta en vint llocs és una invitació que en algun s'escrigui malament. Una vista l'encapsula, i a més és un exemple pur del nivell extern d'ANSI/SPARC de 01-04:

CREATE VIEW v_esdeveniments_ocupacio AS
SELECT e.esdeveniment_id,
       e.titol,
       e.inici,
       e.places_ofertes,
       COALESCE(SUM(1 + i.acompanyants) FILTER (WHERE i.estat = 'confirmada'), 0) AS places_ocupades,
       e.places_ofertes
         - COALESCE(SUM(1 + i.acompanyants) FILTER (WHERE i.estat = 'confirmada'), 0) AS places_lliures
  FROM esdeveniments e
  LEFT JOIN inscripcions i ON i.esdeveniment_id = e.esdeveniment_id
 GROUP BY e.esdeveniment_id, e.titol, e.inici, e.places_ofertes;

La vista no desa res: es recalcula en cada consulta. Continua complint la regla 4.

Quan sí que es desa

Hi ha dues situacions en què un derivat s'acaba desant, i totes dues tenen nom:

  1. Per rendiment. Si l'agenda pública (C1) mostra les places lliures de quaranta esdeveniments i cadascuna implica agregar milers d'inscripcions, pot compensar mantenir una columna places_ocupades actualitzada per disparador. Això és desnormalització deliberada, té un cost conegut (el risc que el valor desat i el real divergeixin) i és el tema de la lliçó 05-04. No ho facis sense mesurar abans.
  2. Perquè el valor s'ha de congelar. Aquest cas és diferent i es confon amb l'anterior. L'import d'una multa es calcula avui a 0,20 €/dia, però si demà l'ordenança apuja la tarifa, les multes d'ahir no s'han de recalcular. Aquest import no és un derivat: és un fet històric que es desa perquè el seu valor depèn d'un context que ja no existeix. La mateixa lògica que justifica desar el preu de venda en una factura.

A BiblioRed, multes.import és del segon tipus i per això és una columna. places_lliures és del primer i per això no ho és.

  1. Regla 5 — Relació 1:N → clau forana al costat N

Regla 5. En una relació 1:N, la taula del costat N rep una clau forana que apunta a la clau primària del costat 1. Els atributs propis de la relació, si n'hi ha, van també al costat N.

És la regla que més vegades s'aplica en qualsevol esquema. A BiblioRed:

CREATE TABLE sales (
    sala_id     INTEGER GENERATED BY DEFAULT AS IDENTITY,
    sucursal_id INTEGER      NOT NULL,
    nom         VARCHAR(80)  NOT NULL,
    aforament   INTEGER      NOT NULL,
    planta      INTEGER      NOT NULL DEFAULT 0,
    accessible  BOOLEAN      NOT NULL DEFAULT TRUE,
    CONSTRAINT pk_sales PRIMARY KEY (sala_id),
    CONSTRAINT uq_sales_sucursal_nom UNIQUE (sucursal_id, nom),
    CONSTRAINT fk_sales_sucursal
        FOREIGN KEY (sucursal_id) REFERENCES sucursals (sucursal_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

Quatre coses derivades directament del diagrama:

  • sucursal_id va a sales, el costat N. Mai a l'inrevés.
  • NOT NULL perquè la participació de SALES era total: tota sala és en una sucursal (04-02, apartat 6).
  • UNIQUE (sucursal_id, nom) implementa l'afirmació de R3: el nom només és únic dins de la sucursal. Això és el que queda que SALES fos conceptualment feble; hi tornarem a la regla 8.
  • ON DELETE RESTRICT: no s'esborra una sucursal que té sales. Coherent amb la decisió ja presa per a socis.sucursal_id i exemplars.sucursal_id a 02-06.

Per què mai a l'inrevés

La pregunta és legítima: per què no posar a sucursals una columna sala_id? Perquè una sucursal té diverses sales, i en una columna només hi cap un valor. Les dues úniques maneres de forçar-ho són els dos antipatrons de 04-01:

Intent Què és Per què falla
sucursals.sala1_id, sala2_id, ... sala6_id Columnes numerades R3 diu "entre 1 i 6" avui; la setena arriba tan bon punt reformin un edifici. NULL massiu. Consultar "en quina sucursal és la sala 12?" requereix revisar sis columnes
sucursals.sales = '4,7,12' Llista amb comes Sense clau forana, sense tipus, sense JOIN, amb LIKE que retorna falsos positius

La FK va sempre al costat que té com a màxim un valor. Aquest costat és el N.

El cas de l'esdeveniment sense sala

esdeveniments.sala_id és diferent perquè la decisió D4 de 04-02 va fer la sala opcional:

    sala_id INTEGER NULL,          -- participació parcial: esdeveniments a l'aire lliure
    CONSTRAINT fk_esdeveniments_sala
        FOREIGN KEY (sala_id) REFERENCES sales (sala_id)
        ON DELETE RESTRICT ON UPDATE CASCADE

La participació del diagrama es tradueix literalment en NOT NULL o la seva absència. És la conversió més directa de tot l'algorisme i per això convé haver fet bé aquella pregunta a 04-02.

  1. Regla 6 — Relació 1:1 → tres opcions

Regla 6. Una relació 1:1 admet tres solucions: fusionar les dues entitats en una taula, posar la clau forana en un dels dos costats amb UNIQUE, o crear una taula intermèdia. L'elecció depèn de la participació i del patró d'accés.

El cas de BiblioRed és ESDEVENIMENTS — INFORMES_ESDEVENIMENT (R9): un esdeveniment té com a molt un informe, un informe és d'un esdeveniment.

Opció A — Fusionar en una taula

-- Opció A: els camps de l'informe dins d'esdeveniments
ALTER TABLE esdeveniments ADD COLUMN assistents_reals  INTEGER;
ALTER TABLE esdeveniments ADD COLUMN valoracio_mitjana NUMERIC(3,2);
ALTER TABLE esdeveniments ADD COLUMN observacions      VARCHAR(2000);
ALTER TABLE esdeveniments ADD COLUMN data_redaccio     DATE;

A favor: cap JOIN, una sola fila per llegir, simplicitat màxima. En contra: dels 1.200 esdeveniments previstos a tres anys, potser 700 tindran informe; els altres 500 portaran quatre columnes a NULL. I hi ha un problema pitjor que el malbaratament: no es pot distingir "informe no redactat encara" de "informe redactat amb zero assistents". Tots dos casos donen assistents_reals IS NULL o = 0 segons com s'ompli, i la consulta C12 ("esdeveniments sense informe") es torna ambigua.

Opció B — Clau forana amb UNIQUE en un dels costats

-- Opció B, triada per a BiblioRed
CREATE TABLE informes_esdeveniment (
    esdeveniment_id   INTEGER      NOT NULL,
    assistents_reals  INTEGER      NOT NULL,
    valoracio_mitjana NUMERIC(3,2),
    observacions      VARCHAR(2000),
    data_redaccio     DATE         NOT NULL,
    CONSTRAINT pk_informes_esdeveniment PRIMARY KEY (esdeveniment_id),
    CONSTRAINT fk_informes_esdeveniment_esdeveniment
        FOREIGN KEY (esdeveniment_id) REFERENCES esdeveniments (esdeveniment_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

Aquí la clau forana és la clau primària. Això és el que fa la relació 1:1: si esdeveniment_id és PK d'informes_esdeveniment, no es pot repetir, i per tant un esdeveniment no pot tenir dos informes. No cal un UNIQUE addicional: la clau primària ja ho és.

A favor: zero NULL, l'existència de la fila és la resposta a "té informe?", i C12 es resol amb un anti-join net:

SELECT e.esdeveniment_id, e.titol, e.inici
  FROM esdeveniments e
  LEFT JOIN informes_esdeveniment i ON i.esdeveniment_id = e.esdeveniment_id
 WHERE e.estat = 'celebrat'
   AND e.inici < CURRENT_DATE - INTERVAL '15 days'
   AND i.esdeveniment_id IS NULL
 ORDER BY e.inici;
 esdeveniment_id |             titol               |         inici
-----------------+---------------------------------+------------------------
              31 | Taller d'escriptura creativa    | 2026-06-18 18:00:00+02
              38 | Presentació: "Els dies llargs"  | 2026-07-02 19:30:00+02
(2 rows)

En contra: un JOIN quan es necessiten tots dos junts. En aquest cas no importa: l'informe es consulta en una pantalla diferent de l'agenda.

Opció C — Taula intermèdia

Una tercera taula amb dues claus foranes, totes dues UNIQUE. Només té sentit quan tots dos costats són de participació parcial i la relació en si és un fet que pot aparèixer i desaparèixer (per exemple, "quin empleat té assignat quin vehicle de flota"). Per a l'informe seria absurd: un informe sense esdeveniment no existeix.

Taula de decisió

Criteri Opció A: fusionar Opció B: FK + UNIQUE Opció C: taula intermèdia
Participació dels dos costats Total en tots dos Total en un, parcial en l'altre Parcial en tots dos
Nuls generats Molts si un costat és opcional Cap Cap
JOIN necessaris Cap Un Dos
Distingir "no existeix" de "val zero" No Sí Sí
Columnes grans o poc consultades Penalitza cada lectura Aïlla el pes Aïlla el pes
Complexitat Mínima Baixa Alta

Regla pràctica: si tots dos costats són de participació total i es consulten sempre junts, fusiona (opció A) —de fet, si és el teu cas, replanteja't si de debò eren dues entitats—. Si un costat és opcional, FK al costat opcional amb la PK compartida (opció B). L'opció C és rara i cal justificar-la.

Decisió de BiblioRed: opció B. El costat opcional és l'informe, i allà va la taula.

  1. Regla 7 — Relació N:M → taula d'unió

Regla 7. Una relació N:M es converteix en una taula d'unió amb dues claus foranes, una a cada entitat. Els atributs propis de la relació es converteixen en columnes d'aquesta taula. La clau primària és, per defecte, la combinació de les dues claus foranes.

És la regla que més vegades s'aplica malament, gairebé sempre oblidant la segona frase.

El cas principal: inscripcions (R6)

CREATE TABLE inscripcions (
    esdeveniment_id   INTEGER      NOT NULL,
    soci_id           INTEGER      NOT NULL,
    data_inscripcio   TIMESTAMPTZ  NOT NULL DEFAULT now(),
    estat             VARCHAR(15)  NOT NULL DEFAULT 'confirmada',
    acompanyants      INTEGER      NOT NULL DEFAULT 0,
    CONSTRAINT pk_inscripcions PRIMARY KEY (esdeveniment_id, soci_id),
    CONSTRAINT fk_inscripcions_esdeveniment
        FOREIGN KEY (esdeveniment_id) REFERENCES esdeveniments (esdeveniment_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_inscripcions_soci
        FOREIGN KEY (soci_id) REFERENCES socis (soci_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

Els tres atributs propis —data, estat, acompanyants— viuen aquí i enlloc més. És l'aplicació de la regla que vam enunciar a 04-02: si un atribut necessita totes dues claus per tenir valor, pertany a la relació.

La clau primària composta implementa de franc la regla de R6:

INSERT INTO inscripcions (esdeveniment_id, soci_id) VALUES (47, 14);
INSERT INTO inscripcions (esdeveniment_id, soci_id, acompanyants) VALUES (47, 14, 2);
INSERT 0 1
ERROR:  duplicate key value violates unique constraint "pk_inscripcions"
DETAIL:  Key (esdeveniment_id, soci_id)=(47, 14) already exists.

"Un soci no es pot inscriure dues vegades al mateix esdeveniment", diu R6. Aquí ho tens, sense una línia de codi d'aplicació.

Les dues accions referencials, raonades

Aquest és un bon moment per aplicar el criteri de 02-06, perquè les dues claus foranes de la mateixa taula porten accions diferents i això desconcerta molta gent:

Clau forana Acció Motiu
esdeveniment_id ON DELETE CASCADE Una inscripció no significa res sense el seu esdeveniment. Si un esdeveniment programat s'elimina del sistema, les seves inscripcions han de desaparèixer. És el mateix criteri que vam aplicar a reserves.llibre_id
soci_id ON DELETE RESTRICT La inscripció és un fet històric amb valor (assistències, estadístiques de C5). Esborrar un soci no ha d'esborrar el registre que va assistir a dotze esdeveniments. Mateix criteri que prestecs.soci_id

Compara-ho amb reserves.soci_id, que sí que és CASCADE: una reserva és efímera i no té valor històric; una inscripció, sí. L'acció referencial es dedueix del valor de la dada, no de la forma de la relació.

Clau composta o clau subrogada?

És la decisió que cal prendre a cada taula d'unió:

PK composta (esdeveniment_id, soci_id) PK subrogada inscripcio_id + UNIQUE (esdeveniment_id, soci_id)
Impedeix duplicats Sí, directament Sí, amb l'UNIQUE (si te'n recordes de posar-lo)
Referenciable des d'una altra taula Amb FK composta, més feixuga Amb una sola columna, més còmoda
Semàntica La clau és la regla de negoci La clau no significa res
Mida 8 bytes 4 bytes + l'índex de l'UNIQUE
Compatible amb ORM Alguns ORM ho porten malament Universal

Decisió de BiblioRed: PK composta a inscripcions, participacions i esdeveniments_materials. Cap altra taula no necessita referenciar-les, i la clau composta expressa la regla de negoci directament.

Els altres dos N:M

esdeveniments_materials (R8) és el cas més simple de l'esquema, N:M amb un sol atribut propi:

CREATE TABLE esdeveniments_materials (
    esdeveniment_id INTEGER      NOT NULL,
    material_id     INTEGER      NOT NULL,
    paper           VARCHAR(15)  NOT NULL DEFAULT 'recomanat',
    CONSTRAINT pk_esdeveniments_materials PRIMARY KEY (esdeveniment_id, material_id),
    CONSTRAINT fk_esdeveniments_materials_esdeveniment
        FOREIGN KEY (esdeveniment_id) REFERENCES esdeveniments (esdeveniment_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_esdeveniments_materials_material
        FOREIGN KEY (material_id) REFERENCES materials (material_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

I participacions (R7) és en realitat la regla 9; arriba en dos apartats.

  1. Regla 8 — Entitat feble → clau primària composta

Regla 8. Una entitat feble es converteix en una taula la clau primària de la qual és la combinació de la clau primària de l'entitat propietària i el seu identificador parcial. La clau forana a la propietària forma part de la clau primària i és, obligatòriament, NOT NULL.

Ja l'hem aplicada tres vegades sense dir-ho: telefons_soci (regla 3), informes_esdeveniment (regla 6) i inscripcions (regla 7). El cas pur és el dels exemplars respecte al material.

La versió ortodoxa

-- Aplicació literal de la regla 8
CREATE TABLE exemplars (
    material_id   INTEGER     NOT NULL,
    num_exemplar  INTEGER     NOT NULL,     -- identificador parcial
    codi          VARCHAR(15) NOT NULL,
    sucursal_id   INTEGER     NOT NULL,
    estat         VARCHAR(20) NOT NULL,
    data_adquisicio DATE,
    CONSTRAINT pk_exemplars PRIMARY KEY (material_id, num_exemplar),
    ...
);

És correcta i expressa exactament la semàntica: "l'exemplar 3 d'El mapa del temps".

Per què BiblioRed no la fa servir

Hi ha una raó concreta i decisiva: prestecs referencia exemplars. Amb la PK composta, prestecs necessitaria dues columnes i una clau forana composta:

-- El que implicaria la versió ortodoxa
CREATE TABLE prestecs (
    prestec_id   INTEGER GENERATED BY DEFAULT AS IDENTITY,
    soci_id      INTEGER NOT NULL,
    material_id  INTEGER NOT NULL,     -- dues columnes
    num_exemplar INTEGER NOT NULL,     -- per identificar un exemplar
    ...
    CONSTRAINT fk_prestecs_exemplar
        FOREIGN KEY (material_id, num_exemplar)
        REFERENCES exemplars (material_id, num_exemplar)
);

I a més prestecs ja existeix amb 4.312 files i una FK a exemplar_id. Canviar-ho seria una migració considerable per no guanyar res.

Decisió de BiblioRed: clau subrogada + UNIQUE sobre la clau feble. És el patró habitual i conserva tota la integritat:

CREATE TABLE exemplars (
    exemplar_id     INTEGER GENERATED BY DEFAULT AS IDENTITY,
    codi            VARCHAR(15) NOT NULL,
    material_id     INTEGER     NOT NULL,
    num_exemplar    INTEGER     NOT NULL,
    sucursal_id     INTEGER     NOT NULL,
    estat           VARCHAR(20) NOT NULL DEFAULT 'disponible',
    data_adquisicio DATE,
    CONSTRAINT pk_exemplars PRIMARY KEY (exemplar_id),
    CONSTRAINT uq_exemplars_codi UNIQUE (codi),
    CONSTRAINT uq_exemplars_material_num UNIQUE (material_id, num_exemplar),
    CONSTRAINT fk_exemplars_material
        FOREIGN KEY (material_id) REFERENCES materials (material_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_exemplars_sucursal
        FOREIGN KEY (sucursal_id) REFERENCES sucursals (sucursal_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

La combinació és: exemplar_id com a clau primària (còmoda per referenciar), uq_exemplars_material_num com a clau candidata natural que preserva la semàntica d'entitat feble, i uq_exemplars_codi per al codi imprès a l'etiqueta. No s'ha perdut res.

El principi general: a la fase lògica, una entitat feble pot portar clau subrogada sense deixar de ser feble, sempre que la seva clau natural composta es declari UNIQUE. El que no és negociable és la unicitat; la forma de la clau primària sí.

El cas de sales és idèntic: PK sala_id, UNIQUE (sucursal_id, nom). I el de pagaments també: PK pagament_id, amb la particularitat que allà ni tan sols hi ha identificador parcial natural (dos pagaments de la mateixa multa pel mateix import el mateix dia són dos pagaments diferents), cosa que és un argument addicional per a la subrogada.

  1. Regla 9 — Relació ternària → taula amb tres claus foranes

Regla 9. Una relació de grau 3 es converteix en una taula amb tres claus foranes, una a cada entitat participant, més els atributs propis de la relació. La clau primària és, per defecte, la combinació de les tres.

I un advertiment que és tan important com la regla: abans d'aplicar-la, comprova que la relació és ternària de debò.

La prova de descomposició

Una relació ternària R(A, B, C) és genuïna si l'existència d'una tripleta no es pot deduir de tres relacions binàries. La prova consisteix a preguntar-se si R(A,B,C) equival a R1(A,B) ∧ R2(B,C) ∧ R3(A,C).

Un contraexemple clàssic i erroni: "un soci demana en préstec un exemplar en una sucursal" sembla ternari, però no ho és: la sucursal es dedueix de l'exemplar. És una binària amb un derivat. Modelar-la com a ternària introdueix redundància i la possibilitat que la sucursal registrada al préstec contradigui la de l'exemplar.

Un cas genuïnament ternari: "un proveïdor subministra un component per a un projecte concret". Que el proveïdor A subministri el component X, que X es faci servir al projecte P i que A treballi amb P no implica que A subministri X per a P. La informació es perdria en descompondre.

El cas de BiblioRed: participacions (R7)

A 04-02 (decisió D5) vam analitzar ESDEVENIMENT — PONENT — ROL. La conclusió va ser que ROL no és una entitat: és un conjunt petit, estable i sense atributs propis. El que queda és una relació binària N:M l'identificador de la qual inclou un atribut:

CREATE TABLE participacions (
    esdeveniment_id INTEGER       NOT NULL,
    ponent_id       INTEGER       NOT NULL,
    rol             VARCHAR(25)   NOT NULL,
    honoraris       NUMERIC(8,2)  NOT NULL DEFAULT 0,
    CONSTRAINT pk_participacions PRIMARY KEY (esdeveniment_id, ponent_id, rol),
    CONSTRAINT fk_participacions_esdeveniment
        FOREIGN KEY (esdeveniment_id) REFERENCES esdeveniments (esdeveniment_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_participacions_ponent
        FOREIGN KEY (ponent_id) REFERENCES ponents (ponent_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

La clau primària de tres columnes és el que permet que Elena Roig sigui alhora moderadora i tallerista del mateix esdeveniment —R7 ho exigia— i alhora impedeix que aparegui dues vegades com a moderadora del mateix esdeveniment.

INSERT INTO participacions (esdeveniment_id, ponent_id, rol, honoraris) VALUES
    (47, 8, 'moderador',  0),
    (47, 8, 'tallerista', 180.00),
    (47, 9, 'autor_convidat', 250.00);
INSERT 0 3
INSERT INTO participacions (esdeveniment_id, ponent_id, rol, honoraris)
VALUES (47, 8, 'moderador', 50.00);
ERROR:  duplicate key value violates unique constraint "pk_participacions"
DETAIL:  Key (esdeveniment_id, ponent_id, rol)=(47, 8, moderador) already exists.

ponent_id és RESTRICT i no CASCADE per un motiu molt concret: els honoraris són un registre amb implicacions econòmiques. Esborrar un ponent no pot esborrar el rastre del que se li va pagar.

Consell: quan en un disseny aparegui una relació de grau 3, dedica cinc minuts a la prova de descomposició. En l'experiència pràctica, quatre de cada cinc resulten ser una binària amb un atribut, una binària amb un derivat, o dues binàries independents.

  1. Regla 10 — Jerarquia de generalització → tres estratègies

Regla 10. Una jerarquia de generalització es porta a taules amb una de tres estratègies: taula única, taula per subclasse o taula per classe concreta. L'elecció depèn de la disjunció, la totalitat, el nombre d'atributs específics i el patró de consulta.

És la decisió més important de tota l'ampliació de BiblioRed, perquè afecta el catàleg sencer i les taules que ja existeixen.

Estratègia 1 — Taula única (single table inheritance)

Una sola taula amb tots els atributs de totes les subclasses, més una columna discriminant.

CREATE TABLE materials (
    material_id    INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    tipus_material VARCHAR(15) NOT NULL,
    titol          VARCHAR(200) NOT NULL,
    -- ... atributs comuns ...
    isbn           VARCHAR(13),   -- només llibres
    num_pagines    INTEGER,       -- només llibres
    enquadernacio  VARCHAR(20),   -- només llibres
    durada_min     INTEGER,       -- DVD i audiollibres
    format_video   VARCHAR(15),   -- només DVD
    codi_regio     INTEGER,       -- només DVD
    issn           VARCHAR(9),    -- només revistes
    numero         VARCHAR(20),   -- només revistes
    periodicitat   VARCHAR(20),   -- només revistes
    narrador       VARCHAR(120),  -- només audiollibres
    format_audio   VARCHAR(15)    -- només audiollibres
);

Ràpida de consultar (cap JOIN) i desastrosa de validar: és impossible declarar isbn NOT NULL encara que R1 ho exigeixi per als llibres, perquè els DVD el tindrien a NULL. L'única sortida són CHECK condicionals de l'estil CHECK (tipus_material <> 'llibre' OR isbn IS NOT NULL), un per atribut obligatori, que es multipliquen ràpidament.

Estratègia 2 — Taula per subclasse (class table inheritance)

Una taula per a la superclasse amb el que és comú, i una taula per subclasse amb el que és específic, unides per la clau primària compartida.

CREATE TABLE materials (
    material_id    INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    tipus_material VARCHAR(15)  NOT NULL,
    titol          VARCHAR(200) NOT NULL,
    autor_id       INTEGER,
    editorial      VARCHAR(120),
    any_publicacio INTEGER,
    idioma         VARCHAR(5)   NOT NULL,
    data_alta      DATE         NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE materials_llibre (
    material_id   INTEGER     NOT NULL PRIMARY KEY REFERENCES materials (material_id) ON DELETE CASCADE,
    isbn          VARCHAR(13) NOT NULL UNIQUE,       -- ara sí que es pot exigir
    num_pagines   INTEGER,
    enquadernacio VARCHAR(20)
);

Zero nuls innecessaris, restriccions específiques exigibles, i la clau forana d'exemplars apunta a materials, que és el que necessitaven C10, prestecs i reserves. El seu cost és un JOIN quan es necessiten els detalls del subtipus.

Estratègia 3 — Taula per classe concreta (concrete table inheritance)

Una taula completa i independent per a cada subclasse, sense taula de superclasse: llibres, dvds, revistes, audiollibres, cadascuna amb tots els atributs, comuns i específics.

És la pitjor opció per a BiblioRed i convé entendre exactament per què: exemplars no tindria a què apuntar. Necessitaria quatre columnes (llibre_id, dvd_id, revista_id, audiollibre_id) amb tres a NULL en cada fila, o una parella (tipus, id) sense clau forana possible —un antipatró conegut com a referència polimòrfica, que renuncia a la integritat referencial—. I la consulta C10 exigiria un UNION de quatre branques. Només és viable quan les subclasses no es referencien des de fora, i aquí es referencien des de tres llocs.

Taula comparativa

Criteri Taula única Taula per subclasse Taula per classe concreta
Nre. de taules 1 1 + n n
Nuls Molts Cap Cap
NOT NULL en atributs de subclasse Impossible (només CHECK condicional) Directe Directe
Consultar tots els materials Trivial Trivial (la superclasse) UNION de n branques
Consultar un subtipus amb els seus detalls Trivial Un JOIN Trivial
Referenciar des de fora (exemplars, reserves) Trivial Trivial (a la superclasse) Impossible sense renunciar a la FK
Afegir un subtipus nou ALTER TABLE sobre la taula gran Una taula nova, res existent no canvia Una taula nova + revisar tots els UNION
Coherència discriminant ↔ subtipus Automàtica Cal garantir-la No s'aplica
Requereix jerarquia disjunta No No Sí
Requereix jerarquia total No No Sí
Quan triar-la Pocs atributs específics (2-3), subtipus que gairebé no difereixen, prioritat absoluta a la lectura Molts atributs específics, subtipus referenciats des de fora, integritat important Subtipus gairebé sense res en comú i que ningú no referencia

La decisió de BiblioRed: taula per subclasse

Quatre raons, en ordre de pes:

  1. exemplars, prestecs i reserves necessiten una entitat comuna a la qual apuntar. Descarta l'estratègia 3 per si sola.
  2. Cada subtipus té 3-4 atributs específics obligatoris. Amb taula única serien onze columnes majoritàriament nul·les i onze CHECK condicionals.
  3. R1 exigeix isbn NOT NULL UNIQUE per a llibres i issn per a revistes. Només l'estratègia 2 ho permet de manera directa.
  4. Afegir "mapes" o "partitures" en el futur és crear una taula, sense tocar res existent. És exactament l'objectiu 5 de 04-01, evolució sense migracions traumàtiques.

El preu: mantenir la coherència del discriminant

L'estratègia 2 té un forat conegut i cal reconèixer-lo: res no impedeix, per si sol, que un material amb tipus_material = 'llibre' tingui una fila a materials_dvd, o que no tingui cap fila a materials_llibre (violant la totalitat de la jerarquia).

Es tanca amb una tècnica elegant: incloure el discriminant a la clau forana. Es declara UNIQUE (material_id, tipus_material) a materials i la subtaula referencia aquesta parella amb el seu propi tipus_material fixat per un CHECK. És una aplicació directa de les claus foranes compostes de 02-06:

ALTER TABLE materials
    ADD CONSTRAINT uq_materials_id_tipus UNIQUE (material_id, tipus_material);

CREATE TABLE materials_llibre (
    material_id    INTEGER     NOT NULL,
    tipus_material VARCHAR(15) NOT NULL DEFAULT 'llibre',
    isbn           VARCHAR(13) NOT NULL,
    num_pagines    INTEGER,
    enquadernacio  VARCHAR(20),
    CONSTRAINT pk_materials_llibre PRIMARY KEY (material_id),
    CONSTRAINT uq_materials_llibre_isbn UNIQUE (isbn),
    CONSTRAINT chk_materials_llibre_tipus CHECK (tipus_material = 'llibre'),
    CONSTRAINT fk_materials_llibre_material
        FOREIGN KEY (material_id, tipus_material)
        REFERENCES materials (material_id, tipus_material)
        ON DELETE CASCADE ON UPDATE CASCADE
);

Ara és físicament impossible que un DVD tingui fila a materials_llibre:

-- el material 1204 és un DVD
INSERT INTO materials_llibre (material_id, isbn) VALUES (1204, '9788401339097');
ERROR:  insert or update on table "materials_llibre" violates foreign key constraint "fk_materials_llibre_material"
DETAIL:  Key (material_id, tipus_material)=(1204, llibre) is not present in table "materials".

L'altra meitat de la jerarquia —que tot material tingui fila en alguna subtaula, la totalitat— no es pot garantir amb restriccions declaratives. És la mateixa limitació que vam veure a 04-02 amb "tota sucursal té almenys una sala". Es resol amb disparador, amb una transacció que insereixi totes dues files juntes, o assumint el risc i comprovant-ho periòdicament. A 04-04 tanquem el criteri.

Compatibilitat: llibres es converteix en una vista

Tot el codi i tots els informes existents consulten llibres. Reescriure'ls tots és innecessari:

CREATE VIEW llibres AS
SELECT m.material_id AS llibre_id,
       l.isbn,
       m.titol,
       m.autor_id,
       m.editorial,
       m.any_publicacio,
       m.idioma
  FROM materials m
  JOIN materials_llibre l ON l.material_id = m.material_id;

Les consultes antigues continuen funcionant sense ni un sol canvi. És l'exemple més clar d'independència lògica de tot el curs: hem canviat el nivell conceptual i el nivell extern ha absorbit el canvi, exactament com descrivia ANSI/SPARC a 01-04.

  1. Entregable: l'script complet de BiblioRed ampliat

Aquí hi ha el resultat d'aplicar les deu regles. Recorda que els tipus són provisionals: VARCHAR(n) posats a ull, INTEGER per a tot el que és enter, restriccions limitades a les estructurals. La lliçó 04-04 revisa cada tipus i hi afegeix el catàleg complet de CHECK, DEFAULT, dominis i columnes generades.

-- =====================================================================
-- BiblioRed · Migració V004: ampliació esdeveniments, materials i multes
-- Tipus PROVISIONALS. Revisats a 04-04.
-- =====================================================================

-- ---------------------------------------------------------------------
-- BLOC 1 · Regla 2: atribut compost (R13)
-- ---------------------------------------------------------------------
ALTER TABLE sucursals ADD COLUMN adr_carrer      VARCHAR(120);
ALTER TABLE sucursals ADD COLUMN adr_numero      VARCHAR(10);
ALTER TABLE sucursals ADD COLUMN adr_codi_postal VARCHAR(5);
ALTER TABLE sucursals ADD COLUMN adr_ciutat      VARCHAR(60);
-- (migració de dades i DROP COLUMN adreca: vegeu l'apartat 3)

-- ---------------------------------------------------------------------
-- BLOC 2 · Regla 10: jerarquia de materials (R1)
-- ---------------------------------------------------------------------
CREATE TABLE materials (
    material_id    INTEGER GENERATED BY DEFAULT AS IDENTITY,
    tipus_material VARCHAR(15)  NOT NULL,
    titol          VARCHAR(200) NOT NULL,
    autor_id       INTEGER,
    editorial      VARCHAR(120),
    any_publicacio INTEGER,
    idioma         VARCHAR(5)   NOT NULL,
    data_alta      DATE         NOT NULL DEFAULT CURRENT_DATE,
    CONSTRAINT pk_materials       PRIMARY KEY (material_id),
    CONSTRAINT uq_materials_id_tipus UNIQUE (material_id, tipus_material),
    CONSTRAINT chk_materials_tipus
        CHECK (tipus_material IN ('llibre','dvd','revista','audiollibre')),
    CONSTRAINT fk_materials_autor
        FOREIGN KEY (autor_id) REFERENCES autors (autor_id)
        ON DELETE SET NULL ON UPDATE CASCADE      -- igual que llibres.autor_id
);

CREATE TABLE materials_llibre (
    material_id    INTEGER     NOT NULL,
    tipus_material VARCHAR(15) NOT NULL DEFAULT 'llibre',
    isbn           VARCHAR(13) NOT NULL,
    num_pagines    INTEGER,
    enquadernacio  VARCHAR(20),
    CONSTRAINT pk_materials_llibre  PRIMARY KEY (material_id),
    CONSTRAINT uq_materials_llibre_isbn UNIQUE (isbn),
    CONSTRAINT chk_materials_llibre_tipus CHECK (tipus_material = 'llibre'),
    CONSTRAINT fk_materials_llibre_material
        FOREIGN KEY (material_id, tipus_material)
        REFERENCES materials (material_id, tipus_material)
        ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE materials_dvd (
    material_id    INTEGER     NOT NULL,
    tipus_material VARCHAR(15) NOT NULL DEFAULT 'dvd',
    durada_min     INTEGER     NOT NULL,
    format_video   VARCHAR(15),
    codi_regio     INTEGER,
    CONSTRAINT pk_materials_dvd PRIMARY KEY (material_id),
    CONSTRAINT chk_materials_dvd_tipus CHECK (tipus_material = 'dvd'),
    CONSTRAINT fk_materials_dvd_material
        FOREIGN KEY (material_id, tipus_material)
        REFERENCES materials (material_id, tipus_material)
        ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE materials_revista (
    material_id    INTEGER     NOT NULL,
    tipus_material VARCHAR(15) NOT NULL DEFAULT 'revista',
    issn           VARCHAR(9)  NOT NULL,
    numero         VARCHAR(20) NOT NULL,
    periodicitat   VARCHAR(20),
    CONSTRAINT pk_materials_revista PRIMARY KEY (material_id),
    CONSTRAINT uq_materials_revista_issn_num UNIQUE (issn, numero),   -- RN9
    CONSTRAINT chk_materials_revista_tipus CHECK (tipus_material = 'revista'),
    CONSTRAINT fk_materials_revista_material
        FOREIGN KEY (material_id, tipus_material)
        REFERENCES materials (material_id, tipus_material)
        ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE materials_audiollibre (
    material_id    INTEGER      NOT NULL,
    tipus_material VARCHAR(15)  NOT NULL DEFAULT 'audiollibre',
    durada_min     INTEGER      NOT NULL,
    narrador       VARCHAR(120),
    format_audio   VARCHAR(15),
    CONSTRAINT pk_materials_audiollibre PRIMARY KEY (material_id),
    CONSTRAINT chk_materials_audiollibre_tipus CHECK (tipus_material = 'audiollibre'),
    CONSTRAINT fk_materials_audiollibre_material
        FOREIGN KEY (material_id, tipus_material)
        REFERENCES materials (material_id, tipus_material)
        ON DELETE CASCADE ON UPDATE CASCADE
);

-- Regla 3: atribut multivaluat (R1)
CREATE TABLE subtitols_dvd (
    material_id INTEGER    NOT NULL,
    idioma      VARCHAR(5) NOT NULL,
    CONSTRAINT pk_subtitols_dvd PRIMARY KEY (material_id, idioma),
    CONSTRAINT fk_subtitols_dvd_material
        FOREIGN KEY (material_id) REFERENCES materials_dvd (material_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

-- Regla 3: atribut multivaluat (R12)
CREATE TABLE telefons_soci (
    soci_id INTEGER     NOT NULL,
    numero  VARCHAR(20) NOT NULL,
    tipus   VARCHAR(10) NOT NULL DEFAULT 'mobil',
    CONSTRAINT pk_telefons_soci PRIMARY KEY (soci_id, numero),
    CONSTRAINT chk_telefons_soci_tipus CHECK (tipus IN ('mobil','fix','feina')),
    CONSTRAINT fk_telefons_soci_soci
        FOREIGN KEY (soci_id) REFERENCES socis (soci_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

-- ---------------------------------------------------------------------
-- BLOC 3 · Sales i esdeveniments (R3, R4, R5)
-- ---------------------------------------------------------------------
CREATE TABLE sales (
    sala_id     INTEGER GENERATED BY DEFAULT AS IDENTITY,
    sucursal_id INTEGER     NOT NULL,
    nom         VARCHAR(80) NOT NULL,
    aforament   INTEGER     NOT NULL,
    planta      INTEGER     NOT NULL DEFAULT 0,
    accessible  BOOLEAN     NOT NULL DEFAULT TRUE,
    CONSTRAINT pk_sales PRIMARY KEY (sala_id),
    CONSTRAINT uq_sales_sucursal_nom UNIQUE (sucursal_id, nom),   -- R3, regla 8
    CONSTRAINT fk_sales_sucursal
        FOREIGN KEY (sucursal_id) REFERENCES sucursals (sucursal_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE tipus_esdeveniment (
    tipus_esdeveniment_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
    codi                  VARCHAR(30)  NOT NULL,
    nom                   VARCHAR(80)  NOT NULL,
    descripcio            VARCHAR(500),
    durada_estandard_min  INTEGER,
    CONSTRAINT pk_tipus_esdeveniment PRIMARY KEY (tipus_esdeveniment_id),
    CONSTRAINT uq_tipus_esdeveniment_codi UNIQUE (codi)
);

CREATE TABLE esdeveniments (
    esdeveniment_id       INTEGER GENERATED BY DEFAULT AS IDENTITY,
    titol                 VARCHAR(200) NOT NULL,
    descripcio            VARCHAR(2000),
    tipus_esdeveniment_id INTEGER      NOT NULL,
    sala_id               INTEGER,                       -- opcional: decisió D4
    inici                 TIMESTAMPTZ  NOT NULL,
    fi                    TIMESTAMPTZ  NOT NULL,
    places_ofertes        INTEGER      NOT NULL,
    estat                 VARCHAR(15)  NOT NULL DEFAULT 'programat',
    publicat              BOOLEAN      NOT NULL DEFAULT FALSE,
    CONSTRAINT pk_esdeveniments PRIMARY KEY (esdeveniment_id),
    CONSTRAINT fk_esdeveniments_tipus
        FOREIGN KEY (tipus_esdeveniment_id) REFERENCES tipus_esdeveniment (tipus_esdeveniment_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_esdeveniments_sala
        FOREIGN KEY (sala_id) REFERENCES sales (sala_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE ponents (
    ponent_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
    nom       VARCHAR(60)  NOT NULL,
    cognoms   VARCHAR(80)  NOT NULL,
    email     VARCHAR(120),
    biografia VARCHAR(1000),
    extern    BOOLEAN      NOT NULL DEFAULT TRUE,
    CONSTRAINT pk_ponents PRIMARY KEY (ponent_id),
    CONSTRAINT uq_ponents_email UNIQUE (email)
);

-- ---------------------------------------------------------------------
-- BLOC 4 · Regles 6, 7 i 9: inscripcions, informes, participacions
-- ---------------------------------------------------------------------
CREATE TABLE inscripcions (                           -- Regla 7 (N:M amb atributs)
    esdeveniment_id INTEGER     NOT NULL,
    soci_id         INTEGER     NOT NULL,
    data_inscripcio TIMESTAMPTZ NOT NULL DEFAULT now(),
    estat           VARCHAR(15) NOT NULL DEFAULT 'confirmada',
    acompanyants    INTEGER     NOT NULL DEFAULT 0,
    CONSTRAINT pk_inscripcions PRIMARY KEY (esdeveniment_id, soci_id),  -- R6
    CONSTRAINT fk_inscripcions_esdeveniment
        FOREIGN KEY (esdeveniment_id) REFERENCES esdeveniments (esdeveniment_id)
        ON DELETE CASCADE  ON UPDATE CASCADE,
    CONSTRAINT fk_inscripcions_soci
        FOREIGN KEY (soci_id) REFERENCES socis (soci_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE informes_esdeveniment (                  -- Regla 6 (1:1, opció B)
    esdeveniment_id   INTEGER      NOT NULL,
    assistents_reals  INTEGER      NOT NULL,
    valoracio_mitjana NUMERIC(3,2),
    observacions      VARCHAR(2000),
    data_redaccio     DATE         NOT NULL DEFAULT CURRENT_DATE,
    CONSTRAINT pk_informes_esdeveniment PRIMARY KEY (esdeveniment_id),
    CONSTRAINT fk_informes_esdeveniment_esdeveniment
        FOREIGN KEY (esdeveniment_id) REFERENCES esdeveniments (esdeveniment_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE participacions (                         -- Regla 9 (ternària resolta)
    esdeveniment_id INTEGER      NOT NULL,
    ponent_id       INTEGER      NOT NULL,
    rol             VARCHAR(25)  NOT NULL,
    honoraris       NUMERIC(8,2) NOT NULL DEFAULT 0,
    CONSTRAINT pk_participacions PRIMARY KEY (esdeveniment_id, ponent_id, rol),
    CONSTRAINT fk_participacions_esdeveniment
        FOREIGN KEY (esdeveniment_id) REFERENCES esdeveniments (esdeveniment_id)
        ON DELETE CASCADE  ON UPDATE CASCADE,
    CONSTRAINT fk_participacions_ponent
        FOREIGN KEY (ponent_id) REFERENCES ponents (ponent_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE esdeveniments_materials (                -- Regla 7 (N:M simple)
    esdeveniment_id INTEGER     NOT NULL,
    material_id     INTEGER     NOT NULL,
    paper           VARCHAR(15) NOT NULL DEFAULT 'recomanat',
    CONSTRAINT pk_esdeveniments_materials PRIMARY KEY (esdeveniment_id, material_id),
    CONSTRAINT fk_esdeveniments_materials_esdeveniment
        FOREIGN KEY (esdeveniment_id) REFERENCES esdeveniments (esdeveniment_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_esdeveniments_materials_material
        FOREIGN KEY (material_id) REFERENCES materials (material_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

-- ---------------------------------------------------------------------
-- BLOC 5 · Multes i pagaments (R10, R11)
-- ---------------------------------------------------------------------
CREATE TABLE multes (
    multa_id      INTEGER GENERATED BY DEFAULT AS IDENTITY,
    soci_id       INTEGER      NOT NULL,
    prestec_id    INTEGER,                    -- opcional: decisió D6
    motiu         VARCHAR(15)  NOT NULL,
    import        NUMERIC(6,2) NOT NULL,
    data_emissio  DATE         NOT NULL DEFAULT CURRENT_DATE,
    estat         VARCHAR(15)  NOT NULL DEFAULT 'pendent',
    CONSTRAINT pk_multes PRIMARY KEY (multa_id),
    CONSTRAINT uq_multes_prestec_motiu UNIQUE (prestec_id, motiu),  -- R10
    CONSTRAINT fk_multes_soci
        FOREIGN KEY (soci_id) REFERENCES socis (soci_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_multes_prestec
        FOREIGN KEY (prestec_id) REFERENCES prestecs (prestec_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE pagaments (                              -- Regla 8 (entitat feble)
    pagament_id    INTEGER GENERATED BY DEFAULT AS IDENTITY,
    multa_id       INTEGER      NOT NULL,
    data_pagament  TIMESTAMPTZ  NOT NULL DEFAULT now(),
    import         NUMERIC(6,2) NOT NULL,
    metode         VARCHAR(15)  NOT NULL,
    referencia     VARCHAR(50),
    CONSTRAINT pk_pagaments PRIMARY KEY (pagament_id),
    CONSTRAINT fk_pagaments_multa
        FOREIGN KEY (multa_id) REFERENCES multes (multa_id)
        ON DELETE RESTRICT ON UPDATE CASCADE       -- registre comptable: no cascada
);

-- ---------------------------------------------------------------------
-- BLOC 6 · Adaptació de les taules existents a la jerarquia
-- ---------------------------------------------------------------------
ALTER TABLE exemplars ADD COLUMN material_id  INTEGER;
ALTER TABLE exemplars ADD COLUMN num_exemplar INTEGER;
-- (migració: materials hereta els llibres; exemplars.material_id = antic llibre_id)
ALTER TABLE exemplars DROP CONSTRAINT fk_exemplars_llibre;
ALTER TABLE exemplars DROP COLUMN llibre_id;
ALTER TABLE exemplars ALTER COLUMN material_id SET NOT NULL;
ALTER TABLE exemplars
    ADD CONSTRAINT uq_exemplars_material_num UNIQUE (material_id, num_exemplar),
    ADD CONSTRAINT fk_exemplars_material
        FOREIGN KEY (material_id) REFERENCES materials (material_id)
        ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE reserves ADD COLUMN material_id INTEGER;
ALTER TABLE reserves DROP CONSTRAINT fk_reserves_llibre;
ALTER TABLE reserves DROP COLUMN llibre_id;
ALTER TABLE reserves ALTER COLUMN material_id SET NOT NULL;
ALTER TABLE reserves
    ADD CONSTRAINT fk_reserves_material
        FOREIGN KEY (material_id) REFERENCES materials (material_id)
        ON DELETE CASCADE ON UPDATE CASCADE;

DROP TABLE llibres;

CREATE VIEW llibres AS
SELECT m.material_id AS llibre_id, l.isbn, m.titol, m.autor_id,
       m.editorial, m.any_publicacio, m.idioma
  FROM materials m
  JOIN materials_llibre l ON l.material_id = m.material_id;

Resum de les accions referencials

Clau forana ON DELETE Motiu
sales.sucursal_id RESTRICT Coherent amb socis i exemplars; no s'esborra una sucursal en ús
materials.autor_id SET NULL Igual que l'antiga llibres.autor_id: el material sobreviu a l'autor
materials_*.material_id CASCADE Les subtaules no existeixen sense la superclasse
subtitols_dvd.material_id CASCADE Atribut multivaluat del DVD
telefons_soci.soci_id CASCADE Atribut multivaluat del soci
exemplars.material_id CASCADE Igual que l'antiga exemplars.llibre_id
esdeveniments.tipus_esdeveniment_id RESTRICT No s'esborra un tipus amb esdeveniments històrics
esdeveniments.sala_id RESTRICT Perdre la sala d'un esdeveniment passat destrueix C5
inscripcions.esdeveniment_id CASCADE La inscripció no existeix sense el seu esdeveniment
inscripcions.soci_id RESTRICT Fet històric amb valor estadístic (C5)
informes_esdeveniment.esdeveniment_id CASCADE L'informe no existeix sense el seu esdeveniment
participacions.esdeveniment_id CASCADE Igual
participacions.ponent_id RESTRICT Registre amb implicacions econòmiques
esdeveniments_materials.* CASCADE Relació auxiliar sense valor propi
multes.soci_id RESTRICT Registre econòmic
multes.prestec_id RESTRICT Registre econòmic
pagaments.multa_id RESTRICT Entitat feble, però comptable: mai cascada

L'última fila mereix atenció perquè contradiu la intuïció: pagaments és una entitat feble de multes i tot i així no és CASCADE. La forma de la relació suggereix l'acció; el valor de la dada la decideix.

  1. Revisió posterior: validar l'esquema resultant

Aplicar deu regles i quedar-se tan tranquil és un error. Hi ha tres comprovacions que es fan sempre.

Comprovació 1 — Tota consulta de l'enunciat es pot respondre

Recorrem les dotze consultes de 04-01 i escrivim el FROM ... JOIN de cadascuna. Un extracte de les tres menys evidents:

C5 — Ocupació mitjana de cada sala per sucursal i trimestre:

SELECT su.nom AS sucursal,
       s.nom  AS sala,
       DATE_TRUNC('quarter', e.inici) AS trimestre,
       ROUND(AVG(oc.places_ocupades::numeric / NULLIF(s.aforament, 0)) * 100, 1) AS ocupacio_pct
  FROM esdeveniments e
  JOIN sales s        ON s.sala_id = e.sala_id
  JOIN sucursals su   ON su.sucursal_id = s.sucursal_id
  JOIN v_esdeveniments_ocupacio oc ON oc.esdeveniment_id = e.esdeveniment_id
 WHERE e.estat = 'celebrat'
 GROUP BY su.nom, s.nom, DATE_TRUNC('quarter', e.inici)
 ORDER BY su.nom, s.nom, trimestre;
 sucursal |      sala         |       trimestre        | ocupacio_pct
----------+-------------------+------------------------+--------------
 Centre   | Sala Polivalent   | 2026-04-01 00:00:00+02 |         72.4
 Centre   | Sala Polivalent   | 2026-07-01 00:00:00+02 |         61.0
 Nord     | Sala Infantil     | 2026-04-01 00:00:00+02 |         88.7
(3 rows)

C7 — Socis amb deute pendent superior a 20 € (regla 4: el pendent és derivat):

SELECT s.soci_id,
       s.nom || ' ' || s.cognoms AS soci,
       SUM(m.import - COALESCE(p.pagat, 0)) AS deute
  FROM socis s
  JOIN multes m ON m.soci_id = s.soci_id AND m.estat = 'pendent'
  LEFT JOIN (SELECT multa_id, SUM(import) AS pagat
               FROM pagaments GROUP BY multa_id) p ON p.multa_id = m.multa_id
 GROUP BY s.soci_id, s.nom, s.cognoms
HAVING SUM(m.import - COALESCE(p.pagat, 0)) > 20
 ORDER BY deute DESC;
 soci_id |     soci      | deute
---------+---------------+-------
      15 | Ivan Pereda   | 27.40
      16 | Núria Bastos  | 22.00
(2 rows)

C10 — Els deu materials més prestats per tipus (aquesta és la que l'estratègia de jerarquia havia de fer possible):

SELECT m.tipus_material, m.titol, COUNT(*) AS prestecs
  FROM prestecs pr
  JOIN exemplars ex ON ex.exemplar_id = pr.exemplar_id
  JOIN materials m  ON m.material_id = ex.material_id
 GROUP BY m.tipus_material, m.material_id, m.titol
 ORDER BY prestecs DESC
 LIMIT 10;

Un sol JOIN a materials, sense UNION de quatre branques. És exactament el benefici que buscàvem en descartar l'estratègia 3.

Comprovació 2 — Cap taula sense clau primària

SELECT t.table_name
  FROM information_schema.tables t
  LEFT JOIN information_schema.table_constraints c
         ON c.table_name = t.table_name
        AND c.constraint_type = 'PRIMARY KEY'
 WHERE t.table_schema = 'public'
   AND t.table_type = 'BASE TABLE'
   AND c.constraint_name IS NULL;
 table_name
------------
(0 rows)

Zero files és la resposta correcta. Una taula sense clau primària admet duplicats exactes, no es pot referenciar i no es pot actualitzar fila a fila amb seguretat.

Comprovació 3 — Cap clau forana òrfena ni cap relació sense FK

Dues consultes. La primera, del catàleg, llista totes les claus foranes declarades per contrastar-les amb el diagrama:

SELECT conrelid::regclass  AS taula,
       conname             AS restriccio,
       confrelid::regclass AS referencia
  FROM pg_constraint
 WHERE contype = 'f'
 ORDER BY 1, 2;

La segona busca columnes que semblen clau forana pel seu nom però no la tenen declarada, que és l'oblit més comú:

SELECT c.table_name, c.column_name
  FROM information_schema.columns c
 WHERE c.table_schema = 'public'
   AND c.column_name LIKE '%\_id'
   AND NOT EXISTS (
        SELECT 1 FROM information_schema.key_column_usage k
          JOIN information_schema.table_constraints t
            ON t.constraint_name = k.constraint_name
         WHERE k.table_name = c.table_name
           AND k.column_name = c.column_name
           AND t.constraint_type IN ('FOREIGN KEY','PRIMARY KEY'))
 ORDER BY 1, 2;
 table_name | column_name
------------+-------------
(0 rows)

Llista de comprovació final

# Comprovació Compleix?
1 Les 12 consultes de l'enunciat tenen resposta Sí
2 Tota taula té clau primària Sí
3 Tota columna *_id és PK o FK declarada Sí
4 Tota entitat del diagrama té taula Sí, 21 de 21
5 Tota relació del diagrama està representada Sí
6 Els atributs multivaluats són en taules pròpies Sí (2)
7 Els atributs derivats NO són columnes Sí (5, en vistes)
8 Les claus naturals tenen UNIQUE Sí (isbn, issn+numero, codi, email, nom de sala)
9 Cada acció referencial està raonada Sí (taula de l'apartat 12)
10 Les regles RN1–RN10 estan assignades Pendent → 04-04

Nou de deu. La desena és la lliçó següent.

Errors Habituals i Consells

Posar la clau forana al costat 1. L'error estructural més greu i el més fàcil de detectar: si la FK admetés diversos valors, és al costat equivocat. Rellegeix la cardinalitat del diagrama i pregunta't "quants valors necessito desar aquí?".

Crear una taula d'unió per a una relació 1:N. Funciona, però permet estats que el negoci prohibeix: si esdeveniments_sales fos una taula d'unió, res no impediria dues files per al mateix esdeveniment, és a dir, un esdeveniment en dues sales. L'estructura ha de fer impossible el que és prohibit, no només permetre el que és correcte.

Oblidar els atributs de la relació N:M. Es crea inscripcions (esdeveniment_id, soci_id) i es dóna per acabat. La data, l'estat i els acompanyants no tenen lloc, i acaben apareixent a socis o a esdeveniments, on no signifiquen res.

Desar un derivat sense adonar-se'n. esdeveniments.sucursal_id "per no fer el JOIN" crea la possibilitat que un esdeveniment digui ser a Centre mentre la seva sala és a Nord. Si un valor es pot deduir de l'esquema, desar-lo és crear una contradicció potencial.

Aplicar CASCADE per comoditat. ON DELETE CASCADE en totes les claus foranes evita errors en desenvolupar i esborra mig esquema en producció. Un DELETE FROM socis WHERE soci_id = 14 amb cascades per tot arreu s'emporta per davant préstecs, inscripcions i multes. Cada acció es decideix per separat, amb el criteri de 02-06.

Descompondre atributs compostos que ningú no consultarà per parts. Quatre columnes d'adreça per a un ponent extern l'adreça del qual només s'imprimeix en un sobre és feina permanent a canvi de res.

Triar taula única per a una jerarquia amb molts atributs específics. És la temptació de la simplicitat, i produeix onze columnes nul·les i onze CHECK condicionals que ningú no manté. Compta els atributs específics abans de decidir: amb més de dos o tres per subtipus, gairebé sempre guanya la taula per subclasse.

Consell: aplica les regles en ordre i no improvisis. L'algorisme funciona precisament perquè cada pas es recolza en l'anterior. Saltar-se la regla 1 i començar per les relacions produeix FK a taules que encara no existeixen.

Consell: anota al costat de cada taula quina regla la va generar. L'script de l'apartat 12 porta aquests comentaris. Quan algú pregunti per què subtitols_dvd és una taula a part, la resposta està escrita: regla 3, atribut multivaluat, R1.

Consell: crea l'esquema en una base buida i executa'l sencer abans de donar-lo per bo. Els errors d'ordre de creació, de noms de restricció duplicats i de tipus incompatibles a les FK apareixen en segons. Un script d'esquema que ningú no ha executat és una hipòtesi.

Exercicis

Exercici 1 — Aplicar les regles a un fragment nou

BiblioRed incorpora a la v1.1 el fragment següent:

R16 — Punts de recollida. A més de a les quatre sucursals, els materials reservats es poden recollir en punts de recollida (taquilles automàtiques) instal·lats en centres cívics. Cada punt té un codi, una adreça (carrer, número, codi postal), un nombre de taquilles i la sucursal que l'abasteix. Un soci, en fer una reserva, tria on recollir-la: en una sucursal o en un punt de recollida. Cada punt té a més un horari d'obertura per dia de la setmana (dilluns a diumenge, amb hora d'obertura i de tancament; alguns dies tanca).

Aplica les regles corresponents i escriu el SQL. Indica explícitament quina regla apliques a cada pas, com resols el "una sucursal o un punt de recollida" i quines accions referencials tries amb el seu motiu.

Exercici 2 — Triar l'estratègia d'una jerarquia

BiblioRed vol modelar les notificacions que envia als socis. N'hi ha de tres tipus:

  • Recordatori de devolució: porta el préstec associat i els dies que falten.
  • Avís de reserva disponible: porta la reserva associada i la data límit de recollida.
  • Recordatori d'esdeveniment: porta l'esdeveniment associat i les hores que falten.

Totes comparteixen: destinatari (soci), canal (email, sms, push), data d'enviament, estat (pendent, enviada, fallida) i text del missatge. Se n'envien unes 3.000 al mes, es consulten gairebé sempre totes juntes ("les notificacions d'aquest soci, ordenades per data") i es purguen als sis mesos.

  1. Determina si la jerarquia és disjunta/solapada i total/parcial.
  2. Tria una de les tres estratègies i justifica-la amb almenys tres arguments de la taula comparativa.
  3. Escriu el SQL de l'estratègia triada.

Exercici 3 — Detectar errors de transformació

Un company lliura aquesta part de l'esquema. Localitza almenys cinc errors de transformació, indica quina regla s'ha incomplert i escriu la versió corregida.

CREATE TABLE esdeveniments (
    esdeveniment_id SERIAL PRIMARY KEY,
    titol           VARCHAR(200),
    sala_id         INTEGER REFERENCES sales(sala_id),
    aforament_sala  INTEGER,
    sucursal_id     INTEGER REFERENCES sucursals(sucursal_id),
    ponent1_id      INTEGER REFERENCES ponents(ponent_id),
    ponent2_id      INTEGER REFERENCES ponents(ponent_id),
    inici           TIMESTAMPTZ,
    places_ofertes  INTEGER,
    places_lliures  INTEGER,
    materials       VARCHAR(300)
);

CREATE TABLE inscripcions (
    esdeveniment_id INTEGER REFERENCES esdeveniments(esdeveniment_id) ON DELETE CASCADE,
    soci_id         INTEGER REFERENCES socis(soci_id)                 ON DELETE CASCADE
);

Solucions

Solució a l'Exercici 1

Regla 1 (entitat forta) + Regla 2 (atribut compost):

CREATE TABLE punts_recollida (
    punt_id         INTEGER GENERATED BY DEFAULT AS IDENTITY,
    codi            VARCHAR(15) NOT NULL,
    adr_carrer      VARCHAR(120) NOT NULL,
    adr_numero      VARCHAR(10),
    adr_codi_postal VARCHAR(5)  NOT NULL,
    num_taquilles   INTEGER     NOT NULL,
    sucursal_id     INTEGER     NOT NULL,          -- Regla 5: 1:N
    CONSTRAINT pk_punts_recollida PRIMARY KEY (punt_id),
    CONSTRAINT uq_punts_recollida_codi UNIQUE (codi),
    CONSTRAINT fk_punts_recollida_sucursal
        FOREIGN KEY (sucursal_id) REFERENCES sucursals (sucursal_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);

L'adreça es descompon (regla 2) perquè l'enunciat l'enumera trossejada i perquè el web ja busca per codi postal (R13). sucursal_id va aquí (regla 5, costat N) i és RESTRICT: una sucursal amb punts abastits no s'esborra.

Regla 3 (atribut multivaluat): l'horari. És multivaluat —set valors simultanis— i compost —obertura i tancament—. Es converteix en taula, amb el dia com a identificador parcial:

CREATE TABLE horaris_punt (
    punt_id       INTEGER NOT NULL,
    dia_setmana   INTEGER NOT NULL,     -- 1 = dilluns ... 7 = diumenge
    hora_obertura TIME,                 -- NULL = tancat aquell dia
    hora_tancament TIME,
    CONSTRAINT pk_horaris_punt PRIMARY KEY (punt_id, dia_setmana),
    CONSTRAINT chk_horaris_dia CHECK (dia_setmana BETWEEN 1 AND 7),
    CONSTRAINT fk_horaris_punt
        FOREIGN KEY (punt_id) REFERENCES punts_recollida (punt_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

CASCADE perquè l'horari no existeix fora del seu punt.

El "una sucursal o un punt": relació exclusiva. És el cas interessant de l'exercici i admet dues solucions:

Opció 1 — dues claus foranes anul·lables amb un CHECK d'exclusió mútua:

ALTER TABLE reserves ADD COLUMN recollida_sucursal_id INTEGER;
ALTER TABLE reserves ADD COLUMN recollida_punt_id     INTEGER;
ALTER TABLE reserves
  ADD CONSTRAINT fk_reserves_recollida_sucursal
      FOREIGN KEY (recollida_sucursal_id) REFERENCES sucursals (sucursal_id)
      ON DELETE RESTRICT,
  ADD CONSTRAINT fk_reserves_recollida_punt
      FOREIGN KEY (recollida_punt_id) REFERENCES punts_recollida (punt_id)
      ON DELETE RESTRICT,
  ADD CONSTRAINT chk_reserves_recollida_exclusiva
      CHECK ((recollida_sucursal_id IS NOT NULL AND recollida_punt_id IS NULL)
          OR (recollida_sucursal_id IS NULL AND recollida_punt_id IS NOT NULL));

Opció 2 — generalitzar: crear una entitat punts_servei de la qual sucursals i punts de recollida siguin subtipus (regla 10), i que reserves referenciï amb una sola FK.

L'opció 1 és més simple i suficient amb dues alternatives. L'opció 2 és preferible si demà apareixen més llocs de recollida (bibliobús, oficina de correus). Per a la v1.1 es tria l'opció 1, i s'anota al registre de decisions que la generalització és el pla si apareix un tercer tipus.

Solució a l'Exercici 2

1. Naturalesa de la jerarquia. Disjunta: una notificació és d'un sol tipus. Total: tota notificació és d'un dels tres tipus; no existeixen notificacions genèriques.

2. Estratègia triada: taula única. Arguments de la taula comparativa:

  • Pocs atributs específics: cada subtipus aporta dues columnes (una FK i un número). Amb taula per subclasse hi hauria quatre taules per desar sis columnes en total, i tres d'aquestes taules serien gairebé trivials.
  • El patró de consulta dominant és sobre la superclasse: "les notificacions d'aquest soci ordenades per data" no necessita els detalls del subtipus. Amb taula per subclasse aquesta consulta, la més freqüent amb diferència, exigiria un LEFT JOIN triple o tres consultes.
  • Volum i cicle de vida: 3.000 al mes amb purga a sis mesos són unes 18.000 files vives. El malbaratament de dues columnes nul·les per fila és irrellevant, i la purga és un sol DELETE sobre una taula en lloc de quatre coordinats.
  • Ningú no referencia les notificacions des de fora, així que l'argument decisiu del cas dels materials aquí no s'aplica.

El preu conegut és que no es pot exigir NOT NULL a les FK específiques i cal suplir-ho amb CHECK condicionals. Amb tres subtipus i una columna obligatòria cadascun, són tres CHECK perfectament manejables.

3. SQL:

CREATE TABLE notificacions (
    notificacio_id INTEGER GENERATED BY DEFAULT AS IDENTITY,
    soci_id        INTEGER      NOT NULL,
    tipus          VARCHAR(20)  NOT NULL,
    canal          VARCHAR(10)  NOT NULL,
    data_enviament TIMESTAMPTZ  NOT NULL DEFAULT now(),
    estat          VARCHAR(12)  NOT NULL DEFAULT 'pendent',
    missatge       VARCHAR(500) NOT NULL,
    -- atributs específics per subtipus
    prestec_id      INTEGER,
    dies_restants   INTEGER,
    reserva_id      INTEGER,
    data_limit      DATE,
    esdeveniment_id INTEGER,
    hores_restants  INTEGER,
    CONSTRAINT pk_notificacions PRIMARY KEY (notificacio_id),
    CONSTRAINT chk_notificacions_tipus
        CHECK (tipus IN ('recordatori_devolucio','avis_reserva','recordatori_esdeveniment')),
    CONSTRAINT chk_notificacions_canal CHECK (canal IN ('email','sms','push')),
    CONSTRAINT chk_notificacions_estat
        CHECK (estat IN ('pendent','enviada','fallida')),
    -- coherència entre discriminant i atributs específics
    CONSTRAINT chk_notif_devolucio
        CHECK (tipus <> 'recordatori_devolucio' OR prestec_id IS NOT NULL),
    CONSTRAINT chk_notif_reserva
        CHECK (tipus <> 'avis_reserva' OR reserva_id IS NOT NULL),
    CONSTRAINT chk_notif_esdeveniment
        CHECK (tipus <> 'recordatori_esdeveniment' OR esdeveniment_id IS NOT NULL),
    CONSTRAINT fk_notificacions_soci
        FOREIGN KEY (soci_id) REFERENCES socis (soci_id) ON DELETE CASCADE,
    CONSTRAINT fk_notificacions_prestec
        FOREIGN KEY (prestec_id) REFERENCES prestecs (prestec_id) ON DELETE CASCADE,
    CONSTRAINT fk_notificacions_reserva
        FOREIGN KEY (reserva_id) REFERENCES reserves (reserva_id) ON DELETE CASCADE,
    CONSTRAINT fk_notificacions_esdeveniment
        FOREIGN KEY (esdeveniment_id) REFERENCES esdeveniments (esdeveniment_id) ON DELETE CASCADE
);

Fixa't en el contrast amb la decisió dels materials: la mateixa pregunta, resposta diferent, i totes dues correctes. L'estratègia no depèn de la teoria de la jerarquia, sinó del nombre d'atributs específics, del patró de consulta i de si algú referencia els subtipus.

Solució a l'Exercici 3

# Error Regla incomplerta Dany concret
1 aforament_sala copiat a esdeveniments Regla 4 (derivat) i "una cosa, un lloc" En reformar una sala, 300 esdeveniments queden amb l'aforament antic; RN1 es valida contra una dada obsoleta
2 sucursal_id a esdeveniments Regla 4 (derivat) La sucursal es dedueix via sala_id. Es pot crear un esdeveniment que digui ser a Centre amb una sala de Nord: cicle al diagrama (04-02)
3 ponent1_id, ponent2_id Regla 7 (N:M) R7 diu "diversos ponents" i "diversos papers per persona". Amb dues columnes no hi cap el tercer, no hi ha on posar el rol ni els honoraris, i "en quants esdeveniments va participar Elena Roig?" necessita UNION
4 places_lliures com a columna Regla 4 (derivat) Es desincronitza tan bon punt algú cancel·la; C2 passa a mentir
5 materials VARCHAR(300) Regla 7 i antipatró de llista amb comes Sense FK, sense integritat, sense poder respondre "en quins esdeveniments s'ha comentat aquest llibre?"
6 inscripcions sense clau primària Regla 7 i comprovació 2 Un soci es pot inscriure infinites vegades al mateix esdeveniment, violant R6
7 inscripcions sense atributs propis Regla 7 No hi ha on desar data, estat ni acompanyants (R6)
8 inscripcions.soci_id ON DELETE CASCADE Criteri de 02-06 Esborrar un soci destrueix l'historial d'assistència i falseja C5
9 Falten NOT NULL a titol, inici, places_ofertes Regla 1 + participació del diagrama Es poden crear esdeveniments sense títol ni data

Versió corregida:

CREATE TABLE esdeveniments (
    esdeveniment_id       INTEGER GENERATED BY DEFAULT AS IDENTITY,
    titol                 VARCHAR(200) NOT NULL,
    tipus_esdeveniment_id INTEGER      NOT NULL,
    sala_id               INTEGER,
    inici                 TIMESTAMPTZ  NOT NULL,
    fi                    TIMESTAMPTZ  NOT NULL,
    places_ofertes        INTEGER      NOT NULL,
    estat                 VARCHAR(15)  NOT NULL DEFAULT 'programat',
    publicat              BOOLEAN      NOT NULL DEFAULT FALSE,
    CONSTRAINT pk_esdeveniments PRIMARY KEY (esdeveniment_id),
    CONSTRAINT fk_esdeveniments_tipus FOREIGN KEY (tipus_esdeveniment_id)
        REFERENCES tipus_esdeveniment (tipus_esdeveniment_id) ON DELETE RESTRICT,
    CONSTRAINT fk_esdeveniments_sala FOREIGN KEY (sala_id)
        REFERENCES sales (sala_id) ON DELETE RESTRICT
);
-- aforament_sala, sucursal_id i places_lliures: eliminats (derivats, vista v_esdeveniments_ocupacio)
-- ponent1_id / ponent2_id: substituïts per la taula participacions
-- materials: substituït per la taula esdeveniments_materials

CREATE TABLE inscripcions (
    esdeveniment_id INTEGER     NOT NULL,
    soci_id         INTEGER     NOT NULL,
    data_inscripcio TIMESTAMPTZ NOT NULL DEFAULT now(),
    estat           VARCHAR(15) NOT NULL DEFAULT 'confirmada',
    acompanyants    INTEGER     NOT NULL DEFAULT 0,
    CONSTRAINT pk_inscripcions PRIMARY KEY (esdeveniment_id, soci_id),
    CONSTRAINT fk_inscripcions_esdeveniment FOREIGN KEY (esdeveniment_id)
        REFERENCES esdeveniments (esdeveniment_id) ON DELETE CASCADE,
    CONSTRAINT fk_inscripcions_soci FOREIGN KEY (soci_id)
        REFERENCES socis (soci_id) ON DELETE RESTRICT
);

Conclusió

Aquesta lliçó ha convertit un diagrama en un esquema executable mitjançant un algorisme de deu regles.

  • Regles 1 a 4 (atributs): cada entitat forta és una taula amb la seva PK subrogada i UNIQUE sobre la clau natural; els atributs compostos es descomponen en columnes —llevat que ningú no consulti les parts—; els multivaluats es converteixen sempre en taula a part, cosa que elimina d'arrel els antipatrons de columnes numerades i llistes amb comes; i els derivats no es desen, amb dues excepcions anomenades: el rendiment (05-04) i els fets històrics que s'han de congelar.
  • Regla 5 (1:N): la clau forana va sempre al costat N, perquè és l'únic que admet un sol valor. La participació del diagrama es tradueix literalment en NOT NULL o la seva absència.
  • Regla 6 (1:1): tres opcions. Fusionar si tots dos costats són totals; FK amb PK compartida al costat opcional si un és parcial —el que s'ha triat per a informes_esdeveniment, perquè distingeix "sense redactar" de "zero assistents"—; taula intermèdia només si tots dos són parcials.
  • Regla 7 (N:M): taula d'unió amb dues FK i, sobretot, amb els atributs propis de la relació, que és el que més s'oblida. La PK composta implementa la regla de negoci sense una línia de codi.
  • Regla 8 (entitat feble): PK composta amb la del propietari. A la pràctica, una entitat feble pot portar clau subrogada sempre que la seva clau natural composta es declari UNIQUE: el que no és negociable és la unicitat.
  • Regla 9 (ternària): tres FK, però abans la prova de descomposició. Quatre de cada cinc relacions ternàries aparents són una altra cosa; la de BiblioRed va resultar ser una N:M amb el rol a la clau.
  • Regla 10 (jerarquia): tres estratègies amb una taula comparativa. BiblioRed va triar taula per subclasse perquè exemplars, prestecs i reserves necessiten una entitat comuna, perquè cada subtipus té atributs obligatoris propis i perquè afegir un tipus nou no toca res existent. La coherència del discriminant es tanca amb una clau forana composta (material_id, tipus_material).
  • La compatibilitat amb el codi existent es va resoldre convertint llibres en una vista: l'exemple més net d'independència lògica de tot el curs.
  • Les accions referencials es van raonar una a una, i la conclusió és que la forma de la relació suggereix l'acció, però el valor de la dada la decideix: inscripcions.soci_id és RESTRICT mentre que reserves.soci_id és CASCADE, i pagaments és una entitat feble que mai no ha de cascadejar perquè és un registre comptable.
  • La revisió posterior —dotze consultes respostes, cap taula sense clau primària, cap columna *_id sense FK declarada— va tancar nou dels deu punts de la llista de comprovació.

Queda el desè, i és el més important: les deu regles de negoci RN1–RN10 continuen sense ser enlloc de l'esquema. Res no impedeix avui una multa de −40 €, un esdeveniment que acaba abans de començar, un aforament de zero o un estat 'confimada' amb una errada. I els tipus de dades són provisionals: hi ha INTEGER on n'hi hauria prou amb SMALLINT, VARCHAR(15) posats a ull, i l'import de les multes és en NUMERIC per bones raons que encara no hem explicat.

A la lliçó següent, 04-04 Tipus de Dades i Restriccions, revisem l'esquema columna per columna: quin tipus enter triar i quan es queda curt, per què els diners mai no van en coma flotant —amb una demostració que sorprèn—, TIMESTAMP enfront de TIMESTAMPTZ i el problema dels fusos horaris, ENUM enfront de taula de catàleg enfront de CHECK, la intercalació que decideix si "Àngels" apareix abans o després d'"Angel" en cercar títols, i el catàleg complet de restriccions: NOT NULL, DEFAULT, UNIQUE amb el seu comportament sorprenent davant dels NULL, CHECK d'una i diverses columnes, columnes generades, dominis reutilitzables i com afegir restriccions a una taula que ja té dades sense blocar-la. El resultat serà la versió definitiva i blindada de l'esquema de BiblioRed.

◄ Anterior
◄
Següent ►
►
El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència
© Copyright 2026. Tots els drets reservats