## Contexto “MotorAndes” es una concesionaria que vende vehículos nuevos y usados, ofrece **test drives** y servicios básicos de taller (mantenimientos y reparaciones menores). Opera con varias **sucursales** y trabaja con **proveedores** de accesorios. ## Objetivo Modelar y construir una base de datos relacional que permita registrar inventario, clientes, vendedores, ventas, pagos, test drives y servicios de taller; además, responder consultas analíticas frecuentes del negocio. ## 1) Modelado (E/R → Relacional) 1. **Identifique entidades y atributos**: - Marca, Modelo, Vehículo (VIN, estado: {nuevo, usado}, año, precio_lista, km), - Sucursal (dirección, ciudad), StockSucursal (cantidad, precio_publicado), - Cliente (persona/empresa), Vendedor (rol de empleado), - Venta (fecha, total, forma_pago), DetalleVenta (vehículo o accesorio, cantidad, precio_unitario), - Pago (fecha, monto, medio: {efectivo, tarjeta, transferencia, financiación}), - TestDrive (fecha, resultado), - ServicioTaller (tipo: {mantenimiento, reparación}, costo_estimado, costo_final, fecha_ingreso/egreso), - Proveedor, Accesorio. 2. **Relaciones clave** (ejemplos, puede proponer más): - Modelo pertenece a una Marca. - Vehículo es de un Modelo. - StockSucursal vincula Vehículo con Sucursal (publicación). - Venta la realiza un Vendedor a un Cliente en una Sucursal. - Cada Venta tiene Detalle(s) (vehículo y/o accesorios). - Pago(s) asociados a una Venta. - TestDrive vincula Cliente–Vehículo–Vendedor–Sucursal. - ServicioTaller vincula Cliente–Vehículo–Sucursal (y opción de Vendedor que abre OT). 3. **Normalización**: justifique decisiones hasta **3FN** (o BCNF si aplica) para evitar redundancias y anomalías. ## 2) Implementación SQL (LDD + LMD) 1. **CREATE TABLE** con: - **PRIMARY KEY**, **FOREIGN KEY**, **UNIQUE**, **NOT NULL**, **CHECK** (por ejemplo: año ≥ 1990; km ≥ 0; medio_pago ∈ conjunto permitido; estado ∈ {‘nuevo’, ‘usado’}; costo_final ≥ 0 y costo_final ≥ costo_estimado). - **Restricciones de integridad referencial** coherentes (ON DELETE/UPDATE según su criterio). 2. **Datos de prueba mínimos**: - ≥3 Marcas, ≥6 Modelos, ≥12 Vehículos (mezcla nuevo/usado), ≥2 Sucursales, - ≥8 Clientes, ≥4 Vendedores, ≥8 Ventas con sus Detalles y Pagos, - ≥6 TestDrives (con casos exitosos y no), ≥5 ServiciosTaller, - ≥6 Accesorios y ≥2 Proveedores (vincule accesorios a proveedores). 3. **Transacciones**: defina al menos **1** procedimiento de carga que **inicie transacción**, inserte Venta + Detalles + Pagos y **valide**: - que cada Vehículo vendido esté **publicado** en esa Sucursal y no haya sido vendido antes, - que la **suma de Pagos = total** de la Venta; - si falla, **ROLLBACK**; si pasa, **COMMIT**. _(Nota: use `BEGIN`/`COMMIT`/`ROLLBACK` según su SGBD; no se piden triggers.)_ ## 3) Consultas que el sistema debe responder (SQL SELECT) Implemente cada consulta usando **joins** apropiados; cuando corresponda, utilice **left/right/full outer join** para no perder información (por ejemplo, clientes sin compras). A) Operativas: 1. Listado de Vehículos **disponibles** por Sucursal (modelo, año, estado, precio_publicado), ordenado por precio descendente. 2. Top 5 **Modelos más vendidos** del último trimestre por cantidad y facturación. 3. **Clientes** con al menos un **TestDrive** que **no** terminaron comprando en los 30 días siguientes. 4. Ventas por **Vendedor** con total vendido y ticket promedio, filtrable por rango de fechas. 5. **Pagos** rechazados o incompletos (si simula estado), y ventas con **saldo pendiente**. B) Analíticas/mixtas: 6. **Margen estimado** por venta: total – (precio_lista del/los vehículo/s ± costos de accesorios). Explique su criterio. 7. Tiempo promedio entre **ingreso y egreso** de ServiciosTaller por tipo y sucursal. 8. **Conversión** de test drives a ventas por modelo. 9. Vehículos **usados** con **> 60.000 km** sin vender luego de 45 días publicados. 10. Ventas con **combos** (vehículo + ≥2 accesorios) y su uplift vs. ventas sin accesorios. ## NO HACER ESTO: 4) Vistas (Reto para cada pareja) -lo realizaremos más adelante- Cree al menos **2 vistas** para reportes frecuentes: - `vw_inventario_disponible` (unifica Modelo–Vehículo–Sucursal–precio_publicado). - `vw_performance_vendedores_mes` (ventas, ticket promedio, conversión de test drive). Explique por qué son vistas (y no tablas) y los beneficios en seguridad/autorización. ## 5) Reglas de negocio (mínimo 5) Ejemplos (impleméntelas con CHECK/UNIQUE/FK y lógica transaccional): 1. Un **VIN** identifica de forma **única** un Vehículo. 2. Un Vehículo **solo puede venderse una vez**. 3. Un TestDrive requiere **licencia** del cliente (campo obligatorio). 4. Una Venta en Sucursal X solo puede referenciar Vehículos **publicados** en Sucursal X. 5. `costo_final ≥ costo_estimado` y ambos no negativos. 6. Si `estado = 'nuevo'`, entonces `km = 0`. ## 6) Entregables 1. **Modelo E/R** (Drawdb) 2. Código **DDL** (creación de esquemas, restricciones). 3. Código **DML** de carga mínima. 4. Código de **consultas** (~~10 pedidas~~ cualquiera de ellas pero al menos 5). 5. Código de **transacción** de alta de venta (con pruebas de éxito y fallo). 6. **Breve memoria** (2–3 páginas): decisiones de modelado, normalización, supuestos y cómo cumplen las reglas. ## 7) Criterios de evaluación - Corrección del modelo y **normalización** (20%). - Calidad de **restricciones** e integridad referencial (20%). - **Consultas**: corrección, uso de **joins** (incluidas outer joins donde apliquen) y agregaciones (25%). - **Transacción** implementada y correctamente controlada (15%). NO APLICA - Vistas y claridad de **entregables** (10%). NO APLICA - Calidad de los **datos de prueba** (10%).