Model Relacional

Definició i objectius

Àlgebra relacional (aprofundiment opcional)

El model relacional té un fonament formal anomenat àlgebra relacional: un conjunt d'operadors (selecció, projecció, join, unió...) que descriuen com manipular relacions de manera matemàtica. SQL és, en essència, una implementació pràctica d'aquest llenguatge formal.

Si vols entendre com el SGBD interpreta i optimitza les teves consultes, consulta Àlgebra relacional.

El model relacional va ser formulat per Edgar F. Codd el 1970 basant-se en la teoria de conjunts i el càlcul de predicats. És el fonament teòric dels SGBD relacionals moderns (PostgreSQL, MySQL, SQL Server, Oracle) i el model lògic més estès en la indústria.

Els seus principals objectius són:

Objectiu	Descripció
Independència física	La forma d'emmagatzemar les dades no afecta la manera de manipular-les lògicament
Independència lògica	Les aplicacions no és veuen afectades per modificacions a l'estructura de la BD
Flexibilitat	Permet presentar els mateixos dades en múltiples vistes per a diferents usuaris
Uniformitat	Totes les dades es representen de la mateixa manera: taules
Senzillesa	L'estructura és simple i intuïtiva

Elements del model relacional

Concepte formal	Terme SQL equivalent	Descripció
Relació	Taula	Un conjunt de tuples amb el mateix esquema; té un nom únic
Tupla	Fila / Registre	Un element del conjunt (una instància d'una entitat)
Atribut	Columna / Camp	Una propietat de les tuples de la relació
Domini	Tipus de dades	El conjunt de valors vàlids per a un atribut
Grau	Nombre de columnes	El nombre d'atributs de la relació
Cardinalitat	Nombre de files	El nombre de tuples de la relació

Restricció fonamental: Cap dues tuples d'una relació poden ser idèntiques, i totes les tuples han de tenir el mateix nombre de camps (tot i que els valors poden ser nuls).

Dominis

El domini d'un atribut especifica els valors permesos per a aquell camp i garanteix l'atomicitat: un camp no pot contenir múltiples valors del mateix tipus simultàniament.

Tipus de domini	Descripció	Exemple
Text	Cadenes de caràcters no operacionals	`nom`, `descripcio`
Numèric	Valors que suporten operacions matemàtiques	`preu`, `edat`
Data/hora	Dates i instants temporals	`data_naixement`, `created_at`
Booleà	Cert o fals	`actiu`, `verificat`
Autonumèric	Seqüències autoincremental	`id` generat automàticament

Valors nuls

Un NULL representa l'absència d'informació, no un zero ni una cadena buida. Pot indicar:

Informació desconeguda (no sabem el valor).
Informació no aplicable (el camp no té sentit per a aquesta tupla).

NULL no és zero ni buit

NULL ≠ 0, NULL ≠ '', NULL ≠ FALSE. Qualsevol operació aritmètica o comparació amb NULL retorna NULL. Usa IS NULL i IS NOT NULL per comprovar-ne l'existència, no = NULL.

Claus

Les claus són atributs (o conjunts d'atributs) amb un significat especial per a la identificació i la integritat de les dades.

Superclau

Qualsevol atribut o conjunt d'atributs que identifica unívocament una tupla. Hi poden haver moltes superclaus en una mateixa taula.

Clau candidata

Una superclau minimal: si traiem qualsevol atribut del conjunt, ja no identifica unívocament. Pot haver-hi múltiples claus candidates en una taula.

Exemple: A ALUMNES(id_alumne, dni, nom, email), tant id_alumne com dni com email són claus candidates (assumint unicitat).

Clau primària (PK)

La clau candidata que escollim per identificar les tuples. Característiques:

Unicitat: No poden existir dues tuples amb el mateix valor de PK.
No nul·la: No pot contenir valors NULL (integritat d'entitat).
hauria de ser estable (no canviar) i compacta (valors curts milloren el rendiment dels índexs).

Clau substituta (surrogate key): Quan cap atribut natural identifica unívocament les files, s'usa una columna numèrica autoincrementada com a clau substituta independent dels valors de negoci.

PostgreSQLMySQL / MariaDBSQL Server

id SERIAL PRIMARY KEY
-- o bé
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY

id INT AUTO_INCREMENT PRIMARY KEY

id INT IDENTITY(1,1) PRIMARY KEY

Clau alternativa (UK)

Les claus candidates que no han estat escollides com a PK. Es implementen com a restriccions UNIQUE per garantir que tampoc no és repeteix el seu valor.

Exemple: Si escollim id_alumne com a PK, llavors dni i email es definiran com UNIQUE.

Clau forana (FK)

Un atribut (o conjunt d'atributs) d'una taula que referència la clau primària d'una altra taula (o de la mateixa). Implementen les relacions entre taules. El domini de la FK i el de la PK referenciada han de ser idèntics.

Les claus foranes poden contenir NULL (indica que la fila no té relació establerta amb l'altra taula).

Restriccions semàntiques

A més de les claus, el model relacional permet definir restriccions addicionals sobre les dades:

Restricció	Abreujament	Descripció
NOT NULL	VNN (Valor No Nul)	Prohibeix valors nuls; el camp és obligatori
UNIQUE	UK	Impedeix la repetició de valors (permet nuls a la majoria de SGBD)
DEFAULT	—	Assigna un valor per defecte si no s'especifica cap
CHECK	—	Condició personalitzada de validació (`CHECK (edat >= 18)`)

Integritat

Integritat d'entitat

Tot atribut de la clau primària ha de tenir valor i no pot ser NULL. Aquesta restricció fonamental garanteix que cada tupla és unívocament identificable.

Integritat referencial

Si la relació R1 té una clau forana que referència R2, llavors tots els valors de la FK han de, o bé coincidir amb algun valor de la PK de R2, o bé ser NULL.

Exemple: Si tenim COMANDES(id_comanda, id_client FK) i CLIENTS(id_client PK), la integritat referencial garanteix:

No podem inserir una COMANDA amb un id_client que no existeixi a CLIENTS.
Si volem eliminar un CLIENT que té COMANDES associades, el SGBD actuarà d'acord amb l'acció referencial configurada.

Accions referencials

Quan s'elimina o s'actualitza un registre pare (al qual apunta una FK), el SGBD pot aplicar:

Acció	Comportament
`RESTRICT` / `NO ACTION`	Rebutja l'operació si hi ha files filles (comportament per defecte)
`CASCADE`	Propaga l'eliminació/actualització a totes les files filles
`SET NULL`	Posa a NULL la FK a les files filles
`SET DEFAULT`	Posa el valor per defecte a la FK de les files filles

Notació textual de l'esquema relacional

L'esquema relacional es representa textualment de la forma següent:

TAULA(atribut1, atribut2, atribut3*, ...)
    PK: (atribut1)
    FK: (atribut3) → ALTRA_TAULA(atribut_referenciat)
    UK: (atribut2)
    VNN: atribut2

Convencions habituals:

Subratllat → clau primària
Asterisc * → clau forana
UK → restricció d'unicitat
VNN → valor no nul

Exemple:

LLIBRE(isbn, titol, autor*, editorial, pvp)
    PK: (isbn)
    FK: (autor) → AUTOR(id_autor)
    VNN: titol

Eines de modelatge gràfic

dbdiagram.io permet dibuixar esquemes relacionals usant el llenguatge DBML i genera automàticament el DDL (CREATE TABLE) per a PostgreSQL, MySQL o SQL Server. ERDPlus és una alternativa gratuïta i accessible des del navegador.

Transformació del model E/R al model relacional

El pas del model E/R al model relacional segueix un conjunt de regles sistemàtiques. Cada regla transforma un element del diagrama ER en un o més elements del model relacional.

Transformació d'atributs

Abans de transformar les entitats, cal tenir en compte com es transformen els atributs especials:

Tipus d'atribut ER	Transformació al model relacional
Simple	Columna directa
Compost	Es descompon en columnes simples (`adreca` → `adreca_carrer`, `adreca_num`, `adreca_cp`)
Derivat	S'omet (es recalcula) o s'emmagatzema el valor base (p. ex., `data_naixement` en lloc de `edat`)
Multivaluat	Es crea una taula separada amb una FK cap a l'entitat original

Exemple d'atribut multivaluat: Si CLIENT pot tenir múltiples telèfons:

CLIENT(id_client, nom, email)       -- taula original
TELEFON_CLIENT(id_client*, telefon) -- nova taula per al multivaluat
    PK: (id_client, telefon)
    FK: (id_client) → CLIENT

Regla 1: Entitats fortes

Cada entitat forta es converteix en una taula. Els atributs de l'entitat es converteixen en columnes. La clau de l'entitat es converteix en la clau primària de la taula.

ENTITAT CLIENT(id_client, nom, email)
→ CLIENT(id_client, nom, email)
     PK: (id_client)

Regla 2: Entitats dèbils

Cada entitat dèbil es converteix en una taula. La clau primària és la combinació de la clau parcial de l'entitat dèbil i la clau primària de l'entitat forta de la qual depèn.

ENTITAT DÈBIL LINIA_COMANDA (num_linia) dependent de COMANDA(id_comanda)
→ LINIA_COMANDA(id_comanda*, num_linia, quantitat, preu_unitari)
     PK: (id_comanda, num_linia)
     FK: (id_comanda) → COMANDA

Regla 3: Relacions 1:N

La clau primària del costat "1" s'afegeix com a clau forana a la taula del costat "N".

Si la cardinalitat mínima del costat N és 1 → la FK s'ha de declarar NOT NULL.
Si la cardinalitat mínima del costat N és 0 → la FK pot ser NULL.

CLIENT (1,1) ── realitza ── (0,N) COMANDA
→ COMANDA(id_comanda, data, id_client*)
     PK: (id_comanda)
     FK: (id_client) → CLIENT    -- NOT NULL perquè min=1 al costat CLIENT

Regla 4: Relacions 1:1

La FK es pot posar a qualsevol de les dues taules, però cal afegir una restricció UNIQUE per garantir que no és repeteixi. La recomanació és posar la FK al costat de participació opcional (min=0).

Opció A — FK a la primera entitat:

A(a0, a1, b0*)   PK: (a0)  FK: (b0) → B  UK: (b0)
B(b0, b1)        PK: (b0)

Opció B — FK a la segona entitat:

A(a0, a1)        PK: (a0)
B(b0, b1, a0*)   PK: (b0)  FK: (a0) → A  UK: (a0)

Si les dues cardinalitats mínimes són 1, la FK ha de ser NOT NULL.

Cas especial: 1:1 amb participació total dels dos costats

Si Card(A,R) = (1,1) i Card(B,R) = (1,1), es pot valorar fusionar les dues entitats en una sola taula per evitar la dependència circular. En cas de mantenir dues taules, relaxa un costat a (0,1).

Exemple: PERSONA (0,1) ── té ── (1,1) PASSAPORT

PERSONA(id_persona, nom, dni)
     PK: (id_persona)

PASSAPORT(id_passaport, numero, id_persona*)
     PK: (id_passaport)
     FK: (id_persona) → PERSONA
     UK: (id_persona)          -- garanteix que un passaport pertany a una sola persona

Regla 5: Relacions N:M

Les relacions N:M requereixen una taula pont (taula d'associació). La clau primària és la combinació de les dues FK. Els atributs propis de la relació passen a ser columnes d'aquesta taula.

ALUMNE (0,N) ── cursa ── (0,N) ASSIGNATURA
→ MATRICULA(id_alumne*, id_assignatura*, data_matricula, nota_final)
     PK: (id_alumne, id_assignatura)
     FK: (id_alumne) → ALUMNE
     FK: (id_assignatura) → ASSIGNATURA

N:M amb dimensió temporal: Si la mateixa parella d'entitats pot relacionar-se en moments diferents, cal incloure la data d'inici a la clau primària.

CLIENT (N) ── lloga ── (M) VEHICLE  (en períodes de temps)
→ LLOGUER(id_client*, id_vehicle*, data_inici, data_fi)
     PK: (id_client, id_vehicle, data_inici)
     FK: (id_client) → CLIENT
     FK: (id_vehicle) → VEHICLE

Regla 6: Relacions reflexives

Una entitat es relaciona amb ella mateixa.

Reflexiva 1:N: S'afegeix una FK a la mateixa taula apuntant a la seva pròpia PK.

EMPLEAT supervisa EMPLEAT
→ EMPLEAT(id_empleat, nom, id_supervisor*)
     PK: (id_empleat)
     FK: (id_supervisor) → EMPLEAT   -- pot ser NULL si no té supervisor

Reflexiva N:M: Es crea una taula pont amb dues FK que apunten a la mateixa taula.

PRODUCTE substitueix PRODUCTE
→ SUBSTITUCIO(id_producte*, id_substitut*)
     PK: (id_producte, id_substitut)
     FK: (id_producte) → PRODUCTE
     FK: (id_substitut) → PRODUCTE

Regla 7: Relacions ternàries (i N-àries)

Es crea una taula pont amb claus foranes cap a totes les entitats participants. La clau primària normalment inclou les FK de totes les entitats (o menys si alguna té cardinalitat màxima 1).

PROVEIDOR ── subministra ── PEÇA ── a ── PROJECTE
→ SUBMINISTRAMENT(id_proveidor*, codi_peca*, num_projecte*, quantitat, data)
     PK: (id_proveidor, codi_peca, num_projecte)
     FK: (id_proveidor) → PROVEIDOR
     FK: (codi_peca) → PECA
     FK: (num_projecte) → PROJECTE

Claus compostes i FK compostes

Quan una entitat té una clau primària composta, les claus foranes que la referencien també han de ser compostes, incloent-hi tots els components de la clau.

Regla 8: Jerarquies de generalització/especialització

Hi ha tres estratègies per transformar una jerarquia IS-A al model relacional:

Opció A — Una taula per entitat (enfocament explícit)

Es crea una taula per a la superclasse i una taula per a cada subclasse. Les taules de les subclasses usen la PK de la superclasse com a PK i FK alhora.

PERSONA(dni, nom, adreca, telefon)        PK: (dni)
ESTUDIANT(dni*, nivell, institucio, hores) PK: (dni) FK: (dni) → PERSONA
TREBALLADOR(dni*, num_ss, salari)          PK: (dni) FK: (dni) → PERSONA

Avantatge: preserva la semàntica, evita nuls.
Inconvenient: cal fer JOINs per obtenir les dades completes d'una subclasse.

Opció B — Una sola taula (enfocament col·lapsat)

Totes les entitats (superclasse i subclasses) s'unifiquen en una única taula amb una columna discriminador que indica a quina subclasse pertany cada fila.

PERSONA(dni, nom, adreca, telefon, tipus, nivell, institucio, hores, num_ss, salari)
     PK: (dni)
     -- "tipus" pot ser 'ESTUDIANT', 'TREBALLADOR', etc.

Avantatge: no cal fer JOINs, consultes simples.
Inconvenient: moltes columnes seran NULL; es perd semàntica.

Opció C — Una taula per subclasse (enfocament horitzontal)

Es crea una taula per a cada subclasse, incloent-hi tots els atributs de la superclasse. No hi ha taula per a la superclasse.

ESTUDIANT(dni, nom, adreca, telefon, nivell, institucio, hores) PK: (dni)
TREBALLADOR(dni, nom, adreca, telefon, num_ss, salari)          PK: (dni)

Avantatge: consultes per subclasse molt eficients.
Inconvenient: redundància d'atributs comuns; si un individu és de dues subclasses, apareix duplicat.

	Opció A (explícita)	Opció B (col·lapsada)	Opció C (horitzontal)
JOINs necessaris	Sí	No	No
Nuls	Cap	Molts	Cap
Redundància	Cap	Cap	Atributs comuns
Semàntica	Alta	Baixa	Mitjana
Recomanada per a	Especialització parcial	Pocs atributs específics	Especialització total disjunta

Resum: regles de cardinalitat a la transformació

Cardinalitat	Efecte a la transformació
Max = 1 a la banda receptora de FK	Afegir restricció `UNIQUE`
Min = 1 a la banda de la FK	Declarar la FK com `NOT NULL`
Min = 1 al costat N d'una 1:N	Expressivitat perduda; no és pot garantir a nivell de BD sense triggers

Exemple complet: De l'E/R al relacional (biblioteca)

Partint del diagrama ER de la biblioteca de la sessió anterior, la transformació al model relacional seguint les regles:

SOCI(id_soci, nom, cognoms, dni, dataAlta, email)
    PK: (id_soci)
    UK: dni, email

AUTOR(id_autor, nom, cognoms, nacionalitat)
    PK: (id_autor)

LLIBRE(id_llibre, isbn, titol, anyPublicacio, editorial)
    PK: (id_llibre)
    UK: isbn

LLIBRE_AUTOR(id_llibre*, id_autor*)        -- Relació N:M
    PK: (id_llibre, id_autor)
    FK: (id_llibre) → LLIBRE
    FK: (id_autor) → AUTOR

EXEMPLAR(id_exemplar, id_llibre*, numInventari, estat)   -- Relació 1:N
    PK: (id_exemplar)
    FK: (id_llibre) → LLIBRE  NOT NULL
    UK: numInventari

PRESTEC(id_prestec, id_soci*, id_exemplar*, dataPrestec, dataDevolucioPrevista, dataDevolucioReal)
    PK: (id_prestec)
    FK: (id_soci) → SOCI
    FK: (id_exemplar) → EXEMPLAR

AC0372/02/04 — Miniactivitat

RA2 · CA2.3, CA2.4, CA2.5, CA2.6, CA2.7

Transformeu al model relacional el diagrama ER de la clínica veterinaria que vau dissenyar a la sessió anterior. Per a cada taula indiqueu:

Nom de la taula i tots els seus atributs.
Clau primària (PK).
Claus foranes (FK) amb la taula i l'atribut referenciat.
Restriccions UNIQUE i NOT NULL on escaigui.
Quina regla de transformació heu aplicat a cada element.