MusicMash is a startup business specializing in online music sales. After receiving initial funding from investors, the
Question:
MusicMash is a startup business specializing in online music sales. After receiving initial funding from investors, the founders of MusicMash are ready to start developing their online business. The owners are University of Arizona MIS graduates and know that the success of the business will depend on efficient and thorough data management. This means that the database behind MusicMash must be well designed and professionally implemented.
The Business Environment
MusicMash anticipates having 100,000 customers after the first year of operation. MusicMash needs to track as much information about customers as possible, including name, address, age, email address, credit card number, and sign-up date. Most competitors in the online music streaming market have at least 1 million songs in their inventory provided by roughly 50,000 artists. MusicMash knows it needs to have a similar sized music inventory to stay competitive. The music inventory is expected to grow by 5% each year. Therefore, it must be easy to add additional songs and artists.
1. Relational Model
-Normalize the relations to at least third normal form.
-Identify the primary and foreign keys for each relation (using appropriate notation).
-Identify and describe referential integrity constraints needed to guarantee the integrity of your database - Explain your choices.
-Submit your normalized relations with the PKs, FKs, and constraints identified.
Data Overview:
1. A customer signs up to use the service by entering their name, address, age, email address, and credit card number. Sign-up ensures that customers always have a one-click option to purchase songs. Sign up does not imply that a customer has made a purchase.
2. After signing-up, customers can browse music by searching artist names, genre, or release date.
3. MusicMash must store the title, release date, genre, duration (length in seconds), and price of each song. Most songs in the MusicMash sales library belong to an album. However, in some cases, an artist releases a single song. In this case, there is no album associated with the song. For songs that do belong to an album, MusicMash must know the title and city where it was produced
4. MusicMash also stores a variety of information about each artist. This information includes name, age, home town/city, and current city of residence. An artist can create/produce many songs and albums, but songs are only associated with one artist.
5. Song purchases made by each customers must be carefully tracked including the date the music was purchased. If a customer wants all the songs on an album, they must purchase the songs individually.
You are responsible for interpreting the information above and then designing and implementing a database that will work for MusicMash. Just like real-world scenario, the information provided is fairly vague. For example, address should not be designed as a single attribute, but rather split into street, city, state, and zip to ensure a variety of queries can be executed. It's up to you to determine what should be an entity, what should be an attribute, and how everything is related.