El correu de l'Helena és el context; això és el contracte. Un requisit serveix d'alguna cosa només si es pot comprovar: "el sistema ha de gestionar bé els préstecs" no és un requisit, és un desig. "Un exemplar no pot aparèixer en dos préstecs amb data_devolucio IS NULL, i la base de dades ha de rebutjar l'intent" sí que ho és, perquè hi ha una manera objectiva de saber si es compleix: intentar-ho i veure si falla.

Aquesta lliçó és aquesta especificació, agrupada en sis blocs —dades, integritat, consulta, rendiment, seguretat i lliurament— i tancada amb la rúbrica amb què es corregeix el projecte, perquè puguis autoavaluar-te abans de donar-lo per acabat. Dins de cada bloc hi ha dues capes: el que no és negociable, perquè és el que el projecte pretén ensenyar, i el que queda al teu criteri, perquè dissenyar és triar i a 12-04 veuràs que diverses solucions diferents són igual de correctes.

1. Convencions obligatòries
2. Requisits de dades (RD)
3. Requisits d'integritat (RI)
4. Requisits de consulta (RC): les 15 consultes
5. Requisits de rendiment (RP)
6. Requisits de seguretat (RS)
7. Requisits de lliurament (RE)
8. Criteris de qualitat
9. Rúbrica d'avaluació
10. Errors habituals i consells
11. Exercicis
12. Conclusió

Són les del curs, i es donen per sabudes (05-01, 11-02). Es llisten perquè formen part de la correcció:

Aquestes són les entitats mínimes obligatòries. Pots afegir-hi columnes, i pots afegir-hi taules si les justifiques; el que no pots és treure'n cap ni fusionar-ne dues.

El que no és negociable, i per què:

1. La separació obra / exemplar (RD-05 + RD-07). És l'objecte del projecte. Una sola taula llibres amb un comptador és un suspens automàtic, per ben escrit que estigui la resta.
2. La N:M obres_autors (RD-06). Una columna autor a obres no permet dos autors, i una autor1/autor2 és l'antipatró de les columnes repetides (01-05). Aquí sí, PK composta (obra_id, autor_id) sense id propi: és una taula pont pura i aquella era l'alternativa que 05-01 va descriure.
3. La jerarquia de bibliotecaris (RD-09). responsable_id referenciant la mateixa taula, nul·lable només a la direcció. És el que alimenta el SELF JOIN de 03-06 i la CTE recursiva de 10-02.
4. L'històric que no s'esborra (RD-10). Cap DELETE sobre prestecs. Les baixes de socis i d'exemplars són lògiques, i les FK cap a prestecs han d'impedir físicament l'esborrat del pare.
5. prestecs penja d'exemplars i reserves penja d'obres. Invertir qualsevol de les dues trenca el model.

El que queda al teu criteri: si editorials és taula o columna de text (es demana taula, però accepta discussió), si obres porta idioma o nombre de pàgines, si socis desa el telèfon, si exemplars desa la signatura topogràfica, si reserves té taula d'estats o CHECK, i com distingeixes un exemplar extraviat d'un donat de baixa.

Volum mínim de dades de prova: 3 seus, 6 matèries, 5 editorials, 8 autors, 10 obres, 15 exemplars, 12 socis, 25 préstecs, 4 reserves i 4 multes. Menys que això no permet comprovar les consultes; molt més, no aporta res a mà. (El joc de dades de referència de 12-03 fa servir 3 / 6 / 5 / 10 / 12 / 20 / 15 / 36 / 5 / 6.)

Totes aquestes regles han d'estar declarades a la base de dades. Que l'aplicació també les comprovi està bé; que només l'aplicació les comprovi és exactament el que 05-01 anomenava "confiar que tothom se'n recordi, de validar".

Aquest requisit es mereix el seu apartat perquè és on es veu si has entès el mòdul 8. La regla és: hi pot haver molts préstecs de l'exemplar 1 —la seva història sencera— però com a molt un amb data_devolucio IS NULL.

Les quatre sortides possibles, i per què tres no valen:

La solució que demana el projecte és un índex únic parcial (08-02): un índex que només conté les files que compleixen una condició, i que per tant només imposa unicitat entre elles.

Es llegeix literalment: "entre els préstecs sense retornar, l'exemplar_id és únic". És declaratiu, ho fa complir el motor, ocupa només tantes entrades com préstecs vius hi hagi —sis, no centenars de milers— i de propina accelera totes les consultes de préstecs actius. A 12-03 s'implementa i a 12-04 es compara amb l'alternativa de la restricció EXCLUDE.

Nota de dialecte: els índexs parcials són de PostgreSQL, SQLite i (amb matisos) SQL Server, on s'anomenen filtered indexes. MySQL no els té, i allà la solució habitual és una columna generada amb NULL quan el préstec està tancat, més un UNIQUE sobre ella — perquè UNIQUE ignora els nuls.

Aquestes quinze consultes són el lliurable 03-consultes.sql. Estan ordenades per dificultat creixent i cadascuna porta la tècnica que exercita i la lliçó on es va ensenyar, perquè sàpigues on tornar si t'encalles. Totes fan servir DATE '2026-06-30' com a data de referència.

Cinc condicions que s'apliquen a les quinze:

1. Cada consulta va precedida d'un comentari que digui quina pregunta respon i, si maneja una mètrica, com la defineix (11-04). "Préstecs" inclou els actius? I els de socis de baixa? Escriu-ho.
2. Cap consulta pot perdre files per un JOIN mal triat. RC-06, RC-12 i RC-15 han d'ensenyar els zeros: l'obra sense exemplars, el mes sense préstecs i la seu sense res en una matèria.
3. Tot ORDER BY d'un rànquing necessita desempat. Sense ell, dues execucions poden retornar ordres diferents i l'informe deixa de ser reproduïble.
4. Cada consulta lliurada va amb el seu resultat. No n'hi ha prou amb el SQL: cal haver-lo executat.
5. Cap consulta fa servir SELECT *. Columnes explícites, sempre (11-02).

El projecte es prova amb desenes de files, però es dissenya per a desenes de milers de socis i centenars de milers de préstecs. Els índexs es justifiquen amb aquesta mida, no amb la del fitxer de proves.

RP-05 i RP-07 són els que de debò es corregeixen. Posar deu índexs és fàcil; explicar per què aquests deu i no uns altres és el que demostra criteri.

Tres rols, segons el model d'11-03: es concedeixen privilegis a rols de grup i els usuaris se'n fan membres, mai al revés.

I quatre requisits sobre les dades personals, que aquí no són un adorn: un historial de préstecs és un registre del que llegeix una persona, una de les dades més sensibles que pot desar una biblioteca.

• RS-04. Cap rol d'aplicació té DELETE sobre l'històric, i cap no és propietari de les taules.
• RS-05. Els informes de direcció se serveixen de vistes agregades que no exposen què ha llegit cada soci. Qui necessita la dada agregada no necessita la dada individual.
• RS-06. La baixa d'un soci és lògica; l'anonimització posterior (substituir nom, document i email per valors neutres conservant l'id) ha d'estar prevista, per poder complir una petició de supressió sense destruir l'estadística.
• RS-07. Les dades de prova són íntegrament fictícies: noms inventats, documents amb format vàlid però inexistents i correus a @example.com, que és un domini reservat precisament per a això. No facis servir mai dades reals, ni tan sols les teves, en un fitxer que acabarà en un repositori.

Opcionalment, i com a exercici d'11-03: implementa RLS perquè un bibliotecari només vegi els socis de la seva seu. Suma a la rúbrica, però no és obligatori i no compensa lliurar-ho malament.

RE-01. Els fitxers s'executen en ordre: 01, 02, 03. La seqüència completa ha de funcionar d'una tirada sobre una base buida:

RE-02. L'esquema és idempotent pel mateix mecanisme que botigaverda.sql (01-06): DROP TABLE IF EXISTS ... CASCADE al principi, en ordre invers a les dependències. És el correcte per a un script de creació des de zero; per a un sistema en producció serien migracions versionades (05-06), i dir-ho a l'informe suma.

RE-03. Els fitxers van en un repositori git amb el seu README.md (12-05), sense credencials de cap mena.

RE-04. Cada consulta de 03-consultes.sql porta a sobre el seu número RC-nn, la pregunta que respon i les lliçons que aplica.

Aquests no són requisits amb número: són la diferència entre un projecte que funciona i un que a més està ben fet. Surten íntegres d'11-02.

• Format uniforme. Paraules clau en majúscula, una clàusula per línia, àlies de taula curts i consistents, sagnat estable. Si dues consultes del mateix fitxer es veuen diferents, es nota.
• Noms que no necessiten comentari. data_devolucio sí; dd no. uq_prestec_actiu_per_exemplar sí; idx3 no.
• Comentaris que expliquen el perquè, no el què. -- El LEFT JOIN és obligatori: hi ha obres sense cap exemplar és útil. -- Uneix obres amb exemplars és soroll.
• Ni una consulta sense executar. Un 03-consultes.sql amb una consulta que dona error és la fallada més cara de totes, perquè costa zero evitar-la.
• Ni una definició implícita. Cada mètrica de l'informe diu què inclou i què exclou (11-04).
• Res de sobreenginyeria. Tres disparadors, cinc vistes materialitzades i una taula d'auditoria en un projecte de dotze taules no sumen: resten, perquè cal mantenir-les i defensar-les.

Fes-la servir com a llista d'autoavaluació abans de lliurar. La columna de pes indica quant compta cada bloc.

Quatre errors anul·len el bloc sencer, per molt bé que estigui la resta:

1. Una sola taula llibres en lloc d'obres + exemplars → model a zero.
2. RI-03 sense resoldre o resolta només a l'aplicació → integritat a zero.
3. Una consulta del lliurable que dona error en executar-se → consultes a zero.
4. Dades personals reals al repositori → lliurament a zero.

• Llegir els requisits una vegada, al principi. Torna a aquesta lliçó en acabar cada bloc i ratlla el que has complert. La meitat del que es perd a la rúbrica és material oblidat, no material mal fet.
• Prendre els mínims com a objectius. "12 socis" és el terra perquè les consultes tinguin sentit, no la meta. Però més d'un parell de centenars de files a mà és temps llençat: per a volum, generate_series (12-03).
• Confondre requisit i solució. RI-03 diu què s'ha de complir; l'índex parcial és com ho resolem aquí. Si trobes una altra manera que també ho garanteixi a la base, és vàlida — i a 12-04 n'hi ha una.
• Resoldre a l'aplicació el que demana la base. "Ja ho comprova el meu codi abans d'inserir" no compleix RI-03. Dues peticions simultànies s'hi colen, i això és exactament el que el mòdul 9 explicava.
• Escriure les quinze consultes d'una tirada i executar-les al final. Executa cadascuna tan bon punt l'escriguis i comprova el recompte de files a cada JOIN (11-04). Un error a la tercera contamina les dotze següents.
• Consell: converteix aquesta lliçó en un fitxer requisits.md del repositori, amb caselles de verificació. És la checklist d'11-02 aplicada al teu propi projecte, i és el que consultarà qui et corregeixi.
• Consell: escriu primer la consulta més difícil que hi vegis (probablement RC-11 o RC-12). Si el model aguanta la més difícil, aguanta les altres catorze; si no, val més descobrir-ho abans de carregar les dades.
• Consell: desa la sortida de cada consulta en un fitxer. Quan canviïs l'esquema o les dades, podràs comparar i veure què s'ha mogut. És la "xifra de control" d'11-04 aplicada al projecte.

Com a 12-01, són tasques del projecte: porten esbós o rúbrica, no solució completa.

Converteix els requisits en un pla de treball teu: una taula amb les tasques, la seva dependència amb les altres, l'estimació en hores i el requisit que tanca cadascuna. Ha de cobrir del model al lliurament. Si el teu pla no inclou una tasca explícita de "generar dades de prova amb casos límit", està incomplet.

Abans d'escriure gens de SQL, raona sobre RI-03: (1) per què UNIQUE (exemplar_id, data_devolucio) no funciona a PostgreSQL, i en quin motor sí que funcionaria? (2) Escriu l'INSERT exacte que hauria de fallar i el que ha de continuar funcionant. (3) Què passa amb l'índex parcial si demà es decideix que un exemplar es pot prestar "en sala" alhora que està prestat a domicili?

Abans de RC-08, escriu la definició exacta d'aquestes cinc mètriques, dient què inclou i què no, a l'estil de la taula de definicions d'11-04: préstecs del període, soci actiu, taxa de retard, deute pendent i obra disponible. Per a cadascuna, indica a més quina altra definició raonable existeix i com canviaria la xifra.

Rúbrica de la Tasca 1

Un pla acceptable té entre 8 i 12 tasques i respecta aquestes dependències: model → DDL → dades → consultes → índexs → vistes i seguretat → informe. Els dos errors de planificació que es repeteixen són deixar les dades de prova per al final —i descobrir llavors que el model no permet representar un cas— i deixar l'informe per a l'últim dia, quan ja no recordes per què vas prendre la meitat de les decisions. Escriu l'informe a mesura que decideixes.

Solució de la Tasca 2

(1) Perquè a l'estàndard SQL, i a PostgreSQL, NULL no és igual a NULL, així que dues files amb (1, NULL) no es consideren duplicades i el UNIQUE les admet totes dues (04-03, 05-01). A SQL Server sí que fallaria, perquè tracta tots els NULL com a iguals a efectes de l'índex únic — i a PostgreSQL 15+ es pot imitar amb UNIQUE NULLS NOT DISTINCT, encara que per a aquest cas l'índex parcial continua sent millor perquè a més indexa només les files vives.

(2) Ha de fallar un segon préstec obert del mateix exemplar, i ha de continuar funcionant un altre préstec tancat del mateix exemplar:

La primera retorna:

(3) L'índex deixaria de valer tal qual, perquè ja no hi hauria "com a molt un préstec actiu" sinó "com a molt un de cada modalitat". La solució seria afegir una columna modalitat i incloure-la a l'índex: ON prestecs (exemplar_id, modalitat) WHERE data_devolucio IS NULL. És un bon recordatori que una restricció codifica una regla de negoci concreta, i que quan la regla canvia, la restricció canvia amb ella — la qual cosa, per cert, és un avantatge: si la regla visqués repartida pel codi de l'aplicació, ningú no sabria on tocar.

Esbós de la Tasca 3

Dues de les cinc, per fixar el nivell de detall esperat:

La lliçó de fons és la d'11-04: no hi ha una definició correcta, hi ha una definició escrita. El greu no és triar malament; és publicar dues xifres diferents al mateix informe sense dir en què es diferencien.

Ja tens el contracte del projecte:

• 12 requisits de dades amb les entitats mínimes i els seus atributs. L'innegociable: la separació obra / exemplar, la N:M obres_autors amb PK composta, la jerarquia reflexiva de bibliotecaris, l'històric que no s'esborra, i que prestecs pengi d'exemplars mentre reserves penja d'obres.
• 14 requisits d'integritat, tots declarats a la base i amb nom. El difícil és RI-03 —un exemplar, un sol préstec actiu—, que no es pot resoldre amb UNIQUE (pels nuls), ni amb CHECK (no pot mirar altres files), i que es resol amb un índex únic parcial WHERE data_devolucio IS NULL.
• 15 consultes ordenades per dificultat, cadascuna amb la seva tècnica i la seva lliçó: del filtre simple a l'anti-join, del HAVING a l'agregat condicional, de la correlacionada al LATERAL, i de la sèrie temporal sense buits al pivot, la finestra i la CTE recursiva. Amb cinc condicions transversals: comentari amb la definició, res de files perdudes, desempat als rànquings, resultat adjunt i ni un SELECT *.
• Rendiment: índexs a les FK que es naveguen, el parcial de RI-03, un per a la sèrie temporal i un altre per a la cerca per títol, cadascun justificat amb la consulta que el fa servir, dos EXPLAIN comentats i la llista del que decideixes no indexar.
• Seguretat: tres rols de privilegi mínim, sense DELETE sobre l'històric, informes servits per vistes agregades, baixa lògica amb anonimització prevista i dades íntegrament fictícies.
• Lliurament: quatre fitxers que s'executen en ordre sobre una base buida, esquema idempotent, i la rúbrica de sis criteris —model 25 %, integritat 20 %, consultes 25 %, rendiment 10 %, seguretat 10 %, lliurament 10 %— amb quatre errors que anul·len el seu bloc sencer.

Saps què cal fer i amb què se't mesurarà. Falta el com. A la lliçó següent, Implementació del projecte, hi ha la guia de construcció en set passos: de l'enunciat al diagrama entitat-relació complet, amb les decisions difícils justificades —per què exemplars és taula, per què es desa data_prevista en lloc de calcular-la, per què les multes no són una columna i com es modela una cua—; el 01-esquema.sql comentat amb l'índex parcial explicat a fons; com generar dades coherents i quins casos límit han de contenir; el mètode per escriure consultes sense equivocar-te, amb dues resoltes d'exemple; com decidir els índexs a partir de les consultes i no al revés; què encapsular en vistes, procediments i disparadors sense passar-se; i el cronograma de treball.