[Case study: Databases·]

CS 330 databases review tool.

A study app I built for my databases course. It turns the course's published question bank, 280 questions across 52 cards, into spaced-repetition practice: auto-graded multiple choice for concepts, and write-it-from-memory for SQL and other code. No account, no backend, and nothing leaves your browser.

Vanilla JSSpaced repetitionSQL practiceMock examsTested on MySQL 8.4No account

Try it live →All projects

The questions and their reference answers are from the CS 330 course's own question bank, published by the instructor, Dr. Stephen Foster, on the CS 330 course homepage. I did not write them. This is an independent study aid and is not affiliated with or endorsed by the course or its instructor.

[Why I built it·]

A course built on recall needs a recall tool.

CS 330 is an asynchronous databases course. The material is 52 flashcard-style cards, and the grade rests on check-ins and two exams where you write queries and explain concepts from memory. The instructor publishes a bank of practice questions with answers, written for paper exams. A list of questions can't tell you which ones to repeat, so I built something that can.

The tool quizzes you, tracks what you miss, and brings those questions back sooner. It follows the course's own card order, so the first unit is the first thing the course teaches, and the check-in ranges (cards 1–12, 1–36, all 52) are one click each.

It also taught me something I did not plan on. Running the course's example SQL on a real MySQL server showed several places where the generic SQL on the cards behaves differently in practice. That list is further down, and it is in the tool's Reference tab too.

[Coverage·]

What the question bank contains.

The course comes in runs of four cards (what is it, two syntax cards, then a fluency card), so a unit is one run. In guided mode a unit unlocks when you have mastered the one before it.

Unit	Topic	Cards	Questions
1	Course overview	1–4	20
2	Tables and queries (SELECT, WHERE, ORDER BY, LIMIT)	5–8	23
3	Relationships and foreign keys	9–12	21
4	JOINs	13–16	21
5	Normalization and anomalies	17–20	21
6	Normal forms and many-to-many	21–24	22
7	Keys and constraints	25–28	21
8	INSERT, UPDATE, DELETE	29–32	22
9	GROUP BY and HAVING	33–36	20
10	Subqueries and set operations	37–40	22
11	Document and key-value stores	41–44	22
12	Graph databases	45–48	22
13	Apps and choosing a database	49–52	23
Σ	Total questions		280

Questions and reference answers: the CS 330 homepage by Dr. Stephen Foster.

[Question types·]

Auto-grade what you can, make you type what you must.

Concepts, definitions, tracing (“what does this return?”), debugging, and “which database would you pick” questions are multiple choice (149) or select-all (12), graded the moment you check. Select-all is all or nothing: miss one option or pick a wrong one and it counts as a miss.

The 119 questions whose answer is code stay typed, because SQL has to be written accurately anyway. You write it from memory, reveal the reference answer, and grade yourself. A keyword check lists the clauses in the reference that your answer left out, for example no LIMIT. It is a hint, never a grade, since a query can contain every keyword and still be wrong.

My first draft of the multiple-choice options had a flaw. The correct option was the longest one in 74% of the questions, so picking the longest answer would have scored well without knowing anything. I rewrote the options so the correct one is the longest about 16% of the time (random is 25%), and the tool's validator now fails if that bias comes back. Options are also reshuffled on every showing, and the wrong ones are drawn from a larger pool, so you have to read all of them each time.

[Engineering, not just content·]

A spaced-repetition scheduler.

Three published methods, combined:

Leitner boxes. Each question sits in one of six boxes. A correct answer promotes it to a longer interval. A miss drops it to box 1 so it comes right back.
Streak mastery. A question counts as mastered after three correct answers in a row, so one lucky guess never sticks.
Mastery-gated units. The next unit unlocks at about 90% mastery of the current one. Earlier units keep mixing in, weighted toward what you have missed.

Inside a unit there is a difficulty ramp: concept questions first, then syntax, tracing, writing, debugging, and design. A tier opens once you have recalled most of the one below it. The snippet is the real weighting function, and the scheduler, the gating, the ramp, and the answer-format rules all have tests.

engine.js
TESTED
1
2
3
4
5
6
7
8
9
10
11
12
// Weight for picking the next question from the pool.
function weight(q) {
  var s = progress[q.id];
  var box = s ? (s.box || 1) : 1;
  var w = ({ 1: 12, 2: 7, 3: 4, 4: 2.5, 5: 1.5, 6: 0.8 })[box] || 1;
  if (!s || s.seen === 0) w = (q.unit === focusUnit) ? 9 : 3;
  if (s && s.everWrong && !s.mastered) w *= 1.9;   // past mistakes
  if (s && s.mastered) w *= 0.5;                   // keep mastered ones in rotation, lightly
  w *= (q.unit === focusUnit) ? 1.6 : (q.unit < focusUnit ? 0.8 : 0.45);
  if (s && s.lastSeen != null && (now - s.lastSeen) < 2) w *= 0.15; // no echoes
  return Math.max(w, 0.01);
}

[Tested against a real database·]

The docs say one thing. The server does another.

The course teaches generic SQL, and its answers are correct as SQL. I also run everything I practice on a local MySQL 8.4 server, and several of the answers behave differently there. I checked each of these on the server instead of trusting memory.

The tool still grades you against the course's own syntax, because that is what the exams use. The Reference tab keeps the MySQL notes separate, for when you are running queries in Workbench.

Inline REFERENCES is ignored

A column written as deck_id INTEGER REFERENCES decks(id) creates no key at all. A card that points at a deck that does not exist inserts without an error.

TEXT cannot be UNIQUE

email TEXT UNIQUE fails with error 1170. The column has to be a VARCHAR with a length.

SET NOT NULL is a syntax error

ALTER COLUMN ... SET NOT NULL does not exist in MySQL. It uses MODIFY and restates the column type.

INNER JOIN without ON is not an error

The course calls a missing ON clause a mistake. MySQL accepts it and returns every pairing, 12 rows from a 3-row and a 4-row table.

foreign_key.sql
VERIFIED
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- As the course writes it. MySQL parses the
-- REFERENCES, then ignores it: no key exists.
CREATE TABLE cards (
  id INTEGER PRIMARY KEY,
  deck_id INTEGER REFERENCES decks(id)
);
INSERT INTO cards VALUES (1, 99);  -- succeeds
 
-- What MySQL needs to enforce it:
CREATE TABLE cards (
  id INTEGER PRIMARY KEY,
  deck_id INTEGER,
  FOREIGN KEY (deck_id) REFERENCES decks(id)
);
INSERT INTO cards VALUES (1, 99);
-- ERROR 1452: Cannot add or update a child row

[How it runs·]

Static files. No backend, no account.

The whole tool is vanilla JavaScript plus one small library (marked) for rendering the course's markdown. It makes no network requests, so nothing about your answers goes anywhere. Progress is saved in your browser, and Settings can export it as a file and import it back.

It also has a timed mock exam. You answer a mixed sheet, the choice questions are graded for you, you mark the written ones against the key, and it keeps your scores as a history without touching your mastery progress.

Open the live build →

The live build at /cs330/ is the same tool with this site's corona palette and a brand strip across the top.

[Work with me·]

If your data model matters, let's talk.

I build small production systems end to end, and I care about the part that usually gets skipped: checking that the thing behaves the way the docs say it does. If you have a database that needs to hold up, I'm happy to look at it.

Start a project →See other projects