Download PRCTICAS DE BASES DE DATOS

PRÁCTICAS DE BASES DE DATOS
Curso 2006/07
En este documento se presentan una serie de prácticas no obligatorias que tiene por objeto
familiarizar al alumno con el entorno de trabajo y los conceptos fundamentales de implementación
de bases de datos, así como de su diseño.
PRÁCTICA 1: EL ENTORNO DE TRABAJO
Con esta práctica se pretende que el alumno se familiarice con el entorno con el que va a trabajar.
En concreto, vamos a utilizar el Sistema Gestor de Bases de Datos (SGBD) “PostgreSQL” en su
versión 8.0.8 sobre el sistema operativo Linux. Este SGBD es de libre distribución, encontrándose
en las principales distribuciones de Linux, por lo que cualquier alumno puede disponer de él en su
ordenador personal.
La implementación del SGBD sigue una arquitectura típica cliente-servidor: Una sesión Postgresql
está formada por los siguientes procesos:
- Un proceso supervisor (daemon) denominado “postmaster”.
- El cliente de usuario (por ejemplo el programa “psql”)
- Uno o más procesos servidores de bases de datos (procesos “postgres”)
Un solo proceso supervisor gestiona un conjunto de bases de datos en una determinada máquina. Un
proceso cliente que desee acceder a una determinada base de datos hace llamadas a una librería. La
librería envía las peticiones de usuario a través de la red al “postmaster”, que crea un nuevo
proceso servidor para atender la petición y conecta al proceso cliente con el nuevo servidor.
Aplicación
cliente
LIBPQ
1
POSTMASTER
2
SERVIDOR
Máquina Cliente
Máquina servidora
Desde este momento el cliente y el servidor se comunican sin intervención del “postmaster”. De
esta forma, el “postmaster” está siempre ejecutándose a la espera de peticiones mientras los
clientes y servidores aparecen y desaparecen.
El “postmaster” y los procesos servidores se deben ejecutar en la misma máquina (servidor)
mientras que los clientes pueden ejecutarse en la misma máquina o máquinas distintas (clientes).
En las prácticas a realizar no es necesario programar aplicaciones clientes (que se podrían
implementar en diversos lenguajes para los que existen versiones de la librería: C, Java, Perl ,...)
sino que vamos a utilizar una aplicación cliente proporcionada con el entorno PostgreSQL y que se
denomina “psql”.
-1-
Ejecución del cliente “psql”
Antes de llamar al proceso cliente se deben realizar las siguientes operaciones:
- Añadir a nuestro PATH el directorio donde se encuentran los comandos de PostgreSql:
“/usr/bin” (esta operación ya está realizada en el .cshrc por defecto de las cuentas del
laboratorio)
- Si queremos utilizar las páginas de manual, añadir a nuestro MANPATH el siguiente
directorio: “/usr/share/man” (esta operación ya está realizada en el .cshrc por defecto de las
cuentas del laboratorio)
- Si es la primera vez que accedemos a nuestra cuenta de usuario, cambiar nuestra password
por defecto.
Una vez hecho esto podemos llamar a nuestro programa cliente con el siguiente comando:
psql [–h nombre_máquina] nombre_bd [nombre_usuario] 1 , donde:
- nombre_máquina es el nombre de la máquina en que se ejecuta el servidor. Si el servidor se
ejecuta en la misma máquina que el cliente esta opción no es necesaria. En nuestro caso el
servidor se ejecuta en la máquina “clave”.
- nombre_bd es el nombre de la base de datos, de las gestionadas por el servidor, a la que nos
queremos conectar. Siempre debemos conectarnos a una base de datos ya existente en el
servidor. En el entrono del laboratorio cada grupo de prácticas tiene ya creadas dos bases de
datos en el servidor denominadas “prácticasX” y “pruebasX” (X es el número de grupo)
- nombre_usuario es el nombre de usuario con el que queremos conectarnos a la base de
datos. En caso de no indicarlo será nuestro nombre de usuario actual.
Importante: Las cuentas de usuario en el SGBD son diferentes e independientes de las de Linux,
aunque tengan el mismo nombre. Por tanto, una vez conectado al SGBD debe cambiar también
su password por defecto para su cuenta del SGBD.
El programa psql es un interprete que nos permite introducir, editar y ejecutar de manera interactiva
comandos SQL sobre la base de datos a la que estamos conectados. Por tanto, una vez que hayamos
accedido nos presentará un prompt y se quedará a la espera de que introduzcamos comandos.
Podemos introducir comandos de dos tipos:
1. Comandos SQL: podemos introducir comandos en SQL que se ejecutarán sobre la
base de datos a que estamos conectados. Estas sentencias pueden ocupar varias
líneas y se ejecutarán en el momento que las terminemos con un “;”. Para obtener
ayuda sobre la sintaxis de estos comandos podemos teclear “\h”
2. Comandos propios del programa psql, y que, por tanto, no realizarán acción alguna
sobre la base de datos a que estamos conectados. Estos comandos se escriben en una
sola línea y se distinguen de los anteriores en que empiezan por el símbolo “\”. Para
obtener ayuda sobre la sintaxis y significado de estos comandos podemos teclear “\?”
(podemos ver que los comandos de petición de ayuda son comandos de este tipo)
Prueba diferentes comandos de psql que pueden ser de interés como pueden ser: “\l”, “\d*”, “\c” y,
obviamente “\q” para abandonar el programa.
Comprueba también que el interprete proporciona algunas facilidades de edición de comandos
parecidas a las de la “tcsh”, como pueden ser: repetir las últimas líneas con la flechas arriba/abajo,
etc.
1
Este programa permite otros parámetros que puedes consultar en la página de manual correspondiente.
-2-
PRÁCTICA 2: OPERACIONES BÁSICAS SOBRE EL SGBD
En esta práctica introduciremos nuestras primeras sentencias SQL, que no afectarán a la base de
datos a las que estamos conectados. En este documento no describiremos los comandos SQL que
utilizaremos, sino que se puede consultar la sintaxis completa, su utilidad y ejemplos de uso en el
“manual de referencia” de SQL de “PostgreSQL” 2 que podemos encontrar en formato HTML y
PDF en el servidor WWW: www-labs.det.uvigo.es.
Cambio de password
Para cambiar la password en el SGBD se debe utilizar el comando ALTER USER (los comandos
SQL se pueden introducir en mayúsculas o minúsculas de forma indiferente). Consulta el manual
donde encontrarás la forma de hacerlo. Recuerda terminar la sentencia con “;” para que se ejecute.
Creación de nuevas bases de datos
Podemos crear nuevas bases de datos con el comando CREATE DATABASE en su forma más
sencilla. Crea una nueva base de datos y prueba a conectarte a ella sin salir de psql (la creación de
una nueva base de datos no implica una conexión a ella). Puedes comprobar si se ha creado
correctamente con el comando “\l”. El nombre de una base de datos debe ser único en el sistema.
Cuando creamos una nueva base de datos, los demás usuarios del sistema pueden conectarse a ella y
realizar operaciones sobre ella. Para impedir la realización de determinadas operaciones por parte
de determinados usuarios se puede utilizar el comando REVOKE, y para permitirlas GRANT.
Impedir que determinados usuarios puedan conectarse a una base de datos sólo puede hacerlo el
administrador del sistema (las dos bases de datos que ya tiene creadas cada usuario están protegidas
de tal forma que sólo ese usuario puede conectarse a ellas)
Elimiación de nuevas bases de datos
Podemos eliminar bases de datos con el comando DROP DATABASE. Prueba a eliminar las base
de datos que hayas creado anteriormente. Si no se puede eliminar una base de datos se debe
comprobar que no estamos conectados a ella.
Importante: Al finalizar cada sesión de laboratorio debes eliminar todas las bases de datos que no
sean las dos que cada grupo tiene creadas por defecto (practicasX y pruebasX)
2
En el Manual del Lenguaje SQL puede encontrar información útil sobre tipos de datos, operadores, funciones, etc.
-3-
PRÁCTICA 3: OPERACIONES BÁSICAS SOBRE UNA BASE DE DATOS
En esta práctica introduciremos algunas sentencias SQL, que modificarán y consultarán la base de
datos a las que estamos conectados y que nos permitirán comprobar el funcionamiento de algunos
de los conceptos vistos en clase de teoría.
Creación de tablas
El comando que permite crear tablas es CREATE TABLE. Crea dos tablas denominadas
“Profesores” y “Alumno” con los campos que estimes oportuno. Para crear una tabla debes utilizar
solamente los campos obligatorios del comando. Es decir, su sintaxis más sencilla.
Introducción de datos
Para introducir datos en una tabla utilizamos el comando INSERT. Introduce varias filas de datos en
tus tablas.
Consulta de datos de una tabla
En SQL todas las consultas a una base de se realizan con una sola sentencia: SELECT. Esta es, con
diferencia, la sentencia más compleja de SQL. Por ahora la utilizaremos en su forma más sencilla
que nos permite obtener el contenido completo de una tabla:
SELECT * FROM nombre_tabla;
Eliminación de tablas
Para eliminar tablas utilizaremos el comando DROP TABLE. En este momento eliminaremos la
tabla “Profesores” antes creada.
Creación de relaciones
Una relación es una tabla que tiene una clave, es decir que no puede tener filas repetidas (ya que no
puede haber dos filas con la misma clave)
Vamos a crear una relación llamada “Profesores” que tendrá los mismos campos que la tabla que
acabamos de eliminar, y un campo adicional que contendrá el “codigo” del profesor y que será el
campo clave. Para introducir una clave utilizaremos la restricción a nivel de columna PRIMARY
KEY en la sentencia CREATE TABLE.
Podemos comprobar que la tabla “Alumnos” permite entradas repetidas y la relación “Profesores”
no.
Los comandos vistos para introducir datos, consulta y eliminación para tablas son válidos para
relaciones.
Prueba ahora a crear una relación en la que la clave esté formado por un conjunto de varios
atributos en vez de uno sólo. Para ello utilizamos la restricción PRIMARY KEY a nivel de tabla.
Podremos comprobar que podemos introducir filas en las que se repitan valores de alguno de los
campos que forman la clave, pero no para el conjunto de todos ellos.
-4-
Otras restricciones
Como se puede ver en la sintaxis del comando CREATE TABLE se pueden introducir otras
restricciones en una relación como pueden ser valores por defecto, posibilidad de valores nulos
(campos en blanco) o checks (comprobaciones sobre determinados campos). Por ahora nos
olvidaremos de la restricciones REFERENCES y FOREIGN KEY.
También podemos probar el funcionamiento del tipo especial de datos para columnas SERIAL, que
nos permite tener una columna con valores numéricos que se autoincrementan. (Cuando pruebes
este tipo de datos asegurate de consultar la información sobre él en el “Manual de Usuario”, sobre
todo la parte referente a eliminación de relaciones que utilizan este tipo de datos)
Define posibles escenarios para probar estas restricciones. Por ejemplo: introducir un nuevo campo
en la relación “Profesores” que almacene el estado de trabajo que sólo podrá tomar cuatro valores:
A (activo), B (baja), V (vacaciones) e ID (investigador desplazado)
Modificación de una tabla
Podemos utilizar la sentencia ALTER TABLE para modificar la estructura de una relación (añadir
campos, eliminar campos, modificar las características de un campo, añadir restricciones, ...) sin
tener que eliminarla y volver a crearla, como nos ocurriría en el apartado anterior. Comprueba las
posibilidades de este comando.
Eliminación de datos
Para eliminar los valores almacenados en determinadas entradas en una relación utilizaremos el
comando DELETE. Probemos a eliminar todos los “Profesores que están de “baja” de nuestra
relación.
Restriciones de integridad referencial
Para implementar una relación 1:N podemos utilizar las restricciones REFERENCES (para una sola
columna ) y FOREIGN KEY (para una o varias columnas) del comando CREATE TABLE.
Para probar esta característica crearemos una nueva relación “Asignaturas” con los campos que
estimemos oportunos. Cada profesor puede impartir varias asignaturas y una asignatura determinada
es impartida por un solo profesor. Implementa esta relación mediante una clave foránea y
comprueba su correcto funcionamiento por ejemplo intentando introducir una asignatura impartida
por un profesor que no exista.
Carga de comandos
Para terminar esta práctica aprenderemos a utilizar el comando de psql “\i”.
Normalmente, la introducción directa de comandos a través del interprete no es la forma más
adecuada de trabajar. Lo normal es crear nuestro programa SQL con todos sus comandos en un
fichero de texto, de tal manera que podamos conservarlo y modificarlo adecuadamente. Una vez
creado un fichero con diversos comandos SQL podemos cargarlo y ejecutar todos sus comandos
desde el interprete mediante el comando “\i” seguido del nombre del fichero.
Prueba este modo de trabajo que será el que utilizarás habitualmente. Un problema que se puede
presentar es que si cargamos un fichero en el que creamos elementos (bases de datos, tablas, ...) y
-5-
posteriormente lo modificamos y lo volvemos a cargar aparecerán errores porque esos elementos ya
están creados por al carga anterior. Para evitar esto debemos, en el fichero de comandos, eliminar
cada elemento (mediante el comando DROP correspondiente) antes de volver a crearlo. Es decir,
debemos utilizar siempre un comando DROP ..... antes de un comando CREATE ....
También podemos utilizar el programa pg_dump para crear copias de seguridad de nuestras bases
de datos en un fichero (consulta su uso en la página de manual correspondiente). Los ficheros
generados por esta utilidad se pueden recargar en el mismo o en otro SGBD PostgreSQL mediante
el comando “\i” de psql.
PRÁCTICA 4: DISEÑO E IMPLEMENTACIÓN DE UNA BASE DE DATOS
Antes de afrontar el diseño e implementación de la práctica final se recomienda que el alumno
realice una pequeña base de datos y experimente con ella con el fin de familiarizarse con el diseño
de bases de datos.
Para ello se propone la realización de una base de datos para la gestión de Proyectos Fin de Carrera
(PFC) en realización de un centro universitario.
La información que se debe almacenar es la siguiente:
1. Alumnos que los realizan.
2. Profesores que los dirigen.
3. Información sobre los PFC.
y las restricciones que se definen para los datos son las siguientes:
1. Los alumnos se definen por su número de matrícula, DNI y nombre y apellidos. Un alumno
realiza un solo PFC.
2. Un PFC se define por su título, su tema, un número de orden y por la fecha de comienzo. Un
PFC determinado no puede ser realizado por varios alumnos.
3. Un profesor se define por su DNI, nombre, apellidos y domicilio. Un PFC es dirigido por un
único profesor.
Se recomienda que una vez implementada la base de datos se definan varias consultas sobre la
misma, como pueden ser: “Título de los proyectos dirigidos por un determinado profesor”,
“Nombre de alumnos que realizan proyectos comenzados antes de una determinada fecha”, etc.
-6-