# Revision and Exam Questions — Database Fundamentals

Source: https://www.skillbyai.com/en/database-fundamentals/p-revision

> Recall database fundamentals quickly for university exams and interviews.

## Cheat sheet

**DBMS vs files**: redundancy, inconsistency, integrity, atomicity, concurrency and security problems solved by a DBMS. **Architecture**: external, conceptual, internal levels; physical and logical data independence; schema vs instance. **ER model**: entities, attribute types, relationships, cardinality (1:1, 1:N, M:N), total/partial participation, weak entities, ISA, aggregation; mapping rules (M:N → junction table). **Relational model**: relation, tuple, attribute, domain, degree, cardinality; super, candidate, primary, alternate, foreign keys; entity and referential integrity. **Relational algebra**: σ, π, ρ, ∪, −, ∩, ×, ⋈, outer joins, ÷. **SQL**: DDL, DML, DCL, TCL; logical order FROM-WHERE-GROUP BY-HAVING-SELECT-ORDER BY; NULL semantics. **Normalisation**: FDs, Armstrong's axioms, closures, candidate keys; 1NF, 2NF (no partial), 3NF (no transitive), BCNF (every determinant a super key); lossless join test; dependency preservation. **Transactions**: ACID, states, schedules, conflict serializability via precedence graphs, recoverable and cascadeless schedules; anomalies and isolation levels. **Concurrency**: S/X locks, 2PL, strict 2PL, deadlocks, wait-die, wound-wait, timestamps, optimistic control, MVCC. **Recovery**: WAL, checkpoints, undo/redo, ARIES. **Storage**: pages, buffer pool, B+ trees, hash indexes, clustered vs secondary, composite and covering indexes; query optimisation and join algorithms. **Distributed**: CAP, BASE, replication, sharding, 2PC.

## Common exam and interview questions

Practise answering each with a definition and an example.

```text
1. Explain the three-schema architecture and the two kinds of data independence.
2. Differentiate super key, candidate key, primary key and foreign key with an example.
3. Convert an ER diagram with a weak entity and an M:N relationship into tables.
4. Write relational algebra and SQL for a "for all" query using division.
5. Given R(A,B,C,D) and a set of FDs, find all candidate keys and the highest normal form.
6. Decompose a relation into BCNF and check whether it is lossless and dependency preserving.
7. Explain ACID with a bank transfer example.
8. Test a schedule for conflict serializability using a precedence graph.
9. Compare 2PL, timestamp ordering and MVCC; explain wait-die and wound-wait.
10. Explain WAL and how a DBMS recovers after a crash.
11. Why are B+ trees preferred for database indexes?
12. State the CAP theorem and give examples of CP and AP systems.
```

## Show your working

In normalisation and serializability questions, examiners award marks for the steps: list the FDs, compute closures, draw the precedence graph. A correct final answer with no working often scores less than clear steps with a small slip.

**Quiz:** A relation is in 3NF but not in BCNF. What must be true?

- [ ] It has repeating groups
- [ ] It has no candidate keys
- [ ] It contains a partial dependency of a non-prime attribute
- [x] Some FD X → A has a non-super-key X where A is a prime attribute

*Answer:* Some FD X → A has a non-super-key X where A is a prime attribute. 3NF allows X → A with A prime even when X is not a super key; BCNF does not.