03/13/2022 UPDATE

Got through chapter 10, just forgot to keep track and update the DK30 project. Anyway, I’d consider this project completed.

02/14/2022 UPDATE

So, I’ve been slacking for a week and haven’t actually worked on the project since my last update. Whoops!

Today, however, I buckled down and started working in the textbook. I got through chapter 5, essentially getting through my week three goals. I’m going to re-work my DK30 time table since I’m working through chapters faster than expected.

Some things I’ve learned:

• The BETWEEN operator is inclusive (“BETWEEN 1 AND 3” includes results of 1 and 3)
• Learned of the IN operator which allows you to select a set of many discreet values
• Got a better hold on when subqueries are applicable and how they can be used
• I knew about the % wild card, but today learned about the _ wild card which acts as a single character wild card.
• You can join a table to itself, and there are valid reasons for doing so (this blew my mind a bit)

Example using what I reviewed and learned today - let’s say I want to know what band(s) had a release or multiple releases in 2019. I would need to join some tables to get that data, and I only want two columns returned: Band Name and Album Release Year. Here’s the query I came up with to get that info (it took a few tries before I realized I needed single quotes around date strings… you know, since they are strings.)

SELECT album.release_date, band.name
FROM album
INNER JOIN band
ON album.band_id = band.id
WHERE album.release_date BETWEEN '2019-01-01' AND '2019-12-31';

View post on imgur.com

Two bands had releases in 2019, it seems! Very cool, I’m proud of what I accomplished today.

Approx hours practicing querying: 2 Approx hours building database: 30.25 Total approx hours invested: 32.25

02/07/2022 UPDATE

The ‘track’ table is complete after listening to at least a portion of each of the 217 tracks in the collection and associating a mood with them. This means that phase one of the project is complete! The database is finished unless more albums come my way, and I can move on to working in my “Learning SQL” book with all of this data.

Approx. total hours invested thus far: 30.25

02/04/2022 UPDATE

I began working on the ‘track’ table, and it’s going surprisingly fast considering I need to listen to pretty much every track of every album I own to determine what mood fits the track. I’m in the middle of album 6 out of 20 which took approximately 3 hours to get through. Of course, quite a few tracks I only listened to a portion of to get a general feel of the mood, so that shaved some time off.

I know the schemas are in earlier project updates, but here’s a screenshot of the table anyway to give an idea of what exactly I’m entering.

View post on imgur.com

Anyway, I feel I’m making good progress. I believe this is the last table that needs data entered before I’m ready to begin rocking (pun intended) the actual querying and textbook exercises!

Approx. total hours invested thus far: 24.25

02/03/2022 UPDATE

I spent three hours and fifteen minutes on data entry for the band_website table today. It was boring, but I was able to complete the table’s dataset. I’m now absolutely dreading the ‘track’ table’s data entry work, especially with it being connected to the ‘mood’ table. I’m almost debating dropping that part of the schema so I don’t have to listen to each track from every album in my collection and associate a mood with it. That’s going to be a time investment.

I made a quick GIF of querying band_website just to kind of show its structure and contents.

View post on imgur.com

Approx. total hours invested thus far: 21.25

02/02/2022 UPDATE

Took a break from the project today because watching Sean play KKND was simply too entertaining.

I decided to track the overall time invested in the project, however. I figure it will be interesting to see how that relates to my progress.

Approx. total hours invested thus far: 18

02/01/2022 UPDATE

I put in about two and a half hours today as opposed to the 7 to 8 hours I put in each day for the previous two days. I simply haven’t been giving myself enough time to sleep considering when I wake up for work!

I still achieved a decent amount of data entry into some tables, however. No schema changes today.

I manually collected and entered data into the following tables today:

• language (finished)
• site_description (tentatively finished unless I think of more types of websites to add)
• band_website (partially filled; three bands worth of URLs entered)

I wish I could continue today. I’ve got the motivation to work on this, so it’s unfortunate that I’m so tired, but I’d rather call it a short day than make mistakes from being tired.

01/31/2022 Update

Lots of changes made today to the schema, and I began the process of manually filling in data within the physical database:

• Added a ‘band_website’ table that will hold any relevant links relating to the band (band site, bandcamp, streaming service links, merch pages, etc)
• Added a ‘site_description’ table to contain website descriptions for the ‘band_website’ table. These will be very short, generic things such as “Merch”, “Main Site”, “Bandcamp”, etc.
• Added a ‘continent’ table to further normalize the schema
• Moved ‘genre’ relations with respect to ‘subgenre’ and ‘album’ tables to make more sense
• Added a province field to the ‘city’ table as too many cities have the same name in each country (seriously, where’s the originality??)
• Changed formation_date in the ‘band’ table to a year data type and renamed accordingly since exact dates of band formation are usually not available or are nebulous
• Created all tables in the database to match the schema
• Filled in data manually to tables - the following are considered complete unless something changes in terms of structure:
  • country
  • city
  • album
  • band
  • label
  • genre
  • subgenre

A very productive night, I’d say. Although, I did end up spending two frustrating hours on the process of importing a 41,000 row CSV containing city data into the city table. Turns out, you have to remember that CSV stands for Comma Separated Value, so any commas that are in the fields of Excel will screw up column alignment for the import!

Updated schema for today:

View post on imgur.com

More schema updates:

• Broke out the ‘area’ table into two tables: ‘country’ and ‘city’ since having every city listed per country can’t be considered normalized
• Added ‘description’ column to the mood table (not pictured in screenshot)

View post on imgur.com

Made some minor updates with the help of a buddy of mine:

• Removed ‘number_tracks’ from album, as this can be calculated directly from the track table itself
• Removed ‘runtime’ from album, as this can also be calculated by adding track runtimes
• Added ‘mood’ table and created its foreign key in the ‘track’ table so I can query for songs based on mood and return moods associated with entire albums based on track moods

View post on imgur.com

I’ve created the first iteration of the music database’s schema. I’m self-learned, so if anyone looks at this and sees something incorrect, please reach out to me! Every mistake is a chance to learn.

Album Database Schema