Elementos repetidos

Recientemente la página http://www.codeproject.com/ propuso un ejercicio para eliminar los registros duplicados de una tabla que poseía registros parecidos a estos:

Tabla

ID	V1	V2
1	a	b
2	a	a
3	a	b
4	x	y
5	x	y
6	x	y
7	a	x

La página indicaba que la consulta tenía que ejecutarse correctamente en SQL Server. Yo planteé la siguiente solución que no sólo funciona en SQL Server sino en la mayoría de los gestores de bases de datos:

DELETE FROM tabla WHERE id > (SELECT MIN(id) FROM tabla as b WHERE b.v1=tabla.v1 AND b.v2 = tabla.v2)

El funcionamiento de la consulta es el siguiente:

1. La consulta exterior recorre cada registro de la tabla.
2. La consulta interior toma como «parámetros» los campos V1 y V2 de la consulta exterior.
3. La consulta exterior calcula el mínimo valor «ID» para cada combinación de V1 y V2.
4. Una vez que la consulta interior termina, la consulta exterior borra aquellos registros de V1 y V2 cuya «ID» es mayor que la mínima «ID» calculada para esa combinación de V1 y V2. Esto dejará aquellas combinaciones de V1 y V2 cuya clave primaria es mínina.

Puede cambiarse la consulta para que borre aquellos registros que sean «menores» que el «máximo» o también para que borre aquellos registros que sean «distintos» al «máximo». O «distintos» al «mínimo». Ejemplo:

• DELETE FROM tabla WHERE id < (SELECT MAX(id) FROM tabla as b WHERE b.v1=tabla.v1 AND b.v2 = tabla.v2)
• DELETE FROM tabla WHERE id <> (SELECT MAX(id) FROM tabla as b WHERE b.v1=tabla.v1 AND b.v2 = tabla.v2)
• DELETE FROM tabla WHERE id <> (SELECT MIN(id) FROM tabla as b WHERE b.v1=tabla.v1 AND b.v2 = tabla.v2)

El problema se centraba en sólo eliminar los registros pero no tomaba en cuenta otros elementos: ¿Qué pasaría si los registros borrados tenían relaciones con otras tablas? 

Necesariamente habría que realizar una consulta que realizara la actualización de las otras tablas para que usaran las claves primarias que quedarían «vivas» luego de la eliminación de los registros repetidos.

Esto se deja como ejercicio al lector.

La forma normal cero

En muchas oportunidades me preguntan sobre las consideraciones que deben tomarse en cuenta al diseñar una base de datos. Respondo que antes del diseño de base de datos, el correcto levantamiento de requerimientos ayuda a minimizar los ciclos de diseño, desarrollo, pruebas e implantación de sistemas.

Luego agrego que suponiendo que los requerimientos están correctamente levantados y validados,  la consideración más importante para el diseño de bases de datos es la normalización de la misma. Aunque esto es lo adecuado, muchos programadores dejan a un lado la normalización y optan por un esquema con menos tablas que minimice la complejidad de las consultas y mejore el rendimiento de éstas.

Ahí comienzan los problemas.

Una tabla con muchos campos presentará columnas que aceptan valores nulos ya que dependiendo de la aplicación, algunos datos son requeridos para cierto tipo de registros y no requeridos para otro. Esto no sólo es problemático sino que genera un crecimiento importante de la base de datos. Además, si las reglas del negocio cambian, seguramente habrá que agregar nuevos campos y modificar las aplicaciones para que actualicen los mismos.

Definitivamente, es necesario normalizar la base de datos.

En esta oportunidad hablaré sobre una condición que no está documentada y que muchas veces no es tomada en cuenta: 

La forma normal cero. 

Ésta posee las siguientes condiciones:

• Todas las tablas deben tener una clave primaria. Esto es una característica del modelo relacional: Una tabla debe tener un discriminador que distinga sin ambigüedades un registro del resto. Si la clave primaria no existe no sólo será imposible diferenciar los registros sino que tampoco será posible relacionarlos correctamente con otras tablas.
• Los atributos primos deben ser numéricos. Desde el punto de vista computacional, la comparación y almacenamiento de números es más eficiente que la de caracteres, por lo tanto la búsqueda de registros cuya clave es numérica es más veloz que aquella que se ejecuta con una clave alfanumérica.  Otro motivo para preferir claves numéricas es que estructuras especiales como los índices son más compactos cuando están formados por este tipo de claves.
• Las claves no deben poseer atributos redundantes. Una clave debe ser simple o tener la mínima cantidad de atributos. Otra manera de decir esto es que la clave debe ser irreducible. Una clave irreducible es aquella que al quitarle cualquiera de sus atributos pierde la propiedad de identificar registros sin ambigüedad.
• Las claves no deben cambiar sus valores constantemente.  Considere que un registro de una tabla A está relacionado con 10.000 registros de una tabla B.  Si los valores de la clave en A cambian constantemente, se deben hacer cambios en los 10.000 registros de la tabla B.  Aunque un gestor de bases de datos puede realizar esto de manera eficiente, en sistemas con usuarios concurrentes el tiempo de respuesta puede ser prohibitivo.
• Las claves deben ser «familiares» o «fáciles de recordar»: Las claves deben poseer atributos que tengan sentido para los usuarios.  Considérese por ejemplo la identificación de automóviles.  Es sabido que existen varios atributos que tomados en forma independiente identifican un registro de otros: número de matrícula, serial del motor y serial de carrocería, los cuales de manera independiente identifican sin ambigüedad a los registros; sin embargo para un usuario es más sencillo recordar la matrícula de un automóvil (seis a ocho letras y números combinados) que el serial del motor o de la carrocería (por lo menos 15 caracteres y números).

Aunque esta forma normal no está completamente documentada, sí está mencionada en los documentos originales del modelo relacional propuesto por E. F. Codd, donde el autor menciona las características que los diseños de bases de datos deben tener. 

Un texto donde puede consultarse sobre éste y otros aspectos de la normalización de base de datos es la obra de C. J. Date «The Database Relational Model: A Retrospective Review and Analysis» el cual despliega un recorrido histórico sobre el modelo relacional de datos.

Abrazo Mortal - SQL Server

En muchos de los talleres de bases de datos que dicto, muchos estudiantes me preguntan como resolver el problema del "abrazo mortal". En esta oportunidad, hablaré sobre la técnica para resolver este conflicto en SQL Server.

Anotaciones: SQL Server emite un error cuando detecta que hay un abrazo mortal (o deadlock, si lo prefieren) cuando un registro está siendo modificado por más de una conexión. Un ejemplo de esto lo ilustra la siguiente situación: La estación A quiere modificar los registros 1 y 2 y la estación B quiere modificar los registros 2 y 1. Ambas estaciones requieren que esos registros se modifiquen en ese orden.

Si el programa que intenta hacer ambas actualizaciones es capaz de detectar dicho error, entonces podrá resolver sin problemas la situación. Para esto, utilizaremos el mensaje de error 1205, que Microsoft describe como "Transaction (Process ID %d) was deadlocked on {%Z} resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

Adicionalmente utilizaremos la función XACT_STATE() que devuelve cero cuando no hay transacciones activas en la conexión y menos uno o uno en caso contrario.

Para ilustrar dicho ejemplo, utilizaremos varios guiones, cada uno de los cuales realiza una función determinada.


Guión CREACIÓN. Creación de tablas y registros. Este guión no necesita mayor explicación. Crea una tabla con dos registros: Registro 1 y Registro 2.

---

create table abrazo ( a int not null primary key, valor int);
go

insert into abrazo values (1,1);
insert into abrazo values (2,2);

---

Seguidamente se entregan dos guiones que simularán las estaciones que desean modificar los registros según se explicó al inicio de este publicación


GUIÓN ESTACIÓN A. El funcionamiento de este guión establece un número de intentos hasta que la operación se ejecute. Si agotado el número de intentos la operación no se da, el programa termina. La línea señalada en azul es para que el programa haga una pausa y el usuario pueda probar ambos guiones simultáneamente en dos consolas de SQL Server. En sistemas en producción esto no debe hacerse.

Observe que este guión trata de modificar el registro 1 y luego el registro 2.

---

---

GUIÓN ESTACIÓN B. El funcionamiento de este guión es análogo al anterior sólo que éste intenta modificar el registro 2 y luego el registro 1. Recuerde que en el guión anterior, los registros se modifican en orden inverso: cuando el guión B intente modificar el registro 1, éste estará bloqueado por el guión A.

---

---

Resumen. Observe que ambos guiones solucionan entre ellos el problema del abrazo mortal. En general, los sistemas deben validar esta condición para garantizar que la operación se dé sin conflictos. En próximas entregas, se mostrará la solución del abrazo mortal para otros gestores de bases de datos.

Errores de diseño – Tipos fecha/hora

Recientemente tuve la oportunidad de realizar una actividad en una empresa de construcción. Se me contrató para realizar reportes sobre una base de datos que está en funcionamiento desde hace seis años.

Una de las tablas –que llamaremos actividad- tiene tres campos: «Hora de llegada», «hora de atención» y «hora de salida». Estas variables almacenan la hora en la cual una persona llega al lugar, la hora en la cual la atienden y la hora en la que se marcha del lugar. Para calcular el tiempo de espera se resta la «hora de llegada» de la «hora de atención» y para calcular el tiempo de atención se resta la «hora de atención» de la «hora de salida». El tiempo de permanencia se obtiene restando la «hora de llegada» de la «hora de salida».

La fecha en la que se espera realizar la actividad se almacena en la variable «hora de actividad». Esta variable, a diferencia de las otras no sólo almacena la hora sino también la fecha. Dicho de otra manera, la variable «hora de actividad» es de tipo «fecha/hora». La tabla -implantada bajo PostgreSQL- tiene la siguiente configuración (Se omite el resto de los campos).

CREATE TABLE actividad (

hora_de_actividad timestamp without time zone,

hora_de_llegada time without time zone,

hora_de_atencion time without time zone,

hora_de_salida time without time zone

);

Al generar los primeros reportes llamó mi atención el hecho de que muchos de los tiempos calculados eran negativos. Ahí detecté un gran problema: La compañía que diseñó la base de datos supuso que la hora de llegada, la hora de atención y la hora de salida ocurrían el mismo día en el que ocurría la hora de actividad, por lo tanto sólo se almacenaba en estas tres variable la hora en la que ocurrió cada evento.

Craso error.

Muchas actividades duran más de un día: Una persona puede llegar a las nueve de la noche del primer día, ser atendido a la tres de la mañana del segundo día e irse a la una de la mañana del tercer día... o de cualquier otro día. Seguidamente muestro un ejemplo:

Actividad	Llegada	Atención	Salida	Esp.	Atn.	Per.
2013-01-01 05:00:00	21:00:00	03:00:00	01:00:00	-18h	-2h	-20h
2013-01-01 05:00:00	16:00:00	17:00:00	18:00:00	1h	1h	2h

El primer registro indica que alguien fue atendido a las tres de la mañana (de cualquier día, no necesariamente del día después de la actividad) y se marchó a la una de la mañana (de cualquier otro día). Ya que no se conoce la fecha de cada uno de los eventos posteriores a la llegada de una persona es imposible saber con exactitud los tiempos de permanencia, espera y atención. Incluso en el segundo registro, los tiempos de espera no son exactos: Basado en la lógica del negocio, la persona pudo haber llegado a las cuatro de la tarde del tres de enero, ser atendida a las cinco de la tarde del cuatro de enero y marcharse a las seis de la tarde del cinco de enero o de cualquier otro día; de esta manera los cálculos son erróneos.

Otro inconveniente de este diseño es que no es posible saber si la reunión comenzó antes o después de la hora planteada. Explicados estos detalles al cliente, esta fue su solución:

• Suponer que la persona llegó el mismo día en el que fue planteada la reunión.
• Suponer que la reunión comenzó el día en el que se definió.
• Si la hora de atención es menor que la hora de llegada, suponer que la hora de atención ocurrió el día después en el que la reunión se definió. En caso contrario, la hora de atención y la hora de llegada ocurrieron el mismo día en el que la reunión se definió.
• Hacer lo mismo con «hora de salida» y «hora de atención», pero en este caso, tomar como base el cálculo realizado en el punto anterior.

Como se observa, los reportes muestran «esa realidad» pero no lo que realmente ocurrió. Según el cliente los registros en dicha condición eran pocos, sin embargo, al calcular los promedios generales discriminados por gerencia, muchos tiempos resultaban negativos, así que el fenómeno se repetía más de lo esperado.

Más allá del error de tener en la tabla un campo para cada evento (violación de la primera forma normal), el principal inconveniente de este diseño es la pérdida de información ya que no es posible calcular con exactitud los tiempos en dicha base de datos.

Solución para el problema: Usar siempre tipos fecha/hora para evitar ambigüedades. En el caso de este cliente, es imposible recuperar la información de manera exacta. Un fallo tan «pequeño» en el diseño resultó en un gran inconveniente.

Cero para la consultora.

Rompecabezas - Todos los números (Solución)

Hace algunos días propuse el rompecabezas «Todos los números». Seguidamente doy una solución para el mismo, sin embargo, previo a la presentación de la solución, expongo el siguiente análisis:

1. La cantidad de repeticiones de los valores de la tabla registro debe ser la igual a la cantidad de registros de la tabla repetición. Ya que los valores 1, 3 y 4 de la tabla de registro se repiten 3 veces y la cantidad de registros de la tabla repeticiones es 3, los números que pasan esta primera revisión son 1, 3 y 4. Queda el valor 2 descartado de una vez.


2. Para cada valor de la tabla registro, la cantidad de repeticiones del campo repetición debe ser igual a la cantidad de repeticiones del campo repetición de la tabla repetición. Veamos: En la tabla repetición la letra «a» se repite dos veces y la letra «b» se repite una vez. Si se observa en la tabla registro, los únicos valores que están relacionados con la letra «a» dos veces y con la letra «b» una vez son los números 1 y 4.  Esto descarta el número 3, que «sobrevivió» en la revisión [1]

Una manera de solventar el siguiente problema es con la siguiente consulta:

select distinct a.valor  from

( select v.valor, v.repeticion, count (*) as cnt

from registro as v

group by v.valor, v.repeticion ) as a,

(select r.repeticion, count (*) as cnt from repeticion as r

group by r.repeticion ) as b,

(select count (*) as cnt from repeticion ) as c,

(select v.valor, count (*) as cnt

from registro as v

group by v.valor ) as d

where

a.repeticion = b.repeticion and a.cnt = b.cnt and c.cnt = d.cnt and a.valor = d.valor

Aunque la consulta funciona correctamente, cada una de las sub/consultas (a,b,c,d) deben ejecutarse para todos los registros antes de realizar las comparaciones y dar el resultado final. Para una gran cantidad de registros la consulta podría tomar un tiempo considerable en su ejecución.

Dicho esto, se propone la siguiente mejora en la consulta:

select distinct v.valor

from registro as v

group by v.valor, v.repeticion

having count (*) =

( select count (*) from repeticion as r where r.repeticion = v.repeticion and exists

( select 1 from registro as r where  r.valor = v.valor  having count (*) =

( select count (*) as cnt from repeticion )

)

)

A diferencia de la consulta anterior, ésta sólo toma en cuenta los subconjuntos derivados de las «primeras sub/consultas», lo que agiliza el tiempo de respuesta. Otra mejora consiste en sustituir la última sub/consulta por una variable que almacene la cantidad de registros de la tabla repetición. Esto evitaría el cálculo recurrente y acortaría el tiempo de proceso.

Claramente, existen otras soluciones para este rompecabezas.

Éstas se dejan como ejercicio a los lectores.

Rompecabezas - Todos los números

Recientemente estuve leyendo un foro de rompecabezas en SQL y hubo un ejercicio que me llamó la atención. Lo expondré de manera simplificada.

Hay dos tablas: Una llamada registro y otra llamada repetición.

Registro

Valor	Repetición
1	a
1	b
1	a
2	a
2	b
3	b
3	b
3	a
4	a
4	a
4	b

Repetición

Repetición

a

a

b

El rompecabezas consiste en lo siguiente: Mostrar los valores de la tabla registro que tienen exactamente las mismas repeticiones en la tabla repetición. Observe que en la tabla registro los valores 1 y 4 están combinados con [a,a,b]  y en la tabla repetición aparecen los valores [a,a,b], por lo tanto la consulta solamente debe mostrar los valores 1 y 4.

No debe mostrar el valor 2 debido a que éste solamente está combinado con [a,b]. Tampoco debe mostrar el valor 3 ya que éste está combinado con [a,b,b].

En la próxima publicación explicaré la respuesta que le di al rompecabezas, aunque hay varias soluciones para el mismo.