3.2. Construcción de la base de datos 3.2.1. Introducción al lenguaje SQL
El Lenguaje de Consulta Estructurado (Structured Query Language) es un lenguaje declarativo de acceso a bases de datos relacionales que permite especificar diversos tipos de operaciones sobre las mismas. Aún a características del álgebra y el cálculo relacional permitiendo lanzar consultas con el fin de recuperar información de interés de una base de datos, de una forma sencilla. Es un lenguaje de cuarta generación.
El SQL es un lenguaje de acceso a bases de datos que explota la flexibilidad y potencia de los sistemas relacionales permitiendo gran variedad de operaciones sobre los mismos. Es un lenguaje declarativo de alto nivel o de no procedimiento, que gracias a su fuerte base teórica y su orientación al manejo de conjuntos de registros, y no a registros individuales, permite una alta productividad en codificación. De esta forma una sola sentencia puede equivaler a uno o más programas que utilizasen un lenguaje de bajo nivel orientado a registro.
Funcionalidad
El SQL proporciona funcionalidad más allá de la simple consulta (o recuperación) de datos. Asume el papel de lenguaje de definición de datos (LDD), lenguaje de definición de vistas (LDV) y lenguaje de manipulación de datos (LMD). Además permite la concesión y denegación de permisos, la implementación de restricciones de integridad y controles de transacción, y la alteración de esquemas. Las primeras versiones del SQL incluían funciones propias de lenguaje de definición de almacenamiento (LDA) pero fueron suprimidas en los estándares más recientes con el fin de mantener el lenguaje sólo a nivel conceptual y externo.
Modos de uso
El SQL permite fundamentalmente dos modos de uso:
Figura 3.2. Usos del SQL
En el caso de hacer un uso embebido del lenguaje podemos utilizar dos técnicas alternativas de programación. En una de ellas, en la que el lenguaje se denomina SQL estático, las sentencias utilizadas no cambian durante la ejecución del programa. En la otra, donde el lenguaje recibe el nombre de SQL dinámico, se produce una modificación total o parcial de las sentencias en el transcurso de la ejecución del programa. La utilización de SQL dinámico permite mayor flexibilidad y mayor complejidad en las sentencias, pero como contra punto obtenemos una eficiencia menor y el uso de técnicas de programación más complejas en el manejo de memoria y variables.
Modos de uso del SQL
Un uso interactivo, destinado principalmente a los usuarios finales avanzados u ocasionales, en el que las diversas sentencias SQL se escriben y ejecutan en línea de comandos, o un entorno semejante.
Un uso integrado,
destinado al uso por parte de los programadores dentro de programas escritos en cualquier lenguaje de programación anfitrión. En este caso el SQL asume el papel de sublenguaje de datos.
3.2.2. Creación de la base de datos
Una base de datos en un sistema relacional está compuesta por un conjunto de tablas, que corresponden a las relaciones del modelo relacional. En la terminología usada en SQL no se alude a las relaciones, del mismo modo que no se usa el término atributo, pero sí la palabra columna, y no se habla de tupla, sino de línea. A continuación se usarán indistintamente ambas terminologías, por lo que tabla estará en lugar de relación, columna en el de atributo y línea en el de tupla, y viceversa
Prácticamente, la creación de la base de datos consiste en la creación de las tablas que la componen. En realidad, antes de proceder a la creación de las tablas, normalmente hay que crear la base de datos, lo que a menudo significa definir un espacio de nombres separado para cada conjunto de tablas. De esta manera, para un Sistema Manejador de Bases de Datos (DBMS) se pueden gestionar diferentes bases de datos independientes al mismo tiempo sin que se den conflictos con los nombres que se usan en cada una de ellas. El sistema previsto por el estándar para crear los espacios separados de nombres consiste en usar las instrucciones SQL "CREATE SCHEMA". A menudo, dicho sistema no se usa (o por lo menos no con los fines y el significado previstos por el estándar), pero cada DBMS prevé un procedimiento propietario para crear una base de datos. Normalmente, se amplía el lenguaje SQL introduciendo una instrucción no prevista en el estándar: "CREATE DATABASE"
La sintaxis empleada por PostgreSQL, pero también por las DBMS más difundidas, es la siguiente:
CREATE DATABASE nombre_base de datos
Con PostgreSQL está a disposición una orden invocable por shell Unix (o por shell del sistema usado), que ejecuta la misma operación:
Para crear nuestra base de datos bibliográfica, usaremos la orden: createdb biblio
Una vez creada la base de datos, se pueden crear las tablas que la componen. La instrucción SQL propuesta para este fin es:
CREATE TABLE nombre_tabla (nombre_columna tipo_columna [ cláusula_defecto ] [ vínculos_de_columna ][ , nombre_columna tipo_columna [ cláusula_defecto ] [ vínculos_de_columna ] ... ][ , [ vínculo_de tabla] ... ] )
nombre_columna: es el nombre de la columna que compone la tabla. Sería mejor no exagerar con la longitud de los identificadores de columna, puesto que SQL Entry Level prevé nombres con no más de 18 caracteres. Consúltese, de todos modos, la documentación de la base de datos específica. Los nombres tienen que comenzar con un carácter alfabético
tipo_columna: es la indicación del tipo de dato que la columna podrá contener. Los principales tipos previstos por el estándar SQL son:
CHARACTER(n)
Una cadena de longitud fija con exactamente n caracteres. CHARACTER se puede abreviar con CHAR
CHARACTER VARYING(n)
Una cadena de longitud variable con un máximo de n caracteres. CHARACTER VARYING se puede abreviar con VARCHAR o CHAR VARYING.
INTEGER
Un número estero con signo. Se puede abreviar con INT. La precisión, es decir el tamaño del número entero que se puede memorizar en una columna de este tipo, depende de la implementación de la DBMS en cuestión.
SMALLINT
Un número entero con signo y una precisión que no sea superior a INTEGER.
FLOAT(p)
Un número con coma flotante y una precisión p. El valor máximo de p depende de la implementación de la DBMS. Se puede usar FLOAT sin indicar la precisión, empleando, por tanto, la precisión por defecto, también ésta dependiente de la implementación. REAL y DOUBLE PRECISION son sinónimo para un FLOAT con precisión concreta. También en este caso, las precisiones dependen de la implementación, siempre que la precisión del primero no sea superior a la del segundo.
DECIMAL(p,q)
Un número con coma fija de por lo menos p cifras y signo, con q cifras después de la coma. DEC es la abreviatura de DECIMAL. DECIMAL(p) es una abreviatura de DECIMAL(p,0). El valor máximo de p depende de la implementación.
INTERVAL
Un periodo de tiempo (años, meses, días, horas, minutos, segundos y fracciones de segundo).
DATE, TIME y TIMESTAMP
Un instante temporal preciso. DATE permite indicar el año, el mes y el día. Con TIME se pueden especificar la hora, los minutos y los segundos. TIMESTAMP es la combinación de los dos anteriores. Los segundos son un número con coma, lo que permite especificar también fracciones de segundo.
cláusula_defecto: indica el valor de defecto que tomará la columna si no se le asigna uno explícitamente en el momento en que se crea la línea. La sintaxis que hay que usar es la siguiente:
DEFAULT { valor | NULL }
donde valor es un valor válido para el tipo con el que la columna se ha definido.
vínculos_de_columna: son vínculos de integridad que se aplican a cada atributo concreto. Son:
NOT NULL, que indica que la columna no puede tomar el valor NULL. PRIMARY KEY, que indica que la columna es la llave primaria de la tabla. una definición de referencia con la que se indica que la columna es una
llave externa hacia la tabla y los campos indicados en la definición. La sintaxis es la siguiente:
REFERENCES nombre_tabla [ ( columna1 [ , columna2 ... ] ) ] [ ON DELETE { CASCADE | SET DEFAULT | SET NULL } ] [ ON UPDATE { CASCADE | SET DEFAULT | SET NULL } ]
Las cláusulas ON DELETE y ON UPDATE indican qué acción hay que ejecutar en el caso en que una tupla en la tabla referenciada sea eliminada o actualizada. De hecho, en dichos casos en la columna referenciante (que es la que se está definiendo) podría haber valores inconsistentes. Las acciones pueden ser:
o CASCADE: eliminar la tupla que contiene la columna referenciante (en el caso de ON DELETE) o también actualizar la columna referenciante (en el caso de ON UPDATE).
o SET DEFAULT: asignar a la columna referenziante su valor de defecto.
o SET NULL: asignar a la columna referenciante el valor NULL.
un control de valor, con el que se permite o no asignar un valor a la columna en función del resultado de una expresión. La sintaxis que se usa es:
CHECK (expresión_condicional)
Por ejemplo, si estamos definiendo la columna COLUMNA1 con el siguiente control:
CHECK ( COLUMNA1 < 1000 )
en dicha columna se podrán incluir sólo valores inferiores a 1000.
vínculo_de_tabla: son vínculos de integridad que se pueden referir a más columnas de la tabla. Son:
la definición de la llave primaria:
PRIMARY KEY ( columna1 [ , columna2 ... ] ) Véase que en este caso, a diferencia de la definición de la llave primaria como vínculo de columna, ésta se puede formar con mas de un atributo.
las definiciones de las llaves externas:
FOREIGN KEY ( columna1 [ , columna2 ... ] ) definiciones_de_referencia
La definición_de_referencia tiene la misma sintaxis y significado que la que puede aparecer como vínculo de columna.
un control de valor, con la misma sintaxis y significado que el que se puede usar como vínculo de columna.
A continuación, para ejemplificar el uso de la instrucción CREATE TABLE, se lleva a cabo la implementación de una base de datos para catalogar y administrar una colección bibliográfica:
CREATE TABLE Publicacion (ID INTEGER PRIMARY KEY, type CHAR(18) NOT NULL);
La instrucción anterior crea la tabla Publicacion, formada por las dos columnas ID de tipo INTEGER, y type de tipo CHAR(18). ID es la llave primaria de la relación. En el atributo type hay un vínculo de no nulidad
CREATE TABLE Libro
(ID INTEGER PRIMARY KEY REFERENCES Publicacion(ID), titulo VARCHAR(160) NOT NULL,
Editor INTEGER NOT NULL REFERENCES Editor(ID), volume VARCHAR(16),
series VARCHAR(160), edition VARCHAR(16), mes_pub CHAR(3),
anio_pub INTEGER NOT NULL, nota VARCHAR(255));
Crea la relación Libro, formada por nueve atributos. La llave primaria es el atributo ID, que es también una llave externa hacia la relación Publicacion. Sobre los atributos titulo, Editor y anio_pub hay vínculos de no nulidad. Además, el atributo Editor es una llave externa hacia la tabla Editor
CREATE TABLE Autor
(PublicacionID INTEGER REFERENCES Publicacion(ID), personID INTEGER REFERENCES Person(ID),
PRIMARY KEY (PublicacionID, personID));
Crea la relación Autor, compuesta por dos atributos: PublicacionID y personID. La llave primaria en este caso está formada por la combinación de los dos atributos, como está indicado por el vínculo de tabla PRIMARY KEY. PublicacionID es una llave externa hacia la relación Publicacion, mientras que personID lo es hacia la relación Person.
3.2.3. Programación por comandos y por scripts
El lenguaje SQL está compuesto por comandos, cláusulas, operadores y funciones de agregado. Estos elementos se combinan en las instrucciones para crear, actualizar y manipular las bases de datos.
Comandos
Existen dos tipos de comandos SQL:
Comandos DLL: Permiten crear y definir nuevas bases de datos, campos e índices.
Comandos DML: Permiten generar consultas para ordenar, filtrar y extraer datos de la base de datos.
Comandos DLL
Comando Descripción
CREATE Crear nuevas tablas, campos e índices DROP Eliminar tablas e índices
ALTER Modificar las tablas agregando campos o cambiando la definición de los campos.
Comandos DML
Comando Descripción
SELECT Consultar registros de la base de datos que satisfagan un criterio determinado
INSERT Cargar lotes de datos en la base de datos en una única operación.
UPDATE Modificar los valores de los campos y registros especificados
Cláusulas
Las cláusulas son condiciones de modificación utilizadas para definir los datos que desea seleccionar o manipular.
Cláusula Descripción
FROM Especificar la tabla de la cual se van a seleccionar los registros
WHERE Especificar las condiciones que deben reunir los registros que se van a seleccionar
GROUP BY Separar los registros seleccionados en grupos específicos HAVING Expresar la condición que debe satisfacer cada grupo
ORDER BY Ordenar los registros seleccionados de acuerdo con un orden específico
Operadores Lógicos
Operador Uso
AND Es el "y" lógico. Evalúa dos condiciones y devuelve un valor de verdad sólo si ambas son ciertas.
OR Es el "o" lógico. Evalúa dos condiciones y devuelve un valor de verdad si alguna de las dos es cierta.
NOT Negación lógica. Devuelve el valor contrario de la expresión.
Scripts
Un archivo descript SQL consiste de una serie de comandos SQL almacenados en un archivo de texto (con extensión.sql), para crear o modificar objetos de base de datos. Un archivo de script SQL puede contener sentencias para crear tablas, vistas, stored procedures o triggers y visualizar conjuntos de resultados.
Al terminar de definir un script SQL, éste puede ser guardado como un archivo regular ASCII en cualquier directorio de la PC. De esta manera puede ser ejecutado en cualquier momento que se desee, abriendo el archivo correspondiente.
3.2.4. Construcción del esquema de base de datos Roles del Implementador
Algunas de las actividades que corresponden al implementador son:
Debe pensar como interesa que sea la base de datos, escribiendo en papel todas las especificaciones del proyecto, intentando establecer el máximo detalle posible.
Tiene que dividir y clasificar los datos que va a contener la base de datos, de forma que se conviertan en elementos autónomos e independientes. También tiene que evitar la duplicidad y los posibles errores.
Para confeccionar la base de datos es necesario que elija la herramienta adecuada, es decir, el administrador de base de datos con el que se va a trabajar. Debe poder resolver todos los problemas que vayan a plantearse y, además de la manera más sencilla posible.
Debe planificar las modificaciones futuras, ya que debe ser consiente de que se producirán cambios, y es posible que después de que la aplicación esté funcionando haya que incluir varias mejoras, refinamientos y nuevas características.
Debe documentar todos lo elementos de la base de datos (datos, estructuras, informes, tablas, etc.), para futuras modificaciones o correcciones.
Debe hablar con los usuarios finales, para conocer de primera mano sus conocimientos, necesidades y enfoque del asunto.
La previsión de realizar copias de seguridad (a ser posible de modo automatizado) es otra parte importante del diseño de una base de datos, ya que en caso de error es la única forma de recuperar los datos.
Las pruebas no se deben efectuar con los datos reales, y sólo deben incorporarse cuando todo el conjunto funcione correctamente
Debe crear normas comunes para los datos de entrada, ya que la eficacia de una base de datos se mide por la integridad y la homogeneidad de los datos que contiene.
Tablas
La información que contiene una base de datos está organizada en forma de tabla, con filas y columnas. Cada fila contiene exactamente el mismo tipo de datos que todas las demás, y están colocados en el mismo orden. Cada línea se llama registro y contiene todos los datos de un elemento determinado. Cada columna se llama campo y contiene los mismos datos de todos los elementos.
Las bases de datos, como cualquier archivo, deben estar organizadas de manera que sea fácil localizar la información, y su estructura debe estar adaptada a la forma de pensar del ser humano. Por eso, los directorios de teléfono se ordenan alfabéticamente y no por los números de teléfono.
Por ello, las bases de datos tienen dos conceptos básicos: los datos en sí mismos y la estructura de los mismos. Los registros se numeran secuencialmente, según se introducen en la base de datos, aunque después se pueden reordenar según cualquier criterio que se establezca.
Los datos deben almacenarse de manera que resulten independientes de cualquier programa que pueda acceder a ellos, tanto para su consulta como para su tratamiento.
Los programadores de SQL tienden a emplear el término “tabla” en vez de “relación”. Lo hacen por la importancia de trazar una distinción entre relaciones almacenadas, que son “tablas” y relaciones virtuales, que son “vistas”.
Integridad
La integridad de las bases de datos es un objetivo: evitar que haya errores en la información almacenada en la base de datos o en los resultados de los procesos sobre ella. Para ello hay que conseguir que:
Los valores de los datos:
o Sean verdaderos, o por lo menos verosímiles o Estén debidamente relacionados entre sí,
o Y sean accesibles por los caminos de acceso previstos, es decir que los índices, apuntadores y listas de posiciones vacías estén correctos;
En bases de datos distribuidas, las replicas de la misma información sean concordantes;
No haya interferencias entre lecturas y actualizaciones que puedan dar lugar a que aquéllas den resultados incorrectos;
Se impida que personas no autorizadas pudieran hacer actualizaciones indebidas (esto también entra dentro de la seguridad de las bases de datos).
Para conseguir y conservar la integridad de las bases de datos hay dos tipos de técnicas:
preventivas, orientadas a evitar que la base de datos pierda su integridad, y curativas, que le restituyen la integridad perdida.
Índices
Un índice es un archivo auxiliar que sirve para acceder a los registros de otro archivo, que llamamos archivo principal, por el valor de un dato o conjunto de datos que llamaremos clave de indexación o clave. Para un mismo archivo principal puede haber varios índices, correspondientes a sendas claves. A los registros del índice se les llama entradas; cada entrada corresponde a un valor o intervalo de valores de la clave y es el padre de un árbol cuyos hijos son los registros del archivo principal en los cuales la clave toma el valor (o un valor del intervalo de valores) correspondiente a la entrada en cuestión.
Se dice que uníndice es denso si tiene una entrada para cada valor de la clave que aparece por lo menos en algún registro del archivo principal (lo que no quiere decir que tenga una entrada para cada uno de estos registros), Un índice no denso necesita que la clave sea campo de ordenación del archivo principal, pues así es posible acceder a los registros cuyo valor de la clave no aparece en ninguna entrada, para lo cual se accede al registro apuntado por la entrada, para
lo cual se accede al registro apuntado por la entrada con un valor de la clave más próximo por defecto (por ejemplo) al del registro buscado y luego se va corriendo secuencialmente al archivo principal en orden de valores crecientes de la clave desde aquel registro hasta encontrar el registro buscado, si existe, o hasta encontrar uno con valor más alto de la clave.
Vistas
Es una forma alternativa de ver los datos de una o más tablas de la base de datos. Una vista es unatabla virtual, normalmente creada como un subconjunto de las columnas de una o más tablas. Una vista puede hacer referencia a atributos de varias tablas básicas y/o 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 con un nombre. Sin embargo, la vista no existe como conjunto de valores de datos almacenados en una base de datos. Las filas y las columnas de datos proceden de tablas a las que se hace referencia en la consulta que define a la vista y se producen de forma dinámica cuando se hace referencia a la vista.
Una vista actúa como filtro de las tablas subyacentes a las que se hace referencia en ella. La consulta que define a la vista puede ser de una o de varias tablas, o bien tratarse de otras vistas de la base de datos actual o de otras bases de datos. Asimismo, es posible utilizar las consultas distribuidas para definir vistas que utilicen datos de orígenes diversos. Esto puede resultar de utilidad, por ejemplo, si desea combinar datos de estructura similar que proceden de distintos servidores, cada uno de los cuales almacena los datos para una región distinta de la organización.
No hay restricciones que afecten a las consultas mediante vistas y hay pocas restricciones que afecten a la modificación de datos mediante esas consultas.
Las vistas suelen utilizarse para centrar, simplificar y personalizar la percepción de la base de datos para cada usuario. Pueden emplearse como mecanismos de
seguridad al permitir a los usuarios tener acceso a los datos mediante la vista sin concederles permisos para que tengan acceso directo a las tablas base subyacentes de la vista
Triggers
Es un desencadenador, y una clase especial de procedimiento almacenado que se ejecuta automáticamente cuando un usuario intenta la instrucción especificada de modificación de datos en la tabla indicada. Microsoft SQL Server permite la creación de varios desencadenadores para cualquier instrucción INSERT, UPDATE o DELETE dada.
Se utilizan para implementar las reglas de negocio, aquellas que preservan la coherencia de los datos desde el punto de vista de nuestra aplicación concreta. Estos objetos están constituidos por un conjunto de sentencias SQL que se ejecutan como respuesta a operaciones de borrado, actualización o inserción de registros en una tabla, para, por ejemplo, realizar borrados o actualizaciones en cascada.
Stored Procedures
Un procedimiento almacenado (stored procedure) es una colección precompilada de sentencias SQL que pueden tomar y devolver parámetros.
Los procedimientos almacenados ayudan a mejorar el rendimiento y consistencia de la base de datos, pues son muy eficientes en su ejecución. Permiten realizar tareas repetitivas rápidamente, ya que son compilados únicamente la primera vez que son ejecutados, para ser almacenados después. Un procedimiento almacenado es, así, de ejecución mucho más rápido que la suma aislada de las sentencias SQL que lo integran.
Pueden utilizarse para controlar el acceso a tablas individuales y columnas en la base de datos y para restringir los cambios realizados a sus tablas.
Manejo de Transacciones
Una transacción es laejecución de un programa (o una parte de un programa, o un conjunto de programas que se van llamando uno a otro) que hace accesos de lectura y/o actualización a la base de datos y que, si actualiza, siempre o bien completa sus actualizaciones o bien éstas son anuladas totalmente. Típicamente corresponde al proceso derivado de la lectura de un registro de un archivo de entrada que se trata secuencialmente o, en el caso de proceso online, al tratamiento de un mensaje recibido o varios. También se le llama unidad lógica de ejecución.
Es toda secuencia o interrelación de operaciones que se desencadena debido a la llegada de uno o más mensajes de entrada, genera uno o más mensajes de salida y cumple las siguientes condiciones en cuanto a sus efectos sobre la base de datos de un vez ha terminado:
Condiciones
Definitividad
Si ha acabado normalmente, todas las actualizaciones que haya hecho han sido confirmadas; esto significa que ha ordenado al sistema de gestión de bases de datos que las considere irreversibles en el sentido que sólo podrían ser anuladas por medio de otra transacción posterior y que si se destruye la base de datos tenga que poder se reconstruidas.
Atomicidad
Si acaba normalmente, todas las actualizaciones que haya hecho quedan confirmadas; si aborta, todas las actualizaciones que hubiera llegado a hacer quedan anuladas (también se dice que ha hecho un backout de las mismas).
Consistencia
Preserva la integridad de la base de datos, es decir, si la base de datos era integra antes de la transacción vuelve a serlo (o continúa siéndolo) después de acabar la transacción (toda la secuencia de operaciones preserva la integridad interna de los gránulos que actualiza, pero una transacción ha de preservar también la integridad).
Recuperación
El objetivo general es conseguir que una base de datos deteriorada vuelva a cumplir las condiciones de integridad semántica.
Existen tres modelos de recuperación para cada base de datos. Cada uno de ellos determina cómo se realizan las copias de seguridad de los datos y cuál es el riesgo de perder datos.
El modelo de recuperación simple. Permite recuperar la base de datos hasta la copia de seguridad más reciente.
El modelo de recuperación completa. Permite recuperar la base de datos hasta el momento del error.
El modelo de recuperación de registro masivo Permite operaciones de registro masivo.
Figura 3.3. Modelos de recuperación Modelos de
recuperación
