Fundamentos de Bases de Datos y Sistemas de Información

Fundamentos de Bases de Datos

Cláusulas SQL

WF (campo) OVER (PARTITION BY campo ORDER BY campo) AS name

WITH name AS (SELECT campo FROM tabla)

SELECT campo FROM name

PIVOT (F (campo) FOR campo IN ([col], [col])) name

Administración de Bases de Datos (DBA)

El DBA autoriza y regula el acceso, monitorea y optimiza, adquiere software y hardware, y asegura la disponibilidad.

Bases de Datos Transaccionales

• Envío y recepción de datos rápidos.
• Para análisis de calidad y datos.
• Asegurar consistencia.
• Evitar transacciones incompletas.
• Permiten llevar muchas transacciones.
• Capturar datos históricos.
• Rápidas y operan con baja latencia.

• Limitación para generar informes.
• Limitación para el historial de datos.

Data Warehouse (DWH)

Fuente de datos para Business Intelligence, obtiene información de sistemas, limpia datos, los une, aplica reglas y los almacena en estructura OLAP.

Data Lake (DL)

Repositorio centralizado de datos. Almacena datos estructurados y no estructurados, no transforma ni optimiza. Puede gestionar datos en tiempo real.

Collation

Patrón de bits utilizado para almacenar caracteres, reglas utilizadas para ordenar y comparar caracteres, proporciona la distinción de caracteres. CS/CI, AS/AI.

Si comparas dos collates distintos da error de intercalación, que se resuelve convirtiendo explícitamente a otro collate.

Bases de Datos en Memoria

• Velocidad
• Por el IoT surge la necesidad de procesar datos en tiempo real.
• Datos volátiles.
• Estructura simple.
• Ideal para apps con accesos a internet con pocos datos.

Bases de Datos en Disco

• Lenta.
• Datos persistentes.
• Estructuras complejas.
• Aplicaciones con muchos datos.
• Aplicaciones de gestión de clientes y usuarios.

Tabla Variable

• Alcance en el modelo de ejecución.
• Mantiene en memoria y después va a tempdb.
• Declare @
• Su contenido se pierde.
• No tienen índices ni estadísticas.

Tabla Temporal

• Alcance de sesión.
• Almacena en tempdb.
• Create #
• Su contenido se pierde al cerrar sesión.
• Admite índices y estadísticas.

Tabla en Memoria

• Almacena en memoria.
• Create con cláusula.
• Su contenido no se pierde.
• Admite índice no clúster.

Interfaces de Programación de Aplicaciones (API)

Contrato que permite el acceso a datos de un sistema informático desde otro.

Abiertas: Disponibles para uso general, requieren clave para controlar solicitudes que atiende por cliente.

Privadas: Se usan dentro de una organización.

Socios: Permiten que una organización intercambie información con proveedores, clientes y socios.

Tipos: SOAP, XML, JSON, REST.

Difundidas: API REST o API HTTP.

Plan de Ejecución

Describe el conjunto de operaciones que el motor ejecuta para obtener los datos de una consulta. Detalla a qué, en qué orden y de qué manera accederá. Excelente punto para la resolución de problemas de performance. Se almacena en una caché del motor, donde la próxima vez se verifica si ya existe.

No existe para tablas temporales.

Fase de Compilación

Query parser: Verifica sintaxis y genera parse tree.

Algebrizer: Enlaza objetos y genera query processor tree.

Optimizer: Optimiza y genera plan, utiliza heurística. Evita consultas ad hoc.

Fase de Ejecución

Ejecuta el plan.

Índices

Recurso para acceder rápidamente a datos. Generan copias de los campos, menos el clúster.

Índice Clúster

Determina en qué orden se guardan los registros, solo uno por tabla pero puede ser compuesto. Si no tiene, es una tabla heap.

Índice No Clúster

Genera una copia de los campos indexados, se afecta en cada insert, delete y modify.

Índice de Cobertura

Es un no clúster que incluye todos los campos.

CREATE NONCLUSTERED INDEX nombre ON tabla (campo1, campo2) INCLUDE (campo3, campo4)

Fill Factor

Factor de relleno, dejar un espacio libre en cada hoja reduce el page split.

Page Split

Cuando se llena una estructura se genera una nueva y se enlaza a la anterior, agrega hoja, mueve datos, marca como inválidos los datos de la otra hoja movidos, actualiza referencias para que apunten a la nueva. Si se insertan entradas nuevas se usa ese espacio disponible.

Si la clave del índice es creciente es bueno usar 100% fill factor.

Si no lo es hay que evaluar la fragmentación de los índices buscando reducimos el fill factor gradualmente.

Estadísticas

Información generada y mantenida por el motor acerca de la distribución de los valores de las tablas. El optimizador las utiliza para generar los planes, obtiene de ellas la cardinalidad (estimación de cantidad de filas).

Tienen encabezado: información general de un conjunto de estadísticas.

Grafo de densidad: Selectividad y singularidad de datos.

Histograma: Cuenta de las ocurrencias de valores particulares.

Optimización

• Si hay table scan, hacer índice.
• Si hay tabla heap, hacer índice clúster.
• Si hay diferencia entre estimadas y reales, revisar estadísticas.
• Eliminar tablas temporales.
• Verificar tipo de datos.
• Tablas temporales tienen estadísticas, variables no.

Control de Concurrencia

Trata con los problemas de aislamiento y consistencia del procesamiento de transacciones, los esquemas de control se basan en la propiedad de secuencialidad. Los niveles de aislamiento definen el grado en que una transacción se aísla de las modificaciones realizadas por otra transacción.

Problema:

Lectura sucia: una transacción lee datos escritos por otra transacción no confirmada.

Lectura no repetible: una transacción vuelve a leer datos y los encuentra modificados o eliminados por otra transacción confirmada.

Lectura fantasma: una transacción vuelve a leer datos y encuentra más datos agregados por otra transacción confirmada.

Read uncommitted: se puede leer datos modificadas por otras transacciones no confirmadas.

Read committed: no se puede leer datos modificados por otras transacciones no confirmadas.

Repeatable read: no se puede leer datos modificados por otras transacciones no confirmadas y ninguna transacción puede modificar los datos ya leídos hasta que esta transacción termine.

Serializable: no se puede leer datos modificados por otras transacciones no confirmadas, ninguna transacción puede modificar los datos ya leídos hasta que esta transacción termine y otra transacción no puede insertar filas nuevas hasta que esta transacción termine.

Bloqueo compartido: si la transacción obtiene un bloqueo sobre un elemento Q, la transacción puede leer Q pero no escribirlo.

Bloqueo exclusivo: la transacción puede leer y escribir Q.

Transacciones Distribuidas

Transacción que utilizan varias instancias. Para que se confirme todos los participantes deben garantizar que los cambios serán permanentes. Si esto no se da, se revierten los cambios.

Métricas de Performance

Nos ayudan a entender el uso de la DB y consumo de recursos.

Baseline: necesitamos una referencia para determinar un comportamiento anómalo, debemos conocer el uso normal del sistema para detectar algo raro. Usa row counts y database file IO.

Modelo de Recuperación

Es una propiedad de la DB que controla el mantenimiento del log de transacción.

Simple: el log se libera al completar la transacción sin respaldo. Solo se recuperan estados con backup.

Full: el log se libera cuando se respalda, requiere backups. Puede recuperarse a tiempo determinado.

NoSQL

• No tiene estructura definida.
• La información no se organiza en campos.
• Sistema distribuidos de datos.

BASE

Basically Available: el sistema debe funcionar a pesar de tener una falla parcial.

Soft state: los datos pueden eventualmente...

Eventually Consistent: puede haber momentos que la DB sea inconsistente.

CAP

Consistency: lectura coherente, los datos están sincronizados y replicados en todos los nodos.

Availability: obtiene una respuesta válida y rápida aunque haya nodos inactivos.

Partition tolerance: capacidad para permanecer estable y continuar procesando solicitudes a pesar de que haya una partición entre nodos.

Mongo solo tiene CP.