Part 2: Building SQL Database

Contents

3.2. Part 2: Building SQL Database#

You have been tasked with designing an SQLite database using a Spotify tracks dataset. Your objective is to study the dataset, understand the different types of information it contains, and then create a well structured database model that separates this information into meaningful, normalized tables.

3.2.1. Task#

After loading the dataset from Task 1: Spotify Dataset, begin by exploring all available columns. The raw dataset is a single wide table that mixes attributes from multiple entities. Several fields contain repeated information, which is a strong indication that the data should be normalized.

Your task is to:

  1. Identify the logical entities in the dataset. In particular, track level attributes, album level attributes, and artist level attributes are all present in the same table.

  2. Split the dataset into separate tables representing these entities. At minimum, you should create three tables:

    • Tracks

    • Albums

    • Artists

  3. Define appropriate primary keys for each table and establish foreign key relationships to preserve all connections between tracks, albums, and artists.

  4. Ensure that no information is lost during normalization and that your final schema avoids unnecessary duplication.

The end result should be a clean and well structured SQLite database that accurately represents the relationships within the Spotify dataset.

Contents