Excel Bases Datos

Excel y bases de datos

Los sistemas especializados en el manejo de bases de datos son denominados motores o manejadores de bases de datos. las características técnicas que debe cumplir un sistema de este tipo, de acuerdo con Date1, son:

1. Permitir la definición de datos a través de un lenguaje de definición de datos o DDL.

2. Permitir la manipulación de datos a través de un lenguaje de manipulación o DML.

3. Ofrecer características de seguridad e integridad de los datos, el motor debe ser el único medio de acceso a la información de la base de datos y éste debe ser capaz de controlar la integridad de los datos mediante restricciones como las llaves primarias.

4. El sistema de base de datos debe permitir la recuperación de los datos ante eventos en los que se requiera devolver completamente el procesamiento realizado. Por ejemplo, supongamos que un evento del mundo real es realizar una transferencia de fondos entre dos cuentas, el proceso que se intentará realizar es restar del saldo de una cuenta una cantidad de dinero para sumárselo a otra. El sistema inicia la resta, pero si detecta un problema como que la cuenta destino está bloqueada, debe devolver la resta realizada para que la información siga siendo consistente. Esta clase de operaciones en bases de datos se denomina rollback. Cuando la operación se confirma y es exitosa se dice que el sistema hace commit.

5. El sistema de bases de datos permite la concurrencia de usuarios. Esta clase de sistemas están diseñados de tal manera que permite a múltiples usuarios consultar información consistente y garantiza que operaciones que afectan a los mismo datos se realicen consistentemente. En el ejemplo anterior de la transferencia, cuando se realiza la resta, el sistema de base de datos debe ser capaz de bloquear este registro para validar que el monto a restar sea inferior al saldo de la cuenta, si lo es resta y sino indica que el saldo es insuficiente. En caso que otro proceso simultáneamente intente tomar la misma cuenta para realizar otra transferencia encontrará el registro bloqueado, esperará a que la otra transacción termine y el sistema automáticamente tratará de realizar la nueva transacción. El sistema siempre tratará de garantizar que la información se mantenga coherente.

1 Ibid. DATE. Pág. 43 – 46.

6. Diccionario de datos. El sistema de bases de datos permite el mantenimiento y consulta de los metadatos de los objetos del sistema. Recordemos que los metadatos son los datos sobre objetos de la base de datos, por ejemplo para los campos de las tablas su tipo de datos, si es parte de la llave o no, si es requerido u opcional, etc.

7. El motor de base de datos debe ser capaz de ejecutar las operaciones de la forma más óptima posible y debe ofrecer herramientas para mejorar las respuesta del sistema.

En términos precisos herramientas como Excel o Access no pueden ser consideradas como manejadores de bases de datos pues no cumplen muchas de las características que técnicamente deben tener esta clase de sistemas. Realmente esta clase de herramientas se especializan en el procesamiento de datos y pueden ser utilizadas para analizar pequeños volúmenes de información.

En general los costos de un manejador de bases de datos son altos, por lo que puede ser una restricción para que empresas pequeñas puedan montar un sistema de información con bases de datos y aunque existen motores con licenciamiento de software libre como MySQL (cero costo de licenciamiento, cumpliendo ciertas condiciones que puede profundizar en http://www.gnu.org/philosophy/free-sw.es.html), se requiere que la empresa invierta en un proceso de desarrollo de software para que especialistas en bases de datos puedan determinar las necesidades del sistema, construir la solución e instalarla en la empresa. Esto no quiere decir que las pequeñas empresas no puedan analizar los datos que generan sus procesos para generar la información que necesitan. Por esta razón se quiere presentar en este módulo, como herramientas de uso común como hojas de cálculo pueden ser utilizadas para almacenar pequeños volúmenes de información y realizar procesos de análisis y presentación de la información, siendo este el propósito de esta sección.

Se va a tomar como base Excel2 para presentar la forma como se puede utilizar esta herramienta para realizar análisis de los datos. Se va a asumir que posee conocimiento básico en el uso de Excel. Si considera necesario nivelar primero su conocimiento en Excel, se recomienda abordar tutoriales. Por ejemplo, en la página http://www1.ceit.es/asignaturas/Informat1/AyudaInf/ encuentra un tutorial de EXCEL 2000 preparado por la Escuela Superior de Ingenieros de la Universidad de Navarra. También encuentra tutoriales para versiones anteriores de Excel como la versión 7 y 97.

2 Tenga en cuenta que si no dispone de Excel, puede utilizar la herramienta de uso libre Calc de OpenOffice para poder realizar este tipo de práctica. Puede descargarla en http://download.openoffice.org/other.html.

http://www.gnu.org/philosophy/free-sw.es.html

http://www.gnu.org/philosophy/free-sw.es.html

http://www1.ceit.es/asignaturas/Informat1/AyudaInf/

http://download.openoffice.org/other.html

Excel es una aplicación tipo hoja de cálculo que permite trabajar y procesar datos organizados en forma de tabla mediante una amplia variedad de funciones y herramientas para realizar operaciones financieras, matemáticas, estadísticas, etc. Particularmente para lo que atañe a esta sección. Excel ofrece funcionalidad básica para búsqueda y referencia de información y algunas funciones de bases de datos.

A continuación se va a desarrollar una práctica con ejemplos preconstruidos para que usted pueda observar cómo se podría utilizar Excel como un medio para el análisis de la información.

Práctica 1. Identificación de las entidades de información.Tomando como base una empresa del mundo real y un proceso particular identifique las entidades de las que desea almacenar datos. Por ejemplo para una comercializadora es necesaria la información de:

Clientes: Cliente de la comercializadora. Referencias comerciales de clientes: Información de referencias

comerciales del cliente. Productos: Productos en venta de la comercializadora. Empleados: Empleados de la comercializadora. Facturas: Relación del consecutivo de facturas. Ventas por factura y producto: Discriminación de cada ítem de

la factura, cada ítem se asocia a cada producto vendido en la factura.

Lista de precios: Lista de precios de los productos de la comercializadora.

A manera de práctica, tome una pequeña empresa del mundo real e identifique las entidades más importantes sobre las que se desea almacenar información.

Práctica 2. Describa los atributos de cada tabla.Analice que atributos o campos debe registrar para cada entidad para definir las tablas. Empiece por aquellos que le permitirán identificar cada registro. Campos Llave.

Para la práctica se identifican los siguientes campos por tabla. Se resaltan en amarillo los que son llave:

CLIENTES

CódigoCódigo interno asignado por la comercializadora al Cliente.

Nombre Nombres del cliente

Apellidos Apellidos del cliente

Número Identificación Número de identificación

Tipo Identificación

Cédula (CC), NIT (NIT), Cédula Extranjería (CE), Pasaporte (PA), Tarjeta Identidad (TI), Registro Civil (RC).

Género Masculino (M), Femenino (F)

Estado civil Casado, Soltero, Separado, Viudo, Unión Libre

Fecha de nacimiento Fecha de nacimiento del cliente

Fecha de registroFecha de ingreso del cliente a la base de datos de la empresa.

REFERENCIAS COMERCIALESCódigo Cliente Código interno del cliente

Nombre Referencia Personal

Referencia personalDirección Referencia Personal

Teléfono Referencia Personal

Nombre Referencia Comercial

Referencia comercialDirección Referencia Comercial

Teléfono Referencia Comercial

Nombre Referencia Bancaria

Referencia bancariaDirección Referencia Bancaria

Teléfono Referencia Bancaria

Producto con el bancoNúmero de cuenta, tarjeta o producto de la referencia bancaria

PRODUCTOSCódigo Código interno del producto

Nombre Nombre del producto

PresentaciónForma de presentación del producto: Caja, Tarro, Bolsa, Bulto, Pinta, Unidad, Botella, etc.

Unidad de medidaUnidad de medida del producto: Unidad, Docena, Litros, ml., etc.

Medida

Indica la media de la presentación del producto. Por ejemplo si el producto tiene una presentación de 500 ml, este campo almacenará 500.

Marca Marca del producto

Fecha de registro Fecha de registro del producto

EMPLEADOSCódigo Código Interno del Empleado

Nombre Nombres del Empleado

Apellidos Apellidos del Empleado

Número Identificación

Número de identificación del empleado

Tipo IdentificaciónCédula (CC), NIT (NIT), Cédula Extranjería (CE), Pasaporte (PA), Tarjeta Identidad (TI), Registro Civil (RC).

Fecha de ingreso Fecha de ingreso a la empresa.

Cargo Cajero, Administrador, Supervisor, Vendedor, Contabilidad, Auxiliar, Almacenista.

Área Ventas, Compras, Contabilidad, Gerencia, Almacén

Fecha de registro Fecha de registro del empleado en el sistema.

FACTURASCódigo Factura Consecutivo DIAN de la Factura

Código de Cliente Código del cliente

Código de Cajero Código del cajero

Fecha de registro Fecha de Registro

Total Venta Total de la venta

VENTAS

SecuencialSecuencia para cada factura. Cada secuencia hace referencia a un producto relacionado en la factura de venta.

Código Factura Número DIAN de la factura

Código de Producto Código del producto

Fecha de registro Fecha de registro del ítem

Cantidad Vendida Cantidad vendida del producto

LISTA DE PRECIOSAño Año Fiscal

Código Producto Código del producto

Precio Precio del producto

Autorizado por (Código Empleado)

Empleado que definió el precio

Fecha de Registro Fecha de registro o actualización del precio

INVENTARIO DE COMPRASCódigo Producto Código del producto

Cantidad Ingreso Cantidad que ingresa al inventario

Cantidad Salida Cantidad de salidas del inventario

Cantidad Actual en Inventario

Cantidad de Ingreso – Cantidad de Salida

Fecha Ingreso InventarioFecha del último ingreso del producto al inventario

Fecha Salida Inventario Fecha de la última salida del inventario


Práctica 3. Diagrama físico de relaciones entre las tablas.Genere un diagrama físico de las tablas y sus relaciones, tenga en cuenta la notación utilizada en la sección anterior.Para su ayuda, tenga en cuenta aquellos campos que identificó en las tablas y que referencian a otras. Para la práctica, vea los campos en verde.

CLIENTES

Código Código interno asignado por la comercializadora al Cliente.

Nombre Nombres del cliente

Apellidos Apellidos del cliente


Número de identificación


Género Masculino (M), Femenino (F)

Estado civil Casado, Soltero, Separado, Viudo, Unión Libre

Fecha de nacimiento

Fecha de nacimiento del cliente

Fecha de registroFecha de ingreso del cliente a la base de datos de la empresa.

REFERENCIAS COMERCIALES

Código ClienteCódigo interno del cliente (Referencia la tabla de clientes)

Nombre Referencia Personal

Referencia personalDirección Referencia PersonalTeléfono Referencia PersonalNombre Referencia

Referencia comercialDirección Referencia Comercial

Teléfono Referencia Comercial

Nombre Referencia Bancaria

Referencia bancariaDirección Referencia BancariaTeléfono Referencia BancariaProducto con el banco Número de cuenta, tarjeta o producto de la

referencia bancaria

PRODUCTOSCódigo Código interno del producto

Nombre Nombre del producto

Presentación Forma de presentación del producto: Caja, Tarro, Bolsa, Bulto, Pinta, Unidad, Botella, etc.

Unidad de medida

Unidad de medida del producto: Unidad, Docena, Litros, ml., etc.

MedidaIndica la media de la presentación del producto. Por ejemplo si el producto tiene una presentación de 500 ml, este campo almacenará 500.

Marca Marca del producto

Fecha de registro

Fecha de registro del producto

EMPLEADOSCódigo Código Interno del Empleado

Nombre Nombres del Empleado

Apellidos Apellidos del Empleado

Número Identificación Número de identificación del empleado


Cédula (CC), NIT (NIT), Cédula Extranjería (CE), Pasaporte (PA), Tarjeta Identidad (TI), Registro Civil (RC).

Fecha de ingreso Fecha de ingreso a la empresa.

CargoCajero, Administrador, Supervisor, Vendedor, Contabilidad, Auxiliar, Almacenista.

Área Ventas, Compras, Contabilidad, Gerencia, Almacén

Fecha de registro Fecha de registro del empleado en el sistema.

FACTURASCódigo Factura

Consecutivo DIAN de la Factura

Código de Cliente

Código interno del cliente (Referencia la tabla de clientes)

Código de Cajero

Código del cajero (Referencia la tabla de empleados)

Fecha de registro

Fecha de Registro

Total Venta Total de la venta

VENTAS

SecuencialSecuencia para cada factura. Cada secuencia hace referencia a un producto relacionado en la factura de venta.

Código Factura Número DIAN de la factura

Código de Producto

Código del producto (Referencia la tabla de productos).

Fecha de registro

Fecha de registro del ítem

Cantidad Vendida

Cantidad vendida del producto

LISTA DE PRECIOSAño Año Fiscal

Código ProductoCódigo del producto (Referencia la tabla de productos)

Precio Precio del producto

Autorizado por (Código Empleado)

Empleado que definió el precio (Referencia la tabla de empleados)


INVENTARIO DE COMPRAS

Código Producto Código del producto (Referencia la tabla de productos)

Cantidad Ingreso Cantidad que ingresa al inventario

Cantidad Salida Cantidad de salidas del inventario

Cantidad Actual en Inventario Cantidad de Ingreso – Cantidad de Salida

Fecha Ingreso Inventario Fecha del último ingreso del producto al inventario

Fecha Salida Inventario Fecha de la última salida del inventario


Para la práctica, el diagrama es:

Figura 3. Modelo Físico de la práctica

Si durante el proceso de identificación de atributos (práctica 2) e identificación de relaciones (Práctica 3) identifica más tablas o atributos faltantes o redundantes, refine su propuesta hasta que lo considere necesario. Recuerde que el objetivo es tener toda la información requerida por la empresa y que no sea redundante.

Práctica 4. Estructuración de las tablas en Excel.

Tome el modelo refinado y prepare un libro Excel como el que se adjunta a continuación

Práctica 4, tablas

Práctica 5. Generación de tablas paramétricas.

Observe que existen algunas columnas que tomarán valores preestablecidos como el tipo de identificación, que toma los valores de: Cédula (CC), NIT (NIT), Cédula Extranjería (CE), Pasaporte (PA),

Tarjeta Identidad (TI), Registro Civil (RC). Esta clase de información la llamaremos tabla paramétrica.

Analice todos los campos de las tablas e identifique todos las tablas paramétricas.

Para la práctica tenemos:

Tabla Descripción


Género Masculino (M), Femenino (F)Estado civil Casado, Soltero, Separado, Viudo, Unión Libre

PresentaciónForma de presentación del producto: Caja, Tarro, Bolsa, Bulto, Pinta, Unidad, Botella, etc.

Unidad de medidaUnidad de medida del producto: Unidad, Docena, Litros, ml., etc.

CargoCajero, Administrador, Supervisor, Vendedor, Contabilidad, Auxiliar, Almacenista.

Área Ventas, Compras, Contabilidad, Gerencia, AlmacénCódigo Factura Consecutivos autorizados de la DIAN para la FacturaAño Relación de años fiscales

Genere una nueva hoja en el libro llamada Parámetricas. Cada fila tendrá una tabla paramétrica y todos los valores posibles de esta tabla. Vea la siguiente figura.

Seleccione cada columna, desde de su título hasta el último valor y escoja la opción Insertar Nombre Crear.

El sistema presentará la siguiente pantalla:

Seleccione la opción del nombre en la fila superior y oprima aceptar.

Mediante esta operación, el rango seleccionado estará disponible para que otras hojas del libro puedan referenciarlo.

Cree un nombre para cada columna que representa una tabla paramétrica.

Si en un futuro surgen nuevos valores para el rango, es suficiente con volver a realizar la misma operación y reemplazar el rango definido previamente.

Práctica 5, tablas paramétricas

Práctica 6. Preparación de la tabla para el ingreso de los datos.

El propósito de la práctica anterior era la creación de un nombre que permitirá obligar al usuario a ingresar únicamente los valores permitidos por estas tablas paramétricas, en las columnas que son de tipo paramétrico.

Para esto se realiza el siguiente proceso:

En cada hoja que corresponde a una tabla ubíquese en la primer fila de la columna que procede de un tipo paramétrico y escoja la opción Datos Validación:

En la pantalla que presenta se va a configurar que el campo solo permita valores de una lista. Adicionalmente se escriben los mensajes pertinentes cuando el usuario ingrese al campo y el mensaje de error que se le presentaría en caso que ingrese un valor incorrecto. La pantalla que se presenta es como la que sigue:

Realice esta misma operación para todas las columnas que son de tipo paramétrico para que referencien adecuadamente los posibles valores.

Esta misma operación debe ser realizada en cada hoja (tabla) del libro. Tenga en cuenta que puede utilizar la funcionalidad de copiar y pegar la celda que ya tiene la característica paramétrica definida, en otra columna de otra hoja que tenga el mismo comportamiento.

Todos los campos pueden tener la validación que desee, no solamente los paramétricos. Por ejemplo, para las fechas se puede definir una validación que solo acepte fechas dentro de un rango determinado. También se puede validar que el campo solamente acepte números enteros, decimales, horas, alfanuméricos de determinada longitud o una validación personalizada mediante una fórmula. De esta manera la metadata de los campos puede ser utilizada en Excel para crear una validación.

En el Excel adjunto podrá ver cómo quedó configurado el primer registro en todas las tablas. Analice la validación de cada campo y compárela contra los metadatos descritos en el módulo.

Práctica 6, formulario

Práctica 7. Definición de un registro por defecto.

Es una buena práctica crear el primer registro de toda tabla, que sea referenciada por otras, con valores por defecto para que puedan ser siempre referenciadas, así sea con valores por defecto, mientras se define correctamente.

En el caso de la práctica que se está desarrollando, las tablas que van a ser referenciadas por otras son: clientes, productos, empleados y facturas.

Por ejemplo, un registro por defecto para la tabla clientes sería:

Código 1

Nombre Sin Definir

Apellidos Sin Definir



Género

Estado civil

Fecha de nacimiento

Fecha de registro

Para que Excel pueda validar que las relaciones de integridad referencial no se violen, se requiere realizar el mismo proceso que se hizo para las tablas paramétricas. En este caso dado que se ingresarían clientes a diario, es necesario ir actualizando los rangos en la medida que se van ingresando nuevos registros hasta el límite posible en Excel que es 65536 filas, siendo esta una de las razones del porque no se puede considerar a Excel una base de datos. No maneja altos volúmenes de información y el mantenimiento de las restricciones de integridad no es automático.

Visualmente lo que se va a realizar, por ejemplo, para los clientes es:

En el Excel adjunto, para las columnas en color curuba ( ), se definió el rango indicado.

Estos rangos van a ser relacionados, tal como se hizo con las tablas paramétrica en los campos de las demás tablas que lo referencian. Por ejemplo, el cliente es referenciado en la tabla de facturas y referencias comerciales, el producto es referenciado en la tabla de facturas, lista de precios e inventario. En la siguiente figura se puede observar la referencia al cliente creada en la tabla referencias comerciales.

De esta misma manera se deben crear todas las referencias a las tablas en el libro. Puede observar en el libro adjunto las columnas resaltadas en verde.

De esta manera ha configurado el primer registro de cada tabla. Obsérvese que el propósito de lo realizado hasta aquí es el de tratar de ayudar al usuario a ingresar datos de calidad a la hoja de cálculo de tal manera que lo que resta es copiar y pegar el primer registro de cada tabla en las primeras 1000 filas, igualmente, esto se podrá extender hasta el máximo de Excel.

Práctica 7, referencias

Recuerde que de esta manera Excel ofrece una forma sencilla de mantener información, pero no es una base de datos, por lo que su funcionalidad es limitada. Sin embargo, puede permitir realizar análisis de estos datos para generar información que aporte valor a la empresa.

Práctica 8. Ingreso de información

Con base en la siguiente hoja de cálculo ingrese datos, asumiendo y pensando en los procesos reales que se realizarían. Los datos que se han ingresado previamente corresponden a los datos de una empresa distribuidora de productos de panadería.

Identifique ventajas y debilidades del uso del ingreso de datos mediante el uso de Excel.

Práctica 8, datos

Documents

Excel Bases Datos