Emmagatzematge Dades · Tema 3

Modelatge de dades

Models conceptual, lògic i físic, diagrames entitat-relació i pas al model relacional

53 min7 min 16 seccions 40 preguntes 32 targetes
4 fonts originals
T3.pdfExercicisModeladoBases.pdfEjercicio_pizarra_Tema3_Restaurante.pdfActividad_aula_Tema3_Floristeria.pdf

01. Resum

16 seccions · 53 min7 min
Només l'essencial: sense explicacions llargues, exemples ni exercicis.Amb explicacions, exemples i exercicis resolts.

Comença per aquí

Si només recordes cinc coses d'aquest tema:

  1. Modelar és decidir què cal guardar i com es relaciona, abans de crear cap taula.
  2. Tres nivells: conceptual (què necessita el negoci), lògic (taules, PK i FK) i físic (tipus de dades i SGBD).
  3. Diagrama ER: rectangle = entitat, rombe = relació, el·lipse = atribut. La cardinalitat es pregunta en els dos sentits.
  4. Transformació: 1:N → FK al costat N. N:M → taula intermèdia amb les dues FK com a PK. 1:1 → FK amb UNIQUE.
  5. 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ó.

ClientsQui compra
ComandesQuè compren i quan
ProductesEl catàleg
PagamentsCom paguen

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.

  1. Necessitats del negociQuè ha de gestionar l'empresa
  2. Model de dadesConceptual → lògic → físic
  3. Base de dadesImplementació en SQL

Tres nivells: conceptual, lògic i físic

El disseny té tres nivells. Cada un afegeix detall:

NivellPreguntaQuè hi veus
ConceptualQuè necessita el negoci?Entitats, atributs, relacions, cardinalitats
LògicOn va cada dada?Taules, columnes, PK, FK
FísicAmb 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

1 · Conceptual
EntitatRelacióAtributSubratllat = identificador

Un client pot fer moltes comandes. Cada comanda és d'un sol client.

2 · Lògic
CLIENTEClienteIDPKNombreEmail
PEDIDOPedidoIDPKFechaImporteClienteIDFK→ CLIENTE

La relació conceptual es converteix en una relació entre taules mitjançant claus.

3 · Físic
CampTipusRestricció
ClienteIDINTPRIMARY KEY
NombreVARCHAR(100)NOT NULL
EmailVARCHAR(150)UNIQUE
PedidoIDINTPRIMARY KEY
FechaDATENOT NULL
ImporteDECIMAL(10,2)—
ClienteIDINTFOREIGN KEY
ModelRespon aElements principals
ConceptualQuina informació necessitem?Entitats i relacions
LògicCom organitzem les dades?Taules, atributs, PK, FK
FísicCom ho implementem?Tipus de dades, restriccions, SGBD
Pregunta d'examen · Tres nivells

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:

CLIENTE realiza PEDIDO, en notació de Chen
EntitatRelacióAtributSubratllat = identificador

▭ 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 requisitQuè és?Per què
clientsEntitat → CLIENTEEn guardem dades pròpies
comandesEntitat → PEDIDOEn guardem dades pròpies
productesEntitat → PRODUCTOEn guardem dades pròpies
fanRelació → realizaUn verb que uneix client i comanda
catàlegResÉs el conjunt de productes, no una cosa nova
nom del clientAtributDescriu 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 → BPregunta B → ACardinalitat
UnUn1:1
MoltsUn1:N
MoltsMoltsN:M
Per anar més lluny: participació obligatòria o opcional

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

  1. EntitatsIdentifica-les als requisits: els substantius rellevants.
  2. AtributsDecideix què cal guardar de cada entitat.
  3. IdentificadorsTria l'atribut que identifica cada ocurrència: serà la PK.
  4. RelacionsBusca els verbs que associen les entitats.
  5. CardinalitatsPregunta en els dos sentits: 1:1, 1:N o N:M.
  6. RevisióComprova que el model respon a tots els requisits.
Pregunta d'examen · Cardinalitat

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:

  1. EntitatTaula
  2. AtributColumna
  3. IdentificadorClau primària
  4. 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.

CLIENTESClienteIDPKNombreEmailCiudad
ClienteIDNombreEmailCiudad
1Ana Lópezana@mail.catBarcelona
2Carlos Ruizcarlos@mail.catMadrid

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).

CLIENTESClienteIDPKNombreEmail
PEDIDOSPedidoIDPKFechaImporteClienteIDFK→ CLIENTES

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:

EntitatRelacióAtributSubratllat = identificador
Model lògic: tres taules
PEDIDOSPedidoIDPKFechaImporte
DETALLE_PEDIDOPedidoIDPK, FK→ PEDIDOSProductoIDPK, FK→ PRODUCTOSCantidad
PRODUCTOSProductoIDPKNombrePrecio

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:

PedidoIDProductoIDCantidad
100110 · Ordinador2
100111 · Teclat3
100211 · Teclat1

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.

PERSONASPersonaIDPKNombre
PASAPORTESPasaporteIDPKFechaExpPersonaIDFK→ PERSONAS

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 d'examen · D'ER a taules

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

DadaTipus
IDs, estoc, anyINT
Preus i importsDECIMAL(10,2)
Noms, correus, telèfonsVARCHAR(n)
Codis de mida fixa (codi postal)CHAR(n)
DatesDATE

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).

TipusPer a quèExemples
INTNombres entersIDs, Stock, Anio, Duracion
VARCHAR(n)Text de longitud variable, fins a n caràctersNombre, Email, Telefono
CHAR(n)Text de longitud fixaCodigo CHAR(4), codi postal CHAR(5)
DECIMAL(p,s)Nombres amb decimals exactesImporte, Precio DECIMAL(10,2)
DATEDatesFecha, FechaPrestamo
BOOLEANSí o noActivo, Pagado
RestriccióQuè faExemple
PRIMARY KEYIdentifica cada fila: única i mai nul·laClienteID INT PRIMARY KEY
FOREIGN KEYApunta a la PK d'una altra taulaFOREIGN KEY (ClienteID) REFERENCES Clientes(ClienteID)
NOT NULLEl camp és obligatoriNombre VARCHAR(100) NOT NULL
UNIQUENo es pot repetirEmail 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 d'examen · Tipus de dades

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.

1 · Conceptual
EntitatRelacióAtributSubratllat = identificador
2 · Lògic
CLIENTESClienteIDPKNombreEmailCiudad
PEDIDOSPedidoIDPKFechaImporteClienteIDFK→ CLIENTES
DETALLE_PEDIDOPedidoIDPK, FK→ PEDIDOSProductoIDPK, FK→ PRODUCTOSCantidad
PRODUCTOSProductoIDPKNombrePrecioStock

Quatre taules: tres entitats i una taula intermèdia per a la N:M.

3 · Físic (SQL)
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

IDNombre
1Ana
2Carlos

PEDIDOS

IDFechaClienteID
100103/021
100205/022

DETALLE_PEDIDO

PedidoProductoCant.
1001102
1001113
1002111

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.

Del negoci a la base de dades
  1. NegociNecessitats
  2. ConceptualEntitats + relacions + cardinalitats
  3. LògicTaules + atributs + PK + FK
  4. FísicTipus de dades + restriccions
  5. 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:

Solució resumida d'UniLearn

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ó.
TaulaPKFKAltres
PROFESORESProfesorID—Nombre, Email
CURSOSCursoIDProfesorIDTitulo, Horas
ESTUDIANTESEstudianteID—Nombre, Email
MATRICULASEstudianteID + CursoIDEstudianteID, CursoIDFecha, 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ó.

Exercici 1 · Departament i empleats

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:

EntitatRelacióAtributSubratllat = identificador
DEPARTAMENTOSCodigoPKNombreDirectorIDFK→ EMPLEADOS
EMPLEADOSEmpleadoIDPKNombreCategoriaDedicacionDepartamentoCodigoFK→ DEPARTAMENTOS

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);
Exercici 2 · Biblioteca

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.

EntitatRelacióAtributSubratllat = identificador
SOCIOSSocioIDPKNombreEmailTelefono
PRESTAMOSPrestamoIDPKFechaPrestamoFechaDevolucionSocioIDFK→ SOCIOS

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)
);
Exercici 3 · Pel·lícules i actors

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.

EntitatRelacióAtributSubratllat = identificador

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.

PELICULASPeliculaIDPKTituloAnioDuracion
REPARTOPeliculaIDPK, FK→ PELICULASActorIDPK, FK→ ACTORESPersonaje
ACTORESActorIDPKNombreNacionalidad

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)
);
Exercici 4 · Projectes i empleats

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.

EntitatRelacióAtributSubratllat = identificador
EMPLEADOSEmpleadoIDPKNombreCategoria
PARTICIPAEmpleadoIDPK, FK→ EMPLEADOSCodigoProyectoPK, FK→ PROYECTOSHoras
PROYECTOSCodigoPKNombreFechaInicio

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)
);
Exercici 5 · La carta d'un restaurant (pissarra)

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.

EntitatRelacióAtributSubratllat = identificador

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.
CATEGORIASCategoriaIDPKNombreDescripcionEncargado
PLATOSPlatoIDPKNombreDescripcionDificultadFotoPrecioCategoriaIDFK→ CATEGORIAS
RECETASPlatoIDPK, FK→ PLATOSIngredienteIDPK, FK→ INGREDIENTESCantidad
INGREDIENTESIngredienteIDPKNombreUnidadCantidadDisponible

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

ColumnaTipus MariaDBPer què
PrecioDECIMAL(6,2)Diners: decimals exactes. Mai FLOAT.
FotoVARCHAR(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.
DificultadTINYINTUn nivell de l'1 al 5 (amb CHECK). També val ENUM('Baja','Media','Alta').
Cantidad · CantidadDisponibleDECIMAL(8,2)Pot tenir decimals: 0,25 l o 150,5 g.
UnidadVARCHAR(20)Text curt: gramos, litros, unidades.
DescripcionVARCHAR(500) o TEXTText llarg.
IDsINTLa 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.

Del text al model
  1. EntitatsCLIENTES, PEDIDOS, FLORES i ESPECIES tenen dades pròpies.
  2. Relacions 1:NUn client fa comandes; una espècie agrupa flors. Les FK van a PEDIDOS i FLORES.
  3. Relació N:MUna comanda inclou flors; una flor apareix en comandes. Es crea DETALLE_PEDIDO.
  4. Atribut de relacióCantidad és del detall: la mateixa flor pot tenir quantitats diferents segons la comanda.
  5. FísicDECIMAL per imports, DATE per dates, VARCHAR per telèfon; PK i FK per preservar els vincles.
Model lògic de la floristeria
CLIENTESClienteIDPKNombreEmailTelefono
ESPECIESEspecieIDPKNombreEpocaFloracionTipoSueloExposicion
FLORESFlorIDPKNombrePrecioEspecieIDFK→ ESPECIES
PEDIDOSPedidoIDPKFechaImporteTotalClienteIDFK→ CLIENTES
DETALLE_PEDIDOPedidoIDPK, FK→ PEDIDOSFlorIDPK, FK→ FLORESCantidad

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 d'examen · Preu actual i preu de venda

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.»

Els passos de cada exercici
  1. Llegeix l'enunciatSubratlla els substantius i els verbs.
  2. Entitats i atributsUna llista per entitat. Marca l'identificador.
  3. Relacions i cardinalitatsPregunta en els dos sentits i escriu 1:1, 1:N o N:M.
  4. Diagrama ERRectangles, rombes, el·lipses i les cardinalitats a les línies.
  5. Model lògicUna taula per entitat. FK al costat N. Taula intermèdia per a cada N:M.
  6. JustificaExplica on va cada FK i cada atribut de relació.
  7. 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.»
1:N2 taules · 1 FK
N:M3 taules · 2 FK
1:12 taules · 1 FK + UNIQUE

Connexions amb els altres temes

TemaConnexió
T2El model lògic es converteix en CREATE TABLE. Les relacions es consulten amb JOIN.
T4Un bon model ER ja dona taules gairebé normalitzades. La normalització ho comprova.
T1Sense 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 negociModel conceptual
Taules, PK i FKModel lògic
Tipus de dades, restriccions, SGBDModel físic
Entitat→ Taula
Atribut→ Columna
Identificador→ Clau primària
Relació 1:NPK del costat 1 → FK al costat N
Relació N:MTaula intermèdia amb dues FK (juntes, PK)
Relació 1:1FK a una de les dues taules, amb UNIQUE
Atribut que depèn de la parellaA la taula intermèdia
Rectangle · rombe · el·lipseEntitat · relació · atribut
Imports i preusDECIMAL(10,2)
Telèfon, codi postalVARCHAR (text)
Ordre dels CREATE TABLEPrimer les taules sense FK

02. Test

40 preguntes

13 fàcils, 14 mitjanes i 13 difícils. L'ordre canvia cada vegada.

03. Targetes

32 targetes

Toca la targeta per girar-la i marca si te la saps. El progrés queda desat.