How to work through this deck

This deck covers core database systems concepts: relational design, SQL behaviour, indexes and transactions. It is a focused revision set, not a complete course. Check it against your syllabus and notes, especially where your course uses a particular DBMS or SQL dialect.

Work through the cards one at a time. Read the question, answer from memory, then turn the card and compare your answer with the key point. Don’t count a familiar-sounding phrase as a correct answer if you cannot explain the idea. Grade each card honestly: choose Again if the answer was wrong or missing; Hard if you reached it but needed substantial effort; Good if you recalled it correctly with ordinary effort; and Easy if it came quickly and completely. The four buttons help a spaced-repetition system decide when to show a card again; the exact interval depends on the system.

This MySummaries deck is built from a revision board, so use your own course notes to add a qualification or example wherever the wording in your materials is more specific. The first card is ready to turn; the other questions form the rest of the drill.

A database systems deck on this topic looks like this:

Cards — Database Systems — Core Concepts18 due

What does atomicity mean in ACID?

A transaction happens completely or not at all; a partial result is not committed.

All 18 cards
What does atomicity mean in ACID?A transaction happens completely or not at all; a partial result is not committed.
What does consistency mean in ACID?A committed transaction leaves the database satisfying its defined integrity constraints.
What does isolation mean in ACID?Concurrent transactions should not interfere in a way that exposes an invalid intermediate result; the guarantees depend on the isolation level.
What does durability mean in ACID?After a transaction commits, its changes survive a subsequent failure, subject to the system’s durability guarantees.
What must be true of a primary key?It uniquely identifies each row and cannot contain NULL values.
What does a foreign key constraint enforce?A non-NULL foreign-key value must match a value in the referenced key; whether NULL is allowed depends on the column constraint.
What is a candidate key?A minimal set of attributes that uniquely identifies a row.
What is the main requirement of first normal form (1NF)?Each row-column position holds a single value, rather than a repeating group or set of values.
What does second normal form (2NF) rule out?Partial dependency of a non-prime attribute on part of a candidate key; the relation must also be in 1NF.
State the usual functional-dependency test for third normal form (3NF).For every non-trivial dependency X → A, X is a superkey or A is a prime attribute.
What is the defining condition of BCNF?For every non-trivial functional dependency X → Y, X is a superkey.
What rows does a LEFT JOIN preserve?Every row from the left input; unmatched right-side columns are returned as NULL.
How do COUNT(*) and COUNT(column) differ?COUNT(*) counts rows; COUNT(column) counts non-NULL values in that column.
What is the purpose of HAVING in a grouped query?It filters groups after grouping and aggregation; WHERE filters input rows before grouping.
What kind of lookup is a hash index suited to?Equality lookup; a hash index is generally not suited to range searches.
What is a deadlock?A cycle of transactions waiting for resources held by one another, so none in the cycle can proceed without intervention.
What does write-ahead logging require?The relevant log record must be made durable before the changed data page is written to durable storage.
What is conflict serializability?A schedule is conflict-serializable if it is conflict-equivalent to some serial schedule.
A focused 18-card deck covering relational design, SQL, indexes and transaction concepts.

That is a MySummaries deck, filled with database systems material. Yours is written from your own notes. Start free

Learn the facts in useful groups

The deck is easier to retain if you connect each definition to a problem it helps solve. First group the transaction cards: atomicity, consistency, isolation and durability. Say what each property contributes, then contrast the two that are easiest to blur. Atomicity concerns all-or-nothing completion; durability concerns what happens after a successful commit. Isolation concerns concurrent execution, and its strength depends on the chosen isolation level. Consistency is about preserving the database’s declared rules, not about making every transaction produce a desirable business outcome.

Next, connect keys and normal forms. A candidate key is minimal; a superkey is any attribute set that uniquely identifies a row, including a candidate key with extra attributes. A primary key is the candidate key chosen as the table’s primary identifier. Functional dependencies help identify when a relation’s design can cause update, insertion or deletion anomalies. The 2NF, 3NF and BCNF cards are deliberately short definitions: practise applying each definition to a relation and its dependencies, rather than memorising the acronyms alone.

For SQL, use tiny tables of your own to test the join and aggregate cards. Make one left-side row have no match, and include a NULL in the counted column. Predict the output before running the query. This is a practical way to see why COUNT(*) and COUNT(column) differ, and why a condition on grouped results belongs in HAVING rather than WHERE.

For indexes and concurrency, attach each term to the operation it supports or the failure it addresses. A hash index is a useful contrast with a range-oriented access path. A deadlock is a waiting cycle, not just a slow query. Write-ahead logging orders durable log records before durable data-page writes, supporting recovery after a failure.

Keep a short core list

Once you have answered the deck, identify the few facts that would cause the most confusion if forgotten. These are useful to revisit before a longer practice question. Don’t treat the list as a substitute for the cards: it is a compact checklist, while the questions require you to retrieve each idea in your own words.

The deck’s Must not miss core looks like this:

Key pointsDatabase Systems — Core Concepts
ACID: atomicity is all-or-nothing; consistency preserves constraints; isolation governs concurrent effects; durability preserves committed changes.
Keys: a candidate key is minimal and unique; a primary key is unique and non-NULL; a foreign key enforces a reference to a key.
Normal forms: 2NF rules out partial dependency on a candidate key; 3NF allows a non-superkey determinant only when the dependent attribute is prime; BCNF requires every determinant to be a superkey.
SQL order of purpose: WHERE filters rows before grouping; HAVING filters groups after aggregation; COUNT(column) ignores NULL.
Concurrency and recovery: a deadlock is a cycle of waits; write-ahead logging makes the log durable before the changed data page.
The five highest-value definitions to check before moving on to longer database questions.

Turn missed answers into another retrieval

A card you miss is useful information about what to study next. If you confuse 3NF and BCNF, return to the definitions and work through a functional-dependency example from your notes. If you mix up WHERE and HAVING, write a grouped query and label which rows are removed before grouping and which groups are removed afterwards. If your course tests a particular isolation level, add a separate card for that level and verify its guarantees against your course material or the DBMS documentation.

Avoid making a card so broad that several facts are hidden in its answer. Split it if you cannot grade yourself clearly. Conversely, a card should include enough wording to distinguish similar ideas: “What does isolation mean?” is more useful when the answer mentions concurrency and the dependence on isolation level than when it merely says “transactions do not interfere”.

If a mark is lost twice, the remediation tray can prompt a targeted retry rather than sending you back through every card. For example:

Remediation tray

You lost this mark twice: for a non-trivial functional dependency X → A, what condition must hold in BCNF?

Add cardDismiss
A targeted retry for a distinction that was missed twice during revision.

Before answering, state the definition, then test it against a dependency in your notes. Grade it as Good only if you can give the full condition—X must be a superkey—not merely name the normal form. Add further tray cards only for repeated gaps; otherwise the queue can become a second, less organised deck.

A repeatable review routine

Use this short routine for each study session:

  1. Start with cards due for review and grade each response immediately after turning it.
  2. For any difficult card, explain the answer in a sentence and link it to an example from your own notes.
  3. Add a card only when it tests one answerable fact, definition or distinction. Keep DBMS-specific behaviour labelled as such.
  4. Finish by trying to recall the core list without looking. Revisit any item you could not state precisely.

A correct definition is a starting point, not proof that you can use the concept. After this deck, practise applying the normal-form rules to a relation, predicting the result of a query, or tracing a transaction schedule. Keep those worked examples alongside the cards so you can revise both precise recall and application.

How MySummaries helps

MySummaries lets you build a database systems revision board from your own material, then turn its sections into flashcards for spaced review. Use it to keep definitions aligned with your course notes and focus repeat practice on the concepts you are missing. Open MySummaries.