Caso Práctico 3


El departamento de Postgrado e investigación del Tecnológico Virtual ha diseñado una base de datos (BD) para la gestión de los proyectos de investigación en los que participan sus investigadores.


En la entidad Línea se almacenarán los distintos planes o programas a los que pueden pertenecer los proyectos de investigación. Para cada Línea se almacenará el código del mismo, el nombre completo de la línea, y la entidad que lo financia. Algunos proyectos podrían no tener fuente de financiamiento.


Entidad Linea
Atributo Tipo Llave
idLinea CHAR(20) PK
nombreLinea CHAR(100)  
FinanciadoPor CHAR(30)  


Por ejemplo, un plan podría ser el VER-MCCC-2008-223, cuyo nombre completo es Estudios de modelos técnicos de NP completos en el Laboratorio Nacional de GRIDS de super cómputo, utilizando algoritmos evolutivos de optimización con técnicas de procesamiento distribuido, cuya entidad financiadora es CONACYT (Consejo Nacional de Ciencia y Tecnología).


En la entidad Proyecto se almacenarán los proyectos en los que participan los distintos investigadores. Cada proyecto de investigación estará asociado a una línea de investigación. Tendremos el código del proyecto, el nombre completo del proyecto, la fecha de inicio del mismo y la de finalización. Además se almacenará en esta tabla el presupuesto concedido a cada proyecto.


Entidad Proyecto
Atributo Tipo Llave

idProyecto

CHAR(20) PK
idLinea CHAR(20) FK
nombreProyecto CHAR(100)  
fInicio DATE  
fTerminacion DATE  
Presupuesto NUMBER(10)  

En la entidad DepAcademico se almacenara el nombre del departamento académico y su identificador.


Entidad DepAcademico
Atributo Tipo Llave
idDepartamento NUMBER PK
nombreDepartamento CHAR(30)  

En la entidad investigadores se almacenan los datos personales de los investigadores que participan en los distintos proyectos de investigación. Se almacenará el IFE, el nombre, el departamento donde laboran, su teléfono de contacto y email.



Entidad Investigador
Atributo Tipo Llave
IFE NUMBER(13) PK

nombreInvestigador

CHAR(50)  
idDepartamento NUMBER FK
telefono CHAR(10)  
email CHAR(50)  

En la entidad Asignado se almacenará qué investigadores trabajan en qué proyectos. Las claves alternas estarán formadas por el IFE del investigador y el idProyecto. Un investigador una vez que abandona el proyecto no puede reincorporarse al mismo proyecto posteriormente.


También se indicará el periodo en el que un determinado investigador trabaja en un proyecto de investigación por medio de los atributos fInicio y fTermino. El atributo tipoParticipacion indicará el papel que juega cada investigador en cada proyecto. Podrá tomar los siguientes valores: lider, investigador a tiempo completo, investigador a tiempo parcial, externo, becario, etc.


Entidad Asignado
Atributo Tipo Llave
IFE NUMBER(13) FK

idProyecto

CHAR(20) FK
fInicio DATE  
fTermino DATE  
tipoParticipacion CHAR(20)  

Se presenta a continuación el diagrama Entidad Relación propuesto mismo que es perfectible. Si lo hace justique sus cambios.



Consultas solicitadas:


C01. Nombre de los investigadores del departamento de Sistemas y el nombre de los proyectos en los que trabajan.


SELECT	I.nombreInvestigador AS nInvestigador,
        P.nombreProyecto     AS "Nombre Proyecto",
        D.departamento       AS Departamento 
FROM	proyecto     P,
        asignado     A,
        investigador I,
        depAcademico D 
WHERE   D.departamento   = 'Sistemas y Computación'
  AND    D.idDepartamento = I.idDepartamento
  AND	  I.IFE            = A.IFE
  AND    A.idProyecto     = P.idProyecto
ORDER BY nInvestigador, P.nombreProyecto;


C02. Nombre de lideres de proyectos sin concluir y fecha de inicio.


SELECT	 D.departamento, I.nombreInvestigador,
        P.nombreProyecto,
        TO_CHAR(P.fInicio, 'YYYY/MM/DD') AS Inicio 
FROM	 proyecto P, asignado A,
         depAcademico D, investigador I 
WHERE  A.tipoParticipacion = 'LIDER' 
   AND  P.fTerminacion IS NULL
   AND  D.idDepartamento = I.idDepartamento
   AND	 I.IFE = A.IFE  
   AND	 A.idProyecto = P.idProyecto
ORDER BY nombreInvestigador


C03. Nombre de los proyectos en los que trabaja el investigador de "TIGER WOODS".


SELECT	 D.departamento,
	     I.nombreInvestigador,
	     P.nombreProyecto,
	     P.fInicio Inicio
FROM	 proyecto     P,
	     asignado     A,
	     depacademico D,
	     investigador I
WHERE  I.nombreInvestigador = 'TIGER WOODS' 
  AND  D.idDepartamento = I.idDepartamento
  AND  I.IFE = A.IFE
  AND  A.idProyecto = P.idProyecto


C04. Nombre y teléfono de los lideres de proyecto que trabajen en proyectos sin presupuesto.


SELECT	 I.nombreInvestigador,
         I.telefono,
         P.nombreProyecto 
FROM	 asignado     A,
         proyecto     P,
         investigador I
WHERE   P.presupuesto = 0
  AND   A.tipoParticipacion = 'LIDER' 
  AND   I.IFE = A.IFE
  AND	 A.idProyecto = P.idProyecto


C05. Nombre de los proyectos que hayan comenzado este año.


SELECT	 nombreProyecto "Nombre Proyecto", 
        TO_CHAR(fInicio,'YYYY/MM/DD') "Fecha Inicio"
FROM	 proyecto
WHERE   TO_CHAR(fInicio,'YYYY') = TO_CHAR(SYSDATE,'YYYY')
  AND   fInicio < SYSDATE
ORDER BY nombreProyecto


C06. Nombre de los Lideres de los proyectos cuya entidad financiadora sea “CONACYT”.


SELECT	 I.nombreInvestigador, P.nombreProyecto 
FROM	 asignado A, proyecto P, investigador I,
         linea L
WHERE   P.presupuesto > 0
  AND    L.financiadoPor = 'CONACYT'
  AND    L.idLinea = P.idLinea 
  AND    A.tipoParticipacion = 'LIDER' 
  AND    I.IFE = A.IFE
  AND	 A.idProyecto = P.idProyecto
ORDER BY nombreInvestigador;


C07. Nombre de las líneas cuyos proyectos tengan un presupuesto global superior a $100, 000.


SELECT L.nombreLinea, 
       TO_CHAR(P.sPresupuesto,'999,999,999') sPresupuesto
FROM linea L,
     (SELECT idLinea, SUM(presupuesto) sPresupuesto
      FROM	 proyecto
      GROUP BY idLinea ) P
WHERE (P.sPresupuesto > 100000) AND (L.idLinea = P.idLinea)


C08. Nombre de aquellos investigadores que hayan participado y culminado al menos un proyecto.


SELECT I.nombreInvestigador,
       P.nombreProyecto "Nombre Proyecto",
       P.fInicio Inicio,
       P.fTerminacion "Culmino"
FROM investigador I,
     (SELECT I.IFE, P.nombreProyecto,
             P.fInicio, P.fTerminacion
      FROM  proyecto P, asignado A,
            investigador I
      WHERE NOT (P.fTerminacion IS NULL)
       AND   NOT (A.tipoParticipacion = 'BECARIO')
       AND   P.idProyecto = A.idProyecto
       AND   I.ife = A.ife
     ) P
WHERE I.IFE = P.IFE;


C09. Obtener el nombre de los proyectos que ya concluyeron.


SELECT nombreProyecto "Nombre Proyecto",
       fInicio "Fecha Inicio",
       fTerminacion "Culmino" 
FROM proyecto
WHERE NOT (fTerminacion IS NULL)


C10. Obtener el nombre de los proyectos que están activos.


SELECT  nombreProyecto  "Nombre Proyecto",
        fInicio         "Fecha Inicio" 
FROM    proyecto
WHERE  fTerminacion IS NULL
  AND  fInicio <= SYSDATE
ORDER  BY fInicio, nombreProyecto;


C11. Obtener el nombre de los investigadores que participan en proyectos que tienen un presupuesto superior o igual al promedio de financiamiento de los proyectos con financiamiento.


SELECT nombreProyecto "Nombre Proyecto", I.nombreInvestigador,
       TO_CHAR(presupuesto,'9,999,999') Presupuesto 
FROM proyecto P, asignado A, investigador I
WHERE (presupuesto >= ( SELECT AVG(presupuesto)
                          FROM  proyecto
                         WHERE presupuesto > 0
                      ))
  AND (P.idProyecto = A.idProyecto)
  AND (A.IFE = i.IFE)
ORDER BY I.nombreInvestigador, Presupuesto, nombreProyecto;


C12. Obtener el nombre de los investigadores que nunca han participado en proyectos sin financiamiento.


SELECT nombreInvestigador AS "Nombre Investigador"
FROM   investigador I
WHERE (I.IFE NOT IN ( SELECT DISTINCT(A.IFE)
                      FROM   proyecto P, asignado A,
                             investigador I  
                      WHERE  presupuesto = 0 
                         AND  A.idProyecto = P.idProyecto
                         AND  I.IFE = A.IFE
					)
	   )
   AND (I.IFE IN ( SELECT DISTINCT(A.IFE)
                   FROM   proyecto P, asignado A,
                          investigador I  
                   WHERE  A.idProyecto = P.idProyecto
                     AND  I.IFE = A.IFE
                  )
	   )
ORDER  BY nombreInvestigador;


C13. Obtener el nombre de los investigadores que nunca han sido lideres de proyecto.


SELECT nombreInvestigador "Nombre Investigador"
FROM Investigador I
WHERE I.IFE NOT IN (
                     SELECT DISTINCT(IFE)
                     FROM Asignado
                     WHERE tipoParticipacion = 'LIDER'
                   )
ORDER BY nombreInvestigador;


C14. Obtener el nombre y el email de los investigadores que participan en proyectos sin concluir.


SELECT	 I.nombreInvestigador, I.email,
	     P.nombreProyecto,
         TO_CHAR(P.fInicio,'YYYY-MM-DD') Inicio,
         A.tipoParticipacion 
FROM	 proyecto P, asignado A, investigador I 
WHERE    NOT (A.tipoParticipacion = 'BECARIO')
   AND   P.fTerminacion IS NULL
   AND   I.IFE = A.IFE
   AND   A.idProyecto = P.idProyecto
ORDER BY nombreInvestigador

C15. Obtener el nombre de todos los proyectos que no están lideriados por investigadores que se apelliden "Del Moral".


SELECT P.nombreProyecto, I.nombreInvestigador
FROM   investigador I, asignado A, proyecto P
WHERE  NOT(nombreInvestigador LIKE '%CASTILLO%')
AND    A.TipoParticipacion = 'LIDER'
AND    A.idProyecto = P.idProyecto
AND    I.IFE = A.IFE

C16. Obtener el nombre y los email de los investigadores que no participan o nunca han participado en un proyecto de investigación.


SELECT nombreInvestigador "Nombre Investigador" 
FROM   investigador I
WHERE I.IFE NOT IN 
      (SELECT DISTINCT(A.IFE)
       FROM   proyecto P,
              asignado A,
              investigador I 
       WHERE  A.idProyecto = P.idProyecto
       AND    I.IFE = A.IFE
       )
ORDER  BY nombreInvestigador

C17. Obtener el nombre de los becarios que participan en proyectos no concluidos.


SELECT I.nombreInvestigador, P.nombreProyecto, 
       TO_CHAR(P.fInicio,'YYYY/MM/DD') Inicio
FROM   asignado A, proyecto P, investigador I 
WHERE  P.fTerminacion IS NULL
AND    A.tipoParticipacion = 'BECARIO'
AND    A.idProyecto = P.idProyecto
AND    I.IFE = A.IFE

C18. Obtener el nombre de los investigadores que únicamente hayan sido lideres de proyectos.


SELECT nombreInvestigador "Nombre Investigador"
FROM   investigador I
WHERE I.IFE IN ( SELECT DISTINCT(IFE)
                 FROM   asignado
                 WHERE tipoParticipacion = 'LIDER'
			   )
ORDER BY nombreInvestigado

C19. Obtener todos los nombres de los proyectos que hayan comenzado despues de enero del 2008 y que todavía no concluyan, pero sin incluir proyectos con financiamiento.


SELECT	nombreProyecto "Nombre Proyecto", 
        TO_CHAR(fInicio,'YYYY/MM/DD') Inicio 
FROM	proyecto
WHERE   fTerminacion IS NULL
AND     TO_CHAR(fInicio,'YYYY/MM/DD') 
        >= '2010/01/01'
AND     presupuesto = 0
ORDER BY nombreProyecto;  

C20. Obtener el nombre de todos los participantes en proyectos sin concluir.


 SELECT  I.nombreInvestigador, P.nombreProyecto, 
         TO_CHAR(P.fInicio,'YYYY-MM-DD') Inicio,
         A.tipoParticipacion 
 FROM	 proyecto P, asignado A, investigador I 
 WHERE   P.fTerminacion IS NULL
 AND     I.IFE = A.IFE
 AND     A.idProyecto = P.idProyecto
 ORDER BY nombreInvestigador




Anterior

Valid XHTML 1.0 Transitional


home


Siguiente