Apuntes DAM
Volver al inicio

Del modelo relacional al DDL: las tablas de un taller mecánico

Ejercicio de SQLDifícilUnos 75 minutos

Pasa a SQL el modelo relacional de un taller: tablas con claves primarias simples y compuestas, claves ajenas con ON DELETE CASCADE y SET NULL, NOT NULL, UNIQUE, DEFAULT y CHECK, una relación N:M, un ALTER TABLE, un índice único y una vista. Se corrige probando las restricciones.

  • CREATE TABLE
  • Claves primarias, ajenas y compuestas
  • NOT NULL, UNIQUE, DEFAULT y CHECK
  • ON DELETE CASCADE y SET NULL
  • ALTER TABLE e índices únicos
  • Vistas

Enunciado

Un taller mecánico tiene ya en su base de datos los clientes y sus vehículos (abajo, con sus datos) y te pasa el resto del modelo relacional para que escribas el DDL. En la notación de clase (PK = clave primaria, # = clave ajena): MECANICOS(mecanico_id PK, dni, nombre, especialidad, salario, alta); REPARACIONES(reparacion_id PK, #matricula, #mecanico_id, entrada, salida, estado, horas); PIEZAS(pieza_id PK, referencia, descripcion, precio, stock); REPARACION_PIEZAS(#reparacion_id PK, #pieza_id PK, cantidad, precio_unidad).

Una tabla bien definida no deja entrar datos malos: las restricciones (NOT NULL, UNIQUE, CHECK, las claves) son la última barrera, aunque la aplicación se equivoque. Por eso cada apartado se corrige así: después de tu script se intentan insertar filas buenas y malas con INSERT OR IGNORE (que se salta en silencio las que violan una restricción) y se compara qué filas quedan con las que quedarían con la solución. La consulta de comprobación de cada apartado es visible: úsala para saber qué se prueba.

Qué hay que hacer

  1. Cada apartado se ejecuta por separado sobre la base de datos inicial (clientes y vehiculos con los datos de abajo), así que no depende de los anteriores. Escribe los nombres de tablas y columnas exactamente como en el enunciado.
  2. La base de datos es SQLite: una clave primaria que se numera sola se declara INTEGER PRIMARY KEY (con INT no funciona igual), los tipos son orientativos y las claves ajenas no se comprueban al insertar salvo con PRAGMA foreign_keys = ON; aun así se comprueba que las declares bien (columna, tabla y acción ON DELETE).
  3. Los nombres de las restricciones dan igual; el del índice del apartado 4, no.

Las tablas y sus datos

Base de datos del ejercicio · 2 tablas

  • clientes
  • vehiculos

Subrayada, la clave primaria; con flecha, la columna a la que apunta cada clave ajena.

clientes
cliente_idnombreemailalta
1Marta Ruizmarta@correo.es2023-01-10
2Jorge Díazjorge@correo.es2023-05-02
3Lucía Pérezlucia@correo.es2024-02-20
4Jorge Díazjorge@correo.es2025-09-14
5Transportes Amigo SLNULL2025-11-03
vehiculos
matriculacliente_idmarcamodeloanio
1234ABC1SeatIbiza2015
5678DEF2RenaultKangoo2019
9012GHI3ToyotaYaris2021
3456JKL1FordFocus2010
7890MNO4CitroënC32022
Ver el script SQL que crea la base de datos
sql
1CREATE TABLE clientes (
2  cliente_id INTEGER PRIMARY KEY,
3  nombre TEXT NOT NULL,
4  email TEXT,
5  alta DATE NOT NULL
6);
7CREATE TABLE vehiculos (
8  matricula TEXT PRIMARY KEY,
9  cliente_id INTEGER NOT NULL REFERENCES clientes(cliente_id),
10  marca TEXT NOT NULL,
11  modelo TEXT NOT NULL,
12  anio INTEGER
13);
14
15INSERT INTO clientes (cliente_id, nombre, email, alta) VALUES
16  (1, 'Marta Ruiz', 'marta@correo.es', '2023-01-10'),
17  (2, 'Jorge Díaz', 'jorge@correo.es', '2023-05-02'),
18  (3, 'Lucía Pérez', 'lucia@correo.es', '2024-02-20'),
19  (4, 'Jorge Díaz', 'jorge@correo.es', '2025-09-14'),
20  (5, 'Transportes Amigo SL', NULL, '2025-11-03');
21
22INSERT INTO vehiculos (matricula, cliente_id, marca, modelo, anio) VALUES
23  ('1234ABC', 1, 'Seat', 'Ibiza', 2015),
24  ('5678DEF', 2, 'Renault', 'Kangoo', 2019),
25  ('9012GHI', 3, 'Toyota', 'Yaris', 2021),
26  ('3456JKL', 1, 'Ford', 'Focus', 2010),
27  ('7890MNO', 4, 'Citroën', 'C3', 2022);

Guía paso a paso

Intenta resolverlo por tu cuenta y abre un paso solo cuando te atasques: cada uno te acerca a la solución sin dártela entera.

1. Del modelo a las columnas

Cada atributo del modelo relacional es una columna con un tipo; lo marcado con PK es la clave primaria y lo marcado con # las claves ajenas. Antes de escribir, apunta para cada columna qué restricciones tiene según el enunciado.

2. Restricciones de columna y de tabla

Las de una sola columna van junto a ella (NOT NULL, UNIQUE, DEFAULT, CHECK, REFERENCES); las que afectan a varias columnas, al final (PRIMARY KEY (a, b), CHECK (salida >= entrada) también puede ir ahí).

sql
CREATE TABLE ejemplo (
  id INTEGER PRIMARY KEY,
  codigo TEXT NOT NULL UNIQUE,
  precio REAL NOT NULL CHECK (precio > 0),
  estado TEXT DEFAULT 'nuevo'
);
3. Lee la comprobación

Cada INSERT OR IGNORE de la comprobación es un caso de prueba: piensa cuáles deberían entrar y cuáles no con tu tabla, y ejecuta tu script seguido de la comprobación para verlo.

4. Modificar con datos dentro

Cambiar una tabla que ya tiene datos obliga a pensar en ellos: no se puede crear un índice único si hay duplicados, ni borrar un cliente que todavía tiene vehículos sin decidir qué pasa con ellos.

Resuélvelo aquí

Cada apartado se corrige por separado contra la base de datos de arriba: escribe la consulta y pulsa «Comprobar».

🗄️SQLApartado 1Difícil

Crea la tabla mecanicos: mecanico_id entero, clave primaria que se numera sola; dni texto obligatorio y que no se puede repetir; nombre texto obligatorio; especialidad texto que vale 'general' si no se indica; salario real obligatorio, nunca menor de 1.100; y alta fecha obligatoria.

🗄️SQLApartado 2Difícil

Crea la tabla reparaciones: reparacion_id entero, clave primaria que se numera sola; matricula texto obligatorio, clave ajena a vehiculos(matricula) (si se borra el vehículo, se borran sus reparaciones); mecanico_id entero, clave ajena a mecanicos(mecanico_id) que puede ser NULL (si se borra el mecánico, se pone a NULL); entrada fecha obligatoria; salida fecha que, si tiene valor, no puede ser anterior a la entrada; estado texto obligatorio que solo puede ser 'pendiente', 'en curso' o 'terminada' y es 'pendiente' si no se indica; y horas real, 0 si no se indica y nunca negativo.

🗄️SQLApartado 3Difícil

Crea piezas con sus columnas en este orden: pieza_id entero clave primaria, referencia texto obligatorio y único, descripcion texto obligatorio, precio real obligatorio mayor que 0 y stock entero, 0 por defecto y nunca negativo. Crea después la tabla de la relación N:M reparacion_piezas, también en este orden: reparacion_id y pieza_id (claves ajenas a reparaciones y a piezas, que juntas forman la clave primaria), cantidad entero obligatorio mayor que 0 y precio_unidad real obligatorio (el precio de la pieza el día de la reparación).

🗄️SQLApartado 4Difícil

Los clientes 2 y 4 son la misma persona dada de alta dos veces. Pasa los vehículos del cliente 4 al 2 y borra el cliente 4; después añade a clientes la columna telefono (texto) y crea el índice único ux_clientes_email para que no se pueda repetir un email.

🗄️SQLApartado 5Difícil

Crea la vista v_clientes con una fila por cliente y estas columnas, en este orden: cliente_id, nombre, vehiculos (cuántos vehículos tiene) y mas_antiguo (el año del más antiguo, o NULL si no tiene ninguno). Los clientes sin vehículos también salen.

Solución explicada

Ver las soluciones de todos los apartados

Apartado 1

sql
CREATE TABLE mecanicos (
  mecanico_id INTEGER PRIMARY KEY,
  dni TEXT NOT NULL UNIQUE,
  nombre TEXT NOT NULL,
  especialidad TEXT DEFAULT 'general',
  salario REAL NOT NULL CHECK (salario >= 1100),
  alta DATE NOT NULL
);

Apartado 2

sql
CREATE TABLE reparaciones (
  reparacion_id INTEGER PRIMARY KEY,
  matricula TEXT NOT NULL REFERENCES vehiculos(matricula) ON DELETE CASCADE,
  mecanico_id INTEGER REFERENCES mecanicos(mecanico_id) ON DELETE SET NULL,
  entrada DATE NOT NULL,
  salida DATE CHECK (salida IS NULL OR salida >= entrada),
  estado TEXT NOT NULL DEFAULT 'pendiente' CHECK (estado IN ('pendiente', 'en curso', 'terminada')),
  horas REAL DEFAULT 0 CHECK (horas >= 0)
);

Apartado 3

sql
CREATE TABLE piezas (
  pieza_id INTEGER PRIMARY KEY,
  referencia TEXT NOT NULL UNIQUE,
  descripcion TEXT NOT NULL,
  precio REAL NOT NULL CHECK (precio > 0),
  stock INTEGER DEFAULT 0 CHECK (stock >= 0)
);
CREATE TABLE reparacion_piezas (
  reparacion_id INTEGER REFERENCES reparaciones(reparacion_id),
  pieza_id INTEGER REFERENCES piezas(pieza_id),
  cantidad INTEGER NOT NULL CHECK (cantidad > 0),
  precio_unidad REAL NOT NULL,
  PRIMARY KEY (reparacion_id, pieza_id)
);

Apartado 4

sql
UPDATE vehiculos SET cliente_id = 2 WHERE cliente_id = 4;
DELETE FROM clientes WHERE cliente_id = 4;
ALTER TABLE clientes ADD COLUMN telefono TEXT;
CREATE UNIQUE INDEX ux_clientes_email ON clientes(email);

Apartado 5

sql
CREATE VIEW v_clientes AS
SELECT c.cliente_id, c.nombre, COUNT(v.matricula) AS vehiculos, MIN(v.anio) AS mas_antiguo
FROM clientes c
LEFT JOIN vehiculos v ON v.cliente_id = c.cliente_id
GROUP BY c.cliente_id, c.nombre;

Las restricciones son reglas de negocio que la base de datos garantiza siempre, venga el dato de donde venga: una aplicación web, un script de importación o alguien con un cliente SQL. Una regla que solo comprueba la aplicación acaba saltándose.

ON DELETE CASCADE y ON DELETE SET NULL responden a una pregunta de diseño: si desaparece el vehículo, sus reparaciones no tienen sentido (se borran); si se va un mecánico, sus reparaciones siguen existiendo (se quedan sin asignar).

La tabla de una relación N:M lleva la clave compuesta por las dos claves ajenas y los atributos de la propia relación (cantidad, precio_unidad), que no son ni de la reparación ni de la pieza.

En MySQL o PostgreSQL la sintaxis es casi la misma; cambian los tipos (VARCHAR(n), DECIMAL(8,2)), la autonumeración (AUTO_INCREMENT, SERIAL o GENERATED ... AS IDENTITY) y que las claves ajenas se comprueban siempre.

Para ir más allá

  • Añade un disparador que reste del stock de cada pieza la cantidad usada en una reparación.
  • Crea la vista v_facturas con el total de cada reparación: horas a 45 € más la suma de cantidad × precio_unidad.
  • Escribe el mismo DDL para MySQL y compara las diferencias.

Dónde se explica