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

Exercicis de Disseny d'Esquemes

Següent ►
►

A la lliçó anterior consultaves un esquema que ja existia. Aquí no hi ha esquema: hi ha un client que t'explica el que necessita, amb el desordre amb què ho expliquen els clients, i la teva feina és convertir aquell relat en taules, claus i restriccions que no es puguin trencar.

Com treballar aquesta lliçó. Cada exercici és un cas complet, no una pregunta d'un minut. Llegeix l'enunciat sencer, agafa paper —o un editor de text— i fes els quatre passos pel teu compte abans de mirar res:

  1. Identificar entitats i relacions: subratlla els substantius de l'enunciat, decideix quins són entitats i quins atributs, i anota la cardinalitat de cada relació.
  2. Dibuixar el diagrama ER amb notació de pota de gall (pots fer servir mermaid, paper o qualsevol eina).
  3. Escriure el CREATE TABLE aplicant les deu regles de transformació de la lliçó 04-03, amb les seves claus foranes i les seves accions ON DELETE / ON UPDATE.
  4. Afegir les restriccions —NOT NULL, UNIQUE, CHECK, DEFAULT, dominis, EXCLUDE— que codifiquen les regles de negoci de l'enunciat.

Només després compara amb la solució proposada. És normal que el teu esquema no sigui idèntic: en disseny gairebé mai no hi ha una única resposta correcta, sinó respostes defensables i respostes que trenquen un requisit. Per això cada solució acaba amb un apartat de decisions discutibles, on s'explica quines alternatives també serien vàlides i què es guanya i es perd amb cadascuna. I al final de la lliçó hi ha una rúbrica d'autoavaluació amb la qual pots puntuar el teu propi disseny.

En aquesta lliçó no es fa anàlisi formal de dependències funcionals ni se citen formes normals: això és exactament la matèria de 07-03. Aquí dissenyem bé des del principi; allà diagnostiquem i arreglem dissenys que ja han sortit malament.

Abans de Començar

No necessites cap joc de dades carregat: quatre dels cinc casos són dominis nous, i el cinquè es recolza en l'esquema de BiblioRed que ja coneixes. Sí que necessites tenir a mà:

  • Les deu regles de transformació ER → relacional (lliçó 04-03), en especial les de relacions N:M, entitats febles, jerarquies de generalització i relacions ternàries.
  • El catàleg de restriccions de la lliçó 04-04: CHECK, UNIQUE sobre diverses columnes, UNIQUE parcial mitjançant índex, dominis (CREATE DOMAIN), columnes generades i EXCLUDE amb btree_gist.
  • Una sessió psql per executar els teus CREATE TABLE. No donis per bo un esquema que no has executat: la meitat dels errors de disseny els detecta el mateix motor en crear les taules.

Per a l'exercici 2 convé tenir present l'esquema de BiblioRed, en particular sucursals, materials, exemplars, socis i prestecs.

Si penses fer servir restriccions EXCLUDE, activa l'extensió una sola vegada per base de dades:

CREATE EXTENSION IF NOT EXISTS btree_gist;

SQLite. No admet CREATE DOMAIN, ni EXCLUDE, ni ALTER TABLE ADD CONSTRAINT, i només aplica les claus foranes si actives PRAGMA foreign_keys = ON. Els CHECK sí que funcionen. Allà on una solució faci servir una cosa exclusiva de PostgreSQL, s'indica l'alternativa portable.

Contingut

  1. Exercici 1 — Bàsic: videoclub de barri
  2. Exercici 2 — Intermedi: préstec interbibliotecari a BiblioRed
  3. Exercici 3 — Intermedi: plataforma de cursos en línia
  4. Exercici 4 — Avançat: taller mecànic amb jerarquia i relació ternària
  5. Exercici 5 — Avançat: tarifes amb vigència temporal
  6. Errors habituals i consells
  7. Exercicis de reforç
  8. Rúbrica d'autoavaluació

Exercici 1: Videoclub de barri

Dificultat: Bàsic

Enunciat. «Cinema Vallmar» és un videoclub que sobreviu llogant pel·lícules en format físic. El seu propietari t'explica el següent:

«Tinc unes 3.000 pel·lícules. De cadascuna en guardo el títol, l'any, la durada en minuts i la classificació per edats. Cada pel·lícula és d'un gènere —drama, comèdia, documental...— encara que n'hi ha algunes que són de dos, i m'agradaria poder buscar-les pels dos. De cada pel·lícula tinc entre una i sis còpies físiques; cada còpia té una etiqueta enganxada amb un codi, un format (DVD o Blu-ray) i un estat, perquè algunes estan ratllades i no les llogo. Els clients es fan socis amb nom, telèfon i correu; el correu no es pot repetir. Quan algú lloga una còpia apunto la data, la data de devolució prevista i, quan la porta, la real. Un mateix soci pot tenir diverses còpies llogades alhora, però una còpia només pot estar llogada a una persona. També m'agradaria poder buscar per actor: cada pel·lícula té diversos actors i cada actor surt en diverses pel·lícules, i m'interessa saber quin personatge interpretava.»

Necessita poder respondre: quines còpies d'una pel·lícula estan disponibles ara mateix, què té llogat un soci, quines pel·lícules hi ha d'un gènere, en quines pel·lícules ha treballat un actor i quins lloguers estan vençuts.

Pista. Hi ha dues relacions N:M a l'enunciat, i una d'elles té un atribut propi.

Solució

Pas 1 — Entitats i relacions

Entitat Justificació
pellicules Té atributs propis i es referencia des de diversos llocs
generes Un gènere és una entitat, no un text lliure: cal buscar-hi
actors Té identitat pròpia i es repeteix entre pel·lícules
copies L'objecte físic que es lloga; no és el mateix que la pel·lícula
socis Clients
lloguers El fet que una còpia surti de la botiga

Relacions:

  • pellicules N:M generes → taula intermèdia pellicules_generes.
  • pellicules N:M actors, amb atribut personatge → taula intermèdia repartiment.
  • pellicules 1:N copies (una pel·lícula té entre 1 i 6 còpies).
  • socis 1:N lloguers, copies 1:N lloguers.

La distinció clau del cas és pel·lícula enfront de còpia. La pel·lícula és l'obra; la còpia és el disc de plàstic amb una etiqueta. El soci no lloga «Casablanca»: lloga la còpia CV-0412. Confondre-les és l'error de disseny més freqüent en aquest domini, i és exactament la mateixa distinció que hi ha a BiblioRed entre materials i exemplars.

Pas 2 — Diagrama ER

erDiagram
    PELLICULES ||--o{ COPIES : "existeix en"
    PELLICULES ||--o{ PELLICULES_GENERES : ""
    GENERES    ||--o{ PELLICULES_GENERES : ""
    PELLICULES ||--o{ REPARTIMENT : ""
    ACTORS     ||--o{ REPARTIMENT : ""
    COPIES     ||--o{ LLOGUERS : "es lloga en"
    SOCIS      ||--o{ LLOGUERS : "fa"

    PELLICULES {
        int  pellicula_id PK
        text titol
        int  any_
        int  durada_min
        text classificacio
    }
    GENERES {
        int  genere_id PK
        text nom UK
    }
    PELLICULES_GENERES {
        int pellicula_id PK_FK
        int genere_id PK_FK
    }
    ACTORS {
        int  actor_id PK
        text nom
        text cognoms
    }
    REPARTIMENT {
        int  pellicula_id PK_FK
        int  actor_id PK_FK
        text personatge PK
    }
    COPIES {
        int  copia_id PK
        text codi UK
        int  pellicula_id FK
        text format
        text estat
    }
    SOCIS {
        int  soci_id PK
        text nom
        text email UK
        text telefon
        bool actiu
    }
    LLOGUERS {
        int  lloguer_id PK
        int  copia_id FK
        int  soci_id FK
        date data_lloguer
        date data_prevista
        date data_devolucio
    }

Pas 3 — CREATE TABLE

CREATE TABLE generes (
    genere_id SERIAL PRIMARY KEY,
    nom       VARCHAR(40) NOT NULL UNIQUE
);

CREATE TABLE pellicules (
    pellicula_id   SERIAL PRIMARY KEY,
    titol          VARCHAR(200) NOT NULL,
    any_           SMALLINT     NOT NULL,
    durada_min     SMALLINT     NOT NULL,
    classificacio  VARCHAR(5)   NOT NULL
);

CREATE TABLE pellicules_generes (
    pellicula_id INTEGER NOT NULL REFERENCES pellicules(pellicula_id) ON DELETE CASCADE,
    genere_id    INTEGER NOT NULL REFERENCES generes(genere_id)       ON DELETE RESTRICT,
    PRIMARY KEY (pellicula_id, genere_id)
);

CREATE TABLE actors (
    actor_id SERIAL PRIMARY KEY,
    nom      VARCHAR(60) NOT NULL,
    cognoms  VARCHAR(80) NOT NULL
);

CREATE TABLE repartiment (
    pellicula_id INTEGER NOT NULL REFERENCES pellicules(pellicula_id) ON DELETE CASCADE,
    actor_id     INTEGER NOT NULL REFERENCES actors(actor_id)         ON DELETE RESTRICT,
    personatge   VARCHAR(80) NOT NULL,
    PRIMARY KEY (pellicula_id, actor_id, personatge)
);

CREATE TABLE copies (
    copia_id     SERIAL PRIMARY KEY,
    codi         VARCHAR(12) NOT NULL UNIQUE,
    pellicula_id INTEGER     NOT NULL REFERENCES pellicules(pellicula_id) ON DELETE RESTRICT,
    format       VARCHAR(10) NOT NULL,
    estat        VARCHAR(12) NOT NULL DEFAULT 'disponible'
);

CREATE TABLE socis (
    soci_id  SERIAL PRIMARY KEY,
    nom      VARCHAR(100) NOT NULL,
    email    VARCHAR(120) NOT NULL UNIQUE,
    telefon  VARCHAR(15),
    actiu    BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE lloguers (
    lloguer_id     SERIAL PRIMARY KEY,
    copia_id       INTEGER NOT NULL REFERENCES copies(copia_id) ON DELETE RESTRICT,
    soci_id        INTEGER NOT NULL REFERENCES socis(soci_id)   ON DELETE RESTRICT,
    data_lloguer   DATE NOT NULL DEFAULT CURRENT_DATE,
    data_prevista  DATE NOT NULL,
    data_devolucio DATE
);

Pas 4 — Restriccions que codifiquen les regles de negoci

ALTER TABLE pellicules
    ADD CONSTRAINT ck_pellicules_any    CHECK (any_ BETWEEN 1888 AND 2100),
    ADD CONSTRAINT ck_pellicules_durada CHECK (durada_min BETWEEN 1 AND 600),
    ADD CONSTRAINT ck_pellicules_classif CHECK (classificacio IN ('TP','7','12','16','18'));

ALTER TABLE copies
    ADD CONSTRAINT ck_copies_format CHECK (format IN ('DVD','Blu-ray')),
    ADD CONSTRAINT ck_copies_estat  CHECK (estat IN ('disponible','llogada','malmesa','retirada'));

ALTER TABLE lloguers
    ADD CONSTRAINT ck_llog_prevista   CHECK (data_prevista > data_lloguer),
    ADD CONSTRAINT ck_llog_devolucio  CHECK (data_devolucio IS NULL
                                          OR data_devolucio >= data_lloguer);

-- Regla dura: una còpia no pot estar llogada a dues persones alhora.
-- Un índex UNIQUE PARCIAL sobre els lloguers oberts ho garanteix.
CREATE UNIQUE INDEX uq_lloguer_obert
    ON lloguers (copia_id)
    WHERE data_devolucio IS NULL;

-- Consulta més freqüent del taulell: còpies disponibles d'una pel·lícula
CREATE INDEX idx_copies_pellicula ON copies (pellicula_id) WHERE estat = 'disponible';

Resultat esperat

L'esquema ha de respondre les cinc consultes de l'enunciat. Comprovació ràpida:

Pregunta del client Consulta que la respon
Còpies disponibles d'una pel·lícula SELECT ... FROM copies WHERE pellicula_id = ? AND estat='disponible'
Què té llogat un soci lloguers JOIN copies JOIN pellicules WHERE soci_id=? AND data_devolucio IS NULL
Pel·lícules d'un gènere pellicules JOIN pellicules_generes JOIN generes WHERE g.nom=?
Pel·lícules d'un actor repartiment JOIN pellicules WHERE actor_id=?
Lloguers vençuts WHERE data_devolucio IS NULL AND data_prevista < CURRENT_DATE

Explicació i decisions discutibles

Per què generes és una taula i no una columna de text. El propietari va dir «n'hi ha algunes que són de dos gèneres». Això descarta de ple una columna genere VARCHAR(40) i descarta amb més força encara l'antipatró de la llista amb comes ('drama,comedia'), que ja vam veure a 04-01: fa impossible l'índex, impossible el JOIN i impossible garantir que el text estigui ben escrit. Una taula generes amb UNIQUE (nom) impedeix a més que hi convisquin «Documental», «documental» i «documentals».

La clau primària de repartiment inclou personatge. És una decisió discutible i val la pena entendre per què. Amb PRIMARY KEY (pellicula_id, actor_id), un actor que interpreta dos papers a la mateixa pel·lícula —bessons, dobles papers— seria impossible de registrar. Incloure personatge a la clau ho permet. L'alternativa igualment vàlida és posar una clau substituta repartiment_id SERIAL i un UNIQUE (pellicula_id, actor_id, personatge); l'efecte és el mateix i les claus foranes cap a repartiment queden més curtes si algun dia calen.

Les accions referencials no són totes iguals, i això és deliberat:

Clau forana Acció Motiu
pellicules_generes → pellicules ON DELETE CASCADE Si desapareix la pel·lícula, la seva classificació per gènere no significa res
pellicules_generes → generes ON DELETE RESTRICT Esborrar el gènere «drama» no ha d'esborrar en cascada la seva assignació a 400 pel·lícules: primer cal reclassificar-les
lloguers → socis ON DELETE RESTRICT L'històric de lloguers és informació comptable. Un soci que es dóna de baixa es marca actiu = FALSE, no s'esborra
copies → pellicules ON DELETE RESTRICT Si hi ha còpies físiques a la prestatgeria, la pel·lícula no pot desaparèixer del catàleg

L'índex únic parcial és la peça més interessant del disseny. La regla «una còpia només pot estar llogada a una persona» no es pot expressar amb un UNIQUE (copia_id) sobre lloguers, perquè llavors una còpia només es podria llogar una vegada en tota la seva història. El que cal restringir és que hi hagi com a molt un lloguer obert per còpia, i això és exactament el que fa CREATE UNIQUE INDEX ... WHERE data_devolucio IS NULL. És una restricció de PostgreSQL; a SQLite existeix el mateix índex parcial, a MySQL 8 no, i allà caldria resoldre-ho amb un disparador o confiant en l'aplicació (pitjor).

Alternativa raonable que no he pres: una columna copies.estat redundant amb l'existència d'un lloguer obert. L'he mantinguda perquè el propietari distingeix estats que no depenen del lloguer (malmesa, retirada), però això introdueix una possibilitat d'incoherència: una còpia disponible amb un lloguer obert. Si el volum fos més gran, valdria la pena mantenir-la amb un disparador; amb 3.000 pel·lícules, l'aplicació se'n pot encarregar.

Exercici 2: Préstec interbibliotecari a BiblioRed

Dificultat: Intermedi

Enunciat. BiblioRed vol llançar un servei de préstec interbibliotecari. La direcció ho descriu així:

«Si un soci de Sud vol un llibre que només és a Nord, ara s'ha de desplaçar. Volem que el pugui demanar des de la seva sucursal i que l'exemplar viatgi. També volem poder demanar a biblioteques d'altres ciutats amb les quals tenim conveni —tenen nom, ciutat, persona de contacte i correu—, i deixar-los-en a elles. Cada petició la fa un soci en una sucursal, sobre un material concret (no sobre un exemplar: ens és igual quin vingui). La petició passa per estats: sol·licitada, acceptada, en trànsit, disponible per recollir, prestada, retornada, rebutjada o cancel·lada, i de cada canvi d'estat volem saber quan va passar i qui el va fer. Quan la petició s'accepta, se li assigna un exemplar concret. L'enviament té un cost que paga la biblioteca sol·licitant, i les peticions a biblioteques externes tenen un termini màxim diferent de l'intern.»

Restriccions addicionals: una petició es dirigeix o bé a una altra sucursal de BiblioRed o bé a una biblioteca externa, mai a totes dues ni a cap. Un soci no pot tenir més de tres peticions actives simultàniament (aquesta la pots deixar per a l'aplicació, però indica-ho).

Pista. L'historial de canvis d'estat és una entitat feble que depèn de la petició.

Solució

Pas 1 — Entitats i relacions

Entitats noves (les existents de BiblioRed no es toquen):

Entitat Tipus Justificació
biblioteques_externes Forta Té identitat i atributs propis
peticions_interbib Forta El fet central del servei
peticions_estats Feble No existeix sense la seva petició; la seva clau inclou la de la petició

Relacions:

  • socis 1:N peticions_interbib (qui demana).
  • sucursals 1:N peticions_interbib com a origen (on ho recull).
  • sucursals 1:N peticions_interbib com a destí opcional (a qui es demana).
  • biblioteques_externes 1:N peticions_interbib com a destí opcional.
  • materials 1:N peticions_interbib (què es demana).
  • exemplars 1:N peticions_interbib (quin exemplar s'ha assignat, nul fins a l'acceptació).
  • peticions_interbib 1:N peticions_estats (identificadora, entitat feble).

Pas 2 — Diagrama ER

erDiagram
    SOCIS                ||--o{ PETICIONS_INTERBIB : sollicita
    SUCURSALS            ||--o{ PETICIONS_INTERBIB : "origen / desti"
    BIBLIOTEQUES_EXTERNES||--o{ PETICIONS_INTERBIB : "desti extern"
    MATERIALS            ||--o{ PETICIONS_INTERBIB : "es demana"
    EXEMPLARS            ||--o{ PETICIONS_INTERBIB : "s assigna"
    PETICIONS_INTERBIB   ||--|{ PETICIONS_ESTATS   : "registra"

    BIBLIOTEQUES_EXTERNES {
        int  biblioteca_id PK
        text nom
        text ciutat
        text contacte_nom
        text contacte_email
        date conveni_des_de
        bool activa
    }
    PETICIONS_INTERBIB {
        int     peticio_id PK
        int     soci_id FK
        int     sucursal_origen_id FK
        int     sucursal_desti_id FK "nul si es externa"
        int     biblioteca_externa_id FK "nul si es interna"
        int     material_id FK
        int     exemplar_id FK "nul fins a acceptar"
        date    data_solicitud
        date    data_limit
        numeric cost_enviament
        text    estat_actual
    }
    PETICIONS_ESTATS {
        int       peticio_id PK_FK
        int       sequencia PK
        text      estat
        timestamp moment
        text      usuari
        text      observacions
    }

Pas 3 — CREATE TABLE

CREATE TABLE biblioteques_externes (
    biblioteca_id  SERIAL PRIMARY KEY,
    nom            VARCHAR(120) NOT NULL,
    ciutat         VARCHAR(60)  NOT NULL,
    contacte_nom   VARCHAR(100),
    contacte_email VARCHAR(120),
    conveni_des_de DATE NOT NULL,
    activa         BOOLEAN NOT NULL DEFAULT TRUE,
    CONSTRAINT uq_bibext_nom_ciutat UNIQUE (nom, ciutat)
);

CREATE TABLE peticions_interbib (
    peticio_id            SERIAL PRIMARY KEY,
    soci_id               INTEGER NOT NULL REFERENCES socis(soci_id)            ON DELETE RESTRICT,
    sucursal_origen_id    INTEGER NOT NULL REFERENCES sucursals(sucursal_id)    ON DELETE RESTRICT,
    sucursal_desti_id     INTEGER          REFERENCES sucursals(sucursal_id)    ON DELETE RESTRICT,
    biblioteca_externa_id INTEGER          REFERENCES biblioteques_externes(biblioteca_id) ON DELETE RESTRICT,
    material_id           INTEGER NOT NULL REFERENCES materials(material_id)    ON DELETE RESTRICT,
    exemplar_id           INTEGER          REFERENCES exemplars(exemplar_id)    ON DELETE SET NULL,
    data_solicitud        DATE NOT NULL DEFAULT CURRENT_DATE,
    data_limit            DATE NOT NULL,
    cost_enviament        NUMERIC(6,2) NOT NULL DEFAULT 0,
    estat_actual          VARCHAR(22)  NOT NULL DEFAULT 'sollicitada'
);

-- Entitat feble: la seva clau primària arrossega la de la petició
CREATE TABLE peticions_estats (
    peticio_id    INTEGER   NOT NULL REFERENCES peticions_interbib(peticio_id) ON DELETE CASCADE,
    sequencia     SMALLINT  NOT NULL,
    estat         VARCHAR(22) NOT NULL,
    moment        TIMESTAMPTZ NOT NULL DEFAULT now(),
    usuari        VARCHAR(60) NOT NULL,
    observacions  TEXT,
    PRIMARY KEY (peticio_id, sequencia)
);

Pas 4 — Restriccions

-- Un domini reutilitzable per al conjunt d'estats (PostgreSQL)
CREATE DOMAIN estat_interbib AS VARCHAR(22)
    CHECK (VALUE IN ('sollicitada','acceptada','en_transit','disponible_recollida',
                     'prestada','retornada','rebutjada','cancellada'));

ALTER TABLE peticions_interbib
    ALTER COLUMN estat_actual TYPE estat_interbib;
ALTER TABLE peticions_estats
    ALTER COLUMN estat TYPE estat_interbib;

ALTER TABLE peticions_interbib
    -- O destí intern O destí extern, exactament un dels dos
    ADD CONSTRAINT ck_pet_desti_exclusiu CHECK (
        (sucursal_desti_id IS NOT NULL AND biblioteca_externa_id IS NULL)
     OR (sucursal_desti_id IS NULL     AND biblioteca_externa_id IS NOT NULL)
    ),
    -- No té sentit demanar-se un material a la mateixa sucursal
    ADD CONSTRAINT ck_pet_origen_diferent CHECK (
        sucursal_desti_id IS NULL OR sucursal_desti_id <> sucursal_origen_id
    ),
    ADD CONSTRAINT ck_pet_limit CHECK (data_limit > data_solicitud),
    ADD CONSTRAINT ck_pet_cost  CHECK (cost_enviament >= 0),
    -- Només hi pot haver exemplar assignat a partir d'«acceptada»
    ADD CONSTRAINT ck_pet_exemplar_estat CHECK (
        exemplar_id IS NOT NULL
     OR estat_actual IN ('sollicitada','rebutjada','cancellada')
    );

-- Un soci no pot demanar dues vegades el mateix material mentre la petició segueixi viva
CREATE UNIQUE INDEX uq_peticio_viva
    ON peticions_interbib (soci_id, material_id)
    WHERE estat_actual NOT IN ('retornada','rebutjada','cancellada');

CREATE INDEX idx_pet_estat   ON peticions_interbib (estat_actual, data_limit);
CREATE INDEX idx_pet_soci    ON peticions_interbib (soci_id, data_solicitud DESC);

Resultat esperat

Tres taules noves, zero modificacions a les taules existents de BiblioRed. Exemple de càrrega coherent:

INSERT INTO biblioteques_externes (nom, ciutat, contacte_nom, contacte_email, conveni_des_de)
VALUES ('Biblioteca Municipal de Port Alt','Port Alt','Lídia Serna','[email protected]','2025-04-01');

INSERT INTO peticions_interbib
  (soci_id, sucursal_origen_id, sucursal_desti_id, material_id, data_limit)
VALUES (16, 3, 2, 904, DATE '2026-08-20');          -- Núria Bastos (Sud) demana a Nord

INSERT INTO peticions_estats (peticio_id, sequencia, estat, usuari)
VALUES (1, 1, 'sollicitada', 'taulell.sud');

I la comprovació que la regla del destí exclusiu funciona:

INSERT INTO peticions_interbib
  (soci_id, sucursal_origen_id, sucursal_desti_id, biblioteca_externa_id, material_id, data_limit)
VALUES (16, 3, 2, 1, 904, DATE '2026-08-20');
-- ERROR: new row violates check constraint "ck_pet_desti_exclusiu"

Explicació i decisions discutibles

Dissenyar sobre un esquema existent canvia les regles del joc. El requisit implícit més fort d'aquest exercici és que no pots trencar res. Per això la solució no afegeix columnes a prestecs ni a exemplars: qualsevol consulta, informe o índex que ja existís a BiblioRed segueix funcionant exactament igual després d'instal·lar el servei.

La petició apunta a materials i no a exemplars, perquè el soci va dir «m'és igual quin vingui». exemplar_id és nul al principi i s'omple en acceptar. Aquell NULL no és una mancança del disseny: és informació («encara no s'ha assignat»), i el CHECK ck_pet_exemplar_estat la lliga a l'estat perquè no hi pugui haver una petició «en trànsit» sense exemplar.

Dues claus foranes cap a la mateixa taula. sucursal_origen_id i sucursal_desti_id apunten totes dues a sucursals. És perfectament legal i molt habitual; l'única cosa que exigeix són noms de columna que diguin el seu paper, perquè sucursal_id a seques seria ambigu. És el mateix patró que ja apareix a BiblioRed entre socis.sucursal_id i exemplars.sucursal_id.

El destí exclusiu: tres alternatives.

Opció Com Avantatge Inconvenient
L'escollida: dues columnes nul·lables + CHECK Un sol CHECK amb IS NULL/IS NOT NULL Simple, llegible, integritat garantida pel motor Dues columnes on conceptualment n'hi ha una
Jerarquia: taula destins amb subtipus destins genèrica + destins_sucursal i destins_externa Extensible a un tercer tipus de destí Un JOIN més a tota consulta; sobredimensionat per a dos casos
Columna genèrica desti_tipus + desti_id Una sola clau forana «polimòrfica» Compacta Impossible declarar la clau forana. Antipatró; el motor deixa de protegir la integritat

La tercera és la que sol proposar qui ve d'un ORM i és l'única clarament incorrecta.

L'historial com a entitat feble. peticions_estats té clau primària (peticio_id, sequencia): el número de seqüència només té sentit dins de la seva petició. És el cas de llibre d'entitat feble amb relació identificadora, i per això porta ON DELETE CASCADE: si la petició desapareix, el seu historial no significa res.

Aquí hi ha una redundància deliberada: estat_actual a la capçalera duplica l'últim estat de l'historial. Es podria calcular sempre amb ORDER BY sequencia DESC LIMIT 1, però és la consulta més freqüent del sistema (el panell de safata d'entrada de cada sucursal) i la columna permet indexar-la. És la desnormalització controlada de la lliçó 05-04: s'accepta a canvi d'haver de mantenir les dues coses sincronitzades, idealment amb un disparador.

El límit de tres peticions actives per soci. No es pot expressar amb un CHECK, perquè un CHECK només veu la fila que s'està inserint i aquesta regla implica comptar files de la taula. Les opcions reals són un disparador BEFORE INSERT que compti i llanci excepció, o la lògica d'aplicació dins de la transacció amb un SELECT ... FOR UPDATE sobre el soci. L'important és dir-ho al disseny, no deixar-ho implícit: una regla de negoci sense restricció és una regla que algun dia s'incomplirà.

Exercici 3: Plataforma de cursos en línia

Dificultat: Intermedi

Enunciat. Una plataforma de formació vol la seva base de dades:

«Tenim cursos, i cada curs està dividit en mòduls, i cada mòdul en lliçons. Les lliçons van numerades dins del seu mòdul i els mòduls dins del seu curs. Una lliçó té títol, tipus (vídeo, text o qüestionari) i durada estimada. Els cursos poden tenir requisits previs: per fer «SQL avançat» cal haver fet abans «SQL bàsic», i un curs pot tenir diversos requisits. Els alumnes es matriculen a cursos; de la matrícula en guardem la data, el preu pagat i si està activa, completada o abandonada. Volem saber, de cada alumne i cada lliçó, si l'ha acabada i quan; també el percentatge d'avenç del curs. Les lliçons de tipus qüestionari tenen preguntes amb diverses opcions, de les quals una és la correcta, i cada intent d'un alumne guarda la nota i la data. Un alumne pot intentar un qüestionari diverses vegades.»

Pista. «Requisits previs» és una relació N:M d'una taula amb ella mateixa.

Solució

Pas 1 — Entitats i relacions

Jerarquia de continguts: cursos 1:N moduls 1:N llicons. Els tres són entitats fortes amb clau substituta, però moduls i llicons porten a més una clau alternativa que reflecteix la seva numeració dins del pare.

Autoreferència: cursos N:M cursos a través de requisits_curs (curs_id, requisit_id).

N:M amb atributs: alumnes N:M cursos a través de matricules, que té data, preu i estat propis.

Progrés: matricules N:M llicons a través de progres_llico. Fixa't que el progrés penja de la matrícula, no de l'alumne: si algú es matricula dues vegades al mateix curs, cada matrícula té el seu propi avenç.

Qüestionaris: llicons 1:N preguntes 1:N opcions; matricules 1:N intents (sobre una lliçó de tipus qüestionari).

Pas 2 — Diagrama ER

erDiagram
    CURSOS    ||--o{ MODULS     : conte
    MODULS    ||--o{ LLICONS    : conte
    CURSOS    ||--o{ REQUISITS_CURS : "exigeix"
    CURSOS    ||--o{ REQUISITS_CURS : "es requisit de"
    ALUMNES   ||--o{ MATRICULES : fa
    CURSOS    ||--o{ MATRICULES : rep
    MATRICULES||--o{ PROGRES_LLICO : avanca
    LLICONS   ||--o{ PROGRES_LLICO : "es completa a"
    LLICONS   ||--o{ PREGUNTES  : "questionari de"
    PREGUNTES ||--|{ OPCIONS    : ofereix
    MATRICULES||--o{ INTENTS    : genera
    LLICONS   ||--o{ INTENTS    : "s avalua a"

    CURSOS    { int curs_id PK
                text titol
                text nivell
                bool publicat }
    MODULS    { int modul_id PK
                int curs_id FK
                int ordre
                text titol }
    LLICONS   { int llico_id PK
                int modul_id FK
                int ordre
                text titol
                text tipus
                int durada_min }
    REQUISITS_CURS { int curs_id PK_FK
                     int requisit_id PK_FK }
    ALUMNES   { int alumne_id PK
                text email UK
                text nom }
    MATRICULES{ int matricula_id PK
                int alumne_id FK
                int curs_id FK
                date data_matricula
                numeric preu_pagat
                text estat }
    PROGRES_LLICO { int matricula_id PK_FK
                    int llico_id PK_FK
                    timestamp completada_en }
    PREGUNTES { int pregunta_id PK
                int llico_id FK
                int ordre
                text enunciat }
    OPCIONS   { int opcio_id PK
                int pregunta_id FK
                text text_opcio
                bool correcta }
    INTENTS   { int intent_id PK
                int matricula_id FK
                int llico_id FK
                timestamp moment
                numeric nota }

Pas 3 — CREATE TABLE

CREATE TABLE cursos (
    curs_id  SERIAL PRIMARY KEY,
    titol    VARCHAR(150) NOT NULL,
    nivell   VARCHAR(15)  NOT NULL,
    publicat BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE moduls (
    modul_id SERIAL PRIMARY KEY,
    curs_id  INTEGER NOT NULL REFERENCES cursos(curs_id) ON DELETE CASCADE,
    ordre    SMALLINT NOT NULL,
    titol    VARCHAR(150) NOT NULL,
    CONSTRAINT uq_modul_ordre UNIQUE (curs_id, ordre)
);

CREATE TABLE llicons (
    llico_id   SERIAL PRIMARY KEY,
    modul_id   INTEGER NOT NULL REFERENCES moduls(modul_id) ON DELETE CASCADE,
    ordre      SMALLINT NOT NULL,
    titol      VARCHAR(150) NOT NULL,
    tipus      VARCHAR(12)  NOT NULL,
    durada_min SMALLINT,
    CONSTRAINT uq_llico_ordre UNIQUE (modul_id, ordre)
);

CREATE TABLE requisits_curs (
    curs_id     INTEGER NOT NULL REFERENCES cursos(curs_id) ON DELETE CASCADE,
    requisit_id INTEGER NOT NULL REFERENCES cursos(curs_id) ON DELETE RESTRICT,
    PRIMARY KEY (curs_id, requisit_id),
    CONSTRAINT ck_req_no_reflexiu CHECK (curs_id <> requisit_id)
);

CREATE TABLE alumnes (
    alumne_id SERIAL PRIMARY KEY,
    email     VARCHAR(120) NOT NULL UNIQUE,
    nom       VARCHAR(100) NOT NULL,
    alta      DATE NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE matricules (
    matricula_id   SERIAL PRIMARY KEY,
    alumne_id      INTEGER NOT NULL REFERENCES alumnes(alumne_id) ON DELETE RESTRICT,
    curs_id        INTEGER NOT NULL REFERENCES cursos(curs_id)    ON DELETE RESTRICT,
    data_matricula DATE NOT NULL DEFAULT CURRENT_DATE,
    preu_pagat     NUMERIC(8,2) NOT NULL,
    estat          VARCHAR(12) NOT NULL DEFAULT 'activa'
);

CREATE TABLE progres_llico (
    matricula_id  INTEGER NOT NULL REFERENCES matricules(matricula_id) ON DELETE CASCADE,
    llico_id      INTEGER NOT NULL REFERENCES llicons(llico_id)        ON DELETE CASCADE,
    completada_en TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (matricula_id, llico_id)
);

CREATE TABLE preguntes (
    pregunta_id SERIAL PRIMARY KEY,
    llico_id    INTEGER NOT NULL REFERENCES llicons(llico_id) ON DELETE CASCADE,
    ordre       SMALLINT NOT NULL,
    enunciat    TEXT NOT NULL,
    CONSTRAINT uq_pregunta_ordre UNIQUE (llico_id, ordre)
);

CREATE TABLE opcions (
    opcio_id    SERIAL PRIMARY KEY,
    pregunta_id INTEGER NOT NULL REFERENCES preguntes(pregunta_id) ON DELETE CASCADE,
    text_opcio  TEXT NOT NULL,
    correcta    BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE intents (
    intent_id    SERIAL PRIMARY KEY,
    matricula_id INTEGER NOT NULL REFERENCES matricules(matricula_id) ON DELETE CASCADE,
    llico_id     INTEGER NOT NULL REFERENCES llicons(llico_id)        ON DELETE CASCADE,
    moment       TIMESTAMPTZ NOT NULL DEFAULT now(),
    nota         NUMERIC(5,2) NOT NULL
);

Pas 4 — Restriccions

ALTER TABLE cursos
    ADD CONSTRAINT ck_cursos_nivell CHECK (nivell IN ('inicial','intermedi','avancat'));

ALTER TABLE llicons
    ADD CONSTRAINT ck_llic_tipus CHECK (tipus IN ('video','text','questionari')),
    ADD CONSTRAINT ck_llic_dur   CHECK (durada_min IS NULL OR durada_min > 0),
    ADD CONSTRAINT ck_llic_ordre CHECK (ordre > 0);

ALTER TABLE matricules
    ADD CONSTRAINT ck_matr_estat CHECK (estat IN ('activa','completada','abandonada')),
    ADD CONSTRAINT ck_matr_preu  CHECK (preu_pagat >= 0);

-- Un alumne no pot tenir dues matrícules actives al mateix curs,
-- però sí que es pot rematricular després d'abandonar
CREATE UNIQUE INDEX uq_matricula_activa
    ON matricules (alumne_id, curs_id)
    WHERE estat = 'activa';

ALTER TABLE intents
    ADD CONSTRAINT ck_intent_nota CHECK (nota BETWEEN 0 AND 10);

-- Cada pregunta ha de tenir exactament una opció correcta
CREATE UNIQUE INDEX uq_opcio_correcta
    ON opcions (pregunta_id)
    WHERE correcta;

Resultat esperat

Onze taules. La consulta del percentatge d'avenç, que és la raó de ser de mitja plataforma, surt directa:

SELECT m.matricula_id,
       count(pl.llico_id) AS completades,
       (SELECT count(*) FROM llicons l
          JOIN moduls mo ON mo.modul_id = l.modul_id
         WHERE mo.curs_id = m.curs_id) AS totals,
       round(100.0 * count(pl.llico_id) /
             NULLIF((SELECT count(*) FROM llicons l
                       JOIN moduls mo ON mo.modul_id = l.modul_id
                      WHERE mo.curs_id = m.curs_id), 0), 1) AS pct
FROM matricules m
LEFT JOIN progres_llico pl ON pl.matricula_id = m.matricula_id
WHERE m.matricula_id = 1
GROUP BY m.matricula_id, m.curs_id;

Explicació i decisions discutibles

El progrés penja de la matrícula, no de l'alumne. És la decisió més important del disseny i la més fàcil d'errar. Si progres_llico tingués (alumne_id, llico_id), un alumne que abandona un curs i es torna a matricular arrossegaria tot el seu avenç anterior, i no hi hauria manera de saber a quina matrícula pertanyia cada lliçó completada. Amb (matricula_id, llico_id), cada intent de fer el curs té la seva pròpia història. El preu és un JOIN més per arribar de l'alumne al progrés; és un preu baix.

La numeració dins del pare: clau substituta + UNIQUE compost. moduls té modul_id com a clau primària i UNIQUE (curs_id, ordre) com a clau alternativa. L'alternativa —clau primària composta (curs_id, ordre)— també és defensable i és més «pura», però té dos inconvenients pràctics: reordenar els mòduls obliga a actualitzar la clau primària (i en cascada totes les lliçons), i les claus foranes cap a llicons acabarien essent de tres columnes. Amb clau substituta, reordenar és un UPDATE de la columna ordre i prou.

L'autoreferència N:M. requisits_curs és una taula de reunió les dues claus foranes de la qual apunten a la mateixa taula. El CHECK (curs_id <> requisit_id) impedeix el cas trivial que un curs sigui requisit d'ell mateix. El que no pot impedir cap restricció declarativa és un cicle més llarg: A exigeix B, B exigeix C, C exigeix A. Detectar això requereix recórrer el graf, i es fa amb una CTE recursiva —la veuràs a 07-04— o amb un disparador que l'executi abans d'inserir.

uq_opcio_correcta garanteix com a molt una de correcta, no exactament una. L'índex únic parcial impedeix dues opcions marcades com a correctes a la mateixa pregunta, però no impedeix que no n'hi hagi cap. Aquella segona meitat de la regla —«almenys una»— és una restricció de conjunt que les bases de dades relacionals no expressen bé de manera declarativa; es resol amb un disparador AFTER o amb una comprovació en publicar el curs. Reconèixer el límit és part del disseny.

Alternativa raonable que no he pres: guardar a intents les respostes concretes de cada pregunta (intents_respostes). L'enunciat només demanava la nota, i afegir aquella taula sense que ningú la demani és sobredisseny. Però si demà volen estadístiques de «quina pregunta falla més gent», caldria, i l'extensió seria neta: (intent_id, pregunta_id, opcio_id).

Exercici 4: Taller mecànic amb jerarquia i relació ternària

Dificultat: Avançat

Enunciat. Un taller mecànic de Vallmar:

«Atenem vehicles: cotxes, motos i furgonetes. De tots en guardem matrícula, marca, model, any i el client propietari. Dels cotxes ens interessa a més el nombre de places i el tipus de combustible; de les motos, la cilindrada; i de les furgonetes, la càrrega màxima en quilos i si tenen tacògraf. Un client pot tenir diversos vehicles i un vehicle té un únic propietari. Quan entra un vehicle obrim una ordre de reparació amb la data d'entrada, el quilometratge i una descripció del problema. En una ordre es fan diverses intervencions; cada intervenció la fa un mecànic concret sobre l'ordre, aplicant un tipus de treball del catàleg (canvi d'oli, alineació, diagnosi...), i anotem les hores dedicades. El mateix mecànic pot fer diversos tipus de treball a la mateixa ordre, i el mateix tipus de treball el poden fer mecànics diferents a la mateixa ordre en dies diferents. També registrem les peces que es fan servir a cada intervenció, amb la quantitat i el preu unitari aplicat aquell dia.»

Pista. «Intervenció» relaciona tres entitats alhora. I una jerarquia de generalització es pot transformar de tres maneres diferents: tria i justifica.

Solució

Pas 1 — Entitats i relacions

La jerarquia: vehicles és la superentitat, amb cotxes, motos i furgonetes com a subentitats. La generalització és total (tot vehicle és d'un dels tres tipus) i disjunta (cap no és dues coses alhora).

La relació ternària: intervencions relaciona ordres × mecanics × tipus_treball. Com que l'enunciat diu explícitament que el mateix mecànic pot repetir tipus de treball a la mateixa ordre en dies diferents, la ternària no es pot identificar per la terna: necessita clau substituta o incloure la data.

peces N:M intervencions amb atributs (quantitat, preu_unitari).

Pas 2 — Diagrama ER

erDiagram
    CLIENTS  ||--o{ VEHICLES : posseeix
    VEHICLES ||--o| COTXES     : "es un"
    VEHICLES ||--o| MOTOS      : "es un"
    VEHICLES ||--o| FURGONETES : "es un"
    VEHICLES ||--o{ ORDRES     : genera
    ORDRES   ||--o{ INTERVENCIONS : inclou
    MECANICS ||--o{ INTERVENCIONS : fa
    TIPUS_TREBALL ||--o{ INTERVENCIONS : "s aplica a"
    INTERVENCIONS ||--o{ INTERVENCIO_PECES : consumeix
    PECES         ||--o{ INTERVENCIO_PECES : "es fa servir a"

    CLIENTS  { int client_id PK
               text nom
               text nif UK
               text telefon }
    VEHICLES { int  vehicle_id PK
               text matricula UK
               text tipus
               int  client_id FK
               text marca
               text model
               int  any_ }
    COTXES     { int vehicle_id PK_FK
                 int places
                 text combustible }
    MOTOS      { int vehicle_id PK_FK
                 int cilindrada }
    FURGONETES { int vehicle_id PK_FK
                 int carrega_max_kg
                 bool tacograf }
    ORDRES   { int  ordre_id PK
               int  vehicle_id FK
               date data_entrada
               int  quilometratge
               text descripcio
               text estat }
    MECANICS { int mecanic_id PK
               text nom
               text especialitat }
    TIPUS_TREBALL { int tipus_treball_id PK
                    text codi UK
                    text nom
                    numeric preu_hora }
    INTERVENCIONS { int  intervencio_id PK
                    int  ordre_id FK
                    int  mecanic_id FK
                    int  tipus_treball_id FK
                    date data
                    numeric hores }
    PECES    { int peca_id PK
               text referencia UK
               text descripcio
               numeric preu_actual }
    INTERVENCIO_PECES { int intervencio_id PK_FK
                        int peca_id PK_FK
                        int quantitat
                        numeric preu_unitari }

Pas 3 — CREATE TABLE

CREATE TABLE clients (
    client_id SERIAL PRIMARY KEY,
    nom       VARCHAR(120) NOT NULL,
    nif       VARCHAR(12) NOT NULL UNIQUE,
    telefon   VARCHAR(15)
);

CREATE TABLE vehicles (
    vehicle_id SERIAL PRIMARY KEY,
    matricula  VARCHAR(10) NOT NULL UNIQUE,
    tipus      VARCHAR(10) NOT NULL,          -- discriminant
    client_id  INTEGER NOT NULL REFERENCES clients(client_id) ON DELETE RESTRICT,
    marca      VARCHAR(40) NOT NULL,
    model      VARCHAR(60) NOT NULL,
    any_       SMALLINT NOT NULL,
    CONSTRAINT ck_veh_tipus CHECK (tipus IN ('cotxe','moto','furgoneta')),
    -- Truc perquè la subtaula només pugui enllaçar amb el seu tipus:
    CONSTRAINT uq_veh_tipus UNIQUE (vehicle_id, tipus)
);

CREATE TABLE cotxes (
    vehicle_id  INTEGER PRIMARY KEY,
    tipus       VARCHAR(10) NOT NULL DEFAULT 'cotxe',
    places      SMALLINT NOT NULL,
    combustible VARCHAR(12) NOT NULL,
    CONSTRAINT ck_cotxes_tipus CHECK (tipus = 'cotxe'),
    CONSTRAINT fk_cotxes_veh FOREIGN KEY (vehicle_id, tipus)
        REFERENCES vehicles (vehicle_id, tipus) ON DELETE CASCADE,
    CONSTRAINT ck_cotxes_places CHECK (places BETWEEN 1 AND 9),
    CONSTRAINT ck_cotxes_comb CHECK (combustible IN ('gasolina','diesel','hibrid','electric','glp'))
);

CREATE TABLE motos (
    vehicle_id INTEGER PRIMARY KEY,
    tipus      VARCHAR(10) NOT NULL DEFAULT 'moto',
    cilindrada SMALLINT NOT NULL,
    CONSTRAINT ck_motos_tipus CHECK (tipus = 'moto'),
    CONSTRAINT fk_motos_veh FOREIGN KEY (vehicle_id, tipus)
        REFERENCES vehicles (vehicle_id, tipus) ON DELETE CASCADE,
    CONSTRAINT ck_motos_cc CHECK (cilindrada BETWEEN 49 AND 2500)
);

CREATE TABLE furgonetes (
    vehicle_id     INTEGER PRIMARY KEY,
    tipus          VARCHAR(10) NOT NULL DEFAULT 'furgoneta',
    carrega_max_kg INTEGER NOT NULL,
    tacograf       BOOLEAN NOT NULL DEFAULT FALSE,
    CONSTRAINT ck_furg_tipus CHECK (tipus = 'furgoneta'),
    CONSTRAINT fk_furg_veh FOREIGN KEY (vehicle_id, tipus)
        REFERENCES vehicles (vehicle_id, tipus) ON DELETE CASCADE,
    CONSTRAINT ck_furg_carrega CHECK (carrega_max_kg BETWEEN 100 AND 5000)
);

CREATE TABLE ordres (
    ordre_id      SERIAL PRIMARY KEY,
    vehicle_id    INTEGER NOT NULL REFERENCES vehicles(vehicle_id) ON DELETE RESTRICT,
    data_entrada  DATE NOT NULL DEFAULT CURRENT_DATE,
    data_sortida  DATE,
    quilometratge INTEGER NOT NULL,
    descripcio    TEXT NOT NULL,
    estat         VARCHAR(12) NOT NULL DEFAULT 'oberta',
    CONSTRAINT ck_ord_estat   CHECK (estat IN ('oberta','en_curs','tancada','facturada')),
    CONSTRAINT ck_ord_km      CHECK (quilometratge >= 0),
    CONSTRAINT ck_ord_sortida CHECK (data_sortida IS NULL OR data_sortida >= data_entrada)
);

CREATE TABLE mecanics (
    mecanic_id   SERIAL PRIMARY KEY,
    nom          VARCHAR(100) NOT NULL,
    especialitat VARCHAR(40),
    actiu        BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE tipus_treball (
    tipus_treball_id SERIAL PRIMARY KEY,
    codi             VARCHAR(10) NOT NULL UNIQUE,
    nom              VARCHAR(80) NOT NULL,
    preu_hora        NUMERIC(7,2) NOT NULL CHECK (preu_hora > 0)
);

-- La relació ternària, amb clau substituta
CREATE TABLE intervencions (
    intervencio_id   SERIAL PRIMARY KEY,
    ordre_id         INTEGER NOT NULL REFERENCES ordres(ordre_id)                ON DELETE CASCADE,
    mecanic_id       INTEGER NOT NULL REFERENCES mecanics(mecanic_id)            ON DELETE RESTRICT,
    tipus_treball_id INTEGER NOT NULL REFERENCES tipus_treball(tipus_treball_id) ON DELETE RESTRICT,
    data             DATE NOT NULL DEFAULT CURRENT_DATE,
    hores            NUMERIC(5,2) NOT NULL,
    CONSTRAINT ck_int_hores CHECK (hores > 0 AND hores <= 24),
    CONSTRAINT uq_intervencio UNIQUE (ordre_id, mecanic_id, tipus_treball_id, data)
);

CREATE TABLE peces (
    peca_id     SERIAL PRIMARY KEY,
    referencia  VARCHAR(30) NOT NULL UNIQUE,
    descripcio  VARCHAR(150) NOT NULL,
    preu_actual NUMERIC(8,2) NOT NULL CHECK (preu_actual >= 0)
);

CREATE TABLE intervencio_peces (
    intervencio_id INTEGER NOT NULL REFERENCES intervencions(intervencio_id) ON DELETE CASCADE,
    peca_id        INTEGER NOT NULL REFERENCES peces(peca_id)                ON DELETE RESTRICT,
    quantitat      SMALLINT NOT NULL CHECK (quantitat > 0),
    preu_unitari   NUMERIC(8,2) NOT NULL CHECK (preu_unitari >= 0),
    PRIMARY KEY (intervencio_id, peca_id)
);

Pas 4 — La restricció que faltava

-- Coherència de quilometratge: una ordre posterior no pot tenir menys km
-- (regla de conjunt: no es pot expressar amb CHECK; disparador o aplicació)

-- Sí que és declarativa aquesta: l'ordre no es pot tancar sense cap intervenció
-- → tampoc no és un CHECK. Es documenta i s'implementa amb disparador AFTER.

-- El que sí que queda garantit pel motor:
--  * un vehicle pertany exactament a un subtipus (per la FK composta)
--  * no hi ha dues intervencions idèntiques el mateix dia (uq_intervencio)
--  * els preus i les hores són positius

Resultat esperat

Dotze taules. Comprovació que la jerarquia està ben tancada:

INSERT INTO clients (nom, nif) VALUES ('Nerea Solans','44112233X');
INSERT INTO vehicles (matricula, tipus, client_id, marca, model, any_)
VALUES ('4471 KLM','moto',1,'Yamaha','MT-07',2021);

-- Correcte: la moto va a la seva subtaula
INSERT INTO motos (vehicle_id, cilindrada) VALUES (1, 689);

-- Incorrecte: intentar posar aquella mateixa moto com a cotxe
INSERT INTO cotxes (vehicle_id, places, combustible) VALUES (1, 5, 'gasolina');
-- ERROR: insert or update on table "cotxes" violates foreign key constraint "fk_cotxes_veh"

Explicació i decisions discutibles

Les tres formes de transformar una jerarquia, i per què he triat aquesta. La lliçó 04-03 donava tres opcions:

Opció Com Quan convé Aquí
Taula única Una taula vehicles amb totes les columnes dels tres subtipus, la majoria nul·les Pocs atributs específics, consultes sempre sobre el conjunt Descartada: 5 columnes nul·lables i cap NOT NULL possible a cilindrada
Taula per subtipus (l'escollida) Superentitat + una taula per subtipus amb vehicle_id com a PK i FK Atributs específics que han de ser obligatoris; generalització total i disjunta Escollida
Només subtipus Tres taules independents sense superentitat Els subtipus no comparteixen relacions Descartada: ordres necessita apuntar a un vehicle qualsevol, i amb tres taules caldria una FK polimòrfica

L'opció tercera és la que trenca el disseny així que apareix ordres: cal poder referenciar «un vehicle» sense saber-ne el tipus.

El truc de la clau forana composta (vehicle_id, tipus) mereix atenció perquè és elegant i poc conegut. El problema que resol: amb una clau forana normal cotxes.vehicle_id → vehicles.vehicle_id, res no impedeix posar a cotxes un vehicle el tipus del qual sigui 'moto'. La solució té tres peces que només funcionen juntes:

  1. UNIQUE (vehicle_id, tipus) a vehicles —redundant amb la clau primària, però necessari perquè la FK composta tingui a què apuntar.
  2. Una columna tipus a la subtaula, amb CHECK (tipus = 'cotxe') i DEFAULT.
  3. La clau forana composta de les dues columnes.

El resultat és que el motor impedeix posar una moto a la taula de cotxes. Sense el truc, aquella regla quedaria en mans de l'aplicació. El que segueix sense quedar garantit és la totalitat de la generalització (que tot vehicle tingui fila en alguna subtaula): això requereix restriccions diferides o un disparador.

La ternària: per què no n'hi ha prou amb la terna com a clau. La temptació és PRIMARY KEY (ordre_id, mecanic_id, tipus_treball_id). L'enunciat la desmunta: «el mateix tipus de treball el poden fer mecànics diferents a la mateixa ordre en dies diferents». I de fet el mateix mecànic pot repetir la mateixa feina dos dies seguits. Per això la solució fa servir clau substituta i afegeix UNIQUE (ordre_id, mecanic_id, tipus_treball_id, data), que sí que és la clau alternativa real. Si demà admeten dues intervencions iguals el mateix dia (matí i tarda), caldria substituir data per moment TIMESTAMPTZ o eliminar el UNIQUE.

La clau substituta té a més un avantatge decisiu: intervencio_peces necessita apuntar a la intervenció, i amb clau composta de quatre columnes la taula de peces tindria sis columnes de clau.

preu_unitari duplica peces.preu_actual, i està bé. És la desnormalització més justificada que existeix: el preu d'una peça canvia amb el temps i l'ordre ja facturada ha de conservar el preu que es va aplicar. peces.preu_actual és el preu d'avui, intervencio_peces.preu_unitari és el preu d'aquell dia. No són la mateixa dada, encara que coincideixin en el moment d'inserir. L'exercici 5 porta aquesta idea fins al final.

Alternativa raonable: modelar intervencions sense tipus_treball, posant el nom del treball com a text lliure. Seria més simple i seria un error: l'enunciat parla d'un «catàleg», i el preu per hora hi viu.

Exercici 5: Tarifes amb vigència temporal

Dificultat: Avançat

Enunciat. Una empresa municipal d'aparcaments de Vallmar:

«Tenim quatre aparcaments i cadascun aplica tarifes que canvien amb el temps. Una tarifa diu: per a aquest aparcament i aquest tipus d'usuari (resident, general, comercial), el preu de la primera hora, el de cada hora addicional i el màxim diari. Les tarifes s'aproven en ple i entren en vigor una data concreta; l'anterior deixa d'aplicar-se aquell mateix dia. Necessitem conservar totes les tarifes històriques, perquè hi ha reclamacions de fa tres anys i hem de poder dir quin preu estava vigent el 14 de març de 2024. De vegades s'aprova una tarifa amb mesos d'antelació, així que hi pot haver tarifes futures carregades. Mai no hi pot haver dues tarifes vigents alhora per al mateix aparcament i tipus d'usuari. També registrem les estades: entrada, sortida, matrícula i l'import cobrat.»

Pista. Pensa en la clau com a «què + des de quan», i busca la restricció de PostgreSQL que impedeix que dos intervals se solapin.

Solució

Pas 1 — Entitats i relacions

  • aparcaments: entitat forta, estable.
  • tipus_usuari: catàleg petit.
  • tarifes: l'entitat temporal. La seva identitat no és «l'aparcament i el tipus», sinó «l'aparcament, el tipus i el període de vigència».
  • estades: els fets. Cada estada es cobra amb la tarifa vigent en el seu moment.

La relació clau: aparcaments × tipus_usuari 1:N tarifes, on el discriminant que completa la identitat és l'interval de vigència.

Pas 2 — Diagrama ER

erDiagram
    APARCAMENTS  ||--o{ TARIFES : "aplica"
    TIPUS_USUARI ||--o{ TARIFES : "per a"
    APARCAMENTS  ||--o{ ESTADES : "allotja"
    TIPUS_USUARI ||--o{ ESTADES : "classifica"
    TARIFES      ||--o{ ESTADES : "cobra segons"

    APARCAMENTS  { int aparcament_id PK
                   text nom UK
                   text adreca
                   int  places }
    TIPUS_USUARI { int tipus_usuari_id PK
                   text codi UK
                   text nom }
    TARIFES { int     tarifa_id PK
              int     aparcament_id FK
              int     tipus_usuari_id FK
              date    vigent_des_de
              date    vigent_fins_a "nul = vigent"
              numeric preu_primera_hora
              numeric preu_hora_extra
              numeric maxim_diari
              text    acord_ple }
    ESTADES { int       estada_id PK
              int       aparcament_id FK
              int       tipus_usuari_id FK
              int       tarifa_id FK
              text      matricula
              timestamp entrada
              timestamp sortida
              numeric   import }

Pas 3 — CREATE TABLE

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE aparcaments (
    aparcament_id SERIAL PRIMARY KEY,
    nom           VARCHAR(60) NOT NULL UNIQUE,
    adreca        VARCHAR(120) NOT NULL,
    places        SMALLINT NOT NULL CHECK (places > 0)
);

CREATE TABLE tipus_usuari (
    tipus_usuari_id SERIAL PRIMARY KEY,
    codi            VARCHAR(12) NOT NULL UNIQUE,
    nom             VARCHAR(40) NOT NULL
);

CREATE TABLE tarifes (
    tarifa_id         SERIAL PRIMARY KEY,
    aparcament_id     INTEGER NOT NULL REFERENCES aparcaments(aparcament_id)  ON DELETE RESTRICT,
    tipus_usuari_id   INTEGER NOT NULL REFERENCES tipus_usuari(tipus_usuari_id) ON DELETE RESTRICT,
    vigent_des_de     DATE NOT NULL,
    vigent_fins_a     DATE,                    -- NULL = vigent indefinidament
    preu_primera_hora NUMERIC(6,2) NOT NULL,
    preu_hora_extra   NUMERIC(6,2) NOT NULL,
    maxim_diari       NUMERIC(6,2) NOT NULL,
    acord_ple         VARCHAR(40),
    CONSTRAINT ck_tar_periode CHECK (vigent_fins_a IS NULL OR vigent_fins_a > vigent_des_de),
    CONSTRAINT ck_tar_preus CHECK (preu_primera_hora >= 0
                               AND preu_hora_extra   >= 0
                               AND maxim_diari       >= preu_primera_hora)
);

CREATE TABLE estades (
    estada_id       SERIAL PRIMARY KEY,
    aparcament_id   INTEGER NOT NULL REFERENCES aparcaments(aparcament_id)    ON DELETE RESTRICT,
    tipus_usuari_id INTEGER NOT NULL REFERENCES tipus_usuari(tipus_usuari_id) ON DELETE RESTRICT,
    tarifa_id       INTEGER NOT NULL REFERENCES tarifes(tarifa_id)            ON DELETE RESTRICT,
    matricula       VARCHAR(10) NOT NULL,
    entrada         TIMESTAMPTZ NOT NULL,
    sortida         TIMESTAMPTZ,
    import          NUMERIC(8,2),
    CONSTRAINT ck_est_sortida CHECK (sortida IS NULL OR sortida > entrada),
    CONSTRAINT ck_est_import  CHECK (import IS NULL OR import >= 0)
);

Pas 4 — La restricció de no solapament

-- La regla de l'enunciat: mai dues tarifes vigents alhora
-- per al mateix aparcament i tipus d'usuari.
ALTER TABLE tarifes ADD CONSTRAINT ex_tarifes_no_solapen
    EXCLUDE USING gist (
        aparcament_id   WITH =,
        tipus_usuari_id WITH =,
        daterange(vigent_des_de, vigent_fins_a, '[)') WITH &&
    );

-- Índex per a la consulta més freqüent: quina tarifa aplicava el dia X?
CREATE INDEX idx_tarifes_vigencia
    ON tarifes (aparcament_id, tipus_usuari_id, vigent_des_de DESC);

Resultat esperat

Càrrega d'exemple amb dos trams històrics i un de futur:

INSERT INTO aparcaments (nom, adreca, places)
VALUES ('P1 Plaça Major','Plaça Major s/n',240);
INSERT INTO tipus_usuari (codi, nom) VALUES ('RES','Resident'),('GEN','General');

INSERT INTO tarifes (aparcament_id, tipus_usuari_id, vigent_des_de, vigent_fins_a,
                     preu_primera_hora, preu_hora_extra, maxim_diari, acord_ple)
VALUES (1,2,'2023-01-01','2024-07-01', 1.80, 1.20, 14.00, 'PLE-2022/114'),
       (1,2,'2024-07-01','2026-01-01', 2.00, 1.35, 16.00, 'PLE-2024/037'),
       (1,2,'2026-01-01', NULL,        2.20, 1.50, 18.00, 'PLE-2025/206');

Consulta de reclamació: «quin preu s'aplicava el 14 de març de 2024?»

SELECT preu_primera_hora, preu_hora_extra, maxim_diari, acord_ple
FROM tarifes
WHERE aparcament_id = 1
  AND tipus_usuari_id = 2
  AND vigent_des_de <= DATE '2024-03-14'
  AND (vigent_fins_a IS NULL OR vigent_fins_a > DATE '2024-03-14');
preu_primera_hora preu_hora_extra maxim_diari acord_ple
1.80 1.20 14.00 PLE-2022/114

I la comprovació que el motor bloqueja un solapament:

INSERT INTO tarifes (aparcament_id, tipus_usuari_id, vigent_des_de, vigent_fins_a,
                     preu_primera_hora, preu_hora_extra, maxim_diari)
VALUES (1, 2, '2025-06-01', '2025-12-01', 2.10, 1.40, 17.00);
-- ERROR: conflicting key value violates exclusion constraint "ex_tarifes_no_solapen"

Explicació i decisions discutibles

La decisió central: no hi ha UPDATE de preus, hi ha files noves. L'instint d'un principiant és UPDATE tarifes SET preu_primera_hora = 2.20 WHERE .... Aquell UPDATE destrueix la informació que l'enunciat demana conservar: així que s'executa, ja no hi ha manera de respondre a la reclamació de 2024. Quan un requisit diu «històric», l'operació de negoci «canviar el preu» es tradueix en un INSERT, no en un UPDATE.

Interval tancat-obert [des de, fins a). L'enunciat diu que la nova tarifa entra en vigor «una data concreta» i l'anterior deixa d'aplicar-se «aquell mateix dia». Això és exactament un interval tancat per l'esquerra i obert per la dreta: la tarifa antiga val fins al 30 de juny inclòs i la nova des de l'1 de juliol, i totes dues s'escriuen amb 2024-07-01 com a frontera. L'alternativa —vigent_fins_a = '2024-06-30' amb interval tancat pels dos costats— també funciona, però obliga a fer aritmètica de dates cada vegada que s'encadena un tram i falla així que la granularitat passa de dies a hores. Tancat-obert és la convenció que cal adoptar per defecte en dades temporals.

vigent_fins_a IS NULL significa «encara vigent». És un ús legítim del nul i encaixa amb daterange(des_de, fins_a), que interpreta el NULL superior com a infinit. L'alternativa és posar '9999-12-31'; simplifica les consultes (BETWEEN funciona sense OR ... IS NULL) a canvi de ficar-hi una data màgica que algun dia algú mostrarà per pantalla.

Tres formes de garantir el no solapament:

Opció Portabilitat Força
EXCLUDE USING gist amb daterange Només PostgreSQL Total: el motor ho garanteix, fins i tot amb concurrència
Índex únic parcial (aparcament_id, tipus_usuari_id) WHERE vigent_fins_a IS NULL PostgreSQL i SQLite Parcial: garanteix una sola tarifa oberta, però no impedeix solapaments entre trams tancats
Disparador que consulta abans d'inserir Qualsevol Depèn del nivell d'aïllament; amb READ COMMITTED dues sessions simultànies s'hi poden colar

La primera és superior i per això és la de la solució; la segona és un bon segon premi i val la pena conèixer-la perquè cobreix el 90 % dels casos amb sintaxi estàndard.

estades.tarifa_id guarda la tarifa aplicada, i és imprescindible. Es podria deduir a partir d'entrada i la taula de tarifes, però fixar-la a l'estada té dos avantatges: la factura queda immutable encara que algú corregeixi a posteriori una data de vigència mal carregada, i la consulta de facturació no necessita el JOIN per rang, que és molt més car que un JOIN per igualtat. És la mateixa lògica que intervencio_peces.preu_unitari a l'exercici 4.

Alternativa raonable que no he pres: dues taules, tarifes_vigents i tarifes_historic. És un patró molt estès i té un avantatge clar (la taula calenta es manté diminuta) i dos inconvenients seriosos: les consultes que creuen la frontera necessiten un UNION, i el pas d'una taula a l'altra és una operació que cal escriure bé i que pot fallar. Amb una sola taula i un índex adequat, PostgreSQL aguanta milions de trams sense despentinar-se.

Errors Habituals i Consells

1. Confondre l'obra amb l'objecte físic. Pel·lícula/còpia, material/exemplar, model/unitat. Si dues coses poden estar en llocs diferents i en estats diferents, són dues entitats.

2. Atributs multivaluats amagats. «De vegades són dos gèneres», «els telèfons», «els idiomes dels subtítols». Cada vegada que el client digui «de vegades en són uns quants», allà hi ha una taula.

3. Claus foranes polimòrfiques. Una columna objecte_tipus + objecte_id que apunta a taules diferents segons el tipus. El motor no pot declarar aquella clau forana, així que la integritat queda sense protegir. Fes servir columnes nul·lables excloents amb CHECK, o una jerarquia.

4. Posar totes les claus foranes amb ON DELETE CASCADE «per si de cas». CASCADE està bé per al que no existeix sense el seu pare (línies d'una comanda, historial d'una petició). Per a l'històric comptable —préstecs, lloguers, factures— l'acció correcta gairebé sempre és RESTRICT, i la baixa es fa amb una columna actiu.

5. Oblidar que un CHECK només veu la seva pròpia fila. «Màxim tres peticions actives», «almenys una opció correcta», «la suma de les línies ha de quadrar amb el total»: cap no és expressable amb CHECK. Reconeix-ho al disseny i decideix on va (disparador, aplicació o restricció diferida).

6. Clau primària composta en entitats que es referenciaran molt. És tècnicament correcte i pràcticament incòmode: cada taula filla arrossega totes les columnes. Clau substituta + UNIQUE sobre la clau natural és gairebé sempre millor compromís.

7. Preus i tarifes sense congelar. Un import facturat que es llegeix de la taula de preus actual canvia tot sol quan canvien els preus. Copia el preu aplicat a la línia del document.

8. Nomenclatura inconsistent. pelliculaID, id_genere, SociId al mateix esquema. Tria una convenció (snake_case, singular o plural, sufix _id) i no la trenquis. És el primer que veu qui hereti el teu esquema.

9. Dissenyar per a consultes que ningú no ha demanat. Cada taula que afegeixes és una taula que cal mantenir. Si l'enunciat no esmenta una necessitat, apunta-la com a possible extensió, no la construeixis.

Consell de mètode. Abans d'escriure el primer CREATE TABLE, escriu la llista de preguntes que l'esquema ha de respondre i deixa-la a la vista. En acabar, comprova una per una que pots escriure la consulta. Aquell és el criteri d'«acabat»; no ho és que el CREATE TABLE s'executi sense error.

Exercicis

Sense pistes. Cadascun demana els quatre passos complets.

Exercici A: Gimnàs municipal

Dissenya l'esquema d'un gimnàs: socis amb quota mensual, classes dirigides amb horari setmanal fix (dia de la setmana i hora), monitors, sales amb aforament, i reserves de plaça dels socis per a una sessió concreta d'una classe en una data concreta. Un soci no pot reservar dues vegades la mateixa sessió, i una sessió no pot admetre més reserves que l'aforament de la seva sala. A més, dues classes no poden ocupar la mateixa sala a la mateixa hora.

Exercici B: Ampliació de BiblioRed — donacions i expurgació

Amplia BiblioRed per gestionar l'origen i el final de vida dels exemplars: d'on va venir cada exemplar (compra a un proveïdor amb factura, donació d'un particular o d'una entitat, o intercanvi amb una altra biblioteca) i, quan es retira, per quin motiu, amb quina data i quin destí va tenir (venda benèfica, reciclatge, cessió). No pots modificar la taula exemplars llevat que sigui per afegir-hi columnes, i cap consulta existent no es pot trencar.

Solucions

Solució A — Gimnàs

Entitats: socis, sales, monitors, classes (la definició: nom, nivell, durada), sessions (una ocurrència concreta: classe + data + hora + sala + monitor), reserves (soci × sessió).

La distinció clau és classe enfront de sessió: «Pilates dels dimarts a les 19:00» és la classe; «el Pilates del dimarts 4 d'agost de 2026 a les 19:00 a la Sala 2 amb l'Aitor» és la sessió. Les reserves es fan sobre sessions.

CREATE TABLE sales (
    sala_id   SERIAL PRIMARY KEY,
    nom       VARCHAR(40) NOT NULL UNIQUE,
    aforament SMALLINT NOT NULL CHECK (aforament > 0)
);

CREATE TABLE monitors (
    monitor_id SERIAL PRIMARY KEY,
    nom        VARCHAR(100) NOT NULL,
    actiu      BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE classes (
    classe_id  SERIAL PRIMARY KEY,
    nom        VARCHAR(60) NOT NULL UNIQUE,
    nivell     VARCHAR(15) NOT NULL,
    durada_min SMALLINT NOT NULL CHECK (durada_min BETWEEN 15 AND 180)
);

CREATE TABLE socis (
    soci_id   SERIAL PRIMARY KEY,
    nom       VARCHAR(100) NOT NULL,
    email     VARCHAR(120) NOT NULL UNIQUE,
    quota_mes NUMERIC(6,2) NOT NULL CHECK (quota_mes >= 0),
    actiu     BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE sessions (
    sessio_id  SERIAL PRIMARY KEY,
    classe_id  INTEGER NOT NULL REFERENCES classes(classe_id)   ON DELETE RESTRICT,
    sala_id    INTEGER NOT NULL REFERENCES sales(sala_id)       ON DELETE RESTRICT,
    monitor_id INTEGER NOT NULL REFERENCES monitors(monitor_id) ON DELETE RESTRICT,
    inici      TIMESTAMPTZ NOT NULL,
    fi         TIMESTAMPTZ NOT NULL,
    places     SMALLINT NOT NULL CHECK (places > 0),
    CONSTRAINT ck_ses_horari CHECK (fi > inici)
);

CREATE TABLE reserves (
    sessio_id INTEGER NOT NULL REFERENCES sessions(sessio_id) ON DELETE CASCADE,
    soci_id   INTEGER NOT NULL REFERENCES socis(soci_id)      ON DELETE RESTRICT,
    moment    TIMESTAMPTZ NOT NULL DEFAULT now(),
    estat     VARCHAR(12) NOT NULL DEFAULT 'confirmada'
                CHECK (estat IN ('confirmada','cancellada','assistida')),
    PRIMARY KEY (sessio_id, soci_id)     -- un soci, una reserva per sessió
);

-- Dues classes no poden ocupar la mateixa sala a la mateixa hora
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE sessions ADD CONSTRAINT ex_sala_ocupada
    EXCLUDE USING gist (sala_id WITH =, tstzrange(inici, fi) WITH &&);

-- Un monitor tampoc no pot estar en dos llocs alhora
ALTER TABLE sessions ADD CONSTRAINT ex_monitor_ocupat
    EXCLUDE USING gist (monitor_id WITH =, tstzrange(inici, fi) WITH &&);

Dos comentaris sobre les decisions:

  • PRIMARY KEY (sessio_id, soci_id) resol de franc la regla «un soci no pot reservar dues vegades la mateixa sessió». És el cas ideal: una regla de negoci que es converteix en la clau primària.
  • L'aforament no és un CHECK. «No més reserves que places» exigeix comptar files, i un CHECK no pot. sessions.places copia l'aforament de la sala en el moment de programar-la (per poder oferir menys places que l'aforament real), i el control es fa a la transacció de reserva amb SELECT ... FOR UPDATE sobre la sessió —exactament el problema de l'última plaça que resoldràs a 07-04.
  • Les dues restriccions EXCLUDE són el mateix patró de l'exercici 5 aplicat a intervals de temps en lloc de a vigències.

Solució B — Donacions i expurgació a BiblioRed

CREATE TABLE proveidors (
    proveidor_id SERIAL PRIMARY KEY,
    nom          VARCHAR(120) NOT NULL,
    nif          VARCHAR(12) UNIQUE,
    contacte     VARCHAR(120)
);

CREATE TABLE donants (
    donant_id SERIAL PRIMARY KEY,
    tipus     VARCHAR(12) NOT NULL CHECK (tipus IN ('particular','entitat')),
    nom       VARCHAR(150) NOT NULL,
    email     VARCHAR(120),
    anonim    BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE adquisicions (
    adquisicio_id SERIAL PRIMARY KEY,
    exemplar_id   INTEGER NOT NULL UNIQUE REFERENCES exemplars(exemplar_id) ON DELETE CASCADE,
    via           VARCHAR(12) NOT NULL,
    data          DATE NOT NULL,
    proveidor_id  INTEGER REFERENCES proveidors(proveidor_id) ON DELETE RESTRICT,
    donant_id     INTEGER REFERENCES donants(donant_id)       ON DELETE RESTRICT,
    biblioteca_origen VARCHAR(150),
    num_factura   VARCHAR(30),
    cost          NUMERIC(8,2),
    CONSTRAINT ck_adq_via CHECK (via IN ('compra','donacio','intercanvi')),
    CONSTRAINT ck_adq_coherencia CHECK (
        (via = 'compra'     AND proveidor_id IS NOT NULL AND donant_id IS NULL
                            AND num_factura IS NOT NULL AND cost IS NOT NULL)
     OR (via = 'donacio'    AND donant_id IS NOT NULL AND proveidor_id IS NULL)
     OR (via = 'intercanvi' AND biblioteca_origen IS NOT NULL
                            AND proveidor_id IS NULL AND donant_id IS NULL)
    )
);

CREATE TABLE expurgacions (
    expurgacio_id SERIAL PRIMARY KEY,
    exemplar_id INTEGER NOT NULL UNIQUE REFERENCES exemplars(exemplar_id) ON DELETE RESTRICT,
    data        DATE NOT NULL DEFAULT CURRENT_DATE,
    motiu       VARCHAR(20) NOT NULL
                  CHECK (motiu IN ('deteriorament','obsolet','duplicat','perdua','baixa_demanda')),
    desti       VARCHAR(20) NOT NULL
                  CHECK (desti IN ('venda_benefica','reciclatge','cessio','destruccio')),
    autoritzat_per VARCHAR(80) NOT NULL,
    observacions   TEXT
);

CREATE INDEX idx_adq_via  ON adquisicions (via, data);
CREATE INDEX idx_exp_data ON expurgacions (data DESC);

Les decisions que cal saber defensar:

  • UNIQUE (exemplar_id) a les dues taules converteix la relació en 1:1 opcional: un exemplar té com a molt un origen registrat i com a molt una expurgació. Sense aquell UNIQUE seria 1:N i un exemplar podria aparèixer donat dues vegades.
  • El CHECK de coherència per via és la peça de disseny més valuosa: fa impossible una compra sense factura o una donació amb proveïdor. L'alternativa —tres taules filles adquisicions_compra, adquisicions_donacio, adquisicions_intercanvi amb el patró de jerarquia de l'exercici 4— és més neta conceptualment i més pesada de consultar. Amb tres subtipus de dues o tres columnes cadascun, el CHECK guanya.
  • expurgacions → exemplars és RESTRICT i adquisicions → exemplars és CASCADE. No és una incoherència: si s'esborra l'exemplar de la base de dades, la seva procedència deixa d'importar, però una expurgació és un acte administratiu que ha de sobreviure i impedir l'esborrat.
  • No es modifica exemplars. L'estat retirat ja existia; l'única cosa que aporta l'expurgació és la documentació del perquè. Qualsevol consulta anterior segueix funcionant paraula per paraula.

Rúbrica d'Autoavaluació

Puntua el teu disseny de cada exercici amb aquesta llista. Un disseny correcte compleix els vuit primers punts; els dos últims separen un disseny correcte d'un bon disseny.

# Criteri Com comprovar-ho
1 Totes les preguntes de l'enunciat es poden respondre Escriu la consulta de cada pregunta. Si alguna necessita una dada que no és a cap taula, el disseny està incomplet
2 Cap atribut multivaluat Busca columnes que puguin contenir «diversos» valors: llistes amb comes, telefon1/telefon2, camps de text amb separadors
3 Tota taula té clau primària Sense excepcions, incloses les taules de reunió N:M
4 Tota relació està materialitzada amb clau forana declarada No val «l'aplicació ho controla». Si el motor no ho declara, no està garantit
5 Cada clau forana té una acció ON DELETE decidida a consciència Recorre la llista i justifica'n una per una: CASCADE, RESTRICT, SET NULL, SET DEFAULT o NO ACTION
6 Cada regla de negoci de l'enunciat té la seva restricció... o la seva nota Fes la llista de regles de l'enunciat i marca al costat de cadascuna: CHECK, UNIQUE, índex parcial, EXCLUDE, disparador o «responsabilitat de l'aplicació»
7 Els conjunts tancats de valors estan restringits Tot estat, tipus o motiu ha de tenir CHECK IN (...), un domini o una taula de catàleg
8 Nomenclatura consistent Mateix idioma, mateix nombre (singular/plural), mateix estil de sufix _id, mateix estil de nom de restricció
9 Les dades històriques no es poden destruir amb un UPDATE Preus, tarifes i imports facturats congelats a la fila que els va usar; vigències com a files noves, no com a modificacions
10 Hi ha índexs per a les consultes freqüents de l'enunciat Almenys les claus foranes que es fan servir en JOIN i les columnes dels filtres habituals

Com puntuar. Si falles el punt 1, torna a l'enunciat: el disseny no serveix. Si falles el 2, el 3 o el 4, tens un problema estructural. Els punts 5 a 8 són els que separen un esquema d'estudiant d'un de producció. Els punts 9 i 10 només s'apliquen als enunciats que els esmenten.

Conclusió

Has dissenyat cinc esquemes complets des de zero: un videoclub amb dues relacions N:M —una d'elles amb atribut propi—, una ampliació de BiblioRed que va haver d'encaixar amb un esquema en producció sense tocar-lo, una plataforma de cursos amb jerarquia de continguts i autoreferència, un taller mecànic amb jerarquia de generalització i relació ternària, i un sistema de tarifes on la clau inclou el temps. En tots cinc, la feina real no va ser al CREATE TABLE sinó a les dues decisions que el precedeixen: què és una entitat i què és un atribut, i quina regla de negoci pot garantir el motor i quina no.

Repassa la llista d'eines que has fet servir per codificar regles: CHECK d'una columna i de diverses, UNIQUE compost, índex únic parcial (una còpia llogada, una matrícula activa, una opció correcta), clau forana composta amb discriminant per tancar una jerarquia, EXCLUDE USING gist per a intervals que no se solapen, i dominis per a conjunts de valors reutilitzables. I recorda les tres regles que cap d'elles va poder expressar —el màxim de peticions actives, l'aforament d'una sessió i la totalitat d'una jerarquia—, perquè saber on s'acaba el que és declaratiu és tan important com saber usar-ho.

A 07-03, Exercicis de Normalització, la feina s'inverteix. En lloc de partir de requisits i arribar a taules, partiràs de taules que ja existeixen i que estan malament: llistats plans amb dades repetides, històrics amb el nom del soci copiat a cada fila, taules on canviar un telèfon obliga a tocar catorze registres. N'hauràs d'escriure les dependències funcionals, calcular clausures, trobar totes les claus candidates, dir en quina forma normal estan i per què, i descompondre-les pas a pas amb l'SQL que fa la migració. Res de requisits: només taules, dades i l'anàlisi formal que revela el que amaguen.

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