Práctica tres

Creación de una base de datos para un centro de renta de películas

Julio Carlos Delgado Hernández Abril, 2011

Objetivo:

Desarrollar la base de datos de un centro de renta de películas de video, utilizando las funciones y macros de Excel.

Materiales.

PC con suite Microsoft Office instalada y con acceso a Internet.

Desarrollo.

El manejo de centros de préstamo de materiales (tales como bibliotecas, centros de renta de videos, centros de préstamo de bicicletas, etc.) exige de parte de los operadores de la base de datos la realización de tres tipos de movimientos:

1. El registro de nuevos usuarios a la base de datos; 2. El registro de nuevos préstamos a un usuario en particular; 3. El registro de la devolución de uno o todos los materiales prestados con anterioridad por

parte del usuario.

Cada uno de estos movimientos requiere el manejo de tablas independientes para el almacenamiento de información, aunque todas ellas deben de hacer referencia al usuario que hace uso de los servicios de estos centros (al darse de alta, al solicitar o al devolver material, según sea el caso)

A fin de desarrollar una base de datos capaz de solucionar esta problemática sobre una herramienta básica como lo es el software denominado “Excel”, desarrollado por Microsoft systemsR se propone resolver la práctica siguiente.

Por parte del instructor se desarrolla el ejemplo de las funciones a utilizar en cada una de las tablas, quedando como tarea por parte del estudiante el definir la manera en que las funciones deben de adecuarse a las necesidades de la tabla en su conjunto.

Creación de la tabla uno: formato de la plantilla

A fin de brindar al operador de la base de datos del centro de renta de videos la oportunidad de capturar los datos de un nuevo usuario mediante una interfaz gráfica amigable, se propone desarrollar una plantilla que tendrá el formato propuesto en la figura 1.

El ID de usuario es un identificador único que permite reconocer de manera inequívoca los datos correspondientes al status de la cuenta de un usuario en particular. En condiciones reales se utilizan identificadores tales como el RFC o la CURP de los usuarios, pues estos datos son validados por ley y son irrepetibles. Si la base de datos es realizada de manera correcta, debe de agregarse un mecanismo que impida ingresar los datos de la misma persona dos veces (esto es, que una persona, habiendo sido registrada con anterioridad, solicite un registro nuevo, o que alguna persona quisiera suplantar la identidad de otra haciendo uso de alguna identificación ya registrada). Estos mecanismos de validación de usuarios quedan fuera del alcance de esta práctica, quedando a decisión del estudiante su búsqueda y agregado a los resultados de la práctica.

Figura 1. Interfaz de captura de datos de un nuevo usuario.

Los datos de los usuarios nuevos se introducen usando una interfaz como la mostrada en la figura 1, siendo almacenados en la base de datos del centro de renta de videos.

1. Abrir un archivo Excel. 2. Nombrar a las tres primeras hojas de documento como sigue: hoja 1 – “Plantillas”; hoja 2 –

“Listas desplegables”; hoja 3 – “Base de datos” (Figura 2).

Figura 2. Denominación de las tres hojas del documento Excel.

3. Guardar el documento en “Mi escritorio” con el nombre “Renta de videos”. Asegúrese de guardarlo como “Libro de Excel habilitado para macros” (figura 3).

Figura 3. Guardado del archivo.

Para desarrollar la plantilla mostrada en la figura 1, en la página marcada como “Plantillas” se debe de preparar un área que sea fácilmente distinguible y los botones donde se capturarán los datos del usuario.

4. Se marca un área de 7 columnas por 9 renglones (celdas C3 : I11) con un color de fondo, dándose mayor longitud a las columnas (15) y mayor altura a las filas (25) – (figura 4).

Figura 4. Formato del área de la primera plantilla (se muestran los valores de ancho y alto de las celdas juntos con fines demostrativos, los parámetros de configuran por separado).

5. Se capturan el nombre de la plantilla y los nombres de las variables de captura.

Figura 5. Captura de los nombres de la plantilla y las variables.

6. El formato de las celdas donde se capturan los datos de los usuarios es como sigue: Click en la celda a formatear click derecho formato de celdas color (se elige uno claro con línea delgada) bordes superior e izquierdo color nuevamente (se elige esta vez uno oscuro, con línea gruesa) bordes inferior y derecho Aceptar.

7. Copie el formato de la celda recién formateada (inicio copiar formato) y pegue enfrente de cada una de las variables capturadas en el paso 5. Puede agregar un contorno final a el área de la plantilla completa (seleccionar todas las celdas del área con color de fondo click derecho formato de celdas bordes color (elija un color oscuro) línea gruesa contorno aceptar). El resultado final será uno similar al mostrado en la figura 6.

Figura 6. Formato de la plantilla de captura de nuevos usuarios.

Una vez concluidos los pasos necesarios para la creación de la primera plantilla, puede empezar a desarrollarse la primera base de datos.

Creación de la tabla uno: captura y almacenamiento de datos

Los datos capturados en la plantilla de nuevo usuario deberán de ser almacenados en la base de datos de los usuarios, que se localizará en la hoja de “Base de datos” de nuestro documento.

Se recomienda dar una altura a los renglones de esta hoja de 25; el ancho de las columnas variará en dependencia del valor que los datos tomen. En esta base de datos todos las variables tienen un

valor alfanumérico (excepto el número telefónico, pero no se realizarán operaciones con ese valor, por lo que se recomienda dar un formato de datos “general” a las cuatro primeras columnas de esta tabla – inicio general).

En el primer renglón de las columnas A – D, capture el nombre de las variables de entrada (ID del usuario, nombre, apellidos, teléfono), cuidando de no dejar espacios entre palabras ni usar símbolos especiales en su denominación (figura 7).

Figura 7. Primeras 4 columnas de la tabla de usuario.

Para almacenar los datos capturados se hará uso de una función “macros”, que permita copiar los datos capturados desde la plantilla en la base de datos.

8. Desde la hoja “Plantillas” click en la pestaña vista macros grabar macro identificar la macro (“ingreso”) aceptar.

Es importante hacer notar que una vez marcado el inicio de la grabación de la macro, todos los comandos que se realicen con el mouse o el teclado van a alterar la función macro, por lo que se ha de ser cuidadoso en su ejecución.

9. Click en la celda formateada de nuestra plantilla donde se capturará el ID del usuario click derecho copiar click en la hoja “Base de datos” click en la primera celda disponible de la columna marcada con el nombre de la variable que se está copiando (en este caso, A2) click derecho insertar celdas copiadas deslazar las celdas hacia abajo aceptar.

10. Regresar a la hoja de “Plantillas” y repetir el paso 9 con todas las variables que se están almacenando en la base de datos (Nombre, Apellidos, Teléfono).

11. Regresar a la hoja “Plantillas”. Click en la primera celda que copiamos suprimir. Repetir el paso para todas las celdas copiadas. Esto garantizará que una vez vaciados los datos de la plantilla a la base de datos, la plantilla se limpiará y quedará lista para la captura de los datos de otro usuario. Una vez culminado el proceso, click en cualquier celda de la hoja.

12. Desde la hoja “Plantillas” click en la pestaña vista macros detener grabación.

Con esto se concluye la grabación de la función por la cual, después de capturar los datos de un usuario nuevo, sus datos habrán de ser vaciados a la base de datos, borrándose de la plantilla. Para comprobar el funcionamiento del macros:

13. Capturar cualquier dato en las celdas marcadas, después click en la pestaña vista macros ver macros – click en el macro “ingreso” ejecutar. La pantalla parpadeará debido a la ejecución de la función y los datos desaparecerán de la pantalla.

14. Al dar click en la hoja “Base de datos”, podremos ver los datos vaciados en la hoja “Base de datos” (figura 8).

Figura 8. Comprobación del funcionamiento del macros.

A fin de automatizar el proceso de vaciado de datos, agregaremos un botón sobre el que, al hacer click, los datos se almacenen. Puede ser utilizada cualquier imagen obtenida desde internet, o una figura cualquiera insertada como imagen en la hoja de cálculo. Colóquela en la esquina inferior derecha de la plantilla.

15. Click sobre la imagen click derecho asignar macro seleccionar la macro “ingreso” aceptar (figura 9).

Figura 9. Automatización del macro

llPara comprobar su funcionamiento, agregamos nuevamente datos a la tabla, que deberán de quedar registrados en la base de datos y desaparecerán de la plantilla.

Figura 10. Automatización del macro.

De esta manera queda concluida la primera tabla de nuestra base de datos, la correspondiente a los usuarios. Restan por desarrollar las referentes a los servicios de préstamo y devolución.

Creación de la tabla dos: captura y almacenamiento de datos

La creación de la segunda plantilla tiene el mismo principio que la de nuevo usuario, a excepción de los identificadores de campos que contiene: ID de usuario, Título, Director, Protagonista, Año, Tarifa por día y fecha de préstamo. El formato final debe de ser muy similar al mostrado en la figura 11.

Figura 11. Plantillas de nuevo usuario y de préstamo de películas.

Ambas plantillas se encuentran en la hoja “Plantillas” y el hecho de que compartan renglones es circunstancial: podrían estar compartiendo columnas de igual manera.

La manera en que los campos de cada plantilla se llenan es, sin embargo, diferente.

Los datos de los campos de la segunda plantilla provienen, casi completamente, de información previamente adquirida (la información de las películas, capturada de acuerdo a su adquisición; la de los usuarios, con la ayuda de la primera plantilla), de manera tal que, básicamente, se requiere de listar datos previos para escoger de entre ellos los necesarios para llenar los campos de cada registro.

Además, una vez definido el título de la película que se quiere rentar, los datos de su director, protagonista, año de aparición y tarifa a aplicar por día de renta no se agregan de manera manual: eso significaría que el operador debería de saberse de memoria todos esos datos!

Así, los campos de “ID del usuario” y de “Título” se escogen al listar a los usuarios del centro de video y a los títulos de las películas disponibles, respectivamente; director, protagonista, año y tarifa por día dependen del título. El último campo, “Fecha de entrega”, puede llenarse de manera manual; sin embargo, a fin de estandarizar el formato de fecha, se usará una lista desplegable (de otra manera, cada operario podría usar un formato propio y el 4 de abril del 2002, por ejemplo, podría ser escrito como 04.04.2002; abril 4, 2002; 4 de abril del 2002; lunes 4 de abril del 2002; etc.)

A fin de crear las listas desplegables de los datos de las películas, en la hoja “Listas desplegables” se asientan los datos de las películas, como se muestra en la figura 12.

El listado de fechas pudo hacerse de manera diferente (en una columna los 31 posibles valores del día; en otra, los 12 nombres de los meses y en una última, listar los años; sin embargo, un formato como el que se propone simplifica el cálculo de los pagos a realizar por parte de los usuarios, como puede notarse más adelante).

Los títulos de las películas deben de ser unidos bajo un nombre de grupo (al igual que las fechas de posible renta de la película) que permita listarlos (al igual que los identificadores de usuario de la tabla de usuarios contenida en la hoja “Base de datos”).

Figura 12. Datos de las películas en existencia del club

16. Seleccionar las celdas 1 a 200 de la columna “Título” click derecho asignar nombre a un rango definir su nombre (que debe de coincidir EXACTAMENTE con el nombre de la columna que aparece en el primer renglón) aceptar (figura 13).

Figura 13. Creación de grupos (función “nombres”)

17. Repetir el paso 17 con los datos de las fechas de la misma tabla y con los ID de los usuarios de la tabla que se encuentra en la hoja “Base de datos”

Con los valores de las listas ya definidos (de los usuarios y de las películas), pueden definirse las listas que deberán de aparecer en los campos de la plantilla dos.

18. Click en el campo “ID del usuario” de la plantilla “Préstamo de película” pestaña datos validación de datos validación de datos pestaña configuración permitir: lista origen: =Idusuario aceptar

Como resultado, al dar click en la flecha que aparecerá del lado izquierdo de la celda, se listarán los identificadores de los usuarios registrados (figura 14).

Figura 14. Listado de los identificadores de usuario de la tabla “Base de datos”

19. Repetir el paso 18 para los campos “Título” y “Fecha de entrega”.

Para que los valores relativos al director, protagonista, año y tarifa por día aparezcan de manera automática, se usará la función “DESREF”. Esta función permite crear referencias tanto a una celda única como a un rango de celdas (en esta caso, habrá de usarse en su primera modalidad, haciendo referencia al título de la película). La sintaxis que ha de utilizarse es

DESREF(ref, COINCIDIR(valor de coincidencia, rango de búsqueda:separado por dos

puntos, valor de coincidencia) valor de cuantas columnas a la derecha de la

columna de búsqueda debe desplegarse)

“ref” nos indica la celda de referencia, a partir de la cual se deberá de realizar la búsqueda del valor indicado en el campo “Título”;

COINCIDIR es una función que permite buscar la coincidencia de un valor de variable (“valor de

coincidencia”) dentro de un rango definido por dos valores de celda (separados por dos puntos). Esto significa que la búsqueda puede realizarse en una columna o en varias, en dependencia de los valores que se asignen a las celdas de inicio y de fin de búsqueda;

“valor de coincidencia” implica el valor lógico de la coincidencia que se busque: un valor de 0, por

ejemplo, implica una coincidencia total del valor que se pudiera encontrar en el rango de búsqueda contra el “valor de coincidencia”;

Fuera del paréntesis de la función COINCIDIR se marca el número de columnas a la derecha de la celda de referencia del cual se ha de extraer el valor que aparecerá en el campo donde se aplica la función.

Por ejemplo, para obtener el nombre del director de la película “El ogro” de la tabla que aparece en la hoja “Listas desplegables”, debe de usarse la fórmula como sigue:

=DESREF('Listas desplegables'!A1,COINCIDIR(O8,Título,0),1)

Donde “„Listas desplegables‟!A1” marca el inicio de la columna “Títulos” de la hoja “Listas desplegables”, donde se encuentran todos los títulos de película disponibles en el centro de video;

“O8” es la celda donde se encuentra la lista desplegable de los títulos, donde se definió que título va a rentar el usuario;

“Título” es el rango de celdas agrupadas con el nombre “Título”, esto es, las celdas que contienen todos los títulos de las películas disponibles;

“0” significa que la coincidencia debe de ser total con el título de la película y

“1” significa que el valor que deberá de aparecer será el de la primera columna a la derecha de la columna “Título”.

20. Definir la fórmula DESREF de acuerdo a la explicación anterior para todas las celdas cuyo valor depende del título de la película en la tabla “Préstamo de película”.

Al finalizar de definir la fórmula DESREF para todas las variables de la película, deberá de aparecer una tabla semejante a la que se muestra en la figura 15.

Figura 15. Resultados de la captura de datos en la tabla “Préstamo de película”

Ejercicio a resolver: Considere el formato final de la tercera tabla, “regreso de película”, como se muestra en la figura 16.

Figura 16. Tabla “Regreso de película”