Generalitat de Catalunya Departament d’EnsenyamentInstitut Obert de Catalunya
Nom i cognoms
2251 CFGS Administració de sistemes informàtics en xarxaMòdul 2 – Gestió de bases de dades
UF1 – Introducció a les bases de dadesUnitat 3 – Model relacional i normalització
EAC2 - Solució(Curs 2012-13 / 2n semestre)
Solució
Activitat 1 1.5 punts
Partint del següent diagrama Entitat-Relació, creeu les taules del model relacional que se'n derivi.
LLIBRE(Títol)
AUTOR(Nom)
ESCRIU (Títol, Nom)
ON {Títol} FA REFERÈNCIA A LLIBRE I
ON {Nom} FA REFERÈNCIA A AUTOR
Codi: I71 Exercici d'avaluació contínua 2 Pàgina 1 de 10
Versió: 02 2251M02_EAC2_Enunciat_1213S2Lliurament: 21/3/2013
ombrenoProp
Formació professionalNom i cognoms
EDICIÓ(ISBN, Títol, Any, Idioma)
ON {Títol} FA REFERÈNCIA A LLIBRE
CÒPIA (ISBN, Número)
ON {ISBN} FA REFERÈNCIA A EDICIÓ
USUARI (IdUsuari, Nom)
PRÈSTEC(ISBN, Número, IdUsuari, DataInici, DataFi)
ON {ISBN, Número} FA REFERÈNCIA A CÒPIA,
ON { IdUsuari} FA REFERÈNCIA A USUARI
Activitat 2 3.5 punts
Partint del següent diagrama Entitat-Relació, creeu les taules del model relacional que se'n derivi.
Codi: I71 Exercici d'avaluació contínua 2 Pàgina 2 de 10
Versió: 02 2251M02_EAC2_Enunciat_1213S2Lliurament: 21/3/2013
Formació professionalNom i cognoms
PARC_BOMBERS(CodParc, Adreça, Categoria)
EQUIP(CodEquip, Nom)
BOMBER(CodBomber, Nom, Adreça, CodParc, CodCàrrec, CodEquip)
ON {CodParc} FA REFERENCIA A PARC_BOMBERS
ON {CodEquip} FA REFERENCIA A EQUIP
TORN(CodTorn, Descripció)
PERÍODE(DataInici, DataFi)
TREBALLA_EN(CodBomber, DataInici, DataFi, CodTorn)
ON {CodBomber} FA REFERÈNCIA A BOMBER
ON {DataInici, DataFi} FA REFERÈNCIA A PERÍODE
ON {CodTorn} FA REFERÈNCIA A TORN
PETICIÓ_SERVEI(CodiPetició, Tipus_Servei, Urgència)
REP(CodParc, CodPetició, Dia, Hora)
ON {CodParc} FA REFERÈNCIA A PARC_BOMBERS
ON {CodPetició} FA REFERÈNCIA A PETICIÓ_SERVEI
COCHE(CodParc, NumCoche, Model, DataCompra, Data_Ultima_Revisió)
ON { CodParc} FA REFERÈNCIA A PARC_BOMBERS
Codi: I71 Exercici d'avaluació contínua 2 Pàgina 3 de 10
Versió: 02 2251M02_EAC2_Enunciat_1213S2Lliurament: 21/3/2013
Formació professionalNom i cognoms
Activitat 3 2 punts
Considereu la següent relació, anomenada TORNS_BOTIGUES, que fa referència a l'assignació de torns de treball dels empleats d'una cadena de botigues de roba.
CORRESPONDÈNCIES_TORNS (DNI, Nom, CodiBotiga, AdreçaBotiga, Torn, Data)
DNI Nom CodiBotiga AdreçaBotiga Torn Data
1234567A Martín 1A Diagonal, 474 M 02/09/13
9876543B Sanz 1A Diagonal, 474 M 02/09/13
3434343C Diaz 1A Diagonal, 474 T 02/09/13
1234567A Martín 2B Pg.Gràcia, 2 T 03/09/13
4455667B Lopez 3C CC.Maquinista M 03/09/13
1212156J Monte 2B Pg.Gràcia, 2 M 03/09/13
4455667B Lopez 3C CC.Maquinista M 04/09/13
1212156J Monte 1A Diagonal, 474 M 04/09/13
9876543B Sanz 1A Diagonal, 474 T 04/09/13
1234567A Martín 2B Pg.Gràcia, 2 M 05/09/13
Es demana, tenint en compte tant l'esquema com l'extensió de la relació mostrada a la taula anterior:
1. Escriure l'esquema de dependències funcionals. Podeu fer-ho, si voleu amb les següent notació textual, per no haver de fer un diagrama (sempre que no us en deixeu cap):
{Atribut1, Atribut2} --> {Atribut3, Atribut4, Atribut5}{Atribut2} --> {Atribut4, Atribut5}{Atribut3} --> {Atribut4}
Les dependències multivalents (si de cas en trobeu), les podeu representar amb una fletxa de punta doble: -->>
Clau Primària: {DNI, Data}
{DNI} --> {Nom}
{DNI, Data} --> {Torn}
{DNI, Data} --> {CodiBotiga}
{DNI, Data} --> {AdreçaBotiga}
Codi: I71 Exercici d'avaluació contínua 2 Pàgina 4 de 10
Versió: 02 2251M02_EAC2_Enunciat_1213S2Lliurament: 21/3/2013
Formació professionalNom i cognoms
2. Dir, de manera argumentada (és a dir, en funció de les dependències funcionals detectades), en quina forma normal es troba la relació.
Com que tenim tots els atributs amb valors atòmics, la relació está en 1FN.
Ara bé, trobem que no tots els atributs que no formen part de la clau depenen completament d'aquesta. Observem que a partir del DNI podem obtenir el Nom, per exemple. Per tant no es compleix la 2FN.
Conclusió: La relació es troba en 1FN
3. Aplicar, pas a pas, el procés de normalització com a mínim fins la 3FN (cal explicar les successives decisions que aneu prenent).
Tenint en compte les dependències funcionals obtingudes, el que primer cal fer es trobar la manera en què tots els atributs depenguin completament de la Clau.
Així doncs, obtindriem:CORRESPONDÊNCIES_TORNS_2 (DNI, Data, CodiBotiga, AdreçaBotiga,Torn)EMPLEAT (DNI, Nom)
Aquestes dues relacions ja estan en 2FN.Ara bé, la relació CORRESPONDÈNCIES_TORNS' no es troba en 3FN donat que tenim depèndències transitivies: podem saber l'adreça de la botiga a partir del codi de la botiga.
Per solucionar-ho, podem fer:CORRESPONDÊNCIES_TORNS_3(DNI, Data, CodiBotiga,Torn)BOTIGA (CodiBotiga, AdreçaBotiga)
Veiem que també es compleixen les condicions per a afirmar que la relació es troba en 3FN al no haver-hi dependències transitives.
4. Construïu totes les representacions tabulars resultants d'aplicar el procés de normalització, i distribuïu les dades de la taula original segons correspongui.
EMPLEAT
DNI Nom
1234567A Martín
9876543B Sanz
3434343C Diaz
4455667B Lopez
1212156J Monte
Codi: I71 Exercici d'avaluació contínua 2 Pàgina 5 de 10
Versió: 02 2251M02_EAC2_Enunciat_1213S2Lliurament: 21/3/2013
Formació professionalNom i cognoms
CORRESPONDONDÈNCIES_TORNS_3
DNI Data CodiBotiga Torn
1234567A 02/09/13 1A M
9876543B 02/09/13 1A M
3434343C 02/09/13 1A T
1234567A 03/09/13 2B T
4455667B 03/09/13 3C M
1212156J 03/09/13 2B M
4455667B 04/09/13 3C M
1212156J 04/09/13 1A M
9876543B 04/09/13 1A T
1234567A 05/09/13 2B M
BOTIGA
CodiBotiga AdreçaBotiga
1A Diagonal, 474
2B Pg.Gràcia, 2
3C CC.Maquinista
Codi: I71 Exercici d'avaluació contínua 2 Pàgina 6 de 10
Versió: 02 2251M02_EAC2_Enunciat_1213S2Lliurament: 21/3/2013
Formació professionalNom i cognoms
Activitat 4 3 punts
Considereu la següent relació, anomenada PRÈSTECS i que conté informació sobre els diferents prèstecs de llibres d'una biblioteca:
PRÈSTECS(num_soci, nom_soci, cod_llibre, data_prèstec, editorial, pais)
num_soci nom_soci cod_llibre data_prèstec Editorial país
C005 Pepe Pérez44H
PG57
11/10/12
01/01/09
Planeta
Platz
Espanya
Alemanya
C540 Rosa Sánchez
44H
L23
L23
09/09/12
01/02/13
12/12/12
Planeta
Peperoncino
Planeta
Espanya
Itàlia
Espanya
Es demana, tenint en compte tant l'esquema com l'extensió de la relació mostrada a la taula anterior:
1. Escriure l'esquema de dependències funcionals. Podeu fer-ho, si voleu amb les següent notació textual, per no haver de fer un diagrama (sempre que no us en deixeu cap):
{Atribut1, Atribut2} --> {Atribut3, Atribut4, Atribut5}{Atribut2} --> {Atribut4, Atribut5}{Atribut3} --> {Atribut4}
Les dependències multivalents (si de cas en trobeu), les podeu representar amb una fletxa de punta doble: -->>
Donat que num_soci és la clau primària, tenim les següents dependències per definició
{num_soci} --> {nom_soci, cod_llibre, data_prèstec, editoria, país}
D'altra banda, també es pot observar el següent;
{editorial} --> {país}
No s'observen més dependències funcionals.
2. Dir, de manera argumentada (és a dir, en funció de les dependències funcionals detectades), en quina forma normal es troba la relació.
Com que trobem atributs que tenen valors no atòmics (1FN) podem dir que la relació no es troba en 1FN i per tant, tampoc en 2FN ni 3FN ni FNBC
Codi: I71 Exercici d'avaluació contínua 2 Pàgina 7 de 10
Versió: 02 2251M02_EAC2_Enunciat_1213S2Lliurament: 21/3/2013
Formació professionalNom i cognoms
3. Aplicar, pas a pas, el procés de normalització com a mínim fins la forma normal de Boyce-Codd (cal explicar les successives decisions que aneu prenent).
Per obtenir la 1FN cal que tots els seus atributs tinguin valors atòmics, afegint totes les files que calguin i redefinint les claus , d'aquesta manera obtenim la tabla següent:
PRÈSTECS_2(num_soci, nom_soci, cod_llibre, data_prèstec, editorial, pais)
num_soci nom_soci cod_llibre data_prèstec Editorial país
C005 Pepe Pérez 44H 11/10/12 Planeta Espanya
C005 Pepe Pérez PG57 01/01/09 Platz Alemanya
C540 Rosa Sánchez 44H 09/09/12 Planeta Espanya
C540 Rosa Sánchez L23 01/02/13 Peperoncino Itàlia
C540 Rosa Sánchez 44H 12/12/12 Planeta Espanya
C540 Rosa Sánchez 55H 13/01/13 Planeta Espanya
Per tal que la relació estigui en 2FN cal que tots els atributs que no són clau depenguin de forma completa de la clau primària, no val amb una sublau.
En el cas de l'editorial del país, podem saber-la a partir del codi de llibre. El mateix passa amb el país.
Així doncs, tindrem que segmentar relacions per tal que es compleixi aquesta premisa, quedant així:
PRESTECS2(num_soci, nom_soci, cod_lIibre, data_prèstec
LLIBRES (cod_llibre, editorial, pais)
Per passar a 3FN cal eliminar les depedències transitives. En trobem a llibres (obtenim país a partir d'editorial)
Aixó doncs, redefinim:
LLIBRES2( cod_llibre, editorial)
EDITORIALS(editorial, país)
Ja tenim 3FN però no FNBC, donat que PRESTÈCS2 nom_soci y num_soci es repeteixen per cada soci i llibre.
Per solucionar aquesta restricció cal treure aquest atribut de la relació PRESTECS2 i fer una nova relació:
SOCI(num_soci, nom_soci)
Ara ja tenim la nostra relació en FNBC!
Codi: I71 Exercici d'avaluació contínua 2 Pàgina 8 de 10
Versió: 02 2251M02_EAC2_Enunciat_1213S2Lliurament: 21/3/2013
Formació professionalNom i cognoms
Codi: I71 Exercici d'avaluació contínua 2 Pàgina 9 de 10
Versió: 02 2251M02_EAC2_Enunciat_1213S2Lliurament: 21/3/2013
Formació professionalNom i cognoms
4. Construïu totes les representacions tabulars resultants d'aplicar el procés de normalització, i distribuïu les dades de la taula original segons correspongui.
SOCI
Num_soci Nom_Soci
C005 Pepe Pérez
C540 Rosa Sánchez
EDITORIALS
Nom_editorial País
Platz Alemanya
Planeta Espanya
Peperoncino Itàlis
LLIBRES2
Cod_llibre Editorial
44H Planeta
PG57 Platz
L23 Peperoncino
55H Planeta
PRÈSTECS3
num_soci cod_llibre data_prèstec
C005 44H 11/10/12
C005 PG57 01/01/09
C540 44H 09/09/12
C540 L23 01/02/13
C540 44H 12/12/12
C540 55H 13/01/13
Codi: I71 Exercici d'avaluació contínua 2 Pàgina 10 de 10
Versió: 02 2251M02_EAC2_Enunciat_1213S2Lliurament: 21/3/2013