Question

SQL

Create a database called MyMusician

Create three tables, one named “User”, one named “Song”, and one named “Playlist”.

The “User” table should have a column for userID (integer, autoincrement, not null, primary key), username, and password. It should also have one column for a foreign key we will assign later called “playlistID” (integer).

The “Playlist” table should have a “playlistID” (integer, autoincrement, not null, primary key), and a column for a foreign key we will assign later called “songID” (integer).

The “Song table should have a “songID” (integer, autoincrement, not null, primary key), and a column for name, artist, album, year, duration, and genre.

Add an additional column to Playlist named “name”, which will contain the playlist name. Then add another table called “UserPL” that contains a userID, and a “name”. This is how we connect multip[le users with multiuple playlists.

Answer 1

Create a database called MyMusician

create database MyMusician;

Create three tables, one named "User", one named "Song", and one named "Playlist" within MyMusician

Create table User:

create table User(usedID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, username varchar(100), password varchar(100), playlistID INT);

Create table Playlist:

create table Playlist( playlistID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, songID INT);

Create table Song:

create table Song(songID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, name varchar(100), artist varchar(100), album varchar(100), year INT, duration DECIMAL(8,2), genre varchar(20));

Add an additional column to Playlist named "name", which will contain the playlist name.

Alter table Playlist add name varchar(100) NOT NULL;

Create table UserPL:

create table UserPL(userID INT NOT NULL , name varchar(100) NOT NULL, constraint fk_user_userPL FOREIGN KEY(userID) references User(userID), PRIMARY KEY(userID, name));

Add foreign key constraint to User table:

Alter table User fk_user_playlist FOREIGN KEY(playlistID) references Playlist(playlistID);

Add foreign key constraint to Playlist:

Alter table Playlist fk_playlist_song FOREIGN KEY(songID) references Song(songID);