En este anexo se presenta el diseño del Sistema de Base de Datos en sus tres niveles de abstracción: Modelo Conceptual, Modelo Lógico y Modelo Físico.
En la siguiente figura se muestra este proceso y las herramientas utilizadas:
ANEXO B.- DISEÑO DEL SISTEMA DE BASE DE DATOS DEL SISTEMA DE INFORMACIÓN
B-2
Diseño del Modelo Conceptual de la Base de Datos
El modelo conceptual más utilizado es el modelo entidad-relación introducido por Peter Chen en 1976, que posee los siguientes conceptos: entidades, atributos, dominios de atributos, identificadores y jerarquías de generalización.
Un modelo conceptual es un conjunto de conceptos que permiten describir la realidad mediante representaciones lingüísticas y gráficas.
A continuación se hará una breve descripción de los conceptos utilizados en el presente Anexo.
1.- Entidad. Se trata de algo que tiene interés para la empresa, tal como un empleado, un departamento o una venta. Desde una perspectiva teórica, una entidad puede ser, simplemente, un conjunto de atributos. Sin embargo, no se trata de una forma útil de pensar en entidades. Es mucho mejor pensar que una entidad es algo que tiene correspondencia en el mundo real.
2.- Atributo. Se trata de una pieza de información que podemos extraer de un objeto o instancia de una entidad, tales como los nombres de los departamentos o las edades de los empleados.
3.- Clave primaria. Se trata de un concepto de teoría relacional que describe los atributos de una entidad que identifican de manera única a cualquier instancia específica de dicha entidad. Por ejemplo, el ID de Empleado (identificador de empleado) es una clave primaria apropiada para la entidad Empleado, por que no puede haber dos empleados que tengan el mismo ID. Las claves primarias deben ser únicas. Ningún componente debe ser nulo, y una vez que han sido asignadas, no podrán modificarse.
4.- Clave candidata. Con frecuencia en una entidad, es posible que más de un atributo pueda servir como clave primaria. Una de las claves candidatas se asignará como clave primaria. Otros atributos que puedan actuar como claves primarias se designarán como claves candidatas.
5.- Dominio. Es un rango de valores permitidos que existe para un atributo.
ANEXO B.- DISEÑO DEL SISTEMA DE BASE DE DATOS DEL SISTEMA DE INFORMACIÓN
B-3
En este apartado, se presenta el diseño conceptual de base de datos, basado en el Modelo E-R entidad-relación.
ANEXO B.- DISEÑO DEL SISTEMA DE BASE DE DATOS DEL SISTEMA DE INFORMACIÓN
B-4
Las siguientes gráficas corresponden al Modelo Conceptual del Sistema de Base de Datos del Sistema Control de Combustibles, de manera general y en particular de los Módulos de Captura de Vehículos, de la Captura de “Valeras” y Vales, del Registro de Tambores y del Registro de Pipas.
Figura B.3.- Modelo Conceptual del Sistema Relacional de Base de Datos del Sistema de Control de Combustibles.
ANEXO B.- DISEÑO DEL SISTEMA DE BASE DE DATOS DEL SISTEMA DE INFORMACIÓN
B-5
Figura B.4.- Modelo Conceptual del Modelo Relacional de Base de Datos de la Captura de Vehículos.
ANEXO B.- DISEÑO DEL SISTEMA DE BASE DE DATOS DEL SISTEMA DE INFORMACIÓN
B-6
Figura B.6.- Modelo Conceptual del Modelo Relacional de Base de Datos del Registro de Tambores.
ANEXO B.- DISEÑO DEL SISTEMA DE BASE DE DATOS DEL SISTEMA DE INFORMACIÓN
B-7
Diseño del Modelo Lógico de la Base de Datos
Una base de datos relacional implica que sus tablas se habrán diseñado de tal forma que se ajustan a la teoría y la práctica relacionales. Estas directrices teóricas se expresan mediante las reglas de normalización. [Dorsey, P., Hudicka, J., 1999].
El modelo relacional representa los datos y las relaciones entre los datos mediante una colección de tablas, cada una de las cuales tiene un número de columnas con nombres únicos.
La normalización, se encarga de obtener los datos agrupados en distintas
tablas siguiendo una serie de pasos, de tal manera que los datos obtenidos tienen una estructura óptima para su implementación, gestión y explotación desde distintas aplicaciones futuras. Una de las ventajas principales que se obtiene al realizar la normalización es que la información no estará duplicada innecesariamente dentro de las estructuras.
Figura B.8.- Niveles del Proceso de Normalización de la Base de Datos.
Primera Forma Normal
Se dice que una tabla está en primera forma normal, si todos los valores que componen a sus elementos son atómicos: un atributo no puede tener más de valor. Para normalizar una tabla que no esté en 1FN han de seguirse los siguientes pasos:
• Se localizan los atributos correspondientes a la clave principal
• Se realiza una proyección sobre la tabla y así se descompone en varias, de manera que se hace la proyección de la clave con los atributos que tengan los valores únicos.
ANEXO B.- DISEÑO DEL SISTEMA DE BASE DE DATOS DEL SISTEMA DE INFORMACIÓN
B-8
Segunda Forma Normal
Esta forma normal se considerará únicamente cuando la llave primaria sea compuesta, si no (la llave primaria está formada por un único atributo) la tabla estaría en segunda forma normal.
Decimos que una tabla está en segunda forma normal, si se cumplen las siguientes condiciones:
• Está en 1FN
• Todo atributo secundario (los que no pertenecen a la llave primaria), tienen una dependencia funcional total de la llave completa y no una parte de ella.
Tercera Forma Normal
Una tabla está en 3FN si:
• Está en 2FN
• No existen atributos no primarios (no pertenecen a la llave) que son transitivamente dependientes de cada posible llave de la tabla, o lo que es lo mismo, un atributo secundario sólo puede ser conocido a través de la llave primaria ó llaves candidatas de la tabla y no por otro atributo no primario.
Para convertir una tabla que no esté en 3FN a 3FN, se realizará una proyección de la llave primaria a los elementos que no tengan dependencia funcional transitiva y otra tabla con una nueva llave primaria a los elementos que anteriormente tenían esta dependencia.
Este procedimiento se aplicó a las tablas identificadas en el Diccionario de Datos, por lo que se cumple con el diseño del Modelo Lógico de la Base de Datos, adecuado para su funcionamiento.
ANEXO B.- DISEÑO DEL SISTEMA DE BASE DE DATOS DEL SISTEMA DE INFORMACIÓN
B-9
Diseño del Modelo Físico de la Base de Datos Diccionario de Datos Lógico
Un diccionario de datos, es un archivo que contiene metadatos, es decir, (datos sobre datos). Este archivo se consulta antes de leer o modificar los datos reales en el sistema de bases de datos. [Korth H., Silberschatz A., 1993]. Los diccionarios
de datos proporcionan significados comunes para los elementos y actividades del sistema, cada elemento consiste en un conjunto de detalles que describen los datos utilizados o producidos por el sistema.
A continuación, se describe el diccionario de datos, para el presente proyecto. Al final de cada una de las tablas siguientes se define la llave primaria (Primary Key) y llaves alternas, según sea el caso.
CO_MARCAS: Catálogo de marcas de Vehículos
CAMPO NULL? TIPO DESCRIPCION
MARCA NOT NULL VARCHAR2(3) Es la clave de la marca del vehículo
DESC_MARCA NOT NULL VARCHAR2(15) Es la descripción de la marca del vehículo por ejemplo, FORD, NISSAN, etc. FIC_ACTUA NOT NULL VARCHAR2(8) Es la ficha del empleado que actualizó el registro
FEC_ACTUA NOT NULL DATE Es la fecha de actualización en que se modificó el registro CVE_BAJAL NOT NULL VARCHAR2(1) Es el estado del registro, A=Alta, B=Baja, M=Modificación y son
visibles al usuario los registros en A y M
PRIMARY KEY PK_CO_MARCA (MARCA)
CO_TIPOS: Catálogo de tipos de vehículos
CAMPO NULL? TIPO DESCRIPCION
TIPO NOT NULL VARCHAR2(3) Es la clave del tipo de vehículo
DESC_TIPO NOT NULL VARCHAR2(30) Es la descripción del tipo de vehículo por ejemplo, sedan, pick-up, etc. FIC_ACTUA NOT NULL VARCHAR2(8) Es la ficha del empleado que actualizó el registro
FEC_ACTUA NOT NULL DATE Es la fecha de actualización en que se modificó el registro
CVE_BAJAL NOT NULL VARCHAR2(1) Es el estado del registro, puede ser A=Alta, B=Baja, M=Modificación y son visibles al usuario los registros en A y M
PRIMARY KEY PK_TIPO (TIPO)
CO_EQUIPOS: Catálogo de clases de equipo de vehículo
CAMPO NULL? TIPO DESCRIPCION
EQUIPO NOT NULL VARCHAR2(1) Es la clave de la clase de equipo de vehículo
DESC_EQUIPO NOT NULL VARCHAR2(15) Es la descripción de la clase de equipo del vehículo por ejemplo, ligero, semipesado, pesado, etc.
FIC_ACTUA NOT NULL VARCHAR2(8) Es la ficha del empleado que actualizó el registro FEC_ACTUA NOT NULL DATE Es la fecha de actualización en que se modificó el registro
CVE_BAJAL NOT NULL VARCHAR2(1) Es el estado del registro, puede ser A=Alta, B=Baja, M=Modificación y son visibles al usuario los registros en A y M
ANEXO B.- DISEÑO DEL SISTEMA DE BASE DE DATOS DEL SISTEMA DE INFORMACIÓN
B-10
CO_CAPACIDADES: Catálogo de capacidad del tanque del vehículo por marca, tipo, equipo y modelo
CAMPO NULL? TIPO DESCRIPCION
MARCA NOT NULL VARCHAR2(3) Es la clave de la marca del vehículo. Viene de la tabla CO_MARCAS
TIPO NOT NULL VARCHAR2(3) Es la clave del tipo de vehículo. Viene de la tabla CO_TIPOS
EQUIPO NOT NULL VARCHAR2(1) Es la clave del equipo del vehículo. Viene de la tabla CO_EQUIPOS
MODELO NOT NULL VARCHAR2(4) Es el año en 4 dígitos del modelo de elaboración del vehículo
CAPACIDAD NOT NULL NUMBER(3) Es la capacidad del tanque de gasolina del vehículo FIC_ACTUA NOT NULL VARCHAR2(8) Es la ficha del empleado que actualizó el registro FEC_ACTUA NOT NULL DATE Es la fecha de actualización en que se modificó el registro CVE_BAJAL NOT NULL VARCHAR2(1) Es el estado del registro, puede ser A = Alta, B = Baja, M =
Modificación y son visibles al usuario los registros en A y M
PRIMARY KEY PK_CAPACIDAD (MARCA, TIPO, EQUIPO, MODELO, CAPACIDAD) FOREIGN KEY FK_CAPACIDAD_MARCA (MARCA) Æ CO_MARCAS
FOREIGN KEY FK_CAPACIDAD_TIPO (TIPO) Æ CO_TIPOS FOREIGN KEY FK_CAPACIDAD_EQUIPO (EQUIPO) Æ CO_EQUIPOS
CO_ESTADOS_VEHICULOS: Es el catálogo de estado físico del vehículo
CAMPO NULL? TIPO DESCRIPCION
ESTADO NOT NULL VARCHAR2(1) Es la clave del estado físico del vehículo
DESC_ESTADO NOT NULL VARCHAR2(15) Es la descripción del estado físico del vehículo por ejemplo Activo, Siniestrado, etc. FIC_ACTUA NOT NULL VARCHAR2(8) Es la ficha del empleado que actualizó el registro
FEC_ACTUA NOT NULL DATE Es la fecha de actualización en que se modificó el registro
CVE_BAJAL NOT NULL VARCHAR2(1) Es el estado del registro, puede ser A=Alta, B=Baja, M=Modificación y son visibles al usuario los registros en A y M
PRIMARY KEY PK_ESTADOS (ESTADO)
CO_PRECIOS: Catálogo de precios de combustible por vigencia
CAMPO NULL? TIPO DESCRIPCION
COMBUSTIBLE NOT NULL VARCHAR2(1) Es la clave del tipo de combustible, viene del catalogo CO_COMBUSTIBLE PRECIO NOT NULL NUMBER(12,2) Es el valor del tipo de combustible por litro en pesos mexicanos COMISION NOT NULL NUMBER(12,2) Es el valor de la comisión por litro para la estación de servicio en pesos mexicanos FECHA_INICIAL NOT NULL DATE Es la fecha en que inicia la vigencia del precio del tipo de combustible FECHA_FINAL NOT NULL DATE Es la fecha en que finaliza la vigencia del precio del tipo de combustible
REGION NOT NULL VARCHAR2(1) Es la clave de la región obtenida del primer dígito del centro operativo por ejemplo para la región Norte 1
CEN_OPERA NOT NULL VARCHAR2(5) Es la clave del centro operativo por ejemplo de la región norte 12490 FIC_ACTUA NOT NULL VARCHAR2(8) Es la ficha del empleado que actualizó el registro
FEC_ACTUA NOT NULL DATE Es la fecha de actualización en que se modificó el registro
CVE_BAJAL NOT NULL VARCHAR2(1) Es el estado del registro, puede ser A=Alta, B=Baja, M=Modificación y son visibles al usuario los registros en A y M
ANEXO B.- DISEÑO DEL SISTEMA DE BASE DE DATOS DEL SISTEMA DE INFORMACIÓN
B-11
CO_COMBUSTIBLES: Catálogo de tipos de Combustible del vehículo
CAMPO NULL? TIPO DESCRIPCION
COMBUSTIBLE NOT NULL VARCHAR2(1) Es la clave del tipo de combustible
DESC_COMBUSTIBLE NOT NULL VARCHAR2(15) Es la descripción del tipo de combustible por ejemplo Magna, Diesel, etc. FIC_ACTUA NOT NULL VARCHAR2(8) Es la ficha del empleado que actualizó el registro
FEC_ACTUA NOT NULL DATE Es la fecha de actualización en que se modificó el registro CVE_BAJAL NOT NULL VARCHAR2(1) Es el estado del registro, puede ser A=Alta, B=Baja,
M=Modificación y son visibles al usuario los registros en A y M
PRIMARY KEY PK_COMBUSTIBLE (COMBUSTIBLE)
CO_GASOLINERAS: Catálogo de estaciones de servicio o gasolineras por región
CAMPO NULL? TIPO DESCRIPCION
GASOLINERA NOT NULL VARCHAR2(4) Es la clave de la estación de servicio o gasolinera DESC_GAS NOT NULL VARCHAR2(50) Es la razón social de la estación de servicio o gasolinera DIRECCION NOT NULL VARCHAR2(50) Es la dirección física de la estación de servicio o gasolinera CIUDAD NOT NULL VARCHAR2(20) Es la ciudad en donde se encuentra la gasolinera o estación de servicio TELEFONO NOT NULL VARCHAR2(10) Es el teléfono de la estación de servicio
PROPIETARIO NOT NULL VARCHAR2(35) Es el nombre del propietario de la estación de servicio RFC NOT NULL VARCHAR2(15) Es el RFC de la estación de servicio
REGION NOT NULL VARCHAR2(1) Es la clave de la región obtenida del primer dígito del centro operativo por ejemplo para la región Norte 1
CEN_OPERA NOT NULL VARCHAR2(5) Es la clave del centro operativo por ejemplo de la región norte 12490 FIC_ACTUA NOT NULL VARCHAR2(8) Es la ficha del empleado que actualizó el registro
FEC_ACTUA NOT NULL DATE Es la fecha de actualización en que se modificó el registro
CVE_BAJAL NOT NULL VARCHAR2(1) Es el estado del registro, puede ser A=Alta, B=Baja, M=Modificación y son visibles al usuario los registros en A y M
PRIMARY KEY PK_GASOLINERA (GASOLINERA, CEN_OPERA)
FOREIGN KEY FK_GASOLINERA_CENTRO (CEN_OPERA) Æ SAP_CENTRO
CO_MOTIVOS: Esta tabla contiene las registros de los motivos de Cancelación o de Reactivación de una valera.
CAMPO NULL? TIPO DESCRIPCION
VALERA NOT NULL VARCHAR2(6) Es el numero de valera
CEN_OPERA NOT NULL VARCHAR2(5) Es el centro operativo donde se registro la valera.
MOT_CANCELACION VARCHAR2(255) Motivo de cancelación de la valera.
MOT_REACTIVACION VARCHAR2(255) Motivo de reactivación de la valera.
FIC_ACTUA NOT NULL VARCHAR2(8) Es la ficha del empleado que actualizó el registro FEC_ACTUA NOT NULL DATE Es la fecha de actualización en que se modificó el registro CVE_BAJAL NOT NULL VARCHAR2(1) Es el estado del registro, puede ser A = Alta, B = Baja, M = Modificación y son visibles al usuario los registros en A
y M
PRIMARY KEY PK_MOTIVOS (VALERA, CEN_OPERA)
FOREIGN KEY FK_VALERA_MOTIVOS (VALERA, CEN_OPERA) Æ CO_VALERAS (VALERA, CEN_OPERA)
ANEXO B.- DISEÑO DEL SISTEMA DE BASE DE DATOS DEL SISTEMA DE INFORMACIÓN
B-12
CO_PERIODOS: Son los períodos del año determinados por región
CAMPO NULL? TIPO DESCRIPCION
PERIODO NOT NULL VARCHAR2(2)
Es la clave del periodo de acuerdo a como se defina por región por ejemplo en región norte 01 es la primera quincena del mes de Enero, 02 es la segunda quincena del mes de Enero y así sucesivamente hasta llegar a 24 periodos, que es la segunda quincena del mes de Diciembre
FECHA_INICIO NOT NULL DATE Es la fecha en que inicia el periodo, en este caso el año no importa solo los días por ejemplo para Región Norte el periodo 01 que inicie el 15/Enero/2000
FECHA_TERMINO NOT NULL DATE Es la fecha en que finaliza el periodo, en este caso el año no importa solo los días por ejemplo para Región Norte el periodo 01 que finalice el 15/Enero/2000
MES NOT NULL VARCHAR2(10) Es el nombre del mes al que pertenece el periodo
REGION NOT NULL VARCHAR2(1) Es la clave de la región obtenida del primer dígito del centro operativo por ejemplo para la región Norte 1
CEN_OPERA NOT NULL VARCHAR2(5) Es la clave del centro operativo por ejemplo de la región norte 12490 FIC_ACTUA NOT NULL VARCHAR2(8) Es la ficha del empleado que actualizó el registro
FEC_ACTUA NOT NULL DATE Es la fecha de actualización en que se modificó el registro
CVE_BAJAL NOT NULL VARCHAR2(1) Es el estado del registro, puede ser A=Alta, B=Baja, M=Modificación y son visibles al usuario los registros en A y M
PRIMARY KEY PK_PERIODOS (PERIODO, FECHA_INICIO, CEN_OPERA) FOREIGN KEY FK_PERIODOS_CENTRO (CEN_OPERA)Æ SAP_CENTRO
CO_TAMBORES : Esta tabla contiene los folios emitidos por carga de tambores de combustible
CAMPO NULL? TIPO DESCRIPCION
FOLIO NOT NULL VARCHAR2(7 ) Es la clave del folio
FICHA_CARGA NOT NULL VARCHAR2(8 ) Es la ficha de quien realizo la carga COMBUSTIBLE NOT NULL VARCHAR2(1 ) Es la clave del tipo de combustible cargado LITROS NOT NULL NUMBER(5,1) Son los litros cargados y en múltiplos de 50 FECHA_CARGA NOT NULL DATE Es la fecha en la que se realizo la carga
PERIODO NOT NULL VARCHAR2(2 ) Es la clave del periodo que depende de la fecha de carga GASOLINERA NOT NULL VARCHAR2(4 ) Es la clave de la gasolinera
REGION NOT NULL VARCHAR2(1 ) Es la clave de la Región
CEN_OPERA NOT NUL VARCHAR2(5 ) Es el centro Operativo de quien realizo la carga CVE_CTRO NOT NULL VARCHAR2(3 ) Es el Centro de trabajo de quien realizo la carga CVE_DEPTO NOT NULL VARCHAR2(5 ) Es el departamento de quien realizo la carga FIC_ACTUA NOT NULL VARCHAR2(8) Es la ficha del empleado que actualizó el registro FEC_ACTUA NOT NULL DATE Es la fecha de actualización en que se modificó el registro CVE_BAJAL NOT NULL VARCHAR2(1) Es el estado del registro, puede ser A = Alta, B = Baja,
M = Modificación y son visibles al usuario los registros en A y M
PRIMARY KEY PK_TAMBOR(FOLIO)
FOREIGN KEY FK_TAMBOR_COMBUSTIBLE (COMBUSTIBLE) Æ CO_COMBUSTIBLES
FOREIGN KEY FK_TAMBOR_GASOLINA (GASOLINERA, CEN_OPERA) Æ CO_GASOLINERAS(GASOLINERA, CEN_OPERA)
ANEXO B.- DISEÑO DEL SISTEMA DE BASE DE DATOS DEL SISTEMA DE INFORMACIÓN
B-13
CO_PIPAS : Esta tabla contiene los folios emitidos por carga de Pipas de combustible
CAMPO NULL? TIPO DESCRIPCION
FOLIO NOT NULL VARCHAR2(7) Es la clave del folio de la Factura FACTURA NOT NULL VARCHAR2(6) Es el número de la factura.
FICHA_CARGA NOT NULL VARCHAR2(8) Es el número de ficha de quien realizó la carga. COMBUSTIBLE NOT NULL VARCHAR2(1) Es la clave del tipo de combustible cargado LITROS NOT NULL NUMBER(71) Son los litros cargados
PERIODO NOT NULL VARCHAR2(2) Es la clave del periodo que depende de la fecha de carga
IEPS NOT NULL NUMBER(122) IEPS
DESTINO NOT NULL VARCHAR2(100) Es descripción del destino donde se llevó el combustible. FECHA_CARGA NOT NULL DATE Es la fecha en la que se realizo la carga
REGION NOT NULL VARCHAR2(1) Es la clave de la Región