S!_A10: Music Royalty

You are designing a database to keep track of royalty distribution for songs released by different music labels. Some entities are marked in red. You may need to create additional entities to complete the design.

Each Song is identified by a unique SongID. The Song entity should store Song Title, Genre, Language, and Release Date.

The system keeps a list of all Contributors involved in songs. A contributor may be a singer, songwriter, or musician. Each Contributor is identified by a unique ContributorID. The Contributor entity should store Contributor Name, Stage Name (if any), and Contributor Type (such as Singer or Musician).

Each contributor may be contracted with a Music Label (such as Warner Music, Disney, etc) for a specific period of time. You need to store Label Name, Headquarters City, Primary Contact Name and Primary Contact Email. Create or assign a suitable Primary Key.
Note that a Label has contracts with many contributors, each for specific period of time. For each contract, store Contract Start Date and Contract End Date.

A song may involve multiple contributors, and a contributor may work on multiple songs. For each contributors participation in a song, the system needs to store:

• Role in the Song (such as Lead Singer, Backing Vocalist, Drummer, Lead Guitarist etc)
• Royalty Percentage

Design a database for this problem. If needed, create additional entities so that the relationships are correctly captured. Make sure to identify a Primary Key for each entity. If a natural Primary Key is not available for an entity, create a surrogate Primary Key. Also identify Foreign Keys as needed.

Create an ERD in Draw.io. Show all entities with attributes, clearly identify Foreign Keys, and include correct relationship lines.

Note:

• The relationship lines MUST show ONLY the cardinality symbol ( | or crow’s foot) on each end. You may need to modify the default relationship lines in Draw.io.
• Make sure the lines connect Primary Keys of a table with corresponding foreign keys of the child table.

Bonus:
A song earns royalties over time through different channels. Add any necessary entities to the ERD to store the monthly royalty amount earned by each song from channels such as streaming, radio, and track downloads.

Submission: .drawio file, PDF, or screenshot. If you are submitting a PDF or an image, make sure that the entire diagram is clearly visible, otherwise you will receive no credit.

Grading:
DB Design:

• Entity & Attribute Completeness (10 pts): all required entities and all their corresponding attributes are present in the ERD. Correct entity shape is used (as discussed in class).
• Keys (5 pts): Primary Keys and Foreign Keys are clearly marked.
• Relationships (5 pts): Relationships correctly model one-to-many and many-to-many relationships.

Draw.io Diagram

• Relationship Lines / Connection (2 pts): Lines must connect the Primary Key of the parent table to the Foreign Key of the child table. Relationship lines must be attached to entities, so that moving entities will adjust the lines.
• Cardinality Symbols (2 pts): Correct use of cardinality symbols (I or crow’s foot only) on both ends of all relationship lines.
• Clarity (1 pt): Diagram is neat, readable, with minimum criss-crossing of lines.

Bonus: 5 pts