The description of the relational model can be either as text (e.g., a list of relations and constraints) or as a "relational schema diagram". Please in either case ensure that foreign key definitions are unambiguous, i.e., that you include both the direction (source and destination) of the constraint and the individual attributes involved in the constraint.

 

Transcribed Image Text:

Task 2: Translation from an ER-diagram to a relational model [50 marks] Below is an ER-diagram describing the design of a database for an online music service. Note that the service stores both information about the music (such as albums, songs and artists) as well as keeping track of user accounts and storing user-generated playlists. title playlist ID User Playlist track no Playlist Track In Playlist Track disjoint ISA Album year Track is Song song ID song name Made By Main Artist Song duration user ID artist ID user name User Artist user_email artist name country Follows country Performs.on Study the diagram and make sure that you understand how this database is intended to store information. Some notes to help you out: . A "playlist" is a list of songs that represents either a user-made playlist or a music album. So an album is treated in the database as just a special type of playlist. A "track" is one track in a playlist, and the "track_no" discriminator defines track order in the list. (So normally, every playlist would have a "track 1", "track 2", etc.) A track is not the same thing as a song. An album would usually have a main artist, but it can also be a collection of songs from various sources (e.g., if the album is the soundtrack of a movie). As task 2, convert the above ER-diagram into a relational model. Write down the resulting relational schema, including all relation names, attribute names, and constraints such as primary keys and foreign key constraints. (You may omit the domains of the attributes, but every other part of the relational model specification should be included.) Task 2: Translation from an ER-diagram to a relational model [50 marks] Below is an ER-diagram describing the design of a database for an online music service. Note that the service stores both information about the music (such as albums, songs and artists) as well as keeping track of user accounts and storing user-generated playlists. title playlist ID User Playlist track no Playlist Track In Playlist Track disjoint ISA Album year Track is Song song ID song name Made By Main Artist Song duration user ID artist ID user name User Artist user_email artist name country Follows country Performs.on Study the diagram and make sure that you understand how this database is intended to store information. Some notes to help you out: . A "playlist" is a list of songs that represents either a user-made playlist or a music album. So an album is treated in the database as just a special type of playlist. A "track" is one track in a playlist, and the "track_no" discriminator defines track order in the list. (So normally, every playlist would have a "track 1", "track 2", etc.) A track is not the same thing as a song. An album would usually have a main artist, but it can also be a collection of songs from various sources (e.g., if the album is the soundtrack of a movie). As task 2, convert the above ER-diagram into a relational model. Write down the resulting relational schema, including all relation names, attribute names, and constraints such as primary keys and foreign key constraints. (You may omit the domains of the attributes, but every other part of the relational model specification should be included.)