4. Normalització

Hem vist que el model relacional consisteix en tindre un conjunt de taules amb qualitat (respectant els requeriments inicials i sense redundàncies). La forma ideal d'aconseguir-ho és la que hem vist: partint d'un esquema E-R.

Però a vegades l'analista no fa el diagrama E-R sinó que fa uns esbossos inicials de com serien les taules. Ixes taules tindran problemes de redundàncies, etc. En eixe cas, cal aplicar la normalització: tècniques per a transformar unes taules mal fetes en altres taules amb qualitat.

Definició

La Normalització és la tècnica del model relacional que consistix en augmentar gradualment la qualitat de les taules, on:

\[Qualitat = Simplicitat + No\ redundància\]

La Normalització ens dirà quines taules falten o sobren, les claus i atributs que tindran i les relacions entre les taules (claus alienes). Així obtindrem un esquema relacional que representarà correctament la informació i permetrà una adequada manipulació de les dades emmagatzemades en la BD.

4.1. El problema

Suposem que un analista ha de fer el disseny de la base de dades per a una biblioteca i, entre altres coses, li han dit:

Necessitem un llistat de llibres on aparega el codi del llibre, el títol, les pàgines, el codi de l'autor, el nom de l'autor i la seua data de naixement.

L'analista no ha volgut fer l'esquema E-R i ha dissenyat directament esta taula:

Disseny inicial

\(LLIBRES = \underline{codi} + títol + n\_pàgines + codi\_autor + nom\_autor + data\_naix\_autor\)

Està bé? Quin problema té?

Totes les ocurrències de LLIBRES que tinguen el mateix codi_autor, també tindran el mateix nom_autor i la mateixa data_naix_autor. Per tant, hi hauria molta informació repetida, amb els problemes que això suposa en una base de dades.

Això passa perquè donat 1 codi_autor tenim només 1 nom_autor i 1 data_naix_autor. En eixe cas direm que codi_autor determina nom_autor i data_naix_autor. O bé, que nom_autor i data_naix_autor depenen de codi_autor. Es representa així:

Com haureu imaginat, caldrà normalitzar la taula. Sense saber la teoria de normalització, podem imaginar que caldrà posar les dades de l'autor en una altra taula i, per tant, quedaria així:

Disseny normalitzat

\(LLIBRES = \underline{codi} + títol + n\_pàgines + codi\_autor\)

\(\hspace{1cm} C. Ali.: codi\_autor \to AUTORS(codi)\)

\(AUTORS = \underline{codi} + nom\_autor + data\_naix\_autor\)

Sense saber-ho, hem aplicat un dels passos de la normalització. Les fletxetes que eixien de codi_autor s'anomenen dependències funcionals.

4.2. Dependències funcionals

Les Dependències Funcionals (DF) establixen relacions de dependència entre els atributs d'una taula. Vegem-ho primer amb l'exemple anterior:

Ixes fletxes són les DF. Siga la següent relació:

\[T = a0 + a1 + \ldots + ai \overset{\curvearrowright}{\longrightarrow} aj + \ldots + an\]

Es diu que l'atribut \(aj\) depén funcionalment de \(ai\), o que \(ai\) determina \(aj\) (es representa \(ai \to aj\)), si cada valor de \(ai\) té associat un únic valor de \(aj\) en la taula \(T\). En altres paraules, si trobàrem dos tuples en \(T\) que coincidiren en el valor de \(ai\), també coincidirien en el valor de \(aj\).

En una taula poden haver dos tipus de DF:

• Les que NO es lleven: les que ixen de la clau. En totes les taules eixiran DF de la clau cap a la resta d'atributs.
• Les que SÍ que cal llevar: les que no ixen de la clau. En l'exemple anterior caldria llevar nom_autor i data_naix_autor de la relació LLIBRES, ja que depenen de codi_autor (que no és clau de la taula).

Normalitzar consisteix bàsicament en llevar eixes DF que no ixen de la clau.

Exemple: Préstecs

Suposem que l'analista ha pensat en esta taula per a guardar la informació sobre els préstecs de la biblioteca:

Disseny inicial

\(PRÉSTECS = \underline{codi\_llibre + num\_soci} + data + titol + nom\_soci\)

Què passaria si introduírem les dades en una taula amb eixe disseny?

PRÉSTECS:

codi_llibre	num_soci	data	titol	nom_soci
HOMMAN	111	11-3-2017	L'home manuscrit	Pep Garcia
NOSVAL	111	11-3-2017	Nosaltres els valencians	Pep Garcia
HOMMAN	222	9-4-2017	L'home manuscrit	Maria Burguera
NOSVAL	111	7-4-2017	Nosaltres els valencians	Pep Garcia
NOSVAL	222	8-4-2017	Nosaltres els valencians	Maria Burguera

Ens adonem que el títol del llibre no depén per a res del soci, i el nom del soci no depén per a res del llibre. Sempre que aparega un mateix codi_llibre tindrem repetit el títol; i sempre que aparega un mateix num_soci tindrem repetit el nom del soci:

Per tant, caldrà llevar eixes DF ja que ixen de PART de la clau, NO de TOTA la clau. Després de normalitzar obtindrem estes taules, on veiem que ja no es repeteix la informació:

PRÉSTECSLLIBRESSOCIS

codi_llibre	num_soci	data
HOMMAN	111	11-3-2017
NOSVAL	111	11-3-2017
HOMMAN	222	9-4-2017
NOSVAL	111	7-4-2017
NOSVAL	222	8-4-2017

codi	titol
HOMMAN	L'home manuscrit
NOSVAL	Nosaltres els valencians

codi	nom
111	Pep Garcia
222	Maria Burguera

4.3. Formes normals

Són les propietats que han de complir les relacions (taules). El procés de normalitzar una relació consisteix en fer les següents fases i en eixe ordre:

Fases de la normalització

1. Passar la relació a 1FN (i, si cal, optimitzar claus)
2. Passar la relació a 2FN
3. Passar la relació a 3FN
4. Passar la relació a FNBC
5. Passar la relació a 4FN
6. Passar la relació a 5FN

Nosaltres vorem fins la 3FN (i FNBC de forma teòrica).

Bàsicament, normalitzar una taula consisteix en:

• Per cada origen de fletxa (que no ix de tota la clau) crear una taula nova, on:
  • L'origen de la fletxa serà la clau.
  • Els destins de les fletxes seran els altres camps de la taula.
• En la taula original:
  • Llevarem els destins de les fletxes.
  • Posarem claus alienes dels origens de fletxes a les respectives taules.

Fase 1) Passar les taules a 1FN

Def. informal: Una taula està en 1FN si:

• No té atributs multivalents {...}
• No té fletxes que van de dins a dins de la clau

Fase 1.1) Llevar atributs multivalents

Disseny inicial

\(PERSONES = \underline{dni} + nom + d\_naix + \{telèfon\}\)

Esta relació no està en 1FN perquè té l'atribut multivalent telèfon. És a dir, una persona pot tindre molts telèfons:

dni	nom	d_naix	telèfon1	telèfon2	telèfon3	...
111	Pep	4-3-70	555111222	555111333	555111777
222	Pepa	5-6-79	555222555	555222666

El problema és que com no sabem quants telèfons pot tindre una persona, no podem reservar espai en la taula per a \(n\) telèfons.

Solució: dividir en dues taules. En compte de tindre els telèfons d'una persona en distintes columnes, els tindrem en distintes files:

EsquemaDades

Representació de les taules

\(PERSONES = \underline{dni} + nom + d\_naix\)

\(TEL\_PERS = \underline{dni + telèfon}\)

\(\hspace{1cm} C. Ali.: dni \to PERSONES(dni)\)

PERSONES:

dni	nom	d_naix
111	Pep	4-3-70
222	Pepa	5-6-79

TEL_PERS:

dni	telèfon
111	555111222
111	555111333
111	555111777
222	555222555

Fase 1.2) Optimitzar claus

Si una relació té clau composta, cal vore si podem fer que la clau no tinga tants camps. Cal llevar DF (fletxetes) entre els camps de la clau composta.

En l'exemple anterior, com una persona podia tindre molts telèfons, ens havia eixit:

\(TEL\_PERS = \underline{dni + telèfon}\)

Com la clau és composta, significa que 1 persona pot tindre molts telèfons i que 1 telèfon pot correspondre a moltes persones (és com una relació M:M). Però... pot 1 telèfon correspondre a molts dnis?

• Si els requeriments diuen que sí (el telèfon pot ser de moltes persones): no cal fer res.
• Si els requeriments diuen que no (el telèfon és d'una sola persona): tenim una DF \(telèfon \to dni\), és a dir, una fletxa que va de dins a dins de la clau. La relació no està en 1FN i cal optimitzar claus.

La solució depén de si la fletxa implica tots els camps de la clau o no:

Fase 1.2.a) Tota la clau implicada

Solució: llevar de la clau el destí de la fletxa (llevar-li el subratllat).

Cas generalExemple

Representació de la taula

\(A = \underline{x} + y + a + b\)

Com la fletxa involucra tots els camps de la clau, la solució és llevar de la clau el destí de la fletxa.

Representació de la taula

\(TEL\_PERS = \underline{telèfon} + dni\)

Fase 1.2.b) Part de la clau implicada

Solució:

• Llevem de la relació el destí de la fletxa.
• Creem altra relació amb origen i destí de la fletxa.

Cas generalExemple

Representació de les taules

\(A = \underline{x + z} + a + b\)

\(\hspace{1cm} C. Ali.: x \to A'(x)\)

\(A' = \underline{x} + y\)

Representació de les taules

\(ADRECES = \underline{dni} + c\_postal\)

\(\hspace{1cm} C. Ali.: c\_postal \to POBLES(cp)\)

\(POBLES = \underline{cp} + nom\)

Tip

Esta forma de resoldre-ho serà la mateixa en la 2FN i 3FN.

---

Exercici 26. Normalitza la següent relació

\(CLIENTS = \underline{codi} + nom + \{tel\}\)

Exercici 27. Normalitza la següent relació

\(CLIENTS = \underline{codi} + nom + \{tel\}\)

R.I.: "El telèfon sols pertany a un únic client"

Exercici 28. Normalitza la següent relació

\(CLIENTS = \underline{codi} + nom + \{tel + operadora\}\)

R.I.: "Un telèfon només treballa en una operadora"

Exercici 29. Normalitza la següent relació

\(CLIENTS = \underline{codi} + nom + \{tel + operadora\}\)

R.I.: "El telèfon sols pertany a un únic client" i "un telèfon només treballa en una operadora"

Fase 2) Passar les taules a 2FN

Def. informal: Una taula està en 2FN si està en 1FN i no té fletxes (D.F.) de dins a fora de la clau.

Solo són sospitoses de no estar en 2FN aquelles taules que tinguen la clau principal composta.

Esta relació NO està en 2FN perquè hi ha una fletxa de dins de la clau (\(y\)) a fora (\(b\)). Si una taula està en 1FN i no té la clau composta, segur que també està en 2FN.

Com passar a 2FN (igual que la forma 1.2.b):

• Llevem de la relació el destí de la fletxa.
• Creem altra relació amb origen i destí de la fletxa.

D'esta forma, desapareixen les redundàncies i possibles inconsistències:

Disseny inicialDisseny normalitzat

Representació de la taula

\(NOTES = \underline{codi\_assig + n\_exp} + nota + nom\_alumne\)

\(\hspace{1cm} D.F.: n\_exp \to nom\_alumne\)

NOTES:

codi_assig	n_exp	nota	nom_alumne
BD	111	7	Pep Garcia
PRG	111	8	Pep Garcia
BD	222	9	Maria Burguera
FOL	111	7	Pep Garcia
PRG	222	8	Maria Burguera

Representació de les taules

\(NOTES = \underline{codi\_assig + n\_exp} + nota\)

\(\hspace{1cm} C. Ali.: n\_exp \to ALUMNES(n\_exp)\)

\(ALUMNES = \underline{n\_exp} + nom\_alumne\)

NOTES:

codi_assig	n_exp	nota
BD	111	7
PRG	111	8
BD	222	9
FOL	111	7
PRG	222	8

ALUMNES:

n_exp	nom_alumne
111	Pep Garcia
222	Maria Burguera

---

Exercici 30. Normalitza la següent relació

\(COMPRES = \underline{art + data} + preu + prov\)

R.I.: "Un article només es pot comprar a un proveïdor"

Exercici 31. Normalitza la següent relació

\(R = \underline{a + b} + c + d\)

\(\hspace{1cm} D.F.: b \to d\)

Exercici 32. Normalitza la següent relació

\(R = \underline{a + b + c} + d\)

\(\hspace{1cm} D.F.: (b + c) \to d\)

Exercici 33. Normalitza la següent relació

\(ESCRIURE = \underline{cod\_autor + cod\_libro} + editorial + data + nombre\_autor + nombre\_libro\)

\(\hspace{1cm} D.F.: cod\_libro \to (editorial + data + nombre\_libro)\)

\(\hspace{2cm} cod\_autor \to nombre\_autor\)

Exercici 34. Normalitza el següent esquema

\(ASSIG = \underline{codigo} + nombre + \{num\_trabajo + descr\_tra\}\)

\(\hspace{1cm} D.F.: (codigo + num\_trabajo) \to descr\_tra\)

\(NOTAS = \underline{n\_exp + assig} + nota\_ass + nombre\_alumno + \{num\_trabajo + nota\_tra\}\)

\(\hspace{1cm} C. Ali.: assig \to ASSIG(codigo)\)

\(\hspace{1cm} D.F.: n\_exp \to nombre\_alumno\)

\(\hspace{2cm} (assig + num\_trab + n\_exp) \to nota\_tra\)

Fase 3) Passar les taules a 3FN

Def. informal: Una taula està en 3FN si està en 2FN i no té fletxes (D.F.) de fora a fora de la clau.

Esta relació NO està en 3FN perquè hi ha una D.F. de fora (\(b\)) a fora (\(c\)).

Com passar a 3FN (igual que la forma 1.2.b):

• Llevem de la relació el destí de la fletxa.
• Creem altra relació amb origen i destí de la fletxa.

Cas generalExemple

Representació de les taules

\(A = \underline{x} + a + b\)

\(\hspace{1cm} C. Ali.: b \to A'(b)\)

\(A' = \underline{b} + c\)

PERSONES:

dni	nom	c_postal	població
111	Pep Garcia	46410	Sueca
222	Maria Burguera	46430	Sollana
333	Manolo Vendrell	46410	Sueca
444	Carles Ventura	46410	Sueca
555	Andreu Albors	46430	Sollana

Sempre que apareix el codi postal 46410 es repeteix que és de Sueca. Cal normalitzar.

Representació de les taules

\(PERSONES = \underline{dni} + nom + c\_postal\)

\(\hspace{1cm} C. Ali.: c\_postal \to POBLES(c\_postal)\)

\(POBLES = \underline{c\_postal} + poblacio\)

PERSONES:

dni	nom	c_postal
111	Pep Garcia	46410
222	Maria Burguera	46430
333	Manolo Vendrell	46410
444	Carles Ventura	46410
555	Andreu Albors	46430

POBLES:

c_postal	població
46410	Sueca
46430	Sollana

---

Exercici 35. Normalitza la següent relació

\(R = \underline{a} + b + c + d\)

\(\hspace{1cm} D.F.: c \to d\)

Exercici 36. Normalitza la següent relació

\(R = \underline{a + b} + c + d + e + f + g + h + \{i\}\)

\(\hspace{1cm} D.F.: c \to (d + e)\)

\(\hspace{2cm} g \to h\)

\(\hspace{2cm} b \to f\)

\(\hspace{2cm} i \to b\)

Exercici 37. Normalitza la següent relació

\(R = \underline{a + b + c} + d + e + \{f + g\}\)

\(\hspace{1cm} D.F.: a \to b\)

\(\hspace{2cm} c \to d\)

Exercici 38. Normalitza la següent relació

\(A = \underline{a0} + \{a1 + a2\} + c + d\)

\(\hspace{1cm} D.F.: a1 \to a2\)

\(\hspace{2cm} c \to d\)

Fase 4) Passar les taules a FNBC

Def. informal: Una taula està en FNBC si està en 3FN i no té fletxes de fora a dins de la clau (on l'origen no és clau alternativa).

Info

• Esta relació NO està en FNBC perquè hi ha una fletxa de fora de la clau (\(a\)) a dins (\(y\)).
• Si una taula està en 3FN i no té la clau composta, segur que també està en FNBC.

Com passar a FNBC (paregut a la forma 1.2.b):

• Llevem de la relació el destí de la fletxa i l'origen de la fletxa també es subratlla.
• Creem altra relació amb origen i destí de la fletxa.
• Ens apareix una R.I. (perquè es perd una restricció original).

Cas generalExemple: IMPARTIR

Representació de les taules

\(A = \underline{x + a} + b\)

\(\hspace{1cm} C. Ali.: a \to A'(a)\)

\(A' = \underline{a} + y\)

\(R.I.: \text{ha de complir-se que } (x + y) \to a\)

Tenim que:

• Donat 1 grup i 1 assignatura (restricció de la clau principal): només és impartit per 1 professor i en 1 aula.
• Cada professor imparteix només 1 assignatura (restricció de la DF \(professor \to assignatura\)).

IMPARTIR (inicial):

grup	assignatura	professor	aula
1DAM	BD	Abdó	1
1DAM	FOL	J.Vicent	1
1ASIX	BD	Pastor	2
1ASIX	FOL	J.Vicent	1
1SMX	FOL	Maria	3

Problemes per no complir FNBC:

• Redundància: cada vegada que apareix Abdó tindrem repetit que imparteix BD.
• Incoherència: no podem garantir que un professor només impartisca una assignatura.
• Pèrdua d'informació: si eliminem el registre de FOL en 1SMX, perdem que Maria imparteix FOL.

Representació de les taules normalitzades

\(IMPARTIR = \underline{grup + professor} + aula\)

\(\hspace{1cm} C. Ali.: professor \to PROFESSORS(professor)\)

\(PROFESSORS = \underline{professor} + assignatura\)

\(R.I.: \text{cal que es complisca la D.F.} (grup + assignatura) \to professor\)

IMPARTIR:

grup	professor	aula
1DAM	Abdó	1
1DAM	J.Vicent	1
1ASIX	Pastor	2
1ASIX	J.Vicent	1
1SMX	Maria	3

PROFESSORS:

professor	assignatura
Abdó	BD
J.Vicent	FOL
Pastor	BD
Maria	FOL

Per què fa falta eixa R.I.? Perquè en la distribució de les noves taules es perd la restricció que teníem inicialment: donat 1 grup i 1 assignatura, només és impartit per 1 professor. Sense la R.I., podríem afegir el registre (1DAM, Pastor, 1) a IMPARTIR i estaríem permetent que al grup 1DAM l'assignatura BD la donen 2 professors (Abdó i Pastor), cosa incorrecta.

---

Exercici 39. Normalitza la següent relació

\(R = \underline{a + b} + c + d + e\)

\(\hspace{1cm} D.F.: (c + d) \to b\)

Exercici 40. Normalitza la següent relació

\(R = \underline{a + b + c} + d + e\)

\(\hspace{1cm} D.F.: d \to (b + c)\)

Exercici 41. Normalitza el següent esquema

\(A = \underline{a0} + a1 + a4 + \{a5\}\)

\(A1 = \underline{a0} + a1\)

\(A2 = \underline{a0} + a2\)

\(B = \underline{b0} + b1 + b2\)

\(R = \underline{a0 + b0} + r1 + r2 + r3\)

\(\hspace{1cm} D.F.: a0 \to r1\)

\(\hspace{2cm} r3 \to b0\)

Exercici 42. Normalitza el següent esquema a partir del graf de dependències

Fase N) Llevar altres redundàncies

Eliminar redundàncies intrataula: llevarem els camps que es puguen obtindre a partir d'altres camps de la mateixa taula.

\(NOTES = \underline{dni + assig} + nota1 + nota2 + nota3 + nota\_mitja\)

Es pot eliminar nota_mitja ja que es pot obtindre a partir d'altres atributs: \((nota1 + nota2 + nota3) / 3\).

Eliminar redundàncies intertaula: llevarem camps que es puguen obtindre a partir de camps d'altres taules.

Exemple

\(NOTES = \underline{dni + assig} + nota\)

\(ALUMNES = \underline{dni} + nom + \cancel{nota\_mitja}\)

Es pot eliminar nota_mitja ja que la nota mitja d'un alumne es pot obtindre sumant les seues notes de la taula NOTES i dividint-ho per la quantitat de notes que té.

Atenció

L'eliminació de la redundància intertaula sol provocar ralentització de les consultes. Cal buscar un compromís entre puresa i eficiència i estudiar cada cas en particular.

Fusionar taules que compartixen la mateixa clau: només si no trenquem el resultat de l'opcionalitat i especialització.

Exemple

\(ALUMNES\_DADES\_PERSONALS = \underline{codi} + nom + d\_naix + tel\)

\(ALUMNES\_DADES\_ACADÈMIQUES = \underline{codi} + curs + grup + repetidor\_sn\)

\[\Downarrow\]

\(ALUMNES = \underline{codi} + nom + d\_naix + tel + curs + grup + repetidor\_sn\)

4.4. Esquema-resum de com normalitzar

Forma Normal	No ha d'haver	Si tenim...	Solució
1FN	{...} (multivalents)	\(A = [\underline{x}] + \{mul\} + b\)	\(A = [\underline{x}] + b\) i \(A' = [\underline{x + mul}]\) amb C.Ali: \(x \to A(x)\)
1FN	fletxa dins→dins (tota la clau)	\(A = [\underline{ori + des}] + a\)	Llevar subratllat de \(des\): \(A = [\underline{ori}] + des + a\)
1FN	fletxa dins→dins (part de la clau)	\(A = [\underline{x + ori + des}] + a\)	\(A = [\underline{x + ori}] + a\) i \(A' = [\underline{ori}] + des\)
2FN	fletxa dins→fora	\(A = [\underline{x + ori}] + des + a\)	\(A = [\underline{x + ori}] + a\) i \(A' = [\underline{ori}] + des\) amb C.Ali
3FN	fletxa fora→fora	\(A = [\underline{x}] + ori + des + a\) (\(ori\) no és C.Alt)	\(A = [\underline{x}] + ori + a\) i \(A' = [\underline{ori}] + des\) amb C.Ali
FNBC	fletxa fora→dins	\(A = [\underline{x + des}] + ori + a\) (\(ori\) no és C.Alt)	\(A = [\underline{x + ori}] + a\) i \(A' = [\underline{ori}] + des\) + R.I.

4.5. Passos per a normalitzar

Pas	Descripció
1) Suposicions	Definir les regles del domini que ens permetran identificar les DF.
2) Detectar les DF	Identificar les DF que no ixen de la clau primària ni de cap clau alternativa.
3) Normalitzar cada taula	Aplicar les fases 1FN → 2FN → 3FN a cada taula.
4) Obtindre l'esquema integrat final	Fusionar taules amb la mateixa clau (si no són especialitzacions) i posar noms coherents.
5) Dibuixar el graf de dependències	Ordre en el qual s'haurien de crear les taules, segons les claus alienes.

---

Exercici 43. Alumnes, assignatures i aules

En un institut tenen el següent llistat amb informació d'alumnes, les assignatures on estan matriculats i a quines aules van.

Ens han explicat eixa taula dient-nos que:

• L'atribut nota_m és la nota mitjana que ha tret l'alumne entre les notes de les seues assignatures.
• L'atribut assignatura representa la identificació de les assignatures en què es troba matriculat cada alumne.
• L'atribut nota és la nota que ha tret cada alumne en cada assignatura.
• L'atribut curs representa el curs en què s'imparteix una assignatura. Una assignatura està assignada a un únic curs.
• L'atribut aula representa les aules en què s'imparteixen les assignatures. Una assignatura només es pot impartir en una aula.
• L'atribut lloc és l'espai físic que ocupa eixa aula dins del centre.

Es demana:

a. Defineix en notació de De Marco la relació ALUMNES que tindrà tota eixa informació (on la clau serà el DNI). b. Detecta les possibles dependències funcionals a partir de les dades de la taula i de l'explicació que ens han donat. c. Normalitza eixa relació fins a FNBC no redundant.

Exercici 44. L'hotel de Sueca

L'hotel de Sueca vol informatitzar les factures i ens n'ha deixat una per a que tinguem clar quina informació vol guardar de cada factura:

Es demana:

a. Defineix en notació de De Marco la relació FACTURES que tindrà tota eixa informació (on la clau serà el número de la factura). b. Detecta les possibles dependències funcionals a partir de les dades de la factura i de l'experiència que tingues en les factures i els hotels. En cas de dubte, indica les possibles suposicions que cregues convenient. c. Normalitza eixa relació fins a FNBC no redundant. d. A partir de la factura fes l'esquema E-R. A continuació fes el pas a taules. Comprova si t'ha donat el mateix resultat que en la normalització.