The problem

From labs/8-weather.rb in your copy of ruby-intro:

Given the following Ruby hash that represents weather data, write a weather summary out to the screen. Something like this:

Currently it is 67 degrees and Sunny.
Forecast for the next 7 days:
- 65 degrees and Mostly Cloudy
- 70 degrees and Partly Cloudy
- 62 degrees and Sunny
- 68 degrees and Sunny
- 65 degrees and Partly Cloudy with a 70% chance of rain
- 72 degrees and Partly Cloudy
- 60 degrees and Rainy

Note: only display the chance of rain if above 50%.
Note: do not display the chance of rain if conditions are already rainy.

Run it from the top folder of the repo:

bin/rails runner labs/8-weather.rb

This one uses everything: a hash, an array inside it, a loop over the array, and a conditional inside the loop.

Steps

1. Look at the weather_data hash.
2. Find the current conditions and display the first line.
3. Find the array of forecast data.
4. Loop through it, displaying one line per day.
5. Inside the loop, decide whether that day mentions the chance of rain.

1. Look at the hash, and notice the keys

weather_data = {
  current: {
    temperature: 67,
    conditions: "Sunny"
  },
  forecast: [
    { temperature: 65, conditions: "Mostly Cloudy", precipitation: 0.4 },
    ...
    { temperature: 60, conditions: "Rainy", precipitation: 0.9 }
  ]
}

The top level has two keys, current and forecast. current is a hash. forecast is an array of hashes, one per day: a table, with a row for each day.

The keys are written differently from the hashes we've built so far. current: { ... } is short for :current => { ... }. The key is a symbol, a word that starts with a colon, not a string. The hashes code-along mentioned this at the end: two ways to write a hash. It matters here because a symbol key and a string key are different keys:

puts weather_data["current"]   # nothing: there is no "current" key
puts weather_data[:current]    # {temperature: 67, conditions: "Sunny"}

If you get nil, or an error like undefined method '[]' for nil, this is the first thing to check. Use the key the way the hash was written.

2. The current conditions

Start with the sample line exactly as written, then swap the parts that should change:

puts "Currently it is 67 degrees and Sunny."

current = weather_data[:current]
puts "Currently it is #{current[:temperature]} degrees and #{current[:conditions]}."

3 and 4. Loop through the forecast

We could pull out each day by its index, weather_data[:forecast][0], then [1], and so on. But that's seven nearly identical lines, and it breaks the day the forecast has eight days. A loop does it once, for however many days there are:

puts "Forecast for the next #{weather_data[:forecast].size} days:"
for day in weather_data[:forecast]
  puts "- #{day[:temperature]} degrees and #{day[:conditions]}"
end

Each time around, day is one hash from the array. .size counts the days, so the heading is right even if the forecast changes.

5. The chance of rain

Two notes in the problem, two conditions, and both have to be true:

• the chance of rain is above 50%: day[:precipitation] > 0.5
• it isn't already rainy: day[:conditions] != "Rainy"

&& means both. != means "is not equal to."

  if day[:precipitation] > 0.5 && day[:conditions] != "Rainy"
    chance_of_rain = (day[:precipitation] * 100).round
    puts "- #{day[:temperature]} degrees and #{day[:conditions]} with a #{chance_of_rain}% chance of rain"
  else
    puts "- #{day[:temperature]} degrees and #{day[:conditions]}"
  end

precipitation is stored as a fraction, 0.7, so we multiply by 100 for a percentage. 0.7 * 100 is 70.0, a Float, which would print as "70.0%"; .round makes it a whole number.

Check the output against the notes. Day 5 (0.7, Partly Cloudy) mentions rain. Day 7 (0.9) has a higher chance, but it's already Rainy, so it doesn't. Day 1 (0.4) is under 50%.

Final code

# 1. inspect the weather_data hash
# puts weather_data

# 2. the current conditions; the keys are symbols, so :current, not "current"
current = weather_data[:current]
puts "Currently it is #{current[:temperature]} degrees and #{current[:conditions]}."

# 3. the forecast is an array of hashes, one per day
puts "Forecast for the next #{weather_data[:forecast].size} days:"

# 4. loop through it, one line per day
for day in weather_data[:forecast]

  # 5. mention the chance of rain only if it's above 50% and it isn't already rainy
  if day[:precipitation] > 0.5 && day[:conditions] != "Rainy"
    chance_of_rain = (day[:precipitation] * 100).round
    puts "- #{day[:temperature]} degrees and #{day[:conditions]} with a #{chance_of_rain}% chance of rain"
  else
    puts "- #{day[:temperature]} degrees and #{day[:conditions]}"
  end
end

Output:

Currently it is 67 degrees and Sunny.
Forecast for the next 7 days:
- 65 degrees and Mostly Cloudy
- 70 degrees and Partly Cloudy
- 62 degrees and Sunny
- 68 degrees and Sunny
- 65 degrees and Partly Cloudy with a 70% chance of rain
- 72 degrees and Partly Cloudy
- 60 degrees and Rainy

Challenge

Add a line under the forecast that says which day will be the warmest, something like "The warmest day will be day 6, at 72 degrees." You'll need a variable that remembers the highest temperature so far, set before the loop and updated inside it.

The problem

From labs/7-replay.rb in your copy of ruby-intro:

The game in 6-craps.rb is random, so running it a few times proves very little. Here is the same game with one change: instead of rolling dice, it reads each total from a list of rolls written down ahead of time. Same rolls in, same result out, every time. That can be checked.

Run it from the top folder of the repo:

bin/rails runner labs/7-replay.rb

The lab comes in four steps, and the order matters: the check is written, and seen to fail, before the game goes in.

1. Decide what right looks like

The file starts with one game: a list of rolls, and the result you expect.

games = [
  { "rolls" => [7], "expected" => "YOU WIN!" }
]

It's an array of hashes, like the teams table: one hash per game. Your job is to add games, and to decide each expected result from the rules, before you run anything. Which lists of rolls would test every rule?

games = [
  { "rolls" => [7], "expected" => "YOU WIN!" },
  { "rolls" => [12], "expected" => "YOU LOSE!" },
  { "rolls" => [8, 4, 8], "expected" => "YOU WIN!" },
  { "rolls" => [8, 7], "expected" => "YOU LOSE!" },
  { "rolls" => [8, 11, 8], "expected" => "YOU WIN!" },
  { "rolls" => [8, 2, 8], "expected" => "YOU WIN!" }
]

Each game is there for a rule:

The last two are the ones random play almost never shows you, and the ones a chat most often gets wrong. You could add [11], [2] and [3] too; each is a rule of its own.

2 and 3. Write the check, and watch it fail

The file loops through the games with for game in games, and starts each one with result = "not played yet". Before the game goes in, write the check: the result we got against the result we expected.

  if result == game["expected"]
    puts "PASS: #{rolls} #{result}"
  else
    raise "FAIL: #{rolls} expected #{game["expected"]}, got #{result}"
  end

raise stops the program with an error, and the error says what you told it to say. Run the file now, with no game in it:

FAIL: [7] expected YOU WIN!, got not played yet (RuntimeError)

That's the point of this step. Nothing has played, so the check should fail, and it does. A check you have never seen fail might not be checking anything.

When you see an error, read its first line: that's the message. The lines under it, starting with from, show where the program was when it stopped. Most of them are Rails, not you.

4. Put the game in

Paste the game from 6-craps.rb above the check and change one thing: where each total comes from. Instead of rand, read the next number from rolls. An index that starts at 0 keeps track of which roll is next:

  total = rolls[index]
  index = index + 1

And instead of puts "YOU WIN!", save it: result = "YOU WIN!", so the check can compare it. A chat can make this change for you:

Here is my craps game in plain Ruby:

[paste your game from labs/6-craps.rb here]

Change one thing: instead of rolling dice, each total should come from a list called rolls, read in order with an index that starts at 0. Don't print anything. Save "YOU WIN!" or "YOU LOSE!" in a variable called result. If the list runs out before the game ends, raise an error that says so, so it can't loop forever.

Keep everything else the same: the same rules, plain Ruby, no methods, no classes. Give me just the code, to paste inside a loop that already sets rolls.

That "runs out" line is worth having. [8, 4] sets a point and never ends the game. With dice, you'd just roll again. With a list, rolls[2] is nil, which is neither the point nor a 7, so the loop would go around forever. Checking for nil turns that into an error you can read:

FAIL: [8, 4] ran out of rolls before the game ended (RuntimeError)

Run the file again. Six games, six PASS.

Final code

# 1. Games to replay: the rolls, and the result you expect.
games = [
  { "rolls" => [7], "expected" => "YOU WIN!" },
  { "rolls" => [12], "expected" => "YOU LOSE!" },
  { "rolls" => [8, 4, 8], "expected" => "YOU WIN!" },
  { "rolls" => [8, 7], "expected" => "YOU LOSE!" },
  { "rolls" => [8, 11, 8], "expected" => "YOU WIN!" },
  { "rolls" => [8, 2, 8], "expected" => "YOU WIN!" }
]

# 2. Replay each game
for game in games
  rolls = game["rolls"]
  result = "not played yet"
  index = 0

  # 4. The game, as in 6-craps.rb, except where each total comes from
  total = rolls[index]
  index = index + 1

  if total == 7 || total == 11
    result = "YOU WIN!"
  elsif total == 2 || total == 3 || total == 12
    result = "YOU LOSE!"
  else
    point = total

    loop do
      total = rolls[index]
      index = index + 1

      # a game that runs out of rolls would otherwise loop forever
      if total == nil
        raise "FAIL: #{rolls} ran out of rolls before the game ended"
      end

      if total == point
        result = "YOU WIN!"
        break
      elsif total == 7
        result = "YOU LOSE!"
        break
      end
    end
  end

  # 3. Check: the result we got against the result we expected.
  #    Written and run before the game was in, so we saw it fail first.
  if result == game["expected"]
    puts "PASS: #{rolls} #{result}"
  else
    raise "FAIL: #{rolls} expected #{game["expected"]}, got #{result}"
  end
end

Output:

PASS: [7] YOU WIN!
PASS: [12] YOU LOSE!
PASS: [8, 4, 8] YOU WIN!
PASS: [8, 7] YOU LOSE!
PASS: [8, 11, 8] YOU WIN!
PASS: [8, 2, 8] YOU WIN!

Run it again. And again. The same six lines every time. That's what makes it checkable.

Try breaking it

Two experiments, to see why step 3 came first.

Break the game. Where the point comes up, change result = "YOU WIN!" to result = "YOU LOSE!". Run it:

PASS: [7] YOU WIN!
PASS: [12] YOU LOSE!
FAIL: [8, 4, 8] expected YOU WIN!, got YOU LOSE! (RuntimeError)

The check caught it, and told you which game and what it got. Put it back.

Break the check. In the check, change == to =, so the line reads
if result = game["expected"]. Run it: six PASS. Now break the game again the same way. Still six PASS.

One = doesn't compare; it sets result to the expected value, and then the PASS line prints that. The game is wrong and every line says it's fine. If this check had been written after the game, and passed the first time, you'd have no reason to doubt it. Because you watched it fail when nothing had played, you know it can.

If a check fails and you're sure the game is right, look at the expected result too. Deciding what right looks like is part of the job, and it can be wrong.

The problem

From labs/4-hashes.rb in your copy of ruby-intro:

Start with an amount of Bitcoin that someone owns. Using the Ruby hash bitcoin_data, display a summary of Bitcoin data for the user. Something similar to the sample output.

Sample output:
1 Bitcoin is valued at $41405.1046 USD.
Your Bitcoin is worth $62107.6569.

Run it from the top folder of the repo (where README.md is):

bin/rails runner labs/4-hashes.rb

The code you don't change

The top of the file asks a live service for the current price of Bitcoin:

# DON'T CHANGE THIS CODE
# ----------------------
require "net/http"
require "json"
url = "https://blockchain.info/ticker"
uri = URI(url)
response = Net::HTTP.get(uri)
bitcoin_data = JSON.parse(response)
# ----------------------

We'll learn more about APIs later. For now: an API is how another piece of software shares its data with your code. A person would visit a website to see the price of Bitcoin; code asks the API instead. These lines go to that URL, get back text, turn the text into a Ruby hash, and save it in bitcoin_data. Everything after this is a hash, which you already know how to work with.

1 and 2. How much Bitcoin, as a number

The file starts you off:

bitcoin = "1.5"
bitcoin = bitcoin.to_f

The amount is in quotes on purpose. Anything a person types into a form or a terminal reaches your code as a string, even when it looks like a number. A string can't be multiplied, so .to_f turns it into a Float (a number with a decimal point). Change "1.5" to any amount you like.

3. Look at the hash before you dig into it

puts bitcoin_data

You'll see something like this (shortened, and your numbers will be today's):

{
  ...
  "EUR" => { "15m" => 74258.37, "last" => 74258.37, "buy" => 74258.37, "sell" => 74258.37, "symbol" => "EUR" },
  ...
  "USD" => { "15m" => 83165.11, "last" => 83165.11, "buy" => 83165.11, "sell" => 83165.11, "symbol" => "USD" }
}

It's a hash (the curly braces), with one key per currency. The value of each key is another hash. Look at just the one we want:

puts bitcoin_data["USD"]

There are a few prices in there. "last" is the most recent one, and it has no quotes around it, so it's already a number we can do math with.

4. Dig out the price, do the math, display it

usd_rate = bitcoin_data["USD"]["last"]
puts usd_rate

It never hurts to puts a value as a sanity check before you use it. Then the math:

usd_value = bitcoin * usd_rate

For the output, start with the sample text exactly as written, then swap the parts that should change for your variables:

puts "1 Bitcoin is valued at $41405.1046 USD."
puts "Your Bitcoin is worth $62107.6569."

becomes

puts "1 Bitcoin is valued at $#{usd_rate} USD."
puts "Your Bitcoin is worth $#{usd_value.round(2)}."

.round(2) keeps two decimal places, the way money is usually shown. Without it you may see a long tail of digits.

Final code

# 1. How much Bitcoin, as a string, the way typed input arrives
bitcoin = "1.5"

# 2. A string can't be multiplied: make it a Float
bitcoin = bitcoin.to_f

# 3. Inspect the hash: one key per currency, each a hash of its own
# puts bitcoin_data
# puts bitcoin_data["USD"]

# 4. Dig out the price, then do the math and display it
usd_rate = bitcoin_data["USD"]["last"]
usd_value = bitcoin * usd_rate

puts "1 Bitcoin is valued at $#{usd_rate} USD."
puts "Your Bitcoin is worth $#{usd_value.round(2)}."

Output (with today's price):

1 Bitcoin is valued at $83165.11 USD.
Your Bitcoin is worth $124747.67.

Challenge

Could you take this a step further? Add a variable for the currency, currency = "EUR", and show the price and the value in that currency instead. If the currency isn't one the API knows (try "XYZ"), show a message instead of an error.

A hint: when you ask a hash for a key it doesn't have, you get nil back.

currency = "EUR"
currency_data = bitcoin_data[currency]

if currency_data == nil
  puts "Sorry, #{currency} isn't a currency we have a price for."
else
  rate = currency_data["last"]
  value = bitcoin * rate
  puts "1 Bitcoin is valued at #{rate} #{currency}."
  puts "Your Bitcoin is worth #{value.round(2)} #{currency}."
end

The problem

From labs/6-craps.rb in your copy of ruby-intro:

Craps: the whole game. The rules:

• The first roll: 7 or 11 wins. 2, 3 or 12 loses. Anything else becomes the point.
• Once there is a point, keep rolling until:
  • the point comes up again: the player wins
  • a 7 comes up: the player loses
• After the point is set, nothing else matters. Any other total means roll again.

Show each roll ("Roll 1: 8"), the point once it's set, then "YOU WIN!" or "YOU LOSE!".

Run it from the top folder of the repo:

bin/rails runner labs/6-craps.rb

The first roll is already written. It's lab 2 with each roll numbered:

roll_number = 1
total = rand(1..6) + rand(1..6)
puts "Roll #{roll_number}: #{total}"

if total == 7 || total == 11
  puts "YOU WIN!"
elsif total == 2 || total == 3 || total == 12
  puts "YOU LOSE!"
else
  point = total
  puts "THE POINT IS #{point}"

  # what happens after the point is set goes here

end

The only part left is the else branch, after the point is set. That's the new idea: until now every program ran top to bottom once. This one has to keep going until something happens, and you can't know in advance how many rolls that will take.

1. Pseudo-code first

Before any code, write what happens after the point in plain English, one step per line, as comments:

# 1. roll again, and show the total
# 2. if it's the point, the player wins: stop
# 3. if it's a 7, the player loses: stop
# 4. anything else: nothing happens, go back to step 1

Check yours against the rules. The gaps that show up most often:

• No "keep rolling." One more roll, then done. The rules say roll until.
• No way to stop. Rolling forever is the opposite mistake.
• Nothing about the other totals. What happens to an 11 after the point? A 2? The rules say nothing: they mean roll again. A first-roll rule (11 wins, 2 loses) creeping into the point phase is the most common bug in this lab, written by people and by chats alike.

2. Ask a chat for the code

Give a chat the rules, your pseudo-code and the file, and say exactly what you want back. Something like:

I'm writing a craps game in plain Ruby. The rules:
- First roll: 7 or 11 wins. 2, 3 or 12 loses. Anything else becomes the point.
- Once there is a point, keep rolling until the point comes up again (the player wins) or a 7 comes up (the player loses).
- After the point is set, nothing else matters. Any other total, including 2, 3, 11 or 12, means roll again.

My pseudo-code for what happens after the point:
# 1. roll again, and show the total
# 2. if it's the point, the player wins: stop
# 3. if it's a 7, the player loses: stop
# 4. anything else: nothing happens, go back to step 1

My file is below. The first roll is already written. Fill in only the else branch, under my pseudo-code, with the code for each step under its comment.

Use only what the file already uses: variables, if/elsif/else, loop do ... end with break, and puts. No methods, no classes, no gets. Print each roll the way the first roll does ("Roll 2: 9"), then "YOU WIN!" or "YOU LOSE!". Give me just the code for the else branch.

[paste labs/6-craps.rb here]

The last paragraph matters. Without it, a chat will often hand back a method, a class, or code that waits for you to press Enter between rolls (gets). None of that is wrong, but it's not what you asked for, and it's harder to read.

3. The loop

Each line of pseudo-code becomes a line or two of code under its comment.

loop do ... end repeats everything inside it, forever, until it reaches break. So the two ways the game ends are the two places we break:

  loop do
    # 1. roll again, and show the total
    roll_number = roll_number + 1
    total = rand(1..6) + rand(1..6)
    puts "Roll #{roll_number}: #{total}"

    # 2. if it's the point, the player wins: stop
    if total == point
      puts "YOU WIN!"
      break
    # 3. if it's a 7, the player loses: stop
    elsif total == 7
      puts "YOU LOSE!"
      break
    end
    # 4. anything else: nothing happens, go back to step 1
  end

A few things to notice:

• roll_number = roll_number + 1 comes before the roll, so the second roll prints as "Roll 2". roll_number was set to 1 above, outside the loop, so it keeps counting up.
• total is reused. We don't need the first roll's total any more; point remembers it.
• Step 4 has no code. If the total is neither the point nor a 7, nothing in the if runs, we reach the bottom of the loop, and it goes around again. That's how an 11 or a 2 after the point "does nothing." Saying it in the pseudo-code is still worth it: it's the step that keeps the first-roll rules out.

If you run it and it never stops, press Ctrl+C. Most likely a break is missing.

Final code

roll_number = 1
total = rand(1..6) + rand(1..6)
puts "Roll #{roll_number}: #{total}"

if total == 7 || total == 11
  puts "YOU WIN!"
elsif total == 2 || total == 3 || total == 12
  puts "YOU LOSE!"
else
  point = total
  puts "THE POINT IS #{point}"

  loop do
    # 1. roll again, and show the total
    roll_number = roll_number + 1
    total = rand(1..6) + rand(1..6)
    puts "Roll #{roll_number}: #{total}"

    # 2. if it's the point, the player wins: stop
    if total == point
      puts "YOU WIN!"
      break
    # 3. if it's a 7, the player loses: stop
    elsif total == 7
      puts "YOU LOSE!"
      break
    end
    # 4. anything else: nothing happens, go back to step 1
  end
end

One run:

Roll 1: 9
THE POINT IS 9
Roll 2: 8
Roll 3: 6
Roll 4: 9
YOU WIN!

Your chat may have written it differently: a while loop, two loops, a method. Same rules, different code. If it follows the rules, it's right.

So, is it right?

How do you know? Most of us ran it a few times, and it seemed to work. But each run, the dice decided which case you got to see. Did you see a point followed by an 11? A point followed by a 2? Running a random program again isn't checking it. Lab 7 takes the dice out so the game can be checked.

In Week 3 you drew the CRM's data model on paper. This is how that drawing becomes real tables: a chat writes the CREATE TABLE statements, you load them, and you check the result against your drawing. It is the same loop as Assignment 1 phase 1, step 4, on a smaller model.

The repository

github.com/dsgn425-fall2026/crm. Click Use this template, then Create a new repository in your own account. In your copy: Code, then Codespaces, then Create codespace on main. The first start takes about three minutes.

Its README has the stories, the steps, and what every file is for. The short version follows.

The steps

1. In the terminal, run bin/rails server and open the port it offers. Then open a second terminal with the + on the terminal panel; the server keeps the first one busy.

2. Give a chat a photo of your ERD (or the stories in the README) and ask for SQLite CREATE TABLE statements. Paste them into schema/crm.sql.

3. In the second terminal:

   bin/load
   

   It rebuilds the database from nothing, from schema/crm.sql, and lists the tables it made. If a statement has an error, it stops, names the line, and leaves the database as it was.

4. Open the Schema page. Is each table in your drawing there, with the columns you drew? Does each foreign key point where you meant it to? Where the chat differs from your drawing, decide who is right. In Assignment 1 the chat reviews your drawing before it writes any SQL, and NOTES.md records what it would change and what you took.

solutions/crm.sql has one possible answer. Compare it once you have your own.

Why the terminal, not the Run SQL box

Changing a database's structure (CREATE TABLE, ALTER TABLE, DROP TABLE) never happens inside an app. The Run SQL box is for reading data. An occasional INSERT, UPDATE or DELETE is closer to what apps do every day, but tables are built from a file, through the terminal. Paste a CREATE TABLE into the box and it refuses, and tells you where it goes instead.

It is the same idea as the database user roles from Week 2: read data, write data, change the structure. Most people need only the first. If your job is analyzing data, ask for a read-only login. Not because anyone thinks you can't be trusted; so that a slip can't break anything.

Assignment 1 works the same way: kmdb is the same app, with schema/kmdb.sql, data/kmdb.sql, and bin/load.

In class, you asked a baseball database which team has lost the most games in history, and it couldn't really answer. It could give you the worst single season (the 1899 Cleveland Spiders, 20-134) or the most losses added up by team name (the Philadelphia Phillies, 10,426). But a fan asking that question means the franchise, and there is no franchise anywhere in the database. Cleveland alone shows up under seven different names.

No amount of clever SQL fixes that. The problem isn't the query; it's the design of the tables. Deciding what tables a system should have, and how they relate to each other, is called domain modeling, and it's the subject of this post.

Models

A domain is the slice of the real world your software is about: a school, an airline, a store, a baseball league. A domain model represents that world in software, using the words the people in it actually use. An airline and a retailer end up with different models because they talk about different things.

We'll use a school as our example. We call each real-world thing in the domain a model, and all of the models, together with the relationships between them, make up the domain model.

What are the real, tangible things in a school? Two obvious ones:

• Student
• Teacher

Students and teachers aren't theoretical. They're people who exist outside the software. Students attend the school; teachers teach its courses.

You might ask: why not a single model for Person? Students and teachers are both people. It's a fair question, and the answer is that models are distinguished by their attributes, the data we need to keep about them. In our school, teachers have a bio and students don't. Students have contact information (email and phone number) and teachers don't. So they're different models.

Where do those attributes come from? From what the product needs to do. If the product shows a teacher's bio on the course page, a teacher needs a bio. Next week, we'll write that down properly as user stories, and the attributes will fall out of them. For now:

• Student: first name, last name, email, phone number
• Teacher: first name, last name, bio

Models become tables

If you've been following along with SQL, you've probably already guessed where this is going. A model and its attributes map neatly onto a database table:

• Each column is an attribute
• Each row is one instance of the model: one student, one teacher

Each model gets its own table, because each model has different attributes:

students

teachers

A good start. Next, a Course. A course isn't a physical thing you can hold, the way a student or teacher is, but it's real and it has data we need to keep: a name and a description.

courses

An attribute, or a new model?

You might be wondering whether that's really all the data a course needs. What about the times it meets, and who teaches it?

Knowing what belongs as an attribute on a model and what should be its own model is one of the keys to being good at domain modeling. So let's try putting the times and teachers on the course and see what happens:

courses

Seems reasonable, until Taco-Making gets popular and needs a third time. Then a fourth. Every new time slot means two new columns, and every course that has fewer slots is left with blank cells. The table is growing sideways.

That's the smell. A well-designed table grows down: new data means new rows, not new columns. When a table keeps needing more columns for "another one of these," that thing wants to be its own model.

One-to-many

Each time a course is offered, with its own teacher, is a Section. One course can have many sections:

sections

Much better. Courses now only hold what's true of the course itself, and sections hold the rest. A new section is just a new row, and a course can have none, one, or a hundred.

Section makes sense as its own model because we don't know how many there will be, and because it has attributes of its own (a time, a teacher) that might grow later.

This is a one-to-many relationship, and the way you build one is the foreign key you met in SQL 2: put a column on the "many" side that points at the "one" side. One course has many sections, so course_id goes on sections.

Many-to-many

Notice that sections has two foreign keys. That's because there are two one-to-many relationships here: a course has many sections, and a teacher has many sections.

Put those together and you get something new. A course can have many teachers, and a teacher can teach many courses. That's a many-to-many relationship, and it never lives directly on either table. It's always two one-to-manys through a model in the middle:

Two one-to-many relationships = one many-to-many relationship

The model in the middle is called a join model. Here, it's Section. Unlike Student or Teacher, Section isn't really a thing you'd point at in the physical world; it exists because of the relationship between two things that are.

Back to the franchise

Now the baseball question has an answer. The database was missing a model:

franchises

teams (one row per team, per season)

One franchise has many team-seasons, so franchise_id goes on teams, the "many" side. The team's name stays on the season, because names change over time; the franchise is what stays the same. "Which team has lost the most games in history?" becomes a SUM of losses grouped by franchise instead of by name.

It isn't free, though. Someone has to decide which seasons belong to which franchise, and in Cleveland's case not all seven names are the same club (the 1899 Spiders were a different team from the one that became the Indians). The data can't make that call for you. That's what domain modeling actually is: decisions about the real world, written down as tables.

Naming conventions

These are the conventions we've been using, and we'll keep using them for the rest of the course. None of them is technically required, but following them makes everything easier to read, and it'll matter when we get to Rails.

• Models are singular and capitalized: Course, Section.
• Table names are plural and lowercase: courses, sections.
• Column names are lowercase: name, description.
• Multi-word names use underscores: first_name, teacher_id.

Next week

We picked the attributes in this post by saying what the product needs to do. Next week, we'll do that properly: start from user stories, and let the domain model come out of them.

This one is optional. In class, COUNT, SUM, AVG, MIN, MAX and GROUP BY were reading vocabulary: you need to recognize them when a chat writes them, not write them yourself. If you want to know how they work, here they are. It picks up where SQL 1 left off, with the same 👶 baby tables.

Aggregate Functions

Often, the questions you want answered will involve math. That is, instead of simply seeing a subset of the rows in a table, we'll want to aggregate the results together in some way, e.g. adding the number of rows, summing a single column, seeing an average, etc.

How many reviews have been written?

• Identify the table that holds the information you need - Reviews
• Identify the columns that hold the information you need - not applicable in this case, since we're looking for an aggregate number
• Add any other conditions - none
• Then, ask the question:

SELECT COUNT(*) FROM Reviews;

There are a couple of crazy things going on here! Let's break it down.

First, instead of a column name like Name, Price, Body, etc. in our SELECT clause, we're calling a SQL function. There are many SQL functions built into SQL, but they will always contain a set of parentheses, which denote arguments to the function. In this case, we're using the COUNT function, which accepts an argument of "what columns are we counting?" Since we're simply looking for the aggregate number of rows in the table, the columns aren't relevant and we just use *.

And even though we're asking for an aggregate, that is, a single number representing the count of all the reviews, our result is still a 👶 baby table. It simply contains a single column -- called COUNT(*) -- and a single row. We can call several SQL functions, or a combination of SQL functions and column names, which will result in a 👶 baby table containing several columns*.*

Let's look at another aggregate function, where the argument does matter:

What is the average rating across all reviews?

• Identify the table that holds the information you need - Reviews
• Identify the columns that hold the information you need - an average Rating
• Add any other conditions - none
• Then, ask the question:

SELECT AVG(Rating) FROM Reviews;

Similar as before, with our COUNT function, but this time, the argument matters. We're using the AVG function, which takes a column name as an argument - which column are we averaging?

As mentioned, there are many functions built-in to SQL. The ones that are relevant to this lesson are aggregate functions - ones that do math to aggregate information. Examples of aggregate functions include:

• COUNT - counts the number of rows
• AVG - average of a column value or expression
• SUM - sum of a column value or expression
• MIN - minimum of a column value or expression
• MAX - maximum of a column value or expression

Grouping

Occasionally, a question like How many reviews have been written? will be enough to satisfy business requirements. However, it's more likely that your boss, client, or other stakeholder will want more specific information than simply aggregating the entire table's worth of data. We'll often want to categorize or aggregate (group) results by the value of another column. For example, a more common question might be:

How many reviews have been written per product?

• Identify the table that holds the information you need - Reviews
• Identify the columns that hold the information you need - the Product and the aggregate number of reviews
• Add any other conditions - none
• Then, ask the question:

SELECT Product, COUNT(*) 
FROM Reviews
GROUP BY Product;

We see that the values separated by commas in the SELECT clause, whether they are column names or SQL functions, end up being the columns in the 👶 baby table. And, because we used a GROUP BY clause, our results are aggregated (grouped) by the distinct values in the Product column.

This brings us to Rule #1A, the First Law of all SQL... when using a GROUP BY clause, the columns listed in the GROUP BY must also be in the SELECT clause. Makes sense, right? In this example, if the Product column wasn't also in the SELECT, the results would just be a list of numbers with no context:

SELECT COUNT(*) 
FROM Reviews
GROUP BY Product;

Not very useful, is it?

The rule in the other direction is the one that actually bites: every column in the SELECT must either be in the GROUP BY or be inside an aggregate function. Most databases, PostgreSQL included, refuse to run a query that breaks it. SQLite runs it anyway, and quietly fills in a value from one row of each group. In the baseball database:

SELECT year, name, MAX(wins)
FROM teams
WHERE year = 2002
GROUP BY year;

The Oakland Athletics also won 103 games in 2002. One row per group has room for one name, so SQLite picked one and didn't mention it. When a chat hands you SQL with a column like name sitting next to a MAX or a COUNT, check whether that column is in the GROUP BY. If it isn't, the name may not mean what it looks like it means.

Order of Clauses

SQL 1 ended with the order of clauses; here it is again with GROUP BY in its place. Because of the way SQL is executed, the order in which the clauses are written does matter. This may differ slightly based on the flavor/implementation of SQL being worked with (i.e. some are more forgiving than others), but generally speaking, the order should be as follows:

• SELECT - the columns or executed functions you want in the result
• FROM - the table(s) involved
• WHERE - the conditions or constraints
• GROUP BY - columns in the SELECT used to aggregate the results
• ORDER BY - columns by which the results shall be ordered, either numerically or alphabetically
• LIMIT - how many rows to return

To put it all together:

What is the average rating per product, taking into account only those reviews with a rating of 3 or above, from lowest to highest average rating?

• Identify the table that holds the information you need - Reviews
• Identify the columns that hold the information you need - the Product and the aggregate average rating of reviews
• Add any other conditions - rating of 3 or above
• Then, ask the question:

SELECT Product, AVG(Rating)
FROM Reviews
WHERE Rating >= 3
GROUP BY Product
ORDER BY AVG(Rating);

Try it: aggregation

In class, aggregates were reading vocabulary: you will see them in SQL a chat writes for you far more often than you will write them. Reading is easier once you have written one or two, so:

• How many teams played in 2020? One COUNT(*) and one WHERE. (30.)
• Which team name has been used for the most seasons? GROUP BY name, COUNT(*), sorted, and one row. Then ask for three rows, and see what one row was hiding.

JOINs

Sometimes the answer to a question is contained within more than one table. Consider our Products table:

And Reviews:

And the question we want answered is:

Can I see all the reviews for the products in the Furniture department?

The results – our 👶 baby table – and the WHERE clause would both need to contain columns from both the Products and Reviews tables. Let's follow our usual formula and see what happens:

• Identify the table(s) that contain the information you need – Reviews and Products
• Identify the columns the hold the information you need – the Product, Rating, and Body from Reviews, as well as Department from Products
• Add any other conditions – products in the Furniture department

Before being able to ask the question, though, we need to add one additional step to this recipe.

• Identify the thing the tables have in common – the Name from the Products table is equivalent to the Product column from the Reviews table

Now, we can ask the question:

SELECT Products.Department, Reviews.Product, Reviews.Rating, Reviews.Body
FROM Reviews INNER JOIN Products ON Products.Name = Reviews.Product
WHERE Products.Department = "Furniture";

Whew! There seems to be a lot more here than what we've seen thus far... but don't worry; it's actually quite straightforward. Let's break it down.

Because there are multiple tables involved, we're prefixing each column name with the name of the table and a dot . – e.g. because the Body column is in the Reviews table, we write it as Reviews.Body. This is prevent conflicts in naming – for instance, what if the Products and Reviews tables both had a Body column? This syntax clears that up.

And, the FROM clause is more complex now. Previously, it was just the name of the single table we were reading from. Now, we're reading from the intersection of the two tables. This intersection is called a JOIN, and the commonality between the two tables is specified in the ON clause. The intersection between the table happens when the Name from the Products table is equivalent to the Product column from the Reviews table, so it's ON Products.Name = Reviews.Product. Furthermore, if there was a third, fourth, fifth, etc. table involved, they would be chained together in the FROM clause with more INNER JOINs.

The Primary Key

A shocking confession: the examples I've provided thus far – the Products and Reviews tables from our fictional e-commerce store that sells a rather eclectic variety of stuff – hasn't been entirely realistic. The one thing that basically all tables in a relational database have, and that's missing from these tables, is some sort of unique identifier for each record. A primary key. If you've ever seen a database table outside of this course, you may have seen a variety of different types of primary keys. Based on the database design, sometimes you'll see something like a Social Security Number, or some other strictly-enforced integer value. Sometimes it's a long alphanumeric string based on a software algorithm. Or, a lot of the time, it's simply an integer that starts at 1 and goes up by 1, forever. The auto-incrementing integer primary key is fairly common for a freshly-built application nowadays, and that will be the rule for primary keys that we'll follow for the reminder of this course.

Have a look at the permanent URL for the tweet above – that URL never changes, and will always direct you to the page for that tweet, unless Mr. Culkin decides to delete it. What is the 1298730289737293824 all about? That's the primary key. That's right, there was a tweet #1 at some point, and at the time this tweet was posted, it was the 1,298,730,289,737,293,824th tweet that had ever been posted. The number never goes down, and the number never gets reused. Luckily, there are a lot of numbers in the universe still left to use.

Foreign Keys

Now that we know what a primary key is, let's look at why it's so important to use them in database design. First and foremost, we use primary keys to make our data design less fragile. To see what I mean by that – let's look at a quick example. If we look at the existing table design we've been working with, which does not contain primary keys, we can see how we can easily break the integrity of the data. For instance, what if we change the name of the "camera" product to something else?

Anarchy! We've immediately broken the relationship between the Products and Reviews tables. Now, the rows in Reviews that previously referenced the "Camera" are now orphaned, that is, they are left with no valid parent record that is referenced in its Product column. The SQL query that we just wrote to get product reviews in the furniture department – broken. Of course, we could update each review to reference the new name of the product. That would be easy enough in a database of this size, but certainly not in a database of more complex design and possibly millions of records. It's much better to update our database design to include a primary key.

The auto-incrementing primary key ID column starts at 1 and simply increments by 1 for each new record in our Products table. Next, we slightly re-design the Reviews table to point to the product's ID instead of its Name:

And to fix our SQL from above, we join by the ID and ProductID, instead of the Name of the product:

SELECT Products.Department, Reviews.Product, Reviews.Rating, Reviews.Body
FROM Reviews INNER JOIN Products ON Products.ID = Reviews.ProductID
WHERE Products.Department = "Furniture";

The ProductID in the Reviews column is known as the foreign key. A foreign key is simply a column in a table that points to a primary key in another table.

Try it: the baseball database

The baseball database is built exactly this way. players and teams each have an id, and stats holds one row per player per team per season, pointing at both with two foreign keys, player_id and team_id. To ask anything about a player's season, you join through it:

SELECT players.first_name, players.last_name, teams.year, teams.name, stats.home_runs
FROM stats
INNER JOIN players ON players.id = stats.player_id
INNER JOIN teams ON teams.id = stats.team_id
WHERE players.last_name = 'Griffey';

Run it on the Run SQL page, and look at the id of each player in the results (add players.id to the SELECT to see it). This is the Ken Griffey question from class: two players, one name. A query that adds up his home runs by name answers a question nobody asked. A query that uses his id cannot make that mistake, which is the whole argument of this post in one line.

SQL stands for Structured Query Language - see, you're halfway through the first sentence of this course, and you're already half asleep! Simply put, SQL is a way for us humans to communicate with databases. And "database" is a pretty generic term for "way for a computer to store data". There are lots of ways for a computer to store data, including (but certainly not limited to):

• CSV, JSON, or other structured text file
• Spreadsheet
• Document database
• Relational database management system (RDBMS)

That last one -- relational databases or RDBMS -- is the one we're going to concentrate on in this course. While there are many software applications that store data in other ways, including those listed, a vast majority of business-class software applications use some flavor of relational database to store data. These "flavors" are vast - some are open-source solutions, and some are proprietary and commercial. Examples of these are:

• MySQL (open-source)
• PostgreSQL (open-source)
• SQLite (open-source)
• IBM DB2 (closed-source, commercial)
• Microsoft SQL Server (closed-source, commercial)
• Oracle Database (closed-source, commercial)

And, although there are a large number of these flavors, developed by many different companies and organizations, the core of the SQL language is a standard that may be used to communicate with any of them.

Let's dive right in.

The Basics

Relational databases store data in tabular format. If you've ever used Microsoft Excel, Google Sheets, or any other spreadsheet application to store a list of anything, you've probably invented a way to organize data that is very similar to how databases do it - perhaps:

• Your data is stored in a grid format
• The columns of the grid represent the attributes of the thing you are storing
• There is a row in the grid for each one of the things, and maybe that's called a record

For example, if I were to build a spreadsheet of my favorite movies, it might look something like this:

Pretty straightforward. And, if I want to add more attributes to my data -- say, I want to add the studio that produced the movie -- I'd simply add to the columns of data.

And, if I want to add more movies, I'd add to the rows of data.

As simple as this example is, this is precisely how data is structured in a relational database system. To offer up some definitions - the columns are known as the fields or attributes, the rows are known as records, and the rows+columns together is a table. When we have a collection of many tables, that's a database.

In practice, the main difference between a spreadsheet (e.g. Excel) and a database system is that Excel already includes a standard front-end by which an end-user (like you) is able to read and write to the data. Since the use-cases for RDBMSes are much more varied and programmable than a spreadsheet, there's no such standard for relational databases. Instead, we use SQL to talk with the database, and to pass the results on to our front-end of choice, whether that's a custom web application, a PDF report, a commercial front-end like Tableau, or whatever else might be needed.

Using SQL to read data

In this lesson, we'll be looking at ways we can use SQL to read data from a relational database. In a later lesson, we will be looking at using SQL to write to and modify a database.

The SELECT statement

We read data from a database by using the SELECT statement in SQL. Let's look at an example. Suppose we own an e-commerce store - we'd probably have a a database table used to store the products we sell. This table would likely be named Products and look something like this:

The SELECT statement allows us to ask the database questions. What products do we sell? How much does the Toaster cost? How many products cost more than $500?

It would be nice if the SQL language knew how to answer your question, in exactly the way you asked it. But, no, it's a computer, so we have to follow a simple formula so that you can ask the question in a way that SQL can understand:

• Identify the table that holds the information you need
• Identify the columns that hold the information you need
• Add any other conditions
• Then, ask the question in SQL

For instance, if my question is: what are the names of the products we sell?

• Identify the table that holds the information you need - Products
• Identify the columns that hold the information you need - Name
• Add any other conditions - none
• Then, ask the question:

SELECT Name FROM Products;

There are 3 things about this SELECT statement that I'll point out:

• It ends with a semicolon ; - this depends on the flavor of RDBMS and the front-end being used, but many databases will require the semicolon at the end of the SQL statement, so all the examples we'll use will include it
• The column and table names are case-sensitive; again, this will depend on the specific database system, but for illustration's sake, all examples will match the table/column name casing in the database
• The SELECT and FROM clauses are in ALL CAPS; this is NOT a requirement in SQL, but clauses in examples will be written this way, as it is often more readable

The result that we'll receive is actually another, temporary table - i.e. one that is not part of the database structure or schema as-designed, but is simply a temporary subset of the larger table we've targeted. I like to think of this as a "👶baby table" - a much smaller table that looks a lot like the larger, original table, but only holds the data we asked for:

To be clear, "👶 baby table" is not an official SQL term... it's just what I like to call it.

Now, it's up to you to decide if the results provided in this "👶 baby table" are everything you need, or if you need to modify your question in order to get more information.

Hmmm, it would nice if I could see the price too. Can I get a list of all the products we sell, along with the price?

Back to the formula:

• Identify the table that holds the information you need - Products
• Identify the columns that hold the information you need - Name and Price
• Add any other conditions - none
• Then, ask the question:

SELECT Name, Price FROM Products;

Sorting

Now that we've seen how to use the SELECT statement in SQL to retrieve rows of data from a database table, let's take one step toward doing something more meaningful. It's pretty rare that we'll want to get all records in a database table, in exactly the order that the records were inserted - this is what we've done thus far.

We'll often want our records in our 👶 baby table to be in a specific order - perhaps alphabetically or sorted by a numeric value. This is going to be based on how the data is ultimately consumed - e.g. in a PDF report for a stakeholder in your company, displayed on-screen for a user of a web application, and so on. Let's continue with our question and answer format for this one:

Can we order the results by price?

• Identify the table that holds the information you need - Products
• Identify the columns that hold the information you need - Name and Price
• Add any other conditions - sort by Price
• Then, ask the question:

SELECT Name, Price FROM Products ORDER BY Price;

As we can see, ascending order (from the least to the greatest) is the default when sorting a numeric value. (It's alphabetically from A-Z with a non-numeric value.)

Can we order by the more expensive products first?

• Identify the table that holds the information you need - Products
• Identify the columns that hold the information you need - Name and Price
• Add any other conditions - sort by Price in descending order
• Then, ask the question:

SELECT Name, Price FROM Products ORDER BY Price DESC;

Limiting

Another common use-case is that we want to limit the number of rows in our 👶baby table - what we've done so far is just fine for our very small table with 4 rows of data. But what if our Products table contained 10 million rows? The SELECT statements we've written thus far would be very slow, and not likely to be very helpful or meaningful to the person receiving the results. So we must often reduce our results, and that usually means refining the question we're asking.

What is the single most expensive product we sell?

• Identify the table that holds the information you need - Products
• Identify the columns that hold the information you need - Name and Price
• Add any other conditions - sort by Price in descending order and only return 1 row
• Then, ask the question:

SELECT Name, Price
FROM Products
ORDER BY Price DESC
LIMIT 1;

Same results as before - but only returns a single record, answering our question. Also note that we're separating each fragment of the SQL statement here -- AKA clauses -- by putting each on its own line. As our SQL becomes more complex, I've found this easier to read and comprehend -- especially when first learning -- so we'll do this from now on.

Try it: the baseball database

Let's play ball! Open your copy of the baseball repository in its Codespace (the one you made in class; reopen it from github.com/codespaces rather than making a new one), run bin/rails server in the terminal, and open the port it offers. The app opens on the Run SQL page, a box that runs whatever SQL you type into it. The database has three tables:

teams (id, year, name, park, wins, losses)
players (id, first_name, last_name, bats, throws)
stats (id, team_id, player_id, games, at_bats, runs, hits, doubles, triples, home_runs, rbis)

Try it:

SELECT first_name, last_name FROM players;

Don't forget that semi-colon! You should see the first and last names of all 20,358 players in the database. Then try the question from class, the five winningest seasons ever, using the formula above: which table, which columns, sorted how, how many rows?

If you would rather work in the terminal, save a query in a file in queries/ and run it with bin/query queries/your-file.sql. And if you want to see the database itself, with no page in front of it, bin/rails db opens SQLite's own prompt (type .quit to leave). We will live there in week 5.

Filtering Data

The ability to filter data is probably the most powerful and often-used feature of SQL. For many people, gaining insights from arbitrary sets of data is often the objective of learning SQL in the first place.

We've already learned about the LIMIT clause, which simply whittles down the size of our result set -- the 👶 baby table -- by some number that we provide. However, we often want to specify criteria for filtering the results, rather than a set number like 10. What are our best-selling products? Which sales tactics are producing the best results this month? How many orders did we receive for the new widget this week?

For questions like these, we must use the WHERE clause. The WHERE clause allows us to add more criteria to the questions we ask. Let's jump into an example using our Products table:

Which products cost more than $500?

• Identify the table that holds the information you need - Products
• Identify the columns that hold the information you need - Name and Price
• Add any other conditions - Price is more than $500
• Then, ask the question:

SELECT Name, Price FROM Products WHERE Price > 500;

A WHERE clause is going to always have a column name (in this case, Price), a comparison operator (> or "greater than") and a value to compare (500). Because the column in question contains numbers, it's going to compare the Price of every row in the table against the numeric value 500 and filter the results to only include those with a price greater than that number. Let's look at what happens with a non-numeric comparison:

Which products are sold in the Furniture department?

• Identify the table that holds the information you need - Products
• Identify the columns that hold the information you need - Name and Price again, but we probably want Department now too
• Add any other conditions - Department is Furniture
• Then, ask the question:

SELECT Name, Price, Department 
FROM Products 
WHERE Department = "Furniture";

Again, we have a column name (in this case, Department), a comparison operator (= or "exactly equals") and a value to compare ("Furniture"). Because Department is non-numeric, the value to compare must be in quotes. This is necessary to distinguish the comparison value from columns name, other SQL clauses, variables, and the like.

Separate multiple filters/conditions with the AND clause. So if we wanted the answer to:

Which products are sold in the furniture department and cost more than $500?

SELECT Name, Price, Department 
FROM Products 
WHERE Department = "Furniture"
  AND Price > 500;

Matching a pattern

= means exactly equal, and sometimes that is not what you want. LIKE matches a pattern, where % means "anything, or nothing, here." In the baseball database:

SELECT year, name, park
FROM teams
WHERE park = 'Wrigley Field';

returns 107 rows, every season played at the ballpark in Chicago. Change the last line to:

WHERE park LIKE '%Wrigley%';

and you get 108. The extra one is the 1961 Los Angeles Angels, who played their first season at a different ballpark, Wrigley Field (LA). Neither query is wrong; they answer different questions. The question you asked and the question you typed are not always the same question, and the only way to know which one you got is to read the WHERE.

Try it: filtering

On the Run SQL page:

• Every team that has played at Wrigley Field. Then change = to LIKE and find the extra row.
• Which team won the most games in 2002? You will probably write ORDER BY wins DESC LIMIT 1. Then change the 1 to a 2. Be honest with yourself about whether you would have checked.

A Star is Born

We've already seen how SQL queries require us to provide the columns that we want to be returned in the 👶 baby table - and how we often need to think about what columns those are when providing clean, concise output. Occasionally though, the columns don't really matter. In these cases, we can use the asterisk or star (*) to denote that we want all columns in a table. Consider a Reviews table, which contains reviews about our products. If we simply want all columns, all rows from this table, we could write a query that looks like:

SELECT * from reviews;

And the result would be everything -- all columns and all rows -- from that table.

In practice, we're rarely going to use it like this, unless a table contains just a few columns, or when we don't care about concise output, like when we're testing our queries. Or, within an aggregate function, which is where the optional next post picks up.

Order of Clauses

We've seen enough clauses now to talk about the order they go in. Because of the way SQL is executed, the order in which the clauses are written does matter:

• SELECT - the columns you want in the result
• FROM - the table(s) involved
• WHERE - the conditions or constraints
• ORDER BY - columns by which the results shall be ordered, either numerically or alphabetically
• LIMIT - how many rows to return

To put it all together, in the baseball database:

Which three teams won the most games in the 2000s?

SELECT year, name, wins
FROM teams
WHERE year >= 2000 AND year < 2010
ORDER BY wins DESC
LIMIT 3;

Run it, then change the 3 to a 4. The 2002 Yankees are third with 103 wins. The 2002 Oakland Athletics also won 103, and they're fourth only because of the order the rows happen to sit in. LIMIT cuts where you tell it to, whether or not that's a sensible place to cut.

Going further (optional)

That's everything we covered in class, and everything you need to read the SQL a chat writes for you in this course. If you're curious about the math side of SQL, counting, averaging and grouping, it's in the optional next post. Otherwise, on to SQL 2: Table Relationships.

This is the longer version of the exercise at the center of Week 2: asking a chat about a database, reading the SQL it writes, and deciding whether to believe the number. If the loop went by too fast in class, or you want the reasoning behind it, start here. The SQL we covered (SELECT, WHERE, ORDER BY, LIMIT, joins, keys) is in SQL 1 and SQL 2.

You were the stakeholder

Every company that runs on data has the same three kinds of people touching it. Analysts read data and turn it into decisions. Developers build the applications that read and write it. Database administrators change its structure. And then there is everyone else, which is most people, and probably you: the stakeholder who has a question, hands it to someone who can answer it, and gets a number back.

In class, the chat was the analyst and you were the stakeholder. You did not write the SQL. Your job was harder: decide whether to believe it.

That is the job this course keeps giving you, and it gets more important, not less, as the tools get better. An analyst who is right nine times out of ten is extremely useful, as long as somebody can tell which time is the tenth.

The analyst knows only what you give it

Before the first question, you pasted a brief into your chat. It said: this is a SQLite database, these are the tables and their columns, the seasons run from 1871 to 2020, and when I ask a question, give me the SQL and the answer you expect it to return.

That brief is the whole of what the chat knew about your database. It never saw the file. It could not look at a row. Everything it got right came from what you gave it, and everything it got wrong came from what you did not.

If you skipped the brief, you saw what happens instead: the chat invents a schema. It writes a confident query against a franchise column or an HR column that does not exist, and the query fails. That is the cheap version of the lesson. The expensive version is a question with just enough context to run and not enough to be right.

The word for what you pasted is context, and it will come back all quarter.

Steps

1. Ask. Paste the question exactly as written.
2. Read. What comes back is two things: the SQL, and the number the chat expects it to return.
3. Run. Paste the SQL into the Run SQL page yourself, and compare what the database returns with what the chat predicted.
4. Save. Keep the query in queries/, with the question on the first line, so the Queries page shows it next to the others.
5. Decide. Write one sentence: trust, or don't trust, because.

The "because" is the skill. "Trust, because the SQL filters on the right year and 107 wins is a believable season" is a judgment. "Trust, because it looked right" is a coin flip with extra steps.

The number was memory. The SQL was work.

Asking for both the SQL and the expected answer was deliberate, because they come from different places.

The chat has read an enormous amount about baseball. Ask it who won the most games in 2019 and it will tell you the Houston Astros, 107, before it has written a line of SQL, because it remembers. That expected answer is a prediction from memory. It is often right. It is not evidence.

The SQL is the only part of the reply that touches your data. When you run it, the database answers the question the SQL asks, which is not always the question you asked.

On the first question, the two agreed: the query returned the Astros with 107, and the chat had said so. That agreement is what trust looks like. On the Ken Griffey question, some of you saw them disagree: the chat said 630 home runs, and its own SQL returned 782. Only one of those numbers came from the data, and it was not the one it was confident about.

Three ways a correct-looking answer goes wrong

None of these produce an error. The query runs, a number appears, and nothing on the screen tells you anything is wrong.

The question meant more than one thing

"Which team has lost the most games in history?" In this database there are at least three answers:

• The worst single season: the 1899 Cleveland Spiders, 20 wins and 134 losses.
• The most losses added up by team name: the Philadelphia Phillies, 10,426.
• The most losses by franchise, which is what a fan means: unanswerable. The database has no idea that the Cleveland Bronchos, Naps and Indians are one club, or that the Cleveland Spiders were a different one. It has team names per season, and Cleveland alone appears under seven of them.

The chat had to pick one, and most of them picked without telling you. Some asked which one you meant, or gave you both, which is what a good analyst does. Either way, the ambiguity was in the question before it was in the SQL. If you do not say which answer you want, someone else decides.

And when none of the readings is the one you meant, that is not a SQL problem. It is a data model problem: the database is missing something (here, a franchise) that the question assumes. Which is where the second half of the class went.

The query matched on a name

"How many home runs did Ken Griffey hit?" There are two Ken Griffeys: the father, with 152, and the son, the Hall of Famer, with 630. A query that finds him with WHERE first_name = 'Ken' AND last_name = 'Griffey' adds them together and returns 782, a number that is exactly right for a question nobody asked.

The same thing turned up earlier in the day, on purpose. WHERE park = 'Wrigley Field' finds 107 seasons, all in Chicago. WHERE park LIKE '%Wrigley%' finds 108, because it also catches the 1961 Los Angeles Angels, who played their first season at a different ballpark named Wrigley Field. The question you asked and the question you typed are not always the same question.

This is what the primary key lecture was for. Names are for humans: they get misspelled, shared and reused. An id is the one thing guaranteed to mean one row. When the answer is about a particular person, place or thing, look at the WHERE and ask whether it identifies that thing, or just something with the same name.

Rows went missing

"Which team won the most games in 2002?" The natural query is:

SELECT year, name, wins
FROM teams
WHERE year = 2002
ORDER BY wins DESC
LIMIT 1;

It returns the New York Yankees, 103 wins. It is also wrong, or at least half of the answer: the Oakland Athletics won 103 games that year too. That is the Moneyball season. LIMIT 1 kept the Yankees only because of the order the rows happened to be stored in. Change it to LIMIT 2 and the A's appear.

A missing row is the hardest mistake to catch, because there is nothing on the screen to look at. You can read a wrong WHERE clause. You cannot read a row that is not there.

You can test a feature. You can only check a query.

When a developer builds a feature or fixes a bug, there is a way to know it worked. Click through it the way a user would. Better, write a test: a small program that says what should happen and checks that it does, run every time the code changes. At the end of class you watched an agent do exactly that, adding a test for 140-character captions and running it.

A query does not get that. It runs, a number comes back, and nothing on the screen says whether it answered the question. Some teams build a small test database, stocked with the edge cases they already know about (the legacy rows, the duplicates, the weird old records), and run their queries against it. That helps. Mostly, though, someone spot-checks the result, and spot-checking takes knowing the data: its edge cases, its quirks, and the places where it is not as clean as it looks.

Look at the traps above again. The 2002 tie, the two Ken Griffeys, Cleveland under seven names, the other Wrigley Field: none of them was a SQL mistake. Each was a fact about the data that you had to already know to notice. And nobody hands you the expected result. If the stakeholder knew the answer, they would not be asking.

None of this is new with AI. It was true when analysts wrote every line of SQL by hand, and it is still true now that a chat writes it. What changed is how fast a confident answer arrives. The checking did not change.

Four cheap checks

None of these need more SQL than you already know.

1. Say what you expect before you run it. Even roughly: "a team, a hundred-ish wins, this century." A result that surprises you is the only alarm you get, and you cannot be surprised if you had no expectation.
2. Ask for one more row than you need. If the question is "which one", run it with LIMIT 3. Ties and near-ties show up immediately, and so does a second Ken Griffey.
3. Count what came back. If you asked for the best team in every season since 1960, you should get at least 61 rows, one per season. Fewer means something was dropped. More is not necessarily wrong (in six of those seasons there was a tie), but you should know why.
4. Read the WHERE out loud. Does it say what you asked? A name where an id should be, a LIKE where you meant an exact match, a year range that is off by one: the WHERE clause is where most wrong answers live, and it is usually one line.

When the analyst is good

Some of you watched your chat get everything right: it named both readings of the losing question, split the two Ken Griffeys by id, or wrote the 2002 query so that it returned both teams. The better chats are good at this, and increasingly so.

Check anyway. The chat caught the 2002 tie because it remembers the 2002 season. It does not remember your company's customers, your pricing table or last quarter's refunds, and on that data it will write the same careful-looking SQL with nothing to check it against but you. Good analyst. Now check that it was right. The loop does not change.

Never run UPDATE or DELETE without WHERE

Everything above is about reading. This is about the one time it stops being safe to get it wrong.

UPDATE and DELETE change data, and both apply to every row the WHERE clause matches. With no WHERE clause, that is every row in the table. DELETE FROM teams; does not ask which teams. It removes all 2,955 of them.

In class you watched an AI agent (a chat that can run commands itself, not just suggest them) get asked to "clear out the teams table so we can reload it from a fresh export." It wrote exactly that statement. It stopped and asked first only because the repository has a file, AGENTS.md, telling any agent working there to show a change to the data and wait for confirmation. Without that line, it runs.

Then it ran, and the table was empty. The database came back because in this class the database is a single file that lives in Git, and git checkout db/baseball.sqlite3 restored it. Production databases do not work like that. Most of the time there is no undo, only a backup, if someone made one, from some time before the mistake.

So: when you see UPDATE or DELETE, from a chat, an agent, a colleague or yourself, find the WHERE before anything else. If there is not one, stop.

The thread through all of it

Every trap in this post ran without an error. The SQL was valid, a number came back, and the only thing between that number and a decision was someone reading it. Writing SQL is increasingly something you will hand off. Deciding whether to believe it is not.

Most weeks from here on start the same way and end the same way. This is the short version of both, plus how to reset the database and one settings fix for Windows. Keep it open the first few times; after that you will not need it.

Before Week 2: have a chat open

In Week 2 you ask an AI chat questions about a database and decide whether to believe its answers. Bring whichever one you already use: ChatGPT, Claude, Gemini, Copilot. Any of them is fine. There is nothing to install and nothing to sign up for. Have it open in a second window before class starts.

Getting started

1. Use this template. On the class repository's page, click Use this template, then
   Create a new repository. Pick your own account as the owner, keep the name, and click
   Create repository. You now have your own copy; nothing you do can affect anyone else's.

2. Create the Codespace. In your new repository: Code, then the Codespaces tab,
   then Create codespace on main. The first start takes a few minutes. Let it finish; the
   terminal at the bottom is ready when it shows a prompt and stops scrolling.

3. Start the server. In the terminal:

   bin/rails server
   

   A box pops up offering to open the port. Click Open in Browser. If you miss the box, open the Ports tab next to the terminal and click the globe icon on port 3000.

The server keeps running in that terminal until you stop it with Ctrl-C. If you need a terminal for anything else, open a second one with the + on the terminal panel.

Coming back later. Do not create a second Codespace. Go to github.com/codespaces, or
Code > Codespaces on your repository, and open the one you already have. Your files are where you left them. The server is not; run bin/rails server again.

Windows: turn on right-click

On some Windows machines, right-clicking in the terminal does not open a menu, and the menu is how you find things like Clear. To fix it:

1. Click the gear icon at the bottom left of the editor (the one inside the editor, not the one at the far left of your screen), then Settings. Not the command palette; the setting is not there.
2. Search for right click behavior.
3. Under Terminal > Integrated: Right Click Behavior, yours probably says copyPaste. Change it to selectWord.

It saves itself. Close the Settings tab and right-click in the terminal again. If you are on a Mac, you can skip this.

Resetting the database

In Week 2 the whole database is one file, db/baseball.sqlite3, and it is committed to the repository. So if anything damages it (a DELETE or UPDATE you did not mean, a table you dropped), you can put it back exactly as it was. In the terminal:

git checkout db/baseball.sqlite3

Then refresh the page. The rows are back. The server can keep running while you do it.

This works as long as you have not committed the damage. If db/baseball.sqlite3 ever shows up under Changes when you go to commit, run the line above first, then commit.

Wrapping up

1. Stage. Open Source Control in the left bar (the icon that looks like a branching line). Your changed files are listed under Changes. Click the + next to each one, or the + on the Changes heading to stage them all.
2. Message, then commit. In the box above the list, write what you did in plain words, like answered the three questions. Click Commit.
3. Push. The button turns into Sync Changes. Click it. That sends your commit to GitHub; until you do, it exists only inside the Codespace. Check your repository's page on github.com: your commit message should be there.
4. Stop the Codespace. On your repository's page: Code > Codespaces, the three dots next to yours, Stop codespace. It stops itself after thirty minutes idle, but doing it yourself is the habit, and Codespaces hours come out of your own monthly allowance.

Stopping is not deleting. Your Codespace and everything in it are still there next time.

When it does not work

Post in #fall-2026 on Slack with a screenshot and the step you were on. If it is during class, raise your hand; an FA will come to you.

This is one of two posts from the first week. The other, Git and GitHub, is the tools: what they are and how a change gets into a codebase. This one is about whose code you are willing to build on in the first place.

In class we have (or will) spend a little time on open source and licensing -- enough to give you the shape of it and not much else. This is the longer version, for when you need it.

And you likely will need it. Not because you are going to become a lawyer, but because at some point somebody on your team is going to say "let's just use this library," and you are going to be the person who decides. That decision has terms attached to it, and the terms occasionally bite.

Closed and open

Software gets built and brought to market two ways.

Closed-source software is a commercial asset. The code is seen only by the people working on it, and the intellectual property belongs to whoever owns the company. Most software that is sold, whether packaged or as a service, works this way. If a company's secret sauce has anything to do with the code, they are not going to show you the code.

Open-source software has its source code available to the public. That is the whole definition. Note what it does not say: it does not say free, it does not say non-commercial, and it does not say nobody owns it. Plenty of open-source software is built by large companies, for commercial reasons, and is owned by someone specific.

Why anyone gives work away

The reasonable first question is why a developer or a company would put thousands of hours into something and then hand it over.

• Crowdsourcing. Publish the code and other people find your bugs, and sometimes fix them. The project gets better than you could have made it alone.
• Knowing what is in your food. Telegram publishes its source so it can prove it is not doing anything malicious with your data. A closed product cannot make that promise; it can only assert it.
• Reputation. An individual developer open-sources a piece of their work so that employers and future partners can see what they are capable of.
• Recruiting. A company open-sources something interesting so that the developers they want to hire come and look at it.
• The ecosystem, which is really marketing. Google does not open-source all of Chrome, but the engine underneath it is open. Other browsers get built on that engine, and when one of those browsers does not work out, moving to Chrome is easy, because it is the thing you were already standing on. Being the foundation other people build on is a competitive position, not charity.

The biggest reason underneath all of those is less strategic than any of them. Software is built the way science is built, by people standing on each other's work. Donald Knuth, the Stanford computer scientist, put it this way:

People think that computer science is the art of geniuses but the actual reality is the opposite, just many people doing things that build on each other, like a wall of mini stones.

That has never been more true than it is right now. The reason you can build something sophisticated in a quarter is that thousands of people already built the parts you are standing on.

Here is what that looks like in practice. Every application with users has to handle user security: passwords, sessions, encryption. Almost no business is about user security. It is table stakes, it is subtle, it is easy to get wrong in ways that hurt people, and writing it yourself costs you weeks you needed for the thing your product is actually for. So you take a library that already exists, that thousands of other applications have already stress tested, and you spend your weeks on your own problem instead.

Do not fool yourself into thinking you are going to write all of your own code. Nobody does this. You would never want to, because you would spend all your time in the foundations and never get to the interesting part.

Licensing: the two families

Open source is not a free-for-all. Every open-source project carries a license, and the license is a real legal document with real terms. There are many of them and there are endless variations, and when it matters you read the actual text or you ask someone whose job that is. But nearly all of them fall into one of two families.

Permissive. Do what you like. Use it, change it, sell it, bundle it into something closed-source. Most permissive licenses ask for one thing in return: keep the copyright notice around so people can tell where the code came from. The MIT license is the most common one you will see, and Apache 2.0 is the other big one, which adds some explicit language about patents.

Copyleft. You may use this, and you may change it, but if you distribute what you built, the result has to carry this same license. The GNU General Public License, the GPL, is the one you will run into. The point is reciprocity: the code was given to you open, and whatever you make out of it goes out open too. Sometimes it is called "share-alike," which is a better name for what it actually does.

One thing worth being precise about, because the short version in class makes it easy to get backwards: copyleft does not mean the code is frozen. You are absolutely allowed to modify GPL code. Modifying it is the point. What you owe in return is that your modified version goes out under the same license, so that the next person gets the same freedom you got. The obligation is on what you publish, not on what you touch.

It is permissive licenses, not copyleft ones, where "leave it alone" comes closest to the truth, and even there the obligation is usually just to preserve the notice.

choosealicense.com is a genuinely good, short guide to picking one, and it is the right link to send a co-founder who is about to make this decision for your company.

Case study: the Linux family tree

Linux is the most widely deployed operating system in the world. Not Windows, not macOS. Estimates move around and depend on what you are counting, but the large majority of the web servers on the internet run some flavor of Linux, as does every one of the world's fastest supercomputers, and so does every Android phone. It was written by Linus Torvalds, who also
later wrote Git, which is the other thing we covered in week one. He has had a productive career.

The core of Linux, called the kernel, is licensed under GPLv2. Copyleft. That single fact shapes an entire industry, and the shape is worth following.

Distributions. Ubuntu, Debian, Red Hat and Fedora are all built around that same kernel. Their kernel remains GPL. They wrap it in package managers, installers, desktop environments and support contracts, and those pieces carry their own licenses. Red Hat built a large public company selling support and certification for software anyone could download for nothing.

Android. In 2008 a team at Google built a phone operating system on the Linux kernel. Android's own layers, the parts Google wrote, are licensed under Apache 2.0, which is permissive. That combination surprises people, and it is worth understanding why it is allowed, because it is the single most useful thing in this whole post.

It is not because Google left the kernel alone. Google modifies the kernel substantially for Android, and those modifications are published under the GPL, exactly as the license requires.

It is because of a boundary. Torvalds attached a note to the kernel's license saying that ordinary user-space programs that call into the kernel through normal system calls are not derivative works of the kernel. They are just using it. Android's own layers sit on the user-space side of that line, so they do not inherit the kernel's license and Google can license them as it chooses. Google also deliberately built Android's C library from permissively licensed code rather than the copyleft one Linux distributions normally use, for exactly this reason.

The lesson generalizes past Android: where the boundary falls between your code and the copyleft code is the question that decides your obligations. Linking a GPL library directly into your application is a very different act from running a GPL program as a separate process and talking to it. If your company is ever seriously weighing this, that is the question to bring to a lawyer, and it is a question you can now ask precisely.

On top of Android. Because Android is permissive, anyone can fork it and build a commercial product. Amazon did, and Fire OS runs the Kindle Fire line. Meta built the software for its VR headsets on it. Peloton runs it on the screen bolted to the bike. None of those companies had to open-source the product they built.

A small correction to something easy to assume here: Amazon is not prevented from charging money for Fire OS. It could. It does not, because Amazon's interest is in selling devices and content, and giving the operating system away with the hardware serves that. The constraint is a business decision, not a licensing one.

Follow that chain once more, because it is the point of the whole case study. A copyleft kernel, given away in 1991. A permissive operating system built on it by one of the largest companies in the world. Closed commercial products built on that, by three more. Your closed-source product will sit on a stack like this one, whether or not you ever look down.

The four questions you asked in class

These came from the room, not from the slides, and they are the right questions.

How is any of this actually enforced?

Mostly the way any contract is: somebody notices. An employee, a competitor, a customer who asks for the source, or one of the organizations that exists specifically to notice. The Free Software Foundation and the Software Freedom Conservancy have both pursued GPL violations, usually by asking first and suing rarely.

The famous case is a router. In the early 2000s Linksys shipped the WRT54G, a wireless router that turned out to be running Linux inside. The GPL obliged them to release the source for what they shipped, they had not, and the community noticed and pushed. Cisco, which by then owned Linksys, released it. The released code became OpenWrt, which is still running on routers today. The violation ended up producing a piece of open-source infrastructure.

Here is the version you are more likely to live through, which happened to a company one of us worked with. They were using an open-source front-end framework. The framework changed its license on a new version. They upgraded, because they wanted the new version, and nobody on the team connected the upgrade to the terms. About a year later somebody worked out that they had been out of compliance the whole time, and it was settled with a payment.

Nobody was reckless. They upgraded a dependency, which is a thing engineering teams do dozens of times a quarter without anyone thinking about it, and the terms had moved underneath them.

Does Linux force updates on you?

No. The project keeps developing, and you run whatever version you chose to run. Nobody can reach into your product and change it.

You will usually want the updates anyway, because that is where the security patches are and because software that stays still gets stranded as everything around it moves. But taking them is your decision, and it is a decision, which matters for the next question.

Can a project change its license retroactively?

Not for the version you already have. The copy you received came with terms, and those terms came with it.

The next version is a different matter. A project can license its next release however it likes, and if you upgrade, you have accepted the new terms. That is the trap in the story above, and it is worth stating plainly: the risk does not arrive when the license changes. It arrives when you upgrade.

This is not hypothetical or rare. Over the last several years, a number of widely used projects have changed their terms on a new version, sometimes to a license that is no longer open source at all. Redis, MongoDB, Elasticsearch and HashiCorp's Terraform all did some version of this, and in several cases the community responded by forking the last version under the old terms and continuing from there. If your product depends on something, a license change upstream is a real business event, not a legal footnote.

The practical defense is unglamorous and it is the same defense as for every other dependency risk: know what you depend on, and have someone actually look when a major version lands.

Does everything built on copyleft code have to be open source?

No, and the Android section above is the long answer. The short answer is that it depends on whether what you built is a derivative work of the copyleft code or a separate thing that uses it, and that boundary is technical as much as legal.

What is not optional: the copyleft code itself, and your changes to it, stay under that license and have to be available to the people you distributed it to. You cannot take GPL code private. You can, in many architectures, build something of your own next to it.

And one you asked that we could not answer

Somebody asked why the authors of copyleft projects care, since a permissive license would get their code used more widely. It is a good question and it deserves a real answer.

The answer is that for the people who started this, wide adoption was never the goal. Richard Stallman and the Free Software Foundation designed the GPL in the 1980s to protect a commons. The worry was specific: that software written to be free would get absorbed into closed products, improved there, and that the improvements would never come back, leaving the open version permanently behind. Copyleft is the mechanism that stops that. It is a one-way ratchet, and every improvement anyone distributes lands back in the pool.

Permissive licensing makes a different bet, which is that the widest possible adoption is worth more than the guarantee of reciprocity. Both bets have paid off enormously. They are simply different bets, and a project's license tells you which one its authors made.

Reading a repository before you depend on it

The part of this that will matter to you most often is not the law. It is judgment: deciding whether some project you found is something you want your product to stand on. You will make this call many times, and usually in about four minutes.

Open the repository on GitHub and look at these, roughly in this order.

1. The license. It is listed in the sidebar. If there is no license file at all, the default is that you have no rights to use it, whatever the author intended by putting it in public. "No license" is a red flag, not a green one.
2. The last commit. Near the top of the file list. A project last touched four years ago is a project you are adopting, not using.
3. Issues and pull requests. Do not just read the count. Open the tabs. Are maintainers replying? Are pull requests getting merged or quietly piling up? An open issue list is healthy. An unanswered one is not.
4. Releases. Regular releases mean somebody is still steering.
5. Who is behind it. One person's side project and a thing maintained by a company or a foundation carry very different risk. Neither is disqualifying. You just want to know which one you are picking.
6. Stars and forks. Useful, weak signals. Stars are closer to a bookmark count than a quality score, and old popular projects keep their stars long after the maintainers have moved on. Read them alongside the last commit, never alone.
7. Documentation, and a demo if there is one. Many projects run a live demo page. It is the fastest way to find out whether the thing does what you think it does.

Three worth opening, because they are good examples of different shapes:

• microsoft/vscode, the editor you are using in your Codespace, maintained by a large company in the open.
• nasa/openmct, NASA's mission control framework, with a demo page you can click through.
• chromium/chromium, the engine underneath Chrome and most other browsers, and an example of the ecosystem argument from the top of this post.

What this is really about

You are not going to be the person reading the license text. You are going to be the person who decides whether the team takes the dependency, and who gets asked, a year later, why the company is having this conversation with a lawyer.

The three things worth carrying out of here: open source is not free of terms, the terms change on upgrade and not before, and where the boundary falls between your code and theirs is the question that determines what you owe. Everything else you can look up.

Next: Practice: GitHub, where you run the loop once on your own. If you have not read Git and GitHub yet, read that first; it covers the
difference between the two and how a change actually gets into a codebase.

In class you ran the loop once: a branch, a file, a commit, a pull request, somebody else's review, a merge. You did it in a room with forty other people doing the same thing and an FA two seats away.

This week you do it again, alone, on a repository of your own where nothing can go wrong. That is the entire point of this exercise. The first time you follow along. The second time you find out what you actually know.

It should take about thirty minutes. There is nothing to turn in.

Part 0: get your own copy

If you already made your own hello-world during class, skip this.

Go to github.com/dsgn425-fall2026/hello-world, click the green Use this template button, then Create a new repository. Owner: your own account, not the class organization. Name: hello-world. Leave the visibility on Private. Click Create repository.

Check the name at the top left of the page. It should read your-username/hello-world. If it still says dsgn425-fall2026/hello-world, you are looking at the template, not your copy; go back and click Use this template.

It is yours now. You cannot break anything that belongs to anyone else, which is why we are doing this here rather than in the class roster repo.

Part 1: the loop, by yourself

Open your copy and follow its README from step 2 to step 6. Do not read ahead and do not copy what you did in class from memory. Work it through.

Branch, change something, commit with a real message, publish, open a pull request, merge it, delete the branch.

Two differences from class, and both are worth noticing:

You are the whole team. In the class repo a classmate had to approve your pull request. Here nobody does, so you get to read your own change on the Files changed tab and merge it yourself. Read it anyway, properly, as though somebody else wrote it. That habit is the one this course is really trying to build.

Nothing is protected. Your repo has no rule stopping you from committing straight to main. You could skip the branch entirely and it would work. Make the branch anyway. The habit has to survive the absence of a rule enforcing it, or it is not a habit.

If you want the reasoning behind any of it, rather than the buttons, that is in Git and GitHub.

Part 2: the same loop, from the terminal

Now do it a second time without touching the file explorer. Everything below happens in the Codespace terminal, which you open with View > Terminal from the hamburger menu.

This half exists because the command line is the part of the first class that fades fastest if you never touch it again. Ten minutes now is worth an hour later.

Start on a new branch. Bottom-left corner, Create new branch..., call it terminal-practice.

Find out where you are.

pwd

It prints your current directory, which should end in /hello-world. Then:

ls

lists what is in it. You should recognize the files from the Explorer. Now:

ls -a

The same list, plus the hidden files, the ones whose names start with a dot. There are more files in a repository than the Explorer shows you by default, and .devcontainer is how your Codespace knew what to build.

Run the programs that are already there.

ruby code/hello.rb

prints a greeting. Then:

ruby code/infinite-tacos.rb

which does not stop on its own. Press Ctrl-C to kill it. You need this more often than you would think, and nobody tells you about it until you need it.

Make something.

mkdir notes

creates a folder. Confirm it with ls. Then:

touch notes/week-01.md

creates an empty file inside it. Open notes/week-01.md from the Explorer, write two or three lines about anything from the first class, and save with Ctrl-S.

Move something.

The repo has an images folder with a couple of files in it. Pick one and move it to the top level, then move it back:

mv images/grogu.jpg .
ls
mv grogu.jpg images
ls

The . means "here, the directory I am in." Watch the Explorer on the left as you run each one. The file jumps. You can of course do all of this by dragging with a mouse. The point is that you now know the other way, and the other way is the one that works over a terminal connection to a server at two in the morning.

One useful trick each. Start typing a directory name and press Tab; the shell finishes it for you, and if nothing happens it is telling you that what you typed does not exist. Press the up arrow to bring back the last command you ran, which saves you retyping a long one with a typo in it.

Then put it through the loop. Source Control, stage, commit with a message that says what you did, Publish Branch, open the pull request on github.com, read the diff, merge, delete the branch.

Before you close the tab

Stop your Codespace. On the repo page, Code > Codespaces, the three dots, Stop codespace. It stops itself after thirty minutes idle, but doing it yourself is the habit, and Codespaces hours come out of your own monthly allowance.

What to check before you call it done

• Your repository's main branch contains both changes.
• The commit history shows how they got there, and the messages say what happened. If any of them says update or tried again, go and look at why that is useless to you two weeks from now.
• Both branches are gone, because you deleted them after merging.
• You can say, without looking anything up, what the difference is between a commit and a pull request.

That last one is the actual assessment. The rest is just evidence.

If you got stuck

Post in #fall-2026 on Slack and say where it stopped working. Getting stuck is not a failure mode here, it is the expected experience of week one, and the stuck point is usually the same three or four things. The troubleshooting list at the bottom of the class roster repo's README covers most of them.

This is one of two posts from the first week. This one is the tools: what Git and GitHub are, and how a change actually gets into a codebase. The other is Open source and licensing, the longer version, which is about deciding whose code you are willing to build on. Start here; that one uses the vocabulary this one sets up.

Git and GitHub are not the same thing

Nearly everyone trips over these two names, and it is worth thirty seconds of precision, because more decisions hang off the distinction than you would guess.

Git is software. It is version control for source code: it stores your files, keeps their entire history, and lets several people work on the same project without overwriting each other. Linus Torvalds wrote it in 2005 to manage development of the Linux kernel. It is free and open source, it runs on your own machine (or, this quarter, in your Codespace), and no company owns it.

GitHub is a company. Founded in 2008, bought by Microsoft in 2018 for around seven and a half billion dollars. Its business is hosting Git repositories and selling the things built around them: a web interface, permissions, issue tracking, code review tools, automation.

The relationship between the two names is exactly that, and nothing more. GitHub hosts Git.

If it helps: Git is email, GitHub is Gmail. Email is a protocol that nobody owns and that many companies provide. Gmail is one company's product for using it, with its own interface and its own features layered on top. You can leave Gmail and still have email.

Git is the thing. GitHub is a place to keep the thing.

Other places to keep the thing

GitHub is the largest by a wide margin, which is why this course uses it and why it is the one worth knowing. It is not the only one, and a few of these will turn up in your working life:

• GitLab, the closest competitor, and the one most often chosen by companies that want to run the whole thing on their own servers.
• Bitbucket, from Atlassian, common in organizations already using Jira.
• Azure DevOps, also Microsoft, common in large enterprises.
• Self-hosted, using software like Gitea or Forgejo, or a plain Git server with no web interface at all. Git needs no company to function.

Why the distinction is worth money

Here is the part that matters when you are the one choosing.

Your project's entire history lives inside the repository itself, in a hidden folder that comes along whenever anyone copies it. Moving a project from one host to another is close to trivial: clone it, point it at the new host, push. Nobody holds your code hostage.

What does not move is everything the host added. Your issues, your pull requests and the discussions on them, your review history, your automation, your integrations. Those are GitHub's, in GitHub's shapes, and leaving means leaving them behind or paying somebody to convert them.

That asymmetry is the actual product. It is also the shape of most software-as-a-service lock-in you will ever evaluate: the thing you thought you were buying is portable, and the thing that accumulated around it is not. Worth recognizing the pattern here, where the stakes are low, so you recognize it later when they are not.

What Git actually does

Think about version history in a document editor: you can see what changed, who changed it, and roll back to how it looked last Tuesday. Git is that, for code, with the history treated as the important part rather than a convenience.

Because Git watches every file in a project continuously, it knows the entire history of the work: what was added, modified and deleted, by whom, and when. That is what makes the rest possible.

• Attribution. Who contributed what, and when.
• Time travel. View or return to the project as it stood at any earlier point.
• Branching. Several versions of the same project, running in parallel from a common starting point, which is how more than one person works on something at once without chaos.

Every professional software team uses version control, and in practice almost all of them use Git. Once it arrived, the alternatives largely emptied out.

Repositories

A Git project lives in a repository, usually one repository per project. Hosting repositories is GitHub's main business.

A GitHub user or organization holds repositories. Microsoft's organization on GitHub holds a great many, one of which is the source for Visual Studio Code, the editor running in your Codespace. Your own account starts empty. That changes in the first class.

There are two ways to make one:

• New repository, starting from nothing or from code already on your computer.
• Use this template, which copies an existing repository's files into a brand new repository of your own. This is the one we use most in this course, because it hands you a working starting point instead of an empty folder.

To try the second one: go to github.com/dsgn425-fall2026/hello-world, click Use this template, name the new repository hello-world, leave it private, and create it. It lands in your own account in a few seconds, and it is yours.

Have a look around. A repository is a folder of files and folders, and not much more than that. You can add files and edit the ones already there, right in the browser.

Your first commit

Open README.md and click the pencil icon. Change anything you like, then scroll to the bottom, to Commit changes.

The first box is the commit message. It is pre-filled with something generic like Update README.md, and you should replace it. The message is a note to your future self and to everybody else about what you did and why, and it is the single most durable piece of communication in a codebase. Write what changed, in plain words.

Click Commit changes. That takes the work you did, one coherent unit of it, and records it permanently in the repository's history.

A commit is a snapshot with a note attached. That is the whole idea, and everything else in Git is built out of it.

Committing straight onto the project the way we just did is fine when the project is yours and nobody else is touching it. It stops being fine immediately after that, and the rest of this post is what teams do instead.

Branches

A repository has a main line of development, called main. Treat it as the official version: the one that works, the one that ships, the one everybody else pulls from.

A branch is a separate line of work that starts from a copy of main at a moment in time. You make one, you do your work on it, and main is untouched the entire time. When the work is done and somebody has looked at it, the branch gets folded back in.

The reason this matters is not tidiness. It is that real work is unfinished for most of its life. Halfway through a change, the code is usually broken, and if you were working directly on main then everybody else is now working on top of your broken half-change. A branch is where something is allowed to be incomplete without hurting anyone.

The second reason is that several people can do this at once. Five people on five branches are not in each other's way, and the question of how their work fits together gets asked once, deliberately, when each branch comes back, instead of continuously and by accident.

Two practical notes. Name the branch for what you are doing, not for yourself: add-search tells a colleague something, bens-branch does not. And do not hoard a branch. The longer it sits away from main, the more main moves underneath it and the harder the eventual reconciliation gets. Branches are cheap and meant to be short-lived, which is the opposite of
the instinct most people arrive with.

Pull requests

Here is a distinction worth holding onto, because it follows the Git and GitHub split from the top of this post: branches and commits are Git. Pull requests are not.

A pull request is a GitHub feature, built on top of Git. It is a request, addressed to the people who own the project, saying: I did this work on my branch, please pull it into main. GitLab calls the same thing a merge request, which is arguably the better name.

It is three things at once, and the third is the one people miss.

1. A proposal. Nothing has happened yet. main is unchanged until somebody merges.
2. A place to talk. The pull request shows the diff: every line added, removed or changed. Anyone can comment on the whole thing, or on one specific line. Conversation happens against the actual change rather than in a meeting about the change.
3. A record. It survives. In two years, when somebody asks why a thing works the way it does, the pull request is where the answer is: what changed, who asked for it, what the objections were, and who decided. Most of the institutional memory of a codebase lives in old pull requests.

On any serious team, main is protected, which means nobody can push to it directly, not even the person who owns the company. The pull request is not the polite option. It is the only door.

Code review

Somebody other than the author has to look at the change and say yes. That is code review, and it is the part of this that will follow you furthest, because you may well end up on the approving side of it without ever writing the code yourself.

A reviewer opens the Files changed tab and answers three questions:

1. Does the change do what the pull request says it does?
2. Does the commit message tell you what changed without opening the diff?
3. Would you merge this?

Then they leave at least one comment on a line, and submit the review as either Approve or Request changes. If they requested changes, the author fixes it on the same branch and commits again, the pull request updates itself, and the reviewer looks again. That loop is the job.

Notice what is not on that list. Nothing about whether the code is clever, and nothing that requires you to be able to have written it yourself. Review is mostly about intent: does this do what it claims, would somebody understand it later, and am I willing to put my name on letting it in. Those are judgment questions, and they are the same questions you would ask about anything else your team proposes to ship.

The checklist grows as the course goes on. The three questions do not change.

One rule that is worth understanding rather than memorizing: GitHub will not let you approve your own pull request. Nobody reviews their own work. That is not a technicality, it is the whole point of the mechanism.

The loop, in one place

Every change to every serious codebase goes through some version of this.

1. Branch. Off main, named for what you are about to do.
2. Change. On the branch, where being half-finished is safe.
3. Commit. One coherent unit of work, with a message that says what it was.
4. Publish. The branch goes up to GitHub.
5. Pull request. The proposal to change main.
6. Review. Somebody else reads it and says yes, or says not yet.
7. Merge. It lands on main.
8. Delete the branch. It served its purpose.

You will run that loop by hand a few times in this course until it is boring. That is the intended outcome. The mechanics are meant to become automatic so that your attention can go to the only part that actually needs a person: step 6.

Where this goes next

• Practice: GitHub to run the whole loop once on your own, with nobody else's repository at risk.
• Open source and licensing, the longer version, if you came here from the class and skipped it.
• Command-line basics and the development environment for what comes after the browser.

PREPARING YOUR APPLICATION

In your Gemfile, it may already look like it does below (specifically with the sqlite3 gem nested inside a group :development block and a pg gem nested inside a group :production block).  If not, delete gem "sqlite3", "~> 1.4" and add the following:

group :development, :test do
  gem "sqlite3", "~> 1.4"
  # note: keep other gems that were already in this group (e.g. "debug")
end

group :production do
  gem 'pg'
end

Note: there was likely already a group :development, :test block in your code. If so, keep that code and just add the sqlite3 gem to that group.  Don't delete any other gems in the group.  For example, your code may now look like this:

group :development, :test do
  gem "debug", platforms: %i[ mri mingw x64_mingw ]
  gem "sqlite3", "~> 1.4"
end

Once you've moved the sqlite3 gem and added the pg gem, execute the change in your Gemfile by running:

bundle install

These changes tell your Rails application to use the industrial-strength PostgreSQL database in production, instead of the sqlite3 database we've been using for development.  The database will behave the same for our purposes, but is ready for production-level traffic and future scalability.

Next, you'll need to update the config/database.yml file.  With the pg library installed for production, we also need to configure the app so that it knows where to find the production database.  Currently, it lives in a file in the db directory (db/development.sqlite3), but in production the database is managed separately.

If it's not already there, add the following to the config file:

production:
  <<: *default
  adapter: postgresql
  database: my_app_production
  username: my_app
  password: <%= ENV["MY_APP_DATABASE_PASSWORD"] %>

Replace my_app in the example with the name of your app - in this case tacogram_final would be appropriate.  Be sure that the database value still has production appended to it.  And MY_APP in the password should be TACOGRAM_FINAL in all caps.  As an example, it might look like this:

production:
  <<: *default
  adapter: postgresql
  database: tacogram_final_production
  username: tacogram_final
  password: <%= ENV["TACOGRAM_FINAL_DATABASE_PASSWORD"] %>

Last step is to commit these changes and push them to Github.

RENDER SETUP

Sign up for a Render account at render.com - use your Github credentials if possible.  You may have to enter payment info, but you'll be using the free tier.  During signup, you'll be asked several questions about how you will use Render - you can answer them however you'd like.  Once complete, you'll be on your Render dashboard.

You'll need to create 2 services - one for the database and one for the application.   Create the database service first.  Click "Create PostgreSQL" on your dashboard (or using the "New +" button in the top right).

In the form, enter a unique name for your new database - it's helpful to use your application name followed by "-db" to easily identify it later.  For example, it might be tacogram-final-db.

Ignore the other settings in the form until the Instance Type at the bottom.  Select the "Free" type and click "Create Database".  Note: Render only permits 1 free database service.  When you want to create a database for another application, you'll first need to suspend/delete this one (or upgrade to a paid plan).

On the next screen, wait for the database service to complete its setup.  Once it's available, scroll to the "Connections" section, find the "Internal Database URL" value and copy it - you'll need it in the next step.

Next, go back to your dashboard and click the "New +" button in the top right to create a new Web Service.

When asked "How would you like to deploy your web service?", choose "Build and deploy from a Git repository".  On the next screen, you'll be asked to "Connect a repository" - you should see your Github repository listed, but if not, click "Configure account" on the right to authorize access to your Github account.  Once you've found your application's repository in the list, click "Connect".

In the following form, the "Name" field will be pre-populated for you based on the name of your application.  However, this must be unique across all of Render, so you may need to change the name slightly (e.g. tacogram-final may already be taken).  You can leave the next few settings unchanged.  Then, in the "Build Command" field, enter the 2 commands that should run each time you deploy a new version of your application: bundle install; rails db:migrate (the semi-colon separates the 2 commands).

Next, change the "Start Command" field.  This is the command that will run to start your application server, just like you do in Codespace: rails server

Then, choose the free Instance Type option.

The last change is adding environment variables needed to run the application - these are variables stored on the "server" instead of in our code and generally used to run the application or for sensitive data we don't want to store in our code (e.g. API keys).  The variables you'll need to add are:

• DATABASE_URL
• SECRET_KEY_BASE
• WEB_CONCURRENCY

The DATABASE_URL value should be the "Internal Database URL" value copied from the database service.  The SECRET_KEY_BASE value can be anything - your application name will work.  And, the WEB_CONCURRENCY value should be 2.

That's it.  You can click "Create Web Service" to complete the setup.  Render will pull your latest code from Github and begin to install all of the necessary libraries.  It will automatically run bundle install and rails db:migrate.  And then lastly, it will run rails server.  If all goes well, you'll see "Your service is live 🎉" in the logs.

From now on, each time you push new code from Codespace to Github, it will be deployed to Render as well.  You can see any deployment events listed in the "Events" tab.

Congratulations!  Your app should now be live!  There's a link to visit your live production site towards the top.  Note the url for future reference - it will be something like my_app.onrender.com where "my_app" is the unique name you entered previously (e.g. tacogram-final-tues).

DEBUGGING

Render is really just a big, industrial Linux-based computer – just like Codepsace, but more powerful.  It's worth exploring the features of your Render services to discover what's possible.  One feature that's very useful is access to the logs (see the "Logs" tab just below the "Events" tab in Render.  As users interact with your application, you will see the output to the server log there.  Just as you did with the rails server log in Codespace, reading these logs will be helpful when you inevitably find a bug in the live app.  You won't see the usual error message that you've seen in local development - that would be a bad user experience.  Instead you'll see a simple 404 error screen with no details about what went wrong.  

To see the actual error message, you'll need to look at the server log.  Render makes this especially easy by providing search and filter functionality for your logs.

CUSTOM DOMAINS

If you're interested in adding a custom domain to your application (e.g. tacogram-final.com instead of tacogram-final.onrender.com), you'll first need to purchase a domain (or use a domain you already own) at a domain registry provider like Namecheap.  Then follow Render's instructions to configure the domain to point to your server on Render.