👉 Gestión de Bases de Datos

EJERCICIO 1

Necesitamos almacenar en un fichero la siguiente información:

Los datos personales de los alumnos que se matriculan en el módulo de gestión de bases de datos:

• A.- Partiendo de esos datos rellena la siguiente tabla:

• B.- Si lo guardamos en el disco duro de nuestro ordenador ¿De qué tipo de dispositivo se trata?
• C.- ¿Cual sería el tipo de acceso adecuado si queremos consultar los datos de un alumno matriculado y el fichero tiene organización secuencial indexada?

EJERCICIO 2

• A.- Encadena los siguientes registros mediante punteros para ordenarlos lógicamente de forma ascendente por el valor de su clave.

• B.- ¿De qué tipo de organización se trata?
• C.- ¿Cómo se denomina el puntero que marca el primer registro?

EJERCICIO 3

Almacena en un fichero con organización relativa aleatoria los siguientes registros:

Algoritmo de transformación para las claves:

Obtener para cada carácter de la clave el valor numérico correspondiente a su orden alfabético. Sumar los valores equivalentes a todos los caracteres y restar 32 de la cifra obtenida.

Ejemplo: FOL (6 + 16 + 12 = 34 – 32 = 2). Dirección de almacenamiento: 2

EJERCICIO 4

Con la siguiente información crea una estructura con organización secuencial indexada:

Área primaria:

EJERCICIO 5

Tenemos el esquema siguiente que representa la arquitectura que utilizamos en nuestras clases para impartir el módulo de bases de datos

Distribuye el siguiente software en función de que se instale en los clientes o en el servidor.

• Navegador Web: Internet Explorer
• S.G.B.D. MySQL
• Lenguaje anfitrión: PHP
• Sistema operativo Windows 7 professional
• Sistema operativo Windows 2008 server
• Interfaz gráfica de acceso a la base de datos: MySQL Query Browser
• Servidor Web Apache

NOTA: 5 / 10

EJERCICIO 1

Siguiendo con el caso práctico que venimos analizando a lo largo de la unidad vamos a ayudar a Alejandra a establecer un modelo lógico que recoja el funcionamiento de talleres FABER , un taller mecánico que organiza su actividad de la siguiente forma:

• Una serie de clientes traen sus vehículos a reparar. En el momento en que llega un cliente nuevo le recibe un empleado de la administración quien registra la información necesaria del cliente, del vehículo y de la posible avería.
• Con estos datos se imprime una orden de reparación que se coloca en un tablero donde el jefe de los mecánicos se la adjudica a un empleado para que efectúe la reparación, entregándole una copia de la misma.
• En esta hoja cada mecánico añade manualmente todas las actuaciones que ha llevado a cabo y el tiempo empleado y las piezas sustituidas.
• Una vez finalizada la reparación el empleado entrega la orden a la administración del taller donde se traslada esta información al ordenador.
• Cuando viene el cliente a retirar su vehículo se prepara la factura que recoge todas las actuaciones que se han llevado a cabo, su precio y el importe.

Los datos que será necesario recoger en nuestra base de datos son:

• Datos del cliente: DNI, Apellidos, Nombre, Dirección y Teléfono. Se le asigna además un Código de Cliente.
• Datos del vehículo: Matrícula, Marca, Modelo, color y la Fecha de matriculación.
• Datos previos, relativos a la reparación que se quiere efectuar que son:
• Fecha de la entrada
• Los km que tiene el coche.
• La descripción de la posible avería

Una vez efectuada la reparación se añade lo siguiente:

• Fecha de finalización
• Horas totales empleadas en la reparación.
• Si está reparado o no
• Las observaciones que se consideren necesarias

Además se necesita saber para cada reparación:

• Empleados que han intervenido y las horas que interviene cada uno. Los datos de los empleados que están registrados en nuestro taller son: DNI, Nombre, Apellidos, Dirección, Teléfono, CP, Fecha de alta y Categoría. La empresa añade un Código de empleado.
• Actuaciones que se han llevado a cabo y el tiempo empleado en cada una de ellas. Se recoge una descripción de las actuaciones que se pueden llevar a cabo: Referencia, Descripción (por ejemplo sustituir batería, etc.), tiempo estimado y precio.
• Recambios y piezas que se han sustituido y las unidades. Para ello guardamos en una tabla una lista de los recambios que se pueden sustituir: código, descripción, unidad base (por ejemplo cajas de 2 lámparas, etc.), stock y precio de referencia.
• Finalmente para elaborar la factura se necesita incluir en el encabezado: Numero de factura y fecha de la factura. El resto de los datos de la factura se obtendrán a partir del los que ya hemos recogido en la reparación.

Se pide:

• Obtener el modelo Entidad-Relación: Entidades, relaciones, atributos, claves principales, cardinalidades, tipos de correspondencia, etc.
• Obtener el modelo Relacional: pasar del modelo entidad-relación al modelo relacional aplicando correctamente las reglas de transformación, justificando la solución elegida cuando se considere necesario

NOTA: 6,3 / 10

EJERCICIO 1

Partiendo del siguiente modelo relacional que corresponde al Restaurante Mouro escribe las instrucciones en SQL necesarias para crear la base de datos:

EMPLEADOS (DNI, Nombre, Apellidos, Dirección, Ciudad, Teléfono, CP, FechaAlta, Categoría, Salario)
COCINEROS (DNI, Puesto, Especialidad)
ADMINISTRACIÓN (DNI, Cargo)
CAMAREROS (DNI, Turno, años, DNI_encargado)
COMEDORES (CodigoC, Nombre, Capacidad, Nmesas, Localización)
MESAS (CodigoM, CodigoC, Nasientos, DNI_camarero)
Hacen (CodigoM, CodigoC, Nreserva, NasientosR)
RESERVAS (Nreserva, Fhreserva, Nombre, Fhreservada, Comida/Cena, Npersonas, Varios)
FACTURAS (NFactura, FechaF, CodigoM, CodigoC)
Incluyen (NFactura, CodigoPl, Unidades)
PLATOS (CodigoPl, Nombre, Descripción, Tipo, Precio)
Contienen (CodigoPl, CodigoPr, Cantidad)
PRODUCTOS (CodigoPr, Descripción, Stock, UnidadBase, PrecioU, Categoría, CodigoPro)
PROVEEDORES (CodigoPro, Dirección, CP, Teléfono, Fax, Contacto)

Nota: Las claves primarias se indican en negrita y las claves ajenas se denotan en cursiva y negrita.

EJERCICIO 2

Sobre la base de datos Restaurante Mouro realiza las siguientes modificaciones:

Tabla EMPLEADOS

2.1. Añade un índice que facilite búsquedas frecuentes por Apellidos y Nombre sin duplicados.
2.2. El Restaurante se ha inaugurado el día 15 de Julio de 2010. Comprueba que la fecha de alta de los empleados no sea anterior a esa fecha.

Tabla CAMAREROS

2.3. El turno de trabajo sólo puede tomar 3 valores: mañana, tarde y noche. Añade esa restricción teniendo en cuenta que un camarero puede tener más de un turno (no utilices CHECK).

Tabla PROVEEDORES

2.4. Añade las columnas Apellidos y Nombre entre el código y la dirección.

Tabla MESAS

2.5. El valor por defecto del número de asientos en las mesas es 4.

Tabla PRODUCTOS

2.6. Añade un índice por Categoría. Muestra todos los índices de la tabla.
2.7. Añade una restricción en la tabla, de forma que el Stock sea entero de 4 cifras, sin signo y que no admita nulos.
2.8. Borra el índice que acabas de crear.

Tabla PLATOS

2.9. Borra la tabla PROVEEDORES. ¿Qué ocurre?. Borra previamente las claves ajenas.

BASE DE DATOS Restaurante Mouro

2.10. Borra la base de datos.

NOTA: 6,8 / 10

EJERCICIO 1

Partiendo de la base de datos TalleresFaber realiza las siguientes consultas:

Realiza las siguientes consultas:

• 1.- Pasadas las fiestas navideñas, Talleres Faber quiere enviar publicidad a sus clientes. Para ello quiere seleccionar a aquellos a los que no se les ha emitido ninguna factura desde el 6 de Enero de 2011. Obtén un listado con Nombre, Apellidos y Dirección de esos clientes.

• 2.- El gerente quiere conocer los apellidos y el nombre del empleado que más horas ha trabajado. La consulta debe mostrar 2 columnas:
• A.- Los apellidos y el nombre, separados por una coma y un espacio con el encabezado ‘Apellidos y nombre’.
• B.- Las horas totales trabajadas, con el encabezado ‘Horas totales trabajadas’.

• 3.- ¿Cuál es el importe total facturado este mes por Talleres Faber?

• A.- Hay que tener en cuenta todas las facturas registradas en la base de datos.
• B.- Es necesario recoger:
  • i.- Importe total de los recambios: PrecioReferencia*Unidades.
  • ii.- Importe total de las actuaciones: Horas*50.

• C.- La consulta devolverá una sola fila con los siguientes resultados:
  • i.- Número total de facturas emitidas.
  • ii.- Importe total facturado.

• D.- Utilizar alias para cambiar los encabezados.

• 4.- Obtener un listado con el número de reparaciones en las que ha intervenido cada uno de los empleados. El listado debe mostrar:
  • A.- El número de reparaciones, los apellidos y el nombre del empleado.
  • B.- El número de reparaciones debe aparecer en orden descendente.

• 5.- Los socios del taller pretenden dar un servicio cada vez más rápido a sus clientes. Para ello han previsto que las reparaciones se realicen antes de 2 días. Interesa conocer las reparaciones que han superado esa previsión y los empleados que la han realizado. Las columnas que se deben mostrar son:
  • A.- Nombre de los empleados, Fecha de entrada, Fecha de salida y Fecha de salida prevista (2 días a partir de la fecha de entrada).
  • B.- Combinar las tablas utilizando la Combinación interna (INNER JOIN).

• 6.- La administración quiere comprobar que los únicos empleados que no han intervenido en reparaciones son aquéllos que se encargan de labores de organización y administración del taller. Hacer una consulta que devuelva los nombres y categorías de los empleados que no han intervenido en ninguna reparación.

• 7.- Interesa conocer cuál es el vehículo con más kilómetros que ha visitado el taller. Mostrar los kilómetros, la marca, el modelo, el color y la matrícula.

• 8.- En Talleres Faber están pensando en hacer más rentable la gestión de los recambios. Se pretende optimizar el stock, para ello se solicita un listado con los siguientes datos: IdRecambio, Descripción, UnidadBase y Stock, de los artículos que superen el stock considerado como óptimo, según los siguientes criterios:
  • A.- Para los artículos cuya unidad base se mide como una unidad se considera el stock óptimo hasta 10 artículos.
  • B.- Para los artículos cuya unidad base no sea una unidad el stock considerado como óptimo es hasta 4 artículos.

• 9.- Con relación a las actuaciones, se considera importante saber cuántas reparaciones se realizan de importe superior a la media. La consulta nos devolverá un solo dato: el número de reparaciones realizadas cuyo importe supera el importe medio de las actuaciones.

• 10.- Obtener un listado que nos informe del número de facturas que hemos emitido a cada cliente. El listado tendrá dos columnas: el nombre completo del cliente y el número de facturas que le corresponden.

NOTA: 6,8 / 10

EJERCICIO 1

La empresa quiere recoger en una tabla las actuaciones cuyo tiempo de realización no coincide con el tiempo estimado. Para ello debes seguir los siguientes pasos:

• Crea una tabla denominada DIFERENCIAS con 3 columnas:
• Referencia.
• Descripción.
• Diferencia.
• Inserta en ella una fila por cada actuación en la que el tiempo estimado de la tabla ACTUACIONES no coincida con las horas realmente recogidas en la tabla Realizan.

Para las columnas Referencia y Descripción elige los tipos de datos y tamaño coincidentes con las columnas de la tabla ACTUACIONES y en la columna Diferencia recoge la diferencia entre Horas y TiempoEstimado.

EJERCICIO 2

Con los datos obtenidos en la consulta anterior modificaremos los tiempos estimados en la tabla ACTUACIONES con las diferencias guardadas en la tabla DIFERENCIAS sumando las Diferencias al TiempoEstimado.

EJERCICIO 3

La empresa decide ascender de categoría al trabajador que más horas ha trabajado. La nueva categoría asignada será Oficial de 1ª mecánico.

EJERCICIO 4

Debido a la crisis del sector, la empresa decide reducir la plantilla. Esta reducción afectará a los empleados que hayan intervenido en 2 reparaciones o menos y que se hayan dado de alta en la empresa hace menos de 3 años.

NOTA: Para resolver este ejercicio utiliza funciones de fecha, no utilices fechas como constantes.

EJERCICIO 5

El cliente Enrique Muriedas nos ha solicitado telefónicamente que le enviemos la factura de la reparación de referencia IdFactura=12. Los datos que teníamos registrados de esa factura son distintos. Reemplazar en la tabla FACTURAS los datos anteriores por los nuevos datos que nos ha suministrado el cliente. Estos datos son:

IdFactura: 12, FechaFactura: '2011-10-03', CodCliente: '00005', IdReparacion:3;

NOTA: No utilices la sentencia UPDATE.

EJERCICIO 6

Uno de los vehículos registrados en nuestra base de datos ha cambiado de propietario. Registrar en una transacción ambos cambios:

• Añadir a la tabla CLIENTES los datos del nuevo propietario:

CodCliente='00011', DNI='112233445F', Apellidos='Campos, Vázquez', Nombre='Miguel Ángel', Direccion='Calle del Cid, nº 23, 1ºA, Santander', Telefono='345764423';

• Modificar en la tabla VEHICULOS el CodCliente del nuevo propietario del vehículo de matrícula '1122 ABC'

En medio de ambas modificaciones situar un punto de retorno (SAVEPOINT) para poder deshacer la transacción hasta ese punto si hay algún error.

Finalmente la venta del vehículo no se confirma, pero decidimos mantener registrado el nuevo cliente.

EJERCICIO 7

La empresa decide borrar de la tabla VEHICULOS todos aquellos vehículos que no hemos reparado en ninguna ocasión, por tanto no se encuentran referenciados en la tabla REPARACIONES.

EJERCICIO 8

Un cliente nuevo nos ha traído su vehículo al taller el día 03/03/2011. En recepción se registran los siguientes datos:

• Del cliente.- Código: 00011, Nombre y apellidos: Tomás Gómez Calle, Teléfono: 22334455
• Del vehículo.- Matrícula: 3131 FGH, Modelo: Renault Scénic, matriculado el 17/03/2009, 105.000 km,
• De la reparación.- Sustitución de las lámparas delanteras.

Diseñar las consultas necesarias para recoger estos datos en las tablas.

EJERCICIO 9

Avisamos al cliente de la reparación anterior para que pase hoy a recoger su vehículo que ya ha sido reparado.

• Datos del recambio sustituido.- Código: LD_222_777, Unidades: 1 unidad
• Empleado que ha realizado la reparación.- Código: 90000, Horas empleadas: 0,15
• Datos de la actuación: Código: 1110008888, horas: las mismas.

Registrar las operaciones necesarias en todas las tablas afectadas, recogidas en una transacción. (REPARACIONES, Incluyen, RECAMBIOS, Intervienen, Realizan).

EJERCICIO 10

Registrar dos transacciones simultáneas sobre la misma tabla utilizando el bloqueo a nivel de tabla LOCK TABLE. Para poder simular transacciones simultáneas puedes abrir dos sesiones distintas con MySQLWorkbench. Bloquea la tabla para la primera transacción y antes de desbloquear intenta ejecutar la segunda.

• Transacción 1: Desde recepción se registra una nueva entrada en la tabla REPARACIONES con los siguientes datos: Matricula: 2233 ABC, Km: 65000 Avería: sustituir el filtro del aire.
• Transacción 2: La reparación de código IdReparación=10 ha finalizado hoy. En Administración registran los cambios que afectan a esa tabla en las columnas FechaSalida, Reparado y Observaciones.

NOTA: 8,5 / 10

EJERCICIO 1

Crea un procedimiento que muestre los vehículos (marca, modelo y color) que no estén reparados y los datos de los clientes y vehículos que han entrado a reparar hoy. (En nuestro caso ninguno).

EJERCICIO 2

1. Realiza un procedimiento que reciba la matrícula de un vehículo y escriba las características del automóvil y el número de reparaciones que ha sufrido ese automóvil, los empleados que han realizado esas reparaciones y los datos de los vehículos de la misma marca.
2. Hacer una llamada al procedimiento creado.

EJERCICIO 3

Modifica el procedimiento anterior añadiendo un HANDLER que controle que si esa matrícula no está en la base de datos, el resto de instrucciones no se ejecuten.

EJERCICIO 4

Crea una función que actualice el estado de las reparaciones que estén finalizadas en una fecha que se indique y que devuelva cuantas reparaciones han finalizado en esa fecha.

EJERCICIO 5

Crea un procedimiento para dar de alta una nueva reparación para un vehículo y un cliente que no tenemos registrado. Llama al procedimiento ReparacionClienteNuevo. Incluye un HANDLER que controle que si insertamos un cliente y/o un vehículo que ya existen, el resto de sentencias continúen ejecutándose, y se añade como mínimo la nueva reparación. Para probar el procedimiento toma como referencia los datos siguientes: (tomados de un
ejercicio de la unidad anterior).

Un cliente nuevo nos ha traído su vehículo al taller el día 03/03/2011. En recepción se registran los siguientes datos:

• Del cliente.- Código: 00011, Nombre y apellidos: Tomás Gómez Calle, Teléfono: 22334455.
• Del vehículo.- Matrícula: 3131 FGH, Modelo: Renault Scénic, matriculado el 17/03/2009, 105.000 km
• De la reparación.- Sustitución de las lámparas delanteras.

EJERCICIO 6

Creación de funciones:

1. Diseña una función que calcule el importe de los recambios sustituidos en una reparación.
2. Crea una función que devuelva el importe de las actuaciones que se llevan a cabo en una reparación (para calcular el importe multiplica las horas por el importe de cada actuación).

En ambas funciones Pasar como variable el Id de la reparación.

3. Diseñar una consulta que calcule el importe total (mano de obra y recambios) de las
   reparaciones que se le hayan realizado al vehículo de matrícula '1313 DEF'.

EJERCICIO 7

Crea una función que reciba como parámetro de entrada el número correspondiente a un mes y devuelva el importe total facturado ese mes. Utiliza para ello las dos funciones obtenidas en la práctica anterior.

NOTAS:

• Por ejemplo para Enero, número del mes 1.
• Utilizar un cursor para recorrer cada fila de la consulta de los IdReparacion que se obtengan en ese mes.
• Controla mediante un HANDLER que la consulta haya devuelto alguna fila.

EJERCICIO 8

Crea un trigger que, antes de insertar una fila en la tabla Incluyen, compruebe si existen unidades en Stock en la tabla RECAMBIOS llevando a cabo las siguientes acciones:

• Si hay suficientes unidades actualiza el Stock restando las unidades que se van a insertar.
• Si no hay suficientes unidades en Stock cancela la inserción de las unidades.

EJERCICIO 9

1. Crea una tabla denominada PedidoRecambios que contenga 3 columnas:

• IdRecambio.
• Descripcion.
• Stock.

Con los mismos tipos de datos que tienen esas columnas en la tabla RECAMBIOS.

2. Crea un trigger asociado a la tabla RECAMBIOS que después de actualizar el Stock de un recambio, si el número de unidades en Stock del recambio modificado es inferior a 4 unidades, inserte una fila en la tabla PedidoRecambios con los datos resultantes.

EJERCICIO 10

Utilizando funciones de librerías disponibles en MySQL obtener:

1. Un listado con dos columnas: en la primera, en mayúsculas apellidos y nombre de todos los clientes (entre los apellidos y el nombre incluir una coma como separador) y en la segunda, la ciudad en la que cada cliente tiene su domicilio (únicamente la ciudad, no la dirección).
2. Un listado con 2 columnas: en la primera la fecha de alta de los empleados con el formato dd/mm/aaaa y en la segunda aparecerá 'Contrato temporal ' para aquellos empleados que lleven contratados en el taller menos de 2 años, y 'Contrato fijo' para el resto.

NOTA: 8,2 / 10

EJERCICIO 1

Activar los ficheros de registro log utilizando los siguientes nombres:

• Log general con el nombre TuNombre.log
• Log binario con el nombre TuNombre-bin
• Log de errores con el nombre TuNombre.err
• Log de consultas lentas TuNombre-slow.log

1. Muestra en una captura de pantalla la activación de esos ficheros, bien desde una herramienta gráfica o mostrando el contenido del fichero my.ini.
2. Ejecuta alguna consulta SELECT, UPDATE…, para y arranca el servidor para generar los log correspondientes e imprime una captura de pantalla de la carpeta donde se guardan esos registros.

EJERCICIO 2

Partiendo de la base de datos TalleresFaber_MyISAM, escribe las sentencias necesarias para detectar y corregir daños en las tablas, siguiendo el proceso general descrito:

1. Verificar todas las tablas en buscar de errores incluyendo las opciones necesarias para comprobar los enlaces borrados así como el checksum.
2. Hacer una copia de seguridad de la tabla Actuaciones.
3. Reparar la tabla Actuaciones.
4. Restaurar la tabla Actuaciones desde la copia de seguridad.
5. Mostrar el contenido del último fichero de registro binario.

De cada uno de los apartados capturar la pantalla con la solución devuelta.

** Recuerda que para utilizar LOAD DATA debes tener local-infile=1(activado) en my.ini

EJERCICIO 3

Migrar la base de datos en Access CompraVenta.mdb a MySQL.

Para documentar esta práctica debes imprimir las pantallas de conexión a ambos SGBD y posteriormente mostrar la base de datos creada en MySQL.

EJERCICIO 4

Hacer una copia de seguridad de la base de datos TalleresFaber_MyISAM a un fichero SQL. Para documentar el resultado:

• Si lo ejecutas desde la línea de comandos:
• Escribir el comando completo.
• Mostrar el fichero SQL obtenido.
• Si utilizas una herramienta gráfica:
• Mostrar la pantalla con la selección de los datos.
• Mostrar el fichero SQL obtenido.

NOTA: 10 / 10