Skip to content
Permalink
master
Switch branches/tags

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

Music_Database/Database.sql

Go to file
 
 
Cannot retrieve contributors at this time
529 lines (461 sloc) 23.4 KB
Raw Blame
Open in GitHub Desktop
SET foreign_key_checks = 0;
DROP DATABASE IF EXISTS music_db;
CREATE DATABASE music_db;
USE music_db;
SET foreign_key_checks = 1;
CREATE TABLE Users
(
Username varchar(50) PRIMARY KEY,
Password varchar(50) NOT NULL,
Email varchar(50) NOT NULL
);
USE `music_db`;
DROP procedure IF EXISTS `Insert_new_user`;
DELIMITER $$
USE `music_db`$$
CREATE PROCEDURE `Insert_new_user`
(IN usernameparam varchar(50), IN pwordparam varchar(50), IN emailparam varchar(50))
BEGIN
INSERT INTO Users
VALUES(usernameparam, pwordparam, emailparam);
END$$
DELIMITER ;
DELIMITER $$
USE `music_db`$$
CREATE PROCEDURE `Insert_new_song`
(IN Song_ID integer,
IN Song_Name VARCHAR(100),
in Song_Length Integer,
in Album_ID Integer,
in artist_id integer)
BEGIN
INSERT INTO music_db.Song(Song_ID,Song_Name,Song_Length,Album_ID)
values (Song_ID,Song_Name,Song_Length,Album_ID);
insert into artist_song
values (artist_id,Song_ID);
END$$
DELIMITER ;
#INSERT INTO Users
#VALUES('zrb11002','password','zach.bugge@gmail.com');
#INSERT INTO Users
#VALUES('etl12003','password','email@gmail.com');
#INSERT INTO Users
#VALUES('par13002','password','patryk.rus@uconn.edu');
CALL `music_db`.`Insert_new_user`('zrb11002','password','zach.bugge@gmail.com');
CALL `music_db`.`Insert_new_user`('etl12003','password','email@gmail.com');
CALL `music_db`.`Insert_new_user`('par13002','password','patryk.rus@uconn.edu');
CALL `music_db`.`Insert_new_user`('wkc12001', 'pword1234', 'william.chin@uconn.edu');
#-------global library--------------------------
CREATE TABLE music_db.Artist #Entity
(
Artist_ID Integer NOT NULL,
Artist_Name VARCHAR(100) NOT NULL,
Artist_Biography TEXT,
PRIMARY KEY(Artist_ID)
);
INSERT INTO music_db.Artist (Artist_ID, Artist_Name, Artist_Biography)
VALUES(1, "The Beatles", "The Boys From Liverpool");
INSERT INTO music_db.Artist (Artist_ID, Artist_Name, Artist_Biography)
VALUES (10, 'Coldplay', 'A very popular band from the UK');
CREATE TABLE music_db.Album #Entity
(
Album_ID Integer NOT NULL,
Album_Name VARCHAR(100) NOT NULL,
Artist_ID Integer NOT NULL,
Album_Release_Date DATE, #YYYY-MM-DD http://www.w3schools.com/sql/sql_dates.asp
Album_Genre VARCHAR(100) NOT NULL,
Album_Length Integer NOT NULL,
PRIMARY KEY(Album_ID)
);
INSERT INTO music_db.Album (Album_ID, Album_Name,Album_Release_Date, Album_Genre, Artist_ID, Album_Length)
VALUES (1,"Sgt. Pepper's Lonely Hearts Club Band",'1967-06-01', 'Rock', 1, 0);
INSERT INTO music_db.Album (Album_ID, Album_Name, Album_Release_Date, Album_Genre, Artist_ID, Album_Length)
VALUES (30, 'A Head Full Of Dreams', '2015-12-04', 'Alternative Rock', 10, 0);
INSERT INTO music_db.Album (Album_ID, Album_Name, Album_Release_Date, Album_Genre, Artist_ID, Album_Length)
VALUES (31, 'Ghost Stories', '2014-05-19', 'Alternative Rock', 10, 0);
INSERT INTO music_db.Album (Album_ID, Album_Name, Album_Release_Date, Album_Genre, Artist_ID, Album_Length)
VALUES (32, 'Mylo Xyloto', '2011-10-24', 'Alternative Rock', 10, 0);
CREATE TABLE music_db.Song #Entity
(
Song_ID Integer NOT NULL,
Song_Name VARCHAR(100) NOT NULL,
Song_Length Integer,
Album_ID Integer NOT NULL,
PRIMARY KEY(Song_ID),
FOREIGN KEY(Album_ID) references Album(Album_ID) ON DELETE RESTRICT
);
DROP TRIGGER IF EXISTS albumlensum;
DELIMITER $$
CREATE TRIGGER albumlensum BEFORE INSERT ON Song
FOR EACH ROW
BEGIN
UPDATE Album SET Album_Length = Album_Length + NEW.Song_Length WHERE Album_ID = NEW.Album_ID;
END
$$
DELIMITER ;
#SHOW TRIGGERS;
#Sgt Peps
INSERT INTO music_db.Song(Song_ID,Song_Name,Song_Length,Album_ID)
values (1,"Sgt. Peppers Lonely Hearts Club Band",122,1);
INSERT INTO music_db.Song(Song_ID,Song_Name,Song_Length,Album_ID)
values (2,"With A Little Help From My Friends",164,1);
INSERT INTO music_db.Song(Song_ID,Song_Name,Song_Length,Album_ID)
values (3,"Lucy in the Sky With Diamonds",208,1);
INSERT INTO music_db.Song(Song_ID,Song_Name,Song_Length,Album_ID)
values (4,"Geting Better",168,1);
INSERT INTO music_db.Song(Song_ID,Song_Name,Song_Length,Album_ID)
values (5,"Fixing a Hole",156,1);
INSERT INTO music_db.Song(Song_ID,Song_Name,Song_Length,Album_ID)
values (6,"She's Leaving Home",215,1);
INSERT INTO music_db.Song(Song_ID,Song_Name,Song_Length,Album_ID)
values (7,"Being for the Benefit of Mr. Kite!",157,1);
INSERT INTO music_db.Song(Song_ID,Song_Name,Song_Length,Album_ID)
values (8,"Within You Without You",304,1);
INSERT INTO music_db.Song(Song_ID,Song_Name,Song_Length,Album_ID)
values (9,"When I'm Sixty-Four",157,1);
INSERT INTO music_db.Song(Song_ID,Song_Name,Song_Length,Album_ID)
values (10,"Lovely Rita",162,1);
INSERT INTO music_db.Song(Song_ID,Song_Name,Song_Length,Album_ID)
values (11,"Good Morning Good Morning",161,1);
INSERT INTO music_db.Song(Song_ID,Song_Name,Song_Length,Album_ID)
values (12,"Sgt. Peppers Lonely Hearts Club Band (Reprise)",79,1);
INSERT INTO music_db.Song(Song_ID,Song_Name,Song_Length,Album_ID)
values (13,"A Day In The Life",79,1);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (100, 'A Head Full of Dreams', 223, 30);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (101, 'Birds', 223, 30);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (102, 'Hymn for the Weekend', 258, 30);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (103, 'Everglow', 282, 30);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (104, 'Adventure of a Lifetime', 263, 30);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (105, 'Fun', 267, 30);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (106, 'Kaleidoscope', 111, 30);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (107, 'Army of One', 376, 30);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (108, 'Amazing Day', 271, 30);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (109, 'Colour Spectrum', 60, 30);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (110, 'Up&Up', 405, 30);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (111, 'Miracles', 235, 30);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (112, 'Always in My Head', 216, 31);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (113, 'Magic', 285, 31);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (114, 'Ink', 228, 31);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (115, 'True Love', 245, 31);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (116, 'Midnight', 294, 31);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (117, 'Anothers Arms', 234, 31);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (118, 'Oceans', 321, 31);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (119, 'A Sky Full of Stars', 268, 31);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (120, 'O', 467, 31);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (121, 'Mylo Xyloto', 43, 32);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (122, 'Hurts Like Heaven', 242, 32);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (123, 'Paradise', 277, 32);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (124, 'Charlie Brown', 285, 32);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (125, 'Us Against the World', 239, 32);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (126, 'M.M.I.X.', 49, 32);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (127, 'Every Teardrop is a Waterfall', 240, 32);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (128, 'Major Minus', 210, 32);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (129, 'U.F.O', 137, 32);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (130, 'Princess of China', 239, 32);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (131, 'Up in Flames', 193, 32);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (132, 'A Hopful Transmission', 33, 32);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (133, 'Dont Let It Break Your Heart', 234, 32);
INSERT INTO music_db.Song (Song_ID, Song_Name, Song_Length, Album_ID)
VALUES (134, 'Up with the Birds', 225, 32);
CREATE TABLE music_db.Rating (
Rating int NOT NULL,
PRIMARY KEY (Rating)
);
INSERT INTO music_db.Rating (Rating) VALUES(1),(2),(3),(4),(5);
CREATE TABLE music_db.Artist_Song #M:M Relationship between Artist and Song
(
Artist_ID Integer NOT NULL,
Song_ID Integer NOT NULL,
PRIMARY KEY(Artist_ID, Song_ID),
FOREIGN KEY(Artist_ID) REFERENCES Artist(Artist_ID) ON DELETE RESTRICT,
FOREIGN KEY(Song_ID) REFERENCES Song(Song_ID) ON DELETE RESTRICT
#Upper two statements make it so you have to delete this row before deleting something in Song/Artist.
);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (1, 1);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (1, 2);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (1, 3);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (1, 4);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (1, 5);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (1, 6);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (1, 7);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (1, 8);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (1, 9);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (1, 10);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (1, 11);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (1, 12);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (1, 13);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 100);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 101);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 102);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 103);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 104);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 105);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 106);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 107);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 108);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 109);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 111);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 112);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 113);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 114);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 115);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 116);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 117);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 118);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 119);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 120);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 121);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 122);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 123);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 124);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 125);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 126);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 127);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 128);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 129);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 130);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 131);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 132);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 133);
INSERT INTO music_db.Artist_Song (Artist_ID, Song_ID) VALUES (10, 134);
CREATE TABLE music_db.Artist_Album #M:M Relationship between Artist and Album
(
Artist_ID Integer NOT NULL,
Album_ID Integer NOT NULL,
PRIMARY KEY(Artist_ID, Album_ID),
FOREIGN KEY(Artist_ID) REFERENCES Artist(Artist_ID) ON DELETE RESTRICT,
FOREIGN KEY(Album_ID) REFERENCES Album(Album_ID) ON DELETE RESTRICT
#Upper two statements make it so you have to delete this row before deleting something in Song/Artist.
);
INSERT INTO music_db.Artist_Album (Artist_ID, Album_ID) VALUES (1, 1);
INSERT INTO music_db.Artist_Album (Artist_ID, Album_ID) VALUES (10, 30);
INSERT INTO music_db.Artist_Album (Artist_ID, Album_ID) VALUES (10, 31);
INSERT INTO music_db.Artist_Album (Artist_ID, Album_ID) VALUES (10, 32);
CALL `music_db`.`Insert_new_song`(852, "100 percent a real song ", 85, 30, 10);
#-------individual users
#---zach
DROP DATABASE IF EXISTS zrb11002_db;
CREATE DATABASE zrb11002_db;
USE zrb11002_db;
FLUSH PRIVILEGES;
DROP USER IF EXISTS 'zrb11002'@'localhost';
create user 'zrb11002'@'localhost' identified by 'password';
grant all privileges on zrb11002_db.* to 'zrb11002'@'localhost';
grant select on music_db.song to 'zrb11002'@'localhost';
grant select on music_db.artist to 'zrb11002'@'localhost';
grant select on music_db.album to 'zrb11002'@'localhost';
grant insert on music_db.users to 'zrb11002'@'localhost';
CREATE TABLE zrb11002_db.USERLIBRARY (
Rating int,
Song_ID Integer NOT NULL,
PRIMARY KEY (Song_ID),
FOREIGN KEY (Song_ID) REFERENCES music_db.Song(Song_ID) ON DELETE RESTRICT,
FOREIGN KEY (Rating) REFERENCES music_db.Rating(Rating)
);
insert into zrb11002_db.USERLIBRARY (Rating, Song_ID) value (1, 1);
insert into zrb11002_db.USERLIBRARY (Rating, Song_ID) value (4, 2);
insert into zrb11002_db.USERLIBRARY (Rating, Song_ID) value (4, 3);
insert into zrb11002_db.USERLIBRARY (Rating, Song_ID) value (1, 4);
insert into zrb11002_db.USERLIBRARY (Rating, Song_ID) value (1, 5);
insert into zrb11002_db.USERLIBRARY (Rating, Song_ID) value (1, 6);
insert into zrb11002_db.USERLIBRARY (Rating, Song_ID) value (2, 7);
insert into zrb11002_db.USERLIBRARY (Rating, Song_ID) value (2, 8);
insert into zrb11002_db.USERLIBRARY (Rating, Song_ID) value (3, 9);
insert into zrb11002_db.USERLIBRARY (Rating, Song_ID) value (3, 10);
insert into zrb11002_db.USERLIBRARY (Rating, Song_ID) value (5, 11);
insert into zrb11002_db.USERLIBRARY (Rating, Song_ID) value (4, 12);
insert into zrb11002_db.USERLIBRARY (Rating, Song_ID) value (4, 13);
CREATE TABLE zrb11002_db.playlist_name
(
Playlist_ID integer NOT NULL,
Playlist_Name varchar(50),
PRIMARY KEY(Playlist_ID)
);
insert into zrb11002_db.playlist_name value(854, 'beatles faves');
insert into zrb11002_db.playlist_name value(842, 'May faves');
insert into zrb11002_db.playlist_name value(872, 'Party Mix');
CREATE TABLE zrb11002_db.Playlist_Song #M:M Relationship between Playlist and Song
(
Playlist_ID integer NOT NULL,
Song_ID Integer NOT NULL,
PRIMARY KEY(Playlist_ID, Song_ID),
FOREIGN KEY(Playlist_ID) references zrb11002_db.playlist_name(Playlist_ID) ON DELETE RESTRICT,
FOREIGN KEY(Song_ID) references music_db.song(Song_ID) ON DELETE RESTRICT
);
insert into zrb11002_db.Playlist_Song (Playlist_ID, Song_ID) value (854, 3);
insert into zrb11002_db.Playlist_Song (Playlist_ID, Song_ID) value (854, 1);
insert into zrb11002_db.Playlist_Song (Playlist_ID, Song_ID) value (854, 10);
insert into zrb11002_db.Playlist_Song (Playlist_ID, Song_ID) value (854, 8);
insert into zrb11002_db.Playlist_Song (Playlist_ID, Song_ID) value (854, 9);
insert into zrb11002_db.Playlist_Song (Playlist_ID, Song_ID) value (842, 9);
insert into zrb11002_db.Playlist_Song (Playlist_ID, Song_ID) value (842, 2);
insert into zrb11002_db.Playlist_Song (Playlist_ID, Song_ID) value (842, 1);
insert into zrb11002_db.Playlist_Song (Playlist_ID, Song_ID) value (872, 1);
insert into zrb11002_db.Playlist_Song (Playlist_ID, Song_ID) value (872, 12);
insert into zrb11002_db.Playlist_Song (Playlist_ID, Song_ID) value (872, 13);
insert into zrb11002_db.Playlist_Song (Playlist_ID, Song_ID) value (872, 5);
insert into zrb11002_db.Playlist_Song (Playlist_ID, Song_ID) value (872, 7);
#---erik
DROP DATABASE IF EXISTS etl12003_db;
CREATE DATABASE etl12003_db;
USE etl12003_db;
FLUSH PRIVILEGES;
DROP USER IF EXISTS 'etl12003'@'localhost';
create user 'etl12003'@'localhost' identified by 'password';
grant all privileges on etl12003_db.* to 'etl12003'@'localhost';
grant select on music_db.song to 'etl12003'@'localhost';
grant select on music_db.artist to 'etl12003'@'localhost';
grant select on music_db.album to 'etl12003'@'localhost';
grant insert on music_db.users to 'etl12003'@'localhost';
CREATE TABLE etl12003_db.USERLIBRARY (
Rating int,
Song_ID Integer NOT NULL,
PRIMARY KEY (Song_ID),
FOREIGN KEY (Song_ID) REFERENCES music_db.Song(Song_ID) ON DELETE RESTRICT,
FOREIGN KEY (Rating) REFERENCES music_db.Rating(Rating)
);
insert into etl12003_db.USERLIBRARY (Rating, Song_ID) value (2,9);
insert into etl12003_db.USERLIBRARY (Rating, Song_ID) value (3,7);
insert into etl12003_db.USERLIBRARY (Rating, Song_ID) value (2,4);
insert into etl12003_db.USERLIBRARY (Rating, Song_ID) value (4,13);
insert into etl12003_db.USERLIBRARY (Rating, Song_ID) value (2,1);
insert into etl12003_db.USERLIBRARY (Rating, Song_ID) value (5,5);
insert into etl12003_db.USERLIBRARY (Rating, Song_ID) value (3,11);
insert into etl12003_db.USERLIBRARY (Rating, Song_ID) value (2,12);
insert into etl12003_db.USERLIBRARY (Rating, Song_ID) value (3, 123);
insert into etl12003_db.USERLIBRARY (Rating, Song_ID) value (2, 118);
insert into etl12003_db.USERLIBRARY (Rating, Song_ID) value (2, 109);
insert into etl12003_db.USERLIBRARY (Rating, Song_ID) value (3, 115);
insert into etl12003_db.USERLIBRARY (Rating, Song_ID) value (1, 106);
insert into etl12003_db.USERLIBRARY (Rating, Song_ID) value (5, 122);
CREATE TABLE etl12003_db.playlist_name
(
Playlist_ID integer NOT NULL,
Playlist_Name varchar(50),
PRIMARY KEY (Playlist_ID)
);
insert into etl12003_db.playlist_name value(867, 'amazing songs');
insert into etl12003_db.playlist_name value(874, 'YAY');
insert into etl12003_db.playlist_name value(834, 'WAFFLE PARTY MIX');
CREATE TABLE etl12003_db.Playlist_Song #M:M Relationship between Playlist and Song
(
Playlist_ID Integer NOT NULL,
Song_ID Integer NOT NULL,
PRIMARY KEY(Playlist_ID, Song_ID),
FOREIGN KEY(Playlist_ID) references etl12003_db.playlist_name(Playlist_ID) ON DELETE RESTRICT,
FOREIGN KEY(Song_ID) references music_db.song(Song_ID) ON DELETE RESTRICT
);
insert into etl12003_db.Playlist_Song (Playlist_ID, Song_ID) value (867, 5);
insert into etl12003_db.Playlist_Song (Playlist_ID, Song_ID) value (867, 9);
insert into etl12003_db.Playlist_Song (Playlist_ID, Song_ID) value (867, 4);
insert into etl12003_db.Playlist_Song (Playlist_ID, Song_ID) value (867, 12);
insert into etl12003_db.Playlist_Song (Playlist_ID, Song_ID) value (867, 118);
insert into etl12003_db.Playlist_Song (Playlist_ID, Song_ID) value (867, 100);
insert into etl12003_db.Playlist_Song (Playlist_ID, Song_ID) value (867, 102);
insert into etl12003_db.Playlist_Song (Playlist_ID, Song_ID) value (874, 1);
insert into etl12003_db.Playlist_Song (Playlist_ID, Song_ID) value (874, 11);
insert into etl12003_db.Playlist_Song (Playlist_ID, Song_ID) value (874, 9);
insert into etl12003_db.Playlist_Song (Playlist_ID, Song_ID) value (874, 4);
insert into etl12003_db.Playlist_Song (Playlist_ID, Song_ID) value (874, 13);
insert into etl12003_db.Playlist_Song (Playlist_ID, Song_ID) value (874, 7);
insert into etl12003_db.Playlist_Song (Playlist_ID, Song_ID) value (874, 12);
insert into etl12003_db.Playlist_Song (Playlist_ID, Song_ID) value (874, 5);
insert into etl12003_db.Playlist_Song (Playlist_ID, Song_ID) value (834, 11);
insert into etl12003_db.Playlist_Song (Playlist_ID, Song_ID) value (834, 12);
insert into etl12003_db.Playlist_Song (Playlist_ID, Song_ID) value (834, 13);
insert into etl12003_db.Playlist_Song (Playlist_ID, Song_ID) value (834, 130);
insert into etl12003_db.Playlist_Song (Playlist_ID, Song_ID) value (834, 103);
#---patryk
DROP DATABASE IF EXISTS par13002_db;
CREATE DATABASE par13002_db;
USE par13002_db;
FLUSH PRIVILEGES;
DROP USER IF EXISTS 'par13002'@'localhost';
create user 'par13002'@'localhost' identified by 'password';
grant all privileges on par13002_db.* to 'par13002'@'localhost';
grant select on music_db.song to 'par13002'@'localhost';
grant select on music_db.artist to 'par13002'@'localhost';
grant select on music_db.album to 'par13002'@'localhost';
grant insert on music_db.users to 'par13002'@'localhost';
CREATE TABLE par13002_db.USERLIBRARY (
Rating int,
Song_ID Integer NOT NULL,
PRIMARY KEY (Song_ID),
FOREIGN KEY (Song_ID) REFERENCES music_db.Song(Song_ID) ON DELETE RESTRICT,
FOREIGN KEY (Rating) REFERENCES music_db.Rating(Rating)
);
insert into par13002_db.USERLIBRARY (Rating, Song_ID) value (2, 100);
insert into par13002_db.USERLIBRARY (Rating, Song_ID) value (4, 115);
insert into par13002_db.USERLIBRARY (Rating, Song_ID) value (4, 117);
insert into par13002_db.USERLIBRARY (Rating, Song_ID) value (5, 131);
insert into par13002_db.USERLIBRARY (Rating, Song_ID) value (1, 120);
insert into par13002_db.USERLIBRARY (Rating, Song_ID) value (3, 123);
insert into par13002_db.USERLIBRARY (Rating, Song_ID) value (2, 118);
insert into par13002_db.USERLIBRARY (Rating, Song_ID) value (2, 119);
insert into par13002_db.USERLIBRARY (Rating, Song_ID) value (3, 105);
insert into par13002_db.USERLIBRARY (Rating, Song_ID) value (1, 106);
insert into par13002_db.USERLIBRARY (Rating, Song_ID) value (5, 112);
insert into par13002_db.USERLIBRARY (Rating, Song_ID) value (3, 121);
insert into par13002_db.USERLIBRARY (Rating, Song_ID) value (4, 122);
CREATE TABLE par13002_db.playlist_name
(
Playlist_ID integer NOT NULL,
Playlist_Name varchar(50),
PRIMARY KEY(Playlist_ID)
);
insert into par13002_db.playlist_name value(100, 'Homework');
insert into par13002_db.playlist_name value(200, 'Bad Mood');
CREATE TABLE par13002_db.Playlist_Song #M:M Relationship between Playlist and Song
(
Playlist_ID integer NOT NULL,
Song_ID Integer NOT NULL,
PRIMARY KEY(Playlist_ID, Song_ID),
FOREIGN KEY(Playlist_ID) references par13002_db.playlist_name(Playlist_ID) ON DELETE RESTRICT,
FOREIGN KEY(Song_ID) references music_db.song(Song_ID) ON DELETE RESTRICT
);
insert into par13002_db.Playlist_Song (Playlist_ID, Song_ID) value (100, 105);
insert into par13002_db.Playlist_Song (Playlist_ID, Song_ID) value (100, 115);
insert into par13002_db.Playlist_Song (Playlist_ID, Song_ID) value (100, 120);
insert into par13002_db.Playlist_Song (Playlist_ID, Song_ID) value (100, 130);
insert into par13002_db.Playlist_Song (Playlist_ID, Song_ID) value (100, 117);
insert into par13002_db.Playlist_Song (Playlist_ID, Song_ID) value (100, 118);
insert into par13002_db.Playlist_Song (Playlist_ID, Song_ID) value (100, 100);
insert into par13002_db.Playlist_Song (Playlist_ID, Song_ID) value (100, 102);
insert into par13002_db.Playlist_Song (Playlist_ID, Song_ID) value (200, 127);
insert into par13002_db.Playlist_Song (Playlist_ID, Song_ID) value (200, 119);
insert into par13002_db.Playlist_Song (Playlist_ID, Song_ID) value (200, 130);
insert into par13002_db.Playlist_Song (Playlist_ID, Song_ID) value (200, 103);