CreateDatabase.sql

CREATE TABLE Artist (ArtistID INTEGER PRIMARY KEY, Name VARCHAR(255) NOT NULL);
CREATE TABLE Album (AlbumID INTEGER PRIMARY KEY, Title VARCHAR(255) NOT NULL, Year INTEGER);
CREATE TABLE Category (CategoryID INTEGER PRIMARY KEY, CategoryName VARCHAR(100) NOT NULL UNIQUE);
CREATE TABLE Song (SongID INTEGER PRIMARY KEY, Title VARCHAR(255) NOT NULL);
CREATE TABLE Plays (SongID INTEGER NOT NULL, ArtistID INTEGER NOT NULL, PRIMARY KEY (SongID, ArtistID), FOREIGN KEY (SongID) REFERENCES Song(SongID), FOREIGN KEY (ArtistID) REFERENCES Artist(ArtistID));
CREATE TABLE IsOn (SongID INTEGER NOT NULL, AlbumID INTEGER NOT NULL, PRIMARY KEY (SongID, AlbumID), FOREIGN KEY (SongID) REFERENCES Song(SongID), FOREIGN KEY (AlbumID) REFERENCES Album(AlbumID));
CREATE TABLE IsIn (CategoryID INTEGER NOT NULL, SongID INTEGER NOT NULL, PRIMARY KEY (CategoryID, SongID), FOREIGN KEY (CategoryID) REFERENCES Category(CategoryID), FOREIGN KEY (SongID) REFERENCES Song(SongID));