Assignment question
pdf
keyboard_arrow_up
School
Simon Fraser University *
*We aren’t endorsed by this school
Course
225
Subject
Information Systems
Date
Dec 6, 2023
Type
Pages
6
Uploaded by AmbassadorKnowledgeCrocodile27
In this assignment, you are to draw an ERD, and demonstrate your knowledge of ERD (key) constraints.
Part 1 -
Music
Draw an entity-relationship diagram to represent information relating to a database to be used to store information for a (music) production company.
The database is to store information about
bands
. A band is a music group (or soloist); the unique name of the band, the year the band started recording, the number of members of the band and the genre of music it plays are to be recorded (pun intended) in the DB. For example, the band
Unleash The Archers
was formed in 2007, has four members and plays
rock
(of course "rock" is a massive over-simplification – see Wikipedia's
list of rock genres
as an example of how complicated this stuff gets).
In addition to the information about bands the database will also store information about the band members, who are to be referred to as
musicians
. The unique SIN, name (consisting of a first name and a last name), instrument and email address of musicians is to be maintained. A band must be associated with at least one musician, although may be associated with more. Musicians that are not associated with bands may be stored in the DB; a musician may be associated with more than one band. The database should also track (pun also intended) the percentage share of the revenue that a musician receives from songs recorded by a band that the musician is associated with. If a musician is associated with more than one band they may have different shares from each such band. For example,
Ian Gillan
might receive 30% of the revenue from songs recorded by
Deep Purple
, and 80% of the revenue from songs recorded by
Gillan
(where Ian Gillan is a musician and Deep Purple and Gillan are bands).
Record
labels
(e.g. Sony, Universal) associated with each musician are also to be recorded in the DB. The unique name, country of incorporation and email addresses of record labels is to be stored - note that a record label may have more than one email address. Record labels hold exclusive contracts with musicians but can sign multiple musicians. A musician does not need a record label and data about record labels with no associated musicians will not be maintained.
The following information is to be maintained for
songs
– the unique
ISRC
(International Standard Recording Code), the song's title, its duration and the year that it was recorded. Songs are recorded by bands, a band can record more than one song, and each song must be recorded by just one band. Since the DB may store information about bands that are just starting out, a band does not have to have recorded any songs.
Sometimes a song will have a guest musician (for example,
Beyond the Black.
's song Wounded Healer featured Elize Ryd, one of the three singers from
Amaranthe.
). Some musicians may never perform for a band that is not their own, other musicians may be guests on many songs. A song does not need to have a guest musician, and some songs have more than one guest musician. The fee paid to guest musicians is also to be recorded, the fee may be different for each song that a guest musician plays on.
Most (though not all) songs are associated with an
album
. An album is a collection of songs (for example,
Halestorm
recently released their new album Back From the Dead). The DB is to record the unique name of an album, the year it was released, the last name of the cover artist (or photographer) and the number of songs on the album. Additional information is to be maintained for two specific types of albums:
Greatest Hits
albums and
Live
albums.
Greatest Hits
albums collect songs originally published in another album; the release dates of the earliest and latest released songs in the collection are to be stored.
Live
albums collect songs recorded at a live concert. The date and location (a location consists of a country, city and venue) of a live album's concert are to be stored.
Finally, daily
sales
of songs are also to be recorded. For each day, vendor and country the number of songs sold, and the total sale amount are to be stored. For example, the
Taylor Swift
song Getaway Car might be sold by iTunes Canada, iTunes UK, and Amazon Canada on the 15th of September 2023, so would require three sales entries that record the number of songs sold and the total amounts for those vendors and countries on that date. A song may be sold many times (or, sadly, not at all).
As an aside, most of this is a reasonably accurate, though simplified, version of the data that might appear in such a database. If you are interested I found
this article.
on recording contracts - I'm least knowledgeable about the part to do with record labels.
Specific Requirements and Notes
•
Draw an ERD
using the version of the model discussed in class.
You
must
use arrows and double lines (or very thick lines) to represent key constraints and participation constraints.
•
You may draw your ERD by hand, if so, you should scan your completed diagram for submission. If your drawing is unclear (for example, whether there is a participation constraint), you will
not
be given the benefit of the doubt.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help