Bases de datos

Antes de las bases de datos, los programas almacenaban datos en archivos planos — usualmente un archivo por programa. Esto es adecuado para datos pequeños, pero falla a gran escala.

Limitaciones

• redundancia de datos — los mismos datos (la dirección de un cliente) se mantienen en varios archivos, uno por programa; se desperdicia almacenamiento y cada copia debe actualizarse.
• inconsistencia de datos — cuando una copia se actualiza y otra no, los archivos discrepan y nadie sabe cuál es la correcta.
• dependencia de datos — cada programa está escrito para el layout exacto de sus archivos; si se cambia la longitud de un campo o se añade uno nuevo, todos los programas que leen el archivo deben reescribirse.
• sin acceso compartido — un archivo se bloquea mientras un programa lo usa, por lo que los usuarios no pueden trabajar en los datos simultáneamente.
• integridad débil — no hay reglas centrales que impidan valores inválidos o enlaces a clientes que no existen; seguridad débil — el acceso es por archivo, no por campo; y las consultas entre archivos requieren un programa nuevo cada vez.

Una base de datos relacional corrige esto almacenando datos en tablas gestionadas por un único software (el SGBD) que utilizan todos los programas.

Por qué una base de datos relacional es mejor — la respuesta de tres puntos. Cada elemento de dato se almacena una sola vez, en una tabla, y las tablas están vinculadas por claves, por lo que no hay redundancia ni inconsistencia; los datos son independientes de los programas, que solicitan al SGBD lo que necesitan y no se ven afectados cuando cambia la estructura; y el SGBD aplica reglas de integridad, controla el acceso por usuario y por campo, permite muchos usuarios a la vez y responde cualquier consulta sin necesidad de escribir un programa nuevo.

Ejercicio resuelto. Una tienda de reparaciones almacena sus clientes, dispositivos y trabajos de reparación usando un enfoque basado en archivos, un archivo por programa. Indique tres problemas que esto causa y describa cómo una base de datos relacional los eliminaría.

El nombre y el teléfono del cliente se almacenan en el archivo de reparaciones y en el archivo de facturas (redundancia); cuando un cliente cambia su número, se actualiza un archivo y el otro no (inconsistencia); y cuando la tienda desea un informe nuevo — reparaciones por técnico — se debe escribir un programa nuevo para leer los archivos (sin consultas ad-hoc). En una base de datos relacional, el cliente se almacena una sola vez en una tabla CUSTOMER y se hace referencia mediante CustomerID desde la tabla REPAIR, por lo que el cambio se realiza una sola vez y se ve en todas partes; el informe es una única consulta SQL.

• tabla — una cuadrícula de filas y columnas; una tabla por tipo de entidad (ej. CUSTOMER).
• registro (fila, también llamado tupla) — una fila; una instancia de la entidad.
• campo (columna, también llamado atributo) — una columna; una pieza de información sobre cada registro.
• clave primaria — un campo (o campos) que identifica de forma única cada registro; nunca nulo ni duplicado.
• clave foránea — un campo cuyo valor coincide con la clave primaria de otra tabla, vinculándolas.
• clave compuesta — una clave primaria formada por dos o más campos juntos.
• clave candidata — cualquier campo(s) que podría ser la clave primaria.
• clave secundaria — un campo no primario que está indexado para búsquedas rápidas.
• indexación — crear un índice en un campo para que las búsquedas y uniones sean más rápidas.
• integridad referencial — cada valor de clave foránea debe coincidir con una clave primaria existente (no hay registros huérfanos).

Una tabla se escribe en abreviatura subrayando la clave primaria y señalando las claves foráneas:

CUSTOMER(CustomerID, Name, Phone)
ORDER(OrderID, CustomerID, OrderDate)   -- CustomerID is FK → CUSTOMER

Ejercicio resuelto. Explique qué significan entidad, clave primaria e integridad referencial en una base de datos relacional, y complete la tabla de término ↔ descripción para tupla y atributo.

Una entidad es algo sobre lo cual se almacenan datos — una persona, objeto o evento — que se convierte en una tabla. Una clave primaria es el atributo (o combinación de atributos) que identifica de forma única cada registro en una tabla. La integridad referencial significa que cada valor de clave foránea debe coincidir con el valor de una clave primaria en la tabla a la que hace referencia, por lo que un registro no puede referirse a uno que no existe. Una tupla es una fila de una tabla (un registro); un atributo es una columna (un campo). Aprenda los pares: tabla/relación, registro/tupla, campo/atributo.

Un diagrama de entidad-relación muestra la estructura: cada entidad es un rectángulo, cada relación una línea, con la cardinalidad marcada en cada extremo:

• uno a uno (1:1).
• uno a muchos (1:M) — cada Cliente tiene muchos Pedidos; cada Pedido tiene un Cliente.
• muchos a muchos (M:N) — los Estudiantes toman muchos Cursos, y los Cursos tienen muchos Estudiantes.

Una relación de muchos a muchos no se puede almacenar directamente. Divídala en dos relaciones de uno a muchos a través de una tabla de enlace que contiene las dos claves foráneas:

ENROLMENT(StudentID, CourseID, EnrolmentDate)

Dibujar el diagrama E-R para un conjunto dado de tablas. Cada tabla se convierte en una entidad. Existe una relación donde una tabla contiene una clave foránea hacia otra; esta va desde la tabla que contiene la clave foránea (el extremo muchos) hasta la tabla cuya clave primaria es (el extremo uno). Una tabla con dos claves foráneas y sin otra identidad suele ser una tabla de enlace que resuelve una relación de muchos a muchos. Etiquete cada línea con el tipo de relación.

Ejemplo resuelto. Una taller de reparaciones tiene las tablas CUSTOMER(CustomerID, Name, Phone), DEVICE(DeviceID, CustomerID, Type, Model), TECHNICIAN(TechnicianID, Name) y REPAIR(RepairID, DeviceID, TechnicianID, RepairDate, Cost). Identifique las relaciones y sus tipos.

DEVICE contiene CustomerID, por lo tanto CUSTOMER–DEVICE es uno a muchos (un cliente, muchos dispositivos). REPAIR contiene DeviceID, por lo tanto DEVICE–REPAIR es uno a muchos; también contiene TechnicianID, por lo tanto TECHNICIAN–REPAIR es uno a muchos. No hay línea directa CUSTOMER–REPAIR: el enlace pasa a través de DEVICE. Tres líneas, tres patas de gallo, todas en los extremos REPAIR o DEVICE.

Normalización 规范化 organiza las tablas para reducir la redundancia e inconsistencia, pasando por formas normales 范式 en orden.

• Primera forma normal (1NF) — cada campo contiene un único valor (atómico 原子), sin grupos repetidos, y con una clave primaria.
• Segunda forma normal (2NF) — en 1NF, y cada campo no clave depende de la clave primaria entera (solo importa si es una clave compuesta).
• Tercera forma normal (3NF) — en 2NF, y cada campo no clave depende únicamente de la clave primaria, no de otro campo no clave (sin dependencia transitiva 传递依赖).

Un diseño 3NF almacena cada dato una sola vez, por lo que desaparecen las anomalías de inserción, actualización y eliminación. La contrapartida es tener más tablas y más uniones (joins). Se debe apuntar a 3NF.

Para obtener un diseño 3NF: encuentre las entidades y sus atributos; elija una clave primaria para cada una; divida campos repetidos o no atómicos (1NF); divida campos que dependen de parte de una clave compuesta (2NF); divida campos que dependen transitivamente de la clave (3NF); añada claves foráneas para las relaciones.

Ejemplo resuelto. La tabla ORDER(OrderID, CustomerID, CustomerName, ProductID, Quantity) tiene la clave primaria compuesta (OrderID, ProductID). Normalícela a 3NF. Pruebe cada campo no clave contra la clave. Quantity depende de ambos OrderID y ProductID, lo cual está bien. Pero CustomerID depende de OrderID solo - solo parte de la clave compuesta. Eso es una dependencia parcial, por lo que la tabla no está en 2NF. Divídala en ORDER_LINE(OrderID, ProductID, Quantity) y ORDER(OrderID, CustomerID, CustomerName). Ahora pruebe 3NF: en esa nueva tabla ORDER, CustomerName depende de CustomerID, que no es la clave - una dependencia transitiva. Vuelva a dividir: ORDER(OrderID, CustomerID) y CUSTOMER(CustomerID, CustomerName). Nombren la dependencia que rompe cada forma (la parcial rompe 2NF, la transitiva rompe 3NF); decir "tiene datos repetidos" describe el síntoma pero no otorga puntos.

Las tres preguntas para hacer a cualquier tabla. ¿Celda contiene un único valor, sin grupo repetido? Si no, no está en 1NF. Si la clave es compuesta, ¿cada campo no clave depende de toda la clave? Si algún campo depende de parte de ella, hay una dependencia parcial 部分依赖 y la tabla no está en 2NF. ¿Cada campo no clave depende solo de la clave? Si un campo depende de otro campo no clave, hay una dependencia transitiva y la tabla no está en 3NF. Una respuesta a "explique por qué la tabla no está en 3NF" debe nombrar la dependencia y los campos implicados.

Ejemplo resuelto. Un alquiler de coches registra cada alquiler como RENTAL(RentalID, RentalDate, CustomerID, CustomerName, CustomerPhone, CarReg, CarModel, DailyRate, Days), donde un alquiler puede incluir varios coches. Explique por qué la tabla no está normalizada y produzca un diseño 3NF.

No está en 1NF: los campos de CarReg, CarModel, DailyRate, Days forman un grupo repetitivo — una renta tiene varios coches. Moverlos a RENTAL_CAR(RentalID, CarReg, CarModel, DailyRate, Days) con la clave compuesta (RentalID, CarReg). No está en 2NF: en RENTAL_CAR, CarModel y DailyRate dependen solo de CarReg — una dependencia parcial. Moverlos a CAR(CarReg, CarModel, DailyRate), dejando RENTAL_CAR(RentalID, CarReg, Days). No está en 3NF: en RENTAL, CustomerName y CustomerPhone dependen de CustomerID, un campo no clave — una dependencia transitiva. Moverlos a CUSTOMER(CustomerID, CustomerName, CustomerPhone), dejando RENTAL(RentalID, RentalDate, CustomerID). El diseño en 3NF son cuatro tablas — CUSTOMER, RENTAL, RENTAL_CAR, CAR — con CustomerID, RentalID y CarReg como claves foráneas; subrayar todas las claves primarias.

Un DBMS 数据库管理系统 gestiona la base de datos centralmente. Funcionalidades que corrigen las limitaciones basadas en archivos:

• data dictionary 数据字典 — una descripción de cada tabla, campo, tipo y clave; los programas la consultan en lugar de codificar la estructura de forma fija.
• control de redundancia/consistencia — cada dato se almacena una sola vez.
• control de acceso concurrente 并发访问 — los bloqueos y transacciones permiten que muchos usuarios trabajen simultáneamente.
• backup 备份 y recuperación; seguridad y permisos por usuario.
• reglas de integridad — claves, restricciones de unicidad y rango, aplicadas centralmente.
• transacciones 事务 — un grupo de operaciones que todas tienen éxito o todas fallan.
• vistas 视图 — tablas virtuales que muestran a cada usuario "su" fragmento de los datos.
• gestión de datos 数据管理 y modelado de datos 数据建模 — controlan cómo se almacenan los datos y definen su estructura como un esquema lógico 逻辑模式 (el diseño lógico, independiente del almacenamiento físico).
• integridad de datos 数据完整性 y seguridad de datos 数据安全 — garantizan la corrección y controlan el acceso centralmente.
• un procesador de consultas 查询处理器 ejecuta consultas; una interfaz de desarrollador 开发者接口 proporciona herramientas y APIs para crear aplicaciones.

Sus herramientas incluyen un editor de diccionario de datos, un constructor de consultas, un constructor de formularios, un generador de informes, gestión de usuarios y un editor SQL.

Lo que contiene el diccionario de datos (una pregunta de "dar tres elementos"): los nombres de las tablas; los nombres de los campos en cada tabla; el tipo de dato y longitud de cada campo; las claves primarias y foráneas y las relaciones entre tablas; reglas de validación; índices; y quién puede acceder a cada tabla. Es metadatos — datos sobre los datos — y el DBMS lo usa para verificar cada consulta y cada cambio.

Cómo mantiene seguro el DBMS los datos (una pregunta de "describir dos métodos"): autenticación 身份验证 — un nombre de usuario y contraseña, o biométrica, antes de cualquier acceso; derechos de acceso — a cada usuario o grupo se le permite leer, escribir o eliminar solo ciertas tablas o campos, a menudo a través de una vista; cifrado de los datos almacenados y de los datos enviados a ellos, para que un archivo copiado sea ilegible; copias de seguridad tomadas regularmente, para restaurar los datos tras una pérdida; y un registro de transacciones que registra quién cambió qué.

Las dos herramientas de software. La interfaz de desarrollador es lo que utiliza un programador para construir la base de datos y las aplicaciones sobre ella: crear tablas y establecer claves y validaciones, escribir consultas y SQL, y diseñar formularios e informes, sin saber cómo se almacenan físicamente los datos. El procesador de consultas toma una consulta (SQL desde un programa, o una consulta construida en la interfaz), la verifica contra el diccionario de datos, determina la forma más eficiente de ejecutarla, recupera los datos y devuelve los resultados.

Esquema lógico. El DBMS mantiene el diseño lógico (qué tablas y campos existen y cómo se relacionan) separado del almacenamiento físico (archivos, índices, bloques de disco). Los programas trabajan con el esquema lógico, por lo que el almacenamiento físico puede reorganizarse sin cambiar un solo programa — esta es la independencia de datos que carecía el enfoque basado en archivos.

SQL 结构化查询语言 (Structured Query Language) tiene dos mitades:

• Data Definition Language 数据定义语言 (DDL) — crea o modifica la estructura (tablas, claves, restricciones).
• Data Manipulation Language 数据操纵语言 (DML) — trabaja con los datos (insertar, actualizar, eliminar, consultar 查询).

Fundamentos de DDL

CREATE TABLE CUSTOMER (
  CustomerID INTEGER PRIMARY KEY,
  Name VARCHAR(50) NOT NULL,
  Phone VARCHAR(20)
);

Añadir una clave foránea:

CREATE TABLE ORDER (
  OrderID INTEGER PRIMARY KEY,
  CustomerID INTEGER,
  OrderDate DATE,
  FOREIGN KEY (CustomerID) REFERENCES CUSTOMER(CustomerID)
);

Modificar y eliminar:

ALTER TABLE CUSTOMER ADD Email VARCHAR(100);
DROP TABLE CUSTOMER;

Tipos comunes: INTEGER, REAL, VARCHAR(n), CHAR(n) (también CHARACTER(n)), DATE, TIME, BOOLEAN, DECIMAL(p, s).

Fundamentos de DML

Consultar con SELECT:

SELECT Name, Phone
FROM CUSTOMER
WHERE City = 'London'
ORDER BY Name ASC;

SELECT lista los campos, FROM nombra la tabla, WHERE filtra las filas y ORDER BY ordena.

Un join (unión) combina dos tablas mediante una relación de clave foránea:

SELECT C.Name, O.OrderDate
FROM CUSTOMER C INNER JOIN ORDER O
  ON C.CustomerID = O.CustomerID
WHERE O.OrderDate >= '2024-01-01';

Las funciones agregadas (COUNT, SUM, AVG, MIN, MAX) se utilizan a menudo con GROUP BY:

SELECT CustomerID, COUNT(*) AS NumOrders
FROM ORDER
GROUP BY CustomerID;

Insertar, actualizar, eliminar:

INSERT INTO CUSTOMER (CustomerID, Name, Phone)
VALUES (101, 'Ada Lovelace', '020-1234-5678');

UPDATE CUSTOMER SET Phone = '020-9999-0000' WHERE CustomerID = 101;

DELETE FROM CUSTOMER WHERE CustomerID = 101;

Siempre incluya una cláusula WHERE en UPDATE y DELETE; de lo contrario, el cambio afectará todas las filas.

Consejos para el examen de SQL

• Use los nombres exactos de tablas y campos del enunciado.
• Encierre las cadenas entre comillas simples ('Smith'); no encierre los números.
• Operadores de comparación: =, <, >, <=, >=, <>.
• LIKE 'A%' coincide con cualquier cadena que empiece por A (% = cualquier cadena, _ = un carácter); IN (1,2,3); BETWEEN 10 AND 20.
• Combine condiciones con AND / OR / NOT y termine cada sentencia con punto y coma.

El patrón DDL que busca el examen. Cada CREATE TABLE nombra cada campo con su tipo, marca la clave primaria y declara cada clave foránea indicando la tabla referenciada; una clave compuesta se declara en su propia línea:

CREATE TABLE RENTAL_CAR (
  RentalID INTEGER,
  CarReg VARCHAR(8),
  Days INTEGER,
  PRIMARY KEY (RentalID, CarReg),
  FOREIGN KEY (RentalID) REFERENCES RENTAL(RentalID),
  FOREIGN KEY (CarReg) REFERENCES CAR(CarReg)
);

Ejemplo resuelto. Usando CUSTOMER(CustomerID, Name, Phone) y DEVICE(DeviceID, CustomerID, Type, Model), escriba scripts SQL para: (a) listar el nombre y número de teléfono de todos los clientes que poseen un dispositivo de tipo 'tablet', en orden alfabético por nombre; (b) contar los dispositivos de cada tipo; (c) registrar que el cliente 17 tiene ahora el número de teléfono '0771 234 5678'; (d) añadir un nuevo dispositivo, ID 305, un 'laptop' del modelo 'X1' perteneciente al cliente 17.

(a)

SELECT CUSTOMER.Name, CUSTOMER.Phone
FROM CUSTOMER INNER JOIN DEVICE
  ON CUSTOMER.CustomerID = DEVICE.CustomerID
WHERE DEVICE.Type = 'tablet'
ORDER BY CUSTOMER.Name ASC;

(b)

SELECT Type, COUNT(DeviceID) AS NumberOfDevices
FROM DEVICE
GROUP BY Type;

(c) UPDATE CUSTOMER SET Phone = '0771 234 5678' WHERE CustomerID = 17; (d) INSERT INTO DEVICE (DeviceID, CustomerID, Type, Model) VALUES (305, 17, 'laptop', 'X1');

Se otorgan marcas por cláusula — los campos, las tablas, la condición de unión, el WHERE, el ORDER BY — por lo que un script con una sola cláusula incorrecta aún obtiene crédito en el resto. Escribe Table.Field siempre que se involucren dos tablas.

Ejemplo resuelto. Explica qué hace este script: SELECT T.Name, SUM(R.Cost) AS Total FROM TECHNICIAN T INNER JOIN REPAIR R ON T.TechnicianID = R.TechnicianID GROUP BY T.Name;

Expresa el nombre de cada técnico junto con el coste total de las reparaciones realizadas por ese técnico, una fila por técnico: las dos tablas se unen mediante TechnicianID, las filas se agrupan por nombre y los costes de cada grupo se suman. Cuando te pidan explicar qué hace un script, describe el resultado, no la sintaxis.

Una pregunta de definición se califica según un texto fijo. Aprende estas definiciones exactamente, y da solo una respuesta.

• Define los términos exactamente: entidad, atributo, clave primaria, clave foránea, y los tipos de relación (1:1, 1:muchos, muchos:muchos).
• Da una razón en cada forma normal: 1FN (sin grupos repetidos), 2FN (sin dependencia parcial), 3FN (sin dependencia no clave) — y nombra los campos implicados.
• Explica qué ofrece un SGBD (independencia de datos, seguridad, integridad, acceso concurrente, un diccionario de datos, una interfaz para desarrolladores, un procesador de consultas).
• Distingue DDL (define la estructura) de DML (consulta y modifica los datos), y escribe la cláusula SQL por cláusula: SELECT, FROM, INNER JOIN … ON, WHERE, GROUP BY, ORDER BY.
• Para dibujar un diagrama E-R a partir de tablas, busca primero cada clave foránea: cada clave foránea representa una relación uno-a-muchos, siendo el "muchos" la tabla que la contiene.

Errores comunes

• Dibujar una relación muchos-a-muchos directamente. Debe dividirse en dos relaciones uno-a-muchos a través de una tabla de enlace que contenga ambas claves foráneas.
• Explicar "no está en 3FN" simplemente diciendo "los datos están repetidos". Nombra la dependencia (parcial o transitiva) y los campos implicados.
• Usar comillas dobles alrededor de cadenas en SQL, o comillas alrededor de números. Las cadenas requieren 'single quotes'; los números no requieren ninguno.
• Omisión de la condición ON después de INNER JOIN. Sin ella, las dos tablas no quedan vinculadas.
• Colocar un campo ordinario junto a COUNT o SUM dentro de un SELECT sin especificar un GROUP BY.
• UPDATE o DELETE sin incluir un WHERE. Este cambia o elimina todas las filas de la tabla.