Transformació de Model Entitat Relació a Model Relacional Joan Puigcerver Ibáñez j.puigcerveribanez@edu.gva.es Llicència: CC BY-NC-SA 4.0 (Reconeixement - NoComercial - CompartirIgual) 🅭 Introducció En aquest material ens centrarem en les regles generals per transformar un Model Entitat Relació a un Model Relacional on les dades s'emmagatzemaran en taules relacionals. Entitats regulars Totes les entitats regulars o fortes es transformen en una taula relacional, on cada atribut està definit en les columnes de la taula. El nom de les taules és en majúscula, i el nom dels atributs en minúscula i sense espais. Consell Si una entitat o atribut està composta per diferents paraules, podeu utilitzar la convenció snake_case, on cada paraula està separada per guions baixos. Les claus primàries es mantenen en els dos models. Per norma general, totes les taules relacionals necessiten una clau primària per identificar els seus elements. Si no s'ha definit una clau primària en el model entitat relació, es pot crear una clau primària "fictícia", com id o ref. Exemple L'entitat Cotxe es traduiria com: COTXE(#matricula, marca, model, km, data_compra) matricula és la clau primària. Aquesta taula podria contindre diversos elements o ocurrències: Taula Cotxe #Matrícula N. Bastidor Marca Model Km Data compra 4231 KJL 16483920 Ford Focus 39031 08/12/2000 4078 CMP 23948756 Ford Fiesta 10542 02/01/2001 3091 LSF 12347003 Volkswagen Golf 8065 02/04/2001 7012 DLM 84012398 Peugeot 207 48623 28/11/2001 3877 FFT 74832134 Audi Q2 57737 05/12/2001 1034 KMC 56481093 BMW X3 8480 NULL Relacions La transformació de les relacions del model ER no és directa, i la transformació es realitzarà de diferents maneres, segons el tipus de relació, el grau i la cardinalitat. Per norma general, totes les relacions poden ser modelades utilitzant una taula relacional. No obstant això, el fet de combinar moltes taules és una operació costosa i pot afectar el rendiment de la base de dades. Taula relacional addicional. Cardinalitat M:N Les relacions binàries de cardinalitat M:N, és a dir, molts a molts, sempre es convertiran en una taula relacional que emmagatzema quins elements de cada taula estan relacionats. En cas que aquesta relació tinga algun atribut, també s'afegirà a la taula relacional com una columna. Aquesta estratègia funciona amb qualsevol relació, però cal evitar crear taules innecessàries per evitar una sobrecàrrega en la base de dades. Exemple Aquest diagrama es traduiria com: PRODUCTE(#id, nom, preu) id és la clau primària. CISTELLA_COMPRA(#id) id és la clau primària. CISTELLA_PRODUCTE(#id_cistella(CISTELLA_COMPRA:#id), #id_producte(PRODUCTE:#id), unitats) El conjunt de id_cistella i id_producte és la clau primària. Clau forana id_cistella fa referència a l'atribut id de la taula CISTELLA. Clau forana id_producte fa referència a l'atribut id de la taula PRODUCTE. Un exemple amb les dades podria ser el següent: CISTELLA id 1 2 3 PRODUCTE id Nom Preu 1 Barra de pa 0.7 2 Botella aigua 0.5L 0.3 3 Pot Coca-Cola 33 cL 1.5 4 Napolitana xocolate 1.5 CISTELLA_PRODUCTE id_cistella id_producte Unitats 1 1 3 1 2 1 1 3 1 3 2 2 3 4 1 Aquestes dades representen que existeixen tres cistelles: Cistella 1, que conté: 3 unitats del Producte 1: Barra de pa. 1 unitat del Producte 2: Botella aigua 0.5L. 1 unitat del Producte 3: Pot Coca-Cola 33 cL. Cistella 2, que no conté cap producte. Cistella 3, que conté: 2 unitats del Producte 2: Botella aigua 0.5L. 1 unitat del Producte 4: Napolitana Xocolate. Es pot veure que aquesta estructura permet que una cistella continguen múltiples productes i que un producte pot estar en múltiples cistelles (N:N). Propagació de clau forana. Cardinalitat 1:N En les relacions binàries de cardinalitat 1:N, no és necessari crear una taula relacional addicional. L'entitat que estiga associada a un únic objecte de l'altra entitat, conté la clau forana de l'altra entitat com a atribut. A aquesta tècnica s'anomena propagació de clau forana. En el cas que hi haja un atribut en la relació, aquest s'afegiria a la taula que conté la clau forana. Si la cardinalitat és (0,1), la clau forana pot prendre el valor NULL per indicar que no té cap element associat de l'altra entitat. En canvi, si la cardinalitat és (1,1), hauríem de definir la restricció NOT NULL a la clau forana. Exemple Aquest diagrama es traduiria com: PERSONA(#dni, nom, cognoms) dni és la clau primària. COTXE(#matricula, n_bastidor, marca, model, km, propietari(PERSONA:#dni), data_compra) matricula és la clau primària. Clau forana propietari fa referència a l'atribut dni de la taula PERSONA. Un exemple amb dades podria ser: Taula Persona #DNI Nom Cognoms 91471118A Carles Perelló 23986973C Òscar Peris 24211153L Andrès Pastor 76064612R Isabel Mora Taula Cotxe #Matrícula N. Bastidor Marca Model Km Propietari Data compra 4231 KJL 16483920 Ford Focus 39031 91471118A 08/12/2000 4078 CMP 23948756 Ford Fiesta 10542 91471118A 02/01/2001 3091 LSF 12347003 Volkswagen Golf 8065 23986973C 02/04/2001 7012 DLM 84012398 Peugeot 207 48623 24211153L 28/11/2001 3877 FFT 74832134 Audi Q2 57737 76064612R 05/12/2001 1034 KMC 56481093 BMW X3 8480 NULL NULL En aquest cas, una persona pot tindre en possessió múltiples cotxes, però un cotxe sols pot tindre un propietari, o no tindre'n cap (NULL). Cardinalitat 1:1 En les relacions binàries de cardinalitat 1:1 poden ser resoltes de diverses maneres. Combinació de taules En el cas que siga una relació (1,1) a (1,1), és a dir, que un objecte d'una entitat sempre està associat a un i sols un element d'una altra entitat, ens podem fer les següents preguntes: Té sentit que siguen dues entitats separades? Cada entitat es relaciona amb altres entitats per si mateixa? En cas afirmatiu, aquest mètode no és l'adient. En cas negatiu, es poden combinar les dues entitats en una mateixa taula. Exemple Aquest diagrama es pot traduir de les següents dues maneres: CONDUCTOR(#dni, nom, cognoms, n_permis, data_obtencio) dni és la clau primària. n_permis és una clau alternativa. O bé: PERMIS(#n_permis, data_obtencio, dni, nom, cognoms). n_permis és la clau primària. dni és una clau alternativa. Un exemple de la primera opció amb dades podria ser: Taula Conductor #DNI Nom Cognoms N. Permís Data obtenció 91471118A Carles Perelló 70594827 28/06/2019 23986973C Òscar Peris 63351206 24/02/2020 24211153L Andrès Pastor 80536775 16/09/2020 76064612R Isabel Mora 80536775 15/07/2021 S'han combinat les dues entiats en la mateixa taula. Propagació de clau forana De la mateixa manera que hem modelat anteriorment les relacions 1:N, podem modelar les relacions 1:1. Aquesta és l'opció més comuna, ja que permet modelar aquesta relació sense necessitat d'una taula relacional addicional i a més permet mantindre la independència entre les dues entitats. En aquest cas, per evitar que múltiples elements puguen estar associats a un mateix element de l'altra entitat, s'ha de definir la restricció UNIQUE. A més, aquesta estratègia permet tindre relacions amb cardinalitat (0,1), on quan no hi ha un element associat s'indica amb el valor NULL. Exemple Aquest diagrama es pot traduir de les següents dues maneres: CONDUCTOR(#dni, nom, cognoms) dni és la clau primària. PERMIS(#n_permis, data_obtencio, dni_conductor(CONDUCTOR:#dni)) n_permis és la clau primària. dni_conductor és una clau forana que fa referència a l'atribut dni de la taula CONDUCTOR. Restricció UNIQUE per evitar que dos permisos estiguen associats al mateix conductor. O bé: CONDUCTOR(#dni, nom, cognoms, n_permis(PERMIS:#n_permis))) dni és la clau primària. n_permis és una clau forana que fa referència a l'atribut n_permis de la taula PERMIS. Restricció UNIQUE. Restricció UNIQUE per evitar que dos conductors estiguen associats al mateix permís. PERMIS(#n_permis, data_obtencio) n_permis és la clau primària. Un exemple de la primera opció amb dades podria ser: Taula Conductor #DNI Nom Cognoms 91471118A Carles Perelló 23986973C Òscar Peris 24211153L Andrès Pastor 76064612R Isabel Mora Taula Permís #N. Permís Data obtenció DNI Conductor 70594827 28/06/2019 91471118A 63351206 24/02/2020 23986973C 80536775 16/09/2020 24211153L 80536775 15/07/2021 76064612R Taula relacional addicional De la mateixa manera que hem modelat anteriorment les relacions N:N, podem modelar les relacions 1:1 mitjançant la creació d'una taula relacional addicional. Aquesta és l'opció menys comuna, ja que s'ha de crear una nova taula relacional, quan amb les dues opcions anteriors no ho necessiten. No obstant això, permet mantindre la independència entre les dues entitats. Aquesta estratègia pot ser útil en el cas d'una relació (0,1) a (0,1) on hi ha molta dispersió, és a dir, on molts dels elements de les dues entitats no estan relacionades entre si. En compte de crear una columna amb la clau forana on molts valors seran NULL, s'opta per crear una taula per emmagatzemar aquells elements que sí que estan relacionats. En aquest cas, per evitar que múltiples elements puguen estar associats a un mateix element de l'altra entitat, s'ha de definir la restricció UNIQUE en les dues claus foranes de la taula relacional addicional. Exemple Aquest diagrama es pot traduir de la següent manera mitjançant una taula relacional addicional: CONDUCTOR(#dni, nom, cognoms) dni és la clau primària. PERMIS(#n_permis, data_obtencio) n_permis és la clau primària. El conjunt de dni i n_permis és la clau primària. dni_conductor és una clau forana que fa referència a l'atribut dni de la taula CONDUCTOR. Restricció UNIQUE per evitar que dos permisos estiguen associats al mateix conductor. CONDUCTOR_PERMIS(#dni_conductor(CONDUCTOR:#dni), #n_permis(PERMIS:#n_permis))) n_permis és una clau forana que fa referència a l'atribut n_permis de la taula PERMIS. Restricció UNIQUE. Restricció UNIQUE per evitar que dos conductors estiguen associats al mateix permís. Un exemple amb dades podria ser: Taula Conductor #DNI Nom Cognoms 91471118A Carles Perelló 23986973C Òscar Peris 24211153L Andrès Pastor 76064612R Isabel Mora Taula Conductor/Permís #DNI Conductor #N. Permís 91471118A 70594827 23986973C 63351206 24211153L 80536775 76064612R 80536775 Taula Permís #N. Permís Data obtenció 70594827 28/06/2019 63351206 24/02/2020 80536775 16/09/2020 80536775 15/07/2021 Relacions reflexives Les relacions reflexives es modelen de la mateixa manera que les relacions binàries, i el seu modelatge depén de la cardinalitat d'aquesta relació. Cardinalitat 1:N Quan la cardinalitat d'una relació reflexiva és 1:N es pot resoldre mitjançant una propagació de la clau forana, on la mateixa entitat té una clau forana que fa referència a l'entitat. Exemple Aquest diagrama es pot traduir de la següent manera mitjançant propagació de clau forana: EMPLEAT(#dni, nom, cognoms, dni_superior(EMPLEAT:#dni)) dni és la clau primària. dni_superior és una clau forana que fa referència a l'atribut dni de la mateixa taula EMPELAT. Un exemple amb dades podria ser: Taula Empleat #DNI Nom Cognoms Superior 91471118A Carles Perelló NULL 23986973C Òscar Peris 24211153L 24211153L Andrès Pastor 91471118A 76064612R Isabel Mora 91471118A En aquest cas es modela que: L'empleat 91471118A no té cap superior assignat. És el superior dels empleats: 24211153L 76064612R L'empleat 24211153L està subordinat 91471118A. És el superior de l'empleat 23986973C. Cardinalitat N:N Quan la cardinalitat d'una relació reflexiva és N:N es pot resoldre mitjançant una taula relacional addicional, on es referència la mateixa entitat dues vegades. Exemple Aquest diagrama es pot traduir de la següent manera mitjançant una taula relacional addicional: EMPLEAT(#dni, nom, cognoms) dni és la clau primària. EMPLEAT_SUPERIOR(#dni(EMPLEAT:#dni), #dni_superior(EMPLEAT:#dni)) El conjunt dni i dni_superior és la clau primària. dni és una clau forana que fa referència a l'atribut dni de la mateixa taula EMPELAT. dni_superior és una clau forana que fa referència a l'atribut dni de la mateixa taula EMPELAT. Un exemple amb dades podria ser: Taula Empleat #DNI Nom Cognoms 91471118A Carles Perelló 23986973C Òscar Peris 24211153L Andrès Pastor 76064612R Isabel Mora Taula Empleat/Superior #DNI #Superior 23986973C 24211153L 24211153L 91471118A 76064612R 91471118A 76064612R 24211153L En aquest cas es modela que: L'empleat 91471118A no té cap superior assignat. És el superior dels empleats: 24211153L 76064612R L'empleat 24211153L està subordinat 91471118A. És el superior dels empleats: 23986973C 76064612R Grau N Quan el grau de la relació és major a 2, sempre es requereix una taula relacional addicional per emmagatzemar quins elements de cada entitat estan relacionats entre si. Si es necessita definir restriccions de cardinalitat en alguna entitat, es pot utilitzar el UNIQUE. Exemple Aquest diagrama es pot traduir de la següent manera mitjançant una taula relacional addicional: METGE(#dni, nom, cognoms, especialitat) dni és una clau primària. PACIENT(#dni, nom, cognoms) dni és una clau primària. MEDICINA(#referencia, nom) referencia és una clau primària. PRESCRIU(dni_metge(METGE:#dni), #dni_pacient(PACIENT:#dni), #medicina(MEDICINA:#referencia), quantitat) dni_metge és una clau forana que fa referència a l'atribut dni de la taula METGE. dni_pacient és una clau forana que fa referència a l'atribut dni de la taula PACIENT. medicina és una clau forana que fa referència a l'atribut referencia de la taula MEDICINA. Taula Metge #DNI Nom Cognoms Especialitat 91471118A Carles Perelló Medicina general 23986973C Òscar Peris Endocrinologia Taula Pacient #DNI Nom Cognoms 24211153L Andrès Pastor 76064612R Isabel Mora Taula Medicina #Referència Nom 3270 Ibuprofeno 600 mg 658244 Paracetamol 500 mg Taula Prescriu #DNI Metge #DNI Pacient #Referència Quantitat 91471118A 24211153L 3270 1 91471118A 24211153L 658244 3 91471118A 76064612R 658244 2 23986973C 24211153L 3270 1 23986973C 76064612R 658244 2 Entitats febles Les entitats febles es transformen en una taula relacional, on cada atribut està definit en les columnes de la taula. Aquestes entitats estan identificades per una entitat regular i, per tant, necessàriament estan relacionades amb aquesta. Es realitza una propagació de clau forana a l'entitat feble, que a més, serà part de la clau primària juntament amb els discriminants. Exemple Aquest diagrama es pot traduir de la següent manera mitjançant propagació de clau forana: AVIO(#codi, marca, model) codi és la clau primària. SEIENT(#codi_avio(AVIO:#codi), #fila, #lletra) El conjunt codi_avio, fila i lletra és la clau primària. codi_avio és una clau forana que fa referència a l'atribut codi de la taula AVIO. Un exemple amb dades podria ser: Taula Avió #Codi Marca Model RYR3755 Boeing 737-800 VLG1377 Airbus A320 Taula Seient #Codi Avió #Fila #Lletra RYR3755 1 A RYR3755 1 B RYR3755 2 A RYR3755 2 B VLG1377 1 A VLG1377 1 B VLG1377 2 A VLG1377 2 B Generalització i especialització La generalització i especialització es modela mitjançant taules relacionals. L'entitat general i les entitats específiques es modelen mitjançant taules relacionals, que comparteixen la clau primària. A més, les taules de les entitats especialitzades estan relacionades a la taula general mitjançant la clau primària, que a més a més és una clau forana. Aquestes taules contindran els atributs específics de cada entitat especialitzada. Exemple Aquest diagrama es pot traduir de la següent manera: VEHICLE(#matricula, marca, model) Aquesta és l'entitat general, que conté els atributs comuns a totes les entitats. matricula és la clau primària. COTXE(#matricula(VEHICLE:#matricula), nombre_seients) Aquesta emmagatzema els atributs especialitzats de COTXE. matricula és la clau primària. matricula és una clau forana que fa referència a l'atribut matricula de la taula VEHICLE. MOTO(#matricula(VEHICLE:#matricula), cilindrada) Aquesta emmagatzema els atributs especialitzats de MOTO. matricula és la clau primària. matricula és una clau forana que fa referència a l'atribut matricula de la taula VEHICLE. Un exemple amb dades podria ser: Taula Vehicle #Matrícula Marca Model 4231 KJL Ford Focus 3091 LSF Volkswagen Golf 7012 DLM Peugeot 5008 4078 CMP Suzuki V-Strom 800 3877 FFT Honda Africa Twin 1034 KMC Ducati DesertX Taula Cotxe #Matrícula Nombre Seients 4231 KJL 5 3091 LSF 5 7012 DLM 7 Taula Moto #Matrícula Cilindrada 4078 CMP 776 3877 FFT 1084 1034 KMC 937 Bibliografia Material de Laura Villalba, Introducció a les bases de dades. Ahijado Sánchez, A, Bases de datos, Ed. Marcombo. https://stackoverflow.com/questions/18365870/er-diagram-ternary-relationship-how-to-read-properly 📌 Aquest document pot quedar desactualitzat després d’imprimir-lo. Pots consultar la versió més recent a la pàgina web. 🌿 Abans d’imprimir aquest document, considera si és realment necessari. Redueix el consum de paper i ajuda a protegir el nostre entorn.

CC BY-NC-SA 4.0 (Reconeixement - NoComercial - CompartirIgual) 🅭 Joan Puigcerver Ibáñez