Exercise: The Database Glossary and Decision Guide

Build the mental model

This closing lesson does two things. First, it collects every term this course introduced into one glossary, plus a decision guide mapping common needs to database types and products. Second, it hands you a query safety checklist for any UPDATE or DELETE that touches more than one row.

The checklist exists because the most expensive database mistakes are rarely exotic. They are ordinary UPDATE and DELETE statements run against the wrong environment, with a WHERE clause that matched too many rows, no backup taken, and no transaction to roll back.

The three scenarios in this exercise ask you to apply that same checklist to situations that look ordinary on the surface, which is exactly when the checklist matters most.

QUERY SAFETY CHECKLIST -- DECISION FLOW
---------------------------------------
QUERY SAFETY CHECKLIST -- DECISION FLOW
-----
[ About to run an UPDATE or DELETE ]
        |
        v
Correct database/environment?   --no--> STOP: fix connection
        | yes
        v
Correct table confirmed?        --no--> STOP: re-check table
        | yes
        v
WHERE clause reviewed?          --no--> STOP: review WHERE
        | yes
        v
Expected row count known?       --no--> run SELECT to check
        | yes
        v
Backup/snapshot taken if needed?--no--> STOP: take one first
        | yes
        v
Transaction available/used?     --no--> wrap query in one
        | yes
        v
Tested with SELECT first?       --no--> test it first
        | yes
        v
   [ Proceed with UPDATE/DELETE ]

Connect it to a real scenario

Here is a worked answer key for applying the query safety checklist to each of the three scenarios, without assuming any single right sequence.

• The most expensive database mistakes are usually ordinary UPDATE/DELETE statements run without checking environment, WHERE clause, or row count, not exotic attacks.
• A glossary and decision guide are starting points for vocabulary and product choice, not rules — the right database for a given project still depends on its actual access patterns and constraints.
• Running the WHERE clause as a SELECT first, and confirming the row count matches expectations, is the single cheapest habit that prevents the most damage.

Query Safety Checklist — Before UPDATE/DELETE

• Correct database/environment confirmed
• Correct table confirmed
• WHERE clause reviewed
• Expected row count known
• Backup/snapshot taken if needed
• Transaction available/used where possible
• Tested with SELECT first where practical

Try the working example

function checkQuerySafety(queryPlan) {
  const steps = [
    { key: "environmentConfirmed", label: "Confirm correct database/environment" },
    { key: "tableConfirmed", label: "Confirm correct table" },
    { key: "whereClauseReviewed", label: "Review the WHERE clause" },
    { key: "expectedRowCountKnown", label: "Know the expected row count" },
    { key: "hasBackupOrSnapshot", label: "Take a backup/snapshot if needed" },
    { key: "transactionAvailable", label: "Use a transaction where possible" },
    { key: "testedWithSelectFirst", label: "Test with SELECT first where practical" },
  ];

  const missingSteps = steps
    .filter((step) => !queryPlan[step.key])
    .map((step) => step.label);

  return {
    safeToProceed: missingSteps.length === 0,
    missingSteps,
  };
}

const riskyPlan = {
  environmentConfirmed: true,
  tableConfirmed: true,
  whereClauseReviewed: false,
  expectedRowCountKnown: false,
  hasBackupOrSnapshot: false,
  transactionAvailable: false,
  testedWithSelectFirst: false,
};

const safePlan = {
  environmentConfirmed: true,
  tableConfirmed: true,
  whereClauseReviewed: true,
  expectedRowCountKnown: true,
  hasBackupOrSnapshot: true,
  transactionAvailable: true,
  testedWithSelectFirst: true,
};

console.log("Risky plan:", JSON.stringify(checkQuerySafety(riskyPlan)));
console.log("Safe plan:", JSON.stringify(checkQuerySafety(safePlan)));

For riskyPlan it logs `{"safeToProceed":false,"missingSteps":["Review the WHERE clause","Know the expected row count","Take a backup/snapshot if needed","Use a transaction where possible","Test with SELECT first where practical"]}`. For safePlan it logs `{"safeToProceed":true,"missingSteps":[]}`.

Evolutionary Database Design (Martin Fowler) — How Databases Work

Database Glossary — Common Terms

Database Product Decision Guide