Módulo 42: Proyecto final
Objetivos del módulo
- Recorrer de principio a fin el ciclo de vida de una base de datos real: requisitos, diseño conceptual, diseño lógico, normalización, implementación, carga, explotación, mantenimiento, seguridad y documentación
- Aplicar en un único caso todo lo aprendido: E/R extendido (jerarquía, entidad débil, relación reflexiva, N:M con atributos, atributo multivaluado), reglas de transformación, dependencias funcionales, DDL, DML, transacciones y DCL
- Justificar cada decisión de diseño, no solo tomarla
- Disponer de un modelo de proyecto que puedas replicar con otro dominio y de una rúbrica para evaluarlo
1. El encargo: VetRed, una red de clínicas veterinarias
1.1. Contexto
VetRed es una pequeña cadena de clínicas veterinarias con sedes en Valencia, Madrid y Sevilla. Hasta ahora cada clínica gestiona sus datos en hojas de cálculo: los propietarios se repiten con datos distintos en cada sede, nadie sabe qué mascotas tienen vacunas pendientes y la facturación se calcula a mano a final de mes. La dirección te encarga diseñar e implantar una base de datos única para toda la red.
Tras varias entrevistas con la directora técnica, la recepcionista de Valencia y el gerente, has recogido los requisitos siguientes.
1.2. Requisitos de datos
| Código | Requisito |
|---|---|
| RD1 | De cada clínica se guarda un identificador, su nombre (único), dirección, ciudad, teléfono y fecha de apertura |
| RD2 | De cada trabajador se guarda un identificador, NIF y email (ambos únicos), nombre, apellidos, fecha de alta y salario. Cada trabajador pertenece a una clínica, y toda clínica tiene al menos un trabajador |
| RD3 | Algunos trabajadores supervisan a otros. Un trabajador tiene como mucho un supervisor, que puede ser de otra clínica (la directora técnica supervisa a los responsables de cada sede). Nadie se supervisa a sí mismo |
| RD4 | Los trabajadores pueden ser veterinarios (con número de colegiado único y, opcionalmente, una especialidad), auxiliares (con su titulación) o personal de recepción. Nadie puede ser a la vez veterinario y auxiliar |
| RD5 | De cada propietario (cliente) se guarda un identificador, NIF (único), nombre, apellidos, email (opcional y único), ciudad y fecha de alta, y uno o varios teléfonos de distinto tipo (móvil, fijo, trabajo). Un cliente recién dado de alta puede no tener aún teléfono ni mascotas |
| RD6 | Cada mascota pertenece a un único propietario. Se guarda su nombre, especie (perro, gato, conejo, ave, reptil u otro), raza, sexo, fecha de nacimiento, peso y, si lo tiene, el número de microchip (15 dígitos, único). La edad se calcula a partir de la fecha de nacimiento |
| RD7 | Las mascotas acuden a consultas: cada una se realiza en una clínica, a una fecha y hora, la atiende un veterinario (no necesariamente de esa clínica: los especialistas rotan) y tiene un motivo, un diagnóstico, un importe y un estado (programada, realizada o cancelada). Una consulta realizada debe tener diagnóstico |
| RD8 | En una consulta se pueden prescribir varios medicamentos del catálogo (nombre único, principio activo, laboratorio, precio, si requiere receta), indicando para cada uno la dosis, la duración del tratamiento en días y las unidades |
| RD9 | Se registran las vacunaciones de cada mascota numeradas correlativamente dentro de la mascota (dosis 1, 2, 3…): vacuna, fecha, fecha recomendada de la siguiente dosis y veterinario que la administró. Si se da de baja una mascota, sus vacunaciones carecen de sentido |
1.3. Requisitos funcionales (consultas que la dirección quiere poder obtener)
| Código | Requisito |
|---|---|
| RF1 | Agenda de consultas programadas por clínica |
| RF2 | Historial clínico completo de una mascota (consultas, diagnósticos y tratamientos) |
| RF3 | Mascotas con vacunas vencidas para avisar a sus propietarios |
| RF4 | Facturación por clínica, mes y año, y gasto acumulado por cliente |
| RF5 | Actividad de cada veterinario y medicamentos más prescritos |
| RF6 | Organigrama de la red |
1.4. Requisitos de seguridad
| Código | Requisito |
|---|---|
| RS1 | Recepción da de alta clientes, mascotas y citas, pero no ve diagnósticos ni salarios |
| RS2 | Los veterinarios y auxiliares consultan todo el historial clínico y registran consultas, prescripciones y vacunaciones |
| RS3 | La gerencia consulta toda la información, incluida la facturación, y es la única que modifica salarios |
| RS4 | Cada trabajador tiene su propio usuario, con contraseña que caduca y bloqueo tras intentos fallidos |
2. Análisis y diseño conceptual (E/R extendido)
2.1. Identificación de elementos
Aplicando el método del módulo 14 (subrayar sustantivos y verbos del enunciado):
| Elemento | Tipo | Justificación |
|---|---|---|
| CLINICA, TRABAJADOR, PROPIETARIO, MASCOTA, MEDICAMENTO, CONSULTA | Entidades fuertes | Tienen identificador propio y existencia independiente |
| VETERINARIO, AUXILIAR | Subtipos de TRABAJADOR | Atributos específicos (colegiado, especialidad, titulación) y participan en relaciones propias |
| VACUNACION | Entidad débil | Se identifica por la mascota más un número de dosis que solo es único dentro de ella (RD9) |
| trabaja_en | Relación 1:N | Cada trabajador en una clínica (RD2) |
| supervisa | Relación reflexiva 1:N | Entre trabajadores (RD3) |
| posee | Relación 1:N | Propietario–mascota (RD6) |
| tiene, atiende, se_realiza | Relaciones 1:N | Consulta–mascota, consulta–veterinario, consulta–clínica (RD7) |
| prescribe | Relación N:M con atributos | Consulta–medicamento con dosis, duración y unidades (RD8) |
| recibe | Relación de identificación | Mascota–vacunación (débil) |
| administra | Relación 1:N | Veterinario–vacunación |
| telefono | Atributo multivaluado | Varios por propietario (RD5) |
| edad | Atributo derivado | Se calcula (RD6) |
📘 Concepto: ¿Por qué CONSULTA es una entidad y no una relación ternaria entre MASCOTA, VETERINARIO y CLINICA? Porque tiene identidad propia (una mascota puede ir dos veces al mismo veterinario en la misma clínica), tiene muchos atributos y, sobre todo, participa en otra relación (prescribe). En notación de Chen, una relación no puede relacionarse con otra entidad salvo mediante agregación; convertirla en entidad es la solución más clara.
2.2. Diagrama E/R: personal y clínicas
Recuerda la convención del curso: la pareja (mín, máx) escrita junto a una entidad indica con cuántas ocurrencias de esa entidad se relaciona una ocurrencia de la otra.
flowchart TB
classDef debil stroke-width:4px
classDef derivado stroke-dasharray: 5 5
CLINICA[CLINICA] ---|"(1,1)"| trabaja{trabaja_en}
trabaja ---|"(1,n)"| TRABAJADOR[TRABAJADOR]
TRABAJADOR ---|"(0,1)<br/>supervisor"| supervisa{supervisa}
supervisa ---|"(0,n)<br/>supervisado"| TRABAJADOR
CLINICA --- c_id([<u>clinica_id</u>])
CLINICA --- c_nom([nombre])
CLINICA --- c_dir([direccion])
CLINICA --- c_ciu([ciudad])
CLINICA --- c_tel([telefono])
CLINICA --- c_ape([fecha_apertura])
TRABAJADOR --- t_id([<u>trabajador_id</u>])
TRABAJADOR --- t_nif([nif])
TRABAJADOR --- t_nom([nombre])
TRABAJADOR --- t_ape([apellidos])
TRABAJADOR --- t_ema([email])
TRABAJADOR --- t_alta([fecha_alta])
TRABAJADOR --- t_sal([salario])
TRABAJADOR --- jer{{"es un<br/>(parcial, exclusiva)"}}
jer --- VETERINARIO[VETERINARIO]
jer --- AUXILIAR[AUXILIAR]
VETERINARIO --- v_col([num_colegiado])
VETERINARIO --- v_esp(["especialidad (0,1)"])
AUXILIAR --- a_tit([titulacion])
- La jerarquía es parcial (el personal de recepción no es de ningún subtipo) y exclusiva (nadie es a la vez veterinario y auxiliar).
supervisaes reflexiva: un trabajador tiene como mucho un supervisor (0,1) y un supervisor puede tener de 0 a n supervisados.nifyemailson claves alternativas (se indican en el texto y en el esquema relacional comoUK).
2.3. Diagrama E/R: clientes, mascotas y vacunaciones
flowchart TB
classDef debil stroke-width:4px
classDef derivado stroke-dasharray: 5 5
PROPIETARIO[PROPIETARIO] ---|"(1,1)"| posee{posee}
posee ---|"(0,n)"| MASCOTA[MASCOTA]
MASCOTA ---|"(1,1)"| recibe{recibe}:::debil
recibe ---|"(0,n)"| VACUNACION[VACUNACION]:::debil
VETERINARIO[VETERINARIO] ---|"(1,1)"| administra{administra}
administra ---|"(0,n)"| VACUNACION
PROPIETARIO --- p_id([<u>propietario_id</u>])
PROPIETARIO --- p_nif([nif])
PROPIETARIO --- p_nombre([nombre_completo])
p_nombre --- p_nom([nombre])
p_nombre --- p_ape([apellidos])
PROPIETARIO --- p_ema(["email (0,1)"])
PROPIETARIO --- p_ciu([ciudad])
PROPIETARIO --- p_alta([fecha_alta])
PROPIETARIO --- p_tel(((telefono)))
MASCOTA --- m_id([<u>mascota_id</u>])
MASCOTA --- m_nom([nombre])
MASCOTA --- m_esp([especie])
MASCOTA --- m_raza([raza])
MASCOTA --- m_sexo([sexo])
MASCOTA --- m_nac([fecha_nacimiento])
MASCOTA --- m_chip(["chip (0,1)"])
MASCOTA --- m_peso([peso_kg])
MASCOTA --- m_edad([edad]):::derivado
VACUNACION --- va_num([<u>num_dosis</u> parcial])
VACUNACION --- va_vac([vacuna])
VACUNACION --- va_fec([fecha])
VACUNACION --- va_prox(["fecha_proxima (0,1)"])
telefonoes multivaluado (óvalo doble) y además tiene un tipo; en el paso a tablas se convertirá en una tabla propia.nombre_completoes un atributo compuesto (nombre y apellidos).edades derivado (borde discontinuo): no se almacenará.- VACUNACION es débil con dependencia en identificación: su identificador es
num_dosis(subrayado, parcial) junto con el identificador de la mascota a la que pertenece, a través de la relación de identificaciónrecibe(rombo de borde grueso).
2.4. Diagrama E/R: actividad clínica
flowchart TB
MASCOTA[MASCOTA] ---|"(1,1)"| tiene{tiene}
tiene ---|"(0,n)"| CONSULTA[CONSULTA]
VETERINARIO[VETERINARIO] ---|"(1,1)"| atiende{atiende}
atiende ---|"(0,n)"| CONSULTA
CLINICA[CLINICA] ---|"(1,1)"| realiza{se_realiza}
realiza ---|"(0,n)"| CONSULTA
CONSULTA ---|"(0,n)"| prescribe{prescribe}
prescribe ---|"(0,n)"| MEDICAMENTO[MEDICAMENTO]
prescribe --- pr_dos([dosis])
prescribe --- pr_dur([duracion_dias])
prescribe --- pr_uni([unidades])
CONSULTA --- co_id([<u>consulta_id</u>])
CONSULTA --- co_fec([fecha_hora])
CONSULTA --- co_mot([motivo])
CONSULTA --- co_dia(["diagnostico (0,1)"])
CONSULTA --- co_imp([importe])
CONSULTA --- co_est([estado])
MEDICAMENTO --- me_id([<u>medicamento_id</u>])
MEDICAMENTO --- me_nom([nombre])
MEDICAMENTO --- me_pa([principio_activo])
MEDICAMENTO --- me_lab([laboratorio])
MEDICAMENTO --- me_pre([precio])
MEDICAMENTO --- me_rec([requiere_receta])
prescribees una relación N:M: una consulta prescribe de 0 a n medicamentos y un medicamento puede prescribirse en 0 a n consultas. Sus atributos (dosis, duración, unidades) no pertenecen ni a la consulta ni al medicamento, sino a la pareja.- Las relaciones
atiendeyadministraenlazan con el subtipo VETERINARIO, no con TRABAJADOR: así el diagrama ya expresa que solo un veterinario puede atender una consulta o poner una vacuna.
2.5. Restricciones semánticas
Hay reglas que el diagrama no puede expresar. Se documentan aparte (módulo 13) y se indica cómo se implementarán:
| Código | Restricción | Implementación |
|---|---|---|
| R1 | Un trabajador no puede supervisarse a sí mismo | CHECK (supervisor_id <> trabajador_id) |
| R2 | La jerarquía es exclusiva: un trabajador no puede estar en VETERINARIOS y en AUXILIARES | Discriminador tipo + clave ajena compuesta (trabajador_id, tipo) (sección 5.2) |
| R3 | Un trabajador de tipo VETERINARIO debe tener fila en VETERINARIOS (totalidad por tipo) | No es declarable: se garantiza en la aplicación o con un trigger (PL/SQL) |
| R4 | Una consulta realizada debe tener diagnóstico | CHECK (estado <> 'REALIZADA' OR diagnostico IS NOT NULL) |
| R5 | La fecha de la siguiente dosis es posterior a la de la vacunación | CHECK (fecha_proxima > fecha) |
| R6 | El chip, si existe, tiene 15 caracteres | CHECK (LENGTH(chip) = 15) |
| R7 | Los números de dosis de una mascota son correlativos (1, 2, 3…) | No declarable: lo controla la aplicación al insertar (MAX(num_dosis) + 1) |
| R8 | No puede haber dos consultas del mismo veterinario a la misma hora | Ampliación propuesta: UNIQUE (veterinario_id, fecha_hora) (sección 13) |
| R9 | Los ciclos de supervisión (A supervisa a B y B a A) no están permitidos | No declarable: trigger o comprobación con CONNECT BY ... NOCYCLE |
💡 Consejo: Las restricciones que exigen un trigger (R3, R7, R9) no se programan en este curso, que es solo de SQL; se estudian en el Curso de Oracle PL/SQL. Documentarlas igualmente es parte del diseño: un proyecto que las ignora está incompleto.
3. Diseño lógico: transformación al modelo relacional
3.1. Reglas aplicadas
| Elemento del E/R | Regla (módulo 17) | Resultado |
|---|---|---|
| Entidades fuertes | Una tabla por entidad; el identificador pasa a clave primaria | CLINICAS, TRABAJADORES, PROPIETARIOS, MASCOTAS, CONSULTAS, MEDICAMENTOS |
| Relaciones 1:N (trabaja_en, posee, tiene, atiende, se_realiza, administra) | Clave ajena en la tabla del lado N | clinica_id en TRABAJADORES, propietario_id en MASCOTAS, etc. |
| Reflexiva 1:N (supervisa) | Clave ajena hacia la propia tabla | supervisor_id → TRABAJADORES |
| N:M con atributos (prescribe) | Tabla nueva con las dos claves como PK compuesta y los atributos de la relación | PRESCRIPCIONES |
| Entidad débil (VACUNACION) | Tabla con PK = clave del propietario + clave parcial; FK con borrado en cascada | VACUNACIONES (mascota_id, num_dosis) |
| Atributo multivaluado (telefono) | Tabla propia con la clave de la entidad + el valor | TELEFONOS_PROPIETARIO |
| Atributo compuesto (nombre_completo) | Se descompone en sus componentes | nombre, apellidos |
| Atributo derivado (edad) | No se almacena; se calcula en las consultas | — |
| Jerarquía (parcial, exclusiva) | Tabla para el supertipo + una por subtipo con la misma PK | TRABAJADORES, VETERINARIOS, AUXILIARES |
3.2. ¿Por qué esa transformación de la jerarquía?
El módulo 17 presentaba tres opciones. Las evaluamos para este caso:
| Opción | Ventajas | Inconvenientes en VetRed | Decisión |
|---|---|---|---|
| Una sola tabla con todos los atributos y un discriminador | Sin joins | num_colegiado y titulacion serían columnas casi siempre nulas; no se podría declarar num_colegiado NOT NULL solo para veterinarios sin un CHECK complejo; las FK de CONSULTAS no podrían apuntar “solo a veterinarios” | Descartada |
| Solo tablas de subtipos | Sin nulos | Recepción no es un subtipo (cobertura parcial): sus datos no tendrían tabla; supervisa y trabaja_en habría que repetirlas | Descartada |
| Supertipo + subtipos | Las FK de CONSULTAS y VACUNACIONES apuntan a VETERINARIOS; los datos comunes no se repiten; admite cobertura parcial | Hace falta un join para los datos completos de un veterinario | Elegida |
3.3. Esquema relacional
CLINICAS (clinica_id, nombre, direccion, ciudad, telefono, fecha_apertura)
- UK: nombre
TRABAJADORES (trabajador_id, nif, nombre, apellidos, email, fecha_alta, salario, tipo, clinica_id, supervisor_id (N))
- UK: nif; UK: email; UK: (trabajador_id, tipo)
clinica_id → CLINICAS(clinica_id);supervisor_id → TRABAJADORES(trabajador_id)
VETERINARIOS (*trabajador_id*, tipo, num_colegiado, especialidad (N))
- UK: num_colegiado
(trabajador_id, tipo) → TRABAJADORES(trabajador_id, tipo), borrado en cascada;tipo = 'VETERINARIO'
AUXILIARES (*trabajador_id*, tipo, titulacion)
(trabajador_id, tipo) → TRABAJADORES(trabajador_id, tipo), borrado en cascada;tipo = 'AUXILIAR'
PROPIETARIOS (propietario_id, nif, nombre, apellidos, email (N), ciudad (N), fecha_alta)
- UK: nif; UK: email
TELEFONOS_PROPIETARIO (*propietario_id*, telefono, tipo)
propietario_id → PROPIETARIOS(propietario_id), borrado en cascada
MASCOTAS (mascota_id, propietario_id, nombre, especie, raza (N), sexo, fecha_nacimiento (N), chip (N), peso_kg (N))
- UK: chip
propietario_id → PROPIETARIOS(propietario_id)
CONSULTAS (consulta_id, mascota_id, veterinario_id, clinica_id, fecha_hora, motivo, diagnostico (N), importe, estado)
mascota_id → MASCOTAS(mascota_id);veterinario_id → VETERINARIOS(trabajador_id);clinica_id → CLINICAS(clinica_id)
MEDICAMENTOS (medicamento_id, nombre, principio_activo, laboratorio (N), precio, requiere_receta)
- UK: nombre
PRESCRIPCIONES (*consulta_id*, *medicamento_id*, dosis, duracion_dias, unidades)
consulta_id → CONSULTAS(consulta_id), borrado en cascada;medicamento_id → MEDICAMENTOS(medicamento_id)
VACUNACIONES (*mascota_id*, num_dosis, vacuna, fecha, fecha_proxima (N), veterinario_id)
mascota_id → MASCOTAS(mascota_id), borrado en cascada;veterinario_id → VETERINARIOS(trabajador_id)
Grafo relacional (cada flecha es una clave ajena):
flowchart LR
TRABAJADORES --> CLINICAS
TRABAJADORES -->|supervisor_id| TRABAJADORES
VETERINARIOS --> TRABAJADORES
AUXILIARES --> TRABAJADORES
TELEFONOS_PROPIETARIO --> PROPIETARIOS
MASCOTAS --> PROPIETARIOS
CONSULTAS --> MASCOTAS
CONSULTAS --> VETERINARIOS
CONSULTAS --> CLINICAS
PRESCRIPCIONES --> CONSULTAS
PRESCRIPCIONES --> MEDICAMENTOS
VACUNACIONES --> MASCOTAS
VACUNACIONES --> VETERINARIOS
3.4. Decisiones de diseño físico
| Decisión | Motivo |
|---|---|
mascota_id y consulta_id como identidad BY DEFAULT ON NULL | Claves subrogadas generadas por la base de datos; BY DEFAULT permite cargas iniciales con valores fijos |
propietario_id con la secuencia seq_propietarios | Ilustra el uso de secuencias; en la aplicación permite conocer el identificador (CURRVAL) para insertar a continuación los teléfonos |
| Índices en todas las claves ajenas | Rendimiento de los joins y evitar bloqueos de tabla hija (módulo 35) |
Índice compuesto (clinica_id, fecha_hora) en CONSULTAS | La agenda y la facturación filtran por clínica y fechas |
Índice basado en función UPPER(apellidos) | Búsqueda de clientes sin distinguir mayúsculas |
ON DELETE CASCADE solo en tablas dependientes (teléfonos, prescripciones, vacunaciones, subtipos) | Son dependientes en existencia; el resto protege los datos con NO ACTION |
| Precio del medicamento no copiado en PRESCRIPCIONES | Simplificación consciente: la facturación usa el precio actual del catálogo (ver ampliación en la sección 13) |
4. Verificación de la normalización
4.1. Dependencias funcionales
Para cada tabla escribimos las dependencias funcionales no triviales que se deducen de los requisitos (notación del módulo 19):
| Tabla | Dependencias funcionales | Claves candidatas |
|---|---|---|
| CLINICAS | clinica_id → nombre, direccion, ciudad, telefono, fecha_apertura; nombre → clinica_id | {clinica_id}, {nombre} |
| TRABAJADORES | trabajador_id → todos; nif → trabajador_id; email → trabajador_id | {trabajador_id}, {nif}, {email} |
| VETERINARIOS | trabajador_id → tipo, num_colegiado, especialidad; num_colegiado → trabajador_id | {trabajador_id}, {num_colegiado} |
| AUXILIARES | trabajador_id → tipo, titulacion | {trabajador_id} |
| PROPIETARIOS | propietario_id → todos; nif → propietario_id; email → propietario_id | {propietario_id}, {nif}, {email} |
| TELEFONOS_PROPIETARIO | (propietario_id, telefono) → tipo | {propietario_id, telefono} |
| MASCOTAS | mascota_id → todos; chip → mascota_id | {mascota_id}, {chip} |
| CONSULTAS | consulta_id → mascota_id, veterinario_id, clinica_id, fecha_hora, motivo, diagnostico, importe, estado | {consulta_id} |
| MEDICAMENTOS | medicamento_id → todos; nombre → medicamento_id | {medicamento_id}, {nombre} |
| PRESCRIPCIONES | (consulta_id, medicamento_id) → dosis, duracion_dias, unidades | {consulta_id, medicamento_id} |
| VACUNACIONES | (mascota_id, num_dosis) → vacuna, fecha, fecha_proxima, veterinario_id | {mascota_id, num_dosis} |
(email y chip admiten nulos: como claves alternativas solo son candidatas entre las filas que los tienen.)
4.2. Comprobación forma a forma
- 1FN. Todos los atributos son atómicos. El atributo multivaluado
telefonose llevó a su propia tabla; el compuestonombre_completose descompuso. Si hubiéramos guardado “600111111, 963111111” en una columna, PROPIETARIOS violaría la 1FN. - 2FN. Solo tres tablas tienen clave compuesta. En PRESCRIPCIONES la dosis depende de la consulta y del medicamento (la misma consulta da dosis distintas para cada medicamento, y el mismo medicamento se prescribe con dosis distintas en cada consulta): no hay dependencias parciales. En VACUNACIONES,
vacunayfechadependen de la dosis concreta de la mascota, no solo de la mascota. En TELEFONOS_PROPIETARIO,tipodepende del teléfono concreto del propietario. - 3FN. Buscamos dependencias transitivas entre atributos no clave:
- En CONSULTAS podría parecer que
veterinario_id → clinica_id(el veterinario trabaja en una clínica), lo que sería una transitividad. Pero el requisito RD7 dice que los especialistas rotan: la clínica de la consulta no se deduce del veterinario. La dependencia no existe yclinica_iddebe quedarse en CONSULTAS. (Si no rotaran, habría que quitarla y obtenerla con un join.) - En MASCOTAS no guardamos el nombre ni la ciudad del propietario:
propietario_id → ciudadsería transitiva. - En TRABAJADORES,
tipodepende solo de la clave.
- En CONSULTAS podría parecer que
- FNBC. En todas las tablas, todo determinante es clave candidata:
nif,email,nombre(de clínica o medicamento),num_colegiadoychipson claves alternativas, no atributos que determinen a otros sin serlo. - 4FN. TELEFONOS_PROPIETARIO podría tentar a incluir también los emails de cada propietario (si fueran varios); mezclar dos multivaluados independientes en una tabla generaría una dependencia multivaluada no trivial (
propietario_id ↠ telefono,propietario_id ↠ email). Al tener un solo email por propietario no se da el caso.
⚠️ Importante: La UK compuesta
(trabajador_id, tipo)de TRABAJADORES no rompe ninguna forma normal: es una superclave (contiene la clave primaria) que se declara solo para que las claves ajenas de los subtipos puedan referenciarla.
4.3. Un diseño alternativo (incorrecto) para comparar
Una primera versión ingenua tenía esta tabla:
CONSULTAS_V0 (consulta_id, mascota, especie, propietario, telefono_propietario, veterinario, colegiado, medicamentos, importe)
- Viola la 1FN:
medicamentoscontendría “Amoxivet, Otoclean”. - Tras pasarla a 1FN con una fila por medicamento y clave (consulta_id, medicamento), violaría la 2FN:
importeymascotadependen solo deconsulta_id. - Viola la 3FN:
mascota → especie, propietario,veterinario → colegiado. - Anomalías: cambiar el teléfono de un propietario obligaría a modificar todas sus consultas (modificación); no se podría dar de alta un medicamento que aún no se ha prescrito (inserción); borrar la única consulta de una mascota borraría la información de la mascota (borrado).
La descomposición en las tablas de la sección 3.3 elimina todas estas anomalías y es sin pérdida (cada descomposición se hace sobre una clave) y conserva las dependencias.
5. Implementación: script DDL
5.1. El script completo
El script crea, en este orden, las tablas (de padres a hijos), la secuencia, los índices, las vistas y los comentarios. Todas las restricciones tienen nombre con los prefijos del curso (pk_, uq_, fk_, ck_, nn_) y una abreviatura de tabla de tres letras (cln, tra, vet, aux, prp, tel, mas, con, med, psc, vac) para que los nombres no choquen con los de EMPRESA si conviven en el mismo esquema.
Guárdalo como vetred_crear.sql:
-- =====================================================================
-- PROYECTO FINAL VETRED - Red de clínicas veterinarias
-- Script DDL (Oracle 19c o superior)
-- =====================================================================
-- ---------------------------------------------------------------------
-- CLINICAS
-- ---------------------------------------------------------------------
CREATE TABLE clinicas (
clinica_id NUMBER(3) CONSTRAINT pk_clinicas PRIMARY KEY,
nombre VARCHAR2(40) CONSTRAINT nn_cln_nombre NOT NULL,
direccion VARCHAR2(80) CONSTRAINT nn_cln_direccion NOT NULL,
ciudad VARCHAR2(30) CONSTRAINT nn_cln_ciudad NOT NULL,
telefono VARCHAR2(15) CONSTRAINT nn_cln_telefono NOT NULL,
fecha_apertura DATE CONSTRAINT nn_cln_apertura NOT NULL,
CONSTRAINT uq_cln_nombre UNIQUE (nombre)
);
-- ---------------------------------------------------------------------
-- TRABAJADORES (supertipo de la jerarquía; reflexiva "supervisa")
-- ---------------------------------------------------------------------
CREATE TABLE trabajadores (
trabajador_id NUMBER(4) CONSTRAINT pk_trabajadores PRIMARY KEY,
nif VARCHAR2(9) CONSTRAINT nn_tra_nif NOT NULL,
nombre VARCHAR2(30) CONSTRAINT nn_tra_nombre NOT NULL,
apellidos VARCHAR2(50) CONSTRAINT nn_tra_apellidos NOT NULL,
email VARCHAR2(60) CONSTRAINT nn_tra_email NOT NULL,
fecha_alta DATE CONSTRAINT nn_tra_alta NOT NULL,
salario NUMBER(7,2) CONSTRAINT nn_tra_salario NOT NULL,
tipo VARCHAR2(11) CONSTRAINT nn_tra_tipo NOT NULL,
clinica_id NUMBER(3) CONSTRAINT nn_tra_clinica NOT NULL,
supervisor_id NUMBER(4),
CONSTRAINT uq_tra_nif UNIQUE (nif),
CONSTRAINT uq_tra_email UNIQUE (email),
CONSTRAINT uq_tra_id_tipo UNIQUE (trabajador_id, tipo),
CONSTRAINT ck_tra_salario CHECK (salario > 0),
CONSTRAINT ck_tra_tipo CHECK (tipo IN ('VETERINARIO', 'AUXILIAR', 'RECEPCION')),
CONSTRAINT ck_tra_supervisor CHECK (supervisor_id <> trabajador_id),
CONSTRAINT fk_tra_clinica FOREIGN KEY (clinica_id) REFERENCES clinicas (clinica_id),
CONSTRAINT fk_tra_supervisor FOREIGN KEY (supervisor_id) REFERENCES trabajadores (trabajador_id)
);
-- ---------------------------------------------------------------------
-- Subtipos (jerarquía total y exclusiva respecto a VETERINARIO/AUXILIAR;
-- la columna tipo fija + FK compuesta garantiza la exclusividad)
-- ---------------------------------------------------------------------
CREATE TABLE veterinarios (
trabajador_id NUMBER(4) CONSTRAINT pk_veterinarios PRIMARY KEY,
tipo VARCHAR2(11) DEFAULT 'VETERINARIO' CONSTRAINT nn_vet_tipo NOT NULL,
num_colegiado VARCHAR2(10) CONSTRAINT nn_vet_colegiado NOT NULL,
especialidad VARCHAR2(30),
CONSTRAINT uq_vet_colegiado UNIQUE (num_colegiado),
CONSTRAINT ck_vet_tipo CHECK (tipo = 'VETERINARIO'),
CONSTRAINT fk_vet_trabajador FOREIGN KEY (trabajador_id, tipo)
REFERENCES trabajadores (trabajador_id, tipo) ON DELETE CASCADE
);
CREATE TABLE auxiliares (
trabajador_id NUMBER(4) CONSTRAINT pk_auxiliares PRIMARY KEY,
tipo VARCHAR2(11) DEFAULT 'AUXILIAR' CONSTRAINT nn_aux_tipo NOT NULL,
titulacion VARCHAR2(60) CONSTRAINT nn_aux_titulacion NOT NULL,
CONSTRAINT ck_aux_tipo CHECK (tipo = 'AUXILIAR'),
CONSTRAINT fk_aux_trabajador FOREIGN KEY (trabajador_id, tipo)
REFERENCES trabajadores (trabajador_id, tipo) ON DELETE CASCADE
);
-- ---------------------------------------------------------------------
-- PROPIETARIOS y su atributo multivaluado TELEFONOS
-- ---------------------------------------------------------------------
CREATE SEQUENCE seq_propietarios START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE TABLE propietarios (
propietario_id NUMBER(6) CONSTRAINT pk_propietarios PRIMARY KEY,
nif VARCHAR2(9) CONSTRAINT nn_prp_nif NOT NULL,
nombre VARCHAR2(30) CONSTRAINT nn_prp_nombre NOT NULL,
apellidos VARCHAR2(50) CONSTRAINT nn_prp_apellidos NOT NULL,
email VARCHAR2(60),
ciudad VARCHAR2(30),
fecha_alta DATE DEFAULT SYSDATE CONSTRAINT nn_prp_alta NOT NULL,
CONSTRAINT uq_prp_nif UNIQUE (nif),
CONSTRAINT uq_prp_email UNIQUE (email)
);
CREATE TABLE telefonos_propietario (
propietario_id NUMBER(6),
telefono VARCHAR2(15),
tipo VARCHAR2(7) DEFAULT 'MOVIL' CONSTRAINT nn_tel_tipo NOT NULL,
CONSTRAINT pk_telefonos_propietario PRIMARY KEY (propietario_id, telefono),
CONSTRAINT ck_tel_tipo CHECK (tipo IN ('MOVIL', 'FIJO', 'TRABAJO')),
CONSTRAINT fk_tel_propietario FOREIGN KEY (propietario_id)
REFERENCES propietarios (propietario_id) ON DELETE CASCADE
);
-- ---------------------------------------------------------------------
-- MASCOTAS
-- ---------------------------------------------------------------------
CREATE TABLE mascotas (
mascota_id NUMBER(6) GENERATED BY DEFAULT ON NULL AS IDENTITY CONSTRAINT pk_mascotas PRIMARY KEY,
propietario_id NUMBER(6) CONSTRAINT nn_mas_propietario NOT NULL,
nombre VARCHAR2(30) CONSTRAINT nn_mas_nombre NOT NULL,
especie VARCHAR2(10) CONSTRAINT nn_mas_especie NOT NULL,
raza VARCHAR2(40),
sexo CHAR(1) CONSTRAINT nn_mas_sexo NOT NULL,
fecha_nacimiento DATE,
chip VARCHAR2(15),
peso_kg NUMBER(5,2),
CONSTRAINT uq_mas_chip UNIQUE (chip),
CONSTRAINT ck_mas_especie CHECK (especie IN ('PERRO', 'GATO', 'CONEJO', 'AVE', 'REPTIL', 'OTRO')),
CONSTRAINT ck_mas_sexo CHECK (sexo IN ('M', 'H')),
CONSTRAINT ck_mas_chip CHECK (LENGTH(chip) = 15),
CONSTRAINT ck_mas_peso CHECK (peso_kg > 0),
CONSTRAINT fk_mas_propietario FOREIGN KEY (propietario_id) REFERENCES propietarios (propietario_id)
);
-- ---------------------------------------------------------------------
-- CONSULTAS (mascota atendida por un veterinario en una clínica)
-- ---------------------------------------------------------------------
CREATE TABLE consultas (
consulta_id NUMBER(8) GENERATED BY DEFAULT ON NULL AS IDENTITY CONSTRAINT pk_consultas PRIMARY KEY,
mascota_id NUMBER(6) CONSTRAINT nn_con_mascota NOT NULL,
veterinario_id NUMBER(4) CONSTRAINT nn_con_veterinario NOT NULL,
clinica_id NUMBER(3) CONSTRAINT nn_con_clinica NOT NULL,
fecha_hora DATE CONSTRAINT nn_con_fecha NOT NULL,
motivo VARCHAR2(100) CONSTRAINT nn_con_motivo NOT NULL,
diagnostico VARCHAR2(200),
importe NUMBER(7,2) DEFAULT 0 CONSTRAINT nn_con_importe NOT NULL,
estado VARCHAR2(10) DEFAULT 'PROGRAMADA' CONSTRAINT nn_con_estado NOT NULL,
CONSTRAINT ck_con_importe CHECK (importe >= 0),
CONSTRAINT ck_con_estado CHECK (estado IN ('PROGRAMADA', 'REALIZADA', 'CANCELADA')),
CONSTRAINT ck_con_diagnostico CHECK (estado <> 'REALIZADA' OR diagnostico IS NOT NULL),
CONSTRAINT fk_con_mascota FOREIGN KEY (mascota_id) REFERENCES mascotas (mascota_id),
CONSTRAINT fk_con_veterinario FOREIGN KEY (veterinario_id) REFERENCES veterinarios (trabajador_id),
CONSTRAINT fk_con_clinica FOREIGN KEY (clinica_id) REFERENCES clinicas (clinica_id)
);
-- ---------------------------------------------------------------------
-- MEDICAMENTOS y PRESCRIPCIONES (N:M entre CONSULTAS y MEDICAMENTOS)
-- ---------------------------------------------------------------------
CREATE TABLE medicamentos (
medicamento_id NUMBER(5) CONSTRAINT pk_medicamentos PRIMARY KEY,
nombre VARCHAR2(40) CONSTRAINT nn_med_nombre NOT NULL,
principio_activo VARCHAR2(40) CONSTRAINT nn_med_principio NOT NULL,
laboratorio VARCHAR2(40),
precio NUMBER(7,2) CONSTRAINT nn_med_precio NOT NULL,
requiere_receta CHAR(1) DEFAULT 'S' CONSTRAINT nn_med_receta NOT NULL,
CONSTRAINT uq_med_nombre UNIQUE (nombre),
CONSTRAINT ck_med_precio CHECK (precio >= 0),
CONSTRAINT ck_med_receta CHECK (requiere_receta IN ('S', 'N'))
);
CREATE TABLE prescripciones (
consulta_id NUMBER(8),
medicamento_id NUMBER(5),
dosis VARCHAR2(40) CONSTRAINT nn_psc_dosis NOT NULL,
duracion_dias NUMBER(3) CONSTRAINT nn_psc_duracion NOT NULL,
unidades NUMBER(3) DEFAULT 1 CONSTRAINT nn_psc_unidades NOT NULL,
CONSTRAINT pk_prescripciones PRIMARY KEY (consulta_id, medicamento_id),
CONSTRAINT ck_psc_duracion CHECK (duracion_dias BETWEEN 1 AND 365),
CONSTRAINT ck_psc_unidades CHECK (unidades > 0),
CONSTRAINT fk_psc_consulta FOREIGN KEY (consulta_id) REFERENCES consultas (consulta_id) ON DELETE CASCADE,
CONSTRAINT fk_psc_medicamento FOREIGN KEY (medicamento_id) REFERENCES medicamentos (medicamento_id)
);
-- ---------------------------------------------------------------------
-- VACUNACIONES (entidad débil de MASCOTAS: identificador parcial num_dosis)
-- ---------------------------------------------------------------------
CREATE TABLE vacunaciones (
mascota_id NUMBER(6),
num_dosis NUMBER(3),
vacuna VARCHAR2(40) CONSTRAINT nn_vac_vacuna NOT NULL,
fecha DATE CONSTRAINT nn_vac_fecha NOT NULL,
fecha_proxima DATE,
veterinario_id NUMBER(4) CONSTRAINT nn_vac_veterinario NOT NULL,
CONSTRAINT pk_vacunaciones PRIMARY KEY (mascota_id, num_dosis),
CONSTRAINT ck_vac_proxima CHECK (fecha_proxima > fecha),
CONSTRAINT fk_vac_mascota FOREIGN KEY (mascota_id) REFERENCES mascotas (mascota_id) ON DELETE CASCADE,
CONSTRAINT fk_vac_veterinario FOREIGN KEY (veterinario_id) REFERENCES veterinarios (trabajador_id)
);
-- ---------------------------------------------------------------------
-- Índices sobre claves ajenas y columnas de búsqueda frecuente
-- ---------------------------------------------------------------------
CREATE INDEX ix_tra_clinica ON trabajadores (clinica_id);
CREATE INDEX ix_tra_supervisor ON trabajadores (supervisor_id);
CREATE INDEX ix_mas_propietario ON mascotas (propietario_id);
CREATE INDEX ix_con_mascota ON consultas (mascota_id);
CREATE INDEX ix_con_veterinario ON consultas (veterinario_id);
CREATE INDEX ix_con_clinica_fec ON consultas (clinica_id, fecha_hora);
CREATE INDEX ix_psc_medicamento ON prescripciones (medicamento_id);
CREATE INDEX ix_vac_veterinario ON vacunaciones (veterinario_id);
CREATE INDEX ix_vac_proxima ON vacunaciones (fecha_proxima);
CREATE INDEX ix_prp_apellidos ON propietarios (UPPER(apellidos));
-- ---------------------------------------------------------------------
-- Vistas
-- ---------------------------------------------------------------------
CREATE OR REPLACE VIEW v_agenda AS
SELECT c.consulta_id, cl.nombre AS clinica, c.fecha_hora,
m.nombre AS mascota, m.especie,
p.nombre || ' ' || p.apellidos AS propietario,
t.nombre || ' ' || t.apellidos AS veterinario,
c.motivo
FROM consultas c
JOIN clinicas cl ON cl.clinica_id = c.clinica_id
JOIN mascotas m ON m.mascota_id = c.mascota_id
JOIN propietarios p ON p.propietario_id = m.propietario_id
JOIN trabajadores t ON t.trabajador_id = c.veterinario_id
WHERE c.estado = 'PROGRAMADA';
CREATE OR REPLACE VIEW v_facturacion_mensual AS
SELECT c.clinica_id,
TRUNC(c.fecha_hora, 'MM') AS mes,
COUNT(*) AS consultas,
SUM(c.importe) AS importe_consultas
FROM consultas c
WHERE c.estado = 'REALIZADA'
GROUP BY c.clinica_id, TRUNC(c.fecha_hora, 'MM');
-- ---------------------------------------------------------------------
-- Comentarios (diccionario de datos)
-- ---------------------------------------------------------------------
COMMENT ON TABLE clinicas IS 'Clínicas de la red VetRed';
COMMENT ON TABLE trabajadores IS 'Personal de las clínicas (supertipo de VETERINARIOS y AUXILIARES)';
COMMENT ON TABLE veterinarios IS 'Subtipo de TRABAJADORES: veterinarios colegiados';
COMMENT ON TABLE auxiliares IS 'Subtipo de TRABAJADORES: auxiliares técnicos veterinarios';
COMMENT ON TABLE propietarios IS 'Clientes propietarios de mascotas';
COMMENT ON TABLE telefonos_propietario IS 'Atributo multivaluado teléfono de PROPIETARIOS';
COMMENT ON TABLE mascotas IS 'Animales atendidos en la red';
COMMENT ON TABLE consultas IS 'Consultas veterinarias programadas, realizadas o canceladas';
COMMENT ON TABLE medicamentos IS 'Catálogo de medicamentos';
COMMENT ON TABLE prescripciones IS 'Medicamentos prescritos en cada consulta (N:M con atributos)';
COMMENT ON TABLE vacunaciones IS 'Dosis de vacunas de cada mascota (entidad débil de MASCOTAS)';
COMMENT ON COLUMN trabajadores.tipo IS 'Discriminador de la jerarquía: VETERINARIO, AUXILIAR o RECEPCION';
COMMENT ON COLUMN trabajadores.supervisor_id IS 'Trabajador que supervisa a este (relación reflexiva)';
COMMENT ON COLUMN mascotas.chip IS 'Microchip de identificación (15 dígitos), opcional y único';
COMMENT ON COLUMN consultas.importe IS 'Importe de la consulta sin medicamentos, en euros';
COMMENT ON COLUMN vacunaciones.num_dosis IS 'Número de dosis dentro de la mascota (identificador parcial)';
COMMENT ON COLUMN vacunaciones.fecha_proxima IS 'Fecha recomendada para la siguiente dosis';
5.2. Cómo se garantiza la exclusividad de la jerarquía
El truco está en tres piezas que trabajan juntas:
- TRABAJADORES tiene la columna discriminadora
tipoy una restricciónUNIQUE (trabajador_id, tipo), que convierte la pareja en referenciable. - VETERINARIOS tiene su propia columna
tipo, conDEFAULT 'VETERINARIO'yCHECK (tipo = 'VETERINARIO'). AUXILIARES, lo mismo con'AUXILIAR'. - La clave ajena de cada subtipo es compuesta:
(trabajador_id, tipo) → TRABAJADORES(trabajador_id, tipo).
Así, un trabajador de tipo AUXILIAR no puede tener fila en VETERINARIOS (no existe la pareja (id, 'VETERINARIO')), y no se le puede cambiar el tipo mientras tenga fila en AUXILIARES (la pareja antigua tiene hijos). Lo comprobarás en la sección 8. Es una técnica puramente declarativa, sin triggers.
5.3. Ejecución
Se ejecutó con el usuario del curso en Oracle AI Database 26ai Free (@vetred_crear.sql). La salida, en orden, fue:
Tabla creada.
Tabla creada.
Tabla creada.
Tabla creada.
Secuencia creada.
Tabla creada.
Tabla creada.
...
Índice creado.
...
Vista creada.
Vista creada.
Comentario creado.
...
En total: 11 Tabla creada., 1 Secuencia creada., 10 Índice creado., 2 Vista creada. y 17 Comentario creado., sin ningún error. Para volver a empezar, ejecuta el bloque de borrado de la sección 12.
⚠️ Importante: Las restricciones
ON DELETE CASCADEde VETERINARIOS y AUXILIARES apuntan a la UK compuesta de TRABAJADORES. Si borras un trabajador, desaparece también su fila de subtipo; pero si tiene consultas o vacunaciones, el borrado lo impide la FKNO ACTIONde CONSULTAS (los historiales clínicos no deben perderse).
6. Carga de datos
6.1. Script de carga
Los datos son ficticios pero realistas y contienen los casos límite necesarios para probar las consultas: un propietario sin mascotas ni teléfono (Raúl), mascotas sin chip, un veterinario sin especialidad, una consulta cancelada, tres consultas programadas, un medicamento nunca prescrito y vacunas vencidas. Guárdalo como vetred_datos.sql:
-- =====================================================================
-- PROYECTO FINAL VETRED - Carga de datos
-- =====================================================================
-- CLINICAS
INSERT INTO clinicas VALUES (1, 'VetRed Valencia Centro', 'Calle Colón 12', 'Valencia', '963000001', DATE '2018-03-01');
INSERT INTO clinicas VALUES (2, 'VetRed Madrid Chamberí', 'Calle Fuencarral 120', 'Madrid', '913000002', DATE '2020-09-15');
INSERT INTO clinicas VALUES (3, 'VetRed Sevilla Triana', 'Calle San Jacinto 45', 'Sevilla', '954000003', DATE '2023-01-10');
-- TRABAJADORES (primero los supervisores)
INSERT INTO trabajadores VALUES (1, '11111111A', 'Carmen', 'Ibáñez Soler', 'carmen.ibanez@vetred.es', DATE '2018-03-01', 3800, 'VETERINARIO', 1, NULL);
INSERT INTO trabajadores VALUES (2, '22222222B', 'Andrés', 'Lozano Pita', 'andres.lozano@vetred.es', DATE '2018-06-01', 3100, 'VETERINARIO', 1, 1);
INSERT INTO trabajadores VALUES (3, '33333333C', 'Beatriz','Ramos Cid', 'beatriz.ramos@vetred.es', DATE '2020-09-15', 3400, 'VETERINARIO', 2, 1);
INSERT INTO trabajadores VALUES (4, '44444444D', 'Óscar', 'Varela Muñoz', 'oscar.varela@vetred.es', DATE '2021-02-01', 2900, 'VETERINARIO', 2, 3);
INSERT INTO trabajadores VALUES (5, '55555555E', 'Inés', 'Calvo Durán', 'ines.calvo@vetred.es', DATE '2023-01-10', 3000, 'VETERINARIO', 3, 1);
INSERT INTO trabajadores VALUES (6, '66666666F', 'Rubén', 'Soria León', 'ruben.soria@vetred.es', DATE '2019-04-01', 1750, 'AUXILIAR', 1, 2);
INSERT INTO trabajadores VALUES (7, '77777777G', 'Paula', 'Nieto Gallego', 'paula.nieto@vetred.es', DATE '2021-03-15', 1700, 'AUXILIAR', 2, 3);
INSERT INTO trabajadores VALUES (8, '88888888H', 'Hugo', 'Pastor Rey', 'hugo.pastor@vetred.es', DATE '2023-02-01', 1650, 'AUXILIAR', 3, 5);
INSERT INTO trabajadores VALUES (9, '99999999J', 'Marta', 'Esteve Gil', 'marta.esteve@vetred.es', DATE '2018-03-01', 1500, 'RECEPCION', 1, 1);
INSERT INTO trabajadores VALUES (10, '10101010K', 'Diego', 'Morales Sanz', 'diego.morales@vetred.es', DATE '2020-09-15', 1500, 'RECEPCION', 2, 3);
INSERT INTO veterinarios (trabajador_id, num_colegiado, especialidad) VALUES (1, 'V-46-0101', 'Cirugía');
INSERT INTO veterinarios (trabajador_id, num_colegiado, especialidad) VALUES (2, 'V-46-0234', 'Dermatología');
INSERT INTO veterinarios (trabajador_id, num_colegiado, especialidad) VALUES (3, 'V-28-1120', 'Medicina interna');
INSERT INTO veterinarios (trabajador_id, num_colegiado, especialidad) VALUES (4, 'V-28-1458', NULL);
INSERT INTO veterinarios (trabajador_id, num_colegiado, especialidad) VALUES (5, 'V-41-0567', 'Animales exóticos');
INSERT INTO auxiliares (trabajador_id, titulacion) VALUES (6, 'Técnico en Cuidados Auxiliares Veterinarios');
INSERT INTO auxiliares (trabajador_id, titulacion) VALUES (7, 'Técnico en Cuidados Auxiliares Veterinarios');
INSERT INTO auxiliares (trabajador_id, titulacion) VALUES (8, 'Técnico Superior en Asistencia Veterinaria');
-- PROPIETARIOS (clave generada con secuencia) y TELEFONOS
INSERT INTO propietarios VALUES (seq_propietarios.NEXTVAL, '20000001A', 'Laura', 'Gómez Ferrer', 'laura.gomez@correo.es', 'Valencia', DATE '2018-05-10');
INSERT INTO propietarios VALUES (seq_propietarios.NEXTVAL, '20000002B', 'Javier', 'Martín Roca', 'javier.martin@correo.es', 'Valencia', DATE '2019-01-22');
INSERT INTO propietarios VALUES (seq_propietarios.NEXTVAL, '20000003C', 'Sofía', 'Navarro Ruiz', NULL, 'Madrid', DATE '2020-10-01');
INSERT INTO propietarios VALUES (seq_propietarios.NEXTVAL, '20000004D', 'Daniel', 'Herrera Vidal', 'daniel.herrera@correo.es', 'Madrid', DATE '2021-04-17');
INSERT INTO propietarios VALUES (seq_propietarios.NEXTVAL, '20000005E', 'Lucía', 'Domínguez Pardo', 'lucia.dominguez@correo.es', 'Sevilla', DATE '2023-02-03');
INSERT INTO propietarios VALUES (seq_propietarios.NEXTVAL, '20000006F', 'Alberto', 'Sanz Moreno', 'alberto.sanz@correo.es', 'Valencia', DATE '2024-06-30');
INSERT INTO propietarios VALUES (seq_propietarios.NEXTVAL, '20000007G', 'Elena', 'Castillo Mena', 'elena.castillo@correo.es', 'Madrid', DATE '2025-03-12');
INSERT INTO propietarios VALUES (seq_propietarios.NEXTVAL, '20000008H', 'Raúl', 'Ortega Prieto', 'raul.ortega@correo.es', 'Sevilla', DATE '2026-09-20');
INSERT INTO telefonos_propietario VALUES (1, '600111111', 'MOVIL');
INSERT INTO telefonos_propietario VALUES (1, '963111111', 'FIJO');
INSERT INTO telefonos_propietario VALUES (2, '600222222', 'MOVIL');
INSERT INTO telefonos_propietario VALUES (3, '600333333', 'MOVIL');
INSERT INTO telefonos_propietario VALUES (3, '912333333', 'TRABAJO');
INSERT INTO telefonos_propietario VALUES (4, '600444444', 'MOVIL');
INSERT INTO telefonos_propietario VALUES (5, '600555555', 'MOVIL');
INSERT INTO telefonos_propietario VALUES (6, '600666666', 'MOVIL');
INSERT INTO telefonos_propietario VALUES (7, '600777777', 'MOVIL');
INSERT INTO telefonos_propietario VALUES (7, '917777777', 'FIJO');
-- MASCOTAS (identidad: reciben 1..12 en orden)
INSERT INTO mascotas (propietario_id, nombre, especie, raza, sexo, fecha_nacimiento, chip, peso_kg) VALUES (1, 'Luna', 'PERRO', 'Labrador', 'H', DATE '2017-04-12', '941000011111111', 28.5);
INSERT INTO mascotas (propietario_id, nombre, especie, raza, sexo, fecha_nacimiento, chip, peso_kg) VALUES (1, 'Michi', 'GATO', 'Europeo común', 'M', DATE '2019-08-03', '941000011111112', 4.8);
INSERT INTO mascotas (propietario_id, nombre, especie, raza, sexo, fecha_nacimiento, chip, peso_kg) VALUES (2, 'Rocky', 'PERRO', 'Bulldog francés', 'M', DATE '2020-02-20', '941000022222221', 12.3);
INSERT INTO mascotas (propietario_id, nombre, especie, raza, sexo, fecha_nacimiento, chip, peso_kg) VALUES (3, 'Nala', 'GATO', 'Siamés', 'H', DATE '2018-11-05', NULL, 3.9);
INSERT INTO mascotas (propietario_id, nombre, especie, raza, sexo, fecha_nacimiento, chip, peso_kg) VALUES (3, 'Kiwi', 'AVE', 'Agapornis', 'H', DATE '2022-05-01', NULL, 0.05);
INSERT INTO mascotas (propietario_id, nombre, especie, raza, sexo, fecha_nacimiento, chip, peso_kg) VALUES (4, 'Thor', 'PERRO', 'Pastor alemán', 'M', DATE '2016-07-30', '941000044444441', 35);
INSERT INTO mascotas (propietario_id, nombre, especie, raza, sexo, fecha_nacimiento, chip, peso_kg) VALUES (5, 'Coco', 'CONEJO', 'Belier', 'M', DATE '2023-03-15', NULL, 1.9);
INSERT INTO mascotas (propietario_id, nombre, especie, raza, sexo, fecha_nacimiento, chip, peso_kg) VALUES (5, 'Draco', 'REPTIL', 'Pogona', 'M', DATE '2021-09-09', NULL, 0.45);
INSERT INTO mascotas (propietario_id, nombre, especie, raza, sexo, fecha_nacimiento, chip, peso_kg) VALUES (6, 'Bimba', 'PERRO', 'Mestizo', 'H', DATE '2024-01-20', '941000066666661', 8.2);
INSERT INTO mascotas (propietario_id, nombre, especie, raza, sexo, fecha_nacimiento, chip, peso_kg) VALUES (7, 'Simba', 'GATO', 'Maine Coon', 'M', DATE '2022-12-01', '941000077777771', 7.1);
INSERT INTO mascotas (propietario_id, nombre, especie, raza, sexo, fecha_nacimiento, chip, peso_kg) VALUES (7, 'Lola', 'PERRO', 'Caniche', 'H', DATE '2015-03-03', '941000077777772', 6.4);
INSERT INTO mascotas (propietario_id, nombre, especie, raza, sexo, fecha_nacimiento, chip, peso_kg) VALUES (2, 'Nemo', 'OTRO', 'Hurón', 'M', DATE '2024-05-05', NULL, 1.1);
-- MEDICAMENTOS
INSERT INTO medicamentos VALUES (1, 'Amoxivet 250 mg', 'Amoxicilina', 'Vetfarma', 12.50, 'S');
INSERT INTO medicamentos VALUES (2, 'Meloxivet 1,5 mg/ml', 'Meloxicam', 'Vetfarma', 18.90, 'S');
INSERT INTO medicamentos VALUES (3, 'Antipulgas Spot-on', 'Fipronil', 'Animalia', 9.95, 'N');
INSERT INTO medicamentos VALUES (4, 'Desparasitante Plus', 'Praziquantel', 'Animalia', 7.40, 'N');
INSERT INTO medicamentos VALUES (5, 'Dermacalm champú', 'Clorhexidina', 'DermaPet', 14.20, 'N');
INSERT INTO medicamentos VALUES (6, 'Cardiovet 5 mg', 'Pimobendán', 'Vetfarma', 32.00, 'S');
INSERT INTO medicamentos VALUES (7, 'Otoclean gotas', 'Gentamicina', 'DermaPet', 11.30, 'S');
INSERT INTO medicamentos VALUES (8, 'Vitaminas Exóticos', 'Complejo vitamínico', 'ExoticCare', 8.60, 'N');
-- CONSULTAS (identidad: reciben 1..22 en orden)
INSERT INTO consultas (mascota_id, veterinario_id, clinica_id, fecha_hora, motivo, diagnostico, importe, estado) VALUES (1, 1, 1, TO_DATE('2025-01-14 10:00', 'YYYY-MM-DD HH24:MI'), 'Revisión anual', 'Sano', 35, 'REALIZADA');
INSERT INTO consultas (mascota_id, veterinario_id, clinica_id, fecha_hora, motivo, diagnostico, importe, estado) VALUES (2, 2, 1, TO_DATE('2025-02-03 17:30', 'YYYY-MM-DD HH24:MI'), 'Picor en la piel', 'Dermatitis alérgica', 45, 'REALIZADA');
INSERT INTO consultas (mascota_id, veterinario_id, clinica_id, fecha_hora, motivo, diagnostico, importe, estado) VALUES (3, 2, 1, TO_DATE('2025-03-11 11:15', 'YYYY-MM-DD HH24:MI'), 'Problemas respiratorios', 'Síndrome braquicefálico leve', 60, 'REALIZADA');
INSERT INTO consultas (mascota_id, veterinario_id, clinica_id, fecha_hora, motivo, diagnostico, importe, estado) VALUES (4, 3, 2, TO_DATE('2025-04-08 09:45', 'YYYY-MM-DD HH24:MI'), 'Vómitos', 'Gastritis', 50, 'REALIZADA');
INSERT INTO consultas (mascota_id, veterinario_id, clinica_id, fecha_hora, motivo, diagnostico, importe, estado) VALUES (6, 3, 2, TO_DATE('2025-05-20 12:00', 'YYYY-MM-DD HH24:MI'), 'Cojera', 'Displasia de cadera', 80, 'REALIZADA');
INSERT INTO consultas (mascota_id, veterinario_id, clinica_id, fecha_hora, motivo, diagnostico, importe, estado) VALUES (6, 4, 2, TO_DATE('2025-06-17 16:00', 'YYYY-MM-DD HH24:MI'), 'Control displasia', 'Evolución favorable', 40, 'REALIZADA');
INSERT INTO consultas (mascota_id, veterinario_id, clinica_id, fecha_hora, motivo, diagnostico, importe, estado) VALUES (7, 5, 3, TO_DATE('2025-07-02 10:30', 'YYYY-MM-DD HH24:MI'), 'Revisión dental', 'Maloclusión dental', 55, 'REALIZADA');
INSERT INTO consultas (mascota_id, veterinario_id, clinica_id, fecha_hora, motivo, diagnostico, importe, estado) VALUES (8, 5, 3, TO_DATE('2025-09-15 13:00', 'YYYY-MM-DD HH24:MI'), 'Inapetencia', 'Déficit de calcio', 65, 'REALIZADA');
INSERT INTO consultas (mascota_id, veterinario_id, clinica_id, fecha_hora, motivo, diagnostico, importe, estado) VALUES (1, 1, 1, TO_DATE('2025-11-04 10:00', 'YYYY-MM-DD HH24:MI'), 'Bulto en el costado', 'Lipoma benigno', 90, 'REALIZADA');
INSERT INTO consultas (mascota_id, veterinario_id, clinica_id, fecha_hora, motivo, diagnostico, importe, estado) VALUES (10, 3, 2, TO_DATE('2025-12-09 18:00', 'YYYY-MM-DD HH24:MI'), 'Revisión', 'Sano', 35, 'REALIZADA');
INSERT INTO consultas (mascota_id, veterinario_id, clinica_id, fecha_hora, motivo, diagnostico, importe, estado) VALUES (11, 4, 2, TO_DATE('2026-01-13 11:00', 'YYYY-MM-DD HH24:MI'), 'Tos persistente', 'Insuficiencia cardiaca leve', 70, 'REALIZADA');
INSERT INTO consultas (mascota_id, veterinario_id, clinica_id, fecha_hora, motivo, diagnostico, importe, estado) VALUES (9, 2, 1, TO_DATE('2026-02-10 17:00', 'YYYY-MM-DD HH24:MI'), 'Primera visita', 'Sano', 30, 'REALIZADA');
INSERT INTO consultas (mascota_id, veterinario_id, clinica_id, fecha_hora, motivo, diagnostico, importe, estado) VALUES (3, 1, 1, TO_DATE('2026-03-17 09:30', 'YYYY-MM-DD HH24:MI'), 'Otitis', 'Otitis externa', 45, 'REALIZADA');
INSERT INTO consultas (mascota_id, veterinario_id, clinica_id, fecha_hora, motivo, diagnostico, importe, estado) VALUES (5, 5, 3, TO_DATE('2026-04-21 12:30', 'YYYY-MM-DD HH24:MI'), 'Plumas dañadas', 'Picaje por estrés', 50, 'REALIZADA');
INSERT INTO consultas (mascota_id, veterinario_id, clinica_id, fecha_hora, motivo, diagnostico, importe, estado) VALUES (2, 2, 1, TO_DATE('2026-05-05 18:30', 'YYYY-MM-DD HH24:MI'), 'Control dermatitis', 'Mejoría', 30, 'REALIZADA');
INSERT INTO consultas (mascota_id, veterinario_id, clinica_id, fecha_hora, motivo, diagnostico, importe, estado) VALUES (11, 3, 2, TO_DATE('2026-06-16 10:15', 'YYYY-MM-DD HH24:MI'), 'Control cardiológico', 'Estable', 60, 'REALIZADA');
INSERT INTO consultas (mascota_id, veterinario_id, clinica_id, fecha_hora, motivo, diagnostico, importe, estado) VALUES (12, 1, 1, TO_DATE('2026-07-07 16:45', 'YYYY-MM-DD HH24:MI'), 'Revisión', 'Sano', 35, 'REALIZADA');
INSERT INTO consultas (mascota_id, veterinario_id, clinica_id, fecha_hora, motivo, diagnostico, importe, estado) VALUES (6, 4, 2, TO_DATE('2026-08-25 09:00', 'YYYY-MM-DD HH24:MI'), 'Dolor articular', NULL, 0, 'CANCELADA');
INSERT INTO consultas (mascota_id, veterinario_id, clinica_id, fecha_hora, motivo, diagnostico, importe, estado) VALUES (10, 4, 2, TO_DATE('2026-09-22 17:15', 'YYYY-MM-DD HH24:MI'), 'Vacunación y revisión', 'Sano', 40, 'REALIZADA');
INSERT INTO consultas (mascota_id, veterinario_id, clinica_id, fecha_hora, motivo) VALUES (1, 1, 1, TO_DATE('2026-10-08 10:00', 'YYYY-MM-DD HH24:MI'), 'Revisión anual');
INSERT INTO consultas (mascota_id, veterinario_id, clinica_id, fecha_hora, motivo) VALUES (7, 5, 3, TO_DATE('2026-10-09 11:30', 'YYYY-MM-DD HH24:MI'), 'Control dental');
INSERT INTO consultas (mascota_id, veterinario_id, clinica_id, fecha_hora, motivo) VALUES (4, 3, 2, TO_DATE('2026-10-14 09:30', 'YYYY-MM-DD HH24:MI'), 'Vómitos recurrentes');
-- PRESCRIPCIONES
INSERT INTO prescripciones VALUES (2, 5, 'Baño cada 3 días', 21, 1);
INSERT INTO prescripciones VALUES (2, 3, '1 pipeta', 30, 1);
INSERT INTO prescripciones VALUES (3, 2, '0,1 ml/kg cada 24 h', 7, 1);
INSERT INTO prescripciones VALUES (4, 1, '1 comprimido cada 12 h', 7, 2);
INSERT INTO prescripciones VALUES (5, 2, '0,1 ml/kg cada 24 h', 30, 2);
INSERT INTO prescripciones VALUES (6, 2, '0,05 ml/kg cada 24 h', 30, 1);
INSERT INTO prescripciones VALUES (8, 8, '5 gotas al día', 60, 1);
INSERT INTO prescripciones VALUES (9, 1, '1 comprimido cada 12 h', 10, 2);
INSERT INTO prescripciones VALUES (11, 6, '1 comprimido cada 12 h', 90, 3);
INSERT INTO prescripciones VALUES (13, 7, '4 gotas cada 12 h', 10, 1);
INSERT INTO prescripciones VALUES (13, 1, '1 comprimido cada 12 h', 7, 1);
INSERT INTO prescripciones VALUES (14, 8, '3 gotas al día', 30, 1);
INSERT INTO prescripciones VALUES (15, 5, 'Baño semanal', 30, 1);
INSERT INTO prescripciones VALUES (16, 6, '1 comprimido cada 12 h', 90, 3);
INSERT INTO prescripciones VALUES (19, 3, '1 pipeta', 30, 1);
-- VACUNACIONES
INSERT INTO vacunaciones VALUES (1, 1, 'Rabia', DATE '2024-01-10', DATE '2025-01-10', 1);
INSERT INTO vacunaciones VALUES (1, 2, 'Rabia', DATE '2025-01-14', DATE '2026-01-14', 1);
INSERT INTO vacunaciones VALUES (1, 3, 'Polivalente canina', DATE '2025-01-14', DATE '2026-01-14', 1);
INSERT INTO vacunaciones VALUES (2, 1, 'Trivalente felina', DATE '2025-02-03', DATE '2026-02-03', 2);
INSERT INTO vacunaciones VALUES (3, 1, 'Rabia', DATE '2025-03-11', DATE '2026-03-11', 2);
INSERT INTO vacunaciones VALUES (3, 2, 'Rabia', DATE '2026-03-17', DATE '2027-03-17', 1);
INSERT INTO vacunaciones VALUES (4, 1, 'Trivalente felina', DATE '2025-04-08', DATE '2026-04-08', 3);
INSERT INTO vacunaciones VALUES (6, 1, 'Rabia', DATE '2025-05-20', DATE '2026-05-20', 3);
INSERT INTO vacunaciones VALUES (6, 2, 'Polivalente canina', DATE '2025-05-20', DATE '2026-05-20', 3);
INSERT INTO vacunaciones VALUES (7, 1, 'Mixomatosis', DATE '2025-07-02', DATE '2026-01-02', 5);
INSERT INTO vacunaciones VALUES (7, 2, 'Mixomatosis', DATE '2026-01-08', DATE '2026-07-08', 5);
INSERT INTO vacunaciones VALUES (9, 1, 'Polivalente canina', DATE '2026-02-10', DATE '2026-03-10', 2);
INSERT INTO vacunaciones VALUES (9, 2, 'Polivalente canina', DATE '2026-03-10', DATE '2027-03-10', 2);
INSERT INTO vacunaciones VALUES (9, 3, 'Rabia', DATE '2026-03-10', DATE '2027-03-10', 2);
INSERT INTO vacunaciones VALUES (10, 1, 'Trivalente felina', DATE '2025-12-09', DATE '2026-12-09', 3);
INSERT INTO vacunaciones VALUES (10, 2, 'Leucemia felina', DATE '2026-09-22', DATE '2027-09-22', 4);
INSERT INTO vacunaciones VALUES (11, 1, 'Rabia', DATE '2026-01-13', DATE '2027-01-13', 4);
COMMIT;
6.2. Comprobación de la carga
El script se ejecutó con SET FEEDBACK OFF (para no mostrar un 1 fila creada. por cada sentencia) y sin errores. Comprobamos el número de filas de cada tabla:
SELECT 'CLINICAS' AS tabla, COUNT(*) AS filas FROM clinicas UNION ALL
SELECT 'TRABAJADORES', COUNT(*) FROM trabajadores UNION ALL
SELECT 'VETERINARIOS', COUNT(*) FROM veterinarios UNION ALL
SELECT 'AUXILIARES', COUNT(*) FROM auxiliares UNION ALL
SELECT 'PROPIETARIOS', COUNT(*) FROM propietarios UNION ALL
SELECT 'TELEFONOS_PROPIETARIO', COUNT(*) FROM telefonos_propietario UNION ALL
SELECT 'MASCOTAS', COUNT(*) FROM mascotas UNION ALL
SELECT 'CONSULTAS', COUNT(*) FROM consultas UNION ALL
SELECT 'MEDICAMENTOS', COUNT(*) FROM medicamentos UNION ALL
SELECT 'PRESCRIPCIONES', COUNT(*) FROM prescripciones UNION ALL
SELECT 'VACUNACIONES', COUNT(*) FROM vacunaciones;
TABLA FILAS
---------------------- ----------
CLINICAS 3
TRABAJADORES 10
VETERINARIOS 5
AUXILIARES 3
PROPIETARIOS 8
TELEFONOS_PROPIETARIO 10
MASCOTAS 12
CONSULTAS 22
MEDICAMENTOS 8
PRESCRIPCIONES 15
VACUNACIONES 17
11 filas seleccionadas.
💡 Consejo: El orden de las sentencias de carga sigue el grafo relacional: primero las tablas sin claves ajenas (CLINICAS, MEDICAMENTOS), después las que dependen de ellas. Dentro de TRABAJADORES, los supervisores se insertan antes que sus supervisados (la directora, la 1, la primera). Si el orden no se puede respetar (ciclos), se recurre a restricciones diferibles (módulo 34).
7. Explotación: 25 consultas de dificultad creciente
Todas las consultas se han ejecutado sobre los datos de la sección 6 con la configuración de sesión habitual del curso (SET NULL '(nulo)', NLS_LANGUAGE = 'SPANISH', NLS_TERRITORY = 'SPAIN', NLS_DATE_FORMAT = 'DD/MM/YYYY'). Cuando se necesita “hoy”, se usa la fecha fija del 1 de octubre de 2026 para que el resultado sea reproducible.
Consulta 1: Mascotas y su edad ★
Todas las mascotas con su edad en años cumplidos (atributo derivado), de mayor a menor.
Ver solución
SELECT m.mascota_id, m.nombre, m.especie, m.raza,
TRUNC(MONTHS_BETWEEN(DATE '2026-10-01', m.fecha_nacimiento) / 12) AS edad
FROM mascotas m
ORDER BY edad DESC, m.nombre;
MASCOTA_ID NOMBRE ESPECIE RAZA EDAD
---------- ---------- ------- ---------------- ----------
11 Lola PERRO Caniche 11
6 Thor PERRO Pastor alemán 10
1 Luna PERRO Labrador 9
2 Michi GATO Europeo común 7
4 Nala GATO Siamés 7
3 Rocky PERRO Bulldog francés 6
8 Draco REPTIL Pogona 5
5 Kiwi AVE Agapornis 4
7 Coco CONEJO Belier 3
10 Simba GATO Maine Coon 3
9 Bimba PERRO Mestizo 2
12 Nemo OTRO Hurón 2
12 filas seleccionadas.
La edad no se almacena (cambiaría cada día); se calcula. En una aplicación real se usaría SYSDATE.
Consulta 2: Clientes de Madrid ★
Ver solución
SELECT propietario_id, nombre, apellidos, email, fecha_alta
FROM propietarios
WHERE ciudad = 'Madrid'
ORDER BY apellidos;
PROPIETARIO_ID NOMBRE APELLIDOS EMAIL FECHA_ALTA
-------------- ---------- ---------------- -------------------------- ----------
7 Elena Castillo Mena elena.castillo@correo.es 12/03/2025
4 Daniel Herrera Vidal daniel.herrera@correo.es 17/04/2021
3 Sofía Navarro Ruiz (nulo) 01/10/2020
3 filas seleccionadas.
Consulta 3: Agenda de citas (RF1) ★
Consultas programadas, usando la vista v_agenda.
Ver solución
SELECT clinica, TO_CHAR(fecha_hora, 'DD/MM/YYYY HH24:MI') AS hora, mascota, propietario, veterinario
FROM v_agenda
ORDER BY fecha_hora;
CLINICA HORA MASCOTA PROPIETARIO VETERINARIO
------------------------ ---------------- -------- ---------------------- --------------------
VetRed Valencia Centro 08/10/2026 10:00 Luna Laura Gómez Ferrer Carmen Ibáñez Soler
VetRed Sevilla Triana 09/10/2026 11:30 Coco Lucía Domínguez Pardo Inés Calvo Durán
VetRed Madrid Chamberí 14/10/2026 09:30 Nala Sofía Navarro Ruiz Beatriz Ramos Cid
3 filas seleccionadas.
La vista oculta un join de cinco tablas: es la forma de dar a recepción una consulta sencilla (y, en la sección 9, de darle permisos solo sobre lo que necesita).
Consulta 4: Teléfonos de cada cliente (atributo multivaluado) ★★
Cada propietario con todos sus teléfonos en una línea, incluidos los que no tienen ninguno.
Ver solución
Primer intento:
SELECT p.nombre, p.apellidos,
LISTAGG(t.telefono || ' (' || t.tipo || ')', ', ') WITHIN GROUP (ORDER BY t.tipo) AS telefonos
FROM propietarios p LEFT JOIN telefonos_propietario t ON t.propietario_id = p.propietario_id
GROUP BY p.propietario_id, p.nombre, p.apellidos
ORDER BY p.propietario_id;
NOMBRE APELLIDOS TELEFONOS
---------- ---------------- ----------------------------------------
Laura Gómez Ferrer 963111111 (FIJO), 600111111 (MOVIL)
Javier Martín Roca 600222222 (MOVIL)
Sofía Navarro Ruiz 600333333 (MOVIL), 912333333 (TRABAJO)
Daniel Herrera Vidal 600444444 (MOVIL)
Lucía Domínguez Pardo 600555555 (MOVIL)
Alberto Sanz Moreno 600666666 (MOVIL)
Elena Castillo Mena 917777777 (FIJO), 600777777 (MOVIL)
Raúl Ortega Prieto ()
8 filas seleccionadas.
Raúl aparece con " ()": el LEFT JOIN le da una fila con teléfono y tipo nulos, y la concatenación con nulos no da nulo en Oracle. Solución: concatenar solo si hay teléfono y sustituir la lista vacía:
SELECT p.nombre, p.apellidos,
NVL(LISTAGG(CASE WHEN t.telefono IS NOT NULL THEN t.telefono || ' (' || t.tipo || ')' END, ', ')
WITHIN GROUP (ORDER BY t.tipo), 'Sin teléfono') AS telefonos
FROM propietarios p LEFT JOIN telefonos_propietario t ON t.propietario_id = p.propietario_id
GROUP BY p.propietario_id, p.nombre, p.apellidos
ORDER BY p.propietario_id;
NOMBRE APELLIDOS TELEFONOS
---------- ---------------- ----------------------------------------
Laura Gómez Ferrer 963111111 (FIJO), 600111111 (MOVIL)
Javier Martín Roca 600222222 (MOVIL)
Sofía Navarro Ruiz 600333333 (MOVIL), 912333333 (TRABAJO)
Daniel Herrera Vidal 600444444 (MOVIL)
Lucía Domínguez Pardo 600555555 (MOVIL)
Alberto Sanz Moreno 600666666 (MOVIL)
Elena Castillo Mena 917777777 (FIJO), 600777777 (MOVIL)
Raúl Ortega Prieto Sin teléfono
8 filas seleccionadas.
LISTAGG ignora los nulos, así que con el CASE la lista de Raúl queda nula y NVL la sustituye.
Consulta 5: Mascotas sin microchip ★
Ver solución
SELECT m.nombre, m.especie, p.nombre || ' ' || p.apellidos AS propietario
FROM mascotas m JOIN propietarios p ON p.propietario_id = m.propietario_id
WHERE m.chip IS NULL
ORDER BY m.especie, m.nombre;
NOMBRE ESPECIE PROPIETARIO
---------- ------- ----------------------
Kiwi AVE Sofía Navarro Ruiz
Coco CONEJO Lucía Domínguez Pardo
Nala GATO Sofía Navarro Ruiz
Nemo OTRO Javier Martín Roca
Draco REPTIL Lucía Domínguez Pardo
5 filas seleccionadas.
Todos los perros tienen chip (es obligatorio en España); Nala, una gata, no lo tiene: un aviso útil para la clínica.
Consulta 6: Censo por especie ★★
Número de mascotas por especie, desglosado por sexo, y peso medio.
Ver solución
SELECT especie,
COUNT(*) AS total,
COUNT(CASE WHEN sexo = 'M' THEN 1 END) AS machos,
COUNT(CASE WHEN sexo = 'H' THEN 1 END) AS hembras,
ROUND(AVG(peso_kg), 2) AS peso_medio
FROM mascotas
GROUP BY especie
ORDER BY total DESC, especie;
ESPECIE TOTAL MACHOS HEMBRAS PESO_MEDIO
------- ---------- ---------- ---------- ----------
PERRO 5 2 3 18,08
GATO 3 2 1 5,27
AVE 1 0 1 ,05
CONEJO 1 1 0 1,9
OTRO 1 1 0 1,1
REPTIL 1 1 0 ,45
6 filas seleccionadas.
SQL*Plus muestra ,05 sin el cero inicial; con COLUMN peso_medio FORMAT 990D99 se mostraría 0,05.
Consulta 7: Facturación por clínica y año (RF4) ★★
Ver solución
SELECT cl.nombre AS clinica, EXTRACT(YEAR FROM c.fecha_hora) AS anio,
COUNT(*) AS consultas, SUM(c.importe) AS facturado
FROM consultas c JOIN clinicas cl ON cl.clinica_id = c.clinica_id
WHERE c.estado = 'REALIZADA'
GROUP BY cl.nombre, EXTRACT(YEAR FROM c.fecha_hora)
ORDER BY clinica, anio;
CLINICA ANIO CONSULTAS FACTURADO
------------------------ ---------- ---------- ----------
VetRed Madrid Chamberí 2025 4 205
VetRed Madrid Chamberí 2026 3 170
VetRed Sevilla Triana 2025 2 120
VetRed Sevilla Triana 2026 1 50
VetRed Valencia Centro 2025 4 230
VetRed Valencia Centro 2026 4 140
6 filas seleccionadas.
Solo se facturan las consultas realizadas: las programadas y la cancelada tienen importe 0, pero contarlas inflaría el número de consultas.
Consulta 8: Actividad de los veterinarios (RF5) ★★
Cada veterinario con su especialidad, el número de consultas realizadas y su importe medio.
Ver solución
SELECT t.nombre || ' ' || t.apellidos AS veterinario, v.especialidad,
COUNT(c.consulta_id) AS consultas, ROUND(AVG(c.importe), 2) AS importe_medio
FROM veterinarios v
JOIN trabajadores t ON t.trabajador_id = v.trabajador_id
LEFT JOIN consultas c ON c.veterinario_id = v.trabajador_id AND c.estado = 'REALIZADA'
GROUP BY t.nombre, t.apellidos, v.especialidad
ORDER BY consultas DESC;
VETERINARIO ESPECIALIDAD CONSULTAS IMPORTE_MEDIO
-------------------- ------------------------------ ---------- -------------
Carmen Ibáñez Soler Cirugía 4 51,25
Andrés Lozano Pita Dermatología 4 41,25
Beatriz Ramos Cid Medicina interna 4 56,25
Óscar Varela Muñoz (nulo) 3 50
Inés Calvo Durán Animales exóticos 3 56,67
5 filas seleccionadas.
La condición sobre el estado va en el ON del LEFT JOIN, no en el WHERE: así un veterinario sin consultas realizadas seguiría apareciendo con 0.
Consulta 9: Historial clínico de Luna (RF2) ★★
Todas las consultas de la mascota 1 con sus tratamientos.
Ver solución
SELECT c.consulta_id, c.fecha_hora, c.motivo, c.diagnostico, md.nombre AS medicamento, p.dosis
FROM consultas c
LEFT JOIN prescripciones p ON p.consulta_id = c.consulta_id
LEFT JOIN medicamentos md ON md.medicamento_id = p.medicamento_id
WHERE c.mascota_id = 1
ORDER BY c.fecha_hora;
CONSULTA_ID FECHA_HORA MOTIVO DIAGNOSTICO MEDICAMENTO DOSIS
----------- ---------- ---------------------- ---------------------------- ---------------------- ----------------------
1 14/01/2025 Revisión anual Sano (nulo) (nulo)
9 04/11/2025 Bulto en el costado Lipoma benigno Amoxivet 250 mg 1 comprimido cada 12 h
20 08/10/2026 Revisión anual (nulo) (nulo) (nulo)
3 filas seleccionadas.
Los dos LEFT JOIN encadenados conservan las consultas sin prescripción. La 20 es la cita programada (aún sin diagnóstico).
Consulta 10: Las consultas más caras ★★
Importe total de cada consulta realizada (consulta + medicamentos a precio de catálogo); las seis más caras.
Ver solución
SELECT c.consulta_id, m.nombre AS mascota, c.importe AS consulta,
NVL(SUM(md.precio * p.unidades), 0) AS medicamentos,
c.importe + NVL(SUM(md.precio * p.unidades), 0) AS total
FROM consultas c
JOIN mascotas m ON m.mascota_id = c.mascota_id
LEFT JOIN prescripciones p ON p.consulta_id = c.consulta_id
LEFT JOIN medicamentos md ON md.medicamento_id = p.medicamento_id
WHERE c.estado = 'REALIZADA'
GROUP BY c.consulta_id, m.nombre, c.importe
ORDER BY total DESC
FETCH FIRST 6 ROWS ONLY;
CONSULTA_ID MASCOTA CONSULTA MEDICAMENTOS TOTAL
----------- -------- ---------- ------------ ----------
11 Lola 70 96 166
16 Lola 60 96 156
5 Thor 80 37,8 117,8
9 Luna 90 25 115
3 Rocky 60 18,9 78,9
4 Nala 50 25 75
6 filas seleccionadas.
c.importe está en el GROUP BY y se suma fuera del SUM: si se escribiera SUM(c.importe + md.precio * p.unidades), el importe de la consulta se sumaría una vez por cada medicamento.
Consulta 11: Plantilla con su supervisor ★★
Ver solución
SELECT t.nombre || ' ' || t.apellidos AS trabajador, t.tipo, cl.nombre AS clinica,
NVL2(s.trabajador_id, s.nombre || ' ' || s.apellidos, '(ninguno)') AS supervisor
FROM trabajadores t
JOIN clinicas cl ON cl.clinica_id = t.clinica_id
LEFT JOIN trabajadores s ON s.trabajador_id = t.supervisor_id
ORDER BY t.clinica_id, t.tipo, t.trabajador_id;
TRABAJADOR TIPO CLINICA SUPERVISOR
---------------------- ----------- ------------------------ ----------------------
Rubén Soria León AUXILIAR VetRed Valencia Centro Andrés Lozano Pita
Marta Esteve Gil RECEPCION VetRed Valencia Centro Carmen Ibáñez Soler
Carmen Ibáñez Soler VETERINARIO VetRed Valencia Centro (ninguno)
Andrés Lozano Pita VETERINARIO VetRed Valencia Centro Carmen Ibáñez Soler
Paula Nieto Gallego AUXILIAR VetRed Madrid Chamberí Beatriz Ramos Cid
Diego Morales Sanz RECEPCION VetRed Madrid Chamberí Beatriz Ramos Cid
Beatriz Ramos Cid VETERINARIO VetRed Madrid Chamberí Carmen Ibáñez Soler
Óscar Varela Muñoz VETERINARIO VetRed Madrid Chamberí Beatriz Ramos Cid
Hugo Pastor Rey AUXILIAR VetRed Sevilla Triana Inés Calvo Durán
Inés Calvo Durán VETERINARIO VetRed Sevilla Triana Carmen Ibáñez Soler
10 filas seleccionadas.
NVL2 pregunta por la clave del supervisor; NVL(s.nombre || ' ' || s.apellidos, ...) no funcionaría porque la concatenación de nulos da ' '.
Consulta 12: Organigrama de la red (RF6) ★★
Ver solución
SELECT LPAD(' ', (LEVEL - 1) * 3) || nombre || ' ' || apellidos || ' (' || tipo || ')' AS organigrama,
clinica_id
FROM trabajadores
START WITH supervisor_id IS NULL
CONNECT BY PRIOR trabajador_id = supervisor_id
ORDER SIBLINGS BY apellidos;
ORGANIGRAMA CLINICA_ID
---------------------------------------- ----------
Carmen Ibáñez Soler (VETERINARIO) 1
Inés Calvo Durán (VETERINARIO) 3
Hugo Pastor Rey (AUXILIAR) 3
Marta Esteve Gil (RECEPCION) 1
Andrés Lozano Pita (VETERINARIO) 1
Rubén Soria León (AUXILIAR) 1
Beatriz Ramos Cid (VETERINARIO) 2
Diego Morales Sanz (RECEPCION) 2
Paula Nieto Gallego (AUXILIAR) 2
Óscar Varela Muñoz (VETERINARIO) 2
10 filas seleccionadas.
La relación reflexiva supervisa se recorre con CONNECT BY; una CTE recursiva daría el mismo resultado (módulo 32). Fíjate en que la supervisión cruza clínicas.
Consulta 13: Vacunas vencidas (RF3) ★★★
Para cada mascota y vacuna, la última dosis cuya fecha de revacunación ya pasó a 1/10/2026, con los días de retraso.
Ver solución
SELECT m.nombre AS mascota, p.nombre || ' ' || p.apellidos AS propietario, v.vacuna, v.fecha, v.fecha_proxima,
DATE '2026-10-01' - v.fecha_proxima AS dias_vencida
FROM vacunaciones v
JOIN mascotas m ON m.mascota_id = v.mascota_id
JOIN propietarios p ON p.propietario_id = m.propietario_id
WHERE v.fecha_proxima < DATE '2026-10-01'
AND NOT EXISTS (SELECT 1 FROM vacunaciones v2
WHERE v2.mascota_id = v.mascota_id
AND v2.vacuna = v.vacuna
AND v2.fecha > v.fecha)
ORDER BY dias_vencida DESC;
MASCOTA PROPIETARIO VACUNA FECHA FECHA_PROX DIAS_VENCIDA
-------- ---------------------- -------------------- ---------- ---------- ------------
Luna Laura Gómez Ferrer Rabia 14/01/2025 14/01/2026 260
Luna Laura Gómez Ferrer Polivalente canina 14/01/2025 14/01/2026 260
Michi Laura Gómez Ferrer Trivalente felina 03/02/2025 03/02/2026 240
Nala Sofía Navarro Ruiz Trivalente felina 08/04/2025 08/04/2026 176
Thor Daniel Herrera Vidal Polivalente canina 20/05/2025 20/05/2026 134
Thor Daniel Herrera Vidal Rabia 20/05/2025 20/05/2026 134
Coco Lucía Domínguez Pardo Mixomatosis 08/01/2026 08/07/2026 85
7 filas seleccionadas.
El NOT EXISTS descarta las dosis que ya tienen una posterior de la misma vacuna: la primera rabia de Luna (vencida en 2025) no sale porque se revacunó. Error típico: olvidar esa condición y avisar de vacunas ya puestas. Alternativa: ROW_NUMBER() OVER (PARTITION BY mascota_id, vacuna ORDER BY fecha DESC) = 1.
Consulta 14: Medicamentos sin uso ★
Ver solución
SELECT md.medicamento_id, md.nombre AS medicamento, md.laboratorio
FROM medicamentos md
WHERE NOT EXISTS (SELECT 1 FROM prescripciones p WHERE p.medicamento_id = md.medicamento_id);
MEDICAMENTO_ID MEDICAMENTO LABORATORIO
-------------- ---------------------- ----------------------------------------
4 Desparasitante Plus Animalia
1 fila seleccionada.
Consulta 15: Datos huérfanos ★★
Propietarios sin mascotas y mascotas sin consultas, en un único listado.
Ver solución
SELECT 'Propietario sin mascotas' AS situacion, p.nombre || ' ' || p.apellidos AS detalle
FROM propietarios p
WHERE NOT EXISTS (SELECT 1 FROM mascotas m WHERE m.propietario_id = p.propietario_id)
UNION ALL
SELECT 'Mascota sin consultas', m.nombre || ' (' || m.especie || ')'
FROM mascotas m
WHERE NOT EXISTS (SELECT 1 FROM consultas c WHERE c.mascota_id = m.mascota_id);
SITUACION DETALLE
------------------------ ------------------------
Propietario sin mascotas Raúl Ortega Prieto
1 fila seleccionada.
Todas las mascotas tienen al menos una consulta; la segunda parte no aporta filas, pero la consulta es correcta para cualquier estado de la base de datos. Las dos partes del UNION ALL deben tener el mismo número de columnas y tipos compatibles.
Consulta 16: Medicamento estrella de cada clínica ★★★
El medicamento con más unidades prescritas en cada clínica (con empates).
Ver solución
SELECT clinica, medicamento, unidades
FROM (SELECT cl.nombre AS clinica, md.nombre AS medicamento, SUM(p.unidades) AS unidades,
RANK() OVER (PARTITION BY cl.clinica_id ORDER BY SUM(p.unidades) DESC) AS rk
FROM prescripciones p
JOIN consultas c ON c.consulta_id = p.consulta_id
JOIN clinicas cl ON cl.clinica_id = c.clinica_id
JOIN medicamentos md ON md.medicamento_id = p.medicamento_id
GROUP BY cl.clinica_id, cl.nombre, md.nombre)
WHERE rk = 1
ORDER BY clinica, medicamento;
CLINICA MEDICAMENTO UNIDADES
------------------------ ---------------------- ----------
VetRed Madrid Chamberí Cardiovet 5 mg 6
VetRed Sevilla Triana Vitaminas Exóticos 2
VetRed Valencia Centro Amoxivet 250 mg 3
3 filas seleccionadas.
Agregado + analítica en la misma consulta: primero GROUP BY (unidades por clínica y medicamento) y después RANK dentro de cada clínica.
Consulta 17: Búsqueda textual ★★
Consultas relacionadas con problemas de piel (por diagnóstico o motivo), sin distinguir mayúsculas.
Ver solución
SELECT c.consulta_id, m.nombre AS mascota, c.diagnostico
FROM consultas c JOIN mascotas m ON m.mascota_id = c.mascota_id
WHERE UPPER(c.diagnostico) LIKE '%DERMAT%'
OR UPPER(c.motivo) LIKE '%PIEL%';
CONSULTA_ID MASCOTA DIAGNOSTICO
----------- -------- ----------------------------
2 Michi Dermatitis alérgica
1 fila seleccionada.
El control de Michi (consulta 15, motivo “Control dermatitis”, diagnóstico “Mejoría”) no sale porque el patrón %DERMAT% solo se aplica al diagnóstico. Ampliar la búsqueda al motivo con OR UPPER(c.motivo) LIKE '%DERMAT%' lo incluiría: leer el enunciado con cuidado es parte del ejercicio.
Consulta 18: Mascotas atendidas por varios veterinarios ★★★
Ver solución
SELECT m.nombre AS mascota,
COUNT(DISTINCT c.veterinario_id) AS veterinarios,
COUNT(DISTINCT c.clinica_id) AS clinicas,
COUNT(*) AS consultas
FROM consultas c JOIN mascotas m ON m.mascota_id = c.mascota_id
WHERE c.estado <> 'CANCELADA'
GROUP BY m.mascota_id, m.nombre
HAVING COUNT(DISTINCT c.veterinario_id) > 1
ORDER BY consultas DESC, mascota;
MASCOTA VETERINARIOS CLINICAS CONSULTAS
-------- ------------ ---------- ----------
Lola 2 1 2
Rocky 2 1 2
Simba 2 1 2
Thor 2 1 2
4 filas seleccionadas.
Consulta 19: Ranking de clientes por gasto (RF4) ★★★
Gasto total de cada propietario (consultas realizadas más medicamentos), con su puesto.
Ver solución
WITH gasto_consulta AS (
SELECT c.consulta_id, c.mascota_id,
c.importe + NVL((SELECT SUM(md.precio * p.unidades)
FROM prescripciones p JOIN medicamentos md ON md.medicamento_id = p.medicamento_id
WHERE p.consulta_id = c.consulta_id), 0) AS total
FROM consultas c
WHERE c.estado = 'REALIZADA'
),
gasto_propietario AS (
SELECT m.propietario_id, COUNT(DISTINCT g.mascota_id) AS mascotas,
COUNT(*) AS consultas, SUM(g.total) AS gastado
FROM gasto_consulta g JOIN mascotas m ON m.mascota_id = g.mascota_id
GROUP BY m.propietario_id
)
SELECT p.nombre || ' ' || p.apellidos AS propietario, gp.mascotas, gp.consultas, gp.gastado,
RANK() OVER (ORDER BY gp.gastado DESC) AS puesto
FROM gasto_propietario gp JOIN propietarios p ON p.propietario_id = gp.propietario_id
ORDER BY puesto;
PROPIETARIO MASCOTAS CONSULTAS GASTADO PUESTO
---------------------- ---------- ---------- ---------- ----------
Elena Castillo Mena 2 4 406,95 1
Laura Gómez Ferrer 2 4 263,35 2
Javier Martín Roca 2 3 182,7 3
Daniel Herrera Vidal 1 2 176,7 4
Sofía Navarro Ruiz 2 2 133,6 5
Lucía Domínguez Pardo 2 2 128,6 6
Alberto Sanz Moreno 1 1 30 7
7 filas seleccionadas.
Dos CTE encadenadas: primero el total por consulta (con una subconsulta escalar para no multiplicar filas), después el agregado por propietario.
Consulta 20: Facturación mensual acumulada de 2026 ★★★
Usando la vista v_facturacion_mensual, la facturación de la red por mes en 2026 con el acumulado.
Ver solución
SELECT TO_CHAR(mes, 'YYYY-MM') AS mes, SUM(consultas) AS consultas, SUM(importe_consultas) AS importe,
SUM(SUM(importe_consultas)) OVER (ORDER BY mes) AS acumulado
FROM v_facturacion_mensual
WHERE mes >= DATE '2026-01-01'
GROUP BY mes
ORDER BY mes;
MES CONSULTAS IMPORTE ACUMULADO
------- ---------- ---------- ----------
2026-01 1 70 70
2026-02 1 30 100
2026-03 1 45 145
2026-04 1 50 195
2026-05 1 30 225
2026-06 1 60 285
2026-07 1 35 320
2026-09 1 40 360
8 filas seleccionadas.
La vista ya agrupa por clínica y mes; aquí se vuelve a agrupar para sumar las clínicas. SUM(SUM(...)) OVER (...) aplica la ventana sobre el agregado. Agosto no aparece: su única consulta se canceló.
Consulta 21: Veterinarios que atienden perros y gatos (división) ★★★
Ver solución
SELECT t.nombre || ' ' || t.apellidos AS veterinario
FROM veterinarios v JOIN trabajadores t ON t.trabajador_id = v.trabajador_id
WHERE NOT EXISTS (SELECT 1
FROM (SELECT 'PERRO' AS especie FROM dual UNION ALL SELECT 'GATO' FROM dual) e
WHERE NOT EXISTS (SELECT 1
FROM consultas c JOIN mascotas m ON m.mascota_id = c.mascota_id
WHERE c.veterinario_id = v.trabajador_id
AND c.estado = 'REALIZADA'
AND m.especie = e.especie))
ORDER BY veterinario;
VETERINARIO
--------------------
Andrés Lozano Pita
Beatriz Ramos Cid
Óscar Varela Muñoz
3 filas seleccionadas.
El divisor es un conjunto constante construido con UNION ALL sobre DUAL (en 23ai también con VALUES ('PERRO'), ('GATO')). Carmen solo ha atendido perros y un hurón; Inés, animales exóticos.
Consulta 22: Frecuencia de visitas ★★★
Para las mascotas con más de una consulta realizada, el número medio de días entre visitas.
Ver solución
SELECT nombre AS mascota, COUNT(*) AS consultas,
ROUND(AVG(dias)) AS dias_medios_entre_visitas
FROM (SELECT m.nombre, m.mascota_id,
c.fecha_hora - LAG(c.fecha_hora) OVER (PARTITION BY c.mascota_id ORDER BY c.fecha_hora) AS dias
FROM consultas c JOIN mascotas m ON m.mascota_id = c.mascota_id
WHERE c.estado = 'REALIZADA')
GROUP BY mascota_id, nombre
HAVING COUNT(*) > 1
ORDER BY dias_medios_entre_visitas;
MASCOTA CONSULTAS DIAS_MEDIOS_ENTRE_VISITAS
-------- ---------- -------------------------
Thor 2 28
Lola 2 154
Simba 2 287
Luna 2 294
Rocky 2 371
Michi 2 456
6 filas seleccionadas.
AVG ignora el nulo de la primera visita de cada mascota (no tiene anterior), que es exactamente lo que queremos.
Consulta 23: Matriz clínica × especie (PIVOT) ★★★
Ver solución
SELECT *
FROM (SELECT cl.nombre AS clinica, m.especie
FROM consultas c
JOIN clinicas cl ON cl.clinica_id = c.clinica_id
JOIN mascotas m ON m.mascota_id = c.mascota_id
WHERE c.estado = 'REALIZADA')
PIVOT (COUNT(*) FOR especie IN ('PERRO' AS perros, 'GATO' AS gatos, 'CONEJO' AS conejos,
'AVE' AS aves, 'REPTIL' AS reptiles, 'OTRO' AS otros))
ORDER BY clinica;
CLINICA PERROS GATOS CONEJOS AVES REPTILES OTROS
------------------------ ---------- ---------- ---------- ---------- ---------- ----------
VetRed Madrid Chamberí 4 3 0 0 0 0
VetRed Sevilla Triana 0 0 1 1 1 0
VetRed Valencia Centro 5 2 0 0 0 1
3 filas seleccionadas.
Confirma la especialización de Sevilla en animales exóticos.
Consulta 24: Salarios frente a la media de su puesto ★★★
Ver solución
SELECT t.nombre || ' ' || t.apellidos AS trabajador, t.tipo, t.salario,
ROUND(AVG(t.salario) OVER (PARTITION BY t.tipo), 2) AS media_tipo,
t.salario - ROUND(AVG(t.salario) OVER (PARTITION BY t.tipo), 2) AS dif_media,
s.salario AS salario_supervisor
FROM trabajadores t LEFT JOIN trabajadores s ON s.trabajador_id = t.supervisor_id
ORDER BY t.tipo, t.salario DESC;
TRABAJADOR TIPO SALARIO MEDIA_TIPO DIF_MEDIA SALARIO_SUPERVISOR
---------------------- ----------- ---------- ---------- ---------- ------------------
Rubén Soria León AUXILIAR 1750 1700 50 3100
Paula Nieto Gallego AUXILIAR 1700 1700 0 3400
Hugo Pastor Rey AUXILIAR 1650 1700 -50 3000
Marta Esteve Gil RECEPCION 1500 1500 0 3800
Diego Morales Sanz RECEPCION 1500 1500 0 3400
Carmen Ibáñez Soler VETERINARIO 3800 3240 560 (nulo)
Beatriz Ramos Cid VETERINARIO 3400 3240 160 3800
Andrés Lozano Pita VETERINARIO 3100 3240 -140 3800
Inés Calvo Durán VETERINARIO 3000 3240 -240 3800
Óscar Varela Muñoz VETERINARIO 2900 3240 -340 3400
10 filas seleccionadas.
Esta consulta usa datos sensibles (salarios): en la sección 9 solo la gerencia podrá ejecutarla.
Consulta 25: Cuadro de mando por clínica ★★★
Por clínica: mascotas distintas atendidas, propietarios, facturación, porcentaje sobre el total de la red y plantilla.
Ver solución
SELECT cl.nombre AS clinica,
COUNT(DISTINCT c.mascota_id) AS mascotas,
COUNT(DISTINCT m.propietario_id) AS propietarios,
SUM(c.importe) AS facturado,
ROUND(100 * RATIO_TO_REPORT(SUM(c.importe)) OVER (), 1) AS pct_facturacion,
(SELECT COUNT(*) FROM trabajadores t WHERE t.clinica_id = cl.clinica_id) AS plantilla
FROM clinicas cl
JOIN consultas c ON c.clinica_id = cl.clinica_id AND c.estado = 'REALIZADA'
JOIN mascotas m ON m.mascota_id = c.mascota_id
GROUP BY cl.clinica_id, cl.nombre
ORDER BY facturado DESC;
CLINICA MASCOTAS PROPIETARIOS FACTURADO PCT_FACTURACION PLANTILLA
------------------------ ---------- ------------ ---------- --------------- ----------
VetRed Madrid Chamberí 4 3 375 41 4
VetRed Valencia Centro 5 3 370 40,4 4
VetRed Sevilla Triana 3 2 170 18,6 2
3 filas seleccionadas.
Combina en una sola consulta agregados con DISTINCT, una analítica sobre el agregado (RATIO_TO_REPORT(...) OVER () sin partición: el total de la red) y una subconsulta escalar correlacionada. La plantilla se calcula con subconsulta porque un join con TRABAJADORES multiplicaría las filas de consultas.
8. Mantenimiento: operaciones DML y transacciones
Todas las operaciones de esta sección terminan con ROLLBACK para no alterar los datos de las consultas anteriores.
8.1. Alta de un cliente nuevo con su mascota y su primera cita
Una sola transacción: propietario (clave de la secuencia), teléfono (CURRVAL), mascota (identidad recuperada con RETURNING) y cita.
INSERT INTO propietarios (propietario_id, nif, nombre, apellidos, email, ciudad)
VALUES (seq_propietarios.NEXTVAL, '20000009J', 'Nuria', 'Blasco Tena', 'nuria.blasco@correo.es', 'Valencia');
INSERT INTO telefonos_propietario (propietario_id, telefono) VALUES (seq_propietarios.CURRVAL, '600999999');
VARIABLE v_mascota NUMBER
INSERT INTO mascotas (propietario_id, nombre, especie, raza, sexo, fecha_nacimiento, peso_kg)
VALUES (seq_propietarios.CURRVAL, 'Pipo', 'GATO', 'Persa', 'M', DATE '2026-05-01', 2.3)
RETURNING mascota_id INTO :v_mascota;
PRINT v_mascota
INSERT INTO consultas (mascota_id, veterinario_id, clinica_id, fecha_hora, motivo)
VALUES (:v_mascota, 2, 1, TO_DATE('2026-10-15 17:00', 'YYYY-MM-DD HH24:MI'), 'Primera visita y vacunación');
SELECT p.propietario_id, p.nombre AS propietario, m.mascota_id, m.nombre, c.consulta_id, c.estado
FROM propietarios p JOIN mascotas m ON m.propietario_id = p.propietario_id
JOIN consultas c ON c.mascota_id = m.mascota_id
WHERE p.nif = '20000009J';
ROLLBACK;
1 fila creada.
1 fila creada.
1 fila creada.
V_MASCOTA
----------
13
1 fila creada.
PROPIETARIO_ID PROPIETARIO MASCOTA_ID NOMBRE CONSULTA_ID ESTADO
-------------- ------------------------------ ---------- ------------ ----------- ----------
9 Nuria 13 Pipo 23 PROGRAMADA
1 fila seleccionada.
Rollback terminado.
El teléfono toma el tipo MOVIL por defecto y la consulta, el estado PROGRAMADA y el importe 0. Tras el ROLLBACK, los números 9 (secuencia), 13 (mascota) y 23 (consulta) quedan “gastados”: la siguiente alta recibirá el 10, el 14 y el 24.
8.2. Realizar una consulta programada, con savepoint
Al terminar la cita de Luna (consulta 20) se registra el diagnóstico y dos medicamentos; el segundo se introduce mal (0 unidades).
UPDATE consultas
SET estado = 'REALIZADA', diagnostico = 'Sano. Sarro leve', importe = 35
WHERE consulta_id = 20;
SAVEPOINT tras_consulta;
INSERT INTO prescripciones VALUES (20, 4, '1 comprimido', 1, 1);
INSERT INTO prescripciones VALUES (20, 3, '1 pipeta', 30, 0);
1 fila actualizada.
Punto de grabación creado.
1 fila creada.
INSERT INTO prescripciones VALUES (20, 3, '1 pipeta', 30, 0)
*
ERROR en línea 1:
ORA-02290: check constraint (CURSO.CK_PSC_UNIDADES) violated
Se vuelve al savepoint (se pierde la primera prescripción, pero no la actualización de la consulta) y se repiten las dos bien:
ROLLBACK TO tras_consulta;
INSERT INTO prescripciones VALUES (20, 4, '1 comprimido', 1, 1);
INSERT INTO prescripciones VALUES (20, 3, '1 pipeta', 30, 1);
SELECT c.consulta_id, c.estado, c.diagnostico, md.nombre AS medicamento, p.unidades
FROM consultas c JOIN prescripciones p ON p.consulta_id = c.consulta_id
JOIN medicamentos md ON md.medicamento_id = p.medicamento_id
WHERE c.consulta_id = 20;
ROLLBACK;
Rollback terminado.
1 fila creada.
1 fila creada.
CONSULTA_ID ESTADO DIAGNOSTICO MEDICAMENTO UNIDADES
----------- ---------- -------------------------- ---------------------- ----------
20 REALIZADA Sano. Sarro leve Desparasitante Plus 1
20 REALIZADA Sano. Sarro leve Antipulgas Spot-on 1
2 filas seleccionadas.
Rollback terminado.
En producción, en lugar del último ROLLBACK iría un COMMIT: la consulta y sus prescripciones se confirman juntas.
8.3. Las restricciones en acción
-- R4: una consulta realizada debe tener diagnóstico
UPDATE consultas SET estado = 'REALIZADA' WHERE consulta_id = 21;
-- R2: un auxiliar no puede ser también veterinario
INSERT INTO veterinarios (trabajador_id, num_colegiado) VALUES (6, 'V-46-9999');
-- R2: no se puede cambiar el tipo de un auxiliar con fila en AUXILIARES
UPDATE trabajadores SET tipo = 'RECEPCION' WHERE trabajador_id = 6;
-- R1: nadie se supervisa a sí mismo
UPDATE trabajadores SET supervisor_id = 4 WHERE trabajador_id = 4;
-- R6: el chip tiene 15 caracteres
INSERT INTO mascotas (propietario_id, nombre, especie, sexo, chip) VALUES (8, 'Toby', 'PERRO', 'M', '12345');
UPDATE consultas SET estado = 'REALIZADA' WHERE consulta_id = 21
*
ERROR en línea 1:
ORA-02290: check constraint (CURSO.CK_CON_DIAGNOSTICO) violated
INSERT INTO veterinarios (trabajador_id, num_colegiado) VALUES (6, 'V-46-9999')
*
ERROR en línea 1:
ORA-02291: integrity constraint (CURSO.FK_VET_TRABAJADOR) violated - parent key not found
UPDATE trabajadores SET tipo = 'RECEPCION' WHERE trabajador_id = 6
*
ERROR en línea 1:
ORA-02292: integrity constraint (CURSO.FK_AUX_TRABAJADOR) violated - child record found
UPDATE trabajadores SET supervisor_id = 4 WHERE trabajador_id = 4
*
ERROR en línea 1:
ORA-02290: check constraint (CURSO.CK_TRA_SUPERVISOR) violated
INSERT INTO mascotas (propietario_id, nombre, especie, sexo, chip) VALUES (8, 'Toby', 'PERRO', 'M', '12345')
*
ERROR en línea 1:
ORA-02290: check constraint (CURSO.CK_MAS_CHIP) violated
Los nombres de las restricciones, elegidos con cuidado, hacen que cada mensaje se entienda sin consultar el diccionario: es una de las razones para nombrarlas siempre.
8.4. Actualización de tarifas con MERGE
El laboratorio envía nuevos precios y un medicamento nuevo:
MERGE INTO medicamentos m
USING (SELECT 1 AS medicamento_id, 'Amoxivet 250 mg' AS nombre, 'Amoxicilina' AS principio, 'Vetfarma' AS lab, 13.10 AS precio, 'S' AS receta FROM dual UNION ALL
SELECT 6, 'Cardiovet 5 mg', 'Pimobendán', 'Vetfarma', 31.50, 'S' FROM dual UNION ALL
SELECT 9, 'Colirio Ocuvet', 'Tobramicina', 'DermaPet', 10.75, 'S' FROM dual) t
ON (m.medicamento_id = t.medicamento_id)
WHEN MATCHED THEN
UPDATE SET m.precio = t.precio WHERE m.precio <> t.precio
WHEN NOT MATCHED THEN
INSERT VALUES (t.medicamento_id, t.nombre, t.principio, t.lab, t.precio, t.receta);
SELECT medicamento_id, nombre AS medicamento, precio FROM medicamentos WHERE medicamento_id IN (1, 6, 9) ORDER BY 1;
ROLLBACK;
3 filas fusionadas.
MEDICAMENTO_ID MEDICAMENTO PRECIO
-------------- ---------------------- ----------
1 Amoxivet 250 mg 13,1
6 Cardiovet 5 mg 31,5
9 Colirio Ocuvet 10,75
3 filas seleccionadas.
Rollback terminado.
Como PRESCRIPCIONES no guarda el precio, este cambio alteraría el importe de las consultas pasadas calculado en las consultas 10 y 19. Es la limitación de diseño anotada en la sección 3.4 (ver ampliación 1).
8.5. Baja de una mascota
SELECT (SELECT COUNT(*) FROM consultas WHERE mascota_id = 9) AS consultas,
(SELECT COUNT(*) FROM vacunaciones WHERE mascota_id = 9) AS vacunas FROM dual;
DELETE FROM mascotas WHERE mascota_id = 9;
CONSULTAS VACUNAS
---------- ----------
1 3
1 fila seleccionada.
DELETE FROM mascotas WHERE mascota_id = 9
*
ERROR en línea 1:
ORA-02292: integrity constraint (CURSO.FK_CON_MASCOTA) violated - child record found
Las consultas protegen el historial (NO ACTION). Si la clínica decide de verdad eliminar el historial de Bimba, primero borra sus consultas (las prescripciones caerían en cascada) y luego la mascota, cuyas vacunaciones desaparecen en cascada:
DELETE FROM consultas WHERE mascota_id = 9;
DELETE FROM mascotas WHERE mascota_id = 9;
SELECT COUNT(*) AS vacunas_bimba FROM vacunaciones WHERE mascota_id = 9;
ROLLBACK;
1 fila suprimida.
1 fila suprimida.
VACUNAS_BIMBA
-------------
0
1 fila seleccionada.
Rollback terminado.
En un sistema real lo habitual es no borrar nunca: se añadiría una columna fecha_baja (ampliación 4).
8.6. Subida salarial por actividad
Un 3 % más a los veterinarios con al menos 4 consultas realizadas:
SELECT v.trabajador_id, COUNT(*) FROM veterinarios v JOIN consultas c ON c.veterinario_id = v.trabajador_id
WHERE c.estado = 'REALIZADA' GROUP BY v.trabajador_id HAVING COUNT(*) >= 4 ORDER BY 1;
UPDATE trabajadores t
SET t.salario = ROUND(t.salario * 1.03, 2)
WHERE t.trabajador_id IN (SELECT c.veterinario_id
FROM consultas c
WHERE c.estado = 'REALIZADA'
GROUP BY c.veterinario_id
HAVING COUNT(*) >= 4);
SELECT trabajador_id, nombre, salario FROM trabajadores WHERE trabajador_id IN (1, 2, 3) ORDER BY 1;
ROLLBACK;
TRABAJADOR_ID COUNT(*)
------------- ----------
1 4
2 4
3 4
3 filas seleccionadas.
3 filas actualizadas.
TRABAJADOR_ID NOMBRE SALARIO
------------- ------------ ----------
1 Carmen 3914
2 Andrés 3193
3 Beatriz 3502
3 filas seleccionadas.
Rollback terminado.
8.7. Cancelar las citas de un veterinario de baja
Beatriz (3) estará de baja del 12 al 19 de octubre:
UPDATE consultas
SET estado = 'CANCELADA'
WHERE veterinario_id = 3 AND estado = 'PROGRAMADA'
AND fecha_hora BETWEEN DATE '2026-10-12' AND DATE '2026-10-19';
SELECT consulta_id, estado FROM consultas WHERE consulta_id >= 20 ORDER BY 1;
ROLLBACK;
1 fila actualizada.
CONSULTA_ID ESTADO
----------- ----------
20 PROGRAMADA
21 PROGRAMADA
22 CANCELADA
3 filas seleccionadas.
Rollback terminado.
⚠️ Importante:
BETWEEN DATE '2026-10-12' AND DATE '2026-10-19'incluye el 19 a las 00:00, pero no una cita del 19 a las 10:00. Para incluir el día completo:fecha_hora >= DATE '2026-10-12' AND fecha_hora < DATE '2026-10-20'.
8.8. Concurrencia: dos recepcionistas y la misma cita
Si dos recepcionistas intentan asignar a la vez el último hueco de un veterinario, el patrón “consultar si está libre y luego insertar” falla en concurrencia (módulo 35, ejercicio 6). Las soluciones son:
- Declarar
UNIQUE (veterinario_id, fecha_hora)(ampliación 2): la segunda inserción esperará a la primera y fallará conORA-00001si aquella confirma. - Bloquear la fila del veterinario con
SELECT ... FROM veterinarios WHERE trabajador_id = :v FOR UPDATEantes de comprobar la agenda, de modo que las altas de citas de un mismo veterinario se serialicen. - Mantener las transacciones de alta de cita cortas: el recepcionista elige el hueco sin transacción abierta y la inserción se hace en una sola transacción breve.
9. Diseño de la seguridad (DCL)
9.1. Matriz de permisos
A partir de los requisitos RS1 a RS4 se define una matriz objeto × rol (S = SELECT, I = INSERT, U = UPDATE, D = DELETE):
| Objeto | Recepción | Clínico (veterinarios y auxiliares) | Gerencia |
|---|---|---|---|
| CLINICAS | S | S | S, I, U |
| TRABAJADORES | — (solo vista sin salario) | — (solo vista sin salario) | S, I, U |
| VETERINARIOS, AUXILIARES | S | S | S, I, U |
| PROPIETARIOS, TELEFONOS_PROPIETARIO | S, I, U | S | S |
| MASCOTAS | S, I, U | S, U (peso) | S |
| CONSULTAS | I (citas), U (fecha_hora, estado) mediante v_agenda | S, I, U | S |
| PRESCRIPCIONES, VACUNACIONES | — | S, I, U, D | S |
| MEDICAMENTOS | S | S | S, I, U |
v_agenda | S | S | S |
v_facturacion_mensual | — | — | S |
Dos ideas de diseño:
- Seguridad mediante vistas: recepción no tiene permiso sobre la tabla CONSULTAS completa (vería los diagnósticos), sino sobre la vista
v_agenda, que no los muestra. Del mismo modo, una vistav_personalsin la columnasalariosirve a recepción y personal clínico. - Privilegios de columna:
GRANT UPDATE (estado, fecha_hora) ON consultaslimita qué puede cambiar cada rol.
9.2. Script DCL
⚠️ Importante: Este script no se ha ejecutado en la base de datos del curso: crear usuarios, roles y perfiles exige privilegios de administrador (
CREATE USER,CREATE ROLE,CREATE PROFILE) que el usuario de prácticas no tiene. Ejecútalo conectado comoSYSTEM(o un usuario con esos privilegios) en tu propia instalación, después de crear el esquema propietarioVETREDcon los scripts de las secciones 5 y 6. Repasa la sintaxis en el módulo 37.
-- =====================================================================
-- VETRED - Seguridad (ejecutar como administrador)
-- =====================================================================
-- 1) Perfil de contraseñas y recursos para el personal (RS4)
CREATE PROFILE perfil_vetred LIMIT
FAILED_LOGIN_ATTEMPTS 5 -- bloqueo tras 5 intentos fallidos
PASSWORD_LOCK_TIME 1 -- durante 1 día
PASSWORD_LIFE_TIME 90 -- la contraseña caduca a los 90 días
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_MAX 5 -- no repetir las 5 últimas
PASSWORD_REUSE_TIME 365
SESSIONS_PER_USER 2
IDLE_TIME 30; -- minutos de inactividad
-- 2) Esquema propietario de los objetos (nadie trabaja conectado con él)
CREATE USER vetred IDENTIFIED BY "Cambiar_2026" DEFAULT TABLESPACE users QUOTA 200M ON users;
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE TO vetred;
-- ... aquí se ejecutarían vetred_crear.sql y vetred_datos.sql conectado como VETRED ...
ALTER USER vetred ACCOUNT LOCK; -- el propietario no inicia sesión en el día a día
-- 3) Vista sin datos sensibles para el personal no directivo (creada por VETRED)
-- CREATE VIEW vetred.v_personal AS
-- SELECT trabajador_id, nombre, apellidos, email, tipo, clinica_id, supervisor_id
-- FROM vetred.trabajadores;
-- 4) Roles funcionales
CREATE ROLE rol_vetred_recepcion;
CREATE ROLE rol_vetred_clinico;
CREATE ROLE rol_vetred_gerencia;
-- 4.1) Recepción (RS1): clientes, mascotas y citas; sin diagnósticos ni salarios
GRANT SELECT ON vetred.clinicas TO rol_vetred_recepcion;
GRANT SELECT ON vetred.veterinarios TO rol_vetred_recepcion;
GRANT SELECT ON vetred.v_personal TO rol_vetred_recepcion;
GRANT SELECT, INSERT, UPDATE ON vetred.propietarios TO rol_vetred_recepcion;
GRANT SELECT, INSERT, UPDATE, DELETE ON vetred.telefonos_propietario TO rol_vetred_recepcion;
GRANT SELECT, INSERT, UPDATE ON vetred.mascotas TO rol_vetred_recepcion;
GRANT SELECT ON vetred.medicamentos TO rol_vetred_recepcion;
GRANT SELECT ON vetred.v_agenda TO rol_vetred_recepcion;
GRANT INSERT ON vetred.consultas TO rol_vetred_recepcion;
GRANT UPDATE (fecha_hora, estado) ON vetred.consultas TO rol_vetred_recepcion;
GRANT SELECT ON vetred.seq_propietarios TO rol_vetred_recepcion;
-- 4.2) Personal clínico (RS2): todo el historial; registra consultas, prescripciones y vacunas
GRANT SELECT ON vetred.clinicas TO rol_vetred_clinico;
GRANT SELECT ON vetred.v_personal TO rol_vetred_clinico;
GRANT SELECT ON vetred.veterinarios TO rol_vetred_clinico;
GRANT SELECT ON vetred.auxiliares TO rol_vetred_clinico;
GRANT SELECT ON vetred.propietarios TO rol_vetred_clinico;
GRANT SELECT ON vetred.telefonos_propietario TO rol_vetred_clinico;
GRANT SELECT, UPDATE (peso_kg) ON vetred.mascotas TO rol_vetred_clinico;
GRANT SELECT, INSERT, UPDATE ON vetred.consultas TO rol_vetred_clinico;
GRANT SELECT, INSERT, UPDATE, DELETE ON vetred.prescripciones TO rol_vetred_clinico;
GRANT SELECT, INSERT, UPDATE, DELETE ON vetred.vacunaciones TO rol_vetred_clinico;
GRANT SELECT ON vetred.medicamentos TO rol_vetred_clinico;
GRANT SELECT ON vetred.v_agenda TO rol_vetred_clinico;
-- 4.3) Gerencia (RS3): lectura total, facturación, mantenimiento de catálogos y salarios
GRANT rol_vetred_clinico TO rol_vetred_gerencia; -- hereda la lectura clínica
GRANT SELECT, INSERT, UPDATE ON vetred.trabajadores TO rol_vetred_gerencia;
GRANT SELECT, INSERT, UPDATE ON vetred.veterinarios TO rol_vetred_gerencia;
GRANT SELECT, INSERT, UPDATE ON vetred.auxiliares TO rol_vetred_gerencia;
GRANT SELECT, INSERT, UPDATE ON vetred.clinicas TO rol_vetred_gerencia;
GRANT SELECT, INSERT, UPDATE ON vetred.medicamentos TO rol_vetred_gerencia;
GRANT SELECT ON vetred.v_facturacion_mensual TO rol_vetred_gerencia;
-- Alternativa desde Oracle 23ai (privilegio de esquema): lectura de todo lo presente y futuro
-- GRANT SELECT ANY TABLE ON SCHEMA vetred TO rol_vetred_gerencia;
-- 5) Usuarios nominales, uno por trabajador (RS4)
CREATE USER c_ibanez IDENTIFIED BY "Temporal_01" PROFILE perfil_vetred PASSWORD EXPIRE;
CREATE USER a_lozano IDENTIFIED BY "Temporal_02" PROFILE perfil_vetred PASSWORD EXPIRE;
CREATE USER r_soria IDENTIFIED BY "Temporal_06" PROFILE perfil_vetred PASSWORD EXPIRE;
CREATE USER m_esteve IDENTIFIED BY "Temporal_09" PROFILE perfil_vetred PASSWORD EXPIRE;
-- ... (uno por cada trabajador)
GRANT CREATE SESSION TO c_ibanez, a_lozano, r_soria, m_esteve;
GRANT rol_vetred_gerencia TO c_ibanez; -- directora técnica
GRANT rol_vetred_clinico TO a_lozano, r_soria;
GRANT rol_vetred_recepcion TO m_esteve;
-- 6) Sinónimos públicos para no escribir el prefijo del esquema (opcional)
CREATE PUBLIC SYNONYM mascotas FOR vetred.mascotas;
CREATE PUBLIC SYNONYM v_agenda FOR vetred.v_agenda;
-- 7) Ejemplos de mantenimiento
REVOKE rol_vetred_clinico FROM r_soria; -- cambio de puesto
GRANT rol_vetred_recepcion TO r_soria;
ALTER USER m_esteve ACCOUNT LOCK; -- baja temporal
💡 Consejo:
PASSWORD EXPIREobliga a cada trabajador a cambiar la contraseña en su primer acceso. Nunca se concede nada aPUBLICni se conectan las aplicaciones con el usuario propietarioVETRED: es el principio de mínimo privilegio.
9.3. Comprobaciones previstas tras ejecutarlo
Con el script ejecutado por un administrador, estas consultas del diccionario (módulo 37) permiten auditar el resultado: DBA_ROLE_PRIVS (roles de cada usuario), DBA_TAB_PRIVS y DBA_COL_PRIVS (privilegios de objeto y de columna de cada rol) y DBA_USERS (estado de las cuentas y perfil). Un usuario cualquiera puede ver sus propios permisos en USER_ROLE_PRIVS, ROLE_TAB_PRIVS y SESSION_PRIVS.
10. Documentación: diccionario de datos generado
En lugar de mantener a mano un documento que se desactualiza, se genera la documentación consultando el diccionario de datos de Oracle (módulo 26). Estas consultas se ejecutaron con el esquema ya creado y cargado.
10.1. Descripción de las tablas
SELECT table_name, comments
FROM user_tab_comments
WHERE table_name IN ('CLINICAS','TRABAJADORES','VETERINARIOS','AUXILIARES','PROPIETARIOS',
'TELEFONOS_PROPIETARIO','MASCOTAS','CONSULTAS','MEDICAMENTOS','PRESCRIPCIONES','VACUNACIONES')
ORDER BY table_name;
TABLE_NAME COMMENTS
---------------------- ----------------------------------------------------------------------
AUXILIARES Subtipo de TRABAJADORES: auxiliares técnicos veterinarios
CLINICAS Clínicas de la red VetRed
CONSULTAS Consultas veterinarias programadas, realizadas o canceladas
MASCOTAS Animales atendidos en la red
MEDICAMENTOS Catálogo de medicamentos
PRESCRIPCIONES Medicamentos prescritos en cada consulta (N:M con atributos)
PROPIETARIOS Clientes propietarios de mascotas
TELEFONOS_PROPIETARIO Atributo multivaluado teléfono de PROPIETARIOS
TRABAJADORES Personal de las clínicas (supertipo de VETERINARIOS y AUXILIARES)
VACUNACIONES Dosis de vacunas de cada mascota (entidad débil de MASCOTAS)
VETERINARIOS Subtipo de TRABAJADORES: veterinarios colegiados
11 filas seleccionadas.
10.2. Columnas documentadas
SELECT table_name, column_name, comments
FROM user_col_comments
WHERE comments IS NOT NULL
AND table_name IN ('TRABAJADORES','MASCOTAS','CONSULTAS','VACUNACIONES')
ORDER BY table_name, column_name;
TABLE_NAME COLUMN_NAME COMMENTS
---------------------- ---------------- ----------------------------------------------------------------------
CONSULTAS IMPORTE Importe de la consulta sin medicamentos, en euros
MASCOTAS CHIP Microchip de identificación (15 dígitos), opcional y único
TRABAJADORES SUPERVISOR_ID Trabajador que supervisa a este (relación reflexiva)
TRABAJADORES TIPO Discriminador de la jerarquía: VETERINARIO, AUXILIAR o RECEPCION
VACUNACIONES FECHA_PROXIMA Fecha recomendada para la siguiente dosis
VACUNACIONES NUM_DOSIS Número de dosis dentro de la mascota (identificador parcial)
6 filas seleccionadas.
10.3. Ficha de columnas de una tabla
SELECT c.table_name AS tabla,
c.column_name, c.data_type,
CASE WHEN c.data_type IN ('VARCHAR2', 'CHAR') THEN TO_CHAR(c.char_length)
WHEN c.data_precision IS NOT NULL THEN c.data_precision || NVL2(NULLIF(c.data_scale, 0), ',' || c.data_scale, '')
END AS tamano,
c.nullable
FROM user_tab_columns c
WHERE c.table_name = 'MASCOTAS'
ORDER BY c.column_id;
TABLA COLUMN_NAME DATA_TYPE TAMANO N
---------- ---------------- ---------- ------ -
MASCOTAS MASCOTA_ID NUMBER 6 N
MASCOTAS PROPIETARIO_ID NUMBER 6 N
MASCOTAS NOMBRE VARCHAR2 30 N
MASCOTAS ESPECIE VARCHAR2 10 N
MASCOTAS RAZA VARCHAR2 40 Y
MASCOTAS SEXO CHAR 1 N
MASCOTAS FECHA_NACIMIENTO DATE (nulo) Y
MASCOTAS CHIP VARCHAR2 15 Y
MASCOTAS PESO_KG NUMBER 5,2 Y
9 filas seleccionadas.
10.4. Resumen de restricciones por tabla
SELECT table_name,
COUNT(CASE WHEN constraint_type = 'P' THEN 1 END) AS pk,
COUNT(CASE WHEN constraint_type = 'U' THEN 1 END) AS uk,
COUNT(CASE WHEN constraint_type = 'R' THEN 1 END) AS fk,
COUNT(CASE WHEN constraint_type = 'C' THEN 1 END) AS ck_nn
FROM user_constraints
WHERE table_name IN ('CLINICAS','TRABAJADORES','VETERINARIOS','AUXILIARES','PROPIETARIOS',
'TELEFONOS_PROPIETARIO','MASCOTAS','CONSULTAS','MEDICAMENTOS','PRESCRIPCIONES','VACUNACIONES')
GROUP BY table_name
ORDER BY table_name;
TABLE_NAME PK UK FK CK_NN
---------------------- ---------- ---------- ---------- ----------
AUXILIARES 1 0 1 3
CLINICAS 1 1 0 5
CONSULTAS 1 0 3 11
MASCOTAS 1 1 1 9
MEDICAMENTOS 1 1 0 6
PRESCRIPCIONES 1 0 2 5
PROPIETARIOS 1 2 0 4
TELEFONOS_PROPIETARIO 1 0 1 2
TRABAJADORES 1 3 2 11
VACUNACIONES 1 0 2 4
VETERINARIOS 1 1 1 3
11 filas seleccionadas.
10.5. Claves ajenas y reglas de borrado
SELECT c.table_name AS tabla,
LISTAGG(cc.column_name, ', ') WITHIN GROUP (ORDER BY cc.position) AS columnas,
r.table_name AS referencia, c.delete_rule
FROM user_constraints c
JOIN user_cons_columns cc ON cc.constraint_name = c.constraint_name
JOIN user_constraints r ON r.constraint_name = c.r_constraint_name
WHERE c.constraint_type = 'R'
AND c.table_name IN ('CLINICAS','TRABAJADORES','VETERINARIOS','AUXILIARES','PROPIETARIOS',
'TELEFONOS_PROPIETARIO','MASCOTAS','CONSULTAS','MEDICAMENTOS','PRESCRIPCIONES','VACUNACIONES')
GROUP BY c.table_name, c.constraint_name, r.table_name, c.delete_rule
ORDER BY c.table_name, columnas;
TABLA COLUMNAS REFERENCIA DELETE_RU
---------------------- ---------------------------- ---------------------- ---------
AUXILIARES TRABAJADOR_ID, TIPO TRABAJADORES CASCADE
CONSULTAS CLINICA_ID CLINICAS NO ACTION
CONSULTAS MASCOTA_ID MASCOTAS NO ACTION
CONSULTAS VETERINARIO_ID VETERINARIOS NO ACTION
MASCOTAS PROPIETARIO_ID PROPIETARIOS NO ACTION
PRESCRIPCIONES CONSULTA_ID CONSULTAS CASCADE
PRESCRIPCIONES MEDICAMENTO_ID MEDICAMENTOS NO ACTION
TELEFONOS_PROPIETARIO PROPIETARIO_ID PROPIETARIOS CASCADE
...
13 filas seleccionadas.
Este listado es, literalmente, el grafo relacional de la sección 3.3 obtenido de la propia base de datos: es la mejor forma de comprobar que la implementación coincide con el diseño.
10.6. Índices
SELECT i.table_name, i.index_name, i.uniqueness,
LISTAGG(ic.column_name, ', ') WITHIN GROUP (ORDER BY ic.column_position) AS columnas
FROM user_indexes i JOIN user_ind_columns ic ON ic.index_name = i.index_name
WHERE i.table_name IN ('CONSULTAS','MASCOTAS','PROPIETARIOS')
GROUP BY i.table_name, i.index_name, i.uniqueness
ORDER BY i.table_name, i.index_name;
SELECT index_name, column_expression AS expresion FROM user_ind_expressions WHERE index_name = 'IX_PRP_APELLIDOS';
TABLE_NAME INDEX_NAME UNIQUENES COLUMNAS
---------------------- -------------------------- --------- ----------------------------
CONSULTAS IX_CON_CLINICA_FEC NONUNIQUE CLINICA_ID, FECHA_HORA
CONSULTAS IX_CON_MASCOTA NONUNIQUE MASCOTA_ID
CONSULTAS IX_CON_VETERINARIO NONUNIQUE VETERINARIO_ID
CONSULTAS PK_CONSULTAS UNIQUE CONSULTA_ID
MASCOTAS IX_MAS_PROPIETARIO NONUNIQUE PROPIETARIO_ID
MASCOTAS PK_MASCOTAS UNIQUE MASCOTA_ID
MASCOTAS UQ_MAS_CHIP UNIQUE CHIP
PROPIETARIOS IX_PRP_APELLIDOS NONUNIQUE SYS_NC00008$
PROPIETARIOS PK_PROPIETARIOS UNIQUE PROPIETARIO_ID
PROPIETARIOS UQ_PRP_EMAIL UNIQUE EMAIL
PROPIETARIOS UQ_PRP_NIF UNIQUE NIF
11 filas seleccionadas.
INDEX_NAME EXPRESION
-------------------------- ------------------------------
IX_PRP_APELLIDOS UPPER("APELLIDOS")
1 fila seleccionada.
El índice basado en función aparece sobre una columna virtual oculta (SYS_NC00008$); su expresión real se consulta en USER_IND_EXPRESSIONS.
10.7. Otros objetos
SELECT view_name, text_length FROM user_views WHERE view_name LIKE 'V\_%' ESCAPE '\' ORDER BY view_name;
SELECT sequence_name, last_number, increment_by FROM user_sequences WHERE sequence_name = 'SEQ_PROPIETARIOS';
SELECT table_name, column_name, generation_type FROM user_tab_identity_cols ORDER BY table_name;
VIEW_NAME TEXT_LENGTH
---------------------- -----------
V_AGENDA 522
V_FACTURACION_MENSUAL 233
2 filas seleccionadas.
SEQUENCE_NAME LAST_NUMBER INCREMENT_BY
------------------ ----------- ------------
SEQ_PROPIETARIOS 10 1
1 fila seleccionada.
TABLE_NAME COLUMN_NAME GENERATION
---------------------- ---------------- ----------
CONSULTAS CONSULTA_ID BY DEFAULT
MASCOTAS MASCOTA_ID BY DEFAULT
2 filas seleccionadas.
La secuencia está en 10 y no en 9 porque el alta de la sección 8.1 consumió el 9 aunque se deshiciera (las secuencias no son transaccionales). En el LIKE se escapa el guion bajo, que sería un comodín.
💡 Consejo: Guarda estas consultas en un script
vetred_documentar.sqly ejecútalo conSPOOL vetred_diccionario.txtal final de cada cambio de esquema: tendrás una documentación siempre al día. SQL Developer ofrece además Data Modeler, que genera el diagrama a partir del esquema (ingeniería inversa).
11. Rúbrica de evaluación
Si este caso (u otro de dominio parecido) es tu proyecto de evaluación, esta es una rúbrica orientativa sobre 10 puntos:
| Criterio | Peso | Excelente (100 %) | Adecuado (60 %) | Insuficiente (20 %) |
|---|---|---|---|---|
| Requisitos | 0,5 | Completos, codificados, sin ambigüedades | Completos pero redactados de forma vaga | Incompletos o contradictorios |
| Diagrama E/R | 2 | Notación correcta; jerarquía, débil, reflexiva, N:M con atributos, multivaluado y derivado bien representados; cardinalidades justificadas | Algún error de cardinalidad o de notación | Elementos clave mal modelados (p. ej. N:M como 1:N) |
| Restricciones semánticas | 0,5 | Identificadas y con su forma de implementación | Identificadas sin implementación | No se documentan |
| Paso a relacional | 1,5 | Reglas aplicadas y alternativas de jerarquía discutidas; notación correcta | Correcto sin justificar | Pérdida de información o FK mal situadas |
| Normalización | 1 | DF por tabla y comprobación razonada hasta FNBC | Afirmación de 3FN sin razonar | Tablas no normalizadas |
| DDL | 1,5 | Tipos adecuados, restricciones con nombre, índices en FK, vistas, comentarios; se ejecuta sin errores | Faltan nombres o índices | No se ejecuta o faltan restricciones esenciales |
| Datos | 0,5 | Realistas y con casos límite | Suficientes pero triviales | Insuficientes para probar las consultas |
| Consultas | 1,5 | 25 consultas correctas con salida real, de dificultad creciente, comentadas | Correctas pero simples o sin comentar | Errores frecuentes con nulos, agrupación o joins |
| DML y transacciones | 0,5 | Operaciones atómicas, uso de savepoint y control de errores | DML correcto sin transacciones | DML que deja datos inconsistentes |
| Seguridad | 0,25 | Matriz de permisos, roles, vistas de seguridad, perfil | Roles sin justificar | Todo concedido a todos |
| Documentación | 0,25 | Diccionario generado y coherente con el diseño | Documentación manual | Inexistente |
⚠️ Importante: La rúbrica premia la justificación. Un diseño “correcto” sin explicar por qué se eligió frente a las alternativas no llega al nivel excelente.
12. Limpieza del esquema
Para eliminar el proyecto (orden inverso a las dependencias; CASCADE CONSTRAINTS evita problemas si el orden no fuera perfecto):
DROP VIEW v_agenda;
DROP VIEW v_facturacion_mensual;
DROP TABLE vacunaciones CASCADE CONSTRAINTS PURGE;
DROP TABLE prescripciones CASCADE CONSTRAINTS PURGE;
DROP TABLE medicamentos CASCADE CONSTRAINTS PURGE;
DROP TABLE consultas CASCADE CONSTRAINTS PURGE;
DROP TABLE mascotas CASCADE CONSTRAINTS PURGE;
DROP TABLE telefonos_propietario CASCADE CONSTRAINTS PURGE;
DROP TABLE propietarios CASCADE CONSTRAINTS PURGE;
DROP TABLE auxiliares CASCADE CONSTRAINTS PURGE;
DROP TABLE veterinarios CASCADE CONSTRAINTS PURGE;
DROP TABLE trabajadores CASCADE CONSTRAINTS PURGE;
DROP TABLE clinicas CASCADE CONSTRAINTS PURGE;
DROP SEQUENCE seq_propietarios;
SELECT object_type, COUNT(*) AS objetos FROM user_objects GROUP BY object_type ORDER BY object_type;
Vista borrada.
Vista borrada.
Tabla borrada.
...
Tabla borrada.
Secuencia borrada.
OBJECT_TYPE OBJETOS
----------------------- ----------
INDEX 14
TABLE 9
2 filas seleccionadas.
Tras la limpieza solo quedan las 9 tablas de EMPRESA y sus 14 índices: los índices, comentarios, identidades y restricciones del proyecto desaparecen con sus tablas.
13. Ejercicios: propuestas de ampliación
Ejercicio 1: Precio histórico de los medicamentos
Añade a PRESCRIPCIONES la columna precio_unitario (como precio_unitario en LINEAS_PEDIDO de EMPRESA), rellénala para las prescripciones existentes con el precio actual y modifica las consultas 10 y 19 para usarla. Comprueba que, tras el MERGE de la sección 8.4, el gasto de los clientes ya no cambia.
Ejercicio 2: Agenda sin solapamientos
Impide que un veterinario tenga dos consultas a la misma hora con una restricción UNIQUE. Discute por qué no basta para evitar citas solapadas de 30 minutos (por ejemplo, 10:00 y 10:15) y qué alternativas existen (franjas horarias en una tabla HUECOS, o un trigger).
Ejercicio 3: Facturas
Modela las facturas: una factura agrupa varias consultas de un mismo propietario, tiene número correlativo por año (entidad débil de AÑO o clave compuesta), base imponible, IVA y total. Amplía el E/R, transforma, normaliza y escribe la consulta que genera las líneas de una factura.
Ejercicio 4: Bajas lógicas
Añade fecha_baja a MASCOTAS y TRABAJADORES. Reescribe la vista v_agenda y las consultas 1, 5 y 8 para que ignoren las bajas, y explica por qué es preferible a borrar filas.
Ejercicio 5: Auxiliares en consultas
Los auxiliares ayudan en las consultas (un auxiliar puede ayudar en muchas consultas y una consulta puede tener varios auxiliares, con una tarea: sedación, sujeción, extracción…). Añade la relación al E/R, crea la tabla con su FK compuesta hacia AUXILIARES y escribe una consulta con el número de consultas en que ha participado cada auxiliar.
Ejercicio 6: Tu propio proyecto
Repite el proceso completo (secciones 1 a 12) con un dominio nuevo de complejidad similar: un gimnasio con reservas de clases, una plataforma de cursos online o una red de talleres mecánicos. Exige al menos una jerarquía, una entidad débil, una relación reflexiva y una N:M con atributos. Evalúalo con la rúbrica de la sección 11.
Resumen
| Fase | Producto | Técnicas del curso aplicadas |
|---|---|---|
| Requisitos | Tablas RD, RF y RS | Análisis de requisitos (módulo 04) |
| Diseño conceptual | Tres diagramas E/R en notación de Chen | Entidades, atributos (multivaluado, compuesto, derivado), 1:N, N:M con atributos, reflexiva, débil, jerarquía parcial y exclusiva (05-11) |
| Restricciones | Tabla R1-R9 | Restricciones semánticas (09) |
| Diseño lógico | Esquema relacional y grafo | Reglas de transformación y opciones de jerarquía (12-13) |
| Normalización | DF por tabla y comprobación hasta FNBC | Dependencias funcionales y formas normales (15-16) |
| Implementación | vetred_crear.sql | CREATE TABLE, restricciones con nombre, identidad, secuencia, índices (también basados en función), vistas, comentarios (19-22) |
| Carga | vetred_datos.sql | INSERT, NEXTVAL, orden por dependencias (30) |
| Explotación | 25 consultas | Filtros, funciones, agregación, joins, subconsultas, CTE, división, analíticas, jerárquicas, PIVOT (23-29) |
| Mantenimiento | 8 operaciones | INSERT ... RETURNING, UPDATE, DELETE, MERGE, savepoints, restricciones en acción (30-31) |
| Seguridad | Matriz y script DCL | Perfiles, usuarios, roles, privilegios de objeto y de columna, vistas de seguridad (33) |
| Documentación | Diccionario generado | USER_TAB_COMMENTS, USER_CONSTRAINTS, USER_INDEXES, USER_TAB_IDENTITY_COLS… (22) |
¡Enhorabuena! Has completado el Curso de Bases de Datos: ya sabes llevar una base de datos desde la primera entrevista con el cliente hasta su puesta en marcha. El siguiente paso natural es programar dentro de la base de datos (triggers para las restricciones R3, R7 y R9, procedimientos para las operaciones de la sección 8) en el Curso de Oracle PL/SQL.