Exercise Monday

Goal: Practice database commands and design with Ruby classes and methods. There is plenty of new information to absorb so we'll start with some practice exercises working with JUST databases. We'll then add Ruby to build specs and classes for the application we built in the lessons. Most important is to practice foundational database concepts — how data is stored, how data tables relate to each other and how data is manipulated in PSQL and Ruby.

Warm Up

  • Why is a primary key important for database records?
  • What is the difference between a primary key and a foreign key?
  • How can we make two tables in a database related using a foreign key?


SQL Statements Practice

Start by practicing SQL command writing. Here are some guidelines for directed practice. Make sure each partner has the opportunity to write each kind of command at least once.

  • Generate a database with two tables. One example might be a table for games, one for sports, and one for hobbies.
  • Add columns to your tables. Examples include name, level of difficulty, average duration, number of people required and type.
  • Focus on one table: drop one of the columns you created.
  • Add primary keys to each table you created.
  • Insert information into your tables (3-4 rows each).
  • Run some SELECTs on the tables that you created.
  • Run some selects with different operators (such as WHERE and LIKE) on your tables.
  • Update some of the data in your tables.
  • Delete some records.
  • Drop the tables you made.
  • Drop the database.

Record Store Continued

Follow along with the homework to add a database to the record store we built in the last course section.

Optional: Once you're done, try experimenting with Ruby methods that use SQL to do the following:

  • Alphabetize results from the database.
  • Add a release_year field along with a method to sort by newest album.
  • Add a cost field along with methods to return the cheapest/most expensive album.
  • Select a random album from the database.

Section 2 Project Update

Choose one of the following projects and update it to use database persistence:

  • Music Festival
  • Maze
  • Message Board
  • Tamagotchi

You may also choose another section 2 project if you prefer. Once basic database functionality has been added for CRUD, consider ways the project can be built out further for persistence. Here are some possible examples:

  • Add a save point to Maze or Tamagotchi.
  • Add an option for users to leave replies in Message Board.
  • Add timestamps to messages on Message Board.
  • Add a time field to Music Festival along with methods to return when various musicians are performing.

Peer Code Review

  • Are standard naming conventions in place for database tables and columns?
  • Is CRUD functionality successfully and correctly executed in methods and routes?
  • Is the code well-refactored and easy-to-follow?
  • Does the application work as expected?

Lesson 13 of 29
Last updated August 7, 2022