UF1A3. Teoria 2a part

Referències

• https://gestionbasesdatos.readthedocs.io/es/latest/Tema2/Teoria.html

T17. Normalització

Exemple que farem servir: ALUMNE cursa ASSIGNATURA

Model E-R

ALUMNE cursa ASSIG (N:M, opcional-opcional) (nota i data són atributs de la interrelació)
ALUMNE viu a MUNICIPI (1:M, obligatori-opcional)
ALUMNE té ARMARIET (M:1, opcional-opcional)

Imaginem que tenim uns armariets a l'institut, i que els alumnes els alumnes poden optar a tenir un armariet. A més, els armariets es poden compartir. La relació és

ALUMNE (dni (PK), nom, cognom, tel, mail)
ASSIG (id_assig (PK), assig)
MUNICIPI (id_mun (PK), municipi)
ARMARIET (ref, pis, capacitat, ref_clau)

ALUMNE cursa ASSIG és opcional-opcional perquè podem considerar casos en què tenim alumnes que estan registrats però que no estan a la bd; i assignatures que aquest curs no tenen cap matrícula.

ALUMNE viu a MUNICIPI és obligatori-opcional. Un alumne segur que pertany a un municipi. Partim d'una base de dades dels municipis de l'Àrea Metropolitana. Si tenim un alumne d'un altre municipi, afegirem el municipi a la bd. Però potser tenim municipis (per ex St Adrià del Besós) sense cap alumne. Ho fem opcional-opcional perquè és el cas més general possible.

Les dades que tenim són les següents:

ALUMNE:
1234A;Pere;Rovira;636 51 66 77;provira@jaumebalmes.net,provira@gmail.com,provira@hp.com
2345B;Maria;Claramunt;638 52 77 88;mclaramunt@jaumebalmes.net,mclaramunt@gmail.com
3456C;Rita;Santandreu;646 22 33 44;rsantandreu@jaumebalmes.net,rsantandreu@gmail.com
4567D;Jordi;Puig;646 44 55 66;jpuig@jaumebalmes.net,jpgui@gmail.com

Veiem que mail és multivaluat.

MUNICIPI:
1;Barcelona
2;Badalona
3;Santa Coloma
4;Sant Adrià
5;L'Hospitalet
6;Cornellà

ASSIG:
M01;Sistemes Informàtics
M02;Bases de Dades
M03;Programació
M04;Llenguatge Marques
M05;Entorns de Desenvolupament

ARMARIET (ref, pis, capacitat, ref_clau)
A1;1;20;K234
A2;1;15;K345
A3;1;10;K456
A4;2;20;K567
A5;2;15;K678
A6;2;10;K789
A7;3;20;K890
A8;3;15;K901

En les interrelacions tenim aquestes dades:

• Pere Rovira viu a Barcelona
• Maria Claramunt viu a Barcelona
• Rita Santandreu viu a Cornellà
• Jordi Puig viu a Santa Coloma

• Pere Rovira està matriculat de Sistemes Informàtics, Bases de Dades, Programació, Llenguatge Marques
• Maria Claramunt està matriculat de Sistemes Informàtics, Bases de Dades
• Rita Santandreu està matriculat de Bases de Dades, Programació, Llenguatge Marques
• Jordi Puig està matriculat de: RES

• Pere Rovira té armariet A1
• Maria Claramunt NO té armariet
• Rita Santandreu té armariet A1
• Jordi Puig viu NO té armariet

De moment encara no tenim dades de les notes de les assignatures.

Pas a model relacional: Entitats

Cada entitat es transforma en una taula. L'identificador (o identificadors) de l'entitat passa a ser la clau principal de la relació (la subratllem o bé posem PK (Primary Key)). Si hi ha clau alternativa es posa en negrita (o bé AK).

Les entitats ALUMNE, ASSIG i MUNICIPI generen taula. Podem posar les claus primàries i les claus alternatives.

Les taules que tenim de moment són:

ALUMNE (dni (PK))
ASSIG (id_assig (PK))
MUNICIPI (id_mun (PK))
ARMARIET (ref (PK))

Ara bé, què passa amb els elements multi-valuats de la taula ALUMNE? Una de les regles és que no pot haver-hi valors multi-valuats. Cada valor multi-valuat el posarem en una fila diferent. La taula alumne queda de la següent manera:

ALUMNE
--------
1234A;Pere;Rovira;636 51 66 77;provira@jaumebalmes.net
1234A;Pere;Rovira;636 51 66 77;provira@gmail.com
1234A;Pere;Rovira;636 51 66 77;provira@hp.com
2345B;Maria;Claramunt;638 52 77 88;mclaramunt@jaumebalmes.net
2345B;Maria;Claramunt;638 52 77 88;mclaramunt@gmail.com
3456C;Rita;Santandreu;646 22 33 44;rsantandreu@jaumebalmes.net
3456C;Rita;Santandreu;646 22 33 44;rsantandreu@gmail.com
4567D;Jordi;Puig;646 44 55 66;jpuig@jaumebalmes.net
4567D;Jordi;Puig;646 44 55 66;jpuig@gmail.com

Ara bé, ara hem trencat la clau primària. Solució: ens veiem obligats a modificar el model ER, creant la relació USUARI té MAIL (1:M):

i ara tindrem una nova taula:

MAIL (mail (PK), tipus) (aprofitem per crear l'atribut tipus: feina, personal, institut)
ALUMNE (dni (PK), nom, cognom, tel) (la taula ALUMNE ja no conté el camp mail)

Amb les següents dades:

ALUMNE
--------
1234A;Pere;Rovira;636 51 66 77
2345B;Maria;Claramunt;638 52 77 88
3456C;Rita;Santandreu;646 22 33 44
4567D;Jordi;Puig;646 44 55 66

MAIL
--------
provira@jaumebalmes.net;institut
provira@gmail.com;personal
provira@hp.com;feina
mclaramunt@jaumebalmes.net;institut
mclaramunt@gmail.com;personal
rsantandreu@jaumebalmes.net;institut
rsantandreu@gmail.com;personal
jpuig@jaumebalmes.net;institut
jpuig@gmail.com;personal

NOTA: hem perdut provisionalment la informació de quins mails tenen els alumnes.

Relacions binàries de grau 2

Cas N:M

És el cas més senzill: sempre generen taula.

Es crea una taula que incorpora com a claus forànees (FK, Foreign Key) cadascuna de les claus de les entitats que participen en la relació. La clau principal d'aquesta nova taula està composta per aquests camps. És important recalcar que no es tracta de 2 claus primàries, sinó d'una clau primària composta per 2 camps. Si hi ha atributs propis de la interrelació, passen a la taula de la relació (la taula pont).

És el cas de la relació ALUMNE cursa ASSIG (N:M). Com que la interrelació genera taula, li hem de trobar un nom adient. En aquest cas, el més adequat és MATRICULA.

MATRICULA (
   dni FK,
   id_assig FK,
   nota,
   data
   PK (dni, id_assig)
)
-------------------------------------
1234A;1;NULL;01/09/2021
1234A;2;NULL;01/09/2021
1234A;3;NULL;01/09/2021
1234A;4;NULL;01/09/2021
2345B;1;NULL;01/09/2021
2345B;2;NULL;01/09/2021
3456C;2;NULL;01/09/2021
3456C;3;NULL;01/09/2021
3456C;4;NULL;01/09/2021

Cas 1:M

Es pot donar dos casos: que generin taula, o que no.

• Cas 1: Si l'entitat del costat 1 presenta participació opcional, aleshores es crea una nova taula per a la relació que incorpora com a claus forànies les claus de les dues entitats. La clau principal de la relació serà només la clau de l'entitat del costat N.
• Cas 2: Per a la resta de situacions, l'entitat del costat N rep com a clau aliena la clau de l'entitat del costat 1. Els atributs propis de la relació passen a la taula on s'ha incorporat la clau aliena.

Exemple Cas 1: L'entitat del costat 1 (ARMARIET) presenta participació opcional (hi ha armariets que no els té ningú). S'ha de crear una nova taula (ALUMNE_ARMARIET), que incorpora com a claus forànies les claus de les dues entitats (dni i ref). La clau principal de la relació serà només la clau de l'entitat del costat N (dni). És a dir:

ALUMNE_ARMARIET (dni (PK), ref)
---------------
1234A;1
3456C;1

NOTA: podem agafar com a PK el dni, o bé la ref de l'armariet. (també hi ha possibilitat de no definit clau primària, funciona igualment).

La segona opció:

ALUMNE (dni (PK), nom, cognom, tel, id_mun (FK), ref (FK))
------
1234A;Pere;Rovira;636 51 66 77;1;1
2345B;Maria;Claramunt;638 52 77 88;1;NULL
3456C;Rita;Santandreu;646 22 33 44;6;1
4567D;Jordi;Puig;646 44 55 66;3;NULL

Exemple Cas 2: En el cas de ALUMNE i MUNICIPI és el cas 2. El costat N és ALUMNE, i el que farem és afegir en la taula ALUMNE el id_mun com a clau forània, fent referència a quin municipi pertany a l'alumne. Per tant, queda:

ALUMNE (dni (PK), nom, cognom, tel, id_mun (FK))
------
1234A;Pere;Rovira;636 51 66 77;1
2345B;Maria;Claramunt;638 52 77 88;1
3456C;Rita;Santandreu;646 22 33 44;6
4567D;Jordi;Puig;646 44 55 66;3

Ens falta fer el cas d'ALUMNE i MAIL. És una relació 1:M, i es soluciona afegint al MAIL el camp dni com a clau forànea. La taula MAIL queda:

MAIL (mail (PK), tipus, dni)
--------
provira@jaumebalmes.net;institut;1
provira@gmail.com;personal;1
provira@hp.com;feina;1
mclaramunt@jaumebalmes.net;institut;2
mclaramunt@gmail.com;personal;2
rsantandreu@jaumebalmes.net;institut;3
rsantandreu@gmail.com;personal;3
jpuig@jaumebalmes.net;institut;4
jpuig@gmail.com;personal;4

Cas 1:1

Relació 1:1, es pot crear una nova taula o no. S'ha de valorar si volem tenir o no valors NULs, i si en volem tenir molts, pocs o cap.

Per exemple, en el cas de l'armariet, imaginem que la relació és 1:1 (no es poden compartir els armariets)

• Pere Rovira té armariet A1
• Maria Claramunt NO té armariet
• Rita Santandreu té armariet A3
• Jordi Puig viu NO té armariet

Si creem una nova taula:

ALUMNE_ARMARIET (dni (PK), ref)
---------------
1234A;1
3456C;3

Si no creem una nova taula, tindrem valors nuls (més o menys)

ALUMNE (dni (PK), nom, cognom, tel, id_mun (FK), ref (FK))
--------
1234A;Pere;Rovira;636 51 66 77;1;1
2345B;Maria;Claramunt;638 52 77 88;1;NULL
3456C;Rita;Santandreu;646 22 33 44;6;3
4567D;Jordi;Puig;646 44 55 66;3;NULL

Clar que també podem posar dni com a clau forànea a ARMARIET (en aquest cas tenim molts valors NULs):

ARMARIET (ref (PK), pis, capacitat, ref_clau, dni (FK))
A1;1;20;K234;1234A
A2;1;15;K345;NULL
A3;1;10;K456;3456C
A4;2;20;K567;NULL
A5;2;15;K678;NULL
A6;2;10;K789;NULL
A7;3;20;K890;NULL
A8;3;15;K901;NULL

Per tant, hi ha tres possibilitats, i hem de valorar quina és la millor opció.

Resum. PERSONA compra COTXE

Veiem a classe els diferents casos d'aquest exemple.

T18. Normalització. Relacions ternàries

Relació ternària

Una relació (interrelació) ternària és aquella en la que intervenen 3 entitats. L'exemple ESTUDIANT matricula ASSIGNATURA ja l'hem fet i discutit. Anem ara a modificar-lo tenint en compte casos que es poden donar a la realitat.

Un estudiant pot suspendre una assignatura, i s'ha de matricular de l'assignatura diversos anys (diferents convocatòries). En el seu expedient ha de constar tot l'històric de les notes que ha anat treient en les diferents convocatòries. Per tant, ja no és vàlid que l'estudiant té una nota d'una assignatura, sinó que en té moltes perquè s'ha matriculat diverses vegades, i en diferents dates.

A més, el preu pot variar, amb diversos aspects: -les assignatures no tenen per què valdre el mateix. -una assignatura pot variar el preu amb els anys (es pot encarir o es pot abaratir) -el preu de l'assignatura es pot encarir a mida que passen les convocatòries. -el preu pot dependre de l'estudiant (normal, beca, famílina nombrosa).

Per tant, veiem que el preu no depèn ni de l'estudiant, ni de l'assignatura ni de l'any, sinó de tots tres al mateix temps: és un atribut d'una relació ternària:

ESTUDIANT matricula ASSIGNATURA en un ANY: N-M:P, obligatori-obligatori-obligatori.

Com resolem aquest cas?

• Cada entitat crea una nova taula. Tindrem 3 taules: ESTUDIANT, ASSIGNATURA, ANY.
• Igual que passava en el cas N:M, en la relació N:M:P la relació també crea una nova taula: MATRICULA
• La taula MATRICULA incorpora les claus primàries de les altres tres taules (i totes elles formen la clau primària). A més, incorpora els atributs que li són propis.

Les tres taules queden:

ESTUDIANT (id (PK), nom)
ASSIGNATURA (ref (PK), assignatura)
ANY (any (PK), calendari)
MATRICULA (id (FK), ref (FK), any (FK), data, preu, nota, PK:[id,ref,any])

Anem a posar unes dades mínimes.

ESTUDIANT
--------------
1;Anna
2;Jordi

ASSIGNATURA
-------------
BD;Bases de dades
PROG;Programació

ANY
---------
2020;calendari_2020
2021;calendari_2021

MATRICULA
---------------
1;BD;01/09/2021;30;NULL
1;PROG;01/09/2021;20;NULL
2;BD;01/09/2020;25;4
2;PROG;01/09/2020;25;8
2;BD;01/09/2021;35;NULL

• Veiem que l'assignatura de BD valia 25e el 2020, i que el 2021 val 30e
• Però el 2021, el Jordi no ha pagat de BD els 30e que ha pagat l'Anna, sinó que ha pagat 35e com a recàrrec perquè està repetint.
• El Jordi va aprovar PROG l'any passat, però en canvi BD no la va aprovar i l'està repetint.

Veiem que amb unes dades mínimes podem veure que el nostre model és consistent.

---

creat per Joan Quintana Compte, octubre 2021