Examius

🌍 Back-end Web Developement · subject

Back-end Web Developement Databases Syllabus

The Databases syllabus for Back-end Web Developement in full: 3 chapters and 9 topics, every one listed below, plus 50 Databases flashcards with answers.

3Chapters
9Topics
50Flashcards

Databases syllabus: all 3 chapters

Expand any chapter to see its topics and sub-topics.

  1. SQL Databases

     3 topics
    • MySQL
    • PostgreSQL
    • SQLite

  2. NoSQL Databases

     3 topics
    • MongoDB
    • Redis
    • Cassandra

  3. Database Design

     3 topics
    • Normalization
    • Indexing
    • Transactions

Back-end Web Developement Databases flashcards — 50 questions with 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).

Show the other 30 Databases flashcards

  1. 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.

  2. 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.

  3. 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').

  4. 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.

  5. 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).

  6. What category of database is PostgreSQL, and what is it known for?

    An open-source object-relational (SQL) database known for strict standards compliance, extensibility, and advanced features like JSONB, CTEs, and window functions.

  7. Name two features PostgreSQL supports that MySQL historically lacked.

    Native JSONB with indexing, full support for CHECK constraints, materialized views, array types, and richer window/CTE functionality (examples).

  8. What is SQLite and what makes it unusual among databases?

    A serverless, self-contained, zero-configuration relational database that stores the entire database in a single file and runs in-process rather than as a separate server.

  9. Give a typical use case for SQLite versus a client-server database.

    SQLite suits embedded/local storage (mobile apps, browsers, small tools, testing); client-server DBs suit high-concurrency, multi-user, networked workloads.

  10. How does SQLite handle data types differently from most SQL databases?

    It uses dynamic typing with 'type affinity' — a value's type is associated with the value itself, not rigidly enforced by the column declaration.

  11. What category of database is MongoDB, and what is its basic storage unit?

    MongoDB is a document-oriented NoSQL database that stores data as flexible, schema-less BSON documents grouped into collections.

  12. In MongoDB, what are the equivalents of a SQL table, row, and column?

    Collection (table), document (row), and field (column).

  13. What is BSON in MongoDB?

    Binary JSON — the binary-encoded serialization format MongoDB uses to store documents, supporting extra types like Date, ObjectId, and binary data.

  14. What category of database is Redis, and where does it primarily store data?

    Redis is an in-memory key-value NoSQL data store, keeping data in RAM for very low latency, with optional disk persistence.

  15. Name four core data structures Redis supports beyond simple strings.

    Lists, Sets, Sorted Sets (with scores), Hashes, plus Bitmaps, HyperLogLogs, and Streams.

  16. What two persistence mechanisms does Redis offer?

    RDB (point-in-time snapshots) and AOF (Append-Only File that logs every write operation for replay on restart).

  17. Give three common use cases for Redis.

    Caching, session storage, rate limiting, leaderboards (sorted sets), and pub/sub messaging or job queues.

  18. What category of database is Cassandra, and what architecture makes it highly available?

    Cassandra is a wide-column NoSQL store using a masterless, peer-to-peer (ring) architecture where every node is equal, providing no single point of failure and linear scalability.

  19. What consistency model does Cassandra follow, and what does 'tunable consistency' mean?

    Eventual consistency (AP in CAP terms); tunable consistency lets you choose per-query how many replicas must acknowledge a read/write (e.g., ONE, QUORUM, ALL).

  20. State the CAP theorem.

    A distributed data store can simultaneously guarantee at most two of: Consistency, Availability, and Partition tolerance.

  21. Under CAP, classify PostgreSQL/MySQL, MongoDB, Cassandra, and Redis by their typical tradeoff.

    Traditional RDBMS lean CP (or CA single-node); MongoDB is typically CP; Cassandra is AP; single-node Redis is CA while a partitioned cluster makes tradeoffs.

  22. What does the BASE model (the NoSQL alternative to ACID) stand for?

    Basically Available, Soft state, Eventual consistency.

  23. Contrast horizontal scaling (sharding) with vertical scaling.

    Vertical scaling adds more power (CPU/RAM) to a single server; horizontal scaling (sharding/partitioning) spreads data across many servers — NoSQL databases are designed for the latter.

  24. What is sharding in a database?

    Horizontally partitioning data across multiple servers by a shard key so each node holds only a subset, distributing load and enabling scale-out.

  25. What is a primary key versus a foreign key?

    A primary key uniquely identifies each row within a table; a foreign key is a column referencing a primary key in another table to enforce referential integrity.

  26. Compare SQL and NoSQL databases in schema and scaling.

    SQL databases use a fixed, predefined schema and scale vertically with strong ACID guarantees; NoSQL databases use flexible/dynamic schemas and scale horizontally, often favoring availability over strict consistency.

  27. What is the difference between the WHERE and HAVING clauses in SQL?

    WHERE filters individual rows before grouping; HAVING filters groups after GROUP BY aggregation.

  28. What is a database view?

    A named, stored SELECT query that presents data as a virtual table; it does not store data itself (unless materialized) and reflects the underlying tables when queried.

  29. What is MongoDB's aggregation pipeline?

    A framework that processes documents through a sequence of stages (e.g., $match, $group, $sort, $project), each transforming the stream, to compute aggregated results.

  30. What is replication in databases, and how do primary-replica (master-slave) setups typically work?

    Replication copies data across multiple nodes for availability and read scaling; the primary handles writes and propagates changes to replicas, which usually serve read queries.

Back-end Web Developement Databases FAQ

What is in the Back-end Web Developement Databases syllabus?

Back-end Web Developement Databases is split into 3 chapters: SQL Databases, NoSQL Databases and Database Design. Together they contain 9 topics.

How many chapters are in Back-end Web Developement Databases?

3 chapters, containing 9 topics. The largest chapter is SQL Databases, with 3 topics.

Are there flashcards for Back-end Web Developement Databases?

Yes — a 50-card Databases deck, printed in full in the flashcards section of this page and available in the Examius app with spaced repetition. The free plan covers one exam of your choice, including its flashcards; Examius Pro, a one-time purchase, unlocks every other exam and deck.