Bases de Datos

Bases de Datos

Parcial de práctica

Semanas 1–8, dos horas. Las soluciones las da el docente.

Parcial de práctica — Bases de Datos

Temas: partes 1 y 2 del curso (semanas 1 a 8) · Duración: 2 horas

Este es un examen de práctica con el formato del parcial. Sirve para medir cuánto tiempo les toma cada tipo de pregunta: resuélvanlo en la computadora, con el contenedor del curso, sin internet y con el reloj corriendo. El formato general está en exams/README.md.

Indicaciones

  • En SQL importa la idea: un error menor de sintaxis no la invalida.
  • En las preguntas de “qué devuelve”, justifiquen cada respuesta: un resultado sin explicación no basta.
  • El esquema es el de PostgreSQL 18. Las consultas de las partes A y B se responden sobre los datos de abajo, salvo que la pregunta diga otra cosa.
  • En el álgebra relacional usen la notación del curso: σ, Π, ρ, ⋈, ×, ∪, −, ∩, ÷, γ y ←.
Parte Preguntas
A. Escribir consultas 1–4
B. Leer consultas y código 5–6
C. Diseño 7–8
D. Preguntas cortas 9–14

El esquema: una red de consultorios

Una red de consultorios médicos de Lima registra a sus médicos, sus pacientes y las citas. Una cita atendida (done) puede tener el pago fee en NULL si todavía no se registró; una cita cancelada no se cobra. insurer es el seguro del paciente, NULL si no tiene.

CREATE TABLE specialty (
  specialty_id integer PRIMARY KEY,
  name         text NOT NULL UNIQUE
);

CREATE TABLE doctor (
  doctor_id    integer PRIMARY KEY,
  name         text NOT NULL,
  specialty_id integer NOT NULL REFERENCES specialty
);

CREATE TABLE patient (
  patient_id integer PRIMARY KEY,
  dni        char(8) NOT NULL UNIQUE,
  name       text NOT NULL,
  district   text NOT NULL,
  insurer    text
);

CREATE TABLE appointment (
  appointment_id integer PRIMARY KEY,
  patient_id     integer NOT NULL REFERENCES patient,
  doctor_id      integer NOT NULL REFERENCES doctor,
  day            date NOT NULL,
  fee            numeric(7, 2) CHECK (fee >= 0),
  status         text NOT NULL CHECK (status IN ('scheduled', 'done', 'cancelled'))
);

Los datos (∅ es NULL):

 specialty_id |     name
--------------+--------------
            1 | Cardiología
            2 | Pediatría
            3 | Dermatología
            4 | Neurología

 doctor_id |  name   | specialty_id
-----------+---------+--------------
        10 | Rojas   |            1
        11 | Quispe  |            1
        12 | Salazar |            2
        13 | Huamán  |            3
        14 | Vargas  |            2

 patient_id |   dni    | name  |  district  | insurer
------------+----------+-------+------------+---------
          1 | 40112233 | Ana   | Surco      | EsSalud
          2 | 41223344 | Bruno | Miraflores | ∅
          3 | 42334455 | Carla | Surco      | SIS
          4 | 43445566 | Diego | Lince      | EsSalud
          5 | 44556677 | Elena | Lince      | ∅

 appointment_id | patient_id | doctor_id |    day     |  fee   |  status
----------------+------------+-----------+------------+--------+-----------
            100 |          1 |        10 | 2026-03-02 | 120.00 | done
            101 |          1 |        11 | 2026-03-09 | 150.00 | done
            102 |          2 |        10 | 2026-03-02 | 120.00 | done
            103 |          2 |        12 | 2026-03-05 |      ∅ | cancelled
            104 |          3 |        11 | 2026-03-10 | 150.00 | done
            105 |          3 |        10 | 2026-03-11 |      ∅ | done
            106 |          4 |        13 | 2026-03-12 |  90.00 | done
            107 |          1 |        12 | 2026-03-15 |  80.00 | done
            108 |          4 |        12 | 2026-03-16 |      ∅ | cancelled
            109 |          2 |        10 | 2026-03-20 | 120.00 | done

Parte A — Escribir consultas

Para cada consulta, además de escribirla, digan qué filas devuelve con los datos de arriba.

Pregunta 1 — Joins y diferencia

a) Los nombres de los pacientes que tuvieron al menos una cita atendida (done) con un médico de Pediatría. Escríbanla en álgebra relacional y en SQL.

b) Los nombres de los pacientes que nunca tuvieron una cita, en ninguno de sus estados. Escríbanla en álgebra relacional (con una diferencia) y en SQL. Un compañero escribe SELECT name FROM patient EXCEPT SELECT p.name FROM patient p JOIN appointment a ON a.patient_id = p.patient_id. Con estos datos da la respuesta correcta. ¿Por qué igual está mal?

Pregunta 2 — “Para todos”

Los pacientes que tuvieron una cita atendida con cada uno de los médicos de Cardiología.

a) En álgebra relacional, con la división ÷. Digan el esquema de cada lado de la división.

b) En SQL, con NOT EXISTS (la doble negación).

c) Si en su consulta de (b) cambian 'Cardiología' por 'Neurología', que no tiene médicos, ¿qué devuelve? ¿Es un error de la consulta o es lo que dice la lógica?

Pregunta 3 — Agregación

a) Para cada especialidad, su nombre, el número de citas atendidas de sus médicos y lo recaudado por esas citas. Las especialidades sin citas atendidas aparecen con 0 en las dos columnas (no con NULL).

b) Las especialidades cuyo pago promedio por cita atendida es mayor que el pago promedio de todas las citas atendidas. ¿Qué hace avg con la cita 105?

Pregunta 4 — Window functions

a) Para cada médico, su última cita atendida: el nombre del médico, el día y el nombre del paciente. Si un médico tuvo dos citas atendidas en su último día, salen las dos. Usen una window function. ¿Qué función de ranking eligen y por qué no otra?

b) Una compañera lo resuelve sin window functions:

SELECT d.name AS doctor, a.day, p.name AS patient
FROM appointment a
  JOIN doctor d ON d.doctor_id = a.doctor_id
  JOIN patient p ON p.patient_id = a.patient_id
WHERE a.status = 'done'
  AND a.day = (SELECT max(a2.day) FROM appointment a2 WHERE a2.doctor_id = a.doctor_id)
ORDER BY doctor;

¿Qué devuelve? ¿Qué médico falta y por qué? Corríjanla.

Parte B — Leer consultas y código

Pregunta 5 — Qué devuelve

Para cada consulta, escriban el resultado completo (nombres de columna aparte) y expliquen cada valor en una o dos líneas.

a)

SELECT count(*), count(fee), count(DISTINCT fee), sum(fee), round(avg(fee), 2)
FROM appointment
WHERE status = 'done';

b) Las dos consultas, y por qué difieren:

SELECT name FROM patient
WHERE insurer NOT IN (SELECT insurer FROM patient WHERE district = 'Lince');

SELECT p.name FROM patient p
WHERE NOT EXISTS (SELECT 1 FROM patient q
                  WHERE q.district = 'Lince' AND q.insurer = p.insurer);

c) Las tres consultas:

-- (i)
SELECT d.name, count(a.appointment_id) AS n
FROM doctor d LEFT JOIN appointment a ON a.doctor_id = d.doctor_id
WHERE a.status = 'done'
GROUP BY d.name;

-- (ii)
SELECT d.name, count(a.appointment_id) AS n
FROM doctor d LEFT JOIN appointment a ON a.doctor_id = d.doctor_id AND a.status = 'done'
GROUP BY d.name;

-- (iii)
SELECT d.name, count(*) AS n
FROM doctor d LEFT JOIN appointment a ON a.doctor_id = d.doctor_id AND a.status = 'done'
GROUP BY d.name;

d) ¿Qué devuelve cada una? ¿Qué devuelve la de UNION sin el count(*) de afuera?

SELECT count(*) FROM (SELECT patient_id FROM appointment WHERE doctor_id = 10
                      UNION ALL
                      SELECT patient_id FROM appointment WHERE fee >= 150) AS t;

SELECT count(*) FROM (SELECT patient_id FROM appointment WHERE doctor_id = 10
                      UNION
                      SELECT patient_id FROM appointment WHERE fee >= 150) AS t;

e) ¿Cuántas filas devuelve cada una, y con qué valores?

SELECT count(*), sum(a.fee), max(a.day)
FROM appointment a JOIN doctor d ON d.doctor_id = a.doctor_id
WHERE d.specialty_id = 4;

SELECT d.specialty_id, count(*), sum(a.fee), max(a.day)
FROM appointment a JOIN doctor d ON d.doctor_id = a.doctor_id
WHERE d.specialty_id = 4
GROUP BY d.specialty_id;

f)

SELECT insurer, count(*) AS n
FROM patient
GROUP BY insurer;

Pregunta 6 — SQL desde Node.js

La web de los consultorios usa estas dos funciones, con un pg.Pool que se crea una vez al iniciar la aplicación:

// Pacientes de un distrito, para el buscador de la web.
export async function patientsIn(pool, district) {
  const { rows } = await pool.query(
    "SELECT name, insurer FROM patient WHERE district = '" + district + "' ORDER BY name",
  );
  return rows;
}

// Reprograma una cita: cancela la cita appointmentId y crea la cita newId
// con el mismo paciente y el mismo médico, para el día newDay.
export async function reschedule(pool, appointmentId, newId, newDay) {
  const client = await pool.connect();
  try {
    await pool.query('BEGIN');
    await client.query(
      "UPDATE appointment SET status = 'cancelled' WHERE appointment_id = $1",
      [appointmentId],
    );
    await client.query(
      `INSERT INTO appointment (appointment_id, patient_id, doctor_id, day, fee, status)
       SELECT $2, patient_id, doctor_id, $3, NULL, 'scheduled'
       FROM appointment WHERE appointment_id = $1`,
      [appointmentId, newId, newDay],
    );
    await pool.query('COMMIT');
    client.release();
  } catch (err) {
    await pool.query('ROLLBACK');
    throw err;
  }
}

a) Escriban un valor de district que haga que patientsIn devuelva a todos los pacientes, y otro que borre todas las citas. ¿Por qué el segundo funciona con pg en esta función? Corrijan la función.

b) Se llama reschedule(pool, 106, 100, '2026-03-30') por error: la cita 100 ya existe. ¿Qué sentencia falla y con qué error? ¿En qué estado queda la cita 106 después de la llamada? ¿Qué le pasa al pool? Hay dos errores distintos en la función: expliquen cada uno y escriban la función corregida.

Parte C — Diseño

Pregunta 7 — Modelo E-R

Un instituto de idiomas tiene varias sedes (código, nombre y distrito) y ofrece cursos (código, nombre como “Inglés Básico 1”, idioma y número de horas). Un curso puede tener como prerrequisito a otros cursos, y puede ser prerrequisito de otros.

Cada ciclo (por ejemplo, 2026-1) el instituto abre secciones de sus cursos. Una sección se identifica por su curso, el ciclo y un número (1, 2, 3, …) que solo distingue a las secciones del mismo curso en el mismo ciclo. Cada sección tiene un horario, se dicta en una sola sede y la dicta un solo profesor. De cada profesor se guarda su DNI, su nombre y sus correos (puede tener varios); un profesor puede dictar varias secciones o ninguna.

Los alumnos (código, nombre y fecha de nacimiento; la edad no se guarda, se calcula) se matriculan en secciones. De cada matrícula se guarda la fecha y la nota final, que no existe mientras el ciclo no termina.

a) Dibujen el diagrama E-R en la notación del libro: conjuntos de entidades con sus atributos (subrayen la clave primaria; marquen los atributos multivaluados y derivados), conjuntos de relaciones con sus cardinalidades y su participación, y la entidad débil con su relación identificadora y su discriminador. Nombren los roles donde haga falta.

b) Tradúzcanlo a tablas con CREATE TABLE, con todas las claves primarias y foráneas y los NOT NULL que se deducen del diagrama.

Pregunta 8 — Dependencias funcionales y normalización

Sea R = (A, B, C, D, E, G) con el conjunto de dependencias funcionales

F = { A → BC, CD → E, B → D, E → A }

a) Calculen A⁺ y B⁺, mostrando los pasos. ¿Está A → E en F⁺?

b) Encuentren todas las claves candidatas de R. Justifiquen por qué G está en todas y por qué cada una es mínima.

c) ¿Es F una cubierta canónica? Justifiquen probando al menos un atributo que podría ser extraño.

d) ¿Está R en BCNF? ¿Está en 3NF? Justifiquen con cada dependencia de F.

e) Descompongan R en BCNF con el algoritmo del curso, diciendo en cada paso qué dependencia viola BCNF. ¿Su descomposición preserva las dependencias? Si R ya estaba en 3NF, ¿qué ganan y qué pierden al pasarla a BCNF?

f) ¿Es sin pérdida la descomposición de R en (A, B, C, D) y (D, E, G)? Si no lo es, den una instancia de R de dos tuplas que cumpla F y muestren las tuplas que aparecen de más en el join.

Parte D — Preguntas cortas

Respondan cada una en tres o cuatro líneas.

Pregunta 9

¿Qué diferencia hay entre una superclave y una clave candidata? ¿Cuáles son las claves candidatas de patient? ¿Es {patient_id, name} una superclave? ¿Y una clave candidata?

Pregunta 10

¿Cuántas tuplas tiene Πpatient_id(σdoctor_id = 10(appointment))? ¿Cuántas filas devuelve SELECT patient_id FROM appointment WHERE doctor_id = 10? ¿Por qué SQL trabaja con multiconjuntos y no con conjuntos?

Pregunta 11

¿Qué devuelve esta consulta? Escriban la tabla de trabajo de cada iteración.

WITH RECURSIVE n(k) AS (
  SELECT 1
  UNION ALL
  SELECT k * 2 FROM n WHERE k < 20
)
SELECT count(*), max(k) FROM n;

Pregunta 12

¿Qué devuelve cada columna? ¿Cuál de las cuatro da un resultado que probablemente no es el que se quería, y cómo se corrige?

SELECT doc -> 'vitals' ->> 'bp'               AS a,
       doc ->> 'allergies'                    AS b,
       doc @> '{"allergies": ["penicilina"]}' AS c,
       doc -> 'vitals' ->> 'hr' > '100'       AS d
FROM (VALUES ('{"vitals": {"bp": "120/80", "hr": 72},
                "allergies": ["penicilina", "látex"]}'::jsonb)) AS t(doc);

Pregunta 13

Cada médico habla uno o más idiomas y atiende uno o más días de la semana, sin relación entre ambas cosas. Se guarda todo en doctor_info(doctor_id, language, office_day), con los tres atributos como clave. ¿Qué dependencias funcionales no triviales tiene? ¿Está en BCNF? ¿Qué redundancia tiene, y qué dependencia la explica? ¿Cómo se descompone?

Pregunta 14

Dos reglas: (1) fee nunca es negativo; (2) cuando una cita pasa a cancelled, su fee se borra (queda en NULL). ¿Qué usarían para cada una: una restricción CHECK, un trigger BEFORE o un trigger AFTER? Justifiquen. ¿Hay una alternativa declarativa para la segunda, y en qué se comporta distinto?