COMP3311 Final Exam 20T3

The University of New South Wales COMP3311 Database Systems Final Exam 20T3

Database Systems

Question 5 (9 marks)

Write a PLpgSQL function that produces a list of groups and all of the music genres they have recorded albums in.

create type GroupGenres as ("group" text, genres text);

create or replace function q5() returns setof GroupGenres ...

The attributes in GroupGenres tuples are as follows:

• "group" = the name of a group
• genres = a comma-separated list of genres

The list of genres is produced as a single string and must be in alphabetical order of genres. If a group has never recorded ad album, return an empty string (not NULL).

Note that the function has no parameters, so will always produce the same result, e.g

music# select * from q5() order by "group";
        group         |                  genres                  
----------------------+------------------------------------------
 After the Sea        | hip-hop,post-rock,rock
 After the Sky        | 
 All of the Floyd     | rock
 All of the Sea       | hip-hop,pop,rock
 Black Eyed Boy       | country,hip-hop,rock
 Black Eyed Nails     | metal,pop,rock
 Black Mountains      | blues,rock
 Black Vanilla        | pop,rock
 Blue Floyd           | hip-hop,metal,pop,post-rock,rock
 Blue Mountains       | country,post-rock,rock
... etc etc etc ...

Warning: see the comments about ordering in the previous question.

Instructions:

• Work on this question in the work/q5/ directory
• Define the function q5(...) in the file q5.sql
• Also, place any additional views/functions used by q5 in this file
• Additional views must be placed before the q5 definition
• Load it into your database using:   psql music -f q5.sql
• Sample output is in the file:   tests/*.expected
• Test it via:   select * from q5() order by "group";
• Check it using:   sh check q5   in the q5 directory
• Submit it using:   give cs3311 exam_q5 q5.sql  or  Webcms3