Laboratorio Uno

Práctica 1: El lenguaje SQL  

Queremos disponer de una base de datos para registrar información sobre grupos musicales,

canciones y músicos, que describiremos en términos de un conjunto de relaciones.

La notación que utilizaremos para definir su intención es: 1) claves primarias subrayadas, 2) claves

foráneas en cursiva y 3) los atributos no pueden tener valor nulo a menos que se diga lo contrario.

A continuación, se describen cada una de las relaciones.

MUSICIAN (id_musician, name, birth, death, gender, nationality)

La relación MUSICIAN contiene los datos generales sobre los músicos que aparecen en la BD. En

concreto, para cada músico se guarda un número identificador (id_musician) que es clave primaria, el

nombre (name), la fecha de nacimiento (birth), fecha de defunción (death) que puede ser NULL, el

género (gender) y la nacionalidad (nationality).

El atributo gender solo puede tomar los valores {‘M’, ‘F’}. M para el masculino y F para el femenino.

BAND (id_band, name, year_formed, year_dissolution, style, origin)

La relación contiene información sobre los grupos musicales. En concreto, para cada grupo, se guarda

el identificador (id_band) que es clave primaria, el nombre del grupo (name), el año de formación

(year_formed), el año de disolución (year_dissolution), el estilo musical (style) y el país de origen del

grupo (origin).

El atributo style sólo puede tomar los valores {‘Blues’, ‘Country’, ‘Heavy’, ‘Jazz’, ‘Pop’, ‘Punk’, ‘Reggae’, ‘Rock’, ‘Soul’, ‘Thrash’, ‘Techno’}.

ALBUM (id_album, title, year, id_band)

Información sobre los álbumes. Para cada álbum se almacena su identificador (id_album) que es clave

primaria, el título (title), el año de publicación (year) y el identificador del grupo musical que ha grabado

el álbum (id_band), que es clave foránea de BAND con política de actualización en cascada en caso

de modificación.

MEMBER (id_musician, id_band, instrument)

Información sobre los músicos que forman parte de un grupo o grupos musicales. Se almacenan los

valores (id_musician, id_band, instrument) que son clave primaria, donde id_musician es clave foránea

de MUSICIAN con política de borrado en cascada, id_band es clave foránea de BAND con política de

actualización en cascada y, finalmente, instrument es el instrumento que utiliza el músico en el grupo;

la voz (Vocals) se considera un instrumento. Un músico puede tocar más de un instrumento en un

grupo.

El atributo instrument, sólo puede tomar los valores {‘Trumpet’, ‘Clarinet’, ‘Oboe’, ‘Flute‘,’Vocals’}.

SONG (id_song, title, duration, name_album)

Guarda información sobre las canciones. Por cada canción guarda el identificador (id_song) que es

clave primaria, el título (title), la duración en minutos y segundos (duration) y el nombre del álbum al

que pertenece la canción (name_album), que puede ser NULL.

COMPOSER (id_musician, id_song)

Información sobre las canciones y los compositores de las mismas. Se almacenan los valores

(id_musician, id_song) que son clave primaria, donde id_musician es clave foránea de MUSICIAN,

id_song es clave foránea de SONG.

ACLARACIONES

En el fichero create_db.sql se proporcionan las sentencias SQL necesarias para crear la base de

datos. En el fichero inserts_db.sql se proporcionan las sentencias de inserción de datos que hay

que ejecutar para responder a las preguntas de la práctica.

Recordad que para poder trabajar sobre tablas que son de un esquema de base de datos concreto se

debe utilizar el nombre del esquema como prefijo, o hay que tener actualizada la variable

search_path. Para vuestra comodidad, al principio de cada sesión indicad:

SET search_path TO ”nombre_bd_que_utilicéis”;

Así mismo, acordaos de eliminar de la solución que entreguéis aquellas sentencias auxiliares que

utilicéis para vuestras pruebas, como DROP, inserciones de prueba, etc. que puedan alterar las salidas

de los resultados esperados.

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

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

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

en las que se explican en los módulos didácticos. Esto es especialmente relevante en el caso del

módulo 4 (evaluado en esta primera parte de la práctica), donde se explica SQL estándar. Si utilizáis

sentencias SQL estándar, vuestro código funcionará en cualquier SGBD.

Pregunta 1  

Enunciado

En el fichero create_db.sql se proporcionan las sentencias SQL necesarias para crear las tablas

MUSICIAN, BAND, MEMBER, SONG y COMPOSER.

Después de un último análisis de la base de datos, se ha llegado a la conclusión que se necesita

introducir las mejoras siguientes:

1. ¿Qué sentencias son necesarias para crear la tabla ALBUM según la definición dada? (12,5%)

2. También se pide dar las sentencias SQL de alteración que permitan hacer algunos cambios en

la base de datos:

    2.1. En la tabla MEMBER queremos hacer las siguientes modificaciones (5%):

           a. Añadir la clave primaria de la relación tal y como se establece en la definición de la relación.

           b. El atributo Instrument también puede tener los valores {‘Bass’, ‘Drums’, ‘Guitar‘,

‘Keyboard’}.

     2.2. En la tabla BAND queremos añadir las siguientes modificaciones (2,5%):

           a. La columna year_dissolution puede tener valor NULL que será su valor por defecto y en caso                 de tener un valor diferente a NULL, este no puede ser nunca inferior a year_formed.

     2.3. En la tabla SONG queremos añadir las siguientes modificaciones (10%):

           a. La columna duration no puede ser negativa ni cero.

           b. La columna name_album no es necesaria y se debe suprimir.

           c. Añadir la columna id_album que es el identificador de ALBUM y puede tener valor NULL. 

           d. Añadir la restricción de id_album como clave foránea de ALBUM con política de

actualización en cascada en caso de borrado.

En el fichero inserts_db.sql encontraréis las sentencias de inserción que debéis lanzar sobre las

tablas una vez modificadas. Tened en cuenta que el número de columnas que deben incluir las tablas

es el que viene dado en el fichero inserts_db.sql, por lo cual no se aceptarán como soluciones

válidas aquellas prácticas que contemplen otro tipo de columna no incluida en este fichero.

Nota importante: Para aplicar los cambios requeridos NO debéis utilizar los dominios explicados en el

módulo teórico.

Nota: En el siguiente enlace encontraréis información sobre el orden de ejecución de las expresiones.

https://www.postgresql.org/docs/current/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL

Pregunta 2  

Enunciado

a) Diseñad una consulta que devuelva los músicos compositores miembros de algún grupo de

Pop o de Rock que usen más de un instrumento y que no sean americanos, En concreto,

queremos saber el nombre, la edad y la nacionalidad del músico así como el número de

canciones que ha compuesto, ordenados por edad de mayor a menor y, en caso de empate,

por orden alfabético del nombre.

b) Diseñad una consulta que devuelva las canciones compuestas por músicos vivos que no sean

de nacionalidad danesa, ni guitarristas, ni cantantes en ningún grupo . En concreto, queremos

que nos muestre el título y la duración de la canción, el nombre del álbum y el año en que se

lanzó, el nombre del compositor y el instrumento que toca.

c) Diseñad una vista (six_albums_more_songs) que obtenga los 6 álbumes con mayor número de

canciones, el año de lanzamiento de los mismos, así como el número de canciones que tiene, y

el número de compositores diferentes de las canciones. En concreto, queremos, el título y el

año de lanzamiento del álbum, el nombre de la banda que lo lanzó, el número de canciones

que componen el álbum y el número de compositores diferentes de las canciones. Queremos el

resultado ordenado por el número de canciones de los álbumes de mayor a menor, en caso de

empate, por orden ascendente del número de compositores, y en caso de empate por orden

alfabético del nombre del álbum.

Nota : En los siguientes enlaces encontraréis información sobre:

○ funciones agregadas: https://www.postgresql.org/docs/8.2/functions-aggregate.html

○ la cláusula LIMIT: https://www.postgresql.org/docs/8.1/queries-limit.html

○ funciones de fecha y hora: https://www.postgresql.org/docs/8.1/functions-datetime.html

Pregunta 3  

Enunciado

Se ha descubierto que las bandas en las que el año de lanzamiento de su último álbum es posterior a

1985, y donde todos los músicos de la banda han fallecido, realmente tienen un estilo “Heavy”.

Proponed una única sentencia SQL para corregir los registros incorrectos (en caso de utilizar más de

una, el ejercicio se considerará incorrecto). Por otro lado, mostrad el conjunto de las filas que se

actualizan.

Nota importante: cuando probéis la implementación de esta actualización, tened en cuenta que

siempre debéis partir de la misma base de datos, de otro modo podéis encontrar incoherencias en

vuestros análisis.