Java Software Solutions: Foundations of Program Design, 7e (Lewis/Loftus)
1 of 2
Musica Case Study
Musica is a corner shop that sells vinyl albums. They are about to build a Web site that they will
use to sell their albums. At present, the owner – Fringe Dweller – keeps keep track of stock via
paper records and by picking through the collection by hand. She
ealises that the information needs to be organised into a databse,
partyly so that a proper search function can be built into the Website and
so that stock levels can be automatically adjusted after sales.
To that end, Musica has hired you to build a database of the music in its
collection. You have interviewed the owner – Fringe Dweller – and picked
up information by looking through her album collection and filing cabinets,
and you believe you now have a list of all the pieces of data you will need to
store.
At the minimum, you know that you need to store who writes the songs, who performs the
songs, song titles, album titles, recording dates, song lengths, who produces albums, and album
Label. [You may be aware that a record “Label” is the company that helps the album get made
and distributed.]
The owner would like to, at times, upload pieces of information as she finds them, including:
• For artists - their date of birth, gender, nationality, and a description
• For songs and albums – genres and general descriptions
Other information you have established:
• The owner said she doesn’t need to
collect information about album
manufacturers or distributors
• You have decided that singer,
group, producer, and writer are all
synonyms for “Artist”
• The owner wants to record album
track numbers and the side of the
album that the song appears on
• An artist can record the same song on
different dates, and so it has become
clear that you will need to store
information about song “Versions”
• The owner wants to show customers up photos for artists (at least 3), album (including
front cover, back cover, and inner sleeve including artist photos, album artwork), and
label (that is, each record company’s “logo”)
• Customers will want to search by album, artist, song, or genre
2 of 2
• Some albums are compilation albums containing many artists. The owner has been
catalogueing these under the artist name “Various” and you will build that technique
into your database
Your Tasks
1. Create an MS Word document named “Your Name – Your Student Number –
Musica.docx"
2. Create an ER Diagram from the above scenario:
a) Create this ER Diagram with Lucidchart (on Google Drive) or another piece of
software. Hand-drawn diagrams will not be accepted
) Only use “crow’s foot notation”
c) Mark cardinality and resolve any relationship problems that are revealed
d) Mark existence, likely primary keys and likely foreign keys
e) Be sure to include your name and student number within the drawing
f) Include all attribute fields that you believe would be useful
g) Insert that ER Diagram into your Word document. You could do this by taking a
screenshot of the diagram and pasting it in, or by exporting the diagram as an
image and the inserting it
3. Draw dependency diagram to use the 1NF, 2NF and 3NF
Submission Instructions