Introducción a las Bases de Datos

Fundamentos de bases de datos Jose Emilio Labra Gayo Universidad de Oviedo Más información Material del curso: https://github.com/cursosLabra/introBBDD Sobre el profesor: https://labra.weso.es/ •Introducción a las bases de datos •Importancia y tecnología en negocios •Tipos de bases de datos y sistemas de gestión de bases de datos •Introducción a SQL y su sintaxis básica •Lenguaje SQL •Diseño conceptual de bases de datos relacionales •Selección y proyección •Uniones •Funciones agregadas y agrupamiento de datos •Subconsultas •Transacciones •Optimización de consultas y técnicas de rendimiento •Manejo de errores y excepciones Contenidos •Introducción a las bases de datos •Importancia y tecnología en negocios •Tipos de bases de datos y sistemas de gestión de bases de datos •Introducción a SQL y su sintaxis básica •Lenguaje SQL •Diseño conceptual de bases de datos relacionales •Selección y proyección •Uniones •Funciones agregadas y agrupamiento de datos •Subconsultas •Transacciones •Optimización de consultas y técnicas de rendimiento •Manejo de errores y excepciones Contenidos Importancia de las Bases de datos Bases de datos = corazón de la era digital Todo lo que usamos depende de Bases de Datos Desde redes sociales, hospitales, sistemas financieros, … Prácticamente todas las aplicaciones modernas se apoyan en Bases de Datos En mundo empresarial Permiten la toma de decisiones Análisis de grandes volúmenes de información Esenciales para IA y aprendizaje automático Aseguran consistencia, integridad y disponibilidad de la información Están en el núcleo de todos los servicios Sin Bases de datos, no hay información Sin información, no hay decisiones Importancia y tecnología en negocios Función esencial de la informática = almacenar datos 2 problemas ¿Cómo representar la información? ¿Cómo almacenar esas representaciones? "Los datos son algo precioso, y durarán más que los propios sistemas" Tim Berners-Lee, Inventor de la Web Personas vs Máquinas Programadas para ciertas tareas Previsibles (sin errores*) Tareas repetitivas sin problema Dificultad para entender el contexto Creatividad, imaginación Imprevisibles (cometemos errores) Nos cansamos ante tareas repetitivas Comprensión basada en contexto *cuando están bien programadas 100000001 010010011 001001001 001001010 Representación de información En ordenadores actuales, todo son 0s y 1s Distintos tipos de información Valores booleanos: 0 (falso), 1 (true) Números: codificaciones de tamaño fijo ó variable Caracteres: Codificaciones como ASCII o Unicode (https://unicode.org/) Cadenas de texto Secuencias de caracteres Registros y estructuras de datos Valores compuestos. Ejemplo: coordenadas, tablas, árboles, grafos, ... Valores binarios Secuencias de 0's y 1's Tamaños de datos Unidad Abrev. Tamaño En bytes Bit Byte B 8 bits Kilobyte KB 1024 B ≈ 1,000 bytes Megabyte MB 1024 KB ≈ 1,000,000 bytes Gigabyte GB 1024 MB ≈ 1,000,000,000 bytes Terabyte TB 1024 GB ≈ 1,000,000,000,000 bytes Petabyte PB 1024 TB ≈ 1,000,000,000,000,000 bytes Exabyte EB 1024 PB ≈ 1,000,000,000,000,000,000 bytes Zettabyte ZB 1024 EB ≈ 1,000,000,000,000,000,000,000 bytes Yottabyte YB 1024 ZB ≈ 1,000,000,000,000,000,000,000,000 bytes Ronnabyte RB 1024 YB ≈ 1,000,000,000,000,000,000,000,000,000 bytes The Zettabyte era: https://en.wikipedia.org/wiki/Zettabyte_Era Aplicaciones de bases de datos Las bases de datos son una parte fundamental de cualquier empresa Cada vez más, las empresas se convierten en empresas de datos En cualquier dominio Banca, seguros, ventas, siderurgia, etc. Tecnológicas: eBay, Facebook, Google, Amazon, ... Algunos datos El negocio de SGBD = 89 mil millones de $ en 2024 Se espera que alcance los 248 mil millones en 2034 [1] [1] https://www.expertmarketresearch.com/reports/database-management-system-market Actividades profesionales relacionadas Gobierno de datos Ciclo de vida, seguridad, acceso, etc. Ingeniería de datos Representación de datos, infraestructura, rendimiento, costes Ciencia de datos Análisis de datos, extracción de información/conocimiento Ingeniería del software Aplicaciones que trabajan con los datos, integración, explotación, etc. Administración de base de datos Mantenimiento Bases de datos, seguridad, réplicas, backups, etc. Actividades relacionadas Migración de datos Cambiar los esquemas de las BBDD Cambiar el SGBD BD1BD2 BD BD BD ETL Extracción - Transformación - Carga (Load) Análisis y minería de datos Extraer información a partir de datos En ocasiones se extrae de los logs de las BBDD Auditoría de datos Certificación Ingestión y conservación de datos Procesos ETL (Extracción - Transformación - Carga) ¿Hojas de cálculo como alternativa a BBDD? Hojas de cálculo Modelo de tabla (celdas) Valores de celdas pueden calcularse a partir de otras Pensadas para acceso interactivo Proceso automatizado más engorroso Tamaño/capacidad limitado Control de usuarios limitado Acceso concurrente limitado No hay soporte para recuperación Bases de datos Varios modelos (tablas...) Datos estáticos (manipulación mediante programas) Pensadas para acceso batch o mediante software Proceso interactivo más engorroso Almacenamiento grandes cantidades de datos Control de usuarios formar parte de SGBD Soporte para concurrencia y transacciones Soporte para recuperación (backups) y similares •Introducción a las bases de datos •Importancia y tecnología en negocios •Tipos de bases de datos y sistemas de gestión de bases de datos •Introducción a SQL y su sintaxis básica •Lenguaje SQL •Diseño conceptual de bases de datos relacionales •Selección y proyección •Uniones •Funciones agregadas y agrupamiento de datos •Subconsultas •Transacciones •Optimización de consultas y técnicas de rendimiento •Manejo de errores y excepciones Contenidos Evolución de bases de datos Los primeros modelos eran jerárquicos y en red Modelo relacional: Desde mediados 70 SQL no es el único modelo: modelos NOSQL Reciente popularidad de bases de datos vectoriales Modelo Jerárquico IBM Modelo red CODASYL Modelo Relacional Lenguaje SQL BBDD documentales BBDD grafo: property graphs, ... 1960 1970 1980 1990 2000 2010 2020 BBDD XML NOSQL BBDD Objetos BBDD clave-valor SPARQL/RDF triple stores RDF BBDD vectoriales Clasificación tradicional en 2 tipos Datos operacionales Utilizados para facilitar la operación de la empresa Ejemplos: datos de usuarios, inventario, transacciones, etc. Son necesarios para que la empresa pueda funcionar Operaciones habituales: Altas, Bajas, Modificaciones A veces se utiliza el término CRUD (Create Retrieve Update Delete) Se asocia con OLTP (OnLine Transaction Processing) y SGBD Datos analíticos Utilizados por científicos de datos y analistas de negocios Objetivo: predicciones, tendencias, inteligencia de negocio, ... No suele ser transaccional ni relacional Los datos no son críticos para el funcionamiento de día a día Se asocia con OLAP (OnLine Analytical Processing) y data warehouses Clasificación de Bases de datos según modelo Relacionales (SQL) Modelo basado en tablas, claves primarias y secundarias Ofrecen soporte para lenguaje SQL Ejemplos: MySQL, SQL Server, SQLite, Oracle, … NoSQL Ideales para datos no estructurados en tablas Pueden escalar horizontalmente (clústeres de servidores) Varios modelos: Documentales, clave-valor, grafos, etc. Ejemplos: MongoDB, CouchDB, Neo4J, … Vectoriales Almacenan vectores numéricos (representaciones texto, imágenes, vídeos,…) Aplicaciones para Búsqueda semántica, similaridad, IA generativa Aplicaciones con embeddings Ejemplo: Pinecone, Qdrant, Milvus, … Clasificación según dónde están los datos On Premises: - Instaladas en servidores propios o de la organización - La empresa controla infraestructura, seguridad, copias de respaldo - Requieren mantenimiento técnico y hardware - Ejemplos: Oracle, SQL Server, MySQL, PostgreSQL, SQLite (en dispositivo) En la nube: - Se accede por internet, proveedor gestiona infraestructura y mantenimiento - Pago por uso (Database as a Service) - Escalabilidad rápida y flexible - Ejemplos: Amazon, Google Cloud, Microsoft Azure Algunos datos sobre cuotas de mercado Mercado global estimado en unos 78 mil millones de dólares en 2024 - BBDD relacionales ocupan 52% del mercado - BBDD NoSQL muestran la mayor tasa de crecimiento (15-16%) - BBDD como servicio pasó de 23 mil millones en 2023 y se espera que alcance los 104 mil millones en 2032 Fuente: https://www.emergenresearch.com/industry-report/dbms-market Ejercicio: Descargar SQLite localmente y crear una base de datos Arrancar Jupyter notebook y ejecutar desde Google Colab https://github.com/cursosLabra/introBBDD/ Sintaxis básica de SQL SQL intenta alcanzar un compromiso entre: Lenguaje legible y natural para el ser humano Lenguaje no ambiguo y procesable por las máquinas Sentencias tipo: SELECT, INSERT, etc. Palabras clave no distinguen minúsculas/mayúsculas Cadenas de texto entre comillas Cada instrucción finaliza en punto y coma (;) Trabaja con conjuntos de datos (no registro a registro) Ejemplo de comando: SELECT Apellidos, Nombre FROM Alumnos WHERE Nombre = 'Ana’ AND Nota >= 9; •Introducción a las bases de datos •Importancia y tecnología en negocios •Tipos de bases de datos y sistemas de gestión de bases de datos •Introducción a SQL y su sintaxis básica •Lenguaje SQL •Diseño conceptual de bases de datos relacionales •Selección y proyección •Uniones •Funciones agregadas y agrupamiento de datos •Subconsultas •Transacciones •Optimización de consultas y técnicas de rendimiento •Manejo de errores y excepciones Contenidos Diseño conceptual de bases de datos relacionales Diagramas Entidad-Relación y modelo relacional SQL como lenguaje de definición de datos (DDL) Creación de tablas SQL como lenguaje de manipulación de datos (DML) Inserción, modificación y borrado de datos SQL como lenguaje de consulta (DQL) Selección de valores Diagramas entidad-relación Propuestos por Peter P. Chen en 1970 Entidad: Cualquier objeto o concepto Se representan por cajas Relación: Correspondencia o asociación entre entidades Se representan con un rombo Grado: número de entidades que relacionan Empleado Proyecto trabajaEn Peter Chen Fuente: https://www.csc.lsu.edu/~chen/ Grados de una relación Grado = número de entidades que intervienen en una relación Relación reflexiva: grado 1 Relación binaria: grado 2 Relación ternaria: grado 3 Auditor Empresa audita Expediente Empleado Proyecto trabajaEn Empleado tieneJefe Cardinalidad Anotar el número de elementos mínimo y máximo que intervienen (1,1) = exactamente 1 (0,1) = opcional (0,n) = 0 ó más (1,n) = 1 ó más (al menos 1) (m,n) = muchos a muchos Empleado Proyecto trabajaEn (m,n) Atributos Atributo = característica o propiedad de una entidad o relación Pueden representarse mediante elipses Empleado Proyecto trabajaEn Nombre Dirección Fecha Dominios Un dominio representa conjunto de valores permitidos para un atributo Por ejemplo: Un número entero, una fecha, una cadena de texto, un valor de una lista, ... Dominio atómico: cuando es un único valor Ejemplo: El dni de una persona es un único valor El número de teléfono podría ser atómico si cada persona tiene un único número No siempre se cumple en la práctica Notación UML para entidades Empleado Nombre: Texto Dirección: Texto FechaNacimiento: Fecha UML = Unified Modelling Language Lenguaje de modelado utilizado para diseñar sistemas Varios tipos de diagramas Los diagramas de clases pueden utilizarse para representar relaciones Proyecto Nombre: Texto Inicio: Fecha Fin: Fecha trabajaEn 1..* 0..3 Diseño de bases de datos relacionales Definir esquemas de Bases de datos Concepto de normalización: Normalización: Dividir en varias tablas para evitar duplicidades Denormalización: Juntar en una tabla para mejorar rendimiento Solución de compromiso: Mantenimiento vs rendimiento Diseño de tablas en bases de datos Ejemplo: Notas de un curso Id Nombre Apellidos FechaNacimiento Tlfno Curso Nota Edición uo234 Jose Torres 1992 -10-29 9855824341 Álgebra 8 2024 - 25 uo512 Ana Cardo 1987 -11-25 6603569787 Álgebra 7 2024 - 25 uo545 Ana Pascual 1995 -01-30 6123677029 Álgebra 9 2024 - 25 uo234 Jose Torres 1992 -10-29 9855824341 Física 4 2024 - 25 u o234 Jose Torres 19 92-10-29 98 65824341 Física 7 2025 - 26 uo545 Ana Pascual 1995 -01-30 6123677029 Lógica 6 2025 - 26 . . . Añadir notas para Jose Torres en Física en edición 2024-25 un 4 y en edición 2025-26 un 7 Y Para Ana Pascual, un 6 en 2025-26 en Logica… Diseño de tablas en bases de datos Normalización: Crear varias tablas unidas por claves para evitar repeticiones Id Nombre Apellidos FechaNacimiento Tlfno uo234 Jose Torres 1992 -10-29 9855824341 uo512 Ana Cardo 1987 -11-25 6603569787 uo545 Ana Pascual 1995 -01-30 6123677029 IdAlumno IdCurso Nota uo234 C1 8 uo512 C1 7 uo545 C1 9 uo234 C2 4 uo234 C3 7 uo545 C4 6 Id Nombre Edición C1 Álgebra 2024 -25 C2 Física 2024 -25 C3 Física 2025 -26 C4 Lógica 2025 -26 Diseño conceptual de bases de datos relacionales Diagramas Entidad-Relación y modelo relacional SQL como lenguaje de definición de datos (DDL) Creación de tablas SQL como lenguaje de manipulación de datos (DML) Inserción, modificación y borrado de datos SQL como lenguaje de consulta (DQL) Selección de valores Creación de tablas CREATE permite crear tablas CREATE TABLE NombreTabla (col1 Tipo1,...colN TipoN) Ejemplo: CREATE TABLE alumnos ( Id TEXT, Nombre TEXT, Apellidos TEXT, FechaNacimiento Date, Tlfno TEXT ) Id Nombre Apellidos FechaNacimiento Tlfno NOTA: SQLLite añade automáticamente una columna denominada ROWID Tipos básicos de SQLite: -TEXT - INTEGER -REAL -BLOB -ANY Otros tipos de SQL que se aceptan - NUMERIC - BOOLEAN -DATE - DATETIME - VARCHAR -… Definiciones de claves CREATE TABLE Alumnos ( Id INTEGER PRIMARY KEY, Nombre TEXT, Apellidos TEXT, FechaNacimiento DATE ); CREATE TABLE Cursos ( Id INTEGER PRIMARY KEY, Nombre TEXT, Descripcion TEXT ); CREATE TABLE Notas ( Id INTEGER PRIMARY KEY, AlumnoId INTEGER, CursoId INTEGER, Nota REAL, FOREIGN KEY (AlumnoId) REFERENCES Alumnos(Id), FOREIGN KEY (CursoId) REFERENCES Cursos(Id) ); Nota: En SQLite, se puede forzar al sistema que comprueba las claves externas mediante: PRAGMA foreign_keys = ON; Restricciones en tablas Es posible añadir restricciones en los valores de las tablas CREATE TABLE Contactos ( id INTEGER PRIMARY KEY, nombre TEXT NOT NULL COLLATE nocase, email TEXT NOT NULL UNIQUE, tlfno TEXT NOT NULL DEFAULT 'UNKNOWN', edad INTEGER CHECK (edad >= 0 AND edad <= 120), UNIQUE (nombre, tlfno) ); Tipos de restricciones: - NOT NULL: El campo no puede ser nulo -UNIQUE: Los valores deben ser únicos en la tabla -CHECK expr: El campo debe cumplir la condición expr -COLLATE (binary, nocase, rtrim): Estrategia para comparar valores de texto … Modificación de tablas (ALTER) Permite modificar una tabla Cambiar de nombre: ALTER TABLE alumnos RENAME TO Estudiantes; Añadir/renombrar/borrar columnas ALTER TABLE Estudiantes ADD COLUMN Email Text; ALTER TABLE Estudiantes RENAME COLUMN Email TO Correo; ALTER TABLE Estudiantes DROP COLUMN Correo; Eliminación de tablas (DROP) Borrar una tabla Ejemplo: DROP TABLE Estudiantes; Diseño conceptual de bases de datos relacionales Diagramas Entidad-Relación y modelo relacional SQL como lenguaje de definición de datos (DDL) Creación de tablas SQL como lenguaje de manipulación de datos (DML) Inserción, modificación y borrado de datos SQL como lenguaje de consulta (DQL) Selección de valores ORDER BY Permite clasificar las filas del resultado Formato: ORDER BY expresión [ASC|DESC] [,...] La expresión suele ser una columna, pero puede ser más compleja ASC/DESC indica orden ascendente/descentente (ASC por defecto) LIMIT y OFFSET Permiten extraer subconjuntos específicos de filas LIMIT define el número máximo de filas a extraer OFFSET define el número de filas a saltar antes de extraer la primera LIMIT 10 -- devuelve las 10 primeras filas LIMIT 10 OFFSET 3-- devuelve las filas 4 a 13 LIMIT 3OFFSET 20 -- devuelve las filas 21 a 23 •Introducción a las bases de datos •Importancia y tecnología en negocios •Tipos de bases de datos y sistemas de gestión de bases de datos •Introducción a SQL y su sintaxis básica •Lenguaje SQL •Diseño conceptual de bases de datos relacionales •Selección y proyección •Uniones •Funciones agregadas y agrupamiento de datos •Subconsultas •Transacciones •Optimización de consultas y técnicas de rendimiento •Manejo de errores y excepciones Contenidos Proyección En la cabecera se indica qué columnas se muestran en resultado La cabecera puede contener: - Una lista de columnas - * para seleccionar todas las columnas - Expresiones A cada columna se puede asociar un alias mediante AS Se puede usar SELECT DISTINCT para eliminar duplicados SELECT Apellidos, Nombre FROM Alumnos SELECT * FROM Alumnos SELECT FechaNacimiento AS Nacimiento, Nombre || ' ' || Apellidos AS NombreCompleto FROM Alumnos SELECT DISTINCT Nombre FROM alumnos Selección (WHERE) Define una condición que permite filtrar registros en tabla de resultados Se proporciona una expresión que se evalúa para cada fila Las filas cuyo resultado sea falso o NULL son descartadas Expresiones que pueden incluirse: Expresiones lógicas encadenadas mediante AND, OR, NOT Expresiones de comparación: <, >, >=, >=, =, != Operadores aritméticos: +, - *, /, % Otras operaciones: ISNULL, NOT NULL, IS DISTINCT FROM, CASE, IN, NOT IN, … Funciones: https://www.sqlite.org/lang_corefunc.html Selección (WHERE) Ejemplos: SELECT Nombre, Apellidos, FechaNacimiento FROM alumnos WHERE FechaNacimiento > '2000-01-01' OR FechaNacimiento < '1990-31-12' SELECT Nombre, Apellidos, FechaNacimiento FROM alumnos WHERE length(Apellidos) > 6 •Introducción a las bases de datos •Importancia y tecnología en negocios •Tipos de bases de datos y sistemas de gestión de bases de datos •Introducción a SQL y su sintaxis básica •Lenguaje SQL •Diseño conceptual de bases de datos relacionales •Selección y proyección •Uniones •Funciones agregadas y agrupamiento de datos •Subconsultas •Transacciones •Optimización de consultas y técnicas de rendimiento •Manejo de errores y excepciones Contenidos FROM Permite identificar las tablas desde las que se obtendrán los valores Puede contener: - Un nombre de tabla - Un Alias para el nombre de la tabla - Varias tablas mediante JOIN - 3 tipos de JOIN: Interno, Externo https://www.youtube.com/shorts/RiCsbGKKymU Explicando los joins con piezas Lego: CROSS JOIN (unión cruzada/producto cartesiano) Id Nombre Id Nota uo234 Jose uo234 7.8 uo512 Ana uo234 7.8 uo545 Luis uo234 7.8 uo234 Jose uo545 10 uo512 Ana uo545 10 uo545 Luis uo545 10 uo234 Jose uo666 3 uo512 Ana uo666 3 uo545 Luis uo666 3 Id Nombre uo234 Jose uo512 Ana uo545 Luis Id Nota uo234 7.8 uo545 10 uo666 3 X X X X X X X X X Nombres Valores SELECT * FROM Nombres CROSS JOIN Valores; SELECT Nombres.Id, Nombre, Nota FROM Nombres INNER JOIN Valores ON Nombres.Id = Valores.Id ; INNER JOIN (Unión natural) Id Nombre Nota uo234 Jose 7.8 uo545 Luis 10 Id Nombre uo234 Jose uo512 Ana uo545 Luis Id Nota uo234 7.8 uo545 10 uo666 3 X X Nombres Valores Subconsultas Se pueden incluir consultas SELECT dentro de consultas SELECT Puede ser útil para utilizar valores auxiliares SELECT N.Id, N.Nota, (SELECT AVG(N2.Nota) FROM Valores N2) AS Nota_Media, N.Nota - (SELECT AVG(N3.Nota) FROM Valores N3) AS Desv FROM Valores N SELECT N.Id, N.Nota, Stats.Nota_Media, N.Nota - Stats.Nota_Media AS Desv FROM Valores N, (SELECT AVG(Nota) AS Nota_Media FROM Valores) Stats Id Nota uo234 7.8 uo545 10 uo666 3 Valores Id Nota Nota_Media Desv uo234 7.8 6.93 0.86 uo545 10 6.93 3.06 uo512 3 6.93 -3.93 Versión más eficiente •Introducción a las bases de datos •Importancia y tecnología en negocios •Tipos de bases de datos y sistemas de gestión de bases de datos •Introducción a SQL y su sintaxis básica •Lenguaje SQL •Diseño conceptual de bases de datos relacionales •Selección y proyección •Uniones •Funciones agregadas y agrupamiento de datos •Subconsultas •Transacciones •Optimización de consultas y técnicas de rendimiento •Manejo de errores y excepciones Contenidos Transacciones Transacción = bloque de operaciones que se ejecutan como una unidad atómica Es decir: O se ejecutan todas, o no se ejecuta ninguna Propiedades ACID - Atomicidad: todas las operaciones se aplican, o ninguna -Consistencia: la base datos pasa de un estado válido a un estado válido - Aislamiento: cambios intermedios no afectan a otras operaciones concurrentes -Durabilidad: los cambios confirmados se mantienen incluso si hay un fallo de energía Comandos de transacciones BEGIN [Transaction] COMMIT ROLLBACK CREATE TABLE alumnos (nombre TEXT,nota REAL); INSERT INTO alumnos VALUES ('Luis', 6); BEGIN TRANSACTION; INSERT INTO alumnos VALUES ('Juan', 7.5); INSERT INTO alumnos VALUES ('Mara', 12); ROLLBACK; Nom bre Nota Luis 6 •Introducción a las bases de datos •Importancia y tecnología en negocios •Tipos de bases de datos y sistemas de gestión de bases de datos •Introducción a SQL y su sintaxis básica •Lenguaje SQL •Diseño conceptual de bases de datos relacionales •Selección y proyección •Uniones •Funciones agregadas y agrupamiento de datos •Subconsultas •Transacciones •Optimización de consultas y técnicas de rendimiento •Manejo de errores y excepciones Contenidos Optimización de consultas y rendimiento Normalización de bases de datos Creación de índices Planes de ejecución Comando EXPLAIN Reconstrucción de la base de datos VACUUM Actualización de estadísticas •Introducción a las bases de datos •Importancia y tecnología en negocios •Tipos de bases de datos y sistemas de gestión de bases de datos •Introducción a SQL y su sintaxis básica •Lenguaje SQL •Diseño conceptual de bases de datos relacionales •Selección y proyección •Uniones •Funciones agregadas y agrupamiento de datos •Subconsultas •Transacciones •Optimización de consultas y técnicas de rendimiento •Manejo de errores y excepciones Contenidos Manejo de errores y excepciones Violaciones de restricciones Uso de disparadores (triggers) Tabla con log de errores mediante triggers Copias de seguridad Más allá de SQL Otros modelos de datos Bases de datos de grafo RDF SPARQL y RDF Triple Stores Wikidata