====== Bases de Datos objeto-relacionales ====== ===== Introducción a las Bases de Datos objeto-relacionales ===== Las Bases de Datos objeto-relacionales son una extensión de las Bases de Datos relacionales tradicionales, a las cuales se les añaden ciertas características propias de la Programación Orientada a Objetos. Algunas de estas características se comentarán en este Resultado de Aprendizaje , y concretamente para el motor de Bases de Datos objeto-relacional //PostgreSQL//. Antes de continuar con las extensiones propias de las Bases de Datos objeto-relacionales, hay que destacar las diferencias existentes entre PostgreSQL y el motor MySQL con el que se ha venido trabajando todo el curso. Hay que tener en cuenta que, independientemente de que el nuevo motor sea objeto-relacional, existirán algunas diferencias de funcionamiento y de síntaxis a la hora de trabajar con el mismo. ===== Conectar con PostgreSQL ===== A la hora de conectar con //PostgreSQL//, como ocurría con //MySQL//, tenemos dos opciones: * Conectar mediante su propia consola de comandos (lado cliente) * Utilizar alguna herramienta gráfica (//PostgreSQL// incluye la suya propia, //pgAdmin//) En este apartado veremos como manejarnos con la primera de las opciones, con la consola de comandos de //PostgreSQL// de forma que al menos sepamos llevar a cabo las funciones básicas como conectarnos, acceder a una Base de Datos y realizar las operaciones más comunes sobre ella. * Conectar con //PostgreSQL//: Ejecutaremos el comando ''psql'' ==== Herramienta de administración pgAdmin ==== ==== Comandos ==== ===== Tipos de datos ===== ==== Tipos de datos básicos ==== Independientemente de los nuevos tipos de datos que postgreSQL incorpora por ser un motor objeto-relacional, presenta algunas diferencias en cuanto a declarar los tipos tradicionales que ya conociamos en MySQL * ''VARCHAR'' se declara ahora como ''text'' y no es necesario indicar la longitud máxima * ''INTEGER'' se declara de la misma forma, como ''integer'' * ''FLOAT'' se declara como ''real'' y se utiliza para indicar números de coma flotante de precisión simple * ''DOUBLE'' se declara como ''double precision'' para indicar números de coma flotante de doble precisión * También muy común utilizar el tipo de datos ''numeric'' para almacenar cantidades de moneda o valores donde la precisión es importante. * En el caso de campos autonuméricos se utiliza la palabra reservada ''serial'' sin indicar el tipo de dato, aunque internamente se almacena utilizando un campo //integer// de 4 bytes ==== Colecciones ==== Los //array//((https://www.postgresql.org/docs/9.4/static/arrays.html)), como tipo de datos colección, puede ser usado en BBDD objeto-relacionales, creando así estructuras de datos más complejas tal y como se hace con lenguajes de programación de aplicaciones. Por ejemplo: CREATE TABLE personas ( id serial PRIMARY KEY, nombre text, apellidos text, fecha_nacimiento timestamp, telefonos text[] ); Que a la hora de trabajar en SQL sería asi (Presta atención a cómo debe asignarse el valor del array): -- Inserta una persona con varios números de teléfono INSERT INTO personas (nombre, apellidos, fecha_nacimiento, telefonos) VALUES ('Peter', 'Parker', '1999-01-08', '{"976654321", "654786556", "976123456"}'); -- Muestra todos los teléfonos de una persona SELECT telefonos FROM personas WHERE nombre = 'Peter' AND apellidos = 'Parker'; -- Muestra el primer teléfono de una persona SELECT telefonos[1] FROM personas WHERE nombre = 'Peter' AND apellidos = 'Parker'; -- Muestra desde el primero al segundo teléfono de una persona SELECT telefonos[1:2] FROM personas WHERE nombre = 'Peter' AND apellidos = 'Parker'; -- Muestra todos los teléfonos de una persona SELECT telefonos[1:array_length(telefonos, 1)] -- equivalente a telefonos FROM personas WHERE nombre = 'Peter' AND apellidos = 'Parker'; -- Muestra el nombre y apellidos de las personas que -- tienen un número determinado SELECT nombre, apellidos FROM personas WHERE '97654321' = ANY(telefonos); Además //PostgreSQL// proporciona una serie de funciones para operar sobre los //array//((https://www.postgresql.org/docs/9.4/static/functions-array.html)): Suponemos un vector compuesto por 3 ciudad españolas ciudades = {'madrid', 'zaragoza', 'barcelona'} * ''array_length(unarray, dimension)'': Devuelve la longitud del array de la dimensión especificada select array_length(ciudades, 1) > 3 * ''array_cat(unarray, otroarray)'': Concatena ambos array y devuelve el resultado select array_cat(ciudades, ciudades) >{'madrid', 'zaragoza', 'barcelona', 'madrid', 'zaragoza', 'barcelona'} * ''array_replace(unarray, valor_viejo, valor_nuevo)'': Reemplaza un valor por otro en todo el array select array_replace(ciudades, 'zaragoza', 'teruel') >{'madrid', 'teruel', 'barcelona'} * ''array_to_string(unarray, texto)'': Convierte un vector en una cadena de texto select array_to_string(ciudades, ',') > madrid,zaragoza,barcelona * ''string_to_array(texto, texto)'': Convierte un texto es un vector select string_to_array('madrid,zaragoza,barcelona', ',') > {madrid,zaragoza,barcelona} ==== Datos estructurados ==== Con este tipo de datos, //Composite//((https://www.postgresql.org/docs/9.4/static/rowtypes.html)) en PostgreSQL, existe la posibilidad de crear estructuras de datos más complejas, compuestas de varios campos de información. Se utilizan para definir la estructura de una fila o un registro. Por ejemplo: CREATE TYPE direccion_postal AS ( calle text, numero integer, provincia text, cp text ); CREATE TABLE personas ( id serial primary key, nombre text, apellidos text, fecha_nacimiento timestamp, telefonos text[], direccion direccion_postal ) Así, en el caso de una inserción, sería asi: INSERT INTO personas (nombre, apellidos, fecha_nacimiento, telefonos, direccion) VALUES ('Un', 'tipo', '2000-01-01', '{"976654321", "654786556"}', ROW('mi calle', 23, 'Zaragoza', 50018)); Que a la hora de consultar sería como sigue: SELECT (direccion).cp FROM personas WHERE nombre = 'Peter' AND apellidos = 'Parker'; En el caso de necesitar un array de estructuras sería de la siguiente manera: CREATE TYPE direccion_postal AS ( calle text, cp text ); CREATE TABLE alumnos ( id serial PRIMARY KEY, nexp text NOT NULL UNIQUE, nombre text NOT NULL, apellidos text NOT NULL, telefono text, email text UNIQUE, direcciones direccion_postal[], id_companero int REFERENCES alumnos (id) ); Que a la hora de insertar datos quedaría: INSERT INTO alumnos (nexp, nombre, apellidos, telefono, email, id_companero, direcciones) VALUES ('11111', 'nombre', 'apellidos', '37834764', 'asda@asda.es', 1, array[ROW('calle', '4663'), ROW('calle2', '34334')]::direccion_postal[]); ==== Enumeraciones ==== Las enumeraciones((https://www.postgresql.org/docs/9.4/static/datatype-enum.html)) en //PostgreSQL// se definen como un nuevo tipo de datos compuesto de una lista fija de valores constantes. CREATE TYPE colores AS ENUM ('rojo', 'amarillo', 'verde', 'negro', 'blanco'); Además //PostgreSQL// proporciona una serie de funciones((https://www.postgresql.org/docs/9.4/static/functions-enum.html)) para trabajar sobre este tipo de datos: * ''enum_first'': Devuelve el primer valor de una enumeración SELECT enum_first(null::colores) > rojo * ''enum_last'': Devuelve el último valor de una enumeración SELECT enum_last(null::colores) > blanco * ''enum_range'': Devuelve el rango de valores entre dos dados SELECT enum_range(null::colores) > {rojo, amarillo, verde, negro, blanco} SELECT enum_range('amarillo'::colores, 'negro'::colores) > {amarillo, verde, negro} Así, es posible utilizar enumeraciones previamente declaradas como tipos de datos para las columnas de una tabla CREATE TABLE muebles ( id serial PRIMARY KEY, nombre text, peso real, color colores ); ===== Claves ajenas ===== Para definir las claves ajenas en una tabla es suficiente con indicar la tabla y campo clave al que se hace referencia en el momento de definir la tabla. Al igual que en MySQL habrá que tener en cuenta que la tabla a la que se hace referencia debe haber sido creada ya, por lo que tendremos cuidado de colocarla siempre por delante en el script de creación de las mismas. CREATE TABLE ciudades ( id serial PRIMARY KEY, nombre text, extension real ); CREATE TABLE habitantes ( id serial PRIMARY KEY, nombre text, apellidos text, ciudad int REFERENCES ciudades(id) ); ===== Transacciones ===== En PostgreSQL una transacción se define entre las instrucciones ''BEGIN'' y ''COMMIT'' de la siguiente manera: BEGIN; UPDATE ciudades SET extension = 100 WHERE id = 3; UPDATE habitantes SET ciudad = 1 WHERE id = 2; . . . COMMIT; Hay que tener en cuenta que algunas aplicaciones clientes engloban todas las instrucciones como transacciones de forma implícita, por lo que conviene leer detenidamente la documentación de dichas aplicaciones. ===== Herencia ===== La herencia((https://www.postgresql.org/docs/9.4/static/tutorial-inheritance.html)) entre tablas permite, de forma similar a lo que ocurre en Programación Orientada a Objetos entre diferentes clases, que una tabla incorpore los campos de otra indicando que una hereda de la otra. Además, conlleva algún comportamiento adicional que veremos a continuación: CREATE TABLE articulos ( id serial PRIMARY KEY, nombre text, descripcion text, precio real ); CREATE TABLE comestibles ( fecha_caducidad timestamp, ingredientes text[] ) INHERITS (articulo); En este caso, si queremos listar el nombre y descripción de todos los artículos con un precio superior a 10 euros, incluyendo los comestibles: SELECT nombre, descripcion FROM articulos WHERE precio > 10; Por otro lado, puede ser útil mostrar solamente aquellos artículos que no son comestibles SELECT nombre, descripcion FROM ONLY articulos WHERE precio > 10; En el caso de que sólo queramos mostrar aquellos artículos que son comestibles, simplemente accedemos a la tabla correspondiente: SELECT nombre, descripcion FROM comestibles WHERE precio > 10; A pesar de los beneficios y la utilidad de estos conceptos de herencia que incorpora //PostgreSQL//, hay que tener en cuenta que a su vez presenta ciertas limitaciones((https://www.postgresql.org/docs/9.4/static/ddl-inherit.html)) que habrá que tener en cuenta en determinadas situaciones. A continuación se expone un ejemplo práctico de una de las limitaciones que seguro que nos encontraremos en este curso, y también qué pasos llevar a cabo para solventar cualquier problema que pueda aparecer Supongamos, siguiendo con el ejemplo anterior, la siguiente Base de Datos: CREATE TABLE proveedores ( id serial PRIMARY KEY, nombre text NOT NULL, categoria text ); CREATE TABLE articulos ( id serial PRIMARY KEY, nombre text, descripcion text, precio real, id_proveedor int REFERENCES proveedores ); CREATE TABLE comestibles ( fecha_caducidad timestamp, ingredientes text[] ) INHERITS (articulos); ===== Funciones ===== ==== Funciones matemáticas ==== * ''abs(numero)'': Devuelve el valor absoluto de un número select abs(-4) > 4 ==== Funciones de cadena ==== * ''concat(cadena1, cadena2)'': Concatena dos cadena y devuelve el resultado select concat('esto será ', 'una cadena') > esto será una cadena * ''length(cadena)'': Devuelve la longitud de una cadena select length('una cadena') > 10 * ''md5(texto)'': Devuelve el hash del texto que se pasa como parámetro select md5('texto') > 62059a74e9330e9dc2f537f712b8797c * ''substr(cadena, indice, cantidad)'': Devuelve la subcadena que resulta de extraer desde el índice especificado el número de caracteres indicados por ''cantidad'' select substr('una cadena', * ''reverse(cadena)'': Devuelve la cadena inversa ==== Funciones de Fecha ==== * ''age(timestamp)'': Devuelve el tiempo pasado entre hoy y la fecha que se pasa como parámetro select age(timestamp '2000-01-01') > 17 years 3 mons 11 days * ''current_date'': Devuelve la fecha de hoy select current_date > 2012-12-03 * ''current_time'': Devuelve la hora actual select current_time > 18:05:25.13039485 * ''current_timestamp'': Devuelve la fecha y hora de hoy select current_timestamp > 2012-12-03 11:35:32.58700 * ''extract(datepart)'': Extrae una parte de fecha de una fecha determinada select extract(month from current_date) > 12 select extract(day from current_date) > 03 ==== Funciones de información del sistema ==== * ''current_database()'' select current_database() > prueba * ''current_user'' select current_user > postgres * ''version()'' select version() PostgreSQL 9.5.3 on x86_64-pc-linux-gnu, compiled by gcc (Debian 5.3.1-19) 5.3.1 20160509, 64-bit ===== Programación en PostgreSQL ===== ==== Procedimientos almacenados ==== ==== Funciones almacenadas ==== Las funciones de //PostgreSQL//, como ocurre con las de //MySQL//, quedan almacenadas en la Base de Datos donde se crean y pueden ser luego utilizadas en otras estructuras de código o bien directamente desde las consultas ''SQL''. Además, como ocurre en todos los lenguajes de programación, las funciones de //PostgreSQL// deben devolver siempre un valor, aunque, como ocurre en lenguajes como //Java//, es posible indicar ''void'' como palabra reservada en el tipo de devolución y entonces la función ya no tiene que devolver un valor. CREATE [OR REPLACE] FUNCTION ( , ) RETURNS AS $$ DECLARE -- Declaración de variables BEGIN -- Instrucciones END; $$ LANGUAGE ; Donde '''' se puede sustituir por el lenguaje que queramos usar para escribir el código. Hay varios en PostgreSQL y nosotros nos centraremos en dos de ellos: ''SQL'' y ''plpgsql''. Si empleamos el primero podremos prescindir de las marcas de inicio y final de bloque (''BEGIN'' y ''END'') y no podremos declarar variables por lo que el bloque ''DECLARE'' no puede aparecer. A continuación se muestran algunos ejemplos de funciones almacenadas de //PostgreSQL// con los diferentes lenguajes que se han comentado. La primera función es un simple ejemplo que lanza una sentencia ''SQL'' y no devuelve ningún valor. Será suficiente con una simple sentencia ''SQL'' por lo que podemos utilizar únicamente dicho lenguaje y especificarlo asi al final de la función. CREATE FUNCTION limpiar_articulos() RETURNS void AS $$ DELETE FROM articulos WHERE precio < 0; $$ LANGUAGE sql; La función equivalente utilizando ''plpgsql'' como lenguaje sería la siguiente. CREATE FUNCTION limpiar_articulos() RETURNS void AS $$ BEGIN DELETE FROM articulos WHERE precio < 0; END; $$ LANGUAGE plpgsql; En el caso de estas funciones donde indiquemos que vamos a utilizar el lenguaje ''SQL'' sólo podremos usarlas para lanzar diferentes sentencias del lenguaje realizando sustituciones con los parámetros que se le pasen y devolviendo resultados mediante la instrucción ''SELECT''. Si queremos poder utilizar un lenguaje de programación completo para realizar funciones almacenadas tendremos que indicar, como ya se ha adelantado anteriormente, que usamos el lenguaje ''plpgsql'' que permitirá emplear sentencias de flujo de código, declaración de variables, asignaciones, . . . La siguiente función incrementa en una cantidad el precio de un articulo y devuelve el precio final del mismo CREATE FUNCTION subir_precio(id_articulo integer, subida real) RETURNS real AS $$ DECLARE precio_final int; BEGIN UPDATE articulos SET precio = precio + subida WHERE id = id_articulo; precio_final := (SELECT precio FROM articulos WHERE id = id_articulo); RETURN precio_final; END; $$ LANGUAGE plpgsql; La siguiente función realiza la misma operación pero no se ha dado nombre a los parámetros sino que se ha utilizado su posición en la declaración de la función para identificarlos CREATE FUNCTION subir_precio(integer, real) RETURNS real AS $$ DECLARE precio_final int; BEGIN UPDATE articulos SET precio = precio + $2 WHERE id = $1; precio_final := (SELECT precio FROM articulos WHERE id = $1); RETURN precio_final; END; $$ LANGUAGE plpgsql; La siguiente función vuelve a realizar la misma operación pero se utiliza una claúsula de la propia sentencia ''UPDATE'' para devolver el precio final del articulo CREATE FUNCTION subir_precio(integer, real) RETURNS real AS $$ BEGIN UPDATE articulos SET precio = precio + $2 WHERE id = $1 RETURNING precio; END; $$ LANGUAGE plpgsql; El siguiente bloque (función y su llamada en una consulta) muestra cómo es posible pasar como parámetro una fila completa para, desde la función, acceder a los campos que sean necesarios para realizar la operación que se desee CREATE FUNCTION precio_iva(articulos) RETURNS real AS $$ BEGIN SELECT $1.precio * 1.16; END; $$ LANGUAGE plpgsql; SELECT nombre, precio_iva(articulos.*) FROM articulos === Estructuras de control de flujo === * Sentencia IF IF condicion-1 THEN . . . ELSIF condicion-2 THEN . . . ELSE . . . END IF: * Sentencia LOOP LOOP -- Instrucciones EXIT [] WHEN ; END LOOP; * Sentencia FOR [ ] FOR IN [REVERSE] .. [BY ] LOOP -- Instrucciones END LOOP [etiqueta]; === Sobrecarga de funciones === //PostgreSQL// permite lo que se conoce como sobrecarga de funciones, que consiste en que es posible declarar más de una función con el mismo nombre siempre y cuando cambie el número de parámetros de la misma. La siguiente función incrementa el precio de un artículo en una cantidad determinada pero comprueba además que el precio final no sobrepase un precio impuesto como límite. En ese caso no realiza ninguna acción y devuelve un valor ''NULL''. En este caso esta segunda función sobrecarga a la primera y en función de los parámetros que se pasen //PostgreSQL// ejecutará una u otra. CREATE FUNCTION subir_precio(id_articulo INTEGER, subida REAL, precio_maximo REAL) RETURNS REAL AS $$ DECLARE precio_final real; BEGIN precio_final := (SELECT precio FROM articulos WHERE id = id_articulo) + subida; IF precio_final > precio_maximo THEN RETURN NULL; END IF; UPDATE articulos SET precio = precio + subida WHERE id = id_articulo; RETURN precio_final; END; $$ LANGUAGE plpgsql; === Eliminar una función === Para eliminar una función se utiliza la instrucción ''DROP FUNCTION'' de la siguiente manera. DROP FUNCTION [IF EXISTS] (tipo_param1, tipo_param2, . . .); Por ejemplo, si quisieramos eliminar las últimas dos funciones creadas justo arriba DROP FUNCTION subir_precio(integer, real); DROP FUNCTION subir_precio(integer, real, real); ==== Triggers ==== De forma similar a como ocurren en //MySQL//, los triggers en PostgreSQL se ejecutan siempre asociados a un evento que ha ocurrido sobre una tabla. CREATE TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF} {event} ON table_name [FOR [EACH] {ROW | STATEMENT}] EXECUTE PROCEDURE trigger_function CREATE OR REPLACE FUNCTION nombre_mayusculas() RETURNS trigger AS $$ BEGIN NEW.nombre := UPPER(NEW.nombre); END; $$ LANGUAGE plpgsql CREATE TRIGGER nombre_mayusculas BEFORE INSERT ON articulos FOR EACH ROW EXECUTE PROCEDURE nombre_mayusculas(); ---- ===== Ejercicios ===== {{ ejercicio.png}} - Se desea gestionar la información correspondiente a un centro de enseñanza: * Por cada alumno se almacenará la siguiente información: DNI, apellidos, nombre, domicilio, teléfono y ciclo que estudia. También se precisa conocer en cada momento las asignaturas en las que está matriculado, así como la nota en cada una de ellas * Por cada asignatura guardaremos un código, título y número de horas * Cada asignatura puede estar impartida por uno o más profesores. Del profesor se deben conocer los mismos datos que para los alumnos, salvo el del ciclo que estudia. El número máximo de asignaturas que puede impartir un profesor es de 6, aunque puede que no imparta ninguna * Algunos profesores tienen un supervisor (sólo uno) que es otro profesor * Cada asignatura tendrá un aula asignada, que se identifica mediante el número de edificio y el número de aula. Los números de aulas se pueden repetir entre edificios - La Base de Datos COMPAÑÍA se ocupa de los empleados, departamentos y proyectos de una empresa, de acuerdo con los siguientes requisitos: * La compañía está organizada en departamentos. Cada departamento tiene un nombre único, un número único y un empleado que lo dirige. Se debe almacenar la fecha en que dicho empleado comenzó a dirigir ese departamento. Hay que tener en cuenta que un departamento puede tener diferentes localizaciones. * Cada departamento controla un cierto número de proyectos, cada uno de los cuales tiene un nombre y un número únicos y se realizan en un solo lugar * Se almacena el nombre, número de la Seguridad Social, dirección, salario, sexo y fecha de nacimiento de cada empleado. Todo empleado está asignado a un departamento, pero puede trabajar en varios proyectos que no tienen por que ser del mismo departamento. Nos interesa saber el número de horas que un empleado dedica a cada uno de los proyectos asignados * También se quiere guardar la relación de las cargas familiares de cada empleado para administrar el seguro médico. Almacenaremos el nombre, sexo y fecha de nacimiento de cada una de las cargas familiares y su parentesco con el empleado - Se quiere diseñar una Base de Datos para almacenar todos los datos de un campeonato de fútbol sala que se organiza este año en la ciudad. Aquellos que quieran participar deberán formar un equipo (nombre, patrocinador, color\_camiseta, color\_2\_camiseta, categoría, . . .) e inscribirse en el campeonato. A medida que transcurran los partidos se irán almacenando los resultados de éstos, así como qué equipos lo jugaron, en qué campo se jugó, quién lo arbitró y alguna incidencia que pudiera haber ocurrido (en caso de que no ocurran incidencias no se anotará nada. Además, los participantes deberán rellenar una ficha de suscripción con algunos datos personales (nombre, apellidos, edad, dirección, teléfono, . . .) - Se quiere diseñar una Base de Datos para controlar el acceso a las pistas deportivas de Zaragoza. Se tendrán en cuenta los siguientes supuestos: * Todo aquel que quiera hacer uso de las instalaciones tendrá que registrarse y proporcionar su nombre, apellidos, email, teléfono, dni y fecha de nacimiento * Hay varios polideportivos en la ciudad, identificados por nombre, dirección, extensión (en m2) * En cada polideportivo hay varias pistas de diferentes deportes. De cada pista guardaremos un código que la identifica, el tipo de pista (tenis, fútbol, pádel, . . .), si está operativa o en mantenimiento, el precio y la última vez que se reservó * Cada vez que un usuario registrado quiera utilizar una pista tendrá que realizar una reserva previa a través de la web que el ayuntamiento ha creado. De cada reserva queremos registrar la fecha en la que se reserva la pista, la fecha en la que se usará y el precio. Hay que tener en cuenta que todos los jugadores que vayan a hacer uso de la pista deberán estar registrados en el sistema y serán vinculados con la reserva ===== Prácticas ===== * **Práctica 5.1** El modelo objeto-relacional en PostgreSQL * **Práctica 5.2** Creación de una Base de Datos O-R sobre PostgreSQL * **Práctica 5.3** Consultas y Programación de una Base de Datos O-R sobre PostgreSQL ---- (c) 2017-2020 Santiago Faci