| Creación de Tablas con SQL | |||
| Integridad referencial | Trigger | Auditoría | Procedimiento almacenado |
Se hizo referencia a los elementos
que poseen los Sistemas de Gestión de Bases de Datos para apoyar
la seguridad y/o integridad de la información, pero no basta con
saber que existen, es importante ver que su implementación es fácil
y que requiere de pocos esfuerzos. Esto nos coloca en la necesidad
de mostrar en una construcción real como pueden ser incluidos. |
Para ello trabajaremos con el DBMS
InterBase 5.5, potente motor de base de datos de la empresa Inprise.
Es seguro que el uso de otros sistemas no generará grandes diferencias
en su montaje. La aplicación cliente se construirá usando Delphi
5.0. Se mostrará paso a paso la materialización de los conceptos
en estudio. |
|
Se desarrolla un sencillo ejercicio,
una pequeña aplicación de venta de productos, donde se incluyen
estos elementos de control. Además se muestra que es posible crear
nuestra propia bitácora de auditoría de acuerdo a necesidades concretas
y conocimiento del sistema. |
Se tomó como base el paradigma
cliente/servidor, a partir del cual se construyó inicialmente la
estructura de la parte servidora en el motor de base de datos, luego
se desarrolla la aplicación cliente que solicita sus servicios. |
|
Para iniciar se debe crear la base
de datos, donde se almacenaran los datos e información administrativa
del propio sistema. Crear la Base de Datos Taller, en directorio
Local C:\Datos\interbase. Este directorio debe ser creado previamente.
Para tener acceso a la ventana para introducción de comandos SQL,
Inicio/Programas/InterBase/InterBase Windows ISQL: |
El usuario definido por defecto en InterBase es SYSDBA y su clave de acceso es masterkey, correspondiente al administrador del sistema de base de datos. Es importante tener presente que cualquier persona que haya usado InterBase conoce este nombre de usuario y la clave, por lo tanto puede ser un hueco de seguridad el no cambiar éstas predefiniciones. |
|
Al crear una nueva Base de Datos,
se realiza la conexión a esta. En otro momento, antes de poder realizar
alguna acción sobre la misma se debe realizar la conexión: File/Connect
to DataBase. Aparecerá una ventana similar a la anterior, donde
deben digitarse los datos para acceso a la misma. Si la conexión
es correcta en la parte inferior de la ventana de ISQL, aparece
el nombre de la Base de Datos activa. |
||
| Escribir en la ventana superior las siguientes instrucciones SQL: | ||
CREATE TABLE CLIENTE( |
||
| NumeroCliente int NOT NULL, | ||
| Nombre char(30), | ||
| Apellido char(30), | ||
| Direccion char(30), | ||
| Ciudad char(20)) | ||
Luego se presiona el botón de Execute
Query .
Si existe error se informa, en caso contrario la acción ha sido
ejecutada. |
||
| Se hace lo propio con las demás tablas: | ||
CREATE TABLE VENTAS( |
||
| NVenta int NOT NULL, | ||
| Fecha date, | ||
| NCliente int NOT NULL, | ||
| NItem int NOT NULL, Total float) | ||
Antes de crear la tabla Productos
se pueden definir dominios con algunas restricciones, esto es crear
un tipo de dato que puede ser utilizado posteriormente como cualquiera
de los predefinidos: |
||
|
||
| CREATE DOMAIN PrecioPr | ||
| AS float | ||
| CHECK (VALUE > 1000) | ||
| CREATE TABLE PRODUCTO( | ||
| Codigo int NOT NULL, | ||
| descripción char(30), | ||
| precio PrecioPr) | ||
Las dos anteriores instrucciones
determinan que todo producto debe tener un precio superior a 1000. |
||
|
||
| CREATE TABLE ITEM( | ||
| NVenta int NOT NULL, | ||
| CodigoP int NOT NULL, | ||
| Cantidad float) | ||
| Se debe hacer un cambio en la Tabla Ventas, que lleve a una definición como: | ||
TABLE VENTAS ( |
||
| nventa integer NOT NULL, | ||
| fecha date, | ||
| ncliente integer NOT NULL, | ||
| total float) | ||
| Entonces: | ||
| Alter table VENTAS drop Nitem; | ||
| Alter table VENTAS drop precio; | ||
| Alter table VENTAS add Total Float; | ||
| Es posible definir las llaves primarias de la siguiente forma: | ||
alter table cliente add PRIMARY KEY (NumeroCliente); |
||
| alter table producto add primary key (Codigo); | ||
| alter table ventas add primary key (NVenta); | ||
| Otra forma consiste en borrar inicialmente la tabla y luego crearla como se desee: | ||
| drop table item; | ||
| CREATE TABLE ITEM | ||
| (NVenta int NOT NULL, | ||
| CodigoP int NOT NULL, | ||
| Cantidad float, | ||
| PRIMARY KEY (Nventa, CodigoP), | ||
| FOREIGN KEY (NVenta), | ||
| REFERENCES ventas(NVenta), | ||
| FOREIGN KEY (CodigoP), | ||
| REFERENCES Producto(Codigo)); | ||
| alter table item add subtotal float | ||
Se puede observar que se está usando
una llave primaria compuesta y se está definiendo la integridad
referencial. Ahora, si en las tablas referenciadas los atributos
de la llave primaria compuesta no están definidos como llave primaria,
no se podrá tener esta relación. Para que se permita que un atributo
se convierta en llave primaria, se debe condicionar que este no
sea nulo (Regla de la Entidad). |
||
|
||
| CREATE TABLE historia | ||
| (Producto int, | ||
| fecha date, | ||
| usuario char (10), | ||
| viejo float, | ||
| porcentaje float); | ||
Esta tabla será usada para almacenar
la información al momento que se presente un cambio en el precio
de un producto. El mecanismo para adicionar registros es un trigger asociado a la tabla producto, el cual puede ser creado usando un
guión SQL, que es un archivo ASCII con una serie de instrucciones,
así: |
||
| CONNECT taller.gdb USER SYSDBA PASSWORD masterkey; | ||
| SET TERM !!; | ||
| CREATE TRIGGER historia FOR producto | ||
| AFTER UPDATE AS | ||
| BEGIN | ||
| IF (old.precio <> new.precio) THEN | ||
| INSERT INTO historia | ||
| (producto, fecha,usuario,viejo,porcentaje) | ||
| VALUES (old.codigo, "now", USER, old.precio,(new.precio - old.precio) * 100 / old.precio); | ||
| END !! | ||
| SET TERM ; !! | ||
| La especificación del trigger permite apreciar lo siguiente: |
|
|
Si lo anterior ha sucedido, inserta
un registro en la tabla historia, almacenando el código del producto,
la fecha en que se realizo la modificación, el usuario que lo hizo,
el precio anterior y el porcentaje de cambio). No se debe olvidar
que este disparador actuará independientemente, solo basta que se
den las condiciones que se han especificado. |
||
Realmente se ha creado, en forma
sencilla, una tabla como log de auditoría a la medida, que
permite seguir el rastro a las acciones deseadas. |
||
Para facilitar la tarea del auditor
en cuanto a recuperación de la información en la tabla historia
es posible construir un procedimiento almacenado. El usuario
que crea el procedimiento es el propietario del mismo y puede conceder
derechos sobre éste a los demás usuarios. |
||
| Para el caso concreto el procedimiento es audit1: | ||
CREATE PROCEDURE audit1 |
||
| AS | ||
| BEGIN | ||
|
||
|
||
|
||
|
||
| end; | ||
| Sólo serán reportados los productos cuyo cambio de precio haya superado el 20% del valor anterior. | ||
A esta altura la parte de la lógica
de la aplicación está completa. La información de control será insertada
en los momentos determinados y los procedimientos están listos para
actuar. Cualquier usuario que interaccione con la base de datos
esta sujeto a lo anteriormente señalado. |
||