Lab 03: Data, State, SQL

Understand what the system remembers and how to query it.

The database is the system’s memory. A schema is the filing cabinet design. A query is a question. A migration is remodeling the cabinet while people still need the files.

1. Install or use SQLite.
2. Create tables for users, tickets, comments, and audit events.
3. Insert sample records.
4. Query all open tickets.
5. Query ticket history with comments.
6. Query tickets assigned to a user.
7. Add a new column with a migration.
8. Write a rollback note.
9. Try inserting invalid data and record what happens.

create table users (
  id integer primary key,
  email text not null unique,
  name text not null
);

create table tickets (
  id integer primary key,
  title text not null,
  status text not null,
  created_by integer not null references users(id)
);

• What data must be unique?
• What data can be null?
• What relationships exist?
• What query needs an index?
• What should be immutable?
• What is the danger of deleting data?

Create data-state-sql.md with:

• Schema.
• Insert statements.
• Five queries.
• Query results.
• Migration plan.
• Rollback plan.

You can create, query, change, and explain a small relational data model.