COMP3311 Final Exam 20T3

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

Database Systems

Question 3 (7 marks)

Write an SQL view that gives a list performers who play many instruments, and how many instruments they play. "Many" is defined as more than half of the possible instruments. The possible instruments can be determined by looking at the instrument column in the PlaysOn table. However we impose the following conditions:

• we do not count 'vocals' as an instrument
• guitars are mentioned as 'lead guitar', 'rythm guitar', 'acoustic guitar' and 'guitars' in the PlaysOn.instrument column; treat all of these as if they were simply 'guitar'

If you handle the above conditions correctly there are 12 distinct instruments ('banjo', 'bass', 'drums', 'flute', 'guitar', 'keyboard', 'mandolin', 'percussion', 'saxophone', 'synthesizer', 'tambourine', 'violin') in the current database. However, you should not assume that this will always be the case (i.e. you should compute the number of instruments). Answers that assume that there are 12 instruments (e.g. explicitly use the number 12 or 6) are worth a maximum of half marks.

Define the view as q3(performer,ninstruments), where the attributes are:

• performer = the name of a performer
• ninstruments = the number of distinct instruments that they play

Instructions:

• Work on this question in the work/q3/ directory
• Define the q3 view in the file q3.sql
• Also, place any additional views used by q3 in this file
• Additional views must be placed before the q3 definition
• You may not define your own functions to help in answering this question
• Load it into your database using:   psql music -f q3.sql
• Sample output is in the file:   tests/01.expected
• Test it via:   select * from q3 order by ninstruments desc, performer;
• Check it using:   sh check q3   in the q3 directory
• Submit it using:   give cs3311 exam_q3 q3.sql  or  Webcms3