En primer lugar determinaremos las relaciones, sus atributos y los dominios de cada uno de ellos:

En la relación PETICION, hemos decidido que convendría tener una referencia interna de la petición, que nos ayudará al hablar de ella con el cliente (si tuviese varias abiertas) y evitará confusiones al trabajar. El resto de atributos son bastante explícitos.

Como una petición puede evolucionar con el tiempo, a medida que se piden más datos al cliente, la incidencia va evolucionando, etc., hemos creado las relaciones NOTA_PETICION y MATERIAL_PETICION para reflejarlo.

También hemos tenido que definir las relaciones CLIENTE y EMPLEADO para poder relacionarlas con las peticiones y las notas que se vayan generando durante su resolución.

A continuación vamos a definir los dominios de los atributos:

Al definir los dominios de cada atributo, ya nos hemos avanzado en la toma de algunas decisiones: al decidir, por ejemplo, que el dominio del atributo empleado en la relación NOTA_PETICION es NIF, estamos implícitamente determinando que la clave primaria de la relación EMPLEADO será del dominio NIF y que usaremos un atributo de este dominio para referirnos a él.

Aunque algunas claves ya se intuyen a partir de los atributos de las relaciones, vamos a determinarlas para completar el caso.

Ahora podemos reescribir las relaciones:

Las claves foráneas ya se intuyen a partir de las relaciones, aunque vamos a comentarlas para completar el caso:

NOTA_PETICION:

Tiene de clave foránea el atributo {peticion}, que establece la relación (y pertenece al mismo dominio) con el atributo {referencia} de la relación PETICION.

También tiene la clave foránea {empleado}, que establece la relación con EMPLEADO a partir de su clave primaria {nif}.

MATERIAL_PETICION:

Tiene de clave foránea el atributo {peticion}, que establece la relación (y pertenece al mismo dominio) con el atributo {referencia} de la relación PETICION.

En este punto, no es necesario preocuparse por las reglas de integridad del modelo que tratan sobre la clave primaria, ya que nos vendrán impuestas en el momento de crear las tablas en el SGBD.

Es conveniente, no obstante, fijar las decisiones sobre la integridad referencial; en concreto, qué vamos a hacer en caso de restricción. Así pues, para cada re- lación que tiene una clave primaria referenciada desde otra, deberemos decidir qué política cabe aplicar en caso de modificación o borrado:

PETICION

• Modificación del atributo {referencia} referenciado desde NOTA_PETICION y MATERIAL_PETICION: aquí podemos optar por la restricción o por la actualización en cascada (más cómoda, aunque no todos los SGBD la implementan, como veremos más adelante).

• Borrado del atributo {referencia}. Aquí optaremos por una política de restricción. Si la petición tiene notas asociadas o materiales, significa que ha habido alguna actividad y, por lo tanto, no deberíamos poder borrarla. Si se desea anularla, ya estableceremos un estado de la misma que lo indique.

CLIENTE

• Modificación del atributo {nif} referenciado desde PETICION. Es probable que si un cliente cambia de NIF (por un cambio del tipo de sociedad, etc.) deseemos mantener sus peticiones. Aquí la política debe ser de actualización en cascada.

• Borrado del atributo {nif}. Es posible que si queremos borrar un cliente, es porque hemos terminado toda relación con él y, por lo tanto, es coherente utilizar aquí la política de anulación.

EMPLEADO

• Modificación del atributo {nif} referenciado desde NOTA_PETICION. No es probable que un empleado cambie su NIF, salvo caso de error. Aun así, en caso de que se produzca, es preferible la actualización en cascada.

• Borrado del atributo {nif}. Aunque eliminemos un empleado si termina su relación con la empresa, no deberíamos eliminar sus notas. La mejor opción es la restricción.

Para probar el modelo, una buena opción es intentar realizar algunas consultas sobre él y ver si obtenemos los resultados deseados. En nuestro caso, vamos

a realizar las siguientes consultas:

• Obtención de una petición junto con los datos del cliente:

• Obtención de una petición con todas sus notas:

• Obtención de los datos de todos los empleados que han participado en la petición 5:

Ejercicio

Os sugerimos que intentéis más operaciones sobre el modelo para familiarizaros con él.

• Creación de la base de datos

• Definición de dominios

• Creación de las tablas

• Creación de vistas

– Peticiones pendientes:

– Tiempo y precio de los materiales empleados para las peticiones terminadas en el mes en curso:

A continuación, decidimos indicar algunas sentencias de  manipulación corrientes para completar la documentación. De esta manera, cuando empecemos el desarrollo, tendremos mucho más claras estas operaciones sobre la base de datos:

•    Nuevo cliente:   
INSERT INTO CLIENTE VALUES (‘Juan Pérez’,’42389338A’,’912223354’,’juanperez@gmail.com’);

•    Nueva petición:
INSERT INTO PETICION VALUES (5,’42389338A’, ‘No le arranca el ordenador’, ’Nueva’,CURRENT_TIMESTAMP,NULL,NULL,NULL);

•    Cambio de estado de la petición, añadimos una nota y un material:
UPDATE PETICION SET estado=‘Aceptada’ WHERE referencia=5;
INSERT INTO NOTA_PETICION VALUES (5,’Parece un problema del disco duro. Vamos examinarlo más a fondo.’,CURRENT_TIMESTAMP,’35485411G’);
INSERT INTO MATERIAL_PETICION VALUES (‘Disco duro 20Gb’,5,250.00,1);

•    Materiales solicitados en la petición 5:
SELECT nombrematerial, cantidad, precio FROM MATERIAL_PETICION WHERE peticion=5

•    Número de peticiones abiertas del cliente ‘42389338A’:
SELECT COUNT(*) FROM PETICION WHERE cliente=‘42389338A’ AND estado NOT IN (‘Resuelta’, ’Cerrada’);

La creación de vistas del apartado anterior nos ha mostrado también algunas consultas complejas que repetimos a continuación:

•    Peticiones abiertas:
SELECT P.referencia, C.nombre, P.resumen, P.estado, (P.fechainicio    P.fecharecepcion), P.fecharecepcion FROM PETICION P JOIN CLIENTE C ON P.cliente = C.nif WHERE estado NOT IN (‘Resuelta’,’Cerrada’) ORDER BY fecharecepcion;

•    Tiempo y precio de los materiales empleados para las peticiones terminadas en el mes en curso:
SELECT P.referencia, C.nombre, P.resumen, P.tiempoempleado, SUM(M.precio) FROM PETICION P, CLIENTE C, MATERIAL_PETICION M WHERE P.cliente=C.nif AND M.peticion=P.referencia AND estado=‘Resuelta’ GROUP BY P.referencia;

Finalmente, vamos a practicar con las consultas que realizamos en álgebra relacional en el apartado anterior:

•    Obtención de una petición junto con los datos del cliente:
R:= PETICION [cliente=nif] CLIENTE
SELECT * FROM PETICION JOIN CLIENTE ON PETICION.cliente=CLIENTE.nif;

•    Obtención de una petición con todas sus notas:
NP(peticionnota,nota,fechanota,empleado):=NOTA_PETICION (peticion,nota,fecha,empleado) R:=PETICION[referencia=peticionnota]NP
SELECT PETICION.*, peticion AS peticionnota, nota, fecha as fechanota, empleado
FROM PETICION JOIN NOTA_PETICION ON referencia=peticionnota;

•    Obtención de los datos de todos los empleados que han participado en la petición 5:
NP:=NOTA_PETICION[peticion=5]
RA:=EMPLEADO[nif=empleado]NP
R:=RA[nombre,nif]
SELECT E.nombre, E.nif FROM EMPLEADO E, NOTA_PETICION N WHERE
E.nif=NOTA_PETICION.empleado
AND NOTA_PETICION.peticion=5;

Vamos a plantear en primer lugar el modelo obtenido y, después, comentaremos los aspectos más interesantes:

El modelo, expresado de este modo, es mucho más comprensible por parte de personal no técnico o no especializado en tecnologías de bases de datos.

A partir de la expresión gráfica del modelo, identificamos limitaciones o puntos de mejora, que anotamos a continuación:

•    Probablemente, debemos incluir información de facturación a clientes por las peticiones realizadas.

•    Probablemente, habrá peticiones que puedan agruparse en una entidad superior (un proyecto o trabajo), o bien peticiones relacionadas entre ellas (peticiones que deban resolverse antes que otras).

Una vez identificadas estas limitaciones, vamos a ampliar el modelo para corregirlas e impresionar a nuestros superiores de cara a la reunión.

•    Información de facturación a clientes:

Lo único que hemos introducido ha sido la entidad FACTURA, que se relaciona con N peticiones y con un único cliente. Un CLIENTE puede tener varias facturas asociadas, pero una petición sólo puede pertenecer a una única factura.

•    Grupos de peticiones y relaciones entre ellas.
 

La pertenencia a un proyecto será opcional, y así lo indicamos en el diagrama. Por lo que respecta a las relaciones de peticiones entre ellas, se trata de una interrelación recursiva. Si queremos contemplar casos como los siguientes, debemos expresar la relación como M:N recursiva y opcional. En la interrelación RELACIONA, debemos contemplar algún atributo que indique de qué tipo de relación se trata en cada caso:

•    Una petición depende de una o más peticiones.
•    Una petición bloquea a una o más peticiones.
•    Una petición es la duplicada de una o más peticiones.
•    Una petición está relacionada con una o más peticiones.

En el apartado anterior sugerimos unas ampliaciones sobre el modelo ER que proporcionaban más prestaciones al proyecto. A continuación, vamos a realizar la transformación al modelo relacional de estas ampliaciones:

•    Información de facturación a clientes.

Según las transformaciones vistas en el módulo “El lenguaje SQL”, la entidad FACTURA se transforma en la relación FACTURA, con los siguientes atributos:

Donde cliente es una clave foránea que corresponde a la interrelación TIENE entre CLIENTE y FACTURA. Un cliente puede tener N facturas, pero una factura pertenece sólo a un único cliente.
 

La interrelación entre FACTURA y PETICION del tipo 1:N se transforma también en una nueva clave foránea, que aparece siempre en el lado N de la interrelación; o sea, en la relación PETICION. Si existen peticiones que no deban facturarse (porque se han cerrado sin resolverse, o eran duplicadas de otras, etc.), su clave foránea tomaría el valor NULO.

•    Grupos de peticiones y relaciones entre ellas.

Por una parte, la entidad proyecto debe transformarse en la relación PROYECTO, con atributos como los siguientes:

La relación 1:N entre PROYECTO y PETICIÓN se transformará en la inserción de una nueva clave foránea en la relación PETICION, que podrá tener valor NULO si la petición no pertenece a ningún proyecto; es decir, si se trata de una petición aislada. La relación PETICION quedaría así:

Por lo que respecta a las relaciones entre peticiones, se trata de una interrelación  recursiva  N:M,  y  por  lo  tanto  se  transformará  en  una  nueva  relación, PETICION_RELACION:

En este caso, y según el valor que pueda tomar el atributo {tiporelacion}, tendrá importancia o no qué referencia de petición aparece en cada atributo de la relación.

En cambio, si el atributo {tiporelacion} indica un bloqueo o una dependencia entre relaciones (porque una debe resolverse antes que otra, por ejemplo), entonces sí tiene sentido qué referencia de petición se almacena en el atributo 1 y cuál en el 2. En todo caso, esta tarea corresponderá a la solución que se adapte y trabaje con la base de datos en último término, no al propio modelo.