Back-end Web Developement Databases Syllabus

Databases syllabus — full chapter and topic list

Expand any chapter to see its topics and sub-topics. This is the whole examinable outline for Databases in Back-end Web Developement, not a summary of it.

1. 1

   SQL Databases

   3 topics

   • MySQL
   • PostgreSQL
   • SQLite
2. 2

   NoSQL Databases

   3 topics

   • MongoDB
   • Redis
   • Cassandra
3. 3

   Database Design

   3 topics

   • Normalization
   • Indexing
   • Transactions

Databases flashcards for Back-end Web Developement

25 of 50 cards from the Databases deck — real questions with worked answers.

1. What does ACID stand for in the context of database transactions?

   Atomicity, Consistency, Isolation, Durability — the four guarantees that ensure reliable transaction processing.

2. Define a database transaction.

   A single logical unit of work composed of one or more operations that either all succeed (commit) or all fail (rollback), treated as indivisible.

3. What does the Atomicity property of a transaction guarantee?

   All operations in the transaction complete fully or none of them do — there are no partial results; on failure everything is rolled back.

4. What does the Isolation property of a transaction guarantee?

   Concurrent transactions execute as if they were serialized, so intermediate states of one transaction are not visible to others.

5. List the four standard SQL transaction isolation levels from weakest to strongest.

   Read Uncommitted, Read Committed, Repeatable Read, Serializable.

6. Which read phenomena does each isolation level prevent (dirty read, non-repeatable read, phantom read)?

   Read Uncommitted prevents none; Read Committed prevents dirty reads; Repeatable Read also prevents non-repeatable reads; Serializable prevents all three including phantoms.

7. What is a 'dirty read'?

   Reading data that another transaction has written but not yet committed; if that transaction rolls back, the read value never truly existed.

8. What SQL commands begin, save, and undo a transaction?

   BEGIN (or START TRANSACTION) starts it, COMMIT saves the changes permanently, and ROLLBACK undoes all changes since the transaction began.

9. What is a deadlock in a database, and how is it typically resolved?

   Two or more transactions each hold a lock the other needs, waiting indefinitely; the DBMS detects the cycle and aborts (rolls back) one transaction as the 'victim'.

10. Contrast optimistic and pessimistic concurrency control.

    Pessimistic control locks data before use to prevent conflicts; optimistic control allows concurrent work and checks for conflicts (e.g., version numbers) only at commit time.

11. What is database normalization?

    The process of organizing columns and tables to reduce data redundancy and improve data integrity by decomposing tables according to normal-form rules.

12. State the requirement of First Normal Form (1NF).

    Every column holds atomic (indivisible) values, there are no repeating groups or arrays, and each row is unique.

13. State the requirement of Second Normal Form (2NF).

    The table is in 1NF and every non-key attribute is fully functionally dependent on the whole primary key (no partial dependency on part of a composite key).

14. State the requirement of Third Normal Form (3NF).

    The table is in 2NF and has no transitive dependencies — non-key attributes depend only on the primary key, not on other non-key attributes.

15. What is Boyce-Codd Normal Form (BCNF)?

    A stricter version of 3NF requiring that for every non-trivial functional dependency $X \to Y$, $X$ must be a superkey.

16. What is a functional dependency, written $X \to Y$?

    A constraint meaning that each value of attribute set $X$ uniquely determines the value of attribute set $Y$.

17. What is denormalization and why is it used?

    Intentionally introducing redundancy by combining tables to reduce join cost and improve read performance, trading write complexity and storage for speed.

18. What is a database index and what is its main tradeoff?

    An auxiliary data structure that speeds up row lookups on indexed columns; the tradeoff is extra storage and slower INSERT/UPDATE/DELETE because the index must be maintained.

19. What data structure do most relational database indexes use, and why?

    A B-tree (or B+ tree) — it keeps data sorted and balanced, giving $O(\log n)$ search, insertion, and deletion while supporting range queries efficiently.

20. Distinguish a clustered index from a non-clustered index.

    A clustered index determines the physical order of rows (one per table); a non-clustered index is a separate structure with pointers to the rows (many allowed per table).

21. When is a hash index preferable to a B-tree index?

    For exact-match equality lookups ($O(1)$ average), but hash indexes cannot support range or ordered queries, where B-trees excel.

22. What is a composite (multi-column) index, and why does column order matter?

    An index on two or more columns; queries can use it only for the leading (leftmost) columns, so the order determines which query patterns benefit — the 'leftmost prefix' rule.

23. What is a covering index?

    An index that includes all columns a query needs, so the query is answered from the index alone without accessing the table (an 'index-only scan').

24. What category of database is MySQL, and what license/ownership does it have?

    MySQL is an open-source relational (SQL) database, owned by Oracle, widely used in the LAMP stack.

25. Name two common storage engines in MySQL and a key difference.

    InnoDB (default; supports transactions, row-level locking, foreign keys) and MyISAM (no transactions, table-level locking, faster for read-heavy legacy use).

See more Databases flashcards →

Planning Databases for Back-end Web Developement

Databases (Back-end Web Developement) FAQ