Caso de Estudio: Sistema de Gestión "Camino a Santiago"
Entorno de Trabajo: Oracle Database 11g Express Edition (XE) & Oracle SQL Developer
Cátedra: Bases de Datos Avanzadas 2025 (Prof. Dra. Angélica Urrutia Sepúlveda)
En la evaluación se pide definir conceptualmente DDL y DML. La respuesta debe ser precisa y respaldada en la teoría de sistemas de bases de datos relacionales:
┌────────────────────────┐
│ LENGUAJE SQL │
└───────────┬────────────┘
┌────────────────────────┴────────────────────────┐
▼ ▼
┌───────────────────────┐ ┌───────────────────────┐
│ DDL │ │ DML │
│ Data Definition Lang. │ │ Data Manipulation L. │
├───────────────────────┤ ├───────────────────────┤
│ CREATE, ALTER, DROP, │ │ INSERT, UPDATE, │
│ TRUNCATE │ │ DELETE, SELECT │
├───────────────────────┤ ├───────────────────────┤
│ Modifica estructuras │ │ Modifica o lee datos │
│ (Metadatos/Tablas) │ │ dentro de las tablas │
├───────────────────────┤ ├───────────────────────┤
│ Auto-COMMIT implícito │ │ Requiere COMMIT o │
│ en Oracle (No rollback│ │ permite ROLLBACK │
└───────────────────────┘ └───────────────────────┘CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE.CREATE TABLE o DROP TABLE no se puede deshacer con ROLLBACK.INSERT INTO (crear tuplas), UPDATE (modificar valores existentes), DELETE FROM (eliminar tuplas) y SELECT (recuperar/consultar datos, clasificado a veces como DQL).COMMIT o las cancela con ROLLBACK.El problema modela la peregrinación del Camino a Santiago a través de 7 tablas interconectadas:
CAMINONombre. Cada camino se identifica de forma única por su nombre.Kilometros_T) y el tiempo total (Tiempo_T) deben ser estrictamente positivos (CHECK > 0).CIUDADNombre. Identifica unívocamente a cada localidad en este modelo.Comunidad_Aut (Comunidad Autónoma a la que pertenece) y Codigo_P (Código postal).ETAPA(Nombre_C, Numero).1, 2, 3...) se repite en distintos caminos. Para saber exactamente a qué etapa nos referimos, requerimos tanto el nombre del camino como el número de etapa.Nombre_C $\rightarrow$ CAMINO(Nombre): Vincula la etapa con su camino correspondiente.Ciudad_S $\rightarrow$ CIUDAD(Nombre): Localidad de salida de la etapa.Ciudad_LL $\rightarrow$ CIUDAD(Nombre): Localidad de llegada de la etapa.CIUDAD) es un patrón clásico de modelado relacional (origen y destino).RECORRIDO(Nombre_C, Numero, Ciudad). Una misma ciudad solo puede registrarse una vez en la misma etapa del mismo camino.(Nombre_C, Numero) $\rightarrow$ ETAPA(Nombre_C, Numero): Clave foránea compuesta hacia la etapa padre.Ciudad $\rightarrow$ CIUDAD(Nombre): Vincula con la localidad recorrida.ALBERGUE(Nombre_A, Ciudad). Permite que puedan existir albergues con el mismo nombre en distintas ciudades (por ejemplo, "Albergue Municipal" en Jaca y "Albergue Municipal" en Pamplona).Ciudad $\rightarrow$ CIUDAD(Nombre).Precio puede ser nulo o cero (según el enunciado: "si lo tuviera"), lo que modela albergues de donación voluntaria o públicos.PEREGRINONumero_I (identificador único: RUT, pasaporte o DNI).Nombre_Completo (obligatorio NOT NULL) y Direccion (opcional/nulo según la nota del enunciado Dirección*).CAMINO_PEREGRINO(Numero_I, Nombre_C, Fecha_Paso). Permite que un peregrino realice el mismo camino en distintas fechas o distintos caminos.Numero_I $\rightarrow$ PEREGRINO(Numero_I)Nombre_C $\rightarrow$ CAMINO(Nombre)Un error muy frecuente en exámenes es intentar colocar una sola columna como Primary Key cuando la realidad del negocio exige más de una:
ETAPA: Si pusieras solo Numero como PK, solo podría existir una etapa 1 en todo el sistema (¡no podrías tener la Etapa 1 del Camino Aragonés y la Etapa 1 del Camino Francés al mismo tiempo!). La combinación (Nombre_C, Numero) garantiza unicidad.RECORRIDO: Al ser una tabla hija de una clave compuesta, su clave foránea debe heredar ambos campos: FOREIGN KEY (Nombre_C, Numero) REFERENCES ETAPA(Nombre_C, Numero).| Relación | Cláusula elegida | Justificación Técnica |
|---|---|---|
ETAPA $\rightarrow$ CAMINO |
ON DELETE CASCADE |
Dependencia de existencia. Si se suprime un camino, sus etapas carecen de sentido y deben eliminarse automáticamente para evitar datos huérfanos. |
RECORRIDO $\rightarrow$ ETAPA |
ON DELETE CASCADE |
Las paradas intermedias dependen exclusivamente de la existencia de esa etapa. |
ETAPA $\rightarrow$ CIUDAD |
RESTRICT (Por defecto) | Protección de datos maestros. Si se intenta borrar la ciudad "Santiago", Oracle debe impedirlo para no corromper ni borrar accidentalmente todas las etapas asociadas. |
ALBERGUE $\rightarrow$ CIUDAD |
ON DELETE CASCADE |
El albergue reside físicamente en la ciudad; si la ciudad se elimina físicamente del catálogo, sus albergues desaparecen con ella. |
CAMINO_PEREGRINO $\rightarrow$ PEREGRINO |
ON DELETE CASCADE |
Si se elimina la ficha de un peregrino, su historial de pasos asociados debe borrarse. |
-- ========================================================================
-- SCRIPT DDL: SISTEMA CAMINO A SANTIAGO
-- Compatible con Oracle Database 11g XE / SQL Developer
-- ========================================================================
-- Borrado previo en orden inverso de dependencias
DROP TABLE CAMINO_PEREGRINO CASCADE CONSTRAINTS;
DROP TABLE RECORRIDO CASCADE CONSTRAINTS;
DROP TABLE ALBERGUE CASCADE CONSTRAINTS;
DROP TABLE ETAPA CASCADE CONSTRAINTS;
DROP TABLE PEREGRINO CASCADE CONSTRAINTS;
DROP TABLE CIUDAD CASCADE CONSTRAINTS;
DROP TABLE CAMINO CASCADE CONSTRAINTS;
-- 1. TABLA CAMINO
CREATE TABLE CAMINO (
Nombre VARCHAR2(50),
Kilometros_T NUMBER(6, 2) NOT NULL,
Tiempo_T NUMBER(4, 1) NOT NULL,
CONSTRAINT pk_camino PRIMARY KEY (Nombre),
CONSTRAINT chk_camino_km CHECK (Kilometros_T > 0),
CONSTRAINT chk_camino_tiempo CHECK (Tiempo_T > 0)
);
-- 2. TABLA CIUDAD
CREATE TABLE CIUDAD (
Nombre VARCHAR2(60),
Comunidad_Aut VARCHAR2(60) NOT NULL,
Codigo_P VARCHAR2(10) NOT NULL,
CONSTRAINT pk_ciudad PRIMARY KEY (Nombre)
);
-- 3. TABLA ETAPA
CREATE TABLE ETAPA (
Nombre_C VARCHAR2(50),
Numero NUMBER(3),
Kilometro_P NUMBER(5, 2) NOT NULL,
Tiempo_P NUMBER(4, 1) NOT NULL,
Ciudad_S VARCHAR2(60) NOT NULL,
Ciudad_LL VARCHAR2(60) NOT NULL,
CONSTRAINT pk_etapa PRIMARY KEY (Nombre_C, Numero),
CONSTRAINT fk_etapa_camino FOREIGN KEY (Nombre_C)
REFERENCES CAMINO(Nombre) ON DELETE CASCADE,
CONSTRAINT fk_etapa_ciudad_salida FOREIGN KEY (Ciudad_S)
REFERENCES CIUDAD(Nombre),
CONSTRAINT fk_etapa_ciudad_llegada FOREIGN KEY (Ciudad_LL)
REFERENCES CIUDAD(Nombre),
CONSTRAINT chk_etapa_numero CHECK (Numero > 0),
CONSTRAINT chk_etapa_km CHECK (Kilometro_P > 0),
CONSTRAINT chk_etapa_tiempo CHECK (Tiempo_P > 0)
);
-- 4. TABLA RECORRIDO
CREATE TABLE RECORRIDO (
Nombre_C VARCHAR2(50),
Numero NUMBER(3),
Ciudad VARCHAR2(60),
CONSTRAINT pk_recorrido PRIMARY KEY (Nombre_C, Numero, Ciudad),
CONSTRAINT fk_recorrido_etapa FOREIGN KEY (Nombre_C, Numero)
REFERENCES ETAPA(Nombre_C, Numero) ON DELETE CASCADE,
CONSTRAINT fk_recorrido_ciudad FOREIGN KEY (Ciudad)
REFERENCES CIUDAD(Nombre)
);
-- 5. TABLA ALBERGUE
CREATE TABLE ALBERGUE (
Nombre_A VARCHAR2(80),
Ciudad VARCHAR2(60),
Capacidad NUMBER(4) NOT NULL,
Precio NUMBER(6, 2), -- Nulo = Gratuito o Donativo
CONSTRAINT pk_albergue PRIMARY KEY (Nombre_A, Ciudad),
CONSTRAINT fk_albergue_ciudad FOREIGN KEY (Ciudad)
REFERENCES CIUDAD(Nombre) ON DELETE CASCADE,
CONSTRAINT chk_albergue_capacidad CHECK (Capacidad > 0),
CONSTRAINT chk_albergue_precio CHECK (Precio >= 0)
);
-- 6. TABLA PEREGRINO
CREATE TABLE PEREGRINO (
Numero_I VARCHAR2(12),
Nombre_Completo VARCHAR2(100) NOT NULL,
Direccion VARCHAR2(150),
CONSTRAINT pk_peregrino PRIMARY KEY (Numero_I)
);
-- 7. TABLA CAMINO_PEREGRINO
CREATE TABLE CAMINO_PEREGRINO (
Numero_I VARCHAR2(12),
Nombre_C VARCHAR2(50),
Fecha_Paso DATE,
CONSTRAINT pk_camino_peregrino PRIMARY KEY (Numero_I, Nombre_C, Fecha_Paso),
CONSTRAINT fk_camperegrino_peregrino FOREIGN KEY (Numero_I)
REFERENCES PEREGRINO(Numero_I) ON DELETE CASCADE,
CONSTRAINT fk_camperegrino_camino FOREIGN KEY (Nombre_C)
REFERENCES CAMINO(Nombre) ON DELETE CASCADE
);Para que las consultas solicitadas en la Pregunta 2 arrojen filas reales y podamos verificar el comportamiento con casos límite:
Camino Francés).Camino Aragonés).-- Inserción de Caminos
INSERT INTO CAMINO VALUES ('Camino Francés', 780.00, 30.0);
INSERT INTO CAMINO VALUES ('Camino Aragonés', 165.00, 6.0);
-- Inserción de Ciudades
INSERT INTO CIUDAD VALUES ('Somport', 'Aragón', '22880');
INSERT INTO CIUDAD VALUES ('Jaca', 'Aragón', '22700');
INSERT INTO CIUDAD VALUES ('Puente la Reina', 'Navarra', '31100');
INSERT INTO CIUDAD VALUES ('Santiago', 'Galicia', '15701');
INSERT INTO CIUDAD VALUES ('Finisterre', 'Galicia', '15155');
-- Inserción de Etapas
-- Etapa 1 del Camino Aragonés (de Somport a Jaca)
INSERT INTO ETAPA VALUES ('Camino Aragonés', 1, 32.00, 1.0, 'Somport', 'Jaca');
-- Etapa final del Camino Francés que llega a Santiago
INSERT INTO ETAPA VALUES ('Camino Francés', 1, 25.00, 1.0, 'Puente la Reina', 'Santiago');
-- Inserción de Recorrido intermedio
INSERT INTO RECORRIDO VALUES ('Camino Aragonés', 1, 'Somport');
INSERT INTO RECORRIDO VALUES ('Camino Aragonés', 1, 'Jaca');
-- Inserción de Albergues
INSERT INTO ALBERGUE VALUES ('Albergue Municipal de Jaca', 'Jaca', 40, 12.00);
INSERT INTO ALBERGUE VALUES ('Albergue Casa Mamré', 'Jaca', 20, 15.00);
INSERT INTO ALBERGUE VALUES ('Albergue Seminario Menor', 'Santiago', 150, 18.00);
-- Nota: Somport y Finisterre se dejan sin albergues a propósito.
-- Inserción de Peregrinos
INSERT INTO PEREGRINO VALUES ('12345678-9', 'Pedro Pascal', 'Calle Gran Vía 12');
INSERT INTO PEREGRINO VALUES ('98765432-1', 'Laura Gómez', 'Av. Providencia 400');
-- Registro de paso: Pedro Pascal realizó el Camino Francés
INSERT INTO CAMINO_PEREGRINO VALUES ('12345678-9', 'Camino Francés', TO_DATE('2025-05-15', 'YYYY-MM-DD'));
COMMIT;"Mostrar el Nombre de los peregrinos que han realizado el camino a Santiago (se cuenta solo aquellos que han llegado a la localidad de Santiago)."
PEREGRINO.CAMINO_PEREGRINO mediante p.Numero_I = cp.Numero_I.ETAPA mediante cp.Nombre_C = e.Nombre_C.WHERE e.Ciudad_LL = 'Santiago'.DISTINCT para evitar que el nombre se repita si el peregrino caminó varias etapas del mismo camino.SELECT DISTINCT p.Nombre_Completo
FROM PEREGRINO p
INNER JOIN CAMINO_PEREGRINO cp ON p.Numero_I = cp.Numero_I
INNER JOIN ETAPA e ON cp.Nombre_C = e.Nombre_C
WHERE e.Ciudad_LL = 'Santiago';"Muestre el nombre y la comunidad autónoma de aquellas localidades que no posean albergues para peregrinos (o posean N albergues). Presente esta consulta ordenada descendentemente."
Esta pregunta evalúa tu capacidad de encontrar registros en una tabla que no tienen correspondencia en otra.
LEFT JOIN con filtro de Nulos (Recomendada para "Sin Albergues")CIUDAD LEFT JOIN ALBERGUE, se conservan todas las ciudades.ALBERGUE vendrán como NULL.WHERE a.Nombre_A IS NULL.SELECT c.Nombre AS Localidad, c.Comunidad_Aut
FROM CIUDAD c
LEFT JOIN ALBERGUE a ON c.Nombre = a.Ciudad
WHERE a.Nombre_A IS NULL
ORDER BY c.Nombre DESC;GROUP BY y HAVING (Ideal si el enunciado pide "posean N albergues")HAVING COUNT(a.Nombre_A) = 0 (o = N).COUNT(a.Nombre_A) y NO COUNT(*). Si usas COUNT(*), las filas con NULL generadas por el LEFT JOIN serán contadas como 1 en lugar de 0.SELECT c.Nombre AS Localidad, c.Comunidad_Aut
FROM CIUDAD c
LEFT JOIN ALBERGUE a ON c.Nombre = a.Ciudad
GROUP BY c.Nombre, c.Comunidad_Aut
HAVING COUNT(a.Nombre_A) = 0
ORDER BY c.Nombre DESC;"Encuentre el número de albergues por localidad pertenecientes a la primera etapa del camino Aragonés."
RECORRIDO contiene las ciudades por las que pasa cada etapa de un camino.WHERE r.Nombre_C = 'Camino Aragonés' AND r.Numero = 1.ALBERGUE mediante un LEFT JOIN por la columna Ciudad. ¿Por qué LEFT JOIN? Porque si una de las ciudades de la etapa 1 (ej. Somport) no tiene albergues, queremos que aparezca en el resultado con cantidad 0, en lugar de desaparecer.GROUP BY r.Ciudad).SELECT r.Ciudad AS Localidad,
COUNT(a.Nombre_A) AS Cantidad_Albergues
FROM RECORRIDO r
LEFT JOIN ALBERGUE a ON r.Ciudad = a.Ciudad
WHERE r.Nombre_C = 'Camino Aragonés'
AND r.Numero = 1
GROUP BY r.Ciudad;"Muestre una consulta utilizando diferentes instrucciones (para 2 tablas que incluya subconsulta/select anidado o JOIN) y que lleguen al mismo resultado. Justifique en qué caso es mejor utilizar una o la otra."
Tomemos como requerimiento: "Obtener el nombre y comunidad de las ciudades que cuentan con albergues de peregrinos".
INNER JOIN con DISTINCTSELECT DISTINCT c.Nombre, c.Comunidad_Aut
FROM CIUDAD c
INNER JOIN ALBERGUE a ON c.Nombre = a.Ciudad;EXISTS (o IN)SELECT c.Nombre, c.Comunidad_Aut
FROM CIUDAD c
WHERE EXISTS (
SELECT 1
FROM ALBERGUE a
WHERE a.Ciudad = c.Nombre
);| Criterio de Comparación | INNER JOIN con DISTINCT |
Subconsulta con EXISTS / IN |
|---|---|---|
| Proyección de Columnas | Permite proyectar atributos de ambas tablas en el SELECT (ej. nombre de la ciudad y precio del albergue). |
Solo permite proyectar atributos de la tabla externa (CIUDAD). |
| Tratamiento de Duplicados | Si una ciudad tiene 100 albergues, el producto del join genera 100 filas intermedias que deben filtrarse con un costoso ordenamiento DISTINCT. |
No genera duplicados. Tan pronto el motor encuentra el primer albergue para la ciudad, evalúa TRUE y pasa a la siguiente (Short-circuit evaluation). |
| Rendimiento en Grandes Volúmenes | Menos eficiente si la tabla secundaria es masiva y solo se requiere verificar existencia. | Altamente eficiente, especialmente si existe un índice sobre la clave foránea ALBERGUE.Ciudad. |
| Manejo de Nulos (Exclusión) | LEFT JOIN ... WHERE IS NULL es seguro y determinista. |
En caso de exclusión con NOT IN, si la subconsulta retorna un solo valor NULL, toda la consulta devuelve cero registros. Por ello, en Oracle siempre se prefiere NOT EXISTS sobre NOT IN. |
ORA-02291: integrity constraint violated - parent key not found
ETAPA) un valor en la clave foránea (ej: Nombre_C = 'Camino Aragonés') que aún no ha sido insertado en la tabla padre (CAMINO).ORA-00979: not a GROUP BY expression
SELECT (por ejemplo c.Comunidad_Aut) que no tiene una función de agregación (COUNT, SUM) y te olvidaste de agregarla en la cláusula GROUP BY.SELECT que no esté dentro de una función de agregación debe figurar explícitamente en el GROUP BY.ORA-02292: integrity constraint violated - child record found
ON DELETE CASCADE.¡Éxito en tu evaluación! Con estos fundamentos dominas tanto el diseño DDL, la coherencia de restricciones, la manipulación DML y el análisis relacional de consultas complejas.