/* @BD_Univ_ej_Feb13 */
-- implementa algunas soluciones a las preguntas SQL de Feb13

-- habilitar si se quiere que muestre el plan de ejecución de las consultas
-- SET AUTOTRACE ON

-- para quitar los espacios al final de la línea
SET trimspool ON

-- para truncar las filas (si no cabe)
SET wrap off
 
SET linesize 132
SET pagesize 66
SET echo ON
column clvProf    heading "idProf"             format 999;
column nombProf   heading "Profesor"           format a20 trunc;
column clvAsign   heading "idAsig"             format 999;
column nombAsign  heading "Asignatura"         format a20 trunc;
/*==========================================================================================*/
/* insertar algunos datos para probar
*/
INSERT INTO Profesor     VALUES (7, 'P_7', 'profesor_7', 5);
INSERT INTO Asignatura   VALUES (9, 'A_9', 'pa borrar', 10, 10, 5);
INSERT INTO AsignTitulo  VALUES (9, 3);
INSERT INTO ImparteAsign VALUES (7, 2, 5, 20);
INSERT INTO ImparteAsign VALUES (7, 5, 15, 15);
INSERT INTO ImparteAsign VALUES (1, 9, 15, 15);
INSERT INTO ImparteAsign VALUES (2, 9, 5, 6);
/*                                                                                       (a.1)
  ############################################################################################
  Eliminar el área de conocimiento de clave 5, así como la información necesaria para mantener
  en todo momento la integridad de la base de datos.
  ##########################################################################################*/
DELETE FROM ImparteAsign WHERE clvProf  IN (SELECT clvProf FROM Profesor WHERE clvArea = 5)
                            OR clvAsign IN (SELECT clvAsign FROM Asignatura WHERE clvArea = 5);
DELETE FROM AsignTitulo  WHERE clvAsign IN (SELECT clvAsign FROM Asignatura WHERE clvArea = 5);
DELETE FROM Profesor     WHERE clvArea = 5;
DELETE FROM Asignatura   WHERE clvArea = 5;
DELETE FROM AreaConoc    WHERE clvArea = 5;
/*==========================================================================================*/
/* deshacer los cambios realizados */
INSERT INTO AreaConoc VALUES (5, 'AC_5', 'LENGUAJES', 3);
/*==========================================================================================*/
/* insertar algunos datos para probar
*/
INSERT INTO Profesor     VALUES (7, 'P_7', 'profesor_7', 5);
INSERT INTO Asignatura   VALUES (9, 'A_9', 'pa borrar', 10, 10, 5);
INSERT INTO AsignTitulo  VALUES (9, 3);
INSERT INTO ImparteAsign VALUES (7, 2, 5, 20);
INSERT INTO ImparteAsign VALUES (7, 5, 15, 15);
INSERT INTO ImparteAsign VALUES (1, 9, 15, 15);
INSERT INTO ImparteAsign VALUES (2, 9, 5, 6);
/*                                                                                       (a.2)
  ##########################################################################################*/
CREATE VIEW AsignElim AS (SELECT clvAsign FROM Asignatura WHERE clvArea = 5);
CREATE VIEW ProfElim  AS (SELECT clvProf  FROM Profesor   WHERE clvArea = 5);
DELETE FROM ImparteAsign WHERE clvProf  IN (SELECT * FROM ProfElim)
                            OR clvAsign IN (SELECT * FROM AsignElim);
DELETE FROM AsignTitulo  WHERE clvAsign IN (SELECT * FROM AsignElim);
DELETE FROM Profesor     WHERE clvArea = 5;
DELETE FROM Asignatura   WHERE clvArea = 5;
DELETE FROM AreaConoc    WHERE clvArea = 5;
DROP VIEW AsignElim;
DROP VIEW ProfElim;
/*==========================================================================================*/
/* deshacer los cambios realizados */
INSERT INTO AreaConoc VALUES (5, 'AC_5', 'LENGUAJES', 3);
/*==========================================================================================*/
/* insertar algunos datos para probar
*/
-- INSERT INTO ImparteAsign VALUES (6, 4, 3, 4);

/* Asignaturas impartidas por algun prof de un area distinta */
SELECT A.clvAsign, nombAsign, A.clvArea
FROM   Asignatura A, ImparteAsign IA, Profesor P
WHERE  A.clvAsign = IA.clvAsign AND IA.clvProf = P.clvProf AND P.clvArea <> A.clvArea
ORDER BY A.clvAsign;
 
/*                                                                                      (b.1)
  ##########################################################################################
  Listado de las titulaciones que tienen al menos una asignatura en la que imparte docencia
  algún profesor de un área distinta de la que tiene encargada la docencia.
  ##########################################################################################*/

SELECT DISTINCT clvTitulo
FROM  AsignTitulo AT
WHERE clvAsign IN (SELECT A.clvAsign
                   FROM Asignatura A, ImparteAsign IA, Profesor P
                   WHERE A.clvAsign = IA.clvAsign AND IA.clvProf = P.clvProf
                     AND P.clvArea <> A.clvArea);
/*                                                                                      (b.2)
  ##########################################################################################*/

SELECT DISTINCT clvTitulo
FROM  Titulacion T
WHERE EXISTS (SELECT * FROM Asignatura A
              WHERE EXISTS (SELECT * FROM Profesor P, ImparteAsign IA
                            WHERE P.clvProf = IA.clvProf AND IA.clvAsign = A.clvAsign
                              AND P.clvArea <> A.clvArea)
                AND A.clvAsign IN (SELECT clvAsign FROM AsignTitulo AT
                                   WHERE  AT.clvTitulo = T.clvTitulo));
/*                                                                                      (b.3)
  ##########################################################################################*/

SELECT DISTINCT clvTitulo
FROM  Titulacion T
WHERE EXISTS (SELECT * FROM Asignatura A
              WHERE (A.clvAsign, T.clvTitulo) IN (SELECT * FROM AsignTitulo)
                AND EXISTS (SELECT * FROM Profesor P
                            WHERE (clvProf,A.clvAsign) IN (SELECT clvProf,clvAsign
                                                           FROM ImparteAsign)
                              AND P.clvArea <> A.clvArea));
/*                                                                                      (b.4)
  ##########################################################################################*/

SELECT DISTINCT clvTitulo
FROM  Titulacion T
WHERE EXISTS(SELECT * FROM Asignatura A
             WHERE EXISTS(SELECT * FROM Profesor P
                          WHERE EXISTS(SELECT * FROM ImparteAsign IA
                                       WHERE IA.clvProf = P.clvProf AND IA.clvAsign = A.clvAsign)
                            AND P.clvArea <> A.clvArea)
               AND A.clvAsign IN (SELECT clvAsign FROM AsignTitulo AT
                                  WHERE  AT.clvTitulo = T.clvTitulo));
/*                                                                                      (b.5)
  ##########################################################################################*/

SELECT DISTINCT clvTitulo
FROM  AsignTitulo AT, Asignatura A, ImparteAsign IA, Profesor P
WHERE AT.clvAsign = A.clvAsign AND A.clvAsign = IA.clvAsign
  AND IA.clvProf  = P.clvProf  AND P.clvArea <> A.clvArea;

/*==========================================================================================*/
/* deshacer los cambios realizados */
-- DELETE FROM ImparteAsign WHERE (clvProf,clvAsign) IN ((6,4));
/*==========================================================================================*/
/* insertar alguna tupla para test */
INSERT INTO ImparteAsign VALUES (6, 6, 5, 20);

/* Profesores y titulaciones en que imparten docencia */
SELECT DISTINCT clvProf, clvTitulo FROM ImparteAsign IA, AsignTitulo AT
 WHERE IA.clvAsign = AT.clvAsign;

/* Areas y titulaciones en que tienen encargada docencia */
SELECT DISTINCT clvArea, clvTitulo FROM Asignatura A, AsignTitulo AT WHERE A.clvAsign=AT.clvAsign;

/* Profesores y titulaciones en que tiene encargada docencia su área */
SELECT DISTINCT clvProf, clvTitulo  FROM Profesor P, Asignatura A, AsignTitulo AT
 WHERE P.clvArea = A.clvArea AND A.clvAsign = AT.clvAsign;

/* profesores y titulaciones en que imparten docencia y no tiene encargada su área */
SELECT DISTINCT clvProf, clvTitulo  -- profesores y titulaciones que imparten
 FROM ImparteAsign IA, AsignTitulo AT WHERE IA.clvAsign = AT.clvAsign
MINUS
SELECT DISTINCT clvProf, clvTitulo  -- profesores y titulaciones en que tiene encargo su área
 FROM Profesor P, Asignatura A, AsignTitulo AT
 WHERE P.clvArea = A.clvArea AND A.clvAsign = AT.clvAsign;
/*==========================================================================================*/
/*                                                                                       (c.1)
  ############################################################################################
  Profesores que imparten docencia en alguna titulación en la que no tiene encargada docencia
  su área de conocimiento. No se usarán los operadores NOT, ni EXISTS.
  ##########################################################################################*/
/* vista con profesores y titulaciones en que imparten docencia sin encargo a su área */
CREATE VIEW ProfTitSinEncArea AS
 SELECT DISTINCT clvProf, clvTitulo -- profesores y titulaciones que imparten
  FROM ImparteAsign IA, AsignTitulo AT
  WHERE IA.clvAsign = AT.clvAsign
 MINUS
 SELECT clvProf, clvTitulo          -- profesores y titulaciones en que tiene encargo su área
  FROM Profesor P, Asignatura A, AsignTitulo AT
  WHERE P.clvArea = A.clvArea AND A.clvAsign = AT.clvAsign;

SELECT clvProf FROM ProfTitSinEncArea;

DROP VIEW ProfTitSinEncArea;
/*                                                                                       (c.2)
  ##########################################################################################*/
SELECT clvProf FROM (
 SELECT DISTINCT clvProf, clvTitulo -- profesores y titulaciones que imparten
  FROM ImparteAsign IA, AsignTitulo AT
  WHERE IA.clvAsign = AT.clvAsign
 MINUS
 SELECT clvProf, clvTitulo          -- profesores y titulaciones en que tiene encargo su área
  FROM Profesor P, Asignatura A, AsignTitulo AT
  WHERE P.clvArea = A.clvArea AND A.clvAsign = AT.clvAsign);
/*                                                                                       (c.3)
  ##########################################################################################*/
SELECT clvProf
FROM ImparteAsign IA, AsignTitulo AT
  WHERE IA.clvAsign = AT.clvAsign
    AND (clvProf, clvTitulo) IN (SELECT DISTINCT clvProf, clvTitulo
                                  FROM ImparteAsign IA, AsignTitulo AT
                                   WHERE IA.clvAsign = AT.clvAsign
                                 MINUS
                                 SELECT clvProf, clvTitulo
                                  FROM Profesor P, Asignatura A, AsignTitulo AT
                                  WHERE P.clvArea = A.clvArea AND A.clvAsign = AT.clvAsign);

/*                                                                                       (c.4)
  ##########################################################################################*/
SELECT P.clvProf
FROM Profesor P, ImparteAsign IA, AsignTitulo AT
  WHERE P.clvProf = IA.clvProf AND IA.clvAsign = AT.clvAsign
    AND clvTitulo NOT IN (SELECT clvTitulo FROM Asignatura A, AsignTitulo AT
                           WHERE P.clvArea = A.clvArea AND A.clvAsign = AT.clvAsign);
/*                                                                                       (c.5)
  ##########################################################################################*/
SELECT clvProf
FROM ImparteAsign IA, AsignTitulo AT
  WHERE IA.clvAsign = AT.clvAsign
    AND clvTitulo <> all (SELECT clvTitulo FROM Profesor P, Asignatura A, AsignTitulo AT
                           WHERE P.clvProf = IA.clvProf AND P.clvArea = A.clvArea
                             AND A.clvAsign = AT.clvAsign);
/*                                                                                       (c.6)
  ##########################################################################################*/
CREATE VIEW AreaTitSinEnc AS   -- areas y titulaciones en las que no tienen encargo
 SELECT clvArea, clvTitulo FROM AreaConoc AC, Titulacion T  -- todas areas y titulaciones
 MINUS
 SELECT clvArea, clvTitulo     -- areas y titulaciones en que tienen encargo
  FROM Asignatura A, AsignTitulo AT WHERE A.clvAsign = AT.clvAsign;

SELECT * FROM AreaTitSinEnc;

SELECT P.clvProf
 FROM Profesor P, ImparteAsign IA, AsignTitulo AT, AreaTitSinEnc ATS
 WHERE P.clvProf = IA.clvProf AND IA.clvAsign = AT.clvAsign
   AND P.clvArea = ATS.clvArea AND AT.clvTitulo = ATS.clvTitulo;

DROP VIEW AreaTitSinEnc;
/*==========================================================================================*/
/* deshacer los cambios introducidos */
DELETE FROM ImparteAsign WHERE (clvProf, clvAsign) IN ((6,6));
/*==========================================================================================*/