Modelatge de dades
Models conceptual, lògic i físic, diagrames entitat-relació i pas al model relacional
4 fonts originals
01. Resum
Comença per aquí
Si només recordes cinc coses d'aquest tema:
- Modelar és decidir què cal guardar i com es relaciona, abans de crear cap taula.
- Tres nivells: conceptual (què necessita el negoci), lògic (taules, PK i FK) i físic (tipus de dades i SGBD).
- Diagrama ER: rectangle = entitat, rombe = relació, el·lipse = atribut. La cardinalitat es pregunta en els dos sentits.
- Transformació: 1:N → FK al costat N. N:M → taula intermèdia amb les dues FK com a PK. 1:1 → FK amb
UNIQUE. - Els atributs de la parella (quantitat, nota, hores) van a la taula intermèdia. Telèfons i codis postals són
VARCHAR.
A la versió completa, cada apartat acaba amb una pregunta d'examen i la seva resposta model.
Què és modelar dades
Modelar és decidir què cal guardar, quines dades té cada cosa i com es relacionen, abans de crear cap taula.
Exemple: una botiga en línia té clients, comandes, productes i pagaments. Si ho poses tot en una sola taula, les dades del client es repeteixen a cada comanda.
Sense model: duplicats, errors i un sistema difícil d'ampliar. Amb model: cada dada en un sol lloc.
Abans de crear les taules d'una base de dades cal respondre quatre preguntes:
Què guardem?
Quina informació necessita l'organització.
Qui hi intervé?
Quins elements formen part del negoci: clients, comandes, productes, pagaments.
Què en sabem?
Quines característiques cal guardar de cada element.
Com es relacionen?
Quins vincles hi ha entre aquests elements.
El modelatge de dades és el procés de representar de manera estructurada la informació que gestiona una organització.
Per què cal modelar abans de crear taules
Sense model: directament a les taules
- Informació duplicada
- Dades difícils de mantenir
- Relacions incorrectes
- Pèrdua d'informació
- Sistema difícil d'ampliar
Amb model: primer entendre el negoci
- Cada dada en un sol lloc
- Relacions correctes entre taules
- Fàcil de mantenir i d'ampliar
- Decisions de disseny justificades
Prova-ho. La taula única repeteix les dades d'Ana a cada comanda. Mira què passa quan Ana canvia d'email o quan esborres una comanda.
- Necessitats del negociQuè ha de gestionar l'empresa
- Model de dadesConceptual → lògic → físic
- Base de dadesImplementació en SQL
Tres nivells: conceptual, lògic i físic
El disseny té tres nivells. Cada un afegeix detall:
| Nivell | Pregunta | Què hi veus |
|---|---|---|
| Conceptual | Què necessita el negoci? | Entitats, atributs, relacions, cardinalitats |
| Lògic | On va cada dada? | Taules, columnes, PK, FK |
| Físic | Amb què s'implementa? | Tipus (INT, VARCHAR), restriccions, SGBD |
Truc: si veus VARCHAR o NOT NULL, és el físic. Si veus PK i FK sense tipus, és el lògic. Si només hi ha entitats i verbs, és el conceptual.
El disseny es fa de manera progressiva, en tres nivells. Cada nivell afegeix detall a l'anterior.
1 · Conceptual
Què informació necessita el negoci?
- Entitats
- Atributs principals
- Relacions
- Cardinalitats
No depèn de cap SGBD.
2 · Lògic
Com s'organitza la informació?
- Taules
- Atributs (columnes)
- Claus primàries
- Claus foranes
Encara no depèn necessàriament d'un SGBD.
3 · Físic
Com s'implementa en un SGBD concret?
- Tipus de dades
- Longitud dels camps
- Restriccions
- Índexs
MySQL, MariaDB, PostgreSQL, Oracle…
Per a qui és cada model
Conceptual · per parlar amb el negoci
El pot llegir una persona que no és informàtica. Serveix per validar els requisits amb el client.
Lògic · per dissenyar
Defineix l'estructura de taules i claus. És la base de qualsevol implementació.
Físic · per implementar
És el que executa el SGBD. Canvia si canvies de MySQL a Oracle.
El mateix exemple en els tres nivells
Un client pot fer moltes comandes. Cada comanda és d'un sol client.
La relació conceptual es converteix en una relació entre taules mitjançant claus.
| Camp | Tipus | Restricció |
|---|---|---|
| ClienteID | INT | PRIMARY KEY |
| Nombre | VARCHAR(100) | NOT NULL |
VARCHAR(150) | UNIQUE | |
| PedidoID | INT | PRIMARY KEY |
| Fecha | DATE | NOT NULL |
| Importe | DECIMAL(10,2) | — |
| ClienteID | INT | FOREIGN KEY |
| Model | Respon a | Elements principals |
|---|---|---|
| Conceptual | Quina informació necessitem? | Entitats i relacions |
| Lògic | Com organitzem les dades? | Taules, atributs, PK, FK |
| Físic | Com ho implementem? | Tipus de dades, restriccions, SGBD |
Pregunta. Explica els tres nivells del modelatge de dades. A quin nivell pertany Precio DECIMAL(10,2) NOT NULL?
Resposta model. El model conceptual descriu què necessita el negoci: entitats, atributs, relacions i cardinalitats, sense dependre de cap tecnologia. El model lògic el converteix en taules, columnes, claus primàries i claus foranes. El model físic concreta com s'implementa en un SGBD: tipus de dades i restriccions. Precio DECIMAL(10,2) NOT NULL és del model físic, perquè indica un tipus de dades i una restricció.
Diagrames entitat-relació (ER)
Un diagrama ER dibuixa el negoci. Notació de Chen:
- Rectangle = entitat (substantiu): CLIENTE.
- Rombe = relació (verb): realiza.
- El·lipse = atribut (dada): Nombre.
- Subratllat = identificador (serà la PK).
Entitat = el tipus (CLIENTE). Ocurrència = un cas concret (Ana López).
Cardinalitat: pregunta en els dos sentits. Quantes comandes fa un client? Moltes. Quants clients té una comanda? Un. → 1:N.
Un diagrama ER és una representació gràfica de les entitats d'un sistema i de les relacions entre elles. Hi representem quatre coses:
Entitat
Un objecte, persona, concepte o element del negoci del qual guardem informació: CLIENTE, PRODUCTO, PEDIDO, EMPLEADO, PROVEEDOR.
Atribut
Una característica o propietat de l'entitat: Nombre, Email, Precio, Stock.
Relació
Una associació entre dues o més entitats. Es nomena amb un verb: realiza, contiene, pertenece a.
Cardinalitat
Quantes ocurrències d'una entitat es poden relacionar amb ocurrències de l'altra: 1:1, 1:N o N:M.
Notació: com es dibuixa
Fem servir la notació de Chen. Cada element té una forma:
▭ Rectangle
Una entitat. El nom va en majúscules i en singular: CLIENTE.
◇ Rombe
Una relació. Dins hi va un verb: realiza.
⬭ El·lipse
Un atribut. Es connecta a la seva entitat amb una línia.
Subratllat
L'identificador de l'entitat. Serà la clau primària.
1 · N · M
La cardinalitat, escrita sobre cada línia que va a una entitat.
El·lipse al rombe
Un atribut de la relació: depèn de la parella, com la quantitat.
Entitat i ocurrència
Entitat: el tipus
CLIENTE
Descriu tots els clients: quins atributs tenen.
→ Al model relacional, una taula.
Ocurrència: un cas concret
Ana López · ana@mail.cat
Un client concret, amb els seus valors.
→ Al model relacional, una fila.
L'identificador
Cada ocurrència d'una entitat s'ha de poder identificar de manera única. A CLIENTE, ClienteID diferencia cada client. Al model relacional, l'identificador es converteix normalment en la clau primària (PK).
Bon identificador
- Únic: no hi ha dos clients amb el mateix valor
- Estable: no canvia amb el temps
- Sempre informat: mai és buit
- Exemple: ClienteID, un número que crea el sistema
Mal identificador
- Nombre: dos clients es poden dir igual
- Email: el client el pot canviar
- Telefono: es pot compartir o no tenir-ne
- Ciudad: es repeteix molt
Com trobar les entitats
Requisit: ShopSphere necessita guardar informació sobre els seus clients, les comandes que fan i els productes del seu catàleg.
| Paraula del requisit | Què és? | Per què |
|---|---|---|
| clients | Entitat → CLIENTE | En guardem dades pròpies |
| comandes | Entitat → PEDIDO | En guardem dades pròpies |
| productes | Entitat → PRODUCTO | En guardem dades pròpies |
| fan | Relació → realiza | Un verb que uneix client i comanda |
| catàleg | Res | És el conjunt de productes, no una cosa nova |
| nom del client | Atribut | Descriu el client |
Estratègia: busca els substantius importants del requisit → entitats. Busca els verbs que els uneixen → relacions. Compte: no tots els substantius són entitats. Alguns són atributs (el nom del client) i altres no cal guardar-los (el catàleg).
Cardinalitats
1:1 · Un a un
Una ocurrència es relaciona com a màxim amb una de l'altra.
PERSONA 1 ── tiene ── 1 PASAPORTE
Una persona té un passaport. Un passaport és d'una persona.
1:N · Un a molts
Una ocurrència es relaciona amb diverses de l'altra.
CLIENTE 1 ── realiza ── N PEDIDO
La més freqüent a les bases de dades d'empresa.
N:M · Molts a molts
Diverses ocurrències amb diverses de l'altra.
PEDIDO N ── contiene ── M PRODUCTO
Una comanda té molts productes. Un producte surt a moltes comandes.
Mira-ho amb dades. Toca una fila per veure amb quines files es relaciona:
Com trobar-la: fes la pregunta en els dos sentits. Quantes comandes pot fer un client? Moltes. Quants clients té una comanda? Un. → 1:N. Si les dues respostes són «moltes», és N:M. Si les dues són «una», és 1:1.
| Pregunta A → B | Pregunta B → A | Cardinalitat |
|---|---|---|
| Un | Un | 1:1 |
| Molts | Un | 1:N |
| Molts | Molts | N:M |
La cardinalitat diu el màxim. També podem pensar en el mínim:
- Un client pot tenir zero comandes (s'acaba de registrar). Participació opcional.
- Una comanda ha de tenir un client. Participació obligatòria.
Al model físic, la participació obligatòria es tradueix en NOT NULL a la FK: ClienteID INT NOT NULL a PEDIDOS. Així no pot existir cap comanda sense client.
Com es construeix un diagrama ER
- EntitatsIdentifica-les als requisits: els substantius rellevants.
- AtributsDecideix què cal guardar de cada entitat.
- IdentificadorsTria l'atribut que identifica cada ocurrència: serà la PK.
- RelacionsBusca els verbs que associen les entitats.
- CardinalitatsPregunta en els dos sentits: 1:1, 1:N o N:M.
- RevisióComprova que el model respon a tots els requisits.
Pregunta. Una acadèmia té alumnes i cursos. Un alumne es pot matricular a diversos cursos i cada curs té molts alumnes. Quina cardinalitat té la relació? Com ho has decidit?
Resposta model. Pregunto en els dos sentits. Quants cursos pot fer un alumne? Molts. Quants alumnes pot tenir un curs? Molts. La relació és N:M. Si només hagués preguntat en un sentit, hauria pogut concloure per error que és 1:N.
Transformació al model relacional
Entitat → taula · atribut → columna · identificador → PK.
Tres regles per a les relacions:
- 1:N → la PK del costat 1 passa com a FK al costat N. ClienteID va a PEDIDOS.
- N:M → taula intermèdia amb les dues FK. Juntes formen la PK. Els atributs de la parella (quantitat, nota, hores) van aquí.
- 1:1 → una FK a una de les dues taules, amb UNIQUE.
Prova de la parella: si un valor canvia quan canvies la comanda i també quan canvies el producte, va a la taula intermèdia.
Quan el diagrama ER està fet, el transformem en un conjunt de taules. Quatre regles bàsiques:
- EntitatTaula
- AtributColumna
- IdentificadorClau primària
- RelacióClau forana o taula nova
Entitat → taula
Cada entitat es converteix normalment en una taula. El nom va en plural. Cada atribut és una columna i l'identificador és la PK.
| ClienteID | Nombre | Ciudad | |
|---|---|---|---|
| 1 | Ana López | ana@mail.cat | Barcelona |
| 2 | Carlos Ruiz | carlos@mail.cat | Madrid |
Relació 1:N
Regla 1:N: la clau primària del costat 1 passa com a clau forana a la taula del costat N. CLIENTE (1) → ClienteID va com a FK a PEDIDOS (N).
Per què no al revés? Prova les dues opcions:
Relació N:M
Una relació N:M no es pot implementar directament. Cal una taula intermèdia:
La N:M es transforma en dues relacions 1:N: PEDIDO 1 → N DETALLE_PEDIDO i PRODUCTO 1 → N DETALLE_PEDIDO. Les dues FK juntes formen la PK de la taula intermèdia.
La comanda 1001 conté 2 ordinadors i 3 teclats. La 1002 conté 1 teclat. Així queda DETALLE_PEDIDO:
| PedidoID | ProductoID | Cantidad |
|---|---|---|
| 1001 | 10 · Ordinador | 2 |
| 1001 | 11 · Teclat | 3 |
| 1002 | 11 · Teclat | 1 |
Cada fila és una parella comanda–producte. La parella (1001, 11) no es pot repetir: és la PK.
Relació 1:1
Una 1:1 es resol amb una FK en una de les dues taules, amb la restricció UNIQUE.
Amb UNIQUE
- Una persona només pot sortir una vegada a PASAPORTES
- La relació continua sent 1:1
Sense UNIQUE
- Una persona podria tenir diversos passaports
- La relació passa a ser 1:N
On poso la FK en una 1:1? Normalment a la taula que depèn de l'altra o que pot no existir: el passaport depèn de la persona. Una persona pot no tenir passaport, però un passaport sempre és d'algú.
Atributs d'una relació
La comanda 1001 conté 2 ordinadors i 3 teclats. On guardem la quantitat?
A PEDIDO?
- La comanda té diversos productes, cada un amb una quantitat diferent.
A PRODUCTO?
- El teclat surt a moltes comandes, cada una amb una quantitat diferent.
A DETALLE_PEDIDO
- La quantitat depèn de la parella comanda–producte: és un atribut de la relació.
Prova de la parella: pregunta't «aquest valor canvia si canvio només la comanda? I si canvio només el producte?». Si canvia en els dos casos, depèn de la parella → taula intermèdia.
A les diapositives, DETALLE_PEDIDO guarda Cantidad i també Precio: el preu al qual es va vendre en aquella comanda, que pot ser diferent del preu actual del producte. També depèn de la parella.
Les tres regles juntes
Pregunta. Transforma en taules: CLIENTE realitza PEDIDO (1:N) i PEDIDO conté PRODUCTO (N:M, amb la quantitat). Justifica on va cada clau forana.
Resposta model. CLIENTES (ClienteID PK, Nombre). PEDIDOS (PedidoID PK, Fecha, ClienteID FK). PRODUCTOS (ProductoID PK, Nombre, Precio). DETALLE_PEDIDO (PedidoID FK, ProductoID FK, Cantidad), amb les dues claus foranes juntes com a clau primària. La FK ClienteID va a PEDIDOS perquè és el costat N: cada comanda té un sol client. La relació N:M necessita una taula intermèdia. Cantidad va a la taula intermèdia perquè depèn de la parella comanda–producte.
Model físic: tipus de dades i restriccions
| Dada | Tipus |
|---|---|
| IDs, estoc, any | INT |
| Preus i imports | DECIMAL(10,2) |
| Noms, correus, telèfons | VARCHAR(n) |
| Codis de mida fixa (codi postal) | CHAR(n) |
| Dates | DATE |
Si no fas càlculs amb un número, guarda'l com a text. Amb INT, el codi postal 08001 quedaria 8001.
Crea primer les taules sense FK i després les que hi apunten.
El model físic tria com es guarda cada columna en un SGBD concret. A l'assignatura fem servir MariaDB (compatible amb MySQL).
| Tipus | Per a què | Exemples |
|---|---|---|
INT | Nombres enters | IDs, Stock, Anio, Duracion |
VARCHAR(n) | Text de longitud variable, fins a n caràcters | Nombre, Email, Telefono |
CHAR(n) | Text de longitud fixa | Codigo CHAR(4), codi postal CHAR(5) |
DECIMAL(p,s) | Nombres amb decimals exactes | Importe, Precio DECIMAL(10,2) |
DATE | Dates | Fecha, FechaPrestamo |
BOOLEAN | Sí o no | Activo, Pagado |
| Restricció | Què fa | Exemple |
|---|---|---|
PRIMARY KEY | Identifica cada fila: única i mai nul·la | ClienteID INT PRIMARY KEY |
FOREIGN KEY | Apunta a la PK d'una altra taula | FOREIGN KEY (ClienteID) REFERENCES Clientes(ClienteID) |
NOT NULL | El camp és obligatori | Nombre VARCHAR(100) NOT NULL |
UNIQUE | No es pot repetir | Email VARCHAR(150) UNIQUE |
Ordre del CREATE TABLE: crea primer les taules sense FK (CLIENTES, PRODUCTOS) i després les que hi apunten (PEDIDOS, DETALLE_PEDIDO). En el procediment de creació dels exemples, les taules referenciades han d’existir quan es declaren les FK; també es poden afegir després amb ALTER TABLE.
Pregunta. Quin tipus de dades triaries per a un preu, un telèfon, un codi postal i una data de naixement? Justifica-ho.
Resposta model. Preu: DECIMAL(10,2), perquè té cèntims i cal exactitud. Telèfon: VARCHAR, perquè no s'hi fan càlculs i pot tenir el signe + o zeros inicials. Codi postal: CHAR(5), perquè té mida fixa i amb INT el 08001 quedaria 8001. Data de naixement: DATE. La regla és: si no fas càlculs amb un número, guarda'l com a text.
Cas pràctic: ShopSphere
Regles: un client fa moltes comandes. Cada comanda és d'un client. Una comanda té molts productes i un producte surt a moltes comandes. Cal guardar la quantitat.
Resultat: 4 taules. CLIENTES, PEDIDOS (amb la FK ClienteID), PRODUCTOS i la taula intermèdia DETALLE_PEDIDO (PedidoID + ProductoID com a PK, i Cantidad).
Regles del negoci:
Client → comandes
Un client pot fer diverses comandes.
Comanda → client
Cada comanda és d'un client.
Comanda → productes
Una comanda pot contenir diversos productes.
Producte → comandes
Un producte pot sortir a diverses comandes.
I cal guardar la quantitat de cada producte de cada comanda.
Quatre taules: tres entitats i una taula intermèdia per a la N:M.
CREATE TABLE Clientes (
ClienteID INT PRIMARY KEY,
Nombre VARCHAR(100) NOT NULL,
Email VARCHAR(150) UNIQUE,
Ciudad VARCHAR(50)
);
CREATE TABLE Productos (
ProductoID INT PRIMARY KEY,
Nombre VARCHAR(100) NOT NULL,
Precio DECIMAL(10,2) NOT NULL,
Stock INT
);
CREATE TABLE Pedidos (
PedidoID INT PRIMARY KEY,
Fecha DATE NOT NULL,
Importe DECIMAL(10,2),
ClienteID INT NOT NULL,
FOREIGN KEY (ClienteID)
REFERENCES Clientes(ClienteID)
);
CREATE TABLE Detalle_pedido (
PedidoID INT,
ProductoID INT,
Cantidad INT NOT NULL,
PRIMARY KEY (PedidoID, ProductoID),
FOREIGN KEY (PedidoID)
REFERENCES Pedidos(PedidoID),
FOREIGN KEY (ProductoID)
REFERENCES Productos(ProductoID)
);Com queden les dades
CLIENTES
| ID | Nombre |
|---|---|
| 1 | Ana |
| 2 | Carlos |
PEDIDOS
| ID | Fecha | ClienteID |
|---|---|---|
| 1001 | 03/02 | 1 |
| 1002 | 05/02 | 2 |
DETALLE_PEDIDO
| Pedido | Producto | Cant. |
|---|---|---|
| 1001 | 10 | 2 |
| 1001 | 11 | 3 |
| 1002 | 11 | 1 |
Per saber què ha comprat Ana: CLIENTES (Ana = 1) → PEDIDOS (ClienteID = 1 → 1001) → DETALLE_PEDIDO (1001 → productes 10 i 11). Cada pas segueix una FK.
- NegociNecessitats
- ConceptualEntitats + relacions + cardinalitats
- LògicTaules + atributs + PK + FK
- FísicTipus de dades + restriccions
- SQLImplementació
Activitat: UniLearn
Una plataforma de formació guarda estudiants, cursos, professors i matrícules.
- PROFESOR 1:N CURSO → la FK ProfesorID va a CURSOS.
- ESTUDIANTE N:M CURSO → taula MATRICULAS amb Fecha i Calificacion.
Resultat: 4 taules. La data i la nota van a MATRICULAS perquè depenen de la parella estudiant–curs.
Una plataforma de formació en línia guarda estudiants, cursos, professors i matrícules. Fes el model pas a pas:
Entitats: ESTUDIANTE, CURSO, PROFESOR.
Relacions:
- PROFESOR 1 ── imparte ── N CURSO: un professor fa molts cursos, cada curs té un professor.
- ESTUDIANTE N ── se matricula ── M CURSO, amb els atributs Fecha i Calificacion a la relació.
| Taula | PK | FK | Altres |
|---|---|---|---|
| PROFESORES | ProfesorID | — | Nombre, Email |
| CURSOS | CursoID | ProfesorID | Titulo, Horas |
| ESTUDIANTES | EstudianteID | — | Nombre, Email |
| MATRICULAS | EstudianteID + CursoID | EstudianteID, CursoID | Fecha, Calificacion |
La data i la nota depenen de la parella estudiant–curs: van a MATRICULAS. Resultat: 4 taules.
Exercicis resolts
Cinc exercicis amb solució: departament i empleats (1:N i 1:1 alhora), biblioteca (1:N), pel·lícules i actors (N:M amb el personatge), projectes i empleats (N:M amb les hores) i la carta d'un restaurant (1:N i N:M amb la quantitat).
Fes-los sempre en el mateix ordre: entitats i atributs → relacions i cardinalitats → taules → tipus de dades.
Fes cada exercici amb el mateix ordre: enunciat → entitats i atributs → relacions i cardinalitats → taules → tipus de dades. Després obre la solució.
Una empresa té departaments (codi i nom). Cada empleat pertany a un únic departament. De cada empleat es guarda un identificador, el nom, la categoria i la dedicació. Un dels empleats del departament en pot ser el director.
Hi ha dues relacions entre les mateixes entitats:
Per què DepartamentoCodigo va a EMPLEADOS? És el costat N: cada empleat té un sol departament.
Per què DirectorID va a DEPARTAMENTOS? Cada departament té un sol director. Amb UNIQUE, un empleat no pot dirigir dos departaments.
Tipus a MariaDB: Codigo CHAR(4) · Nombre VARCHAR(100) · EmpleadoID INT · Categoria VARCHAR(50) · Dedicacion DECIMAL(4,1) (hores) o VARCHAR(20) (completa/parcial).
CREATE TABLE Departamentos (
Codigo CHAR(4) PRIMARY KEY,
Nombre VARCHAR(100) NOT NULL,
DirectorID INT UNIQUE
);
CREATE TABLE Empleados (
EmpleadoID INT PRIMARY KEY,
Nombre VARCHAR(100) NOT NULL,
Categoria VARCHAR(50),
Dedicacion DECIMAL(4,1),
DepartamentoCodigo CHAR(4) NOT NULL,
FOREIGN KEY (DepartamentoCodigo)
REFERENCES Departamentos(Codigo)
);
-- La FK del director s'afegeix al final:
-- quan es crea Departamentos, Empleados encara no existeix.
ALTER TABLE Departamentos
ADD FOREIGN KEY (DirectorID)
REFERENCES Empleados(EmpleadoID);Una biblioteca guarda els socis (identificador, nom, email i telèfon) i els préstecs (identificador, data de préstec i data prevista de devolució). Un soci pot fer molts préstecs, però cada préstec és d'un únic soci.
SocioID va a PRESTAMOS perquè és el costat N: un soci, molts préstecs.
Tipus: IDs INT · Nombre VARCHAR(100) · Email VARCHAR(150) UNIQUE · Telefono VARCHAR(15) (pot tenir + i zeros inicials) · dates DATE.
CREATE TABLE Socios (
SocioID INT PRIMARY KEY,
Nombre VARCHAR(100) NOT NULL,
Email VARCHAR(150) UNIQUE,
Telefono VARCHAR(15)
);
CREATE TABLE Prestamos (
PrestamoID INT PRIMARY KEY,
FechaPrestamo DATE NOT NULL,
FechaDevolucion DATE,
SocioID INT NOT NULL,
FOREIGN KEY (SocioID) REFERENCES Socios(SocioID)
);Una plataforma guarda pel·lícules (identificador, títol, any i durada) i actors (identificador, nom i nacionalitat). Una pel·lícula té molts actors i un actor surt a moltes pel·lícules. També cal guardar el personatge que fa cada actor a cada pel·lícula.
Per què no n'hi ha prou amb una FK? Una pel·lícula hauria de guardar molts ActorID en una sola cel·la, i això trenca l'atomicitat.
PK de REPARTO: (PeliculaID, ActorID). Tipus: Titulo VARCHAR(200) · Anio INT · Duracion INT (minuts) · Personaje VARCHAR(100).
CREATE TABLE Reparto (
PeliculaID INT,
ActorID INT,
Personaje VARCHAR(100),
PRIMARY KEY (PeliculaID, ActorID),
FOREIGN KEY (PeliculaID) REFERENCES Peliculas(PeliculaID),
FOREIGN KEY (ActorID) REFERENCES Actores(ActorID)
);Una empresa guarda els empleats (identificador, nom i categoria) i els projectes (codi, nom i data d'inici). Un empleat participa en diversos projectes i en cada projecte hi treballen diversos empleats. Cal guardar les hores que cada empleat dedica a cada projecte.
On van les hores? A PARTICIPA. No són de l'empleat (treballa en diversos projectes) ni del projecte (hi treballen diversos empleats). Depenen de la parella.
Tipus: EmpleadoID INT · Codigo VARCHAR(10) · FechaInicio DATE · Horas DECIMAL(6,2).
CREATE TABLE Participa (
EmpleadoID INT,
CodigoProyecto VARCHAR(10),
Horas DECIMAL(6,2),
PRIMARY KEY (EmpleadoID, CodigoProyecto),
FOREIGN KEY (EmpleadoID) REFERENCES Empleados(EmpleadoID),
FOREIGN KEY (CodigoProyecto) REFERENCES Proyectos(Codigo)
);Un restaurant vol guardar la seva carta. De cada plat: nom, descripció, nivell de dificultat, foto i preu. Cada plat és d'una categoria (entrants, principals, postres…), amb nom, descripció i nom de l'encarregat. Cal saber la recepta de cada plat: quins ingredients porta i quanta quantitat de cadascun. De cada ingredient: nom, unitat de mesura i quantitat disponible al magatzem. Un ingredient surt a molts plats.
A · Model conceptual
- Entitats: PLATO, CATEGORIA i INGREDIENTE. L'encarregat només és un nom: és un atribut de CATEGORIA.
- PK: cap atribut és únic i estable (el nom pot canviar), així que crea un identificador: PlatoID, CategoriaID, IngredienteID.
- Relacions: categoria–plat 1:N (una categoria, molts plats · un plat, una categoria). Plat–ingredient N:M (un plat porta molts ingredients · un ingredient surt a molts plats).
- On va la quantitat de la recepta? A la relació PLATO–INGREDIENTE. No pot anar a PLATO: un plat porta diversos ingredients, cadascun amb una quantitat. No pot anar a INGREDIENTE: el mateix ingredient en fa servir quantitats diferents a cada plat. Depèn de la parella.
B · Model lògic
- 4 taules: tres entitats i la intermèdia RECETAS.
- On va la FK plat–categoria? A PLATOS (CategoriaID). És una 1:N i la FK va al costat N. Si la posessis a CATEGORIAS, una categoria hauria de guardar molts plats en una cel·la.
- Taula intermèdia: RECETAS, amb PlatoID i IngredienteID. Juntes formen la PK: un plat no pot portar el mateix ingredient dues vegades.
C · Model físic
| Columna | Tipus MariaDB | Per què |
|---|---|---|
| Precio | DECIMAL(6,2) | Diners: decimals exactes. Mai FLOAT. |
| Foto | VARCHAR(255) | Es guarda la ruta o l'URL de la imatge. Un BLOB guardaria la imatge dins la base de dades: possible, però pesa molt. |
| Dificultad | TINYINT | Un nivell de l'1 al 5 (amb CHECK). També val ENUM('Baja','Media','Alta'). |
| Cantidad · CantidadDisponible | DECIMAL(8,2) | Pot tenir decimals: 0,25 l o 150,5 g. |
| Unidad | VARCHAR(20) | Text curt: gramos, litros, unidades. |
| Descripcion | VARCHAR(500) o TEXT | Text llarg. |
| IDs | INT | La FK té el mateix tipus que la PK a què apunta. |
CREATE TABLE Recetas (
PlatoID INT,
IngredienteID INT,
Cantidad DECIMAL(8,2) NOT NULL,
PRIMARY KEY (PlatoID, IngredienteID),
FOREIGN KEY (PlatoID) REFERENCES Platos(PlatoID),
FOREIGN KEY (IngredienteID) REFERENCES Ingredientes(IngredienteID)
);Crea RECETAS al final: les seves FK apunten a PLATOS i INGREDIENTES, que han d'existir abans.
Practica: fes tu el model
Set exercicis interactius, de fàcil a difícil: taller mecànic, clínica veterinària, gimnàs, empresa i aparcament, curses populars, app de música i carta d'un restaurant. A cada pas escrius la resposta i la comproves.
Set exercicis. Els sis primers van de més fàcil a més difícil. El G és l'exercici de pissarra del restaurant. A cada pas escrius tu la resposta: entitats, atributs, identificadors, relacions, cardinalitats, PK, FK i tipus de dades. Prem Comprova per veure què està bé. Prem Mostra la solució per veure la resposta i el perquè.
Com escriure-ho: separa els elements amb comes. Pots escriure en català o en castellà, en singular o en plural: client, clientes i CLIENTE compten igual. Les respostes es desen en aquest navegador.
Cas guiat: floristeria online
Entitats: CLIENTES, PEDIDOS, FLORES i ESPECIES.
- Client–comanda: 1:N. Espècie–flor: 1:N.
- Comanda–flor: N:M → taula DETALLE_PEDIDO amb Cantidad.
LIKE busca text, no significat: '%roja%' no troba «Clavel rojo». Si vols consultar el color, guarda'l en una columna pròpia. DISTINCT evita repetir una comanda que té dues roses.
L'activitat de classe uneix model conceptual, model lògic, tipus de dades i consultes LIKE. Aquí tens una resolució amb noms simplificats.
- EntitatsCLIENTES, PEDIDOS, FLORES i ESPECIES tenen dades pròpies.
- Relacions 1:NUn client fa comandes; una espècie agrupa flors. Les FK van a PEDIDOS i FLORES.
- Relació N:MUna comanda inclou flors; una flor apareix en comandes. Es crea DETALLE_PEDIDO.
- Atribut de relacióCantidad és del detall: la mateixa flor pot tenir quantitats diferents segons la comanda.
- FísicDECIMAL per imports, DATE per dates, VARCHAR per telèfon; PK i FK per preservar els vincles.
Supòsit: cada flor surt un sol cop per comanda. Si compres 3 roses, la línia diu Cantidad = 3. Per això la parella (PedidoID, FlorID) pot ser la PK.
Sis consultes de l'activitat
S'apliquen al model simplificat anterior. La fitxa demana introduir almenys 3 espècies, 8 flors, 3 clients, 4 comandes i 8 línies. El nom de la flor inclou el color en el punt de partida de l'exercici.
-- 11. El nom comença per Rosa.
SELECT FlorID, Nombre FROM FLORES
WHERE Nombre LIKE 'Rosa%';
-- 12. Flors amb el fragment de text blanca.
SELECT FlorID, Nombre FROM FLORES
WHERE Nombre LIKE '%blanca%';
-- 13. El nom no comença per Rosa.
SELECT FlorID, Nombre FROM FLORES
WHERE Nombre NOT LIKE 'Rosa%';
-- 14. El nom complet té exactament cinc caràcters.
SELECT FlorID, Nombre FROM FLORES
WHERE Nombre LIKE '_____';
-- 15. Comandes amb alguna rosa, sense repetir la comanda.
SELECT DISTINCT p.PedidoID, p.Fecha
FROM PEDIDOS AS p
INNER JOIN DETALLE_PEDIDO AS d ON d.PedidoID = p.PedidoID
INNER JOIN FLORES AS f ON f.FlorID = d.FlorID
WHERE f.Nombre LIKE 'Rosa%';
-- 16. Recompte de registres amb roja al nom.
SELECT COUNT(*) AS RegistrosConRoja
FROM FLORES
WHERE Nombre LIKE '%roja%';Com interpretar-les: LIKE no sap què és una espècie ni un color. 'Rosa%' també pot trobar un nom que només comenci per aquelles lletres. Cinc guions baixos compten cinc caràcters, incloent espais; no «cinc lletres del nom sense color». I COUNT amb asterisc a FLORES compta registres del catàleg, no unitats venudes.
Una Rosa roja coincideix amb '%roja%', però un Clavel rojo també és vermell i no hi coincideix. És un exemple concret del límit de buscar significat dins del nom.
Per respondre «quantes flors són vermelles?» amb garanties, afegeix Color com a dada explícita i consulta Color = 'roja', si aquesta és l'etiqueta acordada. Si una flor pot tenir diversos colors, modela una relació N:M amb COLORS. Per separar roses d'altres espècies, filtra per l'espècie identificada, no per una coincidència al nom.
Per què DISTINCT? Si una comanda té Rosa roja i Rosa blanca, el JOIN produeix dues línies. DISTINCT deixa un sol parell (PedidoID, Fecha).
Cardinalitat, obligatorietat i història
- Cardinalitat (màxim): quants elements es poden relacionar. Un client, moltes comandes.
- Participació (mínim): si la relació és obligatòria. Una comanda ha de tenir client → FK
NOT NULL. Un client pot no tenir comandes.
El preu actual i el preu d'una venda són dades diferents. El preu del catàleg pot canviar. El preu cobrat en una comanda antiga no ha de canviar. Per això el preu de venda es guarda a la línia de comanda.
La cardinalitat màxima 1:N no explica tota sola si la participació és obligatòria. Un client pot tenir zero comandes i cada comanda pot requerir exactament un client. En aquest cas ClienteID va a PEDIDOS com a FK NOT NULL. Si admetem compres sense client registrat, cal definir com les representa el sistema.
La taula intermèdia identifica una ocurrència de la relació. La parella de FK pot ser-ne la PK quan no es pot repetir; un identificador artificial també és possible, però cal una restricció UNIQUE sobre la parella si el negoci prohibeix duplicats.
Pregunta. Per què no n'hi ha prou de guardar el preu a la taula PRODUCTOS? On guardaries el preu que va pagar el client?
Resposta model. El preu del catàleg pot canviar. Si només el guardo a PRODUCTOS, quan canviï es modificaran també els imports de les comandes antigues, que ja no serien certs. El preu actual i el preu d'una venda són dos fets diferents. El preu cobrat es guarda a la línia de comanda (DETALLE_PEDIDO), al costat de la quantitat.
Com respondre a l'examen
Set passos: llegeix i subratlla → entitats i atributs → identificadors → relacions i cardinalitats → diagrama ER → taules → tipus de dades.
Justifica sempre on va cada FK i cada atribut de relació. Exemple: «La FK va a PEDIDOS perquè és el costat N: cada comanda té un sol client.»
- Llegeix l'enunciatSubratlla els substantius i els verbs.
- Entitats i atributsUna llista per entitat. Marca l'identificador.
- Relacions i cardinalitatsPregunta en els dos sentits i escriu 1:1, 1:N o N:M.
- Diagrama ERRectangles, rombes, el·lipses i les cardinalitats a les línies.
- Model lògicUna taula per entitat. FK al costat N. Taula intermèdia per a cada N:M.
- JustificaExplica on va cada FK i cada atribut de relació.
- Model físicTipus de dades i restriccions per a cada columna.
Frases per justificar:
- «La FK va a PEDIDOS perquè és el costat N: cada comanda té un sol client.»
- «Cal una taula intermèdia perquè la relació és N:M: una sola FK obligaria a guardar diversos valors en una cel·la.»
- «La quantitat va a DETALLE_PEDIDO perquè depèn de la parella comanda–producte.»
- «Telefono és VARCHAR perquè pot tenir + i zeros inicials, i no se'n fan càlculs.»
Connexions amb els altres temes
| Tema | Connexió |
|---|---|
| T2 | El model lògic es converteix en CREATE TABLE. Les relacions es consulten amb JOIN. |
| T4 | Un bon model ER ja dona taules gairebé normalitzades. La normalització ho comprova. |
| T1 | Sense model apareixen la duplicació i la inconsistència dels fitxers plans. |
Errors típics d'examen
Els errors més freqüents: FK al costat 1, N:M sense taula intermèdia, la quantitat a PRODUCTOS, tipus de dades al model conceptual, 1:1 sense UNIQUE i telèfons com a INT.
FK al costat equivocat
En una 1:N, la FK va sempre al costat N. Mai al costat 1.
N:M amb una FK
Una N:M necessita sempre una taula intermèdia.
Atribut de la relació mal situat
Quantitat, hores, nota o personatge van a la taula intermèdia.
PK de la intermèdia
Normalment és la combinació de les dues FK, no una sola.
Tipus de dades al conceptual
VARCHAR o INT són del físic. El conceptual no depèn de cap SGBD.
Cada substantiu, una entitat
No sempre: alguns substantius són atributs.
Relació amb nom de substantiu
Les relacions es nomenen amb verbs: realiza, contiene, imparte.
Cardinalitat en un sol sentit
Pregunta-ho en els dos sentits abans de decidir.
1:1 sense UNIQUE
Sense UNIQUE a la FK, la relació es converteix en 1:N.
Telèfon o codi postal com a INT
Es perden els zeros inicials i el +. Fes servir VARCHAR.
Xuleta
| Et demanen… | Resposta |
|---|---|
| Què necessita guardar el negoci | Model conceptual |
| Taules, PK i FK | Model lògic |
| Tipus de dades, restriccions, SGBD | Model físic |
| Entitat | → Taula |
| Atribut | → Columna |
| Identificador | → Clau primària |
| Relació 1:N | PK del costat 1 → FK al costat N |
| Relació N:M | Taula intermèdia amb dues FK (juntes, PK) |
| Relació 1:1 | FK a una de les dues taules, amb UNIQUE |
| Atribut que depèn de la parella | A la taula intermèdia |
| Rectangle · rombe · el·lipse | Entitat · relació · atribut |
| Imports i preus | DECIMAL(10,2) |
| Telèfon, codi postal | VARCHAR (text) |
| Ordre dels CREATE TABLE | Primer les taules sense FK |
02. Test
13 fàcils, 14 mitjanes i 13 difícils. L'ordre canvia cada vegada.
03. Targetes
Toca la targeta per girar-la i marca si te la saps. El progrés queda desat.