Introduction
SirenCD is a digital media company that runs several retail stores that sell CDs to
customers. In this coursework, you are asked to design a database for SirenCD to
help manage the information of artists, albums, media tracks, and media purchases.
The final database must be in 3NF and have no M:N relationships. You will also be
asked to write a few queries based on your database design.
Part 1: Database design (60%)
The retail stores of SirenCD sell many different CD albums. An album can have one or
more soundtracks. Each soundtrack has its track name, release date, genre, length
and composer. Each album is associated with an artist. Customers can look up
information about all artists, such as date of birth, biography and all his/her albums
from the website of the company.
The company provides a special service that allows customers to make their own CDs
by providing a playlist to the company. These playlists can have tracks from different
albums. The selection of soundtracks for these playlists are private to customers and
are not visible to others. The price of such a customised CD is the sum of the prices of
all soundtracks plus 30.
Before being able to make purchases, a customer needs to register an account with
the help of a retail store staff. The information needed for registration includes name,
phone number, membership card number, address (for delivery, when needed) and
the staff id who helped with the registration. The information about staff members
consists of the staff id, name, the store he works in and the date of joining the store.
After purchasing CDs, the customer will receive an invoice with the list of items
he/she purchased. The information of invoices should also be stored in the database.
You need to decide what to include in your database.
Part 2: Query Tasks (40%)
Please write down queries for the following tasks and save them in a single SQL file.
You should also indicate the question number correctly in the comments of your
script. Failing to do so will lead to reduced or zero marks. Each task worth 5 marks.
1. List all soundtracks purchased by “Fiona”.
2. List the number of soundtracks purchased by “Louis”, grouped by genre.
3. Calculate the price of all songs in the playlists created by “Fiona”.
4. Find out all customers that registered their membership at the “Northwind”
Store.
5. Find out the customer(s) who spent the most amount of money between the
time from 2020-06-21 to 2021-04-14.
6. List all artists involved in the playlists created by “Daryl”.
7. List all artists involved in the most recent playlist created by “Daryl”.
8. List the customer id involved in all invoices from the “Northwind” Store.
In the zip file:
1. A document called “report.docx” or “report.pdf”, Containing your ER diagram
and necessary explanations to your design.
2. A script called “script.sql”, which contains all statements for creating the
tables and the query tasks. You must also insert necessary data records into all
tables so that the results of query tasks can be fully tested. Very few data
inserted will lead to lower marks.
Introduction
Part 1: Database design (60%)
Part 2: Query Tasks (40%)