Administrador de BD

Page 1

Qué hace un administrador de bd?

El admin de bd tiene la responsabilidad de operar y mantener los sistemas de información de las organizaciones.

Lenguaje estructurado de consulta

Qué es SQL? Lenguaje Estructurado de Consulta

Lenguaje de Definición de Datos (LDD)

CREATE: crear objetos de datos como bd, tablas, vistas

ALTER: modificar la estructura de un objeto

DROP: elimina un objeto de la bd

TRUNCATE: borra el contenido de una tabla

Lenguaje de Manipulación de Datos (LMD)

INSERT: agregar registros a una tabla

UPDATE: actualizar o modificar registros

DELETE: borrar registros de una tabla

SELECT: busca información en una bd

Modelo Relacional

Primer atributo o campo será ocupada por la llave primaria.

Después se pondrán las llaves foráneas.

Al final los demás atributos.

De uno a uno

De uno a muchos

De muchos a muchos

De uno a cero o muchos

De muchos a cero o muchos

Construcción de un modelo relacional

Concebir toda su lógica de operación.

Pasos para crear un modelo relacional

Define las entidades o tablas necesarias en el modelo relacional.

Establece las relaciones y pon su cardinalidad.

Normaliza hasta la tercera forma normal.

Elige atributos, no almacenes datos que se puedan inferir de otros (la edad se puede inferir de la fecha de nacimiento).

Tabla cruzada cuando se tenga "muchos a muchos" o "a muchos o cero".

Nota: las dos llaves primarias de las tablas formarán la nueva llave primaria de la tabla cruzada.

Nota: las llaves foráneas van en la tabla que lleva la cardinalidad "a muchos" o "a muchos o cero".

SQL

Page 2

Almacén de datos

Qué es una Data WereHouse?

Es una bd orientada al análisis de información, solo se enfoca en extraer y organizar información importante almacenada en otras bd.

Características

Orientado a temas: todos los datos deben estar relacionados.

Variante en el tiempo: los cambios de los datos en el tiempo deben quedar registrados.

No volátil: la información no se modifica ni se elimina solo es de lectura.

Principio de separación de los datos

Se deben separar los datos usados en operaciones en la bd, de los datos que se guardan en el almacén, para que nunca coincidan.

ETL

Extracción: obtener información deseada de datos almacenados.

Transformación: adecuarlos a los esquemas de data werehouse.

Carga: depositar los datos en el almacén.

Data marts

Son bd departamentales que se alimentan de data werehouse, con el fin de evitar una búsqueda exhaustiva por parte del sistemas y así recibir información más rápido.

Esquema de almacenes

Esquema de estrella

hechos: recaen en la 2nda forma formal, vista como tabla cruzada entre varias entidades y la llave primaria queda conformada por las llaves primarias de las dimensiones.

dimensiones: sólo existe una tabla de dimensiones por dimensión.

Esquema de Copo de Nieve

Mantenimiento de dimensiones.

Cumple con la 3era forma normal.

Ahorra espacio en memoria.

Las sentencias de consulta incrementan la dificultad y por lo tanto tienen un bajo rendimiento.

Se crean tablas para normalizar las dimensiones.

Esquema de Constelación

Existe más de una tabla de hechos.

Flexibilidad

Difícil de mantener por el crecimiento de datos.

Page 3

Funciones en SQL

Son un conjunto de instrucciones que se ejecutan al ser llamadas desde un programa o procedimiento, estos reciben datos de entrada que son transformados para después arrojar un resultado.

Nota: las funciones deben tener un nombre único, para evitar que el sistemas sufra confusiones.

Tipos

Escalares: devuelven un único valor de cualquier tipo de datos.

Sintaxis

CREATE FUNCTION Nombre de la función (Nombre y Tipo dato)

RETURNS Tipo de dato

AS

BEGIN

Código de la función

END

Ejemplo

CREATE FUNCTION CalculoIVA (@Price money) RETURNS money

AS

BEGIN

DECLARE @IVA money

SET @IVA = @Price * 1.16

RETURN @IVA

END

Ejecutar la función

SELECT ProductID, ProductName, UnitPrice, dbo.CalculoIVA (UnitPrice) AS IVA FROM Products

Nota: Las funciones siempre deben ejecutarse dentro de una sentencia como SELECT, no se pueden ejecutar solas.

Funciones en línea: regresan un conjunto de resultados correspondientes a una sentencia SELECT. Por lo tanto, el resultado es un tabla.

Sintaxis

CREATE FUNCTION Nombre de la función (Parámetro de entrada) RETURNS Tipo de dato

RETURN (Código de la función)

Ejemplo

CREATE FUNCTION CountryCustomers (@Country nvarchar (15)) RETURNS table

RETURN (SELECT CustomerID, CompanyName, Country FROM Customers

WHERE Country= @Country)

Ejecutar

SELECT * FROM dbo.CountryCustomers ('México')

Nota: no se pueden usar sentencias ORDER BY dentro de una función en línea.

Funciones en línea de múltiples sentencias: el resultado esta compuesto por la ejecución de varios SELECT, por lo general se utilizan cuando se requieren de mayor lógica de proceso.

Sintaxis

CREATE FUNCTION Nombre de la función (Parámetro de entrada) RETURNS Nombre variable table (Estructura de la tabla)

AS

BEGIN

Código de la función

END

Ejemplo

CREATE FUNCTION CountryCustomers2 (@Country nvarchar (15)) RETURNS @TableCountry table (CustomerID nvarchar (15),

CompanyName nvarchar (40),

ContactName nvarchar (30),

Country nvarchar (15))

BEGIN

INSERT INTO @TableCountry

SELECT CustomerID, CompanyName, ContactName, Country FROM Customers

WHERE Country = @Country

RETURN

END

Ejecutar

SELECT * FROM dbo.CountryCUstomers2 ('Argentina')

Page 4

Triggers

Activan procesos automáticos en la bd, al utilizar una instrucción DML como INSERT, DELETE o UPDATE.

Está anclado solo a una tabla para proteger a la bd de alteraciones.

Sintaxis

CREATE TRIGGER Nombre del trigger

ON Nombre de la tabla

AFTER DML (INSERT, DELETE, UPDATE)

AS

BEGIN

END

Tiempo de activación

FOR

TRIGGER

DML

AFTER

DML

TRIGGER

Ejemplo

Se requiere que cuando exista una inserción en la tabla Order Details se reste la cantidad de productos comprados de la tabla Products.

CREATE TRIGGER StockUpdate

ON OrderDetails

FOR INSERT

AS

BEGIN

DECLARE @Cantidad smallint, @ProductoID int, @Stock smallint

SELECT @Catidad=Quantity, @ProductoID=ProductID FROM inserted

SELECT @Stock=UnitsInStock FROM Products WHERE ProductID=@ProductoID

UPDATE Products SET UnitsInStock =@Stock - @Cantidad WHERE ProductID=@ProductoID

END

Nota: para cambiar el trigger poner ALTER en lugar de CREATE.

Ejecución

INSERT INTO OrderDetails (OrderID, ProductID, Quantity) VALUES (10248,14,10)

Page 5

Procedimientos Almacenados

Es un conjunto de instrucciones que son ejecutadas cuando lo decidas, pueden aceptar parámetro de entrada y generan o no resultados.

EXECUTE CustomerCountry

Por lo general siempre regresan un valor de estado para comprobar si la operación fue concluida exitosamente.

Dentro de ellos se pueden ejecutar otros procedimientos almacenados.

CREATE PROCEDURE Pro0

AS

EXECUTE Pro1

GO

El usar código frecuente:

Programas más sencillos

Mejor rendimiento

Crear un Procedimiento Almacenado

CREATE PROCEDURE Nombre del procedimiento

AS

Código a guardar

GO

Ejecutar el procedimiento

EXECUTE Nombre del procedimiento

Ejemplo

Crear un procedimiento almacenado que sólo traiga a todos los clientes mexicanos.

CREATE PROCEDURE CustomerCountry

AS

SELECT * FROM Customers

WHERE CustomerCountry = 'México'

GO

Nota: Para modificar el procedimiento almacenada poner ALTER en lugar de CREATE.

Diferencias entre Funciones y Procedimientos Almacenados

Los procedimientos son llamadas únicamente cuando tu lo decidas, mientras las funciones son llamadas dentro de otras sentencias.

Con lso procedimeintos almacenadas se pueden restrigir acciones de usuarios a un nivel mas especifico.

Procedimientos con parámetros de entrada

CREATE PROCEDURE InsertUpdate_Customers @CustomerID nchar(5), @CompanyName nchar(5), @ReturnID nchar(5) OUTPUT

AS

BEGIN

Código

END

Ejemplo

Crear un procedimiento almacenado que inserte un nuevo cliente a la tabla de Customers, en el caso que ya exista actualizarla. El procedimiento envía un mensaje si se actualizó el registro o si se insertó.

CREATE PROCEDURE InsertUpdate_Customers @CustomerID nvarchar (5), @CompanyName nvarchar (40), @ReturnID nvarchar (5) OUTPUT

AS

BEGIN

UPDATE Customers SET Customers.CustomerID=@CustomerID Customers.CompanyName=@CompanyName

WHERE Customers.CustomerID=@CustomerID

PRINT ('Se realizó un UPDATE')

END

ELSE

BEGIN

PRINT ('Se realizó un INSERT')

END

SELECT @ReturnID=CustomerID FROM Customers WHERE Customers.CustomerID=@CustomerID

END

Ejecutar

Nota: primero se debe crear una variable que obtenga el valor del retorno del procedimiento, por lo tanto debe tener el mismo tipo de datos y longitud.

DECLARE @back nvarchar(5)

EXECUTE InsertUpdate_Customers 'ABABA','Puzzle', @back OUTPUT

Page 6

Transacciones

Una transacción es un conjunto de órdenes que se ejecutan formando una unidad indivisible de trabajo, lo que significa que el proceso no puede ser detenido ni dejado a medias, si empieza debe terminar. Si por alguna causa si el sistema cancela la transacción, este deshará las órdenes ejecutadas hasta dejar la bd es su estado inicial.

Principios ACID

Atomicidad: asegura que la operación se realice en su totalidad o no se realice.

Consistencia: asegura que solo se empieza aquello que se puede acabar.

Aislamiento: asegura que una operación no puede afectar a otras.

Durabilidad: asegura que una vez se realiza la operación está persistirá y no se podrá deshacer aunque falle el sistema.

Crear una transacción

BEGIN TRANSACTION: específica que va a empezar una transacción.

BEGIN: Inicio

COMMIT: le indica a la bd que la transacción fue completada con éxito.

ROLLBACK: indica que se a presentado un falló y debe restablecer la bd.

Ejemplo

BEGIN TRANSACTION

UPDATE Customers SET PostalCode = 56640 WHERE CustomerID = 'AAAAA'

INSERT INTO Customers (CustomersID, CompanyName) VALUES ('AAAAA','Manzana')

IF(@@ERROR <> 0)

BEGIN

ROLLBACK

PRINT ('ERROR')

END

ELSE

BEGIN

COMMIT

PRINT ('BIEN')

END

Page 7

Consultas en SQL

DISTINCT: elimina los registros duplicados de las columnas abarcadas en SELECT.

Ejemplo

SELECT DISTINCT [Order Details].ProductID, Products.ProductName FROM [Order Details]

INNER JOIN Order ON [Order Details].OrderID = Orders.OrderID

INNER JOIN Products ON [Order Details].ProductID = Products.ProductID

WHERE OrderDate >= '04-07-1996'

AND OrderDate <= '05-07-1996'

ORDER BY [Order Details].ProductID

Sub-Consultas: se puede agregar la sintaxis de otra consulta a la instrucción complementaria WHERE, para comparar los resultados que arroje.

Sintaxis

SELECT DISTINCT Campo1, Campo2 FROM TablaA

INNER JOIN TablaB ON LlavePrimaria = LlaveForanea

WHERE 'Valor' IN (SELECT Country FROM Suppliers WHERE Products.SupplierID = Suppliers.SupplierID)

Ejemplo

Realizar una consulta para obtener los productos, cuyo nombre empieza con la letra "L" de proveedores Estadounidenses.

SELECT DISTINCT [Order Details].ProductID, Products.ProductsName FROM [Order Details]

INNER JOIN Products ON [Order Details].ProductID =Products.ProductID

WHERE 'USA' IN (SELECT Country FROM Suppliers WHERE Products.SupplierID = Suppliers.SupplierID)

AND ProductName LIKE 'L%'

INTO: es una de las instrucciones complementarias de SELECT, esta crea una tabla en la bd y le agrega los resultados de la búsqueda que haya realizado. La arquitectura de está tabla tendrá los campos del SELECT pero no estarán referenciados a ninguna otra tabla.

Sintaxis

SELECT campo1, campo2, campo3

INTO nueva tabla

FROM dbo.categories

Nota: si la información se encuentra en más de dos tablas, utiliza la sintaxis INNER JOIN. Para verificar que una tabla no existe en la bd utiliza la función del sistema OBJECT_ID, la cual entrega un valor null, si el resultado es un valor not null, significa que la tabla existe.

Ejemplo

Crear una tabla que tenga los campos CustomerID y CompanyName.

IF OBJECT_ID ('CustomerCopy') IS NOT NULL

BEGIN

DROP TABLE CustomerCopy

END

SELECT CustomerID, CompanyName

INTO CustomerCopy

FROM Customers

GROUP BY: es una instrucción complementaria de SELECT y sirve para agrupar un conjunto de registros en una consulta.

Sintaxis

SELECT campo01, campo03 FROM tabla01

INNER JOIN tabla02 ON tabla01.campo01= tabla04.campo06

GROUP BY campo01 ORDER BY campo03

Ejemplo

Un cliente quiere saber el total de ventas por categoría de producto y de región realizadas en la tienda hasta el momento.

Nota: otra forma de cruzar tablas sin usar INNER JOIN.

SELECT RegionDescription, CategoryName, SUM ([Order Details].UnitPrice * [Order Details].Quantity) AS total

FROM Region, Territories, EmployeesTerritories, Employees, Orders, [Order Details], Products, Categories

WHERE Region.RegionID = Territories.RegionID

AND Territories.TerritoryID = EmployeeTerritories.TerritoryID

AND EmployeesTerritories.EmployeeID = Employees.EmployeeID

AND Employees.EmployeesID = Orders.EmployeeID

AND Orders.OrderID = [Order Details].OrderID

AND [Order Details].ProductID = Products.ProductID

AND Products.CategoryID = Categories.CategoryID

GROUP BY RegionDescription, CategoryName ORDER BY Region Description

Reglas para optimizar consultas

Usar alias en consultas.

Utiliza DISTINCT sólo cuando sea necesario, ya que la consulta se vuelve muy costosa en procesamiento.

Traer sólo las columnas que ocuparás.

ORDER BY es altamente costosa en procesamiento, es mejor ordenar la tabla después de haber accedido a la base de datos.

Evita realizar sub-consultas pues acapara el procesamiento del servidor. Si es necesario utilizarla evita el NOT IN; mejor cambia la sintaxis para que solo uses IN.

La instrucción LIKE también debe ser evitada , ya que esta comprara los caracteres seleccionados en toda la tabla; en consecuencia la búsqueda puede tardar tiempo.

Page 8

ETL - Extracción de datos en datawerehouse

Diseño de ETL: la arquitectura de un DW es diferente a la de una BD relacional, y por eso los datos deben ser transformados para después ser cargados. Un DW debe estar diseñado considerando las BDs de donde extraerá la información.

Extracción de datos: una vez creado el diseño se debe crear el DW en un software gestor.

Pasos

Abrir la herramienta Bussines Intellint.

Generar un nuevo proyecto de servicios de integración.

Asigna un nombre al proyecto y guardarla en una carpeta definida solo para este propósito.

Crear un nuevo origen de datos en la carpeta de orígenes de datos, y elige la BD para extraer la información.

Genera una nueva vista en la carpeta de vistas de orígenes de datos, seleccionando el nuevo origen creado, agrega todas las vistas de tabla y después nómbrala para que aparezca el diagrama relacional.

Renombra el archivo en la carpeta de paquetes.

Dar clic derecho en la parte inferior de la carpeta y elige nueva conexión OLE DB, escribe un punto para seleccionar el localhost, elige la BD del almacén de datos.

Una vez teniendo la conexión de datos dar clic derecho en ella, selecciona nueva conexión desde origen de datos y elige la BD de la tienda.

ETL - Transformación de datos en data werehouse

Pasos

Selecciona y arrastra Ejecutar SQL.

Después da clic derecho en el recuadro para evitar la tarea.

Elige la conexión del DW, escribe el código SQL para limpiar las tablas del DW. (DELETE para las dimensiones y TRUCATE TABLE para tablas hechos)

Arrastra una tarea del flujo de datos y editala.

Después arrastra un origen de OLE DB, copiar columnas y destino OLE DB.

Edita origen OLE DB y elige la BD de la tienda, en modo de acceso elige Comandos SQL, genera una consulta y en el icono de tabla, elige las tablas que utilizaste para crear la dimensión en el DW, posteriormente selecciona los campos que conforman a la dimensión.

Edita destino OLEDB y elige la BD del DW, en modo de acceso de datos tabla o vista elige la tabla del DW a poblar, verifica esto en asignaciones.

Úne los rectángulos del origen, copiar columnas y destinos.

En la dimensión de tiempo se debe realizar otro procedimiento para que la tabla ofrezca la información que desea analizar el cliente ya sea por dia, semana, mes, trimestre, cuatrimestre o año.

Edita la tabla para poblar la dimensión de tiempo y así modificar el origen OLE DB, selecciona la BD de la tienda, en modo de acceso elige comando SQL, genera una consulta y en el icono de tabla elige las tablas que utilizaste para crear la dimensión en el DW, después selecciona los campos que conforman a la dimensión, escribe el código de sql de la consulta a la tabla, con la información de la fecha debes generar los campos de la tabla destino, campos como día, semana, año, etc.

Edita el bloque de destino OLE DB y selecciona la BD del DW, en acciones elige los campos que no hayan sido asignados a la tabla destino.

ETL - Carga de datos en data werehouse

Los datos se cargan a la tabla de hechos.

Pasos

Arrastra una nueva tarea Ejecutar SQL hacia la pestaña de flujo de control, editala y elige una conexión al DW, escribe en SQL Statement el código que vas a utilizar ya que requieres las tablas de la BDs de la tienda.

Genera un INSERT a la tabla de hechos y llena cada columna con un SELECT, genera un INNER JOIN en este SELECT por cada tabla de dimensiones, también se debe igualar la llave foránea de la tabla correspondiente al atributo ID de la tabla de dimensiones que no es su llave primaria.

Nota: se puede editar el código en ayuda de estudio.

Page 9

Data Marts

Un data mart es una versión pequeña de un DW, trae información específica de algunos departamentos de una organización. Está pensado para satisfacer las necesidades muy específicas de un negocio.

Tipos

Dependiente: la información es extraída inicialmente desde una BD OLTP, para poblar el DW.

Independiente: la información es extraída directamente de una BD OLTP y llevada al data mart, sin tener que depender de un DW. Es conveniente para empresas pequeñas.

Híbrido: la información necesaria para el data mart es tomada de diferentes fuentes tanto de la BD OLTP como el DW, incluso de hojas de Excel.

Cubo OLAP (Procesamiento Analítico OnLIne)

Para qué sirven?

Sirven para hacer más eficiente la BD ya que preprocesa la información y te permite tener acceso instantáneo a esta.

Funcionalidad

Cubo constituido de diferentes cajas, cada una de ellas contiene un total de la información analizada.

Dimensiones y medidas

Categoría de productos (confección, condimento, bebidas)

Tiempo ( abril, mayo, junio, julio)

Medida (stock, pendientes, costos)

Acceder a la información

Se deben hacer la intersecciones correspondientes y se obtiene el valor.

Jerarquías en dimensiones

Relación Padre - Hijo (año, trimestres) (País, Estado, Municipio)

Operaciones "Drill Down" y "Drill Up"

Para poder desplazarse entre jerarquías y navegar entre la información son necesarias las siguientes operaciones entre los cubos.

Drill Up: Sirve para compactar los datos, por ejemplo si la dimensión de tiempo en el cubo se encuentra en meses hacer un Drill Up para que los datos se muestren en años.

Drill Down: Sirve para ver los datos con más detalle, se puede hacer un Drill Down de años a trimestres.

Creación de cubos OLAP

Configuración de origen de datos

Crear un nuevo proyecto, haciendo clic en nuevo proyecto que se encuentra en la barra de herramientas.

Selecciona la plantilla de proyecto analisis service, asígnale un nombre al proyecto.

Conexión a los datos

Clic derecho en origen de datos, localizado en el explorador de soluciones y selecciona nuevo origen de datos.

Clic en nuevo para crear una conexión con la BD, selecciona la instancia de la BD en donde se encuentra el almacén de datos que quieres trabajar, selecciona el almacén de datos y presiona aceptar.

Selecciona el origen de datos que agregaste, presiona siguiente.

Selecciona utilizar la cuenta del servidor, clic en siguiente.

Selección de tablas

Clic derecho en vistas de origen de datos, seleccionar nueva vista de origen de datos.

Selecciona el almacén de datos que agregaste.

Agregar las tablas y vistas con las que vas a trabajar.

Creación del cubo

Dar clic derecho en la carpeta del cubo y seleccionar nuevo cubo.

Selecciona usar tablas existentes.

Selecciona las tablas que quieres mejorar como medidas, normalmente se marcan las tablas de Hechos.

Marca las medidas y dimensiones que vas a utilizar.

Selección de atributos en dimensiones

Doble clic sobre la dimensión y arrastra los atributos de las tablas hacia la sesión de atributos del cubo.

Jerarquías

Arrastra el atributo padre de la tabla a la sesión de jerarquía.

Arrastra a sus hijos respetando el orden de la jerarquía.

Asígnale un nombre

Procesamiento del cubo

Clic derecho en el cubo que creaste y selecciona procesar.

Presiona Ejecutar.

Acceder a los datos desde excel

Abrir una hoja de cálculo vacía.

Clic en otras fuentes en la pestaña de datos y selecciona desde Analis Service.

Pon un punto (.) como nombre de servidor.

Selecciona la BD y después el cubo en el que trabajaste.

Selecciona informa gráfico y tabla dinámica.

Page 10

Índices

En una BD relacional existen dos caminos para recorrer una tabla, una es de principio a fin hasta localizar los datos y otra es utilizar índices para referenciar la información, así siempre sabrás exactamente donde se encuentra.

Clustered Index (índice agrupado): este define el orden físico de la tabla, es decir la forma en que se acomodaran los registros guardados, solo se puede tener uno por tabla. Ejemplo directorio telefónico.

Nonclustered Index (índice no agrupado): este genera una tabla separada que contiene los punteros que indican el lugar donde se almacenan los elementos indizados. Parecido a la tabla de contenido de un libro. El límite de índices no agrupados que se pueden asignar por tabla oscilan entre los 249 - 999 de acuerdo a la versión de SQL.

Creación de índices en SQL

CREATE CLUSTERED INDEX Nombre relacionado al índice ON

Nombre de la tabla (Nombre del campo a indexar);

Ejemplo

CREATE NONCLUSTERED INDEX Apellidos_indice ON

dbo.Directorio(Apellido);

Script

Un script es un conjunto de instrucciones que te sirven para comunicarle al motor de base de datos qué, cómo y en qué orden debe de desempeñarse.

Un script te sirve para:

• Documentar, mediante comentarios, cambios que hayas realizado en la base de datos

• Realizar copias de bases de datos, de su estructura y datos, de tal manera que puedes recuperar la información si lo requieres.

• Saber cómo está construida una base de datos

Comentarios

Existen dos formas para crear un comentario en un script de SQL,:

• Comentario de bloque

Escribe una diagonal seguida de un asterisco y corchetes. Después de estos caracteres, escribe tu comentario.

/*[Contenido de comentario]*/

• Comentario de línea

Inicia la línea escribiendo 2 guiones, dos corchetes y después tu comentario.

–-[Contenido de comentario]

Documentación de objetos en scripts

Es importarte que al crear o modificar objetos en una base de datos, reportes los cambios en un script utilizando comentarios.

Para documentar objetos en SQL escribe un comentario de línea antes de la sentencia “Create”, como se muestra a continuación:

“--Descripción: Regresa el total de ventas de determinado mes”

Realiza este tipo de comentarios en cada objeto de la base de datos para llevar un control de cambio.

Documentación General de base de datos

Ya que tienes el script en pantalla, agrega un encabezado que contenga:

1. Nombre de la base de datos

2. Versión del motor SQL en el que corre

3. Nombre del que edita la base de datos

4. Fecha de edición

5. Enlista que objetos fueron creados

6. Enlista que objetos fueron modificados. Sin necesidad de detallar, ya que el detalle lo tienes que hacer en el script de cada objeto.

Al finalizar, da clic en “Salvar”