UF1A3. Teoria 1a part
Contingut
T13: Model relacional
Coneixements previs
Partim del model ER (esquema conceptual), que ja hem treballat a classe, i volem arribar a un esquema lògic (model relacional).
Introducció
Un model és un conjunt de normes que ens permeten descriure el món real. Els models són importants perquè unifiquen la forma de representar la informació fent possible l’entesa entre les diferents persones.
El procés general en la creació d'un sistema de base de dades consisteix en:
Especificacions->Model E-R->Model relacional->Model físic.
Model relacional
El model relacional es desenvolupa a partir del model enitat-relació (ER) i és el penúltim pas per a desenvolupar el model físic, que conformen les instruccions a executar. A diferència del model ER, es disposen d'una sèrie de tècniques o passos per a crear el model relacional.
Igual que el model ER, el model relacional és un model semàntic: es centra amb el significat de les dades a través de la seva representació.
Objectius
El model relacional consisteix en realitzar un disseny de la informació, normalment a partir d'un model ER previ, tenint com a objetius bàsics:
- Minimitzar la redundància de dades.
- Assegurar l'accés eficient a la informació.
Per aconseguir aquests objectius s'estableixen 5 formes normals o 5 regles que cal complir. Tot bon disseny cal que compleixi amb aquestes regles, tot i que a nivell pràctic és suficient amb les 3 primeres.
T14: Terminologia
La terminologia que s'utilitza en el model ER és diferent a l'utilitzada en el model relacional. És per aquest motiu que cal definir alguns termes i fer la correspondència amb el model ER.
Registre
Un registre es correspon a una entitat del model ER. En aquest sentit, un registre estarà forma per un conjunt de dades normalment de diferents tipus.
Taula
Una taula és una estructura que permet emmagatzemar un conjunt de registres. Una taula és l'equivalent a un conjunt d'entitats en el model ER, tot i que, com veurem posteriorment, algunes taules són el resultat d'una relació entre diferents conjunts d'entitats.
Camp
Un camp es correspon a una dada d'un registre. Un camp és l'equivalent a un atribut del model ER, tot i que no tots els atributs es transformen en camps. Cada camp tindrà associat un tipus, que normalment ve determinat pel domini de l'atribut o la naturalesa.
Clau primària
Una clau primària és un atribut identificador associat a un conjunt d'entitats. Per tant, una clau primària serà sempre un o varis camps que em permeten identificar un registre dins una taula. Si la clau primària està formada per varis camps s'obté una clau composta.
Clau candidata o secundària o alternativa
En el model ER a vegades hi ha varis atributs que podem escollir com a atribut identificador. Per exemple, si tinc un conjunt d'entitats treballador tinc 2 possibles candidats (com a mínim) a ser identificador: número seguretat social i dni. Tots els camps associats a atributs identificadors que no formin part de la clau primària són claus candidates.
T15: Dependència funcional
Una dependència funcional es dóna quan a partir d'un o varis atributs d'un conjunt de relacions es pot determinar el valor de qualsevol altre atribut. Existirà una dependència funcional entre l'atribut a1 i a2 que pertanyen al mateix conjunt de relacions (supertaula, informació no estructurada) si sabent el valor de l'atribut a1 sempre sé el valor de l'atribut a2. Expressarem que a2 té una dependència funcional d' a1 o bé que a1 determina a a2 utilitzant una fletxa (→) i incloent entre {} tots els atributs que queden determinats.
- a1 → {a2} : a1 determina a a2 o a2 té una dependència funcional d'a1
- a2 → {a1} : a2 determina a a1 o a1 té una dependència funcional d'a2
Sistema càlcul dependència funcional
Una dependència funcional moltes vegades es pot deduir pel que representa l'atribut.
Així, per exemple, si tinc un atribut codi postal i un atribut capital (per a les capitals de província), està clar que donat un codi postal jo sempre podré dir quina població és: el nom de la capital té una dependència funcional respecte al codi postal, o que el codi postal em determina el nom de la capital. En canvi, el nom de la capital no determina el codi postal, ja que Barcelona té molts codis postals: 08012, 08006, 08025,... si dic Barcelona no puc donar només un únic codi postal! El codi postal no té una dependència funcional en relació al nom de la capital.
NOTA: hem posat l'exemple de les capitals de província, perquè per a municipis petits el CP no determina la població. Poden haver-hi varis municipis petits amb un mateix codi postal. Per exemple, Bagà i Gisclareny tenen el mateix codi postal: 08695.
Càlcul dependència funcional
Les dependències funcionals no sempre són tan clares com el codi postal i el nom d'una capital. Quan es dóna aquesta situació , cal disposar d'un sistema per a poder determinar quines són les dependències funcionals. Per explicar el sistema suposarem que tenim un conjunt d'entitats A amb els atributs a1, a2 i a3.
- Crearem una sèrie d'entitats que siguin representatives.
- Mirarem quin atribut o atributs no tenen repeticions
- Si hi ha un atribut que no té cap repetició, la resta d'atributs tindran una dependència funcional respecte a aquest atribut.
Exemple
atrib1 atrib2 atrib3 ------ ------ ------ va11 va21 va31 va12 va22 va31 va13 va23 va31 va14 va24 va32 va15 va25 va33 va16 va26 va33
Observem com l'atribut 1 i 2 (atrib1 i atrib2) no tenen cap valor repetit per les entitats de mostra. Si em diuen un valor de atrib1 o de atrib2 sempre se sap a quin valor correspon per la resta d'atributs (atrib3). Per tant, puc afirmar que:
atrib1 → {atrib2, atrib3}
atrib2 → {atrib1, atrib3}
Si el nostre conjunt d'entitats tingués com a mostra de valors:
atrib1 atrib2 atrib3 ------ ------ ------ va11 va21 va31 va12 va22 va31 va13 va22 va31 va14 va24 va32 va15 va25 va33 va16 va26 va33
Els valors de atrib1 no han canviat i són tots diferents. Per tant, la dependència es manté:
atrib1 → {atrib2, atrib3}
Per contra, atrib2 té ara un valor duplicat va22. Per aquest valor, no puc determinar ún únic valor de atrib1, pot ser va12 o va13. Per tant, atrib2 no em determina atrib1. atrib2 sí que continua determinant atrib3, ja que pel valor repetit va22, el valor de atrib3 és el mateix (va33).
Ara tenim:
atrib2 → {atrib3}
atrib3 → {}
Exemple de la Hípica
Utilitzem un exemple per a veure com calculem les dependències funcionals. Suposem que tenim una supertaula (informació no estructurada) amb els camps: Cavall, Hípica, Data, Temps, Club, Llicència, Campió i Núm amb els següents registres:
Cavall Hípica Data Temps Club Llicència Campió Núm --------- ------ -------- -------- ------ --------- -------- ---- Amor Camil 11/10/21 01:34:00 8474 2976 Fruit 2978 Diversió Camil 11/10/21 01:49:00 8474 2977 Fruit 2978 Fruit Camil 11/10/21 01:32:00 8474 2978 Fruit 2978 Gaudir Camil 11/10/21 01:54:00 8474 2979 Fruit 2978 Amor Camil 13/10/21 01:34:00 8474 2976 Diversió 2977 Diversió Camil 13/10/21 01:39:00 8474 2977 Diversió 2977 Fruit Camil 13/10/21 01:12:00 8474 2978 Diversió 2977 Gaudir Camil 13/10/21 01:25:00 8474 2979 Diversió 2977 Amor Rial 15/10/21 01:34:00 7000 2976 Diversió 2977 Diversió Rial 15/10/21 01:39:00 7000 2977 Diversió 2977 Fruit Rial 15/10/21 01:12:00 7000 2978 Diversió 2977 Gaudir Rial 15/10/21 01:25:00 7000 2979 Diversió 2977
Representa uns cavalls que han anat a fer 3 curses (3 dies diferents) a dues hípiques. I tenim registrat el temps de cada cavall a cada cursa, l'identificador del club (la Hípica), l'identificador del cavall (la llicència). I també tenim registrats qui ha guanyat cada cursa i el seu número de llicència.
Aquestes són unes dades vàlides (però no estan estructurades, són unes dades brutes). Pero entre aquests atributs podem determinar unes dependències funcionals.
Per a calcular les dependències funcionals fem una taula on creuarem tots els camps entre ells. Per a cada un dels camps indicarem si hi ha una dependència funcional o no entre els dos atributs relacionats.
- Identificarem al cavall (A, D, F, G)
- Identificarem l'hípica (C, R).
- Identificarem la data segons el dia.
- Identificarem el temps pel temps que ha trigat a la cursa.
- Identificarem la llicència i el núm. per l'últim dígit.
Si el fons de la cel·la de la taula és de color verd, hi ha una dependència funcional, en cas contrari no hi ha dependència funcional.
Les dependències funcionals que obtenim són les següents (té una certa lògica):
Cavall → {Llicència} (cada cavall té una llicència única)
Hípica → {Club} (cada hípica representa un club diferent)
Data → {Hípica, Club, Campió, Num} (cada Data s'ha celebrat una cursa, que representa una hípica i club diferent, i també un campió i núm diferent)
Temps → {Cavall, Llicència, Campió}
Club → {Hípica} (cada club té la seu en una hípica)
Llicència → {Cavall} (cada llicència es correspon a un cavall)
Campió → {Núm.} (cada campió es correspon a un número de cavall)
Núm. → {Campió} (cada número de campió es correspon a un nom de cavall diferent.)
Donat que no hem trobat cap atribut que em determini a tots els altres, el procés continua agafant parelles de camps, fins a trobar una parella, trio... que impliqui a
tots els camps de la taula. En aquest cas, proseguim el procés combinant el camp Data amb tota la resta. El motiu de l'elecció de Data és perquè aquest atribut ja determina a 4 atributs de la taula.
Amb els camps Data i Cavall tota la resta de camps de la taula queden determinats. La clau primària de la nostra taula és {data,cavall}. Per exemple, la combinació 15/10/21 i Amor significa que un cavall anomenat Amor, amb llicència 2976, va participar el 15/10/21 a una cursa celebrada a la Hípica Rial (club amb llicència 7000), i en la cursa va fer un temps de 01:34:00. Cursa que va guanyar el cavall 2977, anomenat Diversió.
A partir de les dependències funcionals es pot obtenir el model relacional, tot i que les dependències funcionals és un sistema molt lent per a obtenir el model relacional i s'utilitzen només per a validar que el model relacional compleix amb totes les formes normals.
T16: Normalització
El procés de normalització consisteix en convertir una superttaula on s'emmagatzema tota la informació amb vàries taules menors per tal d'eliminar les anomalies de la no normalització.
En aquest cas tenim una super-taula provinent d'unes dades no-estructurades, unes dades brutes posades en forma de taula. No venim d'un model entitat-relació, senzillament tenim unes dades i volem veure com podem extreure informació coherent i estructurada d'aquestes dades.
Anomalies de no fer la normalització
Suposem que una base de dades consti d'una única taula per a emmagatzemar la informació dels estudiants i de les unitats formatives que estan cursant. A continuació es dóna una versió reduïda de la taula amb diferents registres
Redundància de dades
Veiem que hi ha moltes dades repetides. Cada vegada que un estudiant cursa una unitat formativa diferent, en aquesta super-taula es tornen a repetir totes les dades de l'estudiant. La probabilitat d'error s'incrementa ja que hi ha un major volum d'informació.
En el cas d'error (suposem que per a un registre en comptes de Granollers posem Granolers), les dades passen a ser incoherents. Les dades de l'estudiant només s'haurien d'introduir una vegada (aquest és un dels objectius importants que perseguirem).
Coherència de dades
En aquesta super-taula no es garanteix que les dades siguin coherents, ja que si per un mateix estudiant s'han introduït dues adreces diferents (per error, per exemple), el sistema no pot controlar-ho. El fet que les dades no siguin coherents és una conseqüència directe de la redundància de les dades.
Procés de normalització
Per a normalitzar el primer que cal fer és localitzar les dependències funcionals. En el nostre exemple, és fàcil observar que hi ha 4 dependències funcionals diferents:
DNI → {NOM, TELF, POB, CP, ADREÇA}
UF → {Nom UF} i Nom UF → {UF} (la dependència funcional es compta com una de sola)
CP → {POB}
Cap de les dependències funcionals conté una clau primària. Per a obtenir una clau primària, és necessari que entre els camps determinants i els camps amb dependència funcional conformin tots els camps de la taula.
En el nostre cas, per a obtenir una clau primària, és necessari disposar de 2 camps. Combinant DNI amb UF o DNI amb nom UF s'obté la clau primària
{DNI,UF } → {NOM, TELF., POB., CP., ADREÇA, Nom UF}
Les dependències funcionals ens indiquen com hem de dividir la nostra taula en taules més petites. En aquest cas tindrem tres taules diferents, una per a cada dependència funcional.
Creem una taula a partir dels camps que em determina el camp DNI. Anomenem ESTUDIANT a aquesta taula. De la taula ESTUDIANT cal eliminar tots els camps que tinguin una dependència funcional amb un altre camp; en el nostre cas, el camp població depèn de codi postal i per tant, eliminem el camp població de la taula. Els camps UF i nom UF no estan determinats pel DNI.
La combinació de DNI i UF serà una bona clau primària. Mirem com queda i eliminem les dependències funcionals que conté:
Partíem d'una super-taula on hi havia moltes dependències funcionals i redundància. I després del procés, finalment queden les taules ESTUDIANTS-UF, UF i ESTUDIANTS:
Resumint, mirant les dependències funcionals en una super-taula hem aconseguit aïllar 3 taules (més petites) que estan relacionades entre elles i sense redundància de dades. Dins de cada taula la clau primària determina de forma unívoca el registre.
POBLACIO (CP (PK), POBLACIO) ESTUDIANT (DNI (PK), NOM, TELF, CP, ADREÇA) ESTUDIANT-UF (DNI (PK), UF (PK)) UF (UF (PK), NOMUF)
creat per Joan Quintana Compte, octubre 2021
