A la lliçó anterior parties d'un client que explicava el que necessitava i arribaves a un esquema. Aquí el punt de partida és el contrari: una taula que ja existeix i que està malament. Un llistat pla exportat d'un full de càlcul, un històric amb el nom del soci copiat a cada fila, una taula on canviar un telèfon obliga a tocar catorze registres. La teva feina és demostrar formalment què li passa, predir què trencarà i arreglar-la sense perdre informació.

Com treballar aquesta lliçó. La normalització és de les poques parts de les bases de dades que es pot fer sense ordinador, amb paper i llapis, i convé fer-ho així almenys les primeres vegades. El procediment de cada exercici és sempre el mateix:

1. Escriu les dependències funcionals que es dedueixen de les regles de negoci i de les dades.
2. Calcula les clausures que necessitis i determina totes les claus candidates.
3. Classifica els atributs en primers i no primers.
4. Recorre les formes normals en ordre —1FN, 2FN, 3FN, FNBC, 4FN— i atura't a la primera que s'incompleixi, assenyalant la dependència culpable.
5. Descompon, comprova que la descomposició és sense pèrdua i comprova si conserva les dependències.

Un avís sobre les dades: en diversos exercicis veuràs una taula amb files concretes. Les dades no demostren una dependència funcional, només la poden refutar. Que en cinc files cada assessor aparegui amb una única especialitat no prova que assessor → especialitat; el que ho prova és la regla de negoci. En canvi, si dues files tinguessin el mateix assessor amb especialitats diferents, quedaria refutada. Les dades són la comprovació, les regles són la font.

En aquesta lliçó no hi ha disseny des de requisits (això és 07-02) ni plans d'execució (això és 07-04). Només anàlisi formal, descomposició i una decisió final de desnormalització.

No cal cap joc de dades previ: cada exercici porta el seu i els del bloc C inclouen l'SQL per crear la taula de partida. Sí que convé tenir a mà:

• La definició de dependència funcional i els axiomes d'Armstrong (reflexivitat, augment, transitivitat) de la lliçó 05-01.
• L'algorisme de clausura d'atributs X⁺: parteix de X, i mentre hi hagi alguna dependència Y → Z amb Y ⊆ X⁺, afegeix Z a X⁺.
• Les definicions de les formes normals de la lliçó 05-02, en la formulació que farem servir aquí:

Recordatori de vocabulari: un atribut és primer si pertany a alguna clau candidata; no primer en cas contrari. Una dependència és parcial si un atribut no primer depèn d'un subconjunt propi d'una clau candidata; transitiva si va de la clau a un atribut no primer passant per un altre atribut no primer; i total o completa si depèn de la clau sencera i no de cap part seva.

Si vols executar l'SQL del bloc C, qualsevol base PostgreSQL buida serveix. Els INSERT ... SELECT DISTINCT funcionen igual a SQLite.

1. Bloc A — Bàsic: dependències funcionals, clausures i claus candidates (exercicis 1-3)
2. Bloc B — Intermedi: diagnòstic de taules reals (exercicis 4-6)
3. Bloc C — Avançat: normalització completa, FNBC, 4FN i desnormalització (exercicis 7-10)
4. Errors habituals i consells
5. Exercicis de reforç

Dificultat: Bàsic

Enunciat. BiblioRed ofereix un servei de reprografia. Cada treball de còpia es registra en una única taula amb aquests atributs:

copia_id, soci_id, soci_email, data, pagines, tipus_paper, tarifa_pagina, import

Regles de negoci:

• Cada treball de còpia té un identificador propi, copia_id.
• Cada soci té una única adreça electrònica i no hi ha dos socis amb el mateix correu.
• La tarifa per pàgina depèn exclusivament del tipus de paper (normal, reciclat, setinat).
• L'import és el resultat de multiplicar les pàgines per la tarifa per pàgina.

Es demana:

• (a) Escriure el conjunt F de dependències funcionals.
• (b) Calcular {copia_id}⁺ pas a pas, indicant quina dependència es fa servir a cada pas.
• (c) Calcular {soci_email}⁺ i {tipus_paper, pagines}⁺.

Pista. «No hi ha dos socis amb el mateix correu» és una dependència funcional en la direcció que potser no esperes.

Solució

(a) El conjunt F:

(b) Clausura de {copia_id} pas a pas:

(c)

Resultat esperat

Com que {copia_id}⁺ conté tots els atributs de la relació, copia_id és una superclau. I com que és un únic atribut, també és mínima: és una clau candidata.

{soci_email}⁺ es queda en dos atributs: soci_email no és superclau, encara que sí que determini soci_id. {tipus_paper, pagines}⁺ es queda en quatre: tampoc.

Explicació. Tres punts que convé fixar:

• f3 és la dependència que més gent oblida. «No hi ha dos socis amb el mateix correu» significa que el correu determina el soci: soci_email → soci_id. És una restricció UNIQUE traduïda al llenguatge de les dependències funcionals, i és el que converteix el correu en una clau alternativa de l'entitat soci. Ignorar-la porta a comptar malament les claus candidates en exercicis posteriors.
• f5 és una dependència real, no una fórmula. La temptació és dir «l'import es calcula, no depèn». Depèn: donats uns valors concrets de pagines i tarifa_pagina, l'import està determinat. Que sigui calculable no l'exclou de l'anàlisi; el que passa és que al disseny final probablement el resoldrem amb una columna generada en lloc d'amb una taula nova.
• La clausura es calcula fins que deixa de créixer, no fins que has recorregut la llista una vegada. Al pas 3 apareix tarifa_pagina, que és el que al pas 4 permet disparar f5. Si t'haguessis aturat després d'una passada, hauries conclòs que import no és a la clausura i copia_id no seria superclau.

Dificultat: Bàsic

Enunciat. L'agenda d'ocupació de sales de BiblioRed s'ha exportat a una única taula:

OCUPACIO(sala, dia, hora, esdeveniment, aforament, sucursal)

Regles de negoci:

• Cada sala és en una única sucursal i té un únic aforament.
• En una sala, un dia i una hora determinats només s'hi pot celebrar un esdeveniment.
• Cada esdeveniment se celebra en un únic lloc, un únic dia i a una única hora.

Es demana:

• (a) Escriure F.
• (b) Trobar totes les claus candidates, justificant que no n'hi ha més.
• (c) Classificar els atributs en primers i no primers.

Pista. Comença buscant els atributs que no apareixen al costat dret de cap dependència: han de ser a totes les claus.

Solució

(a)

(b) Cerca sistemàtica de claus candidates.

Primer, la classificació d'atributs segons on apareixen:

Com que cap atribut no queda fora de tots els costats drets, no hi ha un nucli obligatori i cal provar combinacions. Calculem clausures:

Claus candidates: {esdeveniment} i {sala, dia, hora}.

{esdeveniment} és mínima perquè és un únic atribut. {sala, dia, hora} és mínima perquè cap dels seus tres subconjunts propis de dos atributs no és superclau ({sala,dia}, {sala,hora} i {dia,hora} es queden curts, com mostren les clausures). Qualsevol altre conjunt que sigui superclau conté una de les dues, per tant no és mínim.

(c)

• Atributs primers (pertanyen a alguna clau candidata): esdeveniment, sala, dia, hora.
• Atributs no primers: aforament, sucursal.

Resultat esperat

Explicació. El mètode que convé automatitzar és aquest:

1. Atributs que mai no apareixen a la dreta → són a totes les claus candidates (aquí no n'hi ha cap).
2. Atributs que mai no apareixen a l'esquerra → no són a cap clau candidata (aquí, aforament i sucursal; per això són no primers sense necessitat de més comprovacions).
3. La resta s'han de provar, començant pels conjunts petits.

L'error freqüent és quedar-se a la primera clau candidata que apareix. És molt fàcil veure {sala, dia, hora} —és la que suggereix la lectura de l'enunciat— i no adonar-se que {esdeveniment} també ho és, perquè g3 és una dependència que es llegeix «al revés» de com es pensa l'agenda. I aquella segona clau canvia el diagnòstic: aforament i sucursal depenen parcialment de {sala, dia, hora} (perquè sala n'és un subconjunt propi) i transitivament de {esdeveniment}. La taula ni tan sols està en 2FN.

Un altre error: comptar {esdeveniment, sala} com a clau candidata perquè la seva clausura és tot. És superclau, no clau candidata: li sobra sala. La minimalitat és part de la definició.

Dificultat: Bàsic

Enunciat. Fent servir les dues relacions dels exercicis 1 i 2, classifica cadascuna d'aquestes dependències respecte de la clau candidata que s'indica. Les categories són: total (o completa), parcial i transitiva. Indica a més quina forma normal viola cadascuna.

Solució

Resultat esperat

REPROGRAFIA està en 2FN però no en 3FN (dues dependències transitives: tipus_paper → tarifa_pagina i soci_id → soci_email).

OCUPACIO està només en 1FN: sala → aforament i sala → sucursal són parcials respecte de {sala, dia, hora}.

Explicació. El punt que cal interioritzar és a les files 4 i 6: la mateixa dependència es classifica de manera diferent segons la clau que prenguis com a referència. sala → sucursal és parcial respecte de {sala, dia, hora} i transitiva respecte de {esdeveniment}. No és una contradicció: són dues descripcions del mateix problema.

D'aquí en surt la regla operativa que estalvia temps: n'hi ha prou que una dependència violi una forma normal respecte d'una clau candidata perquè la relació no estigui en aquella forma normal. No cal que la violi respecte de totes. Per això l'ordre correcte de treball és: trobar totes les claus candidates primer, i només després avaluar les formes normals.

Segon punt: REPROGRAFIA està en 2FN automàticament, sense comprovar res, perquè la seva única clau candidata té un sol atribut. Una clau d'un atribut no té subconjunts propis no buits, per tant no hi pot haver dependències parcials. Tota relació amb clau candidata única i simple està en 2FN per construcció. Comprovar-ho a mà és temps perdut; reconèixer-ho és una drecera legítima.

Tercer punt: la fila 5 recorda que una dependència el costat dret de la qual és un atribut primer no viola mai la 2FN ni la 3FN, perquè les dues formes parlen explícitament d'atributs no primers. Sí que podria violar la FNBC, que no distingeix.

Dificultat: Intermedi

Enunciat. L'àrea d'activitats culturals treballa amb aquesta taula, exportada d'un full de càlcul. Cada fila és la inscripció d'un soci a un esdeveniment.

Regles de negoci: cada esdeveniment té un únic ponent principal, se celebra en una única sala i en una única data; cada sala pertany a una sucursal i té un aforament; cada soci té un nom i un correu únic; cada ponent té un correu únic.

Es demana:

• (a) La clau candidata i les dependències funcionals.
• (b) En quina forma normal està i per què, assenyalant la dependència culpable.
• (c) Predir una anomalia concreta de modificació i una altra d'esborrament, anomenant la fila i la dada exactes de la taula anterior.

Solució

(a) Clau candidata: {esdeveniment_id, soci_id}. Una fila és «aquest soci, en aquest esdeveniment», i res més curt no identifica una fila.

Atributs primers: esdeveniment_id, soci_id, soci_email (perquè {esdeveniment_id, soci_email} també és clau candidata, per h6). Tots els altres són no primers.

(b) La taula està en 1FN i no en 2FN.

Les dependències culpables són h2 i h5:

• esdeveniment_id → titol_esdeveniment és parcial: esdeveniment_id és un subconjunt propi de la clau {esdeveniment_id, soci_id} i titol_esdeveniment és no primer. El mateix per a data, sala i ponent.
• soci_id → soci_nom és parcial pel mateix motiu.

Com que no arriba a 2FN, no té sentit preguntar-se per la 3FN encara. Però convé anotar que, un cop resolta la 2FN, quedarien pendents dues dependències transitives: h3 (esdeveniment_id → sala → aforament_sala, sucursal) i h4 (esdeveniment_id → ponent → ponent_email).

(c) Anomalies concretes.

Anomalia de modificació. La Rosa Calduch canvia el seu correu a [email protected]. El seu correu apareix a tres files: les tres de l'esdeveniment 101 (amb els socis 14, 11 i 13) i la de l'esdeveniment 105 (soci 15) — quatre files en total. Cal actualitzar-les totes quatre. Si l'operador filtra per esdeveniment_id = 101 i actualitza només aquelles tres, el resultat és una taula on la mateixa ponent té dos correus diferents: [email protected] a l'esdeveniment 101 i [email protected] a l'esdeveniment 105. La taula queda internament contradictòria i no hi ha cap restricció de la base de dades que ho impedeixi, perquè ponent_email no és clau de res.

Anomalia d'esborrament. L'Ivan Pereda (soci 15) cancel·la la seva inscripció a l'esdeveniment 105 i s'esborra l'última fila. Amb ella desapareix tota la informació de l'esdeveniment 105: que es deia «Club de lectura: Tokio blues», que va ser el 16 de juliol de 2026, que se celebrà a la Sala Est de la sucursal Est, que la sala té 50 places d'aforament i que el va moderar la Rosa Calduch. L'esdeveniment va existir, però la base de dades ja no ho sap. I de propina, si aquella era l'única fila on apareixia la Sala Est, també es perd que el seu aforament és 50 i que pertany a la sucursal Est.

Resultat esperat

Explicació. El valor de l'exercici és a l'apartat (c), i per això l'enunciat exigeix anomenar la fila i la dada exactes. Dir «hi ha redundància» o «hi pot haver inconsistències» no és un diagnòstic: és una descripció de l'olor. Un diagnòstic és «canviar el correu de la Rosa Calduch obliga a actualitzar quatre files, i n'hi ha prou que una es quedi sense actualitzar perquè la taula afirmi dues coses incompatibles».

Fixa't també que l'anomalia d'inserció apareix tota sola quan intentes donar d'alta una cosa que encara no té fills. L'esdeveniment 107 està programat per a l'octubre i encara no té inscrits: en aquesta taula és inexpressable, llevat que hi fiquis una fila amb soci_id nul, que trencaria la clau primària. Aquella impossibilitat és el senyal més clar que dues entitats diferents s'han ficat a la mateixa taula.

Un matís de model que convé veure: hem assumit «un únic ponent principal per esdeveniment». Si BiblioRed permetés diversos ponents per esdeveniment —i de fet ho permet: l'esdeveniment 104 en té dos—, h2 deixaria de ser certa i la taula ni tan sols estaria en 1FN de manera neta, perquè tindríem un grup repetitiu encobert. Aquell cas és exactament el de l'exercici 9.

Dificultat: Intermedi

Enunciat. El departament de recaptació manté aquesta taula per al seu informe mensual:

sucursal_id és la sucursal a la qual està adscrit el soci.

Es demana: clau candidata, dependències, forma normal i dependència culpable, i una anomalia concreta de cada tipus amb fila i dada exactes.

Pista. La clau és d'un sol atribut. Això descarta automàticament un tipus de violació i obliga a buscar l'altre.

Solució

Clau candidata: {multa_id}.

Forma normal: 2FN, no 3FN.

Està en 2FN automàticament: la clau candidata és un únic atribut, per tant no hi pot haver dependències parcials.

No està en 3FN. Hi ha dues cadenes transitives, i totes dues són culpables:

• multa_id → soci_id → soci_nom (i → sucursal_id), amb soci_id no primer.
• multa_id → soci_id → sucursal_id → sucursal_nom, sucursal_telefon, amb sucursal_id no primer. Aquesta és una transitivitat de dos salts, i segueix essent transitivitat.

Anomalies concretes.

Modificació. La sucursal Centre canvia de telèfon al 935550099. El valor 935550001 apareix a tres files: les multes 1, 4 i 7. Cal actualitzar-les totes tres. Si un UPDATE amb WHERE soci_id = 14 actualitza només les multes 1 i 4, la multa 7 (de la Sònia Mestre, també de Centre) continua dient 935550001 i la taula afirma que la sucursal Centre té dos telèfons. Amb 12.000 socis i quatre sucursals, el nombre de files a tocar per un sol canvi de telèfon és de diversos milers.

Inserció. BiblioRed obre una cinquena sucursal, «Ponent», telèfon 935550005. No hi ha cap manera de registrar-la en aquesta taula fins que un soci adscrit a Ponent rebi una multa. Les dades de la sucursal només existeixen com a acompanyament d'una multa.

Esborrament. S'anul·la la multa 5 (Núria Bastos, deteriorament, 6,50 €) i s'esborra la fila. Amb ella desapareix que existeix una sucursal Sud, que el seu telèfon és 935550003, que la sòcia 16 es diu Núria Bastos i que està adscrita a Sud. És l'única fila de la taula on apareix aquella sucursal.

Resultat esperat

Descomposició a 3FN (només el resultat; el procediment complet és a l'exercici 7):

SUCURSALS(sucursal_id, sucursal_nom, sucursal_telefon) SOCIS(soci_id, soci_nom, sucursal_id) MULTES(multa_id, soci_id, motiu, import, estat)

Explicació. Aquest és el cas de manual de violació de 3FN, i val la pena veure per què el diagnòstic correcte és 2FN i no «1FN» ni «3FN»:

• No és 1FN el diagnòstic perquè tots els atributs són atòmics: no hi ha llistes, ni grups repetitius, ni cel·les amb diversos valors.
• No arriba a 3FN perquè hi ha atributs no primers que depenen d'altres atributs no primers.
• La drecera de la pista: amb clau candidata simple, la 2FN està garantida i tota l'atenció ha d'anar a buscar cadenes clau → X → Y.

Un detall que es passa per alt: la transitivitat encadenada. sucursal_telefon és a dos salts de la clau (multa_id → soci_id → sucursal_id → sucursal_telefon). Continua essent una violació de 3FN, i la descomposició correcta no és ficar la sucursal dins de la taula de multes: és reconèixer que hi ha tres entitats amagades —multa, soci i sucursal— i treure-les en cascada. Un error freqüent és descompondre només un nivell i quedar-se amb SOCIS(soci_id, soci_nom, sucursal_id, sucursal_nom, sucursal_telefon), que continua sense estar en 3FN.

Dificultat: Intermedi

Enunciat. Un gabinet d'assessoria de Vallmar assigna assessors a clients per especialitat:

Regles de negoci:

• Cada assessor treballa una única especialitat.
• Per a cada client i cada especialitat hi ha exactament un assessor assignat.
• Un client pot tenir diversos assessors (un per especialitat) i un assessor pot portar diversos clients.

Es demana: dependències, totes les claus candidates, atributs primers, forma normal exacta i dependència culpable, i les anomalies.

Pista. Comprova primer si algun atribut és no primer. La resposta canvia tota l'anàlisi.

Solució

Claus candidates. Calculem clausures:

Les dues superclaus de dos atributs són mínimes (cap dels seus subconjunts propis no ho és). Claus candidates: {client, especialitat} i {client, assessor}.

Atributs primers: client, especialitat, assessor. Tots tres. No hi ha cap atribut no primer.

Forma normal: 3FN, però no FNBC.

• 2FN: es compleix buidament. Les definicions de 2FN i 3FN parlen d'atributs no primers, i aquí no n'hi ha cap. Sense atributs no primers no hi pot haver dependències parcials ni transitives d'atributs no primers.
• 3FN: es compleix pel mateix motiu.
• FNBC: no es compleix. La definició de FNBC no distingeix primers de no primers: exigeix que el determinant de tota dependència no trivial sigui superclau. La dependència culpable és m2: assessor → especialitat, perquè {assessor}⁺ = {assessor, especialitat} no conté client: assessor no és superclau.

Anomalies.

Inserció. El gabinet fitxa la Lídia Serna, especialista en mercantil. No hi ha manera de registrar-ho: la clau primària exigeix un client, i la Lídia encara no en té cap. La dada «Lídia Serna és mercantilista» és inexpressable.

Esborrament. Òptica Vilamar rescindeix el contracte i s'esborren les seves dues files. Amb la fila (Òptica Vilamar, fiscal, Sergi Prat) desapareix l'únic registre que Sergi Prat és assessor fiscal: és el seu únic client a la taula.

Modificació. La Rita Bonet es recicla i passa de fiscal a comptable. La seva especialitat apareix a dues files (Forn Solé i Taller Ferrer). Si només se n'actualitza una, la taula afirma que la Rita Bonet és fiscal per a un client i comptable per a un altre, violant la primera regla de negoci.

Resultat esperat

Explicació. Aquest exercici existeix perquè és el contraexemple que demostra que la 3FN no n'hi ha prou. Tothom arriba a 3FN i dóna la feina per acabada; aquesta taula està en 3FN de manual i continua tenint les tres anomalies clàssiques.

El senyal d'alarma que cal aprendre a reconèixer: una dependència el costat esquerre de la qual és un sol atribut que també forma part d'una clau, però que per si sol no és clau. Aquí, assessor és primer (és a {client, assessor}) però no és superclau. Aquella configuració —determinant primer però no superclau— és exactament el forat pel qual la 3FN deixa passar redundància.

L'altra lliçó: el solapament de claus candidates. Les dues claus comparteixen l'atribut client. Sempre que dues claus candidates se solapen, convé comprovar la FNBC explícitament, perquè és la situació en què la 3FN i la FNBC divergeixen. Si les claus candidates són disjuntes o n'hi ha una de sola, 3FN i FNBC coincideixen a la pràctica gairebé sempre.

La descomposició d'aquesta taula —i el problema seriós que porta— és l'exercici 8.

Dificultat: Avançat

Enunciat. BiblioRed gestiona les comandes a proveïdors amb aquesta taula, tal com la va deixar la persona que es va jubilar:

Dades de catàleg: 9788401337208 és «Els pilars de la Terra» de Ken Follett; 9788401339097, «El mapa del temps» de Félix J. Palma; 9788483835609, «Tokio blues» de Haruki Murakami.

Porta aquesta taula de 1FN a 3FN, mostrant l'estat de les dades després de cada forma normal i escrivint l'SQL de la descomposició.

Solució

Pas 0 → 1FN: atomitzar.

La taula ni tan sols està en 1FN: telefons_proveidor és una llista amb comes i linies és un grup repetitiu complet dins d'una cel·la. La 1FN exigeix valors atòmics i cap grup repetitiu.

Resultat després de la 1FN, amb els telèfons a la seva pròpia taula i una fila per línia de comanda:

COMANDES_1FN(comanda_id, data, proveidor_id, proveidor_nom, proveidor_cif, proveidor_ciutat, isbn, titol, autor, unitats, preu_unit)

I TELEFONS_PROVEIDOR(proveidor_id, telefon) amb tres files: (P1, 973551111), (P1, 610222333), (P2, 935559999).

Anàlisi. Clau candidata de COMANDES_1FN: {comanda_id, isbn}. Dependències:

Pas 1FN → 2FN: eliminar les dependències parcials.

Culpables: n2 i n3. Totes dues tenen com a determinant un subconjunt propi de la clau i com a dependent atributs no primers.

Estat de les dades després de la 2FN:

comandes_2fn (3 files)

llibres_2fn (3 files)

linies_2fn (5 files)

Pas 2FN → 3FN: eliminar les dependències transitives.

comandes_2fn continua tenint comanda_id → proveidor_id → proveidor_nom, proveidor_cif, proveidor_ciutat. És transitiva: proveidor_id és no primer en aquella relació.

Esquema final en 3FN, amb claus i restriccions:

Resultat esperat

Comprovació que la descomposició és sense pèrdua: reconstruir l'original ha de donar exactament les cinc files de COMANDES_1FN.

Explicació. Quatre coses que aquest exercici ensenya i que no es veuen als exemples de joguina:

1. preu_unit es queda a linies_comanda, i és el correcte. La temptació és treure'l al catàleg de llibres, «perquè el preu és del llibre». No ho és: mira les dades. «Els pilars de la Terra» es va comprar a 21,90 € a la comanda 5001 i a 22,50 € a la 5002, a proveïdors diferents. I «Tokio blues» a 12,95 € i a 13,20 €. El preu depèn de la línia sencera —{comanda_id, isbn}—, no de l'ISBN. És una dependència total, i per tant es queda on és. Treure'l hauria estat una pèrdua d'informació, no una normalització.

2. SELECT DISTINCT és el cor de la migració. Cada CREATE TABLE ... AS SELECT DISTINCT projecta les columnes de la nova relació i deduplica. És exactament la projecció de l'àlgebra relacional. Si després del DISTINCT la nova taula té més files de les esperades, és el senyal que les dades contradiuen la dependència funcional: per exemple, si proveidors sortís amb tres files, significaria que en alguna comanda «P1» apareix amb una altra ciutat, i caldria netejar les dades abans de posar-hi la clau primària.

3. Els telèfons surten a la 1FN, no a la 3FN. Un atribut multivaluat no és un problema de dependències funcionals: és un problema d'atomicitat. Es resol al primer pas, creant una taula la clau primària de la qual és (proveidor_id, telefon).

4. proveidor_cif → proveidor_id (n5) no genera taula nova. És una dependència entre dos atributs que ja conviuen a proveidors, i significa que el CIF és una clau alternativa. Es materialitza amb UNIQUE, no amb una descomposició. Confondre «hi ha una dependència» amb «cal descompondre» és un error freqüent: només cal descompondre quan la dependència viola la forma normal que persegueixes.

Sobre SQLite: CREATE TABLE ... AS SELECT funciona igual, però no admet declarar claus ni restriccions a la mateixa instrucció, i ALTER TABLE ADD CONSTRAINT no existeix. Allà el patró és crear les taules finals amb el seu DDL complet i després INSERT INTO ... SELECT DISTINCT ....

Dificultat: Avançat

Enunciat. Reprèn la taula de l'exercici 6, ASSESSORIA(client, especialitat, assessor) amb m1: {client, especialitat} → assessor i m2: assessor → especialitat.

• (a) Descompon a FNBC eliminant la dependència culpable.
• (b) Demostra que la descomposició és sense pèrdua.
• (c) Demostra que no conserva les dependències i identifica quina es perd.
• (d) Mostra amb dades concretes què es pot colar a l'esquema descompost que l'original impedia.
• (e) Decideix què fer i justifica la decisió.

Solució

(a) Descomposició. El procediment estàndard per arribar a FNBC: donada la dependència culpable X → Y (aquí assessor → especialitat), es descompon en X ∪ Y i en R − Y.

Totes dues estan en FNBC: a R1 l'única dependència és assessor → especialitat i assessor és la clau; a R2 l'única dependència és la trivial i la clau és {client, assessor}, la relació sencera.

Amb les dades de l'exercici 6:

R1 (4 files)

R2 (6 files)

(b) Sense pèrdua. El criteri: una descomposició binària de R en R1 i R2 és sense pèrdua si els atributs comuns determinen funcionalment tots els d'almenys una de les dues.

{assessor} és clau de R1, per tant la descomposició és sense pèrdua. Comprovació amb dades: el JOIN de R1 i R2 per assessor retorna exactament les 6 files originals, ni una més.

(c) No conserva les dependències. La projecció de F sobre les dues relacions dóna:

La dependència m1: {client, especialitat} → assessor s'ha perdut. No es dedueix de F₁ ∪ F₂: la clausura de {client, especialitat} sota F₁ ∪ F₂ és {client, especialitat} i no conté assessor.

I el que és pitjor a la pràctica: no es pot comprovar mirant una sola taula. R1 no coneix els clients; R2 no coneix les especialitats. Verificar-la exigeix unir les dues.

(d) Què s'hi cola ara. Inserim a R2 una fila perfectament legal per a l'esquema descompost:

Cap restricció no es queixa: R2 només exigeix que la parella no es repeteixi, i no es repeteix. Però en reconstruir:

Forn Solé té dos assessors fiscals. La regla de negoci «per a cada client i especialitat hi ha exactament un assessor» està trencada, i l'esquema original en 3FN ho impedia amb la seva clau primària. La FNBC ha eliminat la redundància a costa de perdre una garantia.

(e) La decisió. Hi ha tres sortides defensables i una que no ho és:

L'opció 3 mereix veure's escrita, perquè és el patró que resol la majoria d'aquests casos a la pràctica:

Resultat esperat

Explicació. Aquest és l'exercici que separa qui ha memoritzat les formes normals de qui les entén. El teorema de fons és contundent:

Tota relació admet una descomposició a 3FN sense pèrdua i amb conservació de dependències. No tota relació admet una descomposició a FNBC amb conservació de dependències.

La FNBC és més estricta i de vegades és massa estricta: elimina la redundància al preu que una regla de negoci deixi de ser verificable dins d'una sola taula. Quan això passa, la normalització deixa de ser una decisió tècnica i passa a ser una decisió de negoci: què fa més mal, la redundància o el risc de dades incoherents?

A la pràctica, la resposta gairebé sempre és que fa més mal el risc d'incoherència, perquè la redundància es pot vigilar amb una consulta d'auditoria i la incoherència, un cop ha passat, és molt cara de netejar. Per això l'opció 3 —replicar l'atribut i blindar-lo amb una clau forana composta, el mateix truc que tancava la jerarquia del taller mecànic a 07-02— és el compromís que guanya més vegades: es paga una columna repetida, però el motor garanteix que mai no es descompensi.

L'opció 1 (quedar-se en 3FN) és perfectament respectable quan el volum és petit i la taula es consulta poc. El que no és mai respectable és l'opció 4: descompondre, perdre la dependència i no dir-ho a ningú.

Dificultat: Avançat

Enunciat. Per preparar el material dels esdeveniments, BiblioRed portava aquesta taula:

ESDEVENIMENT_RECURSOS(esdeveniment_id, ponent, material)

on s'anoten els ponents que participen en un esdeveniment i els materials que s'hi exposen. Els ponents i els materials són independents entre si: quins materials s'exposen no depèn de quin ponent parli, i a l'inrevés.

Dades de l'esdeveniment 104 («Presentació: La capsa dels desitjos»), amb dos ponents i dos materials:

Es demana:

• (a) Determinar la clau candidata i comprovar si està en FNBC.
• (b) Escriure les dependències multivaluades.
• (c) Descompondre a 4FN i mostrar les taules amb dades.
• (d) Quantificar l'estalvi si l'esdeveniment tingués 3 ponents i 4 materials.

Solució

(a) La clau candidata és {esdeveniment_id, ponent, material}: la relació sencera. No hi ha cap dependència funcional no trivial —ni esdeveniment_id → ponent (n'hi ha dos), ni ponent → material (cada ponent apareix amb els dos materials)—, així que l'única manera d'identificar una fila és amb els tres atributs.

Com que tots els atributs són primers i no hi ha dependències funcionals no trivials, la taula està en FNBC. I tanmateix la redundància salta a la vista.

(b) Dependències multivaluades. Una dependència multivaluada X ↠ Y diu que el conjunt de valors de Y associat a un valor de X és independent de la resta d'atributs. Aquí:

Totes dues són no trivials (ponent no conté esdeveniment_id, i esdeveniment_id ∪ ponent ≠ R) i esdeveniment_id no és superclau. Per tant la taula no està en 4FN.

El senyal empíric d'una dependència multivaluada: la taula conté el producte cartesià de dos conjunts independents. 2 ponents × 2 materials = 4 files, i les 4 hi han de ser. Si faltés la fila (104, Rosa Calduch, Ciència Vallmar 42), la taula estaria afirmant una cosa falsa: que la Rosa Calduch té alguna cosa a veure amb quin material s'exposa.

(c) Descomposició a 4FN.

esdeveniment_ponents (2 files)

esdeveniment_materials (2 files)

La descomposició és sense pèrdua pel teorema de Fagin: si X ↠ Y es compleix a R, aleshores R es descompon sense pèrdua en R[X ∪ Y] i R[X ∪ (R − Y)]. El JOIN per esdeveniment_id reconstrueix exactament les 4 files originals.

(d) L'estalvi. Amb 3 ponents i 4 materials:

I amb 5 ponents i 20 materials: 100 files enfront de 25. El creixement és multiplicatiu a l'esquema sense normalitzar i additiu al normalitzat.

Resultat esperat

Explicació. La 4FN és la primera forma normal que no es pot diagnosticar mirant dependències funcionals, i per això s'escapa tan sovint. La taula està en FNBC —l'anàlisi funcional diu que és perfecta— i tot i així té una anomalia brutal d'actualització: afegir un tercer ponent a l'esdeveniment 104 obliga a inserir dues files, una per cada material, i oblidar-ne una deixa la taula en un estat que afirma una relació que no existeix.

El criteri pràctic per detectar una dependència multivaluada, més útil que la definició formal:

Si en afegir un valor nou de Y has d'inserir una fila per cada valor existent de Z, i Y i Z no tenen res a veure entre si, hi ha una dependència multivaluada.

Quan NO hi ha dependència multivaluada, que és l'error simètric. Si a BiblioRed cada ponent presentés el seu propi material —la Delia Marchetti la seva novel·la i la Rosa Calduch la revista—, aleshores ponent i material sí que estarien relacionats, no hi hauria producte cartesià, la taula estaria en 4FN i descompondre-la perdria informació: en reconstruir amb un JOIN apareixerien les parelles falses. La independència entre els dos conjunts és una regla de negoci, no una cosa que es dedueixi de les dades, i és el primer que cal confirmar abans de descompondre.

Nota: a l'esquema real de BiblioRed aquesta descomposició ja està feta —participacions i esdeveniments_materials són exactament les dues taules de l'apartat (c)—. L'exercici mostra d'on ve aquella decisió de disseny.

Dificultat: Avançat

Enunciat. El catàleg públic de BiblioRed mostra, per a cada material, quants exemplars disponibles hi ha a cada sucursal. És la consulta més executada del sistema: unes 400 vegades per minut en hora punta. Amb 40.000 exemplars, la consulta actual

triga 380 ms fins i tot amb índex, perquè el catàleg l'executa una vegada per cada material de la pàgina de resultats (fins a 20 materials) i això són gairebé 8 segons per pàgina.

En canvi, els canvis d'estat d'un exemplar són uns 3.000 al dia: préstecs, devolucions, retirades.

Es demana:

• (a) Proposar què desnormalitzar i per què aquella opció i no una altra.
• (b) Escriure l'SQL de l'estructura desnormalitzada.
• (c) Explicar com es manté la coherència.
• (d) Enumerar què s'accepta a canvi.

Solució

(a) Què desnormalitzar: una taula resum.

Comparem les tres opcions del catàleg de 05-04:

(b) L'estructura.

La consulta del catàleg passa a ser:

Un accés per clau primària: menys d'1 ms, enfront dels 380 ms del GROUP BY.

(c) Com es manté la coherència: un disparador sobre exemplars.

I una consulta de reconciliació nocturna que compara el resum amb la font de veritat i reporta qualsevol descompensació:

(d) Què s'accepta a canvi.

Resultat esperat

Explicació. La desnormalització és una decisió d'enginyeria, no un fracàs del disseny, i per això ha d'estar justificada amb números. Els tres números que justifiquen aquesta són: 400 lectures per minut, 3.000 escriptures al dia i 380 ms per consulta. Sense aquests tres, la proposta seria una opinió.

La regla que governa la decisió: desnormalitza quan la proporció lectura/escriptura és molt alta i el cost de la consulta és estructural (un GROUP BY sobre moltes files no s'arregla amb un índex millor). Aquí la proporció és d'unes 200 lectures per cada escriptura.

Tres detalls del disseny que convé no perdre:

• ON CONFLICT ... DO UPDATE (l'«upsert» de PostgreSQL) evita haver de comprovar si la fila existeix. És imprescindible: el primer exemplar disponible d'un material en una sucursal no té fila prèvia.
• El disparador es declara AFTER ... OF estat, sucursal_id, material_id. Restringir les columnes evita que un UPDATE de portada_url dispari feina inútil.
• CHECK (n_disponibles >= 0) és un canari. Si el comptador intenta baixar de zero, hi ha un error a la lògica i és millor que la transacció falli sorollosament que deixar el resum mentint en silenci.

I l'advertiment final: la taula disponibilitat no s'ha de fer servir mai per prendre decisions transaccionals. Per saber si es pot prestar un exemplar concret cal anar a exemplars amb un bloqueig, no al resum. El resum és per pintar el catàleg. Confondre una dada derivada amb la font de veritat és la manera més habitual que una desnormalització acabi prestant el mateix exemplar dues vegades.

1. Deduir les dependències funcionals de les dades. Les dades només poden refutar una dependència, mai demostrar-la. Que en sis files cada assessor tingui una sola especialitat no prova assessor → especialitat; ho prova la regla de negoci. Pregunta sempre «pot canviar això?» abans d'escriure una fletxa.

2. Aturar-se a la primera clau candidata. És l'error de l'exercici 2. Abans d'avaluar formes normals cal tenir totes les claus candidates, perquè només una d'elles ja fa que la relació incompleixi una forma normal.

3. Confondre superclau amb clau candidata. Una superclau determina tots els atributs; una clau candidata és una superclau mínima. {esdeveniment, sala} és superclau i no és clau candidata.

4. Calcular la clausura amb una sola passada. Cal iterar fins que el conjunt deixi de créixer. Moltes dependències només es disparen després que una altra hagi afegit el seu atribut.

5. Saltar-se la 2FN quan la clau és simple. No és un error, és una drecera legítima: amb clau candidata única i d'un atribut, la 2FN es compleix sempre. El que sí que és un error és donar per fet el mateix per a la 3FN.

6. Descompondre tota dependència que aparegui. proveidor_cif → proveidor_id no exigeix una taula nova: és una clau alternativa i es resol amb UNIQUE. Només es descompon el que viola la forma normal que persegueixes.

7. Treure de la taula de detall un atribut que depèn de la clau sencera. El preu_unit de l'exercici 7 depèn de {comanda_id, isbn}, no de l'ISBN. Treure'l al catàleg destrueix informació. Comprova sempre si el valor canvia entre files amb el mateix determinant candidat.

8. Descompondre a FNBC sense comprovar la conservació de dependències. És l'exercici 8. Abans d'aplicar l'algorisme, projecta F sobre les relacions resultants i comprova què has perdut.

9. Confondre dependència funcional amb multivaluada. Si els dos conjunts són independents (producte cartesià obligat), és multivaluada i cal descompondre. Si estan relacionats, descompondre inventa files en reconstruir.

10. Desnormalitzar sense números i sense reconciliació. Una desnormalització sense mesura prèvia és una superstició, i una sense procés de reconciliació és una bomba de rellotgeria.

Consell de mètode. Treballa sempre en aquest ordre i no el canviïs: dependències → clausures → totes les claus candidates → primers i no primers → formes normals en ordre. Saltar-se un pas fa que el diagnòstic surti malament en la meitat dels casos, i el pitjor és que surt malament de manera plausible.

Consell de comprovació. Després de cada descomposició, compta les files de les taules noves i reconstrueix l'original amb un JOIN. Si en reconstruir surten més files que les originals, la descomposició té pèrdua (has generat tuples espúries). Si en surten menys, has perdut dades. Només si surten exactament les mateixes està bé.

Sense pistes. Escriu l'anàlisi completa a cadascun.

Reprèn OCUPACIO(sala, dia, hora, esdeveniment, aforament, sucursal) de l'exercici 2, amb:

Porta-la a FNBC, mostrant la descomposició pas a pas, comprovant que és sense pèrdua i dient si conserva les dependències. Escriu l'esquema final amb les seves claus i les seves restriccions.

Una empresa de repartiment de Vallmar fa servir aquesta taula:

Regles: cada ruta la fa un conductor amb un vehicle en una data; dins d'una ruta les parades van numerades; cada client té una única adreça; cada vehicle té una càrrega màxima. Determina claus candidates, forma normal exacta, dependència culpable i una anomalia de cada tipus anomenant fila i dada.

La direcció de BiblioRed vol un panell que s'obre 30 vegades al dia i mostra, per a cadascun dels últims 24 mesos: préstecs totals, socis diferents que van demanar alguna cosa, multes emeses i import recaptat. La consulta actual recorre prestecs, multes i pagaments sencers —uns 900.000 registres— i triga 4 segons. Les dades de mesos tancats no canvien mai. Proposa la desnormalització, justifica-la, digues com es manté i què s'accepta a canvi. Compara explícitament amb la solució de l'exercici 10 i explica per què aquí l'elecció és diferent.

Solució A

Estat inicial. Claus candidates {esdeveniment} i {sala, dia, hora}; primers: esdeveniment, sala, dia, hora; no primers: aforament, sucursal. Està només en 1FN, perquè g1 és parcial respecte de {sala, dia, hora}.

Pas a 2FN/3FN. El determinant culpable és sala. Se'n treu:

SALES està en FNBC: la seva única dependència és sala → aforament, sucursal i sala és la clau.

AGENDA conserva g2 i g3. Les seves dues claus candidates continuen essent {sala, dia, hora} i {esdeveniment}; tots els seus atributs són primers, així que està en 3FN. I en FNBC? Les dues dependències que queden són g2 i g3, i en totes dues el determinant és una clau candidata, per tant superclau. AGENDA està en FNBC.

Sense pèrdua. SALES ∩ AGENDA = {sala}, i sala → aforament, sucursal fa de {sala} la clau de SALES. Compleix el criteri.

Conservació de dependències. g1 viu sencera a SALES; g2 i g3 viuen senceres a AGENDA. F₁ ∪ F₂ = F: es conserven totes. Aquest cas és l'habitual i contrasta amb l'exercici 8: aquí la FNBC surt de franc.

Esquema final:

L'important de l'esquema final: les dues claus candidates queden declarades, una com a PRIMARY KEY i l'altra com a UNIQUE. Declarar només una de les dues deixaria g2 sense garantia i permetria dos esdeveniments a la mateixa sala a la mateixa hora.

Solució B

Claus candidates. Una fila és una parada dins d'una ruta: {ruta_id, parada_ordre}. Comprovació: {ruta_id, parada_ordre}⁺ arriba a client, i d'allà a adreca; ruta_id dóna data, conductor_id, vehicle_matricula; i d'allà conductor_nom i vehicle_carrega_kg. Clausura completa. Res més curt no ho aconsegueix.

Dependències:

Forma normal: 1FN, no 2FN. La dependència culpable és d2: ruta_id és subconjunt propi de la clau i data, conductor_id i vehicle_matricula són no primers. d3, d4 i d5 són a més transitives i es resoldrien al pas a 3FN.

Anomalies amb fila i dada exactes:

• Modificació. El vehicle 8820 BNM rep una revisió i la seva càrrega màxima passa a 1.300 kg. El valor 1400 apareix a dues files: la de R-101 i la de R-102. Si només s'actualitza la de R-101, la taula afirma que el mateix vehicle té dues càrregues màximes diferents segons el dia.
• Inserció. Es contracta un conductor nou, C9 / Berta Quintana. No es pot registrar fins que se li assigni una ruta amb almenys una parada. El mateix amb un client nou: la seva adreça no existeix fins que tingui un repartiment.
• Esborrament. Es cancel·la la parada d'Òptica Vilamar a la ruta R-101 i s'esborra aquella fila. Com que és l'única fila de R-101, desapareix la ruta sencera: que va existir el 6 de juliol, que la va fer el conductor C7 (Aitor Lemus) i que va usar el vehicle 8820 BNM. I amb ella es perd també que Òptica Vilamar és a la Plaça Major 3, perquè és la seva única aparició... llevat que en quedés una altra, cosa que aquí no passa.

Descomposició a 3FN: clients(client, adreca), conductors(conductor_id, conductor_nom), vehicles(vehicle_matricula, vehicle_carrega_kg), rutes(ruta_id, data, conductor_id, vehicle_matricula), parades(ruta_id, parada_ordre, client, paquets).

Solució C

Proposta: una taula resum de mesos tancats, poblada per un procés mensual.

Com es manté. Un procés programat el dia 1 de cada mes insereix la fila del mes acabat de tancar amb un únic recorregut de les taules d'origen. El mes en curs no es guarda: es calcula al vol, perquè és l'únic que canvia. El panell uneix les 23 files del resum amb el càlcul en viu del mes actual mitjançant un UNION ALL.

Per què l'elecció és diferent de la de l'exercici 10. La diferència decisiva és la volatilitat de la dada:

Un disparador sobre prestecs per mantenir el resum mensual seria desproporcionat: afegiria feina a cada préstec per servir 30 lectures al dia. I una vista materialitzada, que a l'exercici 10 era mala idea perquè ho recalcula tot, aquí és perfectament raonable: es refresca una vegada al mes i el recàlcul complet no molesta ningú.

Què s'accepta a canvi: el mes en curs se segueix calculant al vol (uns 200 ms, acceptable); cal vigilar que cap correcció retroactiva no toqui un mes ja tancat, i si n'hi ha, recalcular aquella fila explícitament; i el panell necessita l'UNION ALL, que és una complicació de la consulta que abans no existia.

Has recorregut la normalització de punta a punta amb deu exercicis: has escrit conjunts de dependències funcionals a partir de regles de negoci, calculat clausures pas a pas, trobat totes les claus candidates de relacions amb claus solapades, classificat atributs en primers i no primers, i vist que una mateixa dependència es diu parcial o transitiva segons la clau que prenguis de referència.

Al bloc de diagnòstic has posat nom i cognoms a les anomalies: no «hi ha redundància», sinó «el correu de la Rosa Calduch és a quatre files i n'hi ha prou que una es quedi sense actualitzar». Aquella precisió és el que converteix una anàlisi en un argument amb què convèncer algú que cal refer una taula.

I al bloc avançat has fet la feina completa: la migració de 1FN a 3FN amb el seu SQL de descomposició i la seva comprovació de reconstrucció; el cas incòmode en què arribar a FNBC costa una dependència i cal decidir entre redundància i garantia —amb el patró de la clau forana composta com a millor compromís—; la quarta forma normal i les dependències multivaluades, que cap quantitat d'anàlisi funcional no detecta; i la desnormalització raonada amb números, amb el seu mecanisme de manteniment i la seva reconciliació obligatòria.

Si et quedes amb una sola idea, que sigui aquesta: normalitzar no és aplicar regles, és fer que cada fet visqui en un sol lloc. Les formes normals són la manera formal de comprovar si ho has aconseguit, i la desnormalització és la decisió conscient de trencar aquella regla en un punt concret, amb una mesura al davant i un procés de vigilància al darrere.

L'última lliçó del mòdul, 07-04, Exercicis de Consultes Avançades i Transaccions, és la més exigent de les quatre i la que més s'assembla al treball real de producció. Tornaràs a BiblioRed per escriure funcions de finestra —el material més prestat de cada sucursal, la comparació mes contra mes amb LAG, els acumulats—, una CTE recursiva, consultes sobre jsonb a les respostes de les enquestes d'esdeveniments, i un pivot manual. Després arriben les transaccions: la del préstec completa amb el seu control d'errors, SAVEPOINT en un procés per lots, i cinc exercicis de dues sessions psql en paral·lel on reproduiràs una actualització perduda, veuràs la diferència entre READ COMMITTED i REPEATABLE READ, provocaràs un interbloqueig a propòsit, implementaràs el bloqueig optimista de l'última plaça i consumiràs una cua amb SKIP LOCKED. I per tancar, quatre exercicis d'índexs i plans d'execució. Tingues els dos terminals a punt.