a. Design a MySQL database to support a Spotify-like media cataloguing application. The application should catalogue music, including artists, their albums, songs in each album, playlists, users that created/shared/played various playlists, etc. The conceptual model describing the application’s data is shown below. Your database must store data described by every entity, relationship, and attribute type shown in the conceptual data model. Your database schema must be in the 3NF. Your answer should consist of the CREATE TABLE statements that represent your database schema.

Assume the following about the application: every album was released by some artist. Every song belongs to some album, every album was released by an artist, and every playlist was created by some user. In other words, for each 1:n relationship type, assume mandatory participation for the entity type on the “n” side.

part b. Write the following queries in SQL:

Q1. Find albums by a given performer. Order results by year (DESC). Display all the information about each album (its id, year, title, etc), and performer (i.e. country and style).

Q2. Find albums by genre released after a given year. Order results by year (DESC). Display all information about each album.

Q3. Find albums of a given genre released in a given country after a given year. Order results by year (DESC).

Q4. Find playlists by a given album.

Q5. Find a user who created a given playlist.

Q6. Find users who shared a given playlist.

key2 email keyl username creates SidD User m plays n- Playlist name m tags country age shares features year title country style n name Artist -1 releases n- Album -1 has n- Song title genre format duration
 

Transcribed Image Text:

country name style key2: email key1: username country id age User genre year -1- m -m plays title Artist ||1- releases -n- Album −1- creates format shares has Playlist features id Song id name tags title duration country name style key2: email key1: username country id age User genre year -1- m -m plays title Artist ||1- releases -n- Album −1- creates format shares has Playlist features id Song id name tags title duration

Answer rating: 100% (QA)

CREATE TABLE User email VARCHAR255 PRIMARY KEY username VARCHAR255 NOT NULL country VARCHAR255 age INTEGER features JSON CREATE TABLE Playlist id INTEGER PRIMARY KEY name VARCHAR255 NOT NULL tags JSON ... View the full answer