4. Lab: Music App Database

4. Lab: Music App Database

🎯 Learning Goals

  • Confidently write basic and intermediate SQL queries.
  • Build a library of queries that can be referenced for future work.

đź“— Technical Vocabulary

  • Foreign key constraint
  • Dot command
  • Subquery
 

Music App Database

Let’s imagine we’re building a new music app, where listeners can play songs and keep track of which songs they played and when. Five beta testers have been using a prototype of the app; their data has been saved to a few CSV files, and now we want to design and use a database that can power the app.
Remix this Replit to follow along:
Music App Database Music App Database

Looking Around

After you remix the Replit, click around in the files and see if you can determine what each file does and why it’s there. Make sure the sidebar on the left is open, as shown in the picture to the right.
  • main.sql
    • This is the file that is run when we hit the Run button in Replit.
    • Notice that we .read the other two sql files in the Setup Section in this File.
    • We will be working in this file to practice our SQL skills.
  • create_tables.sql
    • This file creates the tables that our CSV data will be read into.
    • Take some time to read through each of the create table statements. Pay attention to the data types and column names.
    • Something new here! Some tables, such as the albums table, contain something called a foreign key. See the section below on what this is.
  • import_data.sql
    • This file, which is run after the create_tables.sql file, loads the data from the CSV files into the SQLite database.
    • Discuss: Why do you think this file has to be run after the create_tables.sql file?
  • data directory, including the files:
    • albums.csv
    • artists.csv
    • listeners.csv
    • plays.csv
    • songs.csv
    • “CSV” stands for “comma-separated values.” Each line in a CSV document represents one record in the table. The different fields are separated by commas, hence the name.
    • These files contain the data that we will load into the SQLite database. Once we do that, we can run queries against the data.
    • Notice that the CSV header row (the first row) contains the same column names as the columns we create for each table in the create_tables.sql file.
notion image

Foreign Keys

A foreign key constraint is a rule that helps keep our data accurate and consistent. It ensures that a column in one table only contains values that exist in a specific column of another table.
For example, the songs.album_id column has a foreign key constraint that requires every value in that column to also exist in the albums.album_id column.
This means:
  • If we try to insert a song with an album_id that isn’t in the albums table, we’ll get an error.
  • If we try to delete an album from the albums table while there are songs referencing that album_id, we’ll also get an error.
This constraint prevents data that is incomplete or doesn’t make sense and keeps relationships between tables valid.
erDiagram
    ALBUM ||--|{ SONG : has
    ALBUM {
        integer album_id PK
        text title
        integer artist_id FK
        date release_date
    }
    SONG {
        integer song_id PK
        text title
        integer album_id FK
    }

Code-Along | Music App Database

The Setup Section in main.sql

We don’t need to change any code here. Let’s look at what each line does.
The commands here are called “dot commands.” They are different from queries. Instead of interacting directly with data in a table, dot commands do things like run other SQL files, print text to the output console, or change how printed text is formatted.
Code
Purpose
.read create_tables.sql
Reads the SQL queries in the create_tables.sql file and runs them.
.read import_data.sql
Reads the SQL queries in the import_data.sql file and runs them.
.mode qbox
Prints the output of all queries in a table-like format in the Console, making it easier to read.

Code-along 0

It looks like a line of code is missing from import_data.sql! Let’s take a look at the code in that file:
-- Import data from CSV
.mode csv
.import --skip 1 data/artists.csv artists
.import --skip 1 data/albums.csv albums
.import --skip 1 data/songs.csv songs
.import --skip 1 data/listeners.csv listeners
.import --skip 1 data/plays.csv plays
It looks like we have:
  • A comment describing what these commands do. (-- Import data from CSV)
  • .mode csv, which sets the import/export mode to CSV
  • .import --skip 1 data/artists.csv artists (and a few other lines like this one)
    • Isn’t everything after .import a comment? It sure looks like one, but the syntax for dot commands is different than queries! Even though the Replit code editor colors this portion of the line as if it’s a comment, it is actually a list of arguments that specifies:
        1. To skip the first row of the CSV during import, since that’s the header row.
        1. To import the data from data/artists.csv
        1. To load the imported data into the table artists
    • Given these examples, can you write a line at the end of the file to import the data from data/plays.csv into the plays table?
Once you’re done:
  • Run the Replit to check that you don’t have any errors.
  • Delete the .print lines under Code-along 0 when you’re done. (Note: .print is a great way to print any information you want to print in the console!)
 
page icon
For a summary of this lesson, check out the 4. Lab: Music App Database One-Pager!
Â