Backend Development Databases and Data Modeling Syllabus

Databases and Data Modeling syllabus — full chapter and topic list

Expand any chapter to see its topics and sub-topics. This is the whole examinable outline for Databases and Data Modeling in Backend Development, not a summary of it.

1. 1

   Relational Databases

   4 topics

   • Relational Model and Tables
   • Primary and Foreign Keys
   • PostgreSQL and MySQL
   • Constraints and Data Integrity
2. 2

   SQL Query Language

   5 topics

   • SELECT, INSERT, UPDATE, DELETE
   • Joins and Subqueries
   • Aggregations and Grouping
   • Window Functions
   • Views and Stored Procedures
3. 3

   Data Modeling and Normalization

   4 topics

   • Entity-Relationship Modeling
   • Normalization Forms
   • Denormalization Tradeoffs
   • Schema Migrations
4. 4

   NoSQL Databases

   5 topics

   • Document Stores (MongoDB)
   • Key-Value Stores (Redis)
   • Wide-Column Stores (Cassandra)
   • Graph Databases (Neo4j)
   • Choosing SQL vs NoSQL
5. 5

   Transactions and Concurrency

   4 topics

   • ACID Properties
   • Isolation Levels
   • Locking and Deadlocks
   • Optimistic vs Pessimistic Concurrency
6. 6

   ORMs and Query Builders

   4 topics

   • Object-Relational Mapping Concepts
   • Popular ORMs
   • Query Builders and Raw SQL
   • Connection Pooling
7. 7

   Database Performance and Scaling

   5 topics

   • Indexing Strategies
   • Query Optimization and EXPLAIN
   • Replication and Read Replicas
   • Sharding and Partitioning
   • Backups and Disaster Recovery

Databases and Data Modeling flashcards for Backend Development

22 of 51 cards from the Databases and Data Modeling deck — real questions with worked answers.

1. In the relational model, what is a relation, and what are its rows and columns called?

   A relation is a table representing a set of related data. Its rows are called tuples (records), and its columns are called attributes (fields). Each relation has a schema defining attribute names and domains (allowed value types).

2. What is a candidate key versus the primary key in a relational table?

   A candidate key is any minimal set of attributes that uniquely identifies each tuple. The primary key is the one candidate key chosen to be the main identifier; it must be unique and NOT NULL. Remaining candidate keys are called alternate keys.

3. Define a foreign key and the referential integrity rule it enforces.

   A foreign key is a column (or set) in one table that references the primary key of another table. Referential integrity requires that every foreign key value either match an existing primary key value in the referenced table or be NULL.

4. What do the referential actions ON DELETE CASCADE, SET NULL, and RESTRICT do?

   CASCADE deletes child rows when the referenced parent row is deleted. SET NULL sets the foreign key to NULL on parent deletion. RESTRICT (and NO ACTION) blocks deletion of a parent row while dependent child rows still reference it.

5. Name the five main SQL integrity constraints and what each guarantees.

   NOT NULL (value must exist), UNIQUE (no duplicate values), PRIMARY KEY (unique + not null), FOREIGN KEY (referential integrity), and CHECK (value satisfies a boolean condition). DEFAULT supplies a value when none is given.

6. What are the ACID properties of a database transaction?

   Atomicity (all-or-nothing execution), Consistency (transitions leave the DB in a valid state respecting constraints), Isolation (concurrent transactions do not interfere), and Durability (committed changes survive failures).

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

   READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. Higher levels prevent more anomalies (dirty read, non-repeatable read, phantom read) at the cost of concurrency.

8. Which concurrency anomalies are prevented at each SQL isolation level?

   READ UNCOMMITTED allows dirty, non-repeatable, and phantom reads. READ COMMITTED prevents dirty reads. REPEATABLE READ also prevents non-repeatable reads. SERIALIZABLE prevents all three, including phantoms.

9. Contrast PostgreSQL and MySQL on their default primary storage/concurrency model.

   PostgreSQL is a single unified engine using MVCC (multi-version concurrency control) for all tables. MySQL supports pluggable engines; its default InnoDB also uses MVCC and supports transactions, while the older MyISAM engine lacks transactions and foreign keys.

10. Name two features PostgreSQL supports that classic MySQL historically lacked.

    PostgreSQL has long supported advanced features like CHECK constraints enforcement, full window functions, CTEs, rich JSONB, array types, custom types, and true partial/expression indexes—many of which MySQL added only in later versions (e.g., MySQL 8.0 added window functions and CTEs).

11. Write the general syntax of a SELECT statement including the main clauses in execution-friendly order.

    SELECT columns FROM table WHERE condition GROUP BY cols HAVING group_condition ORDER BY cols LIMIT n. Logical processing order is: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

12. How do INSERT, UPDATE, and DELETE statements differ in their basic syntax and effect?

    INSERT INTO t (cols) VALUES (...) adds new rows. UPDATE t SET col = val WHERE cond modifies existing rows matching the condition. DELETE FROM t WHERE cond removes rows matching the condition. Omitting WHERE affects all rows.

13. What is the difference between DELETE and TRUNCATE?

    DELETE is a DML statement that removes rows one by one (can use WHERE, fires triggers, is logged, is transactional/rollbackable). TRUNCATE is a DDL statement that quickly removes all rows, resets identity counters, and is typically not row-by-row logged.

14. Explain the difference between an INNER JOIN and a LEFT OUTER JOIN.

    INNER JOIN returns only rows where the join condition matches in both tables. LEFT OUTER JOIN returns all rows from the left table plus matching right-table rows, filling unmatched right columns with NULL.

15. What does a FULL OUTER JOIN return, and how does a CROSS JOIN differ?

    FULL OUTER JOIN returns all rows from both tables, with NULLs where either side has no match. CROSS JOIN returns the Cartesian product—every row of the first table paired with every row of the second, producing $m \times n$ rows.

16. What is a self-join and when is it used?

    A self-join joins a table to itself using table aliases. It is used to relate rows within the same table, such as finding an employee's manager when both are rows in an employees table (e.g., e.manager_id = m.employee_id).

17. Distinguish a correlated subquery from a non-correlated (independent) subquery.

    A non-correlated subquery runs once independently and its result is used by the outer query. A correlated subquery references columns from the outer query and is conceptually re-evaluated for each outer row, e.g., using EXISTS or a comparison per row.

18. What is the difference between IN, EXISTS, and a JOIN for matching rows across tables?

    IN tests membership in a subquery's result set. EXISTS returns true if the correlated subquery yields any row (often faster, short-circuits). A JOIN combines columns and can multiply rows on many-to-many matches, whereas EXISTS/IN filter without duplicating.

19. Name the five standard SQL aggregate functions and what each computes.

    COUNT (number of rows/values), SUM (total of numeric values), AVG (arithmetic mean), MIN (smallest value), and MAX (largest value). By default they ignore NULLs; COUNT(*) counts all rows including those with NULLs.

20. What is the crucial rule relating GROUP BY to the SELECT list, and how does HAVING differ from WHERE?

    Every non-aggregated column in SELECT must appear in GROUP BY. WHERE filters individual rows before grouping; HAVING filters groups after aggregation, so HAVING can reference aggregate functions like COUNT(*) > 5 while WHERE cannot.

21. How does COUNT(*) differ from COUNT(column) and COUNT(DISTINCT column)?

    COUNT(*) counts all rows. COUNT(column) counts rows where that column is non-NULL. COUNT(DISTINCT column) counts the number of distinct non-NULL values in the column.

22. What is a window function and how does it differ from a GROUP BY aggregate?

    A window function performs a calculation across a set of rows (a 'window') related to the current row via an OVER clause, but returns a value for every row without collapsing them. GROUP BY aggregates collapse each group into a single output row.

See more Databases and Data Modeling flashcards →

Planning Databases and Data Modeling for Backend Development

Databases and Data Modeling (Backend Development) FAQ