Class Project - Design a Database

Group name: audiophiles.wav

This assignment was to design and create an SQL database. My group chose to work in the music domain.
In this domain, we will be modeling the following entities:

• Song/File
• Album
• Artist
• Genre
• Playlist

We will be looking closely at well established music databases such as Spotify and Apple Music and take note
of how they store and retrieve files. The goal is to create a database that stores pointers to files on my computer,
without actually holding the audio files themselves.

The database will support the following functionalitites:

• Finding songs by title, artist, duration, format, and genre
• Finding albums by title, artist, and year of release
• Finding artists by name and displaying all of their songs or albums

Additionally, we would like to try to include some form of creating a playlist if possible. This may not be possible
because we are storing the indexes of the files in the database instead of the actual files, but it would be a nice feature to add.

ER Diagram

Assumptions:

• Each Songfile has an artist, album, (optional) genre, duration, file format, and file location
• Each Artist has many songs, many albums, and possibly a genre
• Each Album has many songs, an artist, and possibly a genre
• A Playlist has a name and is composed of many songs

SQL Data Definitions:

• CREATE TABLE SongFile (
•         songTitle varchar(72) NOT NULL,
•         artistName varchar(72) NOT NULL,
•         albumTitle varchar(72) NOT NULL,
•         trackNo int,
•         duration float NOT NULL,
•         format varchar(6),
•         location varchar(255) NOT NULL,
•         genreName varchar(72),
•         playlistName varchar(72),
•         FOREIGN KEY artistName REFERENCES Artist(artistName),
•         FOREIGN KEY albumTitle REFERENCES Album(albumTitle),
•         FOREIGN KEY genreName REFERENCES Genre(genreName),
•         FOREIGN KEY playlistName REFERENCES Playlist(playlistName),
•         PRIMARY KEY (songTitle, artistName, albumTitle, duration)
• );
•  
• CREATE TABLE Artist (
•         artistName varchar(72) PRIMARY KEY,
•         genreName varchar(72),
•         FOREIGN KEY genreName REFERENCES Genre(genreName)
• );
•  
• CREATE TABLE Album (
•         albumTitle varchar(72) PRIMARY KEY,
•         artistName varchar(72),
•         genreName varchar(72),
•         tracks int NOT NULL,
•         duration float NOT NULL,
•         year int NOT NULL,
•         FOREIGN KEY artistName REFERENCES Artist(artistName),
•         FOREIGN KEY genreName REFERENCES Genre(genreName),
• );
•  
• CREATE TABLE Genre (
•         genreName varchar(72) PRIMARY KEY,
•         description varchar(255)
• );
•  
• CREATE TABLE Playlist (
•         playlistName varchar(72) PRIMARY KEY,
•         tracks int,
•         duration float,
• );

Schema:

• SongFile(songTitle, artistName, albumTitle, trackNo duration, format, location, genreName, playlistName)
• Artist(artistName, genreName)
• Album(albumTitle, artistName, tracks, duration, year, genreName)
• Genre(genreName, description)
• Playlist(playlistName, tracks, duration)

Normalization of Schema:

The database as we designed it is already in first and second normal form.
We could put the database into third normal form by breaking up the tables like so:

• SongArtist(songTitle, artistName)
• SongAlbum(songTitle, albumTitle, trackNo)
• SongInfo(songTitle, genreName, playlistName)
• SongLocation(songTitle, duration, format, location)
• Artist(artistName, genreName)
• Album(albumTitle, artistName, tracks, duration, year, genreName)
• Genre(genreName, description)
• Playlist(playlistName, tracks, duration)

But, I think this would only complicate things when forming queries on the songs.
Third normal form may still be considered in the final version of the database,
but for now we will stick with second normal form.

Click here to download a copy of the database
(the buttons in the form won't work because they open files on my computer)