Modellazione ER e SQL:
SoundCloudSan Sharing Music Platform

Il seguente esercizio è dato come soluzione alla traccia: TASK – Modelazione ER e MySQL 08/07/2022 – Percorso di Data Engineer

La soluzione è stata sviluppata e gentilmente resa disponibile per tutti gli studenti da Aron Contini.

 

TRACCIA (in Inglese):

The chinese music company 骗局 wants to produce a music sharing platforms to supportand promote the music producers called SuondCluodSan(SCS).

Every platform user, in order to register is asked to provide name, surname, email and a username.
every user can at any moment upload a song to SCS providing a song name and an optional featuring description (e.g. feat: Pitbull, Ne-Yo, Gianni Celeste) becoming effectively an affiliate producer of 骗局.
the system displays at any time the played times for each song and its upload_date.
Producers can organize their songs in albums providing an album name at the time of creation. SCS will save the album creation tim e automatically.
Each producer can prepare one showcase of his songs with an optional description(a showcase is a special plylist that will always be shown in the main page of the artist)
Users can also compile playlist to then share the link with friends, providing a name, a simple description and the list of songs of SCS to add in the playlist.
Each producer can go into a verification process that, once completed, will make him gain a verified badge in the main menu.

 

IL DIAGRAMMA ER:

Il seguente diagramma è consultabile o modificabile direttamente dal suo file sorgente con il programma draw.io scaricabile dall’indirizzo https://www.diagrams.net/ o utilizzabile direttamente sul sito.

SCARICA IL DIAGRAMMA: CLICCA QUI

 

 

DDL – Data Definition Language

 

drop database if exists scs;

create database if not exists scs;
use scs;

create table user(
    id_user int auto_increment primary key,
    _name varchar(30) not null,
    surname varchar(30) not null,
    email varchar(50) not null check(email like "%@%") unique,
    verified boolean not null default 0,
    user_name varchar(30) not null unique,
    unique(_name, surname, email, user_name)
    );

create table song(
    id_song int auto_increment primary key,
    id_user int,
    song_name varchar(30) not null,
    feat text check(feat like "Feat: %"),
    played_times int not null default 0,
    upload_date date not null default(current_date),
    foreign key (id_user) references user(id_user) on update cascade on delete cascade
    );

create table showcase(
    id_showcase int auto_increment primary key,
    id_user int unique,
    _description text, 
    foreign key (id_user) references user(id_user) on update cascade on delete cascade
    );

create table showcase_song(
    id_showcase int,
    id_song int,
    unique(id_showcase, id_song),
    foreign key(id_showcase) references showcase(id_showcase) on update cascade on delete cascade,
    foreign key(id_song) references song(id_song) on update cascade on delete cascade
);

create table album(
    id_album int auto_increment primary key,
    upload_date date not null default(current_date),
    album_name varchar(30) not null,
    id_user int,
    foreign key(id_user) references user(id_user)
);

create table album_song(
    id_album int,
    id_song int,
    foreign key(id_album) references album(id_album) on update cascade on delete cascade,
    foreign key(id_song) references song(id_song) on update cascade on delete cascade,
    unique(id_album, id_song)
);

create table playlist(
    id_playlist int auto_increment primary key,
    playlist_description text not null,
    playlist_name varchar(30) not null,
    id_user int,
    foreign key(id_user) references user(id_user)
);

create table playlist_song(
    id_playlist int,
    id_song int,
    foreign key(id_playlist) references playlist(id_playlist) on update cascade on delete cascade,
    foreign key(id_song) references song(id_song) on update cascade on delete cascade,
    unique(id_playlist, id_song)
);

 

DML – Data Manipilation Language

 

insert into user(user_name, _name, surname, email) values
    ("smokyBOY", "Giovanni", "Muciaccia", "supermanisgreat@gmail.com"),
    ("PaoloBianchi86", "Paolo", "Bianchi", "paolonebellone@yahoo.com");
insert into user(user_name, _name, surname, email, verified) values
    ("smokyGirl", "Manlio", "Bonanni", "manlioboy@gmail.com", 1),
    ("DarkMoney86", "Paolo", "Boni", "boniilbono@yahoo.com", 1);


insert into song(id_user, song_name) values
    (1, "Lovers on the sun"),
    (1, "Printers on the sun"),
    (1, "Sun on the sun");
insert into song(id_user, song_name, feat) values
    (2, "Lovers on the sun",  "Feat: gi celeste"),
    (3, "Printers on the sun",  "Feat: gianni este"),
    (4, "Sun on the sun", "Feat: gigi the");


insert into showcase(id_user) values
    (3),
    (2),
    (1);

insert into showcase_song(id_showcase, id_song) values
    (1, 2),
    (1, 3);

insert into album(album_name, id_user) values 
    ("No woman no CRY", 1),
    ("No baby no main money", 2);

insert into album_song(id_album, id_song) values
    (1, 1),
    (2, 4);

insert into playlist (id_user, playlist_name, playlist_description) values 
	(4, "love you babe", "a playlist only for my lovely love"),
	(1, "my Top baby bangers", "best of the best");

insert into playlist_song(id_playlist, id_song) values
    (1, 4),
    (1, 2),
    (2, 1),
    (2, 2);

 

QL – Query Language

 

# selects songs with a feat
select * from user join song where feat is not NULL;

# select users with a playlist
select user_name, playlist_name from user join playlist;

# users that produced a song put on a showcase, a playlist and an album
select distinct(user_name) from user join playlist join album join showcase join song where feat is not null;
select count(distinct(user_name)) as n_all_done from user join playlist join album join showcase join song where feat is not null;

# count how many artist are verified
select count(distinct(user_name)) as n_verified from user where verified>0;
select count(*)as occurrences, playlist_name from user join playlist group by playlist_name;