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;2021;01/09/2021;30;NULL
1;PROG;2021;01/09/2021;20;NULL
2;BD;2020;01/09/2020;25;4
2;PROG;2020;01/09/2020;25;8
2;BD;2021;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.

NOTA: a vegades pot ser interessant que la clau primària de MATRICULA sigui un autonumèric (queda més simple).

MATRICULA (id_mat (PK), id (FK), ref (FK), any (FK), data, preu, nota)
---------------
1;1;BD;2021;01/09/2021;30;NULL
2;1;PROG;2021;01/09/2021;20;NULL
3;2;BD;2020;01/09/2020;25;4
4;2;PROG;2020;01/09/2020;25;8
5;2;BD;2021;01/09/2021;35;NULL

T19. Normalització. Relacions ternàries, 1:1:1

ESTUDIANT defensa PROJECTE a través de TRIBUNAL (id_proj (PK), projecte) (1:1:1, oblig-oblig-oblig)

La interrelació defensa té com a atributs la data, la nota, i l'aula. Són atributs que no depenen ni de l'estudiant, ni del projecte, ni del tribunal, sinó de la relació entre tots ells.

Les tres entitats creen tres taules. I per la interrelació s'ha de crear una nova taula, i queda:

ESTUDIANT (id (PK), nom)
PROJECTE (id_proj (PK), projecte)
TRIBUNAL (id_trib (PK), components)

DEFENSA (id (PK, FK), id_trib (FK), id_proj (FK), data, nota, aula )

N'hi ha prou en què la clau primària de DEFENSA sigui el id de l'estudiant, doncs l'estudiant només farà un sol projecte. Però també admet altres opcions, com que sigui el id_proj la clau primària o bé fins i tot el id_trib.

Exemple:

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

PROJECTE
-------------
proj1;Projecte 1
proj2;Projecte 2

TRIBUNAL
------------
trib1;J-K-L (són les inicials dels professors que composen el tribunal)
trib2;J-L-M

DEFENSA
------------
1;trib1;proj1;10/10/2021,10,A43
2;trib2;proj2;10/11/2021,NULL,NULL

Ara bé, un cas més habitual seria que el mateix tribunal pugués defensar molts estudiants. Però no canvien les taules. L'anterior definició de les taules també ho admet.

Una altra cosa és que la defensa es pugui suspedre, i s'hagi de repetir la defensa. En aquest cas n'hi ha prou en posar la data dins de la clau primària (juntament amb el id de l'estudiant):

DEFENSA (id (FK), id_trib (FK), id_proj (FK), data, nota, aula, PK (id,aula) )

NOTA: Hem de vigilar que una relació ternària no sigui en realitat dues relacions binàries.

ESTUDIANT defensa PROJECTE a través de TRIBUNAL

en realitat seria

ESTUDIANT defensa PROJECTE, i TRIBUNAL avalua PROJECTE (1 o M)

Quedarà:

ESTUDIANT (id (PK), nom)
TRIBUNAL (id_trib (PK), components)

i per a la taula PROJECTE tenim dues possibilitats:

PROJECTE (id_proj (PK), projecte, id (FK) id_trib(FK), data, nota) (genera valors nuls per a tots els projectes que no estan avaluats)
o bé
PROJECTE (id_proj (PK), projecte, id (FK) id_trib(FK)) (no hi ha valors nuls)
DEFENSA (id_proj (PK), id_trib(FK), data, nota) (no hi ha valors nuls)

T20. Interrelacions recursives

Exemple 1: Categories i subcategories

La idea és que no hi ha categories i subcategories, sinó només categories que estan relacionades amb una relació d'arbre, de manera que aquest arbre pugui tenir diferents nivells de branques.

CATEGORIA té CATEGORIA

és una relació 1:M, opcional-opcional. Una categoria no té perquè tenir subcategories (fills), però en pot tenir vàries o moltes. D'altra banda, una categoria no té perquè tenir un pare (potser està en l'arrel de l'arbre).

Aquest tipus de relacions recursives es solucionen de la següent manera: com que la interrelació té no té atributs, només cal crear la taula CATEGORIA, i indicar per cada entrada quin és el pare de l'element (o NULL si està en l'arrel).

Per exemple:

CATEGORIA (id_cat (PK), categoria, id_cat_parent)
----------------
1;Electrònica;NULL
2;Rentadores;NULL
3;Televisors;1
4;Àudio;1
5;TV 32'';3
6;TV 40'';3
7<;TV 4K;3
8;Alta fidelitat;4
9;Amplificadors bluetooth;4
10;Sony;8
11;Philips;8
12;Ordinadors;NULL

Amb aquestes dades veiem com hem pogut definir diferents nivells de branques d'una manera totalment flexible. Aquesta tècnica és la que s'utilitza per exemple a Wordpress per categoritzar els posts.

Sobre aquest cas farem el qüestionari de les categories que trobem al lloc bikeshop.es.

Exemple 2: PERSONA es casa amb PERSONA

PERSONA es casa amb PERSONA és una relació 1:1 (obligatori-obligatori)

on el fet de casar-se inclou el fet d'ajuntar-se, separar-se, divorciar-se.

En aquest cas, el fet de casar-se té atributs importants, com ara la data i el tipus de relació que s'estableix. Com que aquests atributs són importants, serà necessari crear la taula CASAMENT (a part de la taula PERSONA).

PERSONA (id_persona, nom, cognom)
CASAMENT (id_pers1 (FK), id_pers2 (FK), data, tipus, PK(id_pers1,id_pers2,data))

on tipus pot ser 'CC': casament civil; 'CR': casament religiós; 'PF': parella de fet; 'S': separació; 'D': divorci

Exemple de dades:

PERSONA
---------
1;Alba;Torres
2;Pau;Gutiérrez
3;Clara;Ribas
4;Jeroni;Bou

CASAMENT
-----------
1;2;10/01/2015;CC
3;4;10/05/2016;CR
1;2;10/01/2017;D
3;4;10/05/2018;S 
1;4;03/05/2019;PF
2;3;05/05/2020;PF

T21. Entitats fortes i dèbils

entitat forta i dèbil

EDIFICI conté DESPATX (1:M, obligatori-obligatori)

EDIFICI és l'entitat forta. DESPATX és l'entitats dèbil. No poden haver-hi despatxos sense un edifici.

num (el número del despatx dins d'un edifici) identifica de forma unívoca un despatx dins de l'edifici. És en aquest sentit que és una clau primària d'una entitat dèbil. Ara bé, poden haver despatxos amb el mateix número en edificis diferents.

Entitats:

EDIFICI (nom (PK), adreça)
DESPATX (num (PK), superficie)

Taules en què es transforma:

EDIFICI (nom (PK), adreça)
DESPATX (nom (FK), num, superficie, PK(nom,num))

La combinació de nom (nom de l'edifici) i num (número del despatx) sí que identifica totalment un despatx.

Dades:

EDIFICI (nom (PK), adreça)
--------------
E1;C/E1 BCN
E2;C/E2 BCN

DESPATX (nom (FK), num, superficie, PK(nom,num))
--------------
E1;D1;45
E1;D2;34
E1;D3;55
E2;D1;35
E2;D2;24
E2;D3;45

T22. Generalització / especialització

especialització

ANIMAL és la superclasse, i MAMÍFER, AU, RÈPTIL i PEIX són les subclasses.

ANIMAL és MAMÍFER
ANIMAL és AU
ANIMAL és RÈPTIL
ANIMAL és PEIX

Atributs d'ANIMAL: Són els atributs comuns a tots els animals.

ANIMAL (id, pais_origen, tipus_alimentacio)

I per cada tipus d'animal hi ha atributs que li són propis:

MAMÍFER (nom, data_naix, espai)
AU (ref_anellament, num_ous, gàbia)
RÈPTIL (id_terrari, temperatura, nivell_llum)
PEIX (id_aquari, temperatura, pH)

Una possibilitat seria que hi hagués una sola taula:

ANIMAL (id, pais_origen, tipus_alimentacio, nom, data_naix, espai, ref_anellament, num_ous, gàbia, id_terrari, temperatura, nivell_llum, id_aquari, temperatura, pH )

però està clar que això generaria moltíssims valors nuls, per tant no és una bona opció. La solució serà crear una taula per cadascuna de les entitats dèbils. I ANIMAL també serà taula. Queda:

Taules:

ANIMAL (id (PK), tipus, pais_origen, tipus_alimentacio)
MAMÍFER (id (PK, FK), nom, data_naix, espai)
AU (id (PK, FK), ref_anellament, num_ous, gàbia)
RÈPTIL (id (PK, FK), id_terrari, temperatura, nivell_llum)
PEIX (id (PK, FK), id_aquari, temperatura, pH)

Una millora que es pot fer és afegir el tipus d'animal com a camp en la taula ANIMAL. Però això no és obligatori fer-ho (està introduint una certa redundància, que queda compensada per la claredat).

Dades:

ANIMAL (id (PK), tipus, pais_origen, tipus_alimentacio)
---------
1;mamífer;Uganda;1
2;mamífer;Congo;2
3;au;Romania;3
4;rèptil;Madagascar;4
5;peix;Mar Roig;5

MAMÍFER (id (PK, FK), nom, data_naix, espai)
----------
1;Floquet;03/03/2018;A1
2;Mimo;12/12/1998;G4

AU (id (PK, FK), ref_anellament, num_ous, gàbia)
----------
3;A23423;3;G123

RÈPTIL (id (PK, FK), id_terrari, temperatura, nivell_llum)
----------
4;T234;28;5

PEIX (id (PK, FK), id_aquari, temperatura, pH)
----------
5;A220;18;7.0

---

creat per Joan Quintana Compte, octubre 2021