El mòdul 4 va acabar amb una promesa: sotmetre l'esquema de BiblioRed a un examen formal. Fins ara hem dissenyat per comprensió del domini, guiats per dos principis intuïtius —"una cosa, un lloc" i "un fet, una fila"— que funcionen sorprenentment bé però que no es poden demostrar. Quan dues persones discrepen sobre si una taula està ben dissenyada, la intuïció no arbitra. La normalització sí: és un cos de teoria, publicat per Edgar Codd entre 1970 i 1974 i ampliat després, que converteix aquests principis en definicions amb les quals es pot raonar i, arribat el cas, discutir amb arguments.

Aquesta lliçó és la preparació. Encara no defineix cap forma normal: construeix el vocabulari i les eines que calen per entendre-les. Concretament, respon a tres preguntes. Primera: quin mal fa exactament la redundància, més enllà d'"ocupa espai"? Segona: com s'escriu formalment una regla del tipus "l'ISBN determina el títol"? Tercera: com es calcula, amb un procediment mecànic i no a ull, quina és la clau d'una taula? Sense aquestes tres coses, les formes normals de la lliçó següent són fórmules memoritzades; amb elles, són conseqüències evidents.

Treballarem gairebé tota l'estona sobre un material que ja coneixem: el vell full de càlcul de préstecs de BiblioRed, el que vam diagnosticar a la lliçó 01-01 i que des d'aleshores hem fet servir com a exemple de tot allò que no s'ha de fer. Ha arribat el moment de desmuntar-lo formalment.

1. Què és normalitzar i què no ho és
2. Per què la redundància és el problema (i no l'espai en disc)
3. El full de préstecs com a relació única: prestecs_full
4. Les tres anomalies, demostrades una a una
5. Dependències funcionals: definició i notació
6. D'on surten les dependències: regles de negoci, no dades de mostra
7. Tipus de dependència: total, parcial i transitiva
8. Dependències trivials
9. El graf de dependències de prestecs_full
10. Els axiomes d'Armstrong
11. Les regles derivades: unió, descomposició, pseudotransitivitat
12. La clausura d'un conjunt d'atributs (X⁺)
13. Trobar les claus candidates amb la clausura
14. Atributs primers i no primers
15. Què ve ara: les formes normals

Normalitzar és reorganitzar els atributs d'una base de dades en taules de manera que cada fet quedi emmagatzemat exactament una vegada, i fer-ho seguint un procediment que es pot justificar.

Aquesta definició té dues meitats i totes dues importen. La primera meitat ("cada fet una vegada") és l'objectiu. La segona ("un procediment justificable") és el que distingeix la normalització de la intuïció del mòdul 4. Quan acabem el mòdul, podràs dir d'una taula no només "això està malament" sinó "això viola la segona forma normal perquè exemplar_cod → isbn és una dependència parcial de la clau (exemplar_cod, data_prestec)", que és una frase que es pot verificar o refutar.

Val la pena ser explícits sobre el que no és normalitzar, perquè les tres confusions següents són molt freqüents:

No és "partir taules per partir-les". Hi ha qui creu que normalitzar significa tenir moltes taules petites i que com més, millor. És fals. Una descomposició només està justificada si elimina una dependència problemàtica concreta. Partir socis en socis_dades_basiques i socis_dades_contacte sense cap dependència que ho motivi no normalitza res: afegeix un JOIN a cada consulta i no elimina cap redundància. Si no pots anomenar la dependència que estàs eliminant, no estàs normalitzant.

No és una finalitat en si mateixa. L'objectiu del sistema de BiblioRed és prestar llibres, no exhibir un esquema en cinquena forma normal. La normalització és un mitjà perquè les dades no es contradiguin. Quan deixa de servir aquesta finalitat —quan el cost dels JOIN supera el benefici de la coherència— es fa el contrari expressament, i això té nom i mètode: és la lliçó 05-04.

No és neteja de dades. Aquest punt és subtil i convé fixar-lo des del principi. Al full de BiblioRed hi conviuen "Ken Follet" i "Ken Follett", i un ISBN al qual li falta un dígit. Normalitzar l'esquema no corregeix aquests errors: si fiques dades brutes en un esquema perfectament normalitzat, obtens dades brutes ben organitzades. El que fa la normalització és eliminar la possibilitat que l'error es torni a produir: quan el nom de l'autor està escrit una sola vegada a autors, no hi ha manera física que existeixin dues grafies diferents. La neteja de l'històric és una feina a part que es fa durant la migració, i la veurem a la lliçó 05-03.

El segon objectiu és tan important com el primer i s'oblida més. Una descomposició que elimina tota la redundància però perd informació és un desastre, no una fita.

És habitual justificar la normalització dient que "estalvia espai". És cert que l'estalvia, i el 1970, amb discos de megabytes caríssims, era un argument de pes. Avui no ho és: el disc val gairebé res i repetir el nom d'una sucursal quaranta mil vegades costa uns pocs megabytes que no preocupen ningú.

El veritable problema de la redundància és que crea la possibilitat física de la contradicció.

Pensa en què significa que una dada estigui escrita dues vegades. Significa que existeixen dos llocs al disc que afirmen alguna cosa sobre el món, i que el sistema no té cap garantia que afirmin el mateix. Mentre ningú no els toqui, coincideixen. En el moment en què una operació actualitza l'un i no l'altre —perquè el programa tenia un error, perquè la connexió es va tallar a mitges, perquè l'operari del taulell va corregir el que veia a la pantalla sense saber que hi havia més còpies— la base de dades passa a contenir dues veritats incompatibles. I no hi ha manera automàtica de saber quina és la bona.

Al full de BiblioRed això ja va passar, i per això és un exemple tan útil:

• La sucursal apareix com a Nord en tres files i com a nord en una. Un GROUP BY sucursal retorna dues sucursals on n'hi ha una.
• L'autor apareix com a Ken Follet en una fila i Ken Follett en una altra. Buscar els préstecs de Follett en retorna la meitat.
• El correu de Marta Alsina apareix com a [email protected] en dues files i [email protected] en una. Quin és el bo? Ningú no ho sap sense trucar-li per telèfon.

Cap d'aquests tres problemes no és un problema d'espai. Tots tres són el mateix problema: la dada és en diversos llocs, algú en va tocar un, i ara la base de dades menteix.

Hi ha una formulació que convé memoritzar: la redundància no causa la inconsistència, la fa possible; i tot allò que és possible, amb prou files i prou temps, acaba passant. Una base de dades amb quaranta mil préstecs i vuit anys d'història acumula una quantitat de contradiccions proporcional a la quantitat de redundància que li permetis.

Per treballar formalment necessitem que el full de càlcul sigui una taula amb noms de columna raonables. L'anomenarem prestecs_full i li donem aquesta estructura, que és la del fitxer original amb les columnes reanomenades i sense res afegit:

I aquestes són les dades, ja amb les grafies unificades per poder raonar sobre l'estructura sense que els errors tipogràfics ens distreguin (les recuperarem a 05-03, on cal netejar-les de debò):

Cinc files. Compta quantes vegades apareix la cadena Félix J. Palma: tres. Quantes vegades Vallmar: cinc. Quantes vegades el telèfon de la Marta: tres. Amb cinc files és una curiositat; amb els 84.000 préstecs que BiblioRed porta registrats des del 2018, és un problema estructural.

Un aclariment sobre la clau primària abans de continuar. Hi he posat (exemplar_cod, data_prestec) perquè la regla de negoci de BiblioRed diu que un exemplar físic no pot estar prestat dues vegades el mateix dia: només hi ha una còpia, i si se l'ha endut algú, no se la pot endur ningú més fins que torni. A la secció 13 comprovarem formalment que aquesta parella és efectivament una clau, en comptes de donar-ho per bo.

La lliçó 04-01 va anomenar les tres anomalies en una taula de tres línies i va prometre explicar-les aquí. Anem-hi, i aquesta vegada amb SQL que les provoca de debò.

Una anomalia és un comportament indesitjable que apareix en inserir, modificar o esborrar files d'una taula mal dissenyada. No és un error del programador ni una fallada del SGBD: és una conseqüència inevitable de l'estructura de la taula. Amb prestecs_full no les pots evitar per molta cura que hi posis, perquè són al disseny.

No es pot registrar un fet perquè en falta un altre que no hi té relació.

BiblioRed acaba d'incorporar al seu catàleg d'autors Ken Follett amb la nacionalitat correcta, i vol registrar també que existeix una sucursal nova, Est, al codi postal 08140. Provem-ho:

No es pot. Per desar una dada sobre un autor cal inventar-se un préstec: un exemplar, una data, un soci, un ISBN. La informació sobre autors no té on viure si no és penjada d'un préstec.

El mateix amb la sucursal nova:

L'única sortida seria inserir una fila amb valors inventats o NULL a tota la resta —una "fila fantasma"— i això enverina totes les consultes: els recomptes de préstecs surten malament, les mitjanes de dies surten malament, i algú acabarà preguntant per què hi ha un préstec sense soci.

Traducció del problema: la taula barreja fets sobre préstecs amb fets sobre autors i sucursals, i només té clau per al primer. Els fets de les altres entitats queden atrapats.

Canviar un fet obliga a modificar moltes files; si se n'escapa una, la base de dades es contradiu.

L'ajuntament de Vallmar rebateja la sucursal Nord com a "Vallmar Nord". En un esquema normalitzat això és un UPDATE d'una sola fila. Aquí:

Quatre files a la mostra; unes 31.000 al fitxer real. I aquest UPDATE va funcionar perquè totes les files deien exactament Nord. Al fitxer original una deia nord en minúscula, i per tant no va entrar al WHERE:

Aquí tens l'anomalia en la seva forma més pura: una sucursal que ara existeix amb dos noms diferents perquè una fila es va quedar fora de l'actualització. És exactament el defecte número 7 del diagnòstic de 01-01, i ara sabem que no va ser mala sort ni descuit de l'operari: és la conseqüència matemàtica de tenir el nom de la sucursal repetit en 31.000 files.

El mateix passa amb el telèfon de la Marta Alsina. Si canvia de número, cal tocar les tres files on apareix; si només se n'actualitzen dues, BiblioRed té dos telèfons per a la mateixa persona i cap manera de saber quin marcar.

Traducció del problema: el nom de la sucursal és un fet sobre la sucursal, no sobre el préstec, però està emmagatzemat una vegada per préstec.

Esborrar una fila destrueix informació que no hi tenia res a veure.

L'Ivan Pereda demana que s'esborri el seu historial de préstecs, i BiblioRed està obligada a atendre'l. El seu préstec d'"Els pilars de la Terra" és, diguem-ne, l'únic que queda d'aquest exemplar:

En esborrar aquesta fila hem perdut, sense voler:

• Que existeix un exemplar anomenat EJ-3090.
• Que l'ISBN 9788401337208 correspon a "Els pilars de la Terra".
• Que el seu autor és Ken Follett.
• Que Ken Follett és britànic.

Si aquella fila hagués estat l'última amb Follett de tota la taula, Ken Follett hauria deixat d'existir a la base de dades de BiblioRed. Una dada de catàleg, permanent, esborrada per una operació sobre un préstec. Cap auditoria no ho detectaria: la fila es va esborrar correctament, l'operació va tenir èxit, i tanmateix es va perdre informació.

Traducció del problema: l'existència de l'autor està condicionada a l'existència d'almenys un préstec seu, quan al món real són coses independents.

Totes tres tenen la mateixa causa: la taula desa fets sobre diverses entitats diferents (préstec, soci, llibre, autor, sucursal) en una sola fila, i només una d'aquestes entitats mana sobre la clau. Tota la resta hi és de prestat.

I aquí hi ha el gran salt conceptual d'aquesta lliçó: aquesta causa es pot escriure amb precisió. L'eina per fer-ho són les dependències funcionals.

Una dependència funcional és una regla que diu que, conegut el valor d'uns atributs, el valor d'uns altres queda determinat sense ambigüitat.

Definició. Siguin X i Y dos conjunts d'atributs d'una relació R. Diem que X determina funcionalment Y, i ho escrivim X → Y, si per a qualsevol parell de files de R que coincideixin en tots els atributs de X, necessàriament coincideixen també en tots els atributs de Y.

Anem amb el vocabulari, perquè cada símbol compta i aquí no donem per suposada cap base matemàtica:

• Atribut: una columna. isbn és un atribut.
• Conjunt d'atributs: un grup de columnes, que s'escriu entre claus: {exemplar_cod, data_prestec}. Quan el conjunt té un sol element, les claus se solen ometre: isbn en comptes de {isbn}.
• La fletxa →: es llegeix "determina". isbn → titol es llegeix "l'ISBN determina el títol". No és una assignació, ni una implicació lògica, ni una fletxa d'un diagrama: és un símbol específic d'aquesta teoria.
• El costat esquerre (X) s'anomena determinant. El dret (Y), determinat o dependent.
• La juxtaposició significa unió: XY és una forma abreujada d'escriure "el conjunt format per tots els atributs de X més tots els de Y". L'operació s'anomena unió i el seu símbol formal és ∪, de manera que XY i X ∪ Y són el mateix. Veuràs les dues notacions a la bibliografia.

isbn → titol diu: "si dues files tenen el mateix ISBN, tenen forçosament el mateix títol". O, dit a l'inrevés i de manera més útil: "no pot existir un ISBN amb dos títols diferents". Aquesta segona formulació —com una prohibició— sol ser la més fàcil de validar amb la persona que coneix el negoci.

Fixa't que la dependència no diu res en sentit contrari. isbn → titol no implica titol → isbn: dues edicions diferents d'"El mapa del temps" poden compartir títol i tenir ISBN diferents. Les dependències tenen direcció, i confondre-la és l'error número u de qui comença.

El costat esquerre pot tenir diversos atributs:

Es llegeix: "coneguts l'exemplar i la data en què es va prestar, la data de devolució queda determinada". I té sentit: aquesta parella identifica un préstec concret, i un préstec concret es va tornar un dia concret (o encara no, i aleshores és NULL, però és el mateix NULL per a les dues files que coincideixin).

Cap dels dos atributs per separat no bastaria. exemplar_cod → data_devolucio és falsa: l'exemplar EJ-3081 apareix en dues files amb dates de devolució diferents (2026-03-16 i NULL). Amb trobar dues files que la incompleixin, la dependència queda refutada.

També el costat dret en pot tenir diversos:

"El correu del soci determina el seu nom i el seu telèfon". Com veurem a la secció 11, una dependència així sempre es pot partir en diverses amb un sol atribut a la dreta, i a l'inrevés. És qüestió de comoditat d'escriptura.

Reunides totes, aquest és el conjunt de dependències funcionals que regeixen la nostra taula. Anomenarem F aquest conjunt (per functional dependencies), i el farem servir durant tota la lliçó:

Llegeix-les una a una en veu alta, en la seva versió de prohibició:

Aquest apartat és curt i és, probablement, el més important de la lliçó.

Les dependències funcionals es descobreixen preguntant a qui coneix el negoci, no mirant les dades.

La raó és de lògica elemental. Una dependència X → Y afirma alguna cosa sobre totes les files que puguin existir mai, incloses les que encara no s'han inserit. Les dades de mostra només poden fer dues coses:

• Refutar una dependència: si trobes dues files amb el mateix X i diferent Y, la dependència és falsa. Això sí que és concloent.
• No refutar-la: si no trobes contraexemples, la dependència podria ser certa. Això no demostra res.

Un exemple amb la nostra taula. Mira les cinc files i veuràs que es compleix soci_telefon → soci_email: cada telèfon apareix sempre amb el mateix correu. És una dependència funcional? Preguntem-ho a BiblioRed: "pot haver-hi dos socis amb el mateix telèfon?". Resposta: "És clar, un matrimoni que dóna el fix de casa, o dos germans adolescents que donen el número de la seva mare". No és una dependència. Era una coincidència de la mostra.

Un altre exemple, en la direcció contrària. A les cinc files, sucursal_ciutat és sempre Vallmar, així que aparentment titol → sucursal_ciutat es compleix (tot ho determina, perquè només hi ha un valor). És òbviament absurd. Amb una mostra prou petita, "es compleixen" dependències esbojarrades.

Si et toca normalitzar una base de dades existent i no hi ha ningú a qui preguntar, es pot fer servir l'anàlisi de dades com a punt de partida per formular hipòtesis, i hi ha eines que busquen candidates automàticament. Però cada candidata cal validar-la contra el negoci abans de convertir-la en una decisió de disseny. Una dependència adoptada per error fa que descomponguis on no havies de fer-ho i que la base de dades rebutgi dades legítimes el dia que apareguin.

Regla pràctica: converteix cada dependència en una frase que comenci per "no pot passar que…" i porta-la a la reunió. isbn → titol es converteix en "no pot passar que el mateix ISBN tingui dos títols diferents". Si la persona del negoci dubta o diu "bé, tret de quan…", no tens una dependència: has de continuar investigant.

Amb la definició ja podem classificar. Aquests tres tipus són exactament els que necessitarem a la lliçó següent per definir la segona i la tercera forma normal, així que convé tenir-los clars.

Abans cal un terme: una superclau és un conjunt d'atributs que determina tots els altres atributs de la relació. Una clau candidata és una superclau mínima: si li treus qualsevol atribut, deixa de determinar-ho tot. Hi tornarem a la secció 13 amb un procediment per calcular-les; de moment en tenim prou amb saber que a prestecs_full la clau candidata és {exemplar_cod, data_prestec}.

X → Y és total si Y depèn de X sencer: en treure qualsevol atribut de X, la dependència deixa de complir-se.

Exemple a BiblioRed:

És total perquè cap de les dues meitats no basta:

• exemplar_cod → data_devolucio és falsa: EJ-3081 té dues dates de devolució diferents a la mostra.
• data_prestec → data_devolucio és falsa: dos préstecs del mateix dia es tornen en dies diferents.

Calen els dos atributs, i per això la dependència és total. Les dependències totals de la clau són les bones: són exactament les que volem que sobrevisquin a la normalització.

X → Y és parcial si X és una clau composta (de dos o més atributs) i Y ja queda determinat per una part de X.

Exemple a BiblioRed:

isbn és un atribut de la taula que depèn de la clau {exemplar_cod, data_prestec} —com tota la resta—, però li sobra la meitat de la clau: exemplar_cod tot sol ja el determina. El codi d'un exemplar diu quina obra és, independentment de quan es prestés.

Quin mal fa: l'ISBN, el títol i l'autor es repeteixen a cada préstec d'aquest exemplar. Són un fet sobre l'exemplar, no sobre el préstec, i estan emmagatzemats una vegada per préstec. És la font directa de les anomalies de la secció 4.

El mateix val per a exemplar_cod → sucursal_nom: la sucursal on viu la còpia no depèn de quan es va prestar.

Fixa't en un detall: una taula amb clau d'un sol atribut no pot tenir dependències parcials, perquè no hi ha "parts" de la clau. És una dada útil quan arribi la segona forma normal.

X → Z és transitiva si existeix un conjunt intermedi Y tal que X → Y i Y → Z, sent Y no una superclau i Z un atribut que no forma part de cap clau.

En cristià: l'atribut no depèn de la clau directament, sinó "a través de" un altre atribut que tampoc no és clau.

Exemple a BiblioRed:

El nom del soci depèn de la clau del préstec, sí, però només perquè la clau determina el correu i el correu determina el nom. soci_email no és clau de la taula; és un atribut qualsevol que resulta que en determina d'altres. La dependència real és soci_email → soci_nom, i el préstec no hi pinta res.

Un altre exemple, en cadena de tres salts:

Per saber la nacionalitat de l'autor d'un préstec cal passar per l'exemplar, l'ISBN i l'autor. Quatre esglaons, cap d'ells clau tret del primer, i el resultat repetit a cada fila.

I el més net de tots, que reapareixerà a la lliçó següent:

La ciutat no és un fet sobre la sucursal: és un fet sobre el codi postal. Que la sucursal Nord sigui a Vallmar és una conseqüència que sigui al 08110 i que el 08110 sigui de Vallmar.

Quin mal fa: exactament el mateix que la parcial. Un fet que pertany a una altra entitat es repeteix a totes les files d'aquesta.

Una dependència és trivial quan el costat dret ja està contingut a l'esquerre:

S'anomenen trivials perquè es compleixen sempre, en qualsevol taula, sense que ningú no ho hagi de decidir: si dues files coincideixen en {exemplar_cod, data_prestec}, evidentment coincideixen en exemplar_cod, que és una d'aquestes dues columnes. No aporten cap informació sobre el disseny.

El símbol que es fa servir per expressar-ho és ⊆, que es llegeix "està contingut en" o "és subconjunt de". Y ⊆ X significa que tots els elements de Y són també a X. Amb aquesta notació:

X → Y és trivial si Y ⊆ X. En cas contrari és no trivial. Si a més X i Y no comparteixen cap atribut, es diu que és completament no trivial.

Per a què serveixen, doncs? Per a dues coses. Primer, perquè són la base del primer axioma d'Armstrong (secció 10) i fan que la teoria sigui completa i sense casos especials. Segon, perquè les definicions formals de les formes normals exclouen explícitament les trivials, i si no sabessis què són, aquestes definicions et semblarien incomprensibles. Quan a 05-02 llegeixis "per a tota dependència no trivial X → Y…", ja saps què s'està descartant i per què.

Les dependències es veuen molt millor dibuixades. Cada fletxa del diagrama és una dependència funcional del conjunt F:

El graf explica la història sencera d'un cop d'ull, i val la pena mirar-lo a poc a poc:

• Les dues fletxes gruixudes surten de la clau completa. Són les dependències sanes: soci_email i data_devolucio són fets genuïns sobre el préstec.
• Les fletxes que surten d'exemplar_cod tot sol (f2) són les dependències parcials. Mitja clau determina coses: allà hi ha una taula amagada.
• Les cadenes llargues (isbn → autor → autor_nac, sucursal_nom → sucursal_cp → sucursal_ciutat, soci_email → soci_nom) són les dependències transitives. Cada baula intermèdia que no és clau delata una altra taula amagada.
• Cada node del qual surt almenys una fletxa i que no és la clau —exemplar_cod, isbn, autor, soci_email, sucursal_nom, sucursal_cp— és una entitat disfressada de columna. Compta'n quants n'hi ha: sis. A la lliçó 05-03 aquest compte es convertirà en sis taules.

Aquest és el moment en què la teoria i la intuïció del mòdul 4 es troben. Quan allà vam dir "una fila de prestecs ha de parlar només del préstec", el que estàvem dient sense saber-ho era: "al graf de dependències, totes les fletxes han de sortir de la clau completa".

Tenim un conjunt F de set dependències que ens ha donat el negoci. Però d'elles se'n dedueixen d'altres que ningú no ha escrit. Per exemple, d'isbn → autor i autor → autor_nac es dedueix òbviament isbn → autor_nac, encara que no sigui a la llista.

El 1974, William Armstrong va publicar tres regles que permeten deduir totes les dependències que se segueixen d'un conjunt donat, i només aquestes. S'anomenen axiomes d'Armstrong. Un axioma és una regla que s'accepta com a punt de partida i a partir de la qual es demostra la resta; que aquests tres siguin suficients per deduir-ho tot és un teorema demostrat, no una opinió.

El conjunt de totes les dependències deduïbles de F s'anomena la clausura de F i s'escriu F⁺ (amb un signe + en superíndex, que en aquesta teoria significa sempre "tot allò que es dedueix d'això").

Si Y ⊆ X, aleshores X → Y.

En llenguatge planer: un conjunt de columnes determina qualsevol subconjunt de si mateix.

És la formalització de les dependències trivials de la secció 8. Sembla una ximpleria, i en certa manera ho és, però sense ella la teoria no tanca.

Exemple: {isbn, titol} → titol. Si dues files coincideixen en ISBN i títol, coincideixen en títol.

Si X → Y, aleshores XZ → YZ per a qualsevol conjunt Z.

En llenguatge planer: si unes columnes en determinen unes altres, afegir les mateixes columnes extra als dos costats no trenca res. Saber més mai no pot determinar menys.

Exemple: sabem que isbn → titol. Per augment amb Z = {data_prestec}:

Que es llegeix: "coneguts l'ISBN i la data del préstec, queden determinats el títol i la data del préstec". És cert, encara que poc útil per si sol. El seu valor és a combinar-lo amb els altres axiomes.

Si X → Y i Y → Z, aleshores X → Z.

En llenguatge planer: les dependències s'encadenen.

Aquest és l'axioma que fa feina de debò. Exemple a BiblioRed, amb dues aplicacions seguides:

Hem demostrat que el codi de l'exemplar determina la nacionalitat de l'autor. Ningú no va escriure aquesta regla; es dedueix. I és exactament per això que la nacionalitat de Ken Follett apareix repetida a cada préstec de cadascun dels seus exemplars.

Dels tres axiomes se'n dedueixen altres regles que no afegeixen poder —tot allò que es demostra amb elles es podria demostrar amb els tres axiomes— però que escurcen moltíssim la feina a mà.

Si X → Y i X → Z, aleshores X → YZ.

Planer: si el mateix determinant determina dues coses per separat, les determina juntes.

Exemple: de soci_email → soci_nom i soci_email → soci_telefon s'obté soci_email → {soci_nom, soci_telefon}. És just el que vam escriure com a f5.

Si X → YZ, aleshores X → Y i X → Z.

Planer: és la regla anterior a l'inrevés. Una dependència amb diverses columnes a la dreta es pot partir en diverses amb una de sola.

Exemple: de f2, exemplar_cod → {isbn, sucursal_nom}, s'obtenen exemplar_cod → isbn i exemplar_cod → sucursal_nom.

Unió i descomposició juntes diuen alguna cosa pràctica: el costat dret d'una dependència es pot agrupar o desagrupar lliurement. Per això, quan cal treballar a mà, se sol començar descomponent totes les dependències perquè tinguin un sol atribut a la dreta. El nostre conjunt F quedaria així:

Compte: el costat esquerre no es pot partir així. De {exemplar_cod, data_prestec} → soci_email no se segueix exemplar_cod → soci_email. Aquest és un error clàssic i produeix descomposicions desastroses.

Si X → Y i YW → Z, aleshores XW → Z.

Planer: una transitivitat en què el segon pas necessita ajuda extra. Si X em dóna Y, i amb Y més W arribo a Z, aleshores amb X més W també arribo a Z.

Exemple: suposem que BiblioRed calcula el recàrrec per retard amb una tarifa que depèn de la sucursal i del nombre de dies: {sucursal_nom, dies_retard} → recarrec. Com que exemplar_cod → sucursal_nom, per pseudotransitivitat:

Coneguts l'exemplar i els dies de retard, el recàrrec queda determinat: l'exemplar aporta la sucursal.

Aplicar els axiomes a mà per respondre "es dedueix X → Y de F?" és lent i propens a errors. Existeix un algorisme mecànic que ho resol, i és l'eina més útil de tota la teoria de normalització.

Definició. La clausura d'un conjunt d'atributs X respecte d'un conjunt de dependències F, escrita X⁺, és el conjunt de tots els atributs que queden determinats per X fent servir les dependències de F.

Dit d'una altra manera: X⁺ respon a la pregunta "si conec els valors de X, què més puc esbrinar?".

La idea és la d'una bola de neu: parteixes del que saps, apliques totes les regles que puguis, i amb allò nou que has esbrinat ho tornes a intentar, fins que una passada completa no afegeixi res.

Només cal vigilar una cosa, i és el mateix parany que abans: per disparar una dependència A → B calen tots els atributs d'A a RESULTAT, no n'hi ha prou amb algun.

Pregunta: conegut només el codi de l'exemplar, què sé d'un préstec?

Vuit atributs de tretze. Falten data_prestec, data_devolucio, soci_email, soci_nom i soci_telefon. Conclusió formal: exemplar_cod no és una superclau de prestecs_full, perquè la seva clausura no conté tots els atributs.

I una lectura important: aquests vuit atributs que sí que determina són precisament els que estaran repetits a cada préstec del mateix exemplar. La clausura d'un atribut que no és clau mesura la redundància que aquest atribut genera.

Tres atributs. Tampoc no és superclau. I un cop més la clausura dibuixa una taula que demana existir: socis(email, nom, telefon).

Conté tots els atributs. Per definició, {exemplar_cod, data_prestec} és una superclau.

Ja tenim tot el que cal per resoldre el problema pràctic central: donada una relació i les seves dependències, quines són les seves claus?

El procediment sistemàtic es recolza en una observació molt útil que estalvia la major part de la feina. Classifica cada atribut segons on apareix a F:

La raó de la primera regla és intuïtiva: si un atribut no apareix mai a la dreta, cap dependència no el pot produir, així que l'única manera de conèixer-lo és tenir-lo d'entrada. I la raó de la segona: si apareix només a la dreta, sempre pot deduir-se d'altres, així que mai no cal en un conjunt mínim.

Pas 1. Classificar els tretze atributs.

Pas 2. El nucli obligatori. Els atributs "només esquerra" són a tota clau candidata: {exemplar_cod, data_prestec}.

Pas 3. Basta amb el nucli? Calculem la seva clausura, que ja vam fer a l'exemple 3 de la secció anterior:

Sí que basta. És superclau.

Pas 4. És mínima? Cal comprovar que cap subconjunt propi no ho sigui. Els subconjunts propis d'un conjunt de dos elements són tres: {exemplar_cod}, {data_prestec} i el conjunt buit.

• {exemplar_cod}⁺ = 8 atributs. No és superclau (calculat a 12.1).
• {data_prestec}⁺ = {data_prestec}. Cap dependència no té data_prestec tota sola a l'esquerra, així que la clausura no creix. No és superclau.
• El conjunt buit, òbviament, tampoc.

Pas 5. Conclusió. {exemplar_cod, data_prestec} és superclau i és mínima, per tant és una clau candidata. I com que tot atribut "només esquerra" ha de ser a tota clau candidata, i aquests dos ja basten tots sols, és l'única.

Queda demostrat allò que a la secció 3 vam donar per bo. Aquesta és la diferència entre dissenyar per intuïció i dissenyar amb instrumental: ara no ens ho creiem, ho sabem.

Perquè vegis que la unicitat no està garantida, prenguem la taula socis de l'esquema real de BiblioRed, amb aquestes dependències:

La segona existeix perquè email és UNIQUE (ho vam declarar així a 02-02). Calculem:

• {soci_id}⁺ = tots els atributs → superclau, i mínima (és un sol atribut).
• {email}⁺ = tots els atributs → superclau, i mínima.

Dues claus candidates: {soci_id} i {email}. Una es tria com a primària —soci_id, la subrogada, per les raons que vam discutir a 04-01— i l'altra queda com a clau alternativa, protegida amb UNIQUE. Això no és un defecte de disseny; és el normal quan una entitat té alhora clau natural i clau subrogada.

Última definició de la lliçó, i la més curta. La necessitarem literalment a la primera frase de la segona i la tercera forma normal.

Un atribut és primer (o atribut clau) si forma part d'alguna clau candidata de la relació. Si no forma part de cap, és no primer (o atribut no clau).

Compte amb l'"alguna": si una relació té dues claus candidates, n'hi ha prou de ser en una d'elles per ser primer.

A prestecs_full, amb la seva única clau candidata {exemplar_cod, data_prestec}:

Onze atributs no primers, cadascun penjant de la clau per dependències parcials o transitives. La taula està, formalment parlant, tan malament com semblava.

A socis, amb claus candidates {soci_id} i {email}, són primers tots dos: soci_id i email. Tots els altres són no primers. Aquest exemple sol sorprendre: email és un atribut d'aspecte totalment corrent i tanmateix és primer, perquè és clau candidata.

Confondre "normalitzar" amb "tenir moltes taules". El nombre de taules és una conseqüència, no un objectiu. Si en descompondre no pots anomenar la dependència problemàtica que estàs eliminant, no estàs normalitzant: estàs complicant l'esquema. Davant del dubte, escriu la dependència en un paper abans de tocar el CREATE TABLE.

Deduir dependències de les dades de mostra. És l'error més car de tots, perquè no es detecta fins que el sistema és en producció i rebutja una dada legítima. Tota dependència ha de venir d'una regla de negoci confirmada. Si BiblioRed diu "en principi cada ISBN té un títol", aquest "en principi" és una alarma: pregunta per les excepcions abans d'escriure-la.

Invertir la fletxa. isbn → titol no és el mateix que titol → isbn, i la segona és falsa (diverses edicions comparteixen títol). Quan dubtis de la direcció, fes servir la formulació de prohibició: "pot haver-hi un ISBN amb dos títols?" (no → la dependència va d'ISBN a títol) davant de "pot haver-hi un títol amb dos ISBN?" (sí → no hi ha dependència en aquest sentit).

Partir el costat esquerre d'una dependència. De {A, B} → C no se segueix A → C ni B → C. El costat dret sí que es pot partir (regla de descomposició); l'esquerre, mai. Aplicar aquesta falsa regla en calcular una clausura produeix claus candidates inventades i descomposicions que perden informació.

Oblidar que un sol contraexemple refuta. Per demostrar que una dependència és falsa n'hi ha prou de trobar dues files amb el mateix determinant i diferent determinat. És la comprovació més barata que existeix i sempre val la pena fer-la abans d'acceptar una dependència. En SQL:

Compte: que retorni zero files no demostra la dependència (secció 6), però que en retorni alguna sí que la refuta, o bé indica que hi ha dades brutes per netejar. Al fitxer original de BiblioRed aquesta consulta retornava files, precisament per l'ISBN truncat.

Aturar-se a la primera passada en calcular una clausura. L'algorisme repeteix fins que res no canvia. És molt fàcil afegir isbn i oblidar que ara, amb isbn a dins, es dispara isbn → autor, i amb autor a dins es dispara autor → autor_nac. Marca les dependències ja utilitzades i torna a recórrer la llista sencera després de cada incorporació.

Consell de mètode: per calcular clausures a mà, escriu les dependències en forma desagregada (un sol atribut a la dreta) i ves ratllant-les a mesura que les facis servir. És molt més difícil equivocar-se.

BiblioRed manté una taula plana esdeveniments_full amb aquesta estructura, heretada d'un altre full de càlcul:

esdeveniments_full(esdeveniment_id, titol_esdeveniment, data, sala_nom, sala_aforament, sala_planta, ponent_email, ponent_nom)

Per a cadascuna d'aquestes tres situacions, digues quina anomalia és (inserció, actualització o esborrament) i quina dependència funcional la provoca:

• a) La sala Polivalent de Centre es reforma i el seu aforament passa de 60 a 90 places. Hi ha 214 esdeveniments celebrats.
• b) BiblioRed habilita una sala nova, la Sala Infantil d'Est, amb aforament 25. Encara no hi ha cap esdeveniment programat.
• c) Es cancel·la i s'esborra l'únic esdeveniment en què va participar la ponent Clara Ferran.

Sobre la relació:

R(esdeveniment_id, soci_email, soci_nom, data_insc, estat, acompanyants, places_ocupades)

amb el conjunt de dependències:

Es demana:

• a) Calcular {esdeveniment_id, soci_email}⁺ mostrant les passades.
• b) Dir si és superclau i si és clau candidata, justificant-ho.
• c) Calcular {soci_email}⁺ i dir què significa el resultat.
• d) Llistar els atributs primers i els no primers.

A la relació prestecs_full d'aquesta lliçó, classifica cadascuna d'aquestes cinc dependències com a total, parcial, transitiva o trivial respecte de la clau candidata {exemplar_cod, data_prestec}. Justifica'n cadascuna en una frase.

• a) {exemplar_cod, data_prestec} → soci_email
• b) exemplar_cod → sucursal_nom
• c) {exemplar_cod, data_prestec} → sucursal_ciutat
• d) {exemplar_cod, isbn} → exemplar_cod
• e) soci_email → soci_telefon

Solució 1

a) Anomalia d'actualització. La dependència culpable és sala_nom → sala_aforament: l'aforament és un fet sobre la sala, però està emmagatzemat una vegada per esdeveniment. Canviar-lo obliga a un UPDATE de 214 files, i si alguna es queda fora —per un filtre mal escrit, per una diferència de majúscules al nom de la sala— BiblioRed tindrà la mateixa sala amb dos aforaments. És exactament el cas "Nord"/"nord" traslladat a les sales.

b) Anomalia d'inserció. La mateixa dependència, sala_nom → {sala_aforament, sala_planta}, vista des de l'altre costat. Les dades de la sala només es poden desar penjades d'un esdeveniment, i aquesta sala no en té cap. L'única alternativa seria inserir un esdeveniment fantasma amb titol_esdeveniment i data inventats, que contaminaria qualsevol consulta sobre esdeveniments.

c) Anomalia d'esborrament. La dependència és ponent_email → ponent_nom. En esborrar l'esdeveniment desapareix també l'únic registre que deia que existeix una ponent anomenada Clara Ferran amb aquell correu. La informació sobre la ponent no tenia existència pròpia: vivia de prestat a la fila de l'esdeveniment.

Totes tres tenen la mateixa arrel: hi ha dependències el determinant de les quals (sala_nom, ponent_email) no és clau de la taula. Són entitats —sales, ponents— disfressades de columnes. I no per casualitat: a l'esquema real del mòdul 4, BiblioRed ja les té com a taules pròpies.

Solució 2

a) Clausura de {esdeveniment_id, soci_email}:

Els set atributs de R. Fixa't en l'efecte bola de neu: places_ocupades només entra després que hi entri acompanyants, que al seu torn hi va entrar per g1. Qui s'aturi a la primera dependència se'l perd.

b) És superclau, perquè la seva clausura conté tots els atributs. I és clau candidata perquè és mínima: cal comprovar els dos subconjunts propis d'un element.

• {esdeveniment_id}⁺ = {esdeveniment_id}. esdeveniment_id no apareix sol a l'esquerra de cap dependència (a g1 va acompanyat), així que la clausura no creix. No és superclau.
• {soci_email}⁺ = vegeu l'apartat c). No és superclau.

Com que cap subconjunt propi no és superclau, {esdeveniment_id, soci_email} és mínima i per tant clau candidata.

c) Clausura de {soci_email}:

Només g2 és aplicable, i després res més. Dos atributs de set. Significa que soci_email no és superclau, i —més interessant— que arrossega amb ell un atribut, soci_nom, que quedarà repetit a totes les inscripcions d'aquest soci. És una dependència parcial de la clau composta, el senyal inequívoc que el nom del soci no hi pinta res, en aquesta taula, i ha de viure a socis. A l'esquema real de BiblioRed, inscripcions no té soci_nom, i ara sabem formalment per què.

d) L'única clau candidata és {esdeveniment_id, soci_email}.

• Primers: esdeveniment_id, soci_email.
• No primers: data_insc, estat, acompanyants, soci_nom, places_ocupades.

Solució 3

a) Total. El determinant és la clau completa i cap de les seves dues meitats no basta: exemplar_cod → soci_email és falsa (EJ-3081 es va prestar a la Marta dues vegades diferents, però podria haver-se prestat a una altra persona en una altra data; i en general un exemplar circula entre socis), i data_prestec → soci_email és evidentment falsa (el mateix dia es presten llibres a diversos socis). És una dependència sana: soci_email és un fet genuí sobre el préstec.

b) Parcial. El determinant exemplar_cod és una part pròpia de la clau composta i ja determina sucursal_nom tot sol. La data del préstec no hi intervé: la sucursal on viu una còpia no canvia segons quan es presti. És una de les dependències que eliminarà la segona forma normal.

c) Transitiva. La clau determina sucursal_ciutat, sí, però per una cadena de tres salts: {exemplar_cod, data_prestec} → exemplar_cod → sucursal_nom → sucursal_cp → sucursal_ciutat. Cap de les baules intermèdies no és superclau i sucursal_ciutat és un atribut no primer, que són les dues condicions de la transitivitat. És la que eliminarà la tercera forma normal.

(Nota: aquesta dependència és alhora parcial, perquè exemplar_cod tot sol ja la produeix. No són categories excloents: una mateixa dependència pot ser problemàtica per més d'un motiu, i per això la normalització s'aplica per etapes —primer 2FN, després 3FN— en comptes de tot de cop.)

d) Trivial. El costat dret, {exemplar_cod}, està contingut a l'esquerre, {exemplar_cod, isbn}. Es compleix sempre, en qualsevol taula, sense que ningú no ho decideixi. No diu res sobre el disseny i les definicions de les formes normals l'exclouran explícitament.

e) Transitiva (respecte de la clau). Per si sola, soci_email → soci_telefon és simplement una dependència; el que la fa transitiva és la seva relació amb la clau: {exemplar_cod, data_prestec} → soci_email → soci_telefon, amb soci_email no superclau i soci_telefon no primer. La seva conseqüència pràctica és que el telèfon de la Marta Alsina està escrit tres vegades en cinc files.

Aquesta lliçó ha construït l'instrumental. Recapitulem el que ara és al teu abast i no ho era en començar el mòdul.

Saps què és normalitzar: reorganitzar atributs perquè cada fet hi sigui una sola vegada, amb un procediment justificable. I saps què no ho és: ni partir taules per esport, ni una finalitat en si mateixa, ni netejar dades brutes. Saps també que l'enemic no és l'espai en disc sinó la possibilitat física de la contradicció, i que tot allò que és possible acaba passant.

Saps anomenar el mal. Les tres anomalies —d'inserció, d'actualització i d'esborrament— van deixar de ser una taula de tres línies del mòdul 4 per convertir-se en tres fallades concretes que has vist provocar amb INSERT, UPDATE i DELETE sobre el full de BiblioRed. I saps que totes tres tenen una única causa estructural.

Saps escriure la causa. X → Y, "X determina Y", amb el seu determinant i el seu determinat, amb la seva direcció que no es pot invertir, i amb l'advertiment capital que surt de les regles del negoci i mai de les dades de mostra. Saps distingir les dependències totals (les sanes), les parcials (mitja clau determina alguna cosa) i les transitives (s'hi arriba per un atribut que no és clau), i saps descartar les trivials.

Saps calcular. Els tres axiomes d'Armstrong —reflexivitat, augment, transitivitat— i les seves tres regles derivades —unió, descomposició, pseudotransitivitat— permeten deduir tota dependència que se segueixi de les conegudes. I l'algorisme de la clausura X⁺ converteix en mecànic el que era intuïció: respon si una dependència es dedueix, si un conjunt és superclau i, aplicat amb la classificació d'atributs per la seva posició a F, troba les claus candidates. L'hem fet servir per demostrar que la clau de prestecs_full és {exemplar_cod, data_prestec} i que socis té dues claus candidates.

I saps classificar els atributs en primers (els que formen part d'alguna clau candidata) i no primers (la resta), que és la distinció sobre la qual es recolzen literalment les definicions que vénen ara.

Perquè el que ve ara són les formes normals. Són una escala de nivells d'exigència creixents: la primera demana poc i gairebé qualsevol taula raonable la compleix; la segona hi afegeix una condició; la tercera, una altra; i així fins a un punt en què les exigències són tan fines que rarament s'apliquen a la pràctica. Cada nivell prohibeix un tipus concret de dependència mal col·locada —i has de reconèixer totes les que hem definit en aquesta lliçó per entendre quina prohibeix cadascun.

A la lliçó 05-02, Formes Normals, les recorrerem una a una: primera, segona, tercera, Boyce-Codd, quarta i cinquena, cadascuna amb la seva definició precisa, un exemple mínim de BiblioRed que la viola, la correcció corresponent i el motiu pel qual importa. Encara no aplicarem cap metodologia sobre el full de préstecs —això és la lliçó 05-03—: primer cal tenir el catàleg complet.