Download TEMA 26 - OCW UPM

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.