Ejercicio: (difícil) Alquiler de bicicletas de la ciudad

Enunciado #

GreenWheels alquila bicicletas eléctricas en la ciudad. Actualmente trabaja con hojas de cálculo y necesita una base de datos para registrar bicicletas, clientes, técnicos, mantenimientos y alquileres. La empresa quiere consultar el historial por bicicleta, sus clientes más habituales y el gasto de mantenimiento.

Datos que debes almacenar #

Se usan identificadores internos salvo que se indique una clave natural. Los campos opcionales deben admitir NULL. La lista fija nombres y tipos para poder comparar tu diseño con la solución del challenge.

Flota y mantenimiento #

• Bicicleta: codigo_bicicleta (texto), marca (texto), modelo (texto), fecha_compra (fecha), estado (enum), kilometros_totales (decimal).
• Tecnico: id_tecnico (entero), nombre (texto), especialidad (texto), telefono (texto).
• Mantenimiento: id_mantenimiento (entero), codigo_bicicleta (texto), id_tecnico (entero), fecha (fecha), tipo (texto), coste (decimal).

Clientes y alquileres #

• Cliente: id_cliente (entero), nombre_completo (texto), dni (texto, único), telefono (texto), email (texto), direccion (texto).
• Alquiler: id_alquiler (entero), id_cliente (entero), codigo_bicicleta (texto), inicio (fechahora), devolucion (fechahora, opcional), precio_final (decimal, opcional), incidente (texto, opcional).

Claves, relaciones y reglas del negocio #

1. Bicicleta usa codigo_bicicleta como PK natural de texto. Las otras tablas usan su campo inicial id_... como PK. El DNI del cliente es único; los nombres de clientes y técnicos no lo son.
2. Una bicicleta puede tener muchos mantenimientos y cada mantenimiento pertenece a una bicicleta y a un técnico. Guarda el técnico mediante FK, no repitiendo su nombre en cada reparación. Aunque actualmente haya tres técnicos, el modelo debe admitir más.
3. Cada alquiler corresponde a un cliente y a una bicicleta. Ambos pueden tener muchos alquileres a lo largo del tiempo; no marques sus FK como únicas.
4. devolucion y precio_final permanecen nulos hasta cerrar el alquiler. incidente es opcional: NULL indica que no se ha registrado ninguno. El precio final no puede ser negativo y debe estar informado cuando exista devolución.
5. Una bicicleta no puede tener dos alquileres simultáneos, incluidos los que ya tengan fecha de devolución registrada. Usa intervalos [inicio, devolucion); una devolución nula deja el intervalo abierto. Un CHECK por fila no impide el solapamiento: hace falta una restricción de exclusión o una validación transaccional adecuada al motor SQL.
6. estado de bicicleta admite disponible, en_mantenimiento, alquilada o retirada. La apertura y cierre de un alquiler deben mantener ese estado coherente y comprobar la disponibilidad dentro de la misma transacción. Los kilómetros totales se actualizan con el uso; no se pide guardar kilómetros por alquiler.
7. Los kilómetros, los costes y los precios no pueden ser negativos. No se fija un máximo de alquileres por cliente ni se exige que cada email sea único.
8. El historial se obtiene filtrando Alquiler por bicicleta y ordenando por inicio. El ranking de clientes se obtiene contando sus alquileres y el gasto por bicicleta sumando Mantenimiento.coste. Son consultas agregadas, no nuevas tablas ni columnas acumuladas.

Diseña las cinco tablas y sus cuatro relaciones. Justifica además cómo impedirías alquileres simultáneos al implementar la base de datos.

CHECK que debes configurar #

Escribe cada expresión exactamente en el campo indicado; los demás CHECK se dejan vacíos.

En Alquiler, campo devolucion:

1devolucion IS NULL OR devolucion >= inicio

En Alquiler, campo precio_final:

1(precio_final IS NULL OR precio_final >= 0) AND (devolucion IS NULL OR precio_final IS NOT NULL)

Criterios para el challenge #

Conserva los nombres y tipos indicados. Todos los campos son obligatorios (NN) salvo los marcados como opcionales. Marca PK y FK según las reglas y las restricciones de unicidad indicadas. Una PK compuesta identifica por la combinación; no marques cada componente como único por separado.

Dibuja cada conexión desde el campo referenciado hacia la FK. Usa extremos 1 y n, o 1 y 1 para las relaciones de uno a uno. La opcionalidad se expresa con NN desmarcado en la FK. Si una FK es compuesta, dibuja una conexión por cada componente; en SQL todas esas columnas deben formar una única FOREIGN KEY hacia la misma fila.

Deja desactivados autoincremento, clave alternativa e índice. Deja vacíos valor por defecto, ON DELETE, ON UPDATE y CHECK, salvo los CHECK indicados expresamente. Las restricciones compuestas adicionales, los índices de explotación y las reglas entre varias filas se documentan aparte: el corrector del diagrama no ejecuta operaciones del negocio ni sustituye esas validaciones.