Facultad de Informática UCM - Examen Parcial – Convocatoria de Febrero Curso 2009/2010 – Grupo A

Bases de Datos y Sistemas de la Información SOLUCIÓN Ejercicio 1: 2 puntos  a) Se desea diseñar un diagrama Entidad‐Relación  para representar fracciones. Cada fracción  queda  determinada  por  dos  números  enteros,  el  numerador  y  el  denominador.  Algunas  fracciones  son  las  fracciones  reducidas  de  otras.  Por  ejemplo,  1/4  es  la  fracción  reducida  de  6/24.  Se  verifica  que  todo  número  racional  tiene  una  única  fracción  reducida  (sólo  se  llama  fracción  reducida  a  la  que  ya  está  totalmente  simplificada).  Además  de  conocer  la  fracción  reducida asociada a cada fracción, interesa saber el factor de reducción asociado (en el caso de  6/24 y ¼ el factor es 6).  Nota: Dos fracciones se deben considerar diferentes si tienen el numerador o el denominador  diferentes, aunque correspondan a la misma fracción reducida.  Solución 

  b) Pasar el diagrama anterior a tablas SQL que reflejen el diagrama con sus restricciones.  Solución 

create table fraccion ( numerador integer, denominador integer,

primary key(numerador,denominador) ); create table seReduceA( numerador integer, denominador integer, numeradorReduc integer NOT NULL, denominadorReduc integer NOT NULL, factor integer NOT NULL, primary key (numerador,denominador), foreign key (numerador,denominador) references fraccion, foreign key (numeradorReduc,denominadorReduc) references fraccion );   Notas sobre las tablas:  - Se  puede  añadir  la  restricción  NOT  NULL  a  las  claves,  aunque  no  sea  estrictamente  necesario.  - Se  puede  añadir  cláusulas  ON  DELETE  CASCADE  y  ON  UPDATE  CASCADE,  pero  no  es  imprescindible (es una cuestión de diseño no aclarada en el enunciado). 

Ejercicio 2: 6 puntos   La prestigiosa red social WebOn permite a cada usuario definir el nivel de relación con otros  usuarios. La red se basa en un sencillo esquema con 2 tablas:  usuario(nick, nombre, edad) relación(nick1, nick2, nivel) Los valores nick1, nick2 son clave ajena con usuario. El nivel es el valor numérico que indica el  tipo  de  relación,  y  puede  variar  entre  ‐10  (aborrecimiento  absoluto  de  nick1  a  nick2)  a  +10  (amor eterno de nick1 a nick2). Escribe consultas en SQL para:  1

Mostrar  un  listado  de  parejas  unilaterales,  es  decir  dos  nicks  n1,  n2  tales  que  la  tabla relación contiene una tupla (n1,n2,a), pero ninguna de la forma (n2,n1,b). Un  ejemplo de salida:  Nick1  

 

Nick2 

Bertoldo    

Herminia 

Aniceto 

Cucufato 

 

…  Solución:  Select nick1, nick2 from relación R1  

2

where not exists (select * from relación R2         where R2.nick1 = R1.nick2 and R2.nick2 = R1.nick1);  Escribir la consulta anterior en álgebra relacional.  Solución:  R1 Å Π(nick1,nick2) relación  R2 Å Π(nick1,nick2) relación 

3

Bilaterales Å R1 ZY  (ρ(nick2,nick1)(R2))  Unilaterales ÅR1 \  Bilaterales     El  número  de  relaciones  de  un  usuario  de  Nick  n1  es  el  número  de  tuplas  de  la  forma (n1,n2,a)  que existen en relación para valores n2, a cualesquiera. Para cada  usuario mayor de 26 años y con 100 o más relaciones indicar su nick junto con el  nivel medio de todas sus relaciones (la media del campo nivel para dicho usuario).  Solución: 

         Select nick1, AVG(nivel)  from usuario, relación  where usuario.nick = relacion.nick1 and edad>26   group by usuario.nick  having count(*) >= 100;    Otra posibilidad, con una vista auxiliar  Create view numRel(nick,n, media) as    Select nick1,count(*), AVG(nivel) from relación group by nick1;    Select numRel.nick, numRel.media  From usuarios,numRel   Where usuarios.nick=numRel.nick and usuarios.edad>26 and numRel.n>=100;    4

Indicar nombres de los usuarios con menos relaciones.  Solución: 

5

Utilizando la vista del ejercicio anterior:  Select usuarios.nombre  From usuarios, numRel   Where usuarios.nick=numRel.nick and        numRel.n = (select min(n) from numRel);    Escribir  una  instrucción  SQL  para  eliminar  todos  los  elementos  de  relación  que  sean autorelaciones, es decir que sean de la forma (a,a,n) para algún a.  Solución: 

Delete from relacion where nick1=nick2;  6

Escribir una consulta que para cada usuario muestre el número de relaciones a las  que ha asignado niveles negativos, mayores que 0 y exactamente 0. El aspecto de  esta consulta será:  Nick  

Negativos  

Mayores que 0   

Igual a 0 

Herminia  56 

 

45 

 

 

3 

Bertoldo  84 

 

0 

 

 

1 

………..  Solución:  Select nick, sum(If(nivel0,1,0)) ‘Mayores que 0’,                         sum(If(nivel=0,1,0)) ‘Igual a 0’  from usuarios,relación  where usuarios.nick = relación.nick1  group by relacion.nick;   

Ejercicio 3: 2 puntos  Partimos de una relación R(A,B,C,D) con dependencias S = { ABCÆD, A Æ BC, CÆB, BDÆA }  1 [1 punto] Obtener un recubrimiento mínimo para S.   Solución  Recubrimiento mínimo:  1.‐ Lados derechos unitarios. S ={ ABCÆD, A Æ B, AÆC, CÆB, BDÆA }  2.‐ Atributos redundantes:      ‐ ¿Sobra A en ABCÆD? = ¿se cumple BCÆD en S? = ¿está D en {B,C}+S?   {B,C}+S= {B,C}. Por tanto no sobra  ‐ ¿Sobra B en ABCÆD? = ¿se cumple ACÆD en S? = ¿está D en {A,C}+S?   {A,C}+S= {A,C,B,D}. Por tanto sí sobra. Obtenemos un nuevo S:  S ={ ACÆD, A Æ B, AÆC, CÆB, BDÆA }  ‐ ¿Sobra C en ACÆD? = ¿se cumple AÆD en S? = ¿está D en {A}+S?   {A}+S= {A,B,C,D} Sí sobra. Obtenemos un nuevo S:  S ={ AÆD, A Æ B, AÆC, CÆB, BDÆA }  ‐ ¿Sobra B en BDÆA? = ¿se cumple DÆA en S? = ¿está A en {D}+S?   {D}+S= { D} No sobra.   ‐ ¿Sobra D en BDÆA? = ¿se cumple BÆA en S? = ¿está A en {B}+S?   {B}+S= { B} No sobra.    Al final de este paso: S = { AÆD, A Æ B, AÆC, CÆB, BDÆA }   

2

3

3.‐ Dependencias redundantes:  ‐ ¿Sobra AÆD? = ¿Se cumple AÆD en (S\{AÆD})?  {A}+S\{AÆD} = {A,B,C} No contiene a D y por tanto no sobra  ‐ ¿Sobra AÆB? = ¿Se cumple AÆB en (S\{AÆB})?   {A}+S\{AÆB} = {A,D,C,B} como incluye a B sí que sobra.  S = { AÆD, AÆC, CÆB, BDÆA }  ‐ ¿Sobra AÆC? = ¿Se cumple AÆC en (S\{AÆC})?  {A}+S\{AÆC} = {A,D }. No sobra.  ‐ ¿Sobra CÆB? = ¿Se cumple CÆB en (S\{CÆB})?  {C}+S\{CÆB} = {C }. No sobra.  ‐ ¿Sobra BDÆA? = ¿Se cumple BDÆA en (S\{BDÆA})?  {B,D}+S\{BDÆA} = {B,D }. No sobra.    Recubrimiento mínimo:  S = { AÆD, AÆC, CÆB, BDÆA }    [0.5]  Indica  en  qué  forma  normal  se  encuentra  R  con  respecto  a  estas  dependencias  funcionales  Solución:  Comenzamos por buscar las claves candidatas.  Con  un  atributo  sólo  encontramos  A,  ya  que  {A}+  =  {A,D,C,B}  es  decir  todos  los  atributos.  Con  dos  atributos  debemos  buscar  un  conjunto  cuyo  cierre  sea  {A,B,C,D}  y  que  no  contenga A. Encontramos {B,D}, y {C,D}.   Con 3 atributos sin incluir A ni tampoco {B,D} ni {B,C} no hay ninguna posibilidad.    No está en FNBC porque en CÆB la parte izquierda no es superclave.   Está en 3FN porque en todas salvo en CÆB la parte izq. Es superclave, mientras que en  CÆB la parte derecha es parte de una clave candidata.    [0.5 ]Encuentra una instancia de r de R tal que:  a. Sea válida desde el punto de vista de las claves candidatas.  b. No sea válida con respecto al conjunto S.    Solución:  A  B  C   D  1  2  3  4  6  5  3  7  Esta instancia cumple las restricciones dadas por las claves {A}, {B,C}, {B,D}, pero no la  restricción CÆB, ya que se repite C (3) pero no B (valores 2 y 5).