/* @probar_consultas.sql */
SET echo ON
SELECT sysdate FROM dual;

-- crear la BD de la tienda de ropa (tabla prendas)
@BD_Ropa_createBD.sql

-- añadir algunas prendas para probar
@BD_Ropa_insertData.sql

-- mostrar las tuplas de prendas
SELECT * FROM prendas;

-- insertar algunas tuplas repetidas
INSERT INTO Prendas(nombre, color, talla) VALUES('FALDA', 'ROJO', 4);
INSERT INTO Prendas(nombre, color, talla) VALUES('FALDA', 'AZUL', 5);

-- mostrar las tuplas de prendas
SELECT * FROM prendas;

-- mostrar las tuplas de prendas diferentes, y su número
SELECT prendas.*, count(*) veces FROM prendas GROUP BY nombre, color, talla;

-- mostrar las tuplas de prendas repetidas, y su número
SELECT prendas.*, count(*) veces
 FROM prendas
 GROUP BY nombre, color, talla
 HAVING count(*) > 1;

-- mostrar cuántas prendas están repetidas (sólo el número)
SELECT count(count(*)) FROM prendas GROUP BY nombre, color, talla HAVING count(*) > 1;

----------------------------------------------------------------------------------------
-- eliminar en la tabla prendas las tuplas repetidas (usando una vista)
CREATE VIEW tuplasSelec (idtupla) AS (
  SELECT min(rowid)
   FROM prendas
   GROUP BY nombre, color, talla
);

DELETE FROM prendas WHERE rowid NOT IN (SELECT idtupla FROM tuplasSelec);

DROP VIEW tuplasSelec;
----------------------------------------------------------------------------------------

-- mostrar cuántas prendas están repetidas (sólo el número)
SELECT count(count(*)) FROM prendas GROUP BY nombre, color, talla HAVING count(*) > 1;

-- insertar de nuevo algunas tuplas repetidas
INSERT INTO Prendas(nombre, color, talla) VALUES('FALDA', 'ROJO', 4);
INSERT INTO Prendas(nombre, color, talla) VALUES('FALDA', 'AZUL', 5);

-- mostrar cuántas prendas están repetidas (sólo el número)
SELECT count(count(*)) FROM prendas GROUP BY nombre, color, talla HAVING count(*) > 1;

----------------------------------------------------------------------------------------
-- eliminar en la tabla prendas las tuplas repetidas (directamente)
DELETE FROM prendas
 WHERE rowid NOT IN (SELECT min(rowid) FROM prendas GROUP BY nombre, color, talla);
----------------------------------------------------------------------------------------

-- mostrar cuántas prendas están repetidas (sólo el número)
SELECT count(count(*)) FROM prendas GROUP BY nombre, color, talla HAVING count(*) > 1;

-- comprobar si (nombre, color, talla) es una clave de prendas
SELECT '   SI    ' "es clave" FROM dual
 WHERE NOT EXISTS (
  SELECT * FROM prendas GROUP BY nombre, color, talla HAVING count(*) > 1
 )
UNION
SELECT '   NO    ' "es clave" FROM dual
 WHERE EXISTS (
  SELECT * FROM prendas GROUP BY nombre, color, talla HAVING count(*) > 1
 );



--====================================================================================== 
-- Si se quiere implementar una función parametrizada con los atributos a comprobar y la
-- tabla a utilizar, hay que utilizar SQL DINÁMICO, como se muestra a continuación:
-- 
-- La función es_CLAVE devuelve true si los atributos especificados por el primer parámetro
-- son clave de la tabla especificada por el segundo parámetro, y false en caso contrario.
CREATE OR REPLACE FUNCTION es_CLAVE (atrib IN varchar2, tabla IN varchar2)
RETURN boolean
IS
--  query_str VARCHAR2(200);
  totalRepetidas number;
BEGIN
--  query_str := 'SELECT count(count(*)) FROM '||tabla||' GROUP BY '||atrib||' HAVING count(*)>1';
--  EXECUTE IMMEDIATE query_str INTO totalRepetidas;  
  EXECUTE IMMEDIATE
   'SELECT count(count(*)) from '||tabla||' GROUP BY '||atrib||' HAVING count(*) > 1'
   INTO totalRepetidas;
--   DBMS_OUTPUT.PUT_LINE('hay '||totalRepetidas||' tuplas ('||atrib||') repetidas en '||tabla);
  RETURN totalRepetidas = 0;
END es_CLAVE;
/
SHOW ERRORS FUNCTION es_CLAVE;

-- prueba de la función implementada (no existe en SQL*PLUS el tipo boolean)
-- habilitar la salida de mensajes
SET SERVEROUTPUT ON
-- bloque PL/SQL para probar la función
BEGIN
  IF es_CLAVE('nombre, color, talla', 'prendas') THEN
    DBMS_OUTPUT.PUT_LINE('(nombre, color, talla) SI es una clave de prendas');
  ELSE
    DBMS_OUTPUT.PUT_LINE('(nombre, color, talla) NO es una clave de prendas');
  END IF;
  IF es_CLAVE('nombre, color', 'prendas') THEN
    DBMS_OUTPUT.PUT_LINE('(nombre, color) SI es una clave de prendas');
  ELSE
    DBMS_OUTPUT.PUT_LINE('(nombre, color) NO es una clave de prendas');
  END IF;
END;
/

-- eliminar la función
DROP FUNCTION es_CLAVE;

-- probar el script "tst_clave"
@tst_clave prendas "nombre, talla, color"
@tst_clave prendas "nombre, talla"
@tst_clave prendas "nombre, color"
@tst_clave prendas "talla, color"

-- obsérvese que con los datos del ejemplo también es clave, por casualidad, (talla, color)

-- para no permitir tuplas repetidas, se define la clave candidata correspondiente
-- (UNIQUE) ya que, como color y talla pueden tomar valores nulos, no es posible
-- definir una clave primaria (PRIMARY KEY)
ALTER TABLE prendas ADD CONSTRAINT prendas_UN UNIQUE(nombre, color, talla);

/*
  OBTENCIÓN DE DEPENDENCIAS FUNCIONALES
*/
-- como prueba previa para ver si nombre -> color se van a obtener las prendas de las que
-- hay más de 1 color, junto con el número de colores hay de cada prenda
SELECT nombre, count(*) veces
 FROM prendas
 GROUP BY nombre
 HAVING count(*) > 1;

-- se observa que la consulta anterior no proporciona el resultado deseado porque
-- hay pares de valores (nombre, color) repetidos.
-- Se repite la prueba contando los colores que hay de cada prenda
SELECT nombre, count(DISTINCT color) veces
 FROM prendas
 GROUP BY nombre;
 
-- obsérvese que no se cuentan los colores con valor NULL
-- comprobar las diferentes opciones de contar
SELECT nombre, count(*), count(color), count(DISTINCT color)
FROM prendas
GROUP BY nombre;

-- para ver los pares de valores (nombre, color) repetidos
SELECT nombre, color, count(*) veces
 FROM prendas
 GROUP BY nombre, color
 HAVING count(*) > 1;

-- valores de nombre para los que no se cumple la DF
-- aplicando directamente la definición de DF
SELECT DISTINCT nombre FROM prendas P1
 WHERE EXISTS (
   SELECT * FROM prendas P2
     WHERE P2.nombre = P1.nombre
	   AND (P2.color <> P1.color
	    OR P2.color IS NULL AND P1.color IS NOT NULL
	    OR P2.color IS NOT NULL AND P1.color IS NULL)
 );

-- también se podrían obtener con un auto-join (puede ser más eficiente)
-- valores de nombre para los que no se cumple la DF
SELECT DISTINCT P1.nombre
 FROM prendas P1, prendas P2
 WHERE P1.nombre = P2.nombre
   AND (P1.color <> P2.color
	OR P2.color IS NULL     AND P1.color IS NOT NULL
	OR P2.color IS NOT NULL AND P1.color IS NULL);

 -- contar los nombres de prenda para los que no se cumple la DF
SELECT count(DISTINCT P1.nombre) "nombres fallan"
 FROM prendas P1, prendas P2
 WHERE P1.nombre = P2.nombre
   AND (P1.color <> P2.color
	OR P2.color IS NULL     AND P1.color IS NOT NULL
	OR P2.color IS NOT NULL AND P1.color IS NULL);

-- para obtener las prendas que no verifican la DF (de las que hay más de un color)
-- contando directamente, hay que operar sobre los pares (nombre, color) distintos
-- para ello se creará una vista con todos los atributos que intervienen en la DF
CREATE VIEW nombre_color (nombre, color, veces) AS
 SELECT nombre, color, count(*)
 FROM prendas
 GROUP BY nombre, color;

-- valores de nombre de prenda para los que no se cumple la DF
SELECT nombre, count(*)-1 C_sobran, sum(veces)-max(veces) min_Tpl_sobran
FROM nombre_color
GROUP BY nombre
HAVING count(*) > 1;

-- para obtener paso a paso el porcentaje de tuplas que no cumplen la DF
-- se crea una vista con el número mínimo de tuplas que "fallan" (las que,
-- como mínimo, habría que quitar para que se verificase la DF)
CREATE VIEW fallos(numFallos) AS
SELECT sum(tuplas)
FROM (SELECT sum(veces)-max(veces) tuplas
       FROM nombre_color
       GROUP BY nombre);

-- se crea otra vista con el número total de tuplas que hay
CREATE VIEW totPrendas(numPrendas) AS SELECT count(*) FROM prendas;

-- finalmente se obtiene el porcentaja de tuplas que "fallan"
SELECT numFallos/numPrendas*100 "% fallos" FROM fallos, totPrendas;

-- eliminar las vistas auxiliares
DROP VIEW fallos;
DROP VIEW totPrendas;


-- obtener directamente (sin usar vistas) el porcentaja de tuplas que "fallan"
SELECT numFallos/totPrendas*100 "% fallos"
 FROM
  (SELECT sum(tuplas) numFallos
    FROM (
     SELECT sum(veces)-max(veces) tuplas
      FROM (SELECT nombre, color, count(*) veces FROM prendas GROUP BY nombre, color)
      GROUP BY nombre
    )
  ), (SELECT count(*) totPrendas FROM prendas);

-- la consulta anterior se puede guardar parametrizada con el nombre de la tabla
-- y los conjuntos de atributos que intervienen en la DF, en el fichero tst_DF_nw.sql
-- esto permite obtener los resultados para otras DF, de un modo simple y rápido 
@tst_DF_nw prendas "nombre,color" talla
@tst_DF_nw prendas nombre "color,talla"
@tst_DF_nw prendas color talla


--====================================================================================
-- estudio de dependencias multivaluadas                                            --
--==================================================================================== 
-- comprobar si, en la tabla  prendas,  nombre ->-> color es una DMV

CREATE VIEW nombresFallo AS (
  SELECT DISTINCT nombre FROM prendas PF
   WHERE EXISTS (
     SELECT talla FROM prendas TP
      WHERE nombre = PF.nombre
        AND EXISTS(
              SELECT color FROM prendas WHERE nombre = PF.nombre
              MINUS
              SELECT color FROM prendas WHERE nombre = PF.nombre AND talla = TP.talla
            )
   )
);
SELECT * FROM nombresFallo;

INSERT INTO Prendas(nombre, color, talla) VALUES('PANTALON', 'ROJO', 7);
SELECT * FROM nombresFallo;

INSERT INTO Prendas(nombre, color, talla) VALUES('FALDA', 'VERDE', 4);
SELECT * FROM nombresFallo;

-- se puede definir un disparador que garantice la integridad (mantenga la DMV)
CREATE OR REPLACE TRIGGER tst4FN
AFTER INSERT OR DELETE OR UPDATE ON prendas
DECLARE numFallos number(9);
BEGIN
 SELECT count(*) INTO numFallos FROM prendas PF
   WHERE EXISTS (
     SELECT talla FROM prendas TP
      WHERE nombre = PF.nombre
        AND EXISTS(
          SELECT color FROM prendas WHERE nombre = PF.nombre
          MINUS
          SELECT color FROM prendas WHERE nombre = PF.nombre AND talla = TP.talla
        )
   );
 IF numFallos > 0 THEN
  raise_application_error( -20501, 'violación de la DMV'); 
 END IF;
END;
/
SHOW ERRORS TRIGGER tst4FN
-- ALTER TRIGGER tst4FN DISABLE;
-- ALTER TRIGGER tst4FN ENABLE;

-- si se intenta actualizar la tabla prendas, se abortará la operación si la tabla no
-- verifica la DMV tras la operación (no se detecta que inicialmente no la verifique)
-- ejemplos:
-- la siguiente operación se abortará aunque no modifique la tabla si inicialmente no se verifica la DMV
DELETE FROM prendas WHERE nombre = 'CAMISETA';

-- la siguiente operación no dará problemas pues la tabla final verifica la DMV
DELETE FROM prendas WHERE (nombre, color, talla) IN (
 ('PANTALON', 'ROJO', 7), ('FALDA', 'VERDE', 4),
 ('PANTALON', 'AZUL', 7), ('PANTALON', 'AZUL', NULL)
);

-- eliminar el disparador (ya no se quiere usar más)
DROP TRIGGER tst4FN;

-- Obtención de las tuplas que debería haber para que se verificase la DMV (usando UNION)
CREATE OR REPLACE VIEW coloresPrenda AS (
  SELECT DISTINCT nombre, color FROM prendas WHERE color IS NOT NULL
);

CREATE OR REPLACE  VIEW tallasPrenda AS (
  SELECT DISTINCT nombre, talla FROM prendas WHERE talla IS NOT NULL
);

SELECT * FROM coloresPrenda;
SELECT * FROM tallasPrenda;

-- tuplas que debería haber
SELECT CP.nombre, color, talla
 FROM coloresPrenda CP, tallasPrenda TP WHERE CP.nombre = TP.nombre
UNION
SELECT CP.nombre, color, NULL
 FROM coloresPrenda CP WHERE nombre NOT IN (SELECT nombre FROM tallasPrenda)
UNION
SELECT TP.nombre, NULL, talla
 FROM tallasPrenda TP WHERE nombre NOT IN (SELECT nombre FROM coloresPrenda);

-- tuplas que faltan
CREATE VIEW tuplasFaltan AS (
 SELECT CP.nombre, color, talla
  FROM coloresPrenda CP, tallasPrenda TP WHERE CP.nombre = TP.nombre
 UNION
 SELECT CP.nombre, color, NULL
  FROM coloresPrenda CP WHERE nombre NOT IN (SELECT nombre FROM tallasPrenda)
 UNION
 SELECT TP.nombre, NULL, talla
  FROM tallasPrenda TP WHERE nombre NOT IN (SELECT nombre FROM coloresPrenda)
 MINUS
 SELECT nombre, color, talla FROM prendas
);

SELECT * FROM tuplasFaltan;

-- tuplas que sobran
CREATE VIEW tuplasSobran AS (
 SELECT nombre, color, talla FROM prendas
 MINUS (
 SELECT CP.nombre, color, talla
  FROM coloresPrenda CP, tallasPrenda TP WHERE CP.nombre = TP.nombre
 UNION
 SELECT CP.nombre, color, NULL
  FROM coloresPrenda CP WHERE nombre NOT IN (SELECT nombre FROM tallasPrenda)
 UNION
 SELECT TP.nombre, NULL, talla
  FROM tallasPrenda TP WHERE nombre NOT IN (SELECT nombre FROM coloresPrenda)
 )
);

SELECT * FROM tuplasSobran;

INSERT INTO Prendas(nombre, color, talla) VALUES('CALCETIN', 'NEGRO', NULL);
SELECT * FROM tuplasFaltan;
SELECT * FROM tuplasSobran;
INSERT INTO Prendas(nombre, color, talla) VALUES('PANTALON', 'AZUL', 7);
INSERT INTO Prendas(nombre, color, talla) VALUES('CAMISETA', 'AZUL', NULL);
SELECT * FROM tuplasFaltan;
SELECT * FROM tuplasSobran;

DELETE FROM prendas WHERE (nombre, color, talla) IN (
 ('PANTALON', 'ROJO', 7), ('FALDA', 'VERDE', 4),
 ('PANTALON', 'AZUL', 7), ('PANTALON', 'AZUL', NULL)
);
-- eliminar el calcetín negro
DELETE FROM prendas WHERE color = 'NEGRO';
-- borrar las tuplas con color o talla NULL
DELETE FROM prendas WHERE color IS NULL OR talla IS NULL;

-- Obtención de las tuplas que debería haber para que se verificase la DMV
--
-- creación de tablas (o vistas) con los colores y tallas de las prendas
-- (son las tablas en que habría que descomponer la tabla prendas al normalizar)
CREATE TABLE coloresP AS (
  SELECT DISTINCT nombre, color FROM prendas WHERE color IS NOT NULL
);
CREATE TABLE tallasP AS (
  SELECT DISTINCT nombre, talla FROM prendas WHERE talla IS NOT NULL
);

SELECT * FROM coloresP;
SELECT * FROM tallasP;

-- tuplas que debería haber para que se verificase la DMV (usando OUTER JOINs)
SELECT coloresP.nombre nombre, color, talla
 FROM coloresP, tallasP WHERE coloresP.nombre = tallasP.nombre(+)
UNION
SELECT tallasP.nombre nombre, color, talla
 FROM coloresP, tallasP WHERE tallasP.nombre = coloresP.nombre(+);

-- tuplas que debería haber para que se verificase la DMV (sintaxis SQL2)
SELECT coloresP.nombre, color, talla
FROM tallasP FULL OUTER JOIN coloresP ON tallasP.nombre = coloresP.nombre;

-- para solventar que en el full join no sale el nombre si color es null
SELECT tallasP.nombre, color, talla
FROM tallasP LEFT OUTER JOIN coloresP ON tallasP.nombre = coloresP.nombre
UNION
SELECT coloresP.nombre, color, talla
FROM tallasP RIGHT OUTER JOIN coloresP ON tallasP.nombre = coloresP.nombre;

-- eliminar las tablas auxiliares
DROP TABLE coloresP;
DROP TABLE tallasP;

-- eliminar las vistas auxiliares
DROP VIEW nombresFallo;
DROP VIEW tuplasFaltan;
DROP VIEW tuplasSobran;
DROP VIEW coloresPrenda;
DROP VIEW tallasPrenda;
DROP VIEW nombre_color;

-- eliminar las restricciones definidas
ALTER TABLE prendas DROP CONSTRAINT prendas_UN;

-- eliminar la tabla prendas
DROP TABLE prendas;