3.1 Consideraciones de diseño. 3.2 Normalización. 3.3 Integridad de bases de datos.

◦ 3.3.1 Concepto. ◦ 3.3.2 Restricciones básicas (not null, llave primaria,

orden, verificación y aserción ). ◦ 3.3.3 Integridad de entidad. ◦ 3.3.4 Integridad referencial. ◦ 3.3.5 Reglas de relación. ◦ 3.3.6 Reglas de base de datos. ◦ 3.3.7 Reglas de negocios.

3.4 Seguridad de bases de datos. ◦ 3.4.1 Concepto de seguridad. ◦ 3.4.2 Autenticación y autorización. ◦ 3.4.3 Rol y privilegios de usuarios. ◦ 3.4.4 Vistas y seguridad.

3.5 Recuperación de bases de datos. ◦ 3.5.1 Transacciones. ◦ 3.5.1.1 Definición de transacción. ◦ 3.5.1.2 Propiedades de Atomicidad, Consistencia, Aislamiento y

Durabilidad (ACID). ◦ 3.5.1.3 Estados de las transacciones. ◦ 3.5.2 Bitácora. ◦ 3.5.2.1 Tipos de bitácora. ◦ 3.5.2.2 Contenido de la bitácora.

3.6 Diccionario de datos. ◦ 3.6.1 Concepto. ◦ 3.6.2 Contenido y función. ◦ 3.6.3 Tipos.

El término integridad de datos se refiere a la corrección y completitud de los datos en una base de datos. Cuando los contenidos se modifican con sentencias INSERT, DELETE o UPDATE, la integridad de los datos almacenados puede perderse de muchas maneras diferentes. Pueden añadirse datos no válidos a la base de datos, tales como un pedido que especifica un producto no existente.

Pueden modificarse datos existentes tomando un valor incorrecto, como por ejemplo si se reasigna un vendedor a una oficina no existente. Los cambios en la base de datos pueden perderse debido a un error del sistema o a un fallo en el suministro de energía. Los cambios pueden ser aplicados parcialmente, como por ejemplo si se añade un pedido de un producto sin ajustar la cantidad disponible para vender.

Not Null (datos requeridos). Establece que una columna tenga un valor

no nulo. Se define efectuando la declaración de una columna es NOT NULL cuando la tabla que contiene las columnas se crea por primera vez, como parte de la sentencia CREATE TABLE.

Establece que la clave primaria de una tabla debe tener un valor único para cada fila de la tabla; si no, la base de datos perderá su integridad. Se especifica en la sentencia CREATE TABLE. El DBMS comprueba automáticamente la unicidad del valor de la clave primaria con cada sentencia INSERT Y UPDATE. Un intento de insertar o actualizar una fila con un valor de la clave primaria ya existente fallará.

Llave primaria Not null

MySQL:

CREATE TABLE Customer (SID integer, Last_Name varchar(30), First_Name varchar(30), PRIMARY KEY (SID));

CREATE TABLE Customer (SID integer NOT NULL, Last_Name varchar (30) NOT NULL, First_Name varchar(30));

Orden. Indica el orden de declaración de los

atributos de la tabla. Se indica en la sentencia CREATE TABLE, es importante en las sentencias de INSERT al agregar nuevas tuplas a una tabla.

Se usa para validar que los datos pertenezcan a un dominio predefinido. Se pueden hacer verificaciones más específicas, como el rango de un valor. Se usa la clausula CHECK.

Ejemplo en SQL de My SQL:

CREATE TABLE Persons(P_Id int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255),CHECK (P_Id > 0))

Es un enunciado SQL que asegura que cierta condición en la base de datos existirá siempre. Por ejemplo, en un banco la condición de que la suma de los préstamos nunca exceda a la suma de los depósitos.

“El salario de un empleado nunca debe ser mayor que el salario de su Jefe de Departamento”

CREAT ASSERTION SALARY_CONSTRAINT CHECK (NOT EXISTS (SELECT *

FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT DWHERE E.SALARY > M.SALARY ANDE.DNO=D.NUMBER AND D.MGRSSN=M.SSN))

Las restricciones de entidades aseguran la integridad de las entidades que son modeladas por el sistema. En el nivel más simple, la existencia de una clave principal es una restricción de entidad que impone la regla "cada entidad debe estar identificada de forma única".

En esta no está permitido que algún componente de la clave primaria acepte valores nulos.

La regla de Integridad referencial define que la base de datos no debe contener valores de claves foráneas sin concordancia.

Esta regla se aplica a las claves foráneas. Si en una relación hay alguna clave foránea, entonces sus valores deben coincidir con los valores de la clave primaria a la que hace referencia, o bien, debe ser completamente nulo.

Esta regla impide que, por ejemplo, en una base de datos académica, exista un profesor en un departamento inexistente, o un curso impartido por un profesor inexistente.

Suponer las tablas: Sentencia SQL para declarar la llave foránea:

CREATE TABLE ORDERS (Order_ID integer, Order_Date date, Customer_SID integer, Amount double, Primary Key (Order_ID), Foreign Key (Customer_SID) references CUSTOMER(SID));

Se refiere a los aspectos de: Orden de las tuplas en una relación : una relación se

define como un conjunto de tuplas matemáticamente, los elementos de un conjunto no están ordenados; por tanto, las tuplas de una relación no tienen orden específico.

Orden de los valores dentro de una tupla, y definición alternativa de relación : Una tupla es una lista ordenada de n valores, así que el orden de los valores de una tupla y por tanto de los atributos en la definición de un esquema de relación es importante.

Valores en las tuplas: Cada valor en una tupla es un valor atómico; esto es, no es divisible en componentes en lo que respecta al modelo relacional. Por ello no se permiten valores compuestos ni multivaluados.

Uso del modelo relacional. Acceso garantizado a cada dato. Tratamiento de valores nulos. Diccionario en línea. Uso de un lenguaje de bases de datos. Reglas de actualización de vistas. Inserción, actualización y borrado de alto nivel. Independencia física. Independencia lógica. Integridad incluida en la BD. Independencia de distribución.

Es cualquier restricción, necesidad, requerimiento, o actividad especial que debe ser verificada al momento de intentar grabar información, borrar, actualizar o consultar la ya existente.

Ejemplo, se puede definir un campo o una tabla que contenga información relacionada los clientes a los que se les vende algún determinado producto.

Tal vez, la regla indique, que las claves para determinados clientes de una determinada región empiece con A, para otros con B, etc.

3.4.1 Concepto de seguridad. Consiste en las acciones que toma el

diseñador de base de datos al momento de crear la base de datos, tomando en cuenta el volumen de las transacciones y las restricciones que tiene que especificar en el acceso a los datos; esto permitirá que el usuario adecuado sea quién visualice la información adecuada.

Autenticación. Se refiere a la tarea de verificar la identidad

de una persona o software que se conecte a una BD; es decir consiste en un nombre de usuario y una contraseña secreta que se debe presentar cuando se abra una conexión a la BD.

Por ejemplo, MySQL, encripta la información que proporciona el usuario para su autenticación.

Proceso de permitir al acceso de una persona a un sistema, a una BD, u objetos particulares de la BD (tablas, vistas, etc.).

Los usuarios pueden tener varios tipos de autorización para diferentes partes de la base de datos: ◦ Lectura. ◦ Inserción. ◦ Actualización. ◦ Borrado.

MODIFICAR EL ESQUEMA DE LA BD

◦ Índices.◦ Recursos. ◦ Alteración. ◦ Eliminación.

Primero será necesario crear los usuarios. En MySQL se puede usar la interfase gráfica para crearlos.

Un rol es un papel desempeñado por un individuo dentro de un conjunto de personas. En la base de datos, siempre existen un conjunto de personas que harán uso de ella, las acciones que pueden hacer son: visualización, modificación, agregación, eliminación de registros entre algunas otras, y la regla que permite esto es conocida como privilegio. La cual es el permiso que tienen los usuarios para realizar una operación determinada.

Características No son propiedad de nadie ni están en un esquema.

Se puede dar acceso a cualquier usuario a un rol excepto a uno mismo (reflexiva).

Pueden ser activados y desactivados, por usuarios autorizados (contraseña).

Las definiciones de roles son almacenadas en el diccionario.

Un rol puede decidir el acceso se usuario a un objeto, pero no puede permitir la creación de objetos.

Guía para la creación de roles:

Crear un rol para cada aplicación (rol de aplicación).

Crear un rol para cada tipo de usuario (rol de usuario).

Conceder privilegio de acceso a roles de aplicaciones por parte de los roles de usuario.

Dar privilegio de acceso a roles de aplicación y roles de usuario a los usuarios.

Se proporciona un grupo de roles predefinidos: connect, resource, dba, exp_full_database, imp_full_database.

A nivel de Objeto: El derecho a ejecutar una acción sobre una tabla, vista, secuencia, disparador o

procedimiento almacenado específico.

Puede incluir permisos para pasar privilegios de uno a otro usuario (with grant option).

El propietario de un objeto adquiere automáticamente todos los privilegios sobre dicho objeto.

Los privilegios son: alter, execute, delete, index, insert, references, select, update, all.

A nivel de Sistema: Derecho a ejecutar un tipo de comando sobre objetos de un esquema, objetos de

un tipo especificado, sobre el sistema o sobre un usuario.

El DBA puede tener cualquier variedad de privilegios del sistema.

Existen unos 80 privilegios distintos disponibles.

Se usa la sentencia “grant” para dar autorizaciones. El formato es:

Grant <lista_privilegios> on <lista_objetos> to <lista_usuarios>

Ejemplos: grant select on sucursal to U1, U2, U3 grant update (importe) on prestamo to U1, U3 grant select on sucursal to U1 with grant option

Se usa la sentencia “revoke”, con el formato:

revoke <lista_privilegios> on <lista_objetos> from <lista_usuarios> [restrict | cascade]

Ejemplos:Revoke select on sucursal from U1, U3 cascadeRevoke update(importe) on prestamo from U1,

U2

Las vistas son una forma de proporcionar al usuario un modelo personalizado de la base de datos. Las vistas tienen la misma estructura que una tabla: filas y columnas. La única diferencia es que sólo se almacena de ellas la definición, no los datos.

Una vista es el resultado dinámico de una o varias operaciones relacionales realizadas sobre las relaciones base. Una vista es una relación virtual que se produce cuando un usuario la consulta. Al usuario le parece que la vista es una relación que existe y la puede manipular como si se tratara de una relación base, pero la vista no está almacenada físicamente.

Las vistas son útiles por varias razones:

Proporcionan un poderoso mecanismo de seguridad, ocultando partes de la base de datos a ciertos usuarios.

Permiten que los usuarios accedan a los datos en el formato que ellos desean o necesitan.

Se pueden simplificar operaciones sobre las relaciones base que son complejas. Por ejemplo, se puede definir una vista como la concatenación de dos relaciones.

Suponer que se tiene la tabla: Tabla Customer

(First_Name char(50),Last_Name char(50),Address char(50),City char(50),Country char(25),Birth_Date date)

Se quiere crear una vista denominada V_Customer que contiene sólo las columnas First_Name, Last_Name y País de esta tabla, y solo tuplas del país “Mexico”

CREATE VIEW V_CustomerAS SELECT First_Name, Last_Name, CountryFROM Customer

WHERE Country = “Mexico”

Son los mecanismos que incorporan los sistemas de bases de datos para mantener la consistencia de la BD a pesar de: fallas de diversos tipos y acceso concurrente.

Una transacción es un programa que es tratado como una unidad lógica, y contiene varias operaciones en la base de datos.

Ejemplo de transacción, la transferencia de una cantidad de dinero de una cuenta “A” a una cuenta “B”. Operaciones: leer datos, verificar requisitos, actualizar cuenta “A”, actualizar cuenta “B”.

Una transacción en un Sistema de Gestión de Bases de Datos (SGBD), es un conjunto de órdenes que se ejecutan formando una unidad de trabajo, es decir, en forma indivisible o atómica.

Un SGBD se dice transaccional, si es capaz de mantener la integridad de los datos, haciendo que estas transacciones no puedan finalizar en un estado intermedio. Cuando por alguna causa el sistema debe cancelar la transacción, empieza a deshacer las órdenes ejecutadas hasta dejar la base de datos en su estado inicial (llamado punto de integridad), como si la orden de la transacción nunca se hubiese realizado.

Atomicidad: es la propiedad que asegura que la operación se ha realizado o no, y por lo tanto ante un fallo del sistema no puede quedar a medias.

Consistencia: es la propiedad que asegura que sólo se empieza aquello que se puede acabar. Por lo tanto se ejecutan aquellas operaciones que no van a romper la reglas y directrices de integridad de la base de datos.

Aislamiento: es la propiedad que asegura que una operación no puede afectar a otras. Esto asegura que la realización de dos transacciones sobre la misma información sean independientes y no generen ningún tipo de error.

Durabilidad: es la propiedad que asegura que una vez realizada la operación, ésta persistirá y no se podrá deshacer aunque falle el sistema.

Activa. Es el estado inicial, permanece en este estado durante la ejecución.

Parcialmente comprometida. Después de ejecutarse la última instrucción.

Fallida. Se descubre que no puede continuar con la ejecución normal.

Abortada. Después de haber retrocedido la transacción y restablecido la BD a su estado anterior al comienzo de la transacción.

Comprometida. Se completa con éxito, los cambios realizados son permanentes en la BD.

La bitácora o registro histórico (en inglés: “transaction log”, “database log” o “binary log”) es la estructura más común que usan los sistemas de BD para guardar las modificaciones realizadas.

Está formada por una secuencia de registros y mantiene un registro de todas las actividades de actualización de la BD.

El sistema la utilizada para la recuperación del sistema después de una falla y para garantizar las propiedades ACID de las transacciones.

El sistema guarda diferentes tipos de registros en la bitácora, dependiendo del evento ocurrido en la BD.

Por ejemplo: Un “registro de actualización” describe una

escritura única en la BD y tiene los campos:◦ ID_Transacción.◦ ID_elemento_datos.◦ Valor anterior.◦ Valor nuevo.

Otros registros indican eventos especiales como: inicio de transacción, éxito o fracaso de la misma. Una posible vista de la bitácora puede ser:

Registro Descripción

<Ti, iniciada> La transacción Ti ha comenzado.

<Ti, Xj, V1, V2> La transacción Ti ha realizado una escritura sobre el dato Xj. Xj tenía el valor V1 antes de la escritura y tendrá el valor V2 después de la escritura

<Ti, comprometida> La transacción Ti se ha comprometido.

<Ti, abortada> La transacción Ti ha sido abortada.

Entonces, como mencionamos antes la bitácora contiene modificaciones y eventos importantes en la BD. Los tipos de registros son:◦ Actualización de la BD.◦ Compensación.◦ Commit.◦ Abort.◦ Checkpoint.◦ Terminación.

3.6.1 Concepto. Un diccionario de datos es un conjunto de

metadatos (descripción de datos). Es un almacén centralizado de información

acerca de los datos, como su significado, sus relaciones, origen, uso y formato.

En los sistemas de BD esta almacenado junto con la BD. Se crea como resultado de las sentencias del LDD. Y se puede consultar con el LMD.

Contiene una descripción de cada objeto de la BD (tablas, vistas, índices, procedimientos almacenados, disparadores, etc.). Por ejemplo, para una tabla, contiene información sobre su nombre, los campos y sus tipos de datos, las restricciones de integridad, etc.

Algunas funciones: Los usuarios y aplicaciones pueden consultarlo

y obtener descripciones de los objetos. Se usa por el compilador del LMD para validar

las sentencias.

Algunos de los tipos de tablas de diccionario de datos de SQL:2003:

Tabla Contenido

USERS Fila por usuario.

DOMAINS Fila por dominio.

DOMAIN_CONSTRAINTS Fila por cada restricción de dominio.

TABLES Fila por cada tabla y vista.

VIEWS Fila por vista.

COLUMNS Fila por columna.

TABLE_CONSTRAINTS Fila por cada restricción de tabla.

REFERENTIAL_CONSTRAINTS Fila por cada restricción de referencia.