CS计算机代考程序代写 SQL database ER Introduction

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%)