Emmagatzematge Dades · Tema 4

Normalització de bases de dades

Dependències funcionals, anomalies i pas a 1FN, 2FN i 3FN amb ShopSphere i UniLearn

22 min6 min 15 seccions 40 preguntes 24 targetes
2 fonts originals
T4.pdfPractica2/Practica2_Tema3_Tema4_Tienda_WooCommerce_porgrupo.pdf

01. Resum

15 seccions · 22 min6 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. La redundància provoca tres anomalies: d'inserció, d'actualització i d'eliminació.
  2. Dependència funcional A → B: si saps A, saps B. Surt de les regles del negoci, no de les dades d'exemple.
  3. 1FN: un sol valor per cel·la i cap grup repetitiu.
  4. 2FN: 1FN i cap atribut depèn només d'una part de la clau composta (dependència parcial).
  5. 3FN: 2FN i cap atribut no clau depèn d'un altre atribut no clau (dependència transitiva). La clau, tota la clau i res més que la clau.

A la versió completa, cada apartat acaba amb una pregunta d'examen i la seva resposta model.

La idea en un minut

Normalitzar és posar cada dada a la taula que li correspon. Així no es repeteix i no es contradiu. El tema 3 crea les taules. El tema 4 comprova que cada atribut és al lloc bo.

  1. Regles del negociQuè determina què
  2. ClausQuè identifica cada fila
  3. DependènciesCompletes, parcials i transitives
  4. 1FN → 2FN → 3FNSeparar taules pas a pas
  5. RevisióLes FK permeten tornar a unir-ho tot

Normalitzar no és crear moltes taules. Cada separació necessita un motiu. Les claus han de permetre reconstruir la informació.

Redundància i tres anomalies

Quan una taula repeteix dades, apareixen tres problemes (IAE):

  • Inserció: no pots guardar un client sense inventar una comanda.
  • Actualització: has de corregir el mateix canvi en moltes files.
  • Eliminació: esborres l'única comanda d'un client i perds les dades del client.

Repetir una FK en una relació 1:N és normal. El problema és repetir les dades que la FK ja identifica (el nom, la ciutat…).

Exemple: una taula repeteix el nom i la ciutat del client a cada comanda. L'Ana passa de Barcelona a Girona. Cal canviar moltes files. Si n'oblides una, l'Ana té dues ciutats.

PedidoIDClienteCiudadProducto
1001AnaBarcelonaPortàtil
1002AnaBarcelonaRatolí
1003AnaGironaTeclat

Quina és la ciutat bona? La taula no ho sap.

AnomaliaProblemaExemple
InsercióNo pots guardar una dada sense inventar-ne una altraRegistrar una clienta nova sense crear una comanda falsa
ActualitzacióEl mateix canvi s'ha de fer en diversos llocsCanviar la ciutat a totes les comandes
EliminacióEsborres una dada i en perds una altraEsborrar l'última comanda elimina les dades del client
Pregunta d'examen · Anomalies

Pregunta. Una taula única guarda PedidoID, Fecha, ClienteID, NombreCliente, Ciudad, ProductoID, NombreProducto i Cantidad. Explica les tres anomalies amb un exemple de cadascuna.

Resposta model. Inserció: no puc guardar un client nou fins que faci una comanda, perquè les seves dades viuen a la fila de la comanda. Actualització: si un client canvia de ciutat, he de corregir totes les files de les seves comandes; si me'n deixo una, les dades queden inconsistents. Eliminació: si esborro l'única comanda d'un client, perdo també el nom i la ciutat del client.

Dependències funcionals

A → B vol dir: si saps A, saps B. Dues files amb el mateix A sempre tenen el mateix B.

Exemples: ClienteID → Nombre, Ciudad · ProductoID → Precio · (PedidoID, ProductoID) → Cantidad.

La dependència surt de les regles del negoci, no de les dades d'exemple. I A → B no implica B → A: dos clients poden viure a la mateixa ciutat.

A → B vol dir: dues files amb el mateix A han de tenir el mateix B. A és el determinant.

ClienteID → Nombre, Ciudad

Un client té un nom i una ciutat.

PedidoID → Fecha, ClienteID

Una comanda té una data i un client.

ProductoID → NombreProducto, PrecioCatalogo

Un producte té un nom i un preu actual.

(PedidoID, ProductoID) → Cantidad

La quantitat depèn del producte dins d'una comanda.

Claus i tipus de dependència

Una clau candidata és el mínim d'atributs que identifica una fila. Una d'elles es tria com a PK. Si la clau té diversos atributs, és composta.

TipusQuè passaExemple
CompletaL'atribut depèn de tota la clau(PedidoID, ProductoID) → Cantidad
ParcialL'atribut depèn només d'una part de la clau composta(PedidoID, ProductoID): ProductoID → NombreProducto
TransitivaL'atribut depèn d'un altre atribut no clauClienteID → CodigoPostal → Ciudad
Troba les dependències

Taula PRESTECS (SociID, NomSoci, LlibreID, Titol, DataPrestec). Un soci pot agafar molts llibres. Un llibre es pot prestar a molts socis. Suposa que un soci no agafa el mateix llibre dues vegades. Quina és la clau? Quines dependències hi ha?

Solució
  • Clau: (SociID, LlibreID).
  • SociID → NomSoci (parcial).
  • LlibreID → Titol (parcial).
  • (SociID, LlibreID) → DataPrestec (completa).
Pregunta d'examen · Dependències funcionals

Pregunta. Què vol dir ClienteID → Ciudad? Vol dir també que Ciudad → ClienteID? D'on surten les dependències?

Resposta model. ClienteID → Ciudad vol dir que, si sé el client, sé la ciutat: dues files amb el mateix ClienteID tenen sempre la mateixa ciutat. No implica el contrari: dos clients diferents poden viure a la mateixa ciutat. Les dependències surten de les regles del negoci, no de les dades d'exemple: que en una mostra no hi hagi repetits no demostra res.

Primera forma normal: 1FN

Un sol valor per cel·la. Cap grup repetitiu.

MalamentBé
Productos = «Portàtil, ratolí, teclat»Una fila per producte
Producto1, Producto2, Producto3Una taula de línies

Complir 1FN no treu totes les repeticions. Per això cal revisar 2FN i 3FN.

Una taula està en 1FN quan:

  • cada cel·la té un sol valor,
  • no hi ha grups repetitius (columnes Telefono1, Telefono2…),
  • cada fila es pot identificar.

Abans: no és 1FN

PedidoIDProductos
1001Portàtil, ratolí, teclat

La cel·la té una llista. No pots dir quantes unitats hi ha de cada producte.

Després: 1FN

PedidoIDProductoIDCantidad
1001101
1001202
1001301

Una fila per producte de la comanda.

Producto1, Producto2 i Producto3 tampoc és bona solució. És un grup repetitiu: limita a tres productes i complica les consultes.

L'atomicitat depèn de l'ús. Si mai has de buscar pel cognom, el nom complet pot anar en una sola columna. Si has de buscar o ordenar per cognom, separa'ls.

1FN no treu totes les repeticions. Pots tenir una fila per línia i continuar repetint el nom del producte a cada línia. Per això existeixen 2FN i 3FN.

Segona forma normal: 2FN

1FN + cap atribut depèn només d'una part de la clau composta.

Exemple: clau (PedidoID, ProductoID). NombreProducto depèn només de ProductoID → dependència parcial → el traus a PRODUCTOS. Cantidad depèn de la parella → es queda.

Si la PK és simple (una sola columna), no hi pot haver dependències parcials respecte d'aquesta PK.

Una taula està en 2FN si està en 1FN i cap atribut no clau depèn només d'una part de la clau composta.

PedidoID · PKProductoID · PKNombreProductoPrecioCatalogoCantidad
100110Portàtil8991
100120Ratolí252
100210Portàtil8991
  • Cantidad depèn de (PedidoID, ProductoID): cal la parella. ✅
  • NombreProducto i PrecioCatalogo depenen només de ProductoID: dependència parcial. ❌
  • El nom «Portàtil» i el preu 899 es repeteixen.

La taula és 1FN però no 2FN. Separa:

Resultat en 2FN
PRODUCTOSProductoIDPKNombreProductoPrecioCatalogo
DETALLE_PEDIDOPedidoIDPKProductoIDPK, FK→ PRODUCTOSCantidad
Pregunta d'examen · 2FN

Pregunta. DETALLE (PedidoID, ProductoID, NombreProducto, Cantidad), amb clau (PedidoID, ProductoID). Està en 2FN? Normalitza-la.

Resposta model. No està en 2FN. NombreProducto depèn només de ProductoID, que és una part de la clau: és una dependència parcial. Cantidad depèn de la parella sencera i es queda. Resultat: PRODUCTOS (ProductoID PK, NombreProducto) i DETALLE_PEDIDO (PedidoID, ProductoID, Cantidad), on ProductoID queda com a clau forana.

Tercera forma normal: 3FN

2FN + cap atribut no clau depèn d'un altre atribut no clau (dependència transitiva).

Exemple: CLIENTES (ClienteID, Nombre, CodigoPostal, Ciudad). Si cada codi postal té una ciutat: ClienteID → CodigoPostal → Ciudad. Ciudad depèn de la clau a través de CodigoPostal. Separa CODIGOS_POSTALES (CodigoPostal, Ciudad).

Una taula està en 3FN si està en 2FN i cap atribut no clau depèn d'un altre atribut no clau.

Exemple dels apunts: CLIENTES (ClienteID, Nombre, CodigoPostal, Ciudad). Suposem que cada codi postal correspon a una sola ciutat.

  1. ClienteIDDetermina el codi postal
  2. CodigoPostalDetermina la ciutat
  3. CiudadDepèn de ClienteID a través del codi
ClienteIDNombreCodigoPostalCiudad
1Ana08001Barcelona
2Joan08001Barcelona
3Marta17001Girona

La parella 08001 – Barcelona es repeteix per a cada client d'aquell codi. Separa:

Resultat en 3FN
CODIGOS_POSTALESCodigoPostalPKCiudad
CLIENTESClienteIDPKNombreCodigoPostalFK→ CODIGOS_POSTALES

Aquest exemple funciona si el supòsit codi → ciutat és cert. En un sistema real, cal comprovar-ho.

Pregunta d'examen · 3FN

Pregunta. CLIENTES (ClienteID, Nombre, CodigoPostal, Ciudad). Cada codi postal correspon a una sola ciutat. Està en 3FN? Normalitza-la.

Resposta model. No està en 3FN. Hi ha una dependència transitiva: ClienteID → CodigoPostal → Ciudad. Ciudad depèn de la clau a través d'un atribut no clau. Resultat: CLIENTES (ClienteID PK, Nombre, CodigoPostal FK) i CODIGOS_POSTALES (CodigoPostal PK, Ciudad). La diferència amb la 2FN és que aquí l'atribut depèn d'un no clau, no d'una part de la clau.

ShopSphere: de la taula única a 3FN

Una taula única amb comandes, clients i productes es divideix en quatre:

  • CLIENTES (ClienteID, Nombre, Ciudad)
  • PEDIDOS (PedidoID, Fecha, ClienteID)
  • PRODUCTOS (ProductoID, NombreProducto, PrecioCatalogo)
  • DETALLE_PEDIDO (PedidoID, ProductoID, Cantidad)

El preu actual (PRODUCTOS) i el preu de venda d'una comanda passada (DETALLE_PEDIDO) són dues dades diferents.

Supòsits: cada comanda té un client. Un producte surt com a màxim una vegada per comanda. PrecioCatalogo és el preu actual.

Taula inicial, amb una fila per producte de cada comanda:

PedidoIDProductoIDFechaClienteIDClienteCiudadProductoPrecioCatalogoCantidad
1001102026-09-151AnaBarcelonaPortàtil8991
1001202026-09-151AnaBarcelonaRatolí252
1002102026-09-171AnaBarcelonaPortàtil8991
Resolució pas a pas
  1. 1. Clau i 1FNPK = (PedidoID, ProductoID). Els valors són atòmics: ja és 1FN.
  2. 2. Dependències parcialsPedidoID → Fecha, ClienteID, Cliente, Ciudad. ProductoID → Producto, PrecioCatalogo. Separa PEDIDOS i PRODUCTOS.
  3. 3. Dependència completa(PedidoID, ProductoID) → Cantidad. La quantitat es queda a DETALLE_PEDIDO.
  4. 4. Dependència transitivaA PEDIDOS: PedidoID → ClienteID → Cliente, Ciudad. Separa CLIENTES.
  5. 5. RevisióLes PK i FK connecten les quatre taules.
Resultat en 3FN
CLIENTESClienteIDPKNombreCiudad
PEDIDOSPedidoIDPKFechaClienteIDFK→ CLIENTES
PRODUCTOSProductoIDPKNombreProductoPrecioCatalogo
DETALLE_PEDIDOPedidoIDPK, FK→ PEDIDOSProductoIDPK, FK→ PRODUCTOSCantidad

Tornar a unir les dades

Separar no vol dir perdre informació. Un JOIN ho torna a ajuntar:

SELECT pe.PedidoID, pe.Fecha, c.Nombre,
       p.NombreProducto, d.Cantidad
FROM PEDIDOS AS pe
INNER JOIN CLIENTES AS c ON c.ClienteID = pe.ClienteID
INNER JOIN DETALLE_PEDIDO AS d ON d.PedidoID = pe.PedidoID
INNER JOIN PRODUCTOS AS p ON p.ProductoID = d.ProductoID;

Surt una fila per línia de comanda. El nom del client es repeteix al resultat, però a la base de dades només està guardat un cop.

Preu actual i preu de venda

Avui el portàtil costa 950 €. Una comanda antiga de 899 € no ha de canviar. Solució: afegeix PrecioVenta a DETALLE_PEDIDO i mantén PrecioCatalogo a PRODUCTOS.

  • ProductoID → PrecioCatalogo (el preu d'avui).
  • (PedidoID, ProductoID) → PrecioVenta (el preu d'aquella venda).

No és una dada duplicada: són dos fets diferents. Passa el mateix amb l'adreça de facturació d'una compra antiga i l'adreça actual del client.

UniLearn: exercici resolt

Taula: EstudianteID, Estudiante, CursoID, Curso, ProfesorID, Profesor, Nota.

  • Clau: (EstudianteID, CursoID).
  • 2FN: EstudianteID → Estudiante i CursoID → Curso, ProfesorID són parcials. Separa ESTUDIANTES i CURSOS.
  • 3FN: CursoID → ProfesorID → Profesor és transitiva. Separa PROFESORES.
  • Resultat: ESTUDIANTES, PROFESORES, CURSOS i MATRICULAS (amb la Nota).
Prova abans de mirar la solució

Taula: EstudianteID, Estudiante, CursoID, Curso, ProfesorID, Profesor, Nota. Cada curs té un professor. Cada estudiant es pot matricular en diversos cursos. La nota depèn de l'estudiant i del curs. Troba la clau, les dependències i les taules fins a 3FN.

Solució

Clau: (EstudianteID, CursoID). Suposem una sola matrícula per estudiant i curs.

Dependències:

  • EstudianteID → Estudiante (parcial)
  • CursoID → Curso, ProfesorID (parcial)
  • ProfesorID → Profesor (transitiva dins de CURSOS)
  • (EstudianteID, CursoID) → Nota (completa)

2FN: separa ESTUDIANTES i CURSOS. MATRICULAS conserva la parella i la nota.

3FN: a CURSOS hi ha CursoID → ProfesorID → Profesor. Treu el nom del professor a PROFESORES.

Si un estudiant pot repetir el mateix curs en anys diferents, cal afegir l'edició a la clau.

Model final d'UniLearn
ESTUDIANTESEstudianteIDPKNombre
PROFESORESProfesorIDPKNombre
CURSOSCursoIDPKNombreProfesorIDFK→ PROFESORES
MATRICULASEstudianteIDPK, FK→ ESTUDIANTESCursoIDPK, FK→ CURSOSNota
Normalitza tu: préstecs d'una biblioteca

Taula PRESTECS (SociID, NomSoci, TelefonSoci, LlibreID, Titol, EditorialID, NomEditorial, DataPrestec). Clau: (SociID, LlibreID). Cada llibre té una editorial. Porta-la a 3FN.

Solució
  • 1FN: ja ho és (valors atòmics).
  • 2FN: SociID → NomSoci, TelefonSoci (parcial) → taula SOCIS. LlibreID → Titol, EditorialID, NomEditorial (parcial) → taula LLIBRES.
  • 3FN: a LLIBRES, LlibreID → EditorialID → NomEditorial (transitiva) → taula EDITORIALS.
SOCISSociIDPKNomSociTelefonSoci
EDITORIALSEditorialIDPKNomEditorial
LLIBRESLlibreIDPKTitolEditorialIDFK→ EDITORIALS
PRESTECSSociIDPK, FK→ SOCISLlibreIDPK, FK→ LLIBRESDataPrestec
Pregunta d'examen · Exercici complet

Pregunta. Normalitza fins a 3FN: (EstudianteID, Estudiante, CursoID, Curso, ProfesorID, Profesor, Nota). Cada curs té un sol professor.

Resposta model. Clau: (EstudianteID, CursoID), perquè la nota depèn de la parella. 2FN: EstudianteID → Estudiante i CursoID → Curso, ProfesorID són dependències parcials; separo ESTUDIANTES i CURSOS. 3FN: CursoID → ProfesorID → Profesor és transitiva; separo PROFESORES. Resultat: ESTUDIANTES (EstudianteID, Estudiante), PROFESORES (ProfesorID, Profesor), CURSOS (CursoID, Curso, ProfesorID FK) i MATRICULAS (EstudianteID FK, CursoID FK, Nota).

Disseny real i pràctica de WooCommerce

A la pràctica, normalitzar es combina amb rendiment i manteniment. Si un disseny repeteix dades a propòsit, cal justificar-ho i dir com es mantenen coherents.

Exemples a WooCommerce: metadades en files clau–valor (wp_postmeta), dades històriques de la compra i taules de totals per anar més ràpid. Un producte en dues categories és una relació N:M.

Les diapositives demanen combinar normalització amb rendiment, volum, consultes i manteniment. Una excepció s'ha de justificar i documentar.

Què veus a la pràcticaCom raonar-ho
Metadades en files clau–valor (wp_postmeta)Donen flexibilitat. Compliquen tipus, validacions i consultes
Nom i cognoms separatsPermeten buscar i ordenar per cognom
Dades de la compra guardades a partRepresenten el moment de la compra, no la fitxa actual
Taules de totalsFan les consultes més ràpides. Cal explicar com s'actualitzen
Producte en dues categoriesRelació N:M: CATEGORIAS i PRODUCTO_CATEGORIA

No diguis «3FN» només mirant els noms de les columnes. Escriu les claus, les dependències i els supòsits.

La Pràctica 2

La fitxa demana:

  • explorar les taules i trobar dues relacions 1:N i dues N:M,
  • dibuixar l'ER i passar-lo a model lògic,
  • normalitzar una taula aplanada de quatre files,
  • fer consultes SQL amb captures,
  • preparar una exposició de 8–10 minuts.

Mínims de la botiga: 3 categories, 10 productes, 3 clients i 5 comandes, amb almenys un producte en dues categories.

Comprova a la teva base els noms i les claus reals. Una relació de l'aplicació no sempre té una FOREIGN KEY declarada al SGBD.

Com resoldre un exercici d'examen

Set passos: què és cada fila → clau i supòsits → dependències → 1FN → 2FN → 3FN → revisió.

Justifica cada separació amb el determinant. Exemple: «NombreProducto depèn de ProductoID, que només és una part de la clau. És una dependència parcial. El trasllado a PRODUCTOS i deixo ProductoID com a FK.»

Procediment
  1. 1. Què és cada fila?Una comanda, una línia o una matrícula?
  2. 2. Clau i supòsitsQuè fa única la fila? Es pot repetir un producte en una comanda?
  3. 3. DependènciesEscriu totes les A → B segons les regles del negoci.
  4. 4. 1FNUn valor per cel·la. Cap llista ni grup repetitiu.
  5. 5. 2FNSepara el que depèn d'una part de la clau composta.
  6. 6. 3FNSepara el que depèn d'un atribut no clau.
  7. 7. RevisióPK i FK. Pots tornar a unir-ho tot amb JOIN?

Frases per justificar:

  • «X depèn de Y, que és només una part de la clau. És una dependència parcial: X va a la taula de Y.»
  • «Z depèn de W, que no és clau. És una dependència transitiva: creo una taula amb W com a PK.»
  • «La quantitat depèn de la parella. Es queda a la taula intermèdia.»

Connexions amb els altres temes

TemaConnexió
T3El resultat de normalitzar és el mateix que el d'un bon model ER: una N:M acaba en una taula intermèdia.
T2Cada taula que separes conserva una clau forana per poder-la tornar a unir amb JOIN.
T1Les anomalies són la versió precisa de la duplicació i la inconsistència dels fitxers plans.

Errors típics d'examen

Tota repetició és un error

Repetir una FK en una 1:N és normal.

1FN = normalitzat

1FN no treu les dependències parcials ni transitives.

Afegir un ID resol 2FN

El nom del producte continua repetit.

Quantitat a PRODUCTOS

Canvia a cada comanda: va a DETALLE_PEDIDO.

Parcial = transitiva

Parcial: depèn d'una part de la clau. Transitiva: depèn d'un no clau.

Separar sense FK

Si no conserves les claus, no pots tornar a unir les dades.

Preu actual = preu de venda

Són dos fets diferents.

Xuleta

Si veus…Pensa…
A → BSi sé A, sé B
Llistes en una cel·la o Telefono1, Telefono2No és 1FN
Atribut que depèn d'una part de la clauDependència parcial → no és 2FN
Clau → no clau → altre no clauDependència transitiva → no és 3FN
Nom del producte repetit al detallTaula PRODUCTOS i ProductoID com a FK
Quantitat d'un producte en una comandaDETALLE_PEDIDO
Nota d'un alumne en un cursMATRICULAS
Canviar la mateixa dada en moltes filesAnomalia d'actualització
No poder registrar una dada solaAnomalia d'inserció
Perdre dades en esborrarAnomalia d'eliminació
Preu de catàleg i preu de vendaDos fets: guarda'ls tots dos
Regla de memòriaLa clau, tota la clau i res més que la clau

Fonts

Basat en T4.pdf (Gabriela Zúñiga Zárate) i la fitxa de la Pràctica 2. Tema 3 i Tema 4. ShopSphere i UniLearn són els casos del material. El tema cobreix 1FN, 2FN i 3FN.

02. Test

40 preguntes

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

03. Targetes

24 targetes

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