Laboratorio Numero dos

Laboratorio Nùmero dos 

Para ser entregado a mas tardar

el 15 de enero del 2023.

Base de datos II

Uso de bases de datos

Práctica 2: El lenguaje SQL II

En la primera parte de la práctica hemos realizado consultas y modificaciones sobre los datos

utilizando SQL. En esta segunda parte, sobre la misma base de datos, añadiremos lógica dentro de la base de datos utilizando procedimientos almacenados y disparadores.

Para la correcta ejecución de la segunda parte de la práctica, es necesario volver a crear la base de datos de nuevo e insertar otra vez los datos iniciales utilizando los scripts que se proporcionan junto a este enunciado. Este paso es necesario debido a la introducción de los cambios siguientes en el esquema de la BD respecto a la primera parte de la práctica:

● Creación de una nueva tabla llamada REPORT_ALBUM, con las columnas id_album,

num_songs, num_composers, num_members_alive, y longest_song_title. 

La columna id_album corresponde al identificador del álbum y es clave primaria. El resto de columnas se calculan en 

relación al álbum. Así pues, num_songs es el número de canciones que tiene el álbum en

cuestión, num_composers es el número de compositores diferentes que han compuesto

canciones en dicho álbum, num_members_alive es el número de miembros del grupo autor de

dicho álbum que todavía están vivos, y longest_song_title es el nombre de la canción del

álbum en cuestión que tiene una duración mayor, teniendo en cuenta que en el caso que haya

más de una canción con la misma longitud del título, se escogerá la primera por órden

alfabético.

● Tabla ALBUM: se ha añadido el atributo album_duration que representa la duración total de

las canciones que forman parte del álbum.

Encontraréis las sentencias de creación y modificación de estas tablas en el archivo create_db.sql.

Nota importante: El SQL implementado en PostgreSQL puede aceptar diferentes variantes de

sintaxis, que además pueden diferir según la versión que instaléis, y que pueden ser o no SQL

estándar. Evitad (excepto que se indique lo contrario) utilizar sentencias de este tipo, y concentraros

en las que se explican en los módulos didácticos. Si usáis sentencias SQL estándar vuestro código

funcionará en cualquier SGBD.

Pregunta 1 (50 % puntuación)

Enunciado

Se pide que creéis un procedimiento almacenado que, dado un año, actualice los datos que hacen

referencia al álbum o álbumes publicados en dicho año y los almacene en la tabla REPORT_ALBUM.

Dicha tabla se habrá creado con la ejecución del archivo create_db.sql, el cual deberéis ejecutar en

primer término.

Si ya existe una entrada en la tabla para el álbum (id_album), ésta se tendrá que modificar con los

nuevos valores. Además de guardarlo en la tabla, el procedimiento devolverá el resultado del report.

Se tendrá que informar al usuario con un mensaje específico cuando no exista ningún álbum publicado

en el año especificado.

La firma del procedimiento pedido y el tipo que devolverá son los siguientes:

CREATE OR REPLACE FUNCTION update_report_album(p_year INTEGER)

RETURNS SETOF REPORT_ALBUM_TYPE

donde REPORT_ALBUM_TYPE es de tipo:

CREATE TYPE REPORT_ALBUM_TYPE AS (

t_id_album INTEGER,

t_num_songs INTEGER,

t_num_composers INTEGER,

t_num_members_alive INTEGER,

t_longest_song_title VARCHAR(255)

);

Criterios de evaluación

● Las propuestas de solución que no se puedan ejecutar, es decir las que den error de sintaxis,

no serán evaluadas.

● Se valorará positivamente el uso de sentencias SQL estándar, al margen de otros elementos

que se puedan indicar en el enunciado.

● Para obtener la máxima nota, el código SQL de vuestra solución tiene que ser eficiente. Por

ejemplo, se valorará negativamente realizar más joins de los necesarios.

● Para obtener la máxima nota, la propuesta de solución tiene que incluir pruebas que cubran

todas las posibles situaciones descritas en el enunciado. Por ejemplo, se deberían cubrir todas

las posibles situaciones de error.

● Para obtener la máxima nota, la propuesta de solución tiene que incluir los resultados,

mediante el uso de capturas de pantalla o cualquier otro mecanismo similar.

Pregunta 2 (50 % puntuación)

Enunciado

En la tabla ALBUM tenemos la columna album_duration con el objetivo de almacenar la duración

total de las canciones que forman parte de dicho álbum.

Cread un disparador o disparadores, sobre la tabla o tablas que sean necesarias, de manera que se

mantenga correctamente actualizada la columna album_duration de la tabla ALBUM.

En concreto, se pide que esta columna refleje los valores pedidos y que éstos siempre se mantengan

actualizados en función de los cambios.

Podemos suponer que los usuarios o programas nunca actualizarán directamente la columna

album_duration de la tabla ALBUM, y que, en el momento de insertar una nuevo álbum, el valor

de la columna album_duration será cero.

Nota: En el siguiente enlace encontraréis información sobre operadores y funciones de Fecha y Hora.

https://www.postgresql.org/docs/9.4/functions-datetime.html

Criterios de evaluación

● Las propuestas de solución que no se puedan ejecutar, las que den error de sintaxis, no serán

evaluadas.

● Se valorará positivamente el uso de sentencias SQL estándar, al margen de otros elementos

que se puedan indicar en el enunciado.

● Para obtener la máxima nota, el código SQL de vuestra solución tiene que ser eficiente. Por

ejemplo, se valorará negativamente realizar más joins de los necesarios.

● Para obtener la máxima nota, la propuesta de solución tiene que incluir pruebas que cubran

todas las posibles situaciones descritas en el enunciado.

● Para obtener la máxima nota, la propuesta de solución tiene que incluir los resultados,

mediante capturas de pantalla o de alguna forma similar.

● Para obtener la máxima puntuación, el código que actualice album_duration tiene que

pertenecer a una única función.