SQLite3 schema for Spotify dataset in 3NF

Description: Download the Spotify songs data set. Create a SQLite3 schema to store this data in at least 3rd normal form (3NF), and populate the tables. Use an SQL query to find the names of all playlists that contain instrumentals.

Spotify Songs Dataset

First, let’s download and see the dataset we gonna use. It is a dataset of Spotify songs.

df = pd.read_csv('https://raw.githubusercontent.com/rfordatascience/tidytuesday/master/data/2020/2020-01-21/spotify_songs.csv')`
df

32833 rows × 23 columns

df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 32833 entries, 0 to 32832
Data columns (total 23 columns):
track_id                    32833 non-null object
track_name                  32828 non-null object
track_artist                32828 non-null object
track_popularity            32833 non-null int64
track_album_id              32833 non-null object
track_album_name            32828 non-null object
track_album_release_date    32833 non-null object
playlist_name               32833 non-null object
playlist_id                 32833 non-null object
playlist_genre              32833 non-null object
playlist_subgenre           32833 non-null object
danceability                32833 non-null float64
energy                      32833 non-null float64
key                         32833 non-null int64
loudness                    32833 non-null float64
mode                        32833 non-null int64
speechiness                 32833 non-null float64
acousticness                32833 non-null float64
instrumentalness            32833 non-null float64
liveness                    32833 non-null float64
valence                     32833 non-null float64
tempo                       32833 non-null float64
duration_ms                 32833 non-null int64
dtypes: float64(9), int64(4), object(10)
memory usage: 5.8+ MB

Database normalization Concepts

Now, let’s quick go through some concepts about database normalization.

Database normalization is performed for two main reasons - reduce redundancy and prevent inconsistencies on insert/update/delete.

Note: A fully normalized database is in domain-key normal form (DK/NF) if every constraint is a logical consequence of the definition of the candidate key and domains. However, most practical normalization procedures go through a series of steps known as first, second and third normal forms, and ignore potential modification anomalies that may remain.

First Normal Form (1NF)

1. Table has a primary key (unique, non-null column that identifies each row)
2. No repeating groups of columns
3. Each cell contains a single value

Second Normal Form (2NF)

All columns in each row depend fully on candidate keys. This can be quite tricky to understand.

1. Look for candidate composite keys that can uniquely identify a row.
2. See if the other columns depend on ALL columns of the composite key.

In the sample example below, suppose we have a table for academic books. Note that (publisher, title) is a candidate key. However, headquarters depends only on publisher and not on title, so this violates 2NF.

Third Normal Form (3NF)

No transitive dependencies between non-candidate columns. In the table below, both major and major_description depend on the name (or row number), but major_description only depends on name via the major. This is a transitive dependency and violates 3NF.

Normalizing the Spotify dataset

Is our Spotify dataset normalized? Yes, but only in first normal form (1NF). It is obvious that there are no repeating columns in the dataset and each column contains only single value. For converting the dataset into third normal form, we need to have a closer look at each columns.

for col in df.columns:
    print(col)

track_id
track_name
track_artist
track_popularity
track_album_id
track_album_name
track_album_release_date
playlist_name
playlist_id
playlist_genre
playlist_subgenre
danceability
energy
key
loudness
mode
speechiness
acousticness
instrumentalness
liveness
valence
tempo
duration_ms

Besed on these columns, we can break the dataset into three parts:

• Track

• Album

• Playlist

track_id, track_name, track_artist, track_popularity. These are columns that should be in the track table. In addition, variables such as danceability are features of the track, even though they don’t start with “track”. This is the case for all columns following danceability, including energy, key, and all the way up to duration_ms.

Finally, we need to check the conditions for the dataset to be in the third normal form. Take track_name, for instance. Knowing track_name allows me to identify other characteristics of the track without having to know its primary key - track_id, which is kind of transitive dependencies. Therefor, we have to take track_name out of the table and store it in a table of its own.

df_track = pd.concat([df.iloc[:,[0,2,3]],df.iloc[:,11:]],axis=1).drop_duplicates()
df_track

28356 rows × 15 columns

df_track_name = df.iloc[:,[0,1]].drop_duplicates()
df_track_name

28356 rows × 2 columns

There are three columns that are related to the Album attributes - track_album_id,track_album_name and track_album_release_date.

The third normal form constraint is also applied in this entity. While the id is the most natural primary key, note that if you know the album’s name, you may be able to identify its release date without knowing its id. So we’ll have to split these columns into different tables.

df_album_name = df.iloc[:,[4,5]].drop_duplicates()
df_album_name

22545 rows × 2 columns

df_album_release = df.loc[:,['track_album_id','track_album_release_date']].drop_duplicates()
df_album_release

22545 rows × 2 columns

There are four columns that are related to the Playlist - playlist_name, playlist_id, playlist_genre and playlist_subgenre.

Since if we know the name of the playlist, we may be able to guess its genre as well as its subgenere. So there exists some dependencies between these columns to some extent. In addition, we find that if we know a playlist’s subgenre, we automatically know its genre. But the opposite isn’t true , the playlist_genre cannot be used to identify the playlist_subgenre. So playlist_genre and playlist_subgenre should be put into a seperate table that its primary key is the column playlist_subgenre.

df_playlist_name = df_playlist.loc[:,['playlist_id','playlist_name']].drop_duplicates()
df_playlist_name

471 rows × 2 columns

df_playlist_subgenre = df_playlist.loc[:,['playlist_id','playlist_subgenre']].drop_duplicates()
df_playlist_subgenre

480 rows × 2 columns

df_subgenre_genre = df_playlist.iloc[:,[3,2]].drop_duplicates()
df_subgenre_genre

Finally, we have a series of tables for each entity.

We have to create tables to connect tracks with albums as well as playlists.

df_track_album = df.iloc[:,[0,4]].drop_duplicates()
df_track_album

28356 rows × 2 columns

df_track_playlist = df.loc[:,['track_id','playlist_id']].drop_duplicates()
df_track_playlist

32251 rows × 2 columns

The structure of normalization is pretty clear. It looks like this:

Data Denormalization

After doing data denormalization, we can get a dataset nearly same as the original one.

(
    df_track.
    merge(df_track_name).
    merge(df_track_album).
    merge(df_album_name).
    merge(df_album_release).
    merge(df_track_playlist).
    merge(df_playlist_name).
    merge(df_playlist_subgenre).
    merge(df_subgenre_genre)
)

32948 rows × 23 columns

Create SQLite Schema and Store tables

import sqlite3
conn = sqlite3.connect('hw4.db')

import pandas as pd

df = pd.read_csv('spotify_songs.csv')
df.to_sql('spotify_songs', conn, if_exists = 'replace', index=False)

df_track.to_sql('df_track', 
             conn, 
             if_exists='replace', 
             index = False)

df_track_name.to_sql('df_track_name', conn, if_exists='replace', index = False)
df_track_album.to_sql('df_track_album', conn, if_exists='replace', index = False)
df_album_name.to_sql('df_album_name', conn, if_exists='replace', index = False)
df_album_release.to_sql('df_album_release', conn, if_exists='replace', index = False)
df_track_playlist.to_sql('df_track_playlist', conn, if_exists='replace', index = False)
df_playlist_name.to_sql('df_playlist_name', conn, if_exists='replace', index = False)
df_playlist_subgenre.to_sql('df_playlist_subgenre', conn, if_exists='replace', index = False)
df_subgenre_genre.to_sql('df_subgenre_genre', conn, if_exists='replace', index = False)

c = conn.cursor()
c.execute("SELECT * FROM sqlite_master WHERE type='table'")
c.fetchall()

These are all the tables we have in the database “hw4.db”:

[('table',
  'spotify_songs',
  'spotify_songs',
  2,
  'CREATE TABLE "spotify_songs" (\n"track_id" TEXT,\n  "track_name" TEXT,\n  "track_artist" TEXT,\n  "track_popularity" INTEGER,\n  "track_album_id" TEXT,\n  "track_album_name" TEXT,\n  "track_album_release_date" TEXT,\n  "playlist_name" TEXT,\n  "playlist_id" TEXT,\n  "playlist_genre" TEXT,\n  "playlist_subgenre" TEXT,\n  "danceability" REAL,\n  "energy" REAL,\n  "key" INTEGER,\n  "loudness" REAL,\n  "mode" INTEGER,\n  "speechiness" REAL,\n  "acousticness" REAL,\n  "instrumentalness" REAL,\n  "liveness" REAL,\n  "valence" REAL,\n  "tempo" REAL,\n  "duration_ms" INTEGER\n)'),
 ('table',
  'df_track',
  'df_track',
  2214,
  'CREATE TABLE "df_track" (\n"track_id" TEXT,\n  "track_artist" TEXT,\n  "track_popularity" INTEGER,\n  "danceability" REAL,\n  "energy" REAL,\n  "key" INTEGER,\n  "loudness" REAL,\n  "mode" INTEGER,\n  "speechiness" REAL,\n  "acousticness" REAL,\n  "instrumentalness" REAL,\n  "liveness" REAL,\n  "valence" REAL,\n  "tempo" REAL,\n  "duration_ms" INTEGER\n)'),
 ('table',
  'df_track_name',
  'df_track_name',
  3116,
  'CREATE TABLE "df_track_name" (\n"track_id" TEXT,\n  "track_name" TEXT\n)'),
 ('table',
  'df_track_album',
  'df_track_album',
  3450,
  'CREATE TABLE "df_track_album" (\n"track_id" TEXT,\n  "track_album_id" TEXT\n)'),
 ('table',
  'df_album_name',
  'df_album_name',
  3817,
  'CREATE TABLE "df_album_name" (\n"track_album_id" TEXT,\n  "track_album_name" TEXT\n)'),
 ('table',
  'df_album_release',
  'df_album_release',
  4083,
  'CREATE TABLE "df_album_release" (\n"track_album_id" TEXT,\n  "track_album_release_date" TEXT\n)'),
 ('table',
  'df_track_playlist',
  'df_track_playlist',
  4306,
  'CREATE TABLE "df_track_playlist" (\n"track_id" TEXT,\n  "playlist_id" TEXT\n)'),
 ('table',
  'df_playlist_name',
  'df_playlist_name',
  4724,
  'CREATE TABLE "df_playlist_name" (\n"playlist_id" TEXT,\n  "playlist_name" TEXT\n)'),
 ('table',
  'df_playlist_subgenre',
  'df_playlist_subgenre',
  4732,
  'CREATE TABLE "df_playlist_subgenre" (\n"playlist_id" TEXT,\n  "playlist_subgenre" TEXT\n)'),
 ('table',
  'df_subgenre_genre',
  'df_subgenre_genre',
  4738,
  'CREATE TABLE "df_subgenre_genre" (\n"playlist_subgenre" TEXT,\n  "playlist_genre" TEXT\n)')]

SQL query for playlists that contain instrumentals

query = '''
SELECT playlist_name, df_track_name.track_name, df_track.track_artist, df_track.instrumentalness
FROM df_track
LEFT JOIN
df_track_name
ON df_track.track_id = df_track_name.track_id
LEFT JOIN
df_track_playlist
ON df_track.track_id = df_track_playlist.track_id
LEFT JOIN
df_playlist_name
ON df_playlist_name.playlist_id = df_track_playlist.playlist_id
WHERE df_track.instrumentalness > 0 -- a query contains instrumentals when its instrumentalness is greater than zero
ORDER BY df_track.instrumentalness DESC
'''

pd.read_sql(query, conn)