COMP3311 Final Exam 20T3

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

Database Systems

We have designed and built a database of (fake) information about modern popular music. This database contains information about performers, the music groups they belong to, the albums those groups release, and the songs on those albums. The following ER design describes this database:

Note that we have omitted the primary keys in the above diagram. They are all implemented as numeric attributes called id in the SQL implementation.

An SQL schema based on this design:

create table Groups (
	id          integer,
	name        text not null,
	formed      date not null, -- when the group formed
	disbanded   date, -- when they split up (null if still together)
	primary key (id)
);

create table Albums (
	id          integer,
	title       text not null,
	year        integer not null check (year >= 1980),
	made_by     integer not null, -- which group made this album
	genre       text not null,
	primary key (id),
	foreign key (made_by) references Groups(id)
);

create table Performers (
	id          integer,
	name        text not null,
	birthday    date,
	primary key (id)
);

create table Songs (
	id          integer,
	title       text not null,
	length      integer not null check (length > 0), -- seconds
	on_album    integer not null, -- which album this song appears on
	trackNo     integer not null check (trackNo > 0), -- position
	primary key (id),
	foreign key (on_album) references Albums(id)
);

create table PlaysOn (
	performer   integer,
	song        integer,
	instrument  text,
	primary key (performer,song,instrument),
	foreign key (performer) references Performers(id),
	foreign key (song) references Songs(id)
);

create table MemberOf (
	in_group    integer,
	performer   integer,
	primary key (in_group,performer),
	foreign key (in_group) references Groups(id),
	foreign key (performer) references Performers(id)
);

Notes on the above schema:

• performers belong to a single Group (even though MemberOf is n:m)
• an album is a collection of songs made by one group
• album titles and song titles are not necessarily unique
• however, on a given album, song titles are unique
• a performer may play multiple instruments in a given song
• vocals are counted as an instrument in some contexts
• each group member typically plays on only some songs on an album
• albums are sometimes called CDs

You should familiarise yourself with this schema before proceeding to solve the queries. It would also be useful to examine the database contents to ensure that you understand what all of the data represents.