Question: 3. Create a logical data model documented with an Entity Relationship Diagram (ERD) for this problem using the crowsfoot format. Spotify must pay artists for
3. Create a logical data model documented with an Entity Relationship Diagram (ERD) for this problem using the crowsfoot format. Spotify must pay artists for their songs when a customer plays a song. When a customer plays a song on Spotify, it is referred to as a play event. The amount Spotify pays an artist per play event is referred to as the SongPaymentRate. Spotify wants to keep track of their customers, their customer membership, the artists who create songs, and the play events that customers create when they play songs.
Customers can join, quit, and then re-join Spotify at will. Each time a customer joins, or re-joins, Spotify, the customer must indicate what type of membership he/she wants. Spotify wants to keep track of the different times a customer joins/quits and then re-joins a membership. Spotify offers a variety of different types of membership. A type of membership includes a membership typeID, which is a unique value for the type of membership, a description of the type of membership, the payment rate to the customer (how much the membership costs) and the advertising frequency for that particular type of membership. For example, a Free (Ad-Supported) membership has an advertising frequency of 3, which means an ad is played every three songs. In the sample data provided, Jeffery Amba originally joined Spotify on 9/10/2014 with a premium membership. He quit on 2/11/2015. He rejoined Spotify on 6/28/2017 with a family premium membership and still has a family premium membership. We know he still has that type of membership because the value of the most current MembershipDateEnded field for Jeffery Amba is null.
An artist of a song has a lastname, firstname, and also an artist display name. A song may have many artists, and an artist may produce many songs. The payment rate for a song is dependent on the artist. Different artists participating on the same song may have different payment rates.
Data
| CustomerID | Customer LastName | Customer FirstName | Customer Email | PhoneNumber | Membership DateStarted | Membership DateEnded | Membership TypeID | MembershipDescription | Membership TypeRate | Membership AdvertisingFrequency | SongID | Play Event DateTimePlayed | Play Event Song Name | Song Duration | Song PaymentRate | ArtistID | LastName | FirstName | Artist DisplayName |
| 1 | Jeffery | Amba | ajeffery@mail.com | 5138631181 | 9/10/2014 | 2/11/2015 | 2 | Premium | 12.99 | NULL | 11 | Sep 24 2020 3:55AM | One Kiss (with Dua Lipa) | 03:34:00.000 | 0.01 | 4 | Wiles | Adam | Calvin Harris |
| 1 | Jeffery | Amba | ajeffery@mail.com | 5138631181 | 6/28/2017 | NULL | 4 | Family Premium | 14.99 | NULL | 7 | sep 24 2020 8:56AM | STARGAZING | 04:30:00.000 | 0.02 | 3 | Webster | Jacques | Travis Scott |
| 4 | Crowther | Samiya | samiyacrowther@mail.com | 2699273819 | 3/14/2016 | NULL | 4 | Family Premium | 14.99 | NULL | 18 | sep 22 2020 8:58AM | WAKE UP | 03:51:00.000 | 0.005 | 3 | Webster | Jacques | Travis Scott |
| 5 | Gallagher | Priyanka | pgall@mail.com | 4025515535 | 4/9/2016 | NULL | 1 | Free (Ad-Supported) | 0 | 3 | 12 | aug 13 2020 9:11AM | Nonstop | 03:58:00.000 | 0.01 | 1 | Graham | Aubrey | Drake |
| 5 | Gallagher | Priyanka | pgall@mail.com | 4025515535 | 4/9/2016 | NULL | 1 | Free (Ad-Supported) | 0 | 3 | 15 | sep 3 2020 11:42AM | Psycho (feat. Ty Dolla $ign) | 03:41:00.000 | 0.005 | 8 | Post | Austin | Post Malone |
| 6 | Bryan | Franklin | frank.bryan@outlook.com | 7012284453 | 3/13/2017 | NULL | 4 | Family Premium | 14.99 | NULL | 5 | Aug 17 2020 6:32AM | I Like It | 04:13:00.000 | 0.02 | 6 | Almanzar | Belcalis | Cardi B |
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts
Study Help