In this post, the solution for the problem set of the ninth lecture of and more details about SQLite is summarized.

Database

Database is a collection of data organized for creating, reading, updating, and deleting. Why do we use database instead of Excel, Sheets, or Numbers? Database is very good for working with data at scale. Database works really well for the context when there are millions of rows of data. You could also use a database because it helps you access data very frequently. People often search for a product very, very frequently and you can query data very fast when you have a database at hand. Database work faster in general. They have structures built into them that allow you to find data quickly.

Database Management System

DBMS is a software via which you can interact with database. A database is anything that allows you to store data. DBMS allows you to access that database in certain ways with certain paradigms.

Design principles

When it comes to organizing databases, there’s one most important principle you can keep in mind, which is to have tables that represent one and only one thing. If you trying to store data with books and the authors of them, the best way is to make Books table(book_id, title), Authors table(authour_id, name), and any name of junction table(author_id, book_id). Technical terms to call tables like the last one are associative entity, junction table, or a join table. The idea of this table is associating certain 2 foreign keys. But why don’t we make only Books table(book_id, title, author_id) and Authors table(author_id, name)? It works same in one-to-one relationship case. But if it’s one-to-many or many-to-many relationships you have to duplicate the name of book_title and it’s not a good design. Below is 3 design principles you should care of.

• Create one table for each entity in your dataset.
• All tables should have a primary key.
• The information in the table should depend on the primary key only.

Create

Now let’s create database corresponding the example of books and authors above.

$ sqlite3 reads.db
Are you sure you want to create reads.db? [y/N] y
sqlite> CREATE TABLE books (
   ...>     id INTEGER,
   ...>     title TEXT,
   ...>     published NUMERIC,
   ...>     rating INTEGER,
   ...>     PRIMARY KEY(id)
   ...> );
sqlite>  CREATE TABLE authors (
   ...>     id INTEGER,
   ...>     author TEXT,
   ...>     author_birth INTEGER,
   ...>     PRIMARY KEY(id)
   ...> );
sqlite>  CREATE TABLE authored (
   ...>     author_id INTEGER,
   ...>     book_id INTEGER,
   ...>     PRIMARY KEY(author_id, book_id),
   ...>     FOREIGH KEY(author_id) REFERENCES authors(id),
   ...>     FOREIGN KEY(book_id) REFERENCES books(id)
   ...> );

For the last ‘authored’ table, it seems like we don’t have a primary key. But maybe the combinations of these 2 columns - author_id, book_id - will always be unique. So what I could do is have a primary key composed of these 2 foreign keys. This is a design choice. I could rather have a separate coulumn of ID and set as primary key.

Aggregate function

An aggregate function takes in lots of data and spits out one particular number. You can take a whole column of data and give you one summary number.

COUNT(column)
AVG(column)
MIN(column)

sqlite> SELECT ROUND(AVG(energy), 2) FROM songs;

Combining Tables

In general, there are 2 ways to combine tables and gather data from multiple sources.

Subqueries

Let’s say we want to find out the rating of Car 3. We can do this as below.

sqlite> SELECT rating FROM ratings WHERE movid_id =
   ...> (SELECT id FROM movies WHHERE title = 'Cars 3');

Joins

sqlite> SELECT rating
   ...> FROM ratings
   ...> JOIN movies ON movies.id = ratings.movie_id
   ...> WHERE movies.title = 'Car 3';

Problem Set

song

Below is the .schema of songs.db and you have to write 8 queries. For the sake of simplicity in this problem, we haven’t defined the primary and foreign keys. Although if you were working on your own you probably should define these particular features. Notice if you write query in 1.sql and type .read 1.sql inside of sqlite, it will run that sql statements.

sqlite> .read 1.sql

Answer

1.

SELECT name FROM songs;

2.

SELECT name
FROM songs
ORDER BY tempo;

3.

SELECT name
FROM songs
ORDER BY duration_ms DESC
LIMIT 5;

4.

SELECT name
FROM songs
WHERE danceability > 0.75
AND energy > 0.75
AND valence > 0.75;

5.

SELECT AVG(energy)
FROM songs;

6.

SELECT name FROM songs WHERE id IN
(SELECT id FROM artists WHERE name = "Post Malone");

7.

SELECT AVG(energy)
FROM songs
JOIN artists ON artists.id = songs.artist_id
WHERE artists.name = 'Drake';

8.

SELECT name
FROM songs
WHERE name LIKE '%feat.%';

[answer.txt] Average of danceability, energy, and valence is 0.71646, 0.65906, 0.484443 respectively. I would characterize their audio aura as Purple. Taking average would cause a problem. Listening to 2 musics with 1.0 energy and 0.0 energy will affect as 0.5 average energy value. Instead, we have to classify the levels of energy and select the mode level to help our decision.