ADMINISTRACIÓN DE BASES DE

DATOS

Ingeniería en Sistemas

Objetivo

El alumno será capaz de comprender los procesos de almacenamiento, acceso a la información y administración de un sistema administrador de bases de datos (SABD, SMBD, SGBD, DBMS)

Temario

I. Fundamentos de

administración de bd’s

II. Administración de bd’s

III. Administración de usuarios

IV. Seguridad e integridad

V. Respaldo y recuperación

Bibliografía

Sistemas de Gestión de Bases de DatosRamakrishman, McGraw-Hill, 3ª Ed. 2007

Fundamentos de Bases de DatosSilberschatz, A. McGraw-Hill, 5ª Ed. 2006

SQL Server 2008, Paso a PasoHotek, M. Anaya, 2009

Conceptos

Base de datosEs una colección de datos relacionados con un tema u objetivo concreto

TablaEs una estructura de datos bidimensional donde es posible almacenar datos relacionados

Conceptos

campos (atributos)

regis

tros

relación

llave primaria

Conceptos

Sistema administrador de bases de datos (SABD, SGBD, DBMS)• Es una colección de programas

que permiten a los usuarios crear y mantener bases de datos

• Es un software de propósito general que facilita el proceso de definir, construir, manipular y compartir bases de datos entre varios usuarios y aplicaciones

Conceptos

Conceptos

• Aridad o grado

• Cardinalidad

• Llave primaria y secundaria

• Dominio• Procedimiento almacenado• Trigger (desencadenador)

Conceptos

Bno Street Area City Postcode Tel_No Fax_No

B5 22 Deer Rd Sidcup London SW1 4EH 0171-886-

12120171-886-1214

B7 16 Argyll St Dyce Aberdee

n AB2 3SU 01224-67125 01224-67111

B3 163 Main St Partick Glasgow G11 9QX 0141-339-

21780141-339-4439

B4 32 Manse Rd Leigh Bristol BS99 1NZ 0117-916-

11700117-776-1114

B2 56 Clover Dr London NW10 6EU 0181-963-

10300181-453-7992

grado (aridad)

card

inalid

ad

rela

ción

Tipos de datos (T-SQL)

Tipos de datos (T-SQL)

Tipos de datos (T-SQL)

Vistas

Una vista es una tabla virtual cuyo contenido está definido por una consulta.

Al igual que una tabla real, una vista consta de un conjunto de columnas y filas de datos que proceden de tablas a las que se hace referencia en la consulta y se producen de forma dinámica cuando se hace referencia a la vista.

Vistas

Utilización de vistas

• Para centrarse en datos específicos

• Para simplificar la manipulación de

datos

Utilización de vistas

• Para proporcionar compatibilidad con versiones anteriores

Procedimientos almacenados

Un procedimiento almacenado (stored procedure -SP) es un conjunto de comandos SQL que pueden almacenarse en un servidor.

Procedimientos almacenados

Similitud con funciones en programación:

• Parámetros de entrada / parámetros de salida

• Pueden llamar a otros procedimientos

• Devuelven un valor de estado a un programa que realiza una llamada

Ventajas de los SPs

• Tráfico de red reducido entre el cliente y el servidor

• Mayor seguridad

• Reutilización de código

Ventajas de los SPs

• Mantenimiento más sencillo

• Rendimiento mejorado

Sintaxis (en SQL Server)

Ejemplo (parámetro de entrada)

CREATE PROCEDURE MiProc @Ciudad nvarchar(30) AS SELECT * FROM MiTablaWHERE Ciudad = @Ciudad

EXEC MiProc @Ciudad=‘Querétaro’

Ejemplo (parámetro de salida)

SPs del sistema

SPs del sistema

SPs del sistema

Funciones (SQL Server)

• IntegradasAgregado:

AVG, COUNT, MAX, MIN, SUM, VAR, …Escalares:

Matemáticas: ABS, COS, EXP, POWER, …

De Cadena: CHAR, CONCAT, LEN, REPLACE, …

Entre otras

• Definidas por el usuario (UDF)

Funciones definidas por el usuario

Funciones (ejemplo)

CREATE FUNCTION ElevarAlCubo(@base INT)RETURNS __________ASBEGIN RETURN __________________END

Funciones (ejercicio)

Crear una función que dado un entero que represente un día de la semana, regrese el nombre literal del día

Funciones (ejercicio)

CREATE FUNCTION CalcularVolumenCubo -- dimensiones de entrada en cms (@Longitud decimal(4,1), @Anchura decimal(4,1), @Altura decimal(4,1) )RETURNS decimal(12,3) -- cms cúbicosASBEGIN RETURN ( @Longitud * @Anchura * @Altura )END

Funciones (ejercicio)

CREATE TABLE Cubos ( numero int PRIMARY KEY, color nchar(20), altura decimal(4,1), longitud decimal(4,1), anchura decimal(4,1), volumen AS (

dbo.CalcularVolumenCubo(altura, longitud, anchura) ))

Funciones definidas por el usuario

Funciones definidas por el usuario

CREATE FUNCTION MuestraInfoCubos(@num int) RETURNS TABLERETURNselect numero, volumenfrom Cuboswhere numero < @num

Triggers (desencadenadores, disparadores)

Es un objeto de base de datos que está asociado a una tabla y que se activa cuando ocurre un evento particular para dicha tabla.

• Verificaciones de valores a insertarse en la tabla• Llevar a cabo cálculos sobre los valores

implicados en una actualización

Se activa cuando se ejecuta un INSERT, DELETE o UPDATE

Triggers

• Los triggers se activan solo por sentencias SQL

• Un trigger puede llamar a un SP• Un trigger no puede ser asociada a una

tabla temporal o a una vista• No puede haber dos triggers para una

misma tabla con el mismo evento y acción en el

Triggers (sintaxis –SQL Server)

Triggers (sintaxis –MySQL)

BEFORE, AFTER

INSERT, UPDATE, DELETE

INSERT = INSERT, LOAD DATA, REPLACEUPDATE = UPDATEDELETE = DELETE, REPLACE

Triggers

CREATE TRIGGER upd_check BEFORE UPDATE ON account

FOR EACH ROWBEGIN IF NEW.amount < 0 THEN SET NEW.amount = 0; ELSEIF NEW.amount > 100 THEN SET NEW.amount = 100; END IF;END

Triggers

• Solo puede ser aplicado a una tabla• Crear/alterar/borrar una bd no está

permitido• Asociados a tablas permanentes (no

temporales ni vistas)• En MySQL no pueden contener la

instrucción CALL

III Administración de usuarios

Administración de usuarios

Inicio de sesión (IS):• Un IS es una entidad de seguridad o una

entidad que puede ser autenticada por un sistema seguro

• Los usuarios necesitan iniciar sesión para conectarse a SQL Server

• Se pueden conceder permisos a los IS’s• El ámbito de un IS es todo el motor de bd• Los permisos dentro de la bd se conceden

y deniegan al usuario de la bd, no al inicio de sesión

Administración de usuarios

Usuario de base de datos (UBD):• Un UBD es una entidad de seguridad de la

bd• El ámbito de un UBD es la bd • El UBD es la identidad del inicio de sesión

cuando está conectado a una bd• El UBD puede utilizar el mismo nombre

que el inicio de sesión, pero no es necesario

• Los permisos dentro de la bd se conceden y deniegan al UBD, no al IS

Roles de servidor para los IS’s

Nombre del rol

Permisos

bulkadmin Instrucción BULK INSERT

dbcreator Crear, modificar, quitar y restaurar cualquier base de datos

diskadmin Administrar archivos de disco

processadmin Finalizar procesos mediante la ejecución de una instancia del motor de bd

public Todos los usuarios, grupos y roles de SQLS pertenecen al rol public

securityadmin Administrar los inicios de sesión y sus propiedades (permisos de servidor y de bds: GRANT, DENY, REVOKE); reestablecen contraseñas para IS’s

serveradmin Cambiar opciones de configuración en el servidor y cerrarlo

setupadmin Agregar/quitar servidores vinculados; ejecutar algunos SPs del sistema

sysadmin Cualquier actividad en el motor de bd

Roles de bases de datos

Nombre del rol

Permisos

db_owner Todas las actividades de configuración y mantenimiento en la bd (y quitarla)

db_securityadmin

Modificar la pertenencia a roles y administrar permisos.

db_accessadmin Agregar o quitar el acceso a la bd para IS’s de Windows, grupo de Windows e IS’s de SQL Server

db_backupoperator

Copias de seguridad de la bd

db_ddladmin Cualquier comando DDL en una bd

db_datawriter Agregar, eliminar o cambiar datos en todas las tablas de usuario

db_datareader Leer todos lo datos de todas las tablas de usuario

db_denydatawriter

No pueden agregar, modificar ni eliminar datos de tablas de usuario

db_denydatareader

No pueden leer datos de las tablas de usuario