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 uo234 Jose Torres 1992 -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 NombreEntero 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: IS NULL, NOT NULL, IS DISTINCT FROM, CASE, IN, NOT IN, … Funciones: length, Funciones de sqlite: https://www.sqlite.org/lang_corefunc.html Operadores de comparación Operador Descripción Ejemplo = Igual WHERE nota = 10 != ó <> Distinto WHERE apellidos != 'García' > , < Mayor, menor WHERE nota > 7 >=, <= Mayor o igual, menor o Igual WHERE nota >= 5 Filtrado textual Operador LIKE permite comparar cadenas de texto % = cualquier secuencia _ = un solo carácter SELECT * FROM alumnus WHERE apellidos LIKE 'G%'; SELECT * FROM alumnus WHERE apellidos LIKE '%ez%'; Empieza por G Contiene 'ez' Operadores lógicos AND (Conjunción), OR (Disyunción), NOT (Negación) SELECT * FROM alumnos WHERE nota >= 8 AND apellidos LIKE 'G%'; IS (NOT) NULL Un campo NULL representa la ausencia de valor La comparación con NULL siempre devuelve falso. Para filtrar si el valor es NULL se debe utilizar IS NULL ó IS NOT NULL SELECT nombre, apellidos FROM alumnos WHERE tlfno IS NULL ; SELECT nombre, apellidos FROM alumnos WHERE tlfno IS NOT NULL ; SELECT nombre, apellidos FROM alumnos WHERE tlfno = NULL ; Siempre devuelve falso Funciones de tiempo date time datetime strftime julianday timediff 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 SELECT Nombres.Id, Nombre, Nota FROM Nombres LEFT OUTER JOIN Valores ON Nombres.Id = Valores.Id ; LEFT OUTER JOIN (Unión externa) Id Nombre Nota uo234 Jose 7.8 uo545 Luis 10 uo512 Ana Null Id Nombre uo234 Jose uo512 Ana uo545 Luis Id Nota uo234 7.8 uo545 10 uo666 3 X X Nombres Valores •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 GROUP BY <expresión> Permite agrupar filas Objetivo: contar filas, sumar valores, calcular valor medio, mínimo, etc. Nombre Nota Curso Jose 7.8 Lógica Eva 9 Álgebra Luis 10 Lógica Ana 4 Álgebra Luis 7 Álgebra Jose 6 Álgebra SELECT curso, count(*) AS Num FROM Notas GROUP BY Curso; Curso Num Lógica 2 Álgebra 4 GROUP BY <expresión> Otras funciones de agregación: AVG, MIN, MAX,... Nombre Nota Curso Jose 7.8 Lógica Eva 9 Álgebra Luis 10 Lógica Ana 4 Álgebra Luis 7 Álgebra Jose 6 Álgebra SELECT curso, avg(Nota) AS Media, min(Nota) AS Mín, max(Nota) AS Máx FROM Notas GROUP BY Curso; Curso Media Mín Máx Lógica 8.9 7.8 10.0 Álgebra 6.5 4.0 9.0 HAVING <Expresión> Se utiliza para filtrar resultados después de un GROUP BY Es parecido a WHERE Diferencia: HAVING aparece después de GROUP BY HAVING puede hacer referencia a variables de la cabecera que usan agregados SELECT curso, avg(Nota) as Media FROM Notas GROUP BY Curso Having Media > 8; Nombre Nota Curso Jose 7.8 Lógica Eva 9 Álgebra Luis 10 Lógica Ana 4 Álgebra Luis 7 Álgebra Jose 6 Álgebra Curso Media Lógica 8.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 Subconsultas Subconsulta = consulta dentro de otra consulta Permite calcular valores, filtrar o devolver resultados intermedios Se suele incluir dentro de WHERE, FROM ó SELECT Ejemplo: SELECT nombre FROM nombres WHERE id IN ( SELECT id FROM valores WHERE nota >= 8 ); Id Nombre uo234 Jose uo512 Ana uo545 Luis Id Nota uo234 7.8 uo545 10 uo666 3 Nombres Valores uo545 Nombre Luis Subconsultas También se puede incluir una subconsulta en la cabecera Id Nombre uo234 Jose uo512 Ana uo545 Luis Nombres Id_Alumno Nota Curso uo234 7.8 C1 uo545 10 C1 uo666 3 C2 uo545 7 C2 Notas SELECT nombre, (SELECT avg(nota) FROM notas WHERE notas.id_alumno = nombres.id ) AS promedio FROM nombres; Nombre Promedio Jose 7.8 Ana Luis 8.5 Subconsultas Se pueden incluir consultas SELECT dentro de consultas SELECT Puede ser útil para utilizar valores auxiliares SELECT id, Nota, (SELECT AVG(Nota) FROM Valores) AS Nota_Media, Nota - (SELECT AVG(Nota) FROM Valores) AS Desv FROM Valores; SELECT Id, Nota, TablaAux.Nota_Media, Nota - TablaAux.Nota_Media AS Desv FROM Valores, (SELECT AVG(Nota) AS Nota_Media FROM Valores) TablaAux; 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 Comandos de transacciones Ciclo de vida: COMMIT: Finaliza transacción, hace que cambios sean permanentes ROLLBACK: Revierte todos los cambios planteados desde BEGIN 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; Nombre Nota Luis 6 BEGIN …Operaciones… COMMIT/ROLLBACK Transacciones automáticas Por defecto, SQLite realiza Auto-commit Cada instrucción es una transacción completa Es decir: INSERT INTO alumnos VALUES ('Luis', 6); Equivale a: BEGIN; INSERT INTO alumnos VALUES ('Luis', 6); COMMIT; Este comportamiento se puede modificar al introducir BEGIN para agrupar varias instrucciones en una transacción Concurrencia Una base de datos  cuaderno donde se registra información Concurrencia: ¿Qué pasa si varios usuarios acceden a la vez? En SQLite: Varias personas pueden leer información a la vez sin problema Solo una persona puede escribir en un momento dado Bloqueo Para gestionar que no haya dos sistemas escribiendo a la vez Se utiliza una señal (especie de semáforo): - Se puede leer sin problema - Alguien puede escribir, hay que esperar un momento - Se está escribiendo, no se puede acceder Write-Ahead logging (WAL) Modelo tradicional: - Si alguien escribe, nadie más puede usar el cuaderno - Funciona si hay pocas escrituras Modelo Write-Ahead Logging - Funciona como si hubiese 2 cuadernos - Uno para leer - Uno para apuntar los cambios antes de pasar al principal - Permite que se pueda leer y escribir al mismo tiempo •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 Actualización de estadísticas Reconstrucción de la base de datos VACUUM Normalización de bases de datos Pautas para diseñar las tablas de una base de datos Ventajas de normalización: Evitar redundancias Mejorar consistencia Reducir errores Facilita actualizar la información Problemas Puede afectar al rendimiento Balance normalizar/desnormalizar Primera forma normal (1NF) Eliminar valores repetidos en celdas Cada columna debe tener un único valor Cada fila es única y clara Id Nombre Apellidos Tlfnos uo234 Jose Torres 660123456, 985102030 uo512 Ana Cardo 660987654 Id Nombre Apellidos uo234 Jose Torres uo512 Ana Cardo Id Tlfnos uo234 660123456 uo234 985102030 uo512 660987654 Segunda forma normal (2NF) No repetir información que depende solo de una parte del registro Cuando una tabla tiene una clave formada varias columnas Los datos deben depender del registro completo, no de una parte Ejemplo Id Nota Nombre Apellidos uo234 7 Jose Torres uo512 8 Ana Cardo Id Nota uo234 7 uo512 8 Id Nombre Apellidos uo234 Jose Torres uo512 Ana Cardo Planes de ejecución A la hora de realizar una consulta, el sistema crea un plan de ejecución Estimación del coste de las diferentes posibilidades de ejecución Intenta predecir basándose en cuántos elementos hay en cada tabla EXPLAIN QUERY PLAN permite obtener información del plan de ejecución Ejemplo de plan de ejecución Obtener información sobre el plan de ejecución de una consulta: EXPLAIN QUERY PLAN SELECT a.nombre, a.apellidos, c.nombre AS curso, n.nota FROM alumnos a JOIN notas n ON a.id = n.id_alumno JOIN cursos c ON n.id_curso = c.id WHERE a.apellidos = 'García' AND c.nombre = 'Álgebra' AND n.nota > 8; |--SCAN n |--SEARCH a USING INTEGER PRIMARY KEY (rowid=?) `--SEARCH c USING INTEGER PRIMARY KEY (rowid=?) Sin índices Con índices: |--SEARCH c USING COVERING INDEX idx_cursos_nombre (nombre=?) |--SEARCH n USING INDEX idx_notas_curso_nota (id_curso=? AND nota>?) `--SEARCH a USING INTEGER PRIMARY KEY (rowid=?) CREATE INDEX idx_alumnos_apellidos ON alumnos(apellidos); CREATE INDEX idx_cursos_nombre ON cursos(nombre); CREATE INDEX idx_notas_alumno ON notas(id_alumno); CREATE INDEX idx_notas_curso_nota ON notas(id_curso, nota); Estadísticas de la base de datos En SQLite, el comando ANALYZE permite actualizar estadísticas de base de datos Es un comando costoso y no suele ejecutarse automáticamente Tras ejecutar ANALYZE se crea la tabla sqlite_stat1 Permite que el sistema pueda optimizar los planes de ejecución Recomendación: Ejecutar el comando ANALYZE periódicamente Ejecutar PRAGMA optimize; después de cambios en esquemas de Base de datos En otros sistemas, el comando es UPDATE STATISTICS Reconstrucción de base de datos El comando VACUUM permite reconstruir el fichero de la base de datos Puede ser útil para compactar el espacio tras borrar muchos registros Desfragmentar la base de datos Es una operación costosa: Bloquea la base de datos Requiere espacio de trabajo temporal •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 Errores en bases de datos Bases de datos = corazón de los sistemas informáticos Fallos en bases de datos pueden afectar a todo el sistema Ejemplos… Otros ejemplos fallos de bases de datos… Fecha Fallo Enlace a noticia Comentarios Coste estimado 2023 Fallo en Toyota The Guardian El disco duro se llenó 350 -400 millones de $ 2023 FAA vuelos Enlace Wikipedia Fichero base datos corrupto 32.000 vuelos retrasados > 150 millones de dólares Manejo de errores y excepciones Posibles errores Violaciones de restricciones Uso de disparadores (triggers) Tabla con log de errores mediante triggers Copias de seguridad Posibles errores Al trabajar con bases de datos hay múltiples factores que pueden fallar - Ejemplos: - Peticiones que están mal realizadas - La base de datos está ocupada (database is locked) - Algo falla durante la escritura - La aplicación hace un mal uso de la base de datos El sistema puede devolver errores antes que arriesgar la integridad Errores de integridad Al definir la base de datos se pueden definir ciertas restricciones - Errores habituales: - Clave duplicada: registrar 2 veces el mismo registro - Valores no válidos: datos que no cumplen las reglas - Faltan datos obligatorios: campos vacíos declarados como NOT NULL Mejores prácticas sobre copias de seguridad Análisis de frecuencia de backups Automatización: no confiar en procesos manuales Verificación de integridad después de cada backup Probar restauraciones regularmente Almacenamiento de backups (local, nube, híbrido) Cifrado para datos críticos Incluir archivos WAL si existen Etiquetar backups con fecha y hora No copiar mientras la base de datos está en uso Más allá de SQL Otros modelos de datos Bases de datos de grafo RDF SPARQL y RDF Triple Stores Wikidata RDF y Web Semántica RDF surgió en 1998 Marco para describir recursos Se basa en tripletas: Sujeto - Predicado - Objeto Objetivo: Web Semántica URIs para predicados SPARQL Lenguaje de consultas para RDF Similar a SQL, pero para RDF Recomendación W3C Wikidata Grafo de conocimiento que da soporte a Wikipedia Capturar conocimiento de la humanidad de forma estructurada Fin de la presentación