Download PRCTICAS DE BASES DE DATOS
Document related concepts
Transcript
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-