TEMA 26: ACTUALIZACIÓN E INTEGRACIÓN DE BASES DE DATOS

OBJETIVOS DEL TEMA:

•

Comandos que permiten mantener una BD actualizada.

1.- INTRODUCCIÓN

En ocasiones los usuarios pueden crear tipos de datos personalizados, esto se conoce como DOMINIOS, por ejemplo se puede crear un tipo de dato entero, sería una redundancia puesto que existe el tipo INTEGER, sin embargo la forma de crearlo sería:

CREATE DOMAIN ENTERO AS INTEGER DEFAULT 0

(Esto último indica que por defecto, cada vez que se introduce un dato de este tipo, sería de valor 0)

(Nota: SQLITE no permite el uso de dominios).

Dentro de la tabla CLIENTE se están reservando 20 caracteres para el APELLIDO, esto sería VARCHAR(20), sin embargo se puede crear un tipo TEXTO_20 de la siguiente forma:

CREATE DOMAIN TEXTO_20 AS VARCHAR(20) CHARACTER SET NONE COLLATE NONE

Si por ejemplo se quisiera crear un campo MONEDA (Numérico con 3 decimales), que no permitiera valor NULL (Nulo no es igual a 0), se tendría:

CREATE DOMAIN MONEDA AS NUMERIC(12,3) DEFAULT 0 NOT NULL

2.- CREACIÓN DE TABLAS USANDO DOMINIOS

El comando fundamental para definir datos es el que crea una nueva relación (una nueva tabla). La sintaxis del comando CREATE TABLE es:

CREATE TABLE nombre_tabla (nombre_de_atributo_1 tipo_de_atributo_1 [,nombre_de_atributo_2 tipo_de_atributo_2 [, ...]]);

Para crear las tablas definidas en La Base de Datos de CLIENTE se utilizó la siguiente sentencia:

CREATE TABLE CLIENTE ( DNI ENTERO NOT NULL, APELLIDOS TEXTO_20, NOMBRE TEXTO_10, EDAD ENTERO );

Nótese que los campos hacen referencia a los dominios.

(Nota: los dominios no se suelen usar muy habitualmente, ya que a veces crea mas confusión encontrarse en una tabla con un "tipo de datos especial" que no es de los habituales de SQL, y que será necesario buscar donde está su definición. Por ello, si se usan, hacerlo con cuidado).

3.- CREACIÓN DE ÍNDICES

Se utilizan los índices para acelerar el acceso a una relación. Si una relación R tiene un índice en el atributo A podremos recuperar todas la tuplas t que tienen t(A) = a en un tiempo aproximadamente proporcional al número de tales tuplas t más que en un tiempo proporcional al tamaño de R.

Para crear un índice en SQL se utiliza el comando CREATE INDEX. La sintaxis es:

CREATE INDEX nombre_indice ON nombre_tabla ( nombre_del_atributo );

Para crear un índice llamado INDICE_APELLIDO sobre el atributo APELLIDO de la tabla CLIENTE, utilizaremos la siguiente instrucción:

CREATE INDEX INDICE_APELLIDO ON CLIENTE (APELLIDOS);

También se puede crear en base a una composición de campos, por ejemplo en base al APELLIDO y al NOMBRE y además en orden descendente se haría:

CREATE DESCENDING INDEX APELLIDO_NOMBRE_REVES ON CLIENTE (APELLIDOS, NOMBRE);

El índice creado se mantiene automáticamente, es decir, cada vez que una nueva tupla se inserte en la relación CLIENTE, se adaptarán los índices definidos anteriormente. Nótese que el único cambio que un usuario puede percibir cuando se crea un índice es un incremento en la velocidad de las consultas SELECT sobre la tabla CLIENTE. En algunos casos consultas que sin índice tardan horas en realizarse, se pueden reducir a unos segundos. Aunque el uso de índices en general beneficia, no es una buena política crear índices de todos los campos de una BD, ya que al realizar cualquier operación de inserción (INSERT), borrado (DELETE) o actualización (UPDATE)

de la tabla, se actualizan todos los índices asociados, ralentizando estas operaciones, sólo deben crearse aquellos índices que se necesitan. Una buena costumbre, es no crear los índices al principio y posteriormente, según se vayan necesitando realizar las consultas mas habituales (las que mas se repiten) y/o a las tablas mas grandes (con muchos registros), crear índices sobre los campos de las tablas que usen estas consultas.

4.- CREACIÓN DE VISTAS

Se puede ver una vista como una tabla virtual, es decir, una tabla que no existe físicamente en la base de datos, pero aparece al usuario como si existiese. Por contra, cuando hablamos de una tabla base, hay realmente un equivalente almacenado para cada fila en la tabla en algún sitio del almacenamiento físico.

Las vistas no tienen datos almacenados propios, distinguibles y físicamente almacenados. En su lugar, el sistema almacena la definición de la vista (es decir, las reglas para acceder a las tablas base físicamente almacenadas para materializar la vista) en algún lugar de los catálogos del sistema.

En SQL se utiliza el comando CREATE VIEW para definir una vista. La sintaxis es: CREATE VIEW nombre_vista AS select_stmt

donde select_stmt es una instrucción select válida, como se definió en Select. Nótese que select_stmt no se ejecuta cuando se crea la vista. Simplemente se almacena en los catálogos del sistema y se ejecuta cada vez que se realiza una consulta contra la vista.

Sea la siguiente definición de una vista:

CREATE VIEW RESUMEN_PEDIDOS AS SELECT CLIENTE.NOMBRE,CLIENTE.APELLIDOS,PEDIDO.NUMPEDIDO, FORMA_PAGO.DESCRIPCION PAGO FROM CLIENTE, PEDIDO, FORMA_PAGO WHERE (CLIENTE.DNI = PEDIDO.DNI) AND (PEDIDO.FORMAPAGO = FORMA_PAGO.CODIGO);

Ahora podemos utilizar esta relación virtual RESUMEN_PEDIDOS como si se tratase de otra tabla base :

SELECT * FROM RESUMEN_PEDIDOS; NOMBRE -----------JOSE MARIA JOSE MARIA JOSE MARIA MARIA MARIA MIGUEL ELENA LUIS

APELLIDOS -------------------PEREZ GARCIA PEREZ GARCIA PEREZ GARCIA SEBASTIAN YUSTE SEBASTIAN YUSTE JIMENO DIAZ ARIAS FERNANDEZ ROMERO ALONSO

NUMPEDIDO ---------500 501 523 524 502 275 250 276

PAGO --------------VISA VISA Contrareembolso Talón Talón AMERICAN EXPRES Contrareembolso VISA

Usos habituales de las vistas: se suelen usar para simplificar el acceso a datos agrupados que de otra forma habría que calcular siempre con un select complejo. Aunque el uso mas habitual es ofrecer un subconjunto de datos (campos o columnas) a ciertos usuarios, para limitar el acceso a solo cierta información de la base de datos, (al usuario solo se le daría acceso de consulta a ciertas vistas creadas para tal fin, y deshabilitandole el acceso a las tablas reales con información sesible que no queremos que pueda acceder).

5.- BORRADO DE ELEMENTOS (TABLAS, INDICES, VISTAS)

Se utiliza el comando DROP TABLE para eliminar una tabla, incluyendo todo su contenido (todos los registros) almacenado en ella: DROP TABLE nombre_tabla;

Para eliminar la tabla CLIENTE, utilizaremos la instrucción:

DROP TABLE CLIENTE;

Se utiliza el comando DROP INDEX para eliminar un índice: DROP INDEX nombre_indice;

Finalmente, eliminaremos una vista dada utilizando el comando DROP VIEW: DROP VIEW nombre_vista;

6.- INSERCIÓN DE DATOS EN TABLAS

Una vez que se crea una tabla (véase Create Table), puede ser llenada con tuplas mediante el comando INSERT INTO. La sintaxis es:

INSERT INTO nombre_tabla (nombre_de_atributo_1 [,nombre_de_atributo_2 [,...]]) VALUES (val_atr_1 [, val_atr_2 [, ...]]);

Para insertar la primera tupla (registro) en la tabla CLIENTE utilizamos la siguiente instrucción:

INSERT INTO CLIENTE (DNI,APELLIDOS,NOMBRE,EDAD) VALUES (12345678,'PEREZ GARCIA','JOSE MARIA',34);

Para insertar la primera tupla en la tabla PEDIDO, utilizamos:

INSERT INTO PEDIDO (DNI,NUMPEDIDO,FORMAPAGO,IMPORTE) VALUES (12345678,500,1,123);

7.- EDICIÓN / MODIFICACIÓN DE DATOS EN TABLAS

Para cambiar uno o más valores de atributos de tuplas en una relación, se utiliza el comando UPDATE. La sintaxis es:

UPDATE nombre_tabla SET nombre_de_atr_1 = valor_1 [, ... [,nombre_de_atr_k = valor _k]] WHERE condición;

Para cambiar el valor del atributo EDAD de Jose Maria Perez Garcia (34 años) en la tabla CLIENTE, utilizamos:

UPDATE CLIENTE SET EDAD = 36 WHERE DNI = 12345678; SELECT * FROM DNI -----------12345678

CLIENTE WHERE DNI = 12345678; APELLIDOS NOMBRE -------------------- ---------PEREZ GARCIA JOSE MARIA

EDAD ----36

8.- BORRADO DE DATOS (registros o tuplas) EN TABLAS

Para borrar una tupla de una tabla particular, utilizamos el comando DELETE FROM. La sintaxis es: DELETE FROM nombre_tabla

WHERE condición;

Para borrar el cliente cuyos apellidos son 'ROMERO ALONSO' de la tabla CLIENTE, utilizamos la siguiente instrucción:

DELETE FROM CLIENTE WHERE APELLIDOS = 'ROMERO ALONSO';

9.- CATÁLOGO DEL SISTEMA O DICCIONARIO DE DATOS

En todo sistema de base de datos SQL se emplean catálogos de sistema para mantener el control de qué tablas, vistas, índices, etc están definidas en la base de datos. Estos catálogos del sistema se pueden investigar como si de cualquier otra relación normal se tratase. Por ejemplo, hay un catálogo utilizado para la definición de vistas. Este catálogo almacena la consulta de la definición de la vista. Siempre que se hace una consulta contra la vista, el sistema toma primero la consulta de definición de la vista del catálogo y materializa la vista antes de proceder con la consulta del usuario. No es habitual por parte del usuario acceder a dichos catálogos, que varían de un SDBD a otro.

10.- SQL EMBEBIDO

No es más que la utilización de sentencias SQL desde un lenguaje de programación, por ejemplo Basic, C, Fortran ...Hay dos razones principales por las que podríamos querer utilizar SQL desde un lenguaje de programación:

1. Hay consultas que no se pueden formular utilizando SQL puro (por ejemplo, las consultas recursivas). Para ser capaz de realizar esas

consultas necesitamos un lenguaje de programación de mayor poder expresivo que SQL.

2. Simplemente queremos acceder a una base de datos desde una aplicación que está escrita en un lenguaje de programación (p.e. un sistema de reserva de billetes con una interface gráfica escrita en C, y la información sobre los billetes está almacenada en una base de datos que puede accederse utilizando SQL embebido).

Un programa que utiliza SQL embebido en un lenguaje de programación consiste en instrucciones del lenguaje de programación e instrucciones de SQL embebido (ESQL). Cada instrucción de ESQL empieza con las palabras claves EXEC SQL. Las instrucciones ESQL se transforman en instrucciones del lenguaje de programación mediante un precompilador (que habitualmente inserta llamadas a rutinas de librerías que ejecutan los variados comandos de SQL). Cuando vemos los ejemplos de Select observamos que el resultado de las consultas es algo muy próximo a un conjunto de tuplas. La mayoría de los lenguajes de programación no están diseñados para operar con conjuntos, de modo que necesitamos un mecanismo para acceder a cada tupla única del conjunto de tuplas devueltas por una instrucción SELECT. Este mecanismo puede ser proporcionado declarando un cursor. Tras ello, podemos utilizar el comando FETCH para recuperar una tupla y apuntar el cursor hacia la siguiente tupla.

Enlaces de SQLITE embebido en los lenguajes de programación habituales: http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers

11.- IMPORTAR/EXPORTAR DATOS DESDE FICHEROS DE TEXTO

Habitualmente la inserción de grandes cantidades de datos en una base de datos se realiza mediante un proceso llamado "importación de datos" o

"carga de datos", que suele realizarse mediante una herramienta específica de cada Base de Datos (no es parte de SQL). Vamos a hacer un ejemplo para SQLITE: Supongamos que tenemos un fichero de texto llamado "datos.txt" con el contenido: 55611580|ALONSO ALONSO|FERNANDO|24 66111130|ALBIZU GARCIA|ANA|25 77111000|RODRIGUEZ MARTIN|MARTA|26

Que suponemos que lo hemos creado a mano o lo hemos generado, por ejemplo desde una hoja excel mediante "guardar como" CSV, que es un formato de texto plano habitual para exportar/importar datos. El separador de campo empleado es "|" (que es un caracter poco habitual en el texto normal). Desde SQLITE lo importaremos de la siguiente forma: sqlite> .separator | sqlite> .import datos.txt CLIENTE sqlite> SELECT * FROM CLIENTE; DNI APELLIDOS ------------ -------------------12345678 PEREZ GARCIA 22344556 PEREZ LOPEZ 54321123 SEBASTIAN YUSTE 87882215 JIMENO DIAZ 57374811 JIMENO ZOLA 88010245 ARIAS FERNANDEZ 55611580 ALONSO ALONSO 66111130 ALBIZU GARCIA 77111000 RODRIGUEZ MARTIN

NOMBRE ---------JOSE MARIA ANA MARIA MIGUEL DANIELA ELENA FERNANDO ANA MARTA

EDAD --------36 30 55 61 61 22 24 25 26

Para exportar datos desde la Base de datos SQLITE a un fichero de texto en formato CSV (separado por comas), haremos: sqlite> .mode csv sqlite> select * from CLIENTE; DNI,APELLIDOS,NOMBRE,EDAD 12345678,"PEREZ GARCIA","JOSE MARIA",36 22344556,"PEREZ LOPEZ",ANA,30 54321123,"SEBASTIAN YUSTE",MARIA,55 87882215,"JIMENO DIAZ",MIGUEL,61 57374811,"JIMENO ZOLA",DANIELA,61 88010245,"ARIAS FERNANDEZ",ELENA,22 55611580,"ALONSO ALONSO",FERNANDO,24 66111130,"ALBIZU GARCIA",ANA,25 77111000,"RODRIGUEZ MARTIN",MARTA,26 sqlite> .output salida.txt sqlite> SELECT * FROM CLIENTE; sqlite> .output stdout

Con esto, se ha creado un fichero llamado "salida.txt" con el contenido de la tabla CLIENTE, con los campos separados por comas "," (csv) que puede ser leido fácilmente por otras aplicaciones (excel, etc).

RESUMEN DEL TEMA 26 Se ha visto como crear dominios, que no es mas que una forma de declarar variables. Se ha repasado la creación de tablas usando dominios.

Los índices mejoran drásticamente los tiempos de acceso a tablas

Si una determinada consulta se utiliza muy a menudo, y esta liga tablas, puede ser conveniente crear una vista basada en dicha consulta.

Se pueden borrar todos los elementos de una base de datos, desde registros en concreto, hasta índices y tablas completas.

Se ha visto como insertar datos dentro de una tabla (INSERT), y cómo modificar éstos (UPDATE).

Se ha indicado que el lenguaje SQL se puede integrar dentro de otros lenguajes de programación con el fin de aumentar la potencia de éste.

Finalmente se han visto algunas formas de importar/exportar datos desde/hacia otros programas y la Base de Datos, mediante ficheros de texto plano.