ER Modelling and SQL statement

Task
Apple recently hired you to build a new database system for their iTunes store. The product manager gives you the following business requirements
? Each artist is assigned a unique ID in our iTunes database. The ID is a positive integer number and the database is interested in tracking their name, an artist’s genre and their age.
? Each artist can have multiple genre and each genre has multiple artists.
? An artist owns an album, where each album can be uniquely identified by an album ID and an album name. For simplicity, an album can only be owned by a single artist.
? Each album has a number of songs, where again each song has its own unique song ID. The iTunes database is also interested in tracking the song’s name, the order which it is listed in the album, the pricing of an album and length of the song. Each song is assumed to below to only one album.
? An album has a release date, genre and the region that it was released in. Each album can be released in multiple regions. There are ten different regions in the iTunes database. These regions follow the DVD coding that is given in http://en.wikipedia.org/wiki/DVD_region_code.
? The database should also support region-specific pricing of an album. That is, each album can be priced differently in different regions.

The product manager also tells you that the database must be capable of producing the following reports for the CEO every month. This includes a report that
1. lists all albums that has a song length greater than 5minutes
2. lists all albums released in a given period, e.g., 1 January 2013 and 31 January 2013
3. lists all albums that were above a certain pricing for each region code
Example: If album X in region A, B, C sells for $11, $8, $12 respectively, then the SQL query for pricing above $10 will list the rows as output
Album
Region
Price
X
A
$11
X
C
$12
4. lists all artists who has albums in a given set of region code, e.g., albums in region code 0 and 5
5. lists all artists who share the same genre
Example: If artist A has genre ‘rock’ and ‘classic’, artist B has genre ‘classic’ and ‘pop’, and artist C has genre ‘pop’, then the output should be
Acceptable output 1
First Artist Second Artist Shared Genre
A B Classic
B A Classic
B C Pop
C B Pop
Acceptable output 2 (better but harder)
First Artist Second Artist Shared Genre
A B Classic
B C Pop
6. lists all artists who produced an album that is not in the genre listed for that artist
Example: If artist X has genre ‘pop’ and ‘rock’ registered but produce an album Y with genre ‘classic’,
then the artist, (and optionally, album and that genre) should be listed.
7. lists all artists who has never produced an album that contains more than 10 songs in each album
As your product manager is in a rush, he asks you to start with this initial requirements. He wants you to
write a report containing
? the ER modelling that you have developed;
? the SQL statements that you think will produce each of the report requested, and a short discussion
of how your SQL statement will achieve the outputs desired;
? Since you haven’t got an opportunity to clarify the requirements, the list of assumptions that you
made in order to capture the above data and to produce the reports requested.
Hints
? You will have to refine your ER diagram as you look at each report requested so don’t expect to get
the ER diagram completed the first time.
? As you rework the ER diagram to support a report, make sure your ER diagram continues to support
the other reports, i.e., your SQL statements for the other reports still work.
? Before submission, check your ER diagram and all your SQL statements – your SQL statements should
be able to logically execute over the tables that your ER diagram will ultimately produce.
? Make assumptions that are logical – it is often easier for the marker to appreciate your design and
though process to be able to award marks.
? If you are unable to provide the “perfect” SQL statement, don’t panic. Write what you can as SQL
and then explain them to your marker as if you are the analyst and the marker is the database
administrator who will ultimately be responsible for producing the actual report.

request:
a clearly drawn and complete ER diagram

a list of assumptions (in point form is fine)

one SQL statement to each question

Use the order calculator below and get started! Contact our live support team for any assistance or inquiry.

[order_calculator]