¡Descarga Práctica 1: El lenguaje SQL I - Uso de bases de datos y más Ejercicios en PDF de Programación de Bases de Datos solo en Docsity!
Uso de bases de datos
Práctica 1: El lenguaje SQL I
Queremos disponer de una base de datos para registrar información sobre grupos musicales, canciones y músicos. A continuación, se describen cada una de las relaciones. Las relaciones con las que trabajamos son las siguientes (claves primarias subrayadas, claves foráneas en cursiva) y los atributos no pueden tener valor nulo a menos que se diga lo contrario. MUSICIAN (id_musician, name, birth, death, age, 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 , la edad ( age ), 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 ) que puede ser NULL , 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. Per a cada àlbum se almacena el seu identificador ( id_album ) que és 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. SONG (id_song, title, duration, id_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 ), que no puede ser negativa ni cero y el identificador del álbum al que pertenece la canción ( id_album ), que puede ser NULL y que es clave foránea de ALBUM , con política de anulación en caso de borrado.
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 {‘ Bass ’, ‘ Drums ’, ‘ Guitar ‘, ‘ Keyboard’ , ‘ Vocals’, ‘Trumpet’, ‘Clarinet’, ‘Oboe’, ‘Flute’ }. COMPOSER ( id_musician , id_song, year) 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 y el año en que se compuso la canción ( year ). 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 haced: 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.
● Las preguntas no contestadas no penalizan. ● Las sentencias SQL que no se puedan ejecutar (den error de sintaxis) no serán evaluadas. ● Las sentencias de creación de tablas que propongáis que den error al ejecutar el fichero inserts_db.sql que os proporcionamos no serán evaluadas (en definitiva, el número de columnas que han de incluir las tablas es el que viene dado en el fichero inserts_db.sql). ● No se evaluarán propuestas de solución que utilicen dominios (CREATE DOMAIN).
Pregunta 2 (45 % puntuación)
Enunciado
a) Diseñad una consulta que devuelva los músicos que no sean americanos, de grupos que han lanzado algún álbum en la década de los 90, así como el número de canciones que han compuesto a lo largo de su carrera. En concreto, queremos saber el nombre y la edad del músico, el título del álbum y el año de lanzamiento, y el número de canciones que ha compuesto, (0 si no ha compuesto ninguna), 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 de duración superior a 3’, publicadas en algún álbum del año 1986, compuestas por músicos de bandas en las que todos sus miembros estén vivos. En concreto, queremos que nos muestre el nombre y la duración de la canción, el nombre de la banda, y el título del álbum que se publicó. c) Diseñad una vista ( seven_top_composers ) que obtenga el nombre y el año de nacimiento de los 7 músicos que más canciones han compuesto, así como el número de canciones compuestas, la duración media de las canciones y el número de bandas de las que han sido miembros. Queremos el resultado ordenado por el número de canciones compuestas de mayor a menor y, en caso de empate, por orden alfabético inverso del nombre. 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
Criterios de evaluación
● Todas las preguntas tienen el mismo peso. ● Las preguntas no contestadas no penalizan. ● Las sentencias SQL que no se puedan ejecutar (den error de sintaxis) no serán evaluadas. ● Se valorará positivamente el uso de sentencias SQL estándar (al margen de otros elementos indicados en el enunciado). ● Para obtener la máxima nota, la propuesta de solución de cada pregunta debe incluir el resultado (captura de pantalla o similar).
● Para obtener la máxima nota en cada pregunta, la propuesta de solución se tiene que ajustar estrictamente a lo que se pide en el enunciado (por ejemplo, tiene que incorporar todas las columnas que se esperan en el resultado, se deben hacer las ordenaciones pedidas …) ● Igualmente para obtener la máxima nota la presentación de los resultados debe ser precisa y amigable (nombres de las columnas, redondeo de decimales, …)
Pregunta 3 (25 % puntuación)
Enunciado
Se ha descubierto que los compositores de alguna canción de duración superior a 5’, que todavía están vivos y que pertenecen a bandas en que todos los miembros son de la misma nacionalidad, nacieron realmente 6 meses antes. 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.
Criterios de evaluación
● Las sentencias SQL que no se puedan ejecutar (den error de sintaxis) no serán evaluadas. ● Para obtener la máxima nota en cada pregunta, la propuesta de solución se tiene que ajustar estrictamente a lo que se pide en el enunciado (hay que actualizar los datos estrictamente necesarios) y con una única sentencia de UPDATE. ● Para obtener la máxima nota, la solución debe ser eficiente (por ejemplo, se valorará negativamente hacer más joins de las necesarias). ● Se debe argumentar a la respuesta proporcionada. Para obtener la máxima puntuación se debe mostrar la relación de filas que se actualizan. ● Igualmente para obtener la máxima nota la presentación de los resultados debe ser precisa y amigable (nombres de las columnas, redondeo de decimales, …)
