CPSC 421 Assignment 1
The goal of this assignment is to define a simple music database. Your database should be defined so that it can store basic information about albums, song tracks, artists (groups and group members), and recording labels. In addition, your database should support the following queries.
Q1: Find all the album titles created by a particular group.
Q2: Find all the names of group members for a particular group.
Q3: Find all the album titles that contain a particular track.
Q4: Find all the track titles for a particular album.
Q5: Find all the track titles that were created by groups with a particular group member.
Q6: Find all album titles released by a particular label (e.g., “Columbia”, “CBS”, etc.) within a given pair of years (e.g., “2000” to “2005”).
Instructions: Answer each of the following questions and Submit Your Coursework as a single PDF document to the blackboard system.
- Give the schema (table names, attributes, and datatypes) for your music database. Also indicate which attributes are primary and foreign keys. For foreign keys, state which attributes the foreign keys refer to.
- Give SQL queries expressed against your database schema for Q1 through Q6 above.
- Construct instances of your schema (i.e., tables) such that each of the above queries would return at least two rows.
- List what your SQL queries should return when run against your database instance.
