Design a Cassandra database to support a Spotify-like media cataloguing applicat
ID: 3854137 • Letter: D
Question
Design a Cassandra 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.
b. Write the following queries in Cassandra:
Q1. Find albums by a given performer. Order results by year (DESC).
Q2. Find albums by genre released after a given year. Order results by year (DESC).
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.
First, based on the above access patterns, design a logical data model. Then, for every table, assign datatypes to each column to obtain a physical data model. For this exercise, you do not need to perform other physical optimizations. Your answer should include CREATE table statements in the CQL language that represent your Cassandra database schema
key2: ema creates n keyl: username User m plays n- Playlist Kname tags m shares n country age features year style country name Artist 1 releases Album 1. has Song title genre format durationExplanation / Answer
1.create tables for the following
1.user
2.playlist
3.song
4.Album
5.Artist
1.create table user(username text,email text,country text,age int,primarykey(username,email));
2.create table playlist(playlist_id int,playlist_name text,tags text,primarykey(playlist_id),playlist text references user(username));
3.create table song(song_id int,song_title text,duration float,primarykey(song_id),songs int references playlist(playlist_id));
4.create table album(album_id int,year int,title text,genre text,format text,primarykey(album_id),album int references song(song_id));
5.create table artist(name text,country text,style text,primarykey(name),artist int references album(album_id));
4. select playlist.playlist_id,playlist.playlist_name,playlist.tags from playlist,song,album where playlist.playlist_id=song.song_id and song.song_id=album.album_id and album.title='giventitle';