CMPS 180
Database Systems
Winter 2004
Project Assignment 1
Due in class on January 14th 2004
Description
El Cheapo Inc is planning to open an on-line music store, MP3s’R’US. You have been
hired by El Cheapo in order to design the database that will back their Internet store.
MP3s’R’Us will allow its customers to browse the available songs on-line, listen to
samples, buy music (in the form of MP3 files), alter their personal information, and rate
songs or artists that appear in the catalog.
After meeting with the executives from El Cheapo, you have gathered the following
requirements regarding the information that will be stored in the database:
At an abstract level, the database will store information on songs, customers, recording
firms, and artists. A song must have at least one artist. Songs may belong to musical
albums, which are also associated with artists.
A song has a title, duration, and a year of release. For each song, the store will offer
different MP3 files with varying bit-rates and prices accordingly (if you do not know
what a bit-rate is, here’s the scoop: the higher the bit-rate, the better the mp3 file will
sound). Each song may also be associated with multiple genres, where each genre has a
name.
An artist has a name, a birth date, a birthplace, and is associated with exactly one
recording firm. An album has a name, a year of release, and is also associated with
exactly one recording firm. For the needs of the Internet store, a recording firm has a
name, and an address.
A customer has a name, an e-mail address, a birth date, and a list of preferred genres. For
authentication purposes, the system must also record a user-name and a password. A user
can rate a song, an artist, or an album as: “amazing!” ; “good” ; “bad”; or, “it sucks!”.
A single user can have multiple credit cards registered, and the system must record the
card type, the card number, the expiration date, and the billing address for each credit
card. Whenever a user buys a song, the system must record the transaction. The relevant
information includes the customer, the credit card used, the song, and the quality of the
MP3 file that the user bought.
What to turn in
Design an E-R diagram for the database of MP3s’R’Us. The E-R model that you design
should definitely cover the requirements of the system, as outlined by the executives of
