Describiendo el problema
Enunciado del problema
Se desea implementar una base de datos para facilitar la gestión y administración de un cementerio, en dicha base de datos se contemplan diferentes categorías laborales, distintos tipos de enterramiento, facturas por los servicios prestados, incluso se permite que una familia posea su propio panteón para un determinado número de personas.
El cementerio está dividido en sectores, teniendo estos una capacidad y extensión variable que ha de quedar reflejada.
Asimismo se ha quiere tener información sobre los empleados mediante datos personales como nombre y apellidos,dirección, telefóno, salario, antigüedad, etc.
Las categorías en las que se dividen los empleados son:
- Enterradores
- Jardineros
- Administrativos
Los jardineros se ocuparán del cuidado de los sectores, de tal forma que un jardinero está al cuidado de un sector, aunque del cuidado de un sector pueden encargarse varios jardineros.
Asimismo,cada sector contendrá un determinado número de tumbas.Una tumba pertenece a un sector.
Las Tumbas pueden ser de uno de los siguientes tipos:
Es necesario, además, almacenar información sobre el fallecido, así como de la persona (familiar) que se hará cargo de los costes del servicio (todo ello, obviamente identificado mediante los datos personales y de interés para la empresa).
- Cada fallecido es enterrado por un único enterrador,lógicamente el enterrador puede enterrar a mas de un fallecido durante su jornada laboral.
- Los nichos tienen capacidad para una sola persona.
- Sin embargo un panteón tiene capacidad para varias personas siendo lo normal 4, siendo por eso de tipo smallint.
- La capacidad de una Fosa Común es superior a la de un panteón, y es de tipo integer. En este caso y en los dos anteriores asumimos la indivisibilidad del fallecido.
Además, los administrativos emiten facturas para los familiares, de tal forma que un administrativo puede emitir facturas a varios familiares, y un familiar puede recibir varias facturas.
El único tipo de tumba que puede ser propiedad de un familiar es el panteón, siendo propiedad de una única persona, y dicha persona puede poseer varios panteones.
Construyendo el diagrama E-R
Para realizar el diagrama de entidad-relación hemos de decidir los conjuntos tanto de entidades como de relaciones, atendiendo al enunciado del problema, la estructura del diagrama ha quedado como sigue:
- Entidades:
- Empleado (que constará a su vez de tres tipos: Jardinero, Enterrador y Administrativo).
- Sector (en los que está dividido el cementerio).
- Tumba (puede ser de tres tipos: Nicho, Panteón y Fosa Común).
- Fallecido (Representa a la persona muerta mediante los atributos detallados mas tarde).
- Familiar (Es necesario enviar la factura a los familiares del fallecido).
- Relaciones:
- JarSec ( Indica la relación de los jardineros con los sectores del cementerio)
- TumSec (Relación que se da entre las tumbas y los sectores en que están ubicadas)
- EnFa (Es la relación que se establece entre los enterradores y los fallecidos).
- Factura (Representa la venta de una tumba a la familia del fallecido).
- NiFa (Indica si el fallecido tiene asignado un Nicho).
- FoFa (Indica si el fallecido se encuentra en una Fosa Común).
- PanFa (Indica si el fallecido se encuentra en un Panteón).
- FamFa (Es la relación establecida entre el fallecido y su familia).
- PaFam (Relación que indica la posesión de un panteón por parte de una familia).
A continuación hablaremos de los pormenores tanto de las entidades como de las relaciones, especificando atributos, tipos de relaciones, cardinalidades y todo aquello que sea interesante destacar.
Comenzamos pues por los atributos propios de cada entidad:
La entidad Familiar tiene 5 atributos:
- Nombre: Nombre del familiar al que se envía la factura.
- Apellidos: Contiene los apellidos del familiar.
- Telefono: Teléfono de contacto del familiar.
- Direccion: Almacena la dirección (calle, numero, piso, etc).
- ID_Familia: Código identificador de un familiar, es la clave primaria de esta tabla.
La entidad Enterrador tiene 8 atributos:
- Nombre: Representará el nombre del empleado.
- Apellidos: Contienen los apellidos del empleado.
- Dirección: Almacena la dirección (calle, numero, piso, etc).
- Teléfono: Número de teléfono de contacto.
- Telef_Movil: Número de teléfono móvil.
- Antigüedad: Años de servicio en la empresa.
- Salario: Sueldo en Euros .
- DNI: Contiene el número del DNI, es la clave primaria de esta entidad.
La entidad Administrativo tiene 8 atributos:
- Nombre: Representará el nombre del empleado.
- Apellidos: Contienen los apellidos del empleado.
- Dirección: Almacena la dirección (calle, numero, piso, etc).
- Teléfono: Número de teléfono de contacto.
- Telef_Movil: Número de teléfono móvil.
- Antigüedad: Años de servicio en la empresa.
- Salario: Sueldo en Euros .
- DNI: Contiene el número del DNI, es la clave primaria de esta entidad.
La entidad Sector tiene 4 atributos:
- Nombre: Nombre de cada sector o zona del cementerio.
- ID_Sector: Código identificador de zona
- Superficie: Extensión en m2
- Capacidad: Número de fallecidos que puede alojar.
La entidad Jardinero tiene 9 atributos:
- Nombre: Representará el nombre del empleado.
- Apellidos: Contienen los apellidos del empleado.
- Dirección: Almacena la dirección (calle, numero, piso, etc).
- Teléfono: Número de teléfono de contacto.
- Antigüedad: Años de servicio en la empresa.
- Salario: Sueldo en Euros
- Sector: El sector del cementerio donde trabaja. Clave ajena tomada de Sector.
- DNI: Contiene el número del DNI, es la clave primaria de esta entidad.
La entidad Tumba tiene 4 atributos:
- ID_Tumba: Código identificadore de tumba.
- Tipo: Puede ser de tres tipos: Nicho, Panteón o Fosa Común.
- Sector: Sector en que se encuentra la tumba. Clave ajena tomada de Sector.
La entidad Nicho tiene 3 atributos:
- Altura: Altura del nicho
- ID_Nicho: Código identificador de nicho. Clave primaria y Clave Ajena tomada de Tumba (ID_Tumba).
- Inscripcion: Texto que figura en el.
La entidad FosaComun tiene 3 atributos:
- ID_Fosa: Código identificador de Fosa Común. Clave primaria y Clave Ajena tomada de Tumba (ID_Tumba).
- Capacidad: Número de fallecidos que puede contener.
La entidad Panteon tiene 4 atributos:
- ID_Panteon: Código identificador de panteon. Clave primaria y Clave Ajena tomada de Tumba (ID_Tumba).
- ID_Familia: Código identificador de familia Clave ajena tomada de Familiar (ID_Familia).
- Inscripcion: Texto que figura en el.
- Capacidad: Número de fallecidos que puede contener.
La entidad Factura tiene 5 atributos:
- Cantidad: Total a pagar por la familia.
- Fecha: Fecha en que se emite la factura.
- Clave_Factura: Clave primaria (Fecha,ID_Familia,ID_Admin).
- ID_Familia: Código identificador de familia. Clave ajena tomada de Familiar.
- ID_Admin: Código identificador de Administrativo. Clave ajena tomada de Administrativo (DNI).
La entidad Fallecido tiene 7 atributos:
- Nombre: Representará el nombre del fallecido.
- Apellidos: Contienen los apellidos del fallecido.
- FechaNacimiento: Almacena la fecha de nacimiento del fallecido.
- FechaMuerte: Almacena la fecha de la muerte del fallecido.
- Enterrador: Código q identifica al enterrador encargado del entierro. Clave Ajena tomada de Enterrador (DNI).
- ID_Familia: Código q identifica a la familia del fallecido. Clave Ajena tomada de Familiar (ID_Familia).
- Tumba: Código q identifica la tumba del fallecido. Clave Ajena tomada de Tumba (ID_Tumba).
Para la realización del esquema Entidad-Relación, hemos utilizado una herramienta para el diseño de diagramas muy conocida por los usuarios de Linux como es Dia , se puede descargar en su versión para Windows 95/98/NT/ME/XP... de 
este web. El único inconveniente que hemos observado, es la notación que utiliza para la representación de los atributos, enmarcándolos en una elipse.Debido a esto, decidimos representar en el diagrama sólo los atributos que son clave primaria (para consultar detalles sobre atributos, ver lista anterior)
Por lo que se refiere a la especialización de la entidad Empleado, podemos decir que consta de tres tipos: Jardinero, Enterrador y Administrativo. En cuanto a la ligadura, diremos que se trata de conjuntos de entidades disjuntos cuya ligadura de completitud es de tipo total, posee además un atributo llamado Tipo que identifica cada una de las tres categorías.
La entidad Tumba también posee una especialización en la que se diferencian tres tipos de subclases, a saber: Nicho, Panteón y Fosa Común, se trata de entidades disjuntas cuya participación es total, al pasarlo a tablas aparecerá un atributo en la tabla Tumba q identificará el tipo de Tumba de q se trata.
Construyendo las tablas
Una vez obtenido el esquema relacional es sencillo pasarlo a tablas. Nos ayudaresmo de PgAccess, una aplicación gráfica que permite gestionar Postgres de un modo sencillo. Más adelante comentaremos un poco PgAccess.
En la figura no indicamos campos no nulos ni las claves primarias. De cualquier modo, el resultado es lo bastante claro como para hacernos una idea de la estructura que tendrá la bbdd.
Una vez conseguido este esquema pasamos a plasmarlo a comandos SQL. El resultado se puede observar en este fichero.
Consultando nuestra Base de Datos
En esta sección mostraremos la manera de realizar consultas en la base de datos cementerio mediante unos ejemplos, tomados bien como capturas de Putty para ilustrar la posibilidad de administración remota, bien como salida de pantalla .
Como primer ejemplo pondremos una captura directamente de Putty de una consulta que muestra todos los campos de la tabla Familiar.
A continuación mostramos otra captura tomada también de Putty en la que se relacionan nombres y apellidos de Familiares y Fallecidos, como puede observarse, es una consulta que afecta a dos tablas relacionadas por el campo ID_Familia.
Un ejemplo de lo laborioso que puede resultar hacer una consulta para conocer el Dni del enterrador que se ocupó de un fallecido llamado Restituto
cementerio=# select Fallecido.Nombre, Fallecido.Apellidos, Enterrador.Dni
cementerio-# From Fallecido INNER JOIN Enterrador
cementerio-# ON Fallecido.Enterrador = Enterrador.Dni
cementerio-# WHERE Fallecido.Nombre = 'Restituto';
nombre | apellidos | dni
-----------+----------------+----------
Restituto | Gracia de Dios | 10657493
(1 row)
Ejemplo que muestra la salida de una consulta para listar el nombre de aquellos enterradores cuyo sueldo sea superior a 1600 Euros
cementerio=# select Nombre, Apellidos, Salario
cementerio-# FROM Enterrador
cementerio-# WHERE Salario > 1600;
nombre | apellidos | salario
---------+-------------------+---------
Marc | Pérez | 1800.00
Gonzalo | González González | 1900.00
Luis | Anté Bajo | 1750.00
Frutos | Rojo del Bosque | 1869.25
(4 rows)
Veamos ahora el uso de Alias para las columnas, mientras que en SQL es sufiente con ponerlo a continuacion del campo o con comilla doble, en PostgreSQL es necesario utilizar as de la siguiente manera:
cementerio=# SELECT Nombre as "Nombre de Pila", Apellidos,
cementerio-# Dni as "Código Indentificador" FROM Enterrador;
Nombre de Pila | apellidos | Código Indentificador
----------------+-------------------+-----------------------
Juán Felipe | García Sierra | 71659874
Marc | Pérez | 71545545
Jacinto | Rodríguez López | 10657493
Gonzalo | González González | 71234321
Luis | Anté Bajo | 9632236
Frutos | Rojo del Bosque | 10653298
(6 rows)
Consideraciones finales
En este apartado, trataremos temas como la semántica no reflejada en el diseño, los controles que se realizan mediante la aplicación de acceso a la base de datos así como todos aquellos aspectos y consideraciones que no tienen cabida en otros apartados.
Observaciones
Como puede apreciarse en el esquema, los fallecidos están relacionados tanto con Nicho como con Panteón y Fosa Común, esto nos obligó a crear la tabla Tumba con un campo llamado Tipo que indicará si la tumba en cuestión es Nicho, Panteón o Fosa Común. Este campo resulta muy útil para realizar consultas que necesiten saber el tipo de enterramiento.
No hemos considerado la posibilidad de que una familia pueda ser la propietaria del nicho de su familiar fallecido, dejando esta relación sólo para los panteones. Obviamente no ha sido por comodidad, pues el trabajo añadido sería ínfimo, sino que lo que nos interesaba era conseguir la mayor variedad posible dentro de unos límites razonables.
Además de lo anterior, decidimos que el entierro en fosa común no generase una factura, siendo este un servicio gratuito.
Aunque quizá fuera más propio decir esto en el apartado de conclusiones finales y a pesar de que se ha mencionado en varias ocasiones,hemos de hacer hincapié en la forma de trabajo conjunta entre las dos máquinas, una con Linux RedHat 8.0 (donde está alojada la base de datos y el resto del material del trabajo) y otra con Windows 98 desde donde se pueden realizar las tareas necesarias para administrar la base de datos y trabajar en la documentación (haciendo algún que otro malabarismo).
Semántica no reflejada
Como puede apreciarse viendo tanto el modelo de Entidad-Relación como el diagrama de tablas, pueden darse casos un tanto extraños como que a dos fallecidos se les asigne el mismo nicho. También se puede exceder la capacidad tanto de Panteones como de Fosas Comunes, no obstante estos problemas son controlados mediante software por la aplicación en Python pycemen, así nos aseguramos de que un nicho sólo pueda estar ocupado por un fallecido, que un panteón pueda contener como máximo a 6 (pueden ser menos) personas así como que una fosa común albergue un máximo de 200 personas (pueden ser menos).
Asímismo se observa que los fallecidos pueden pasar indistintamente a un Nicho, Fosa Común o Panteón, sin que aparentemente haya una razón para decidir mas allá de las económicas o de cualquier otro tipo.
Tutoriales
