Back-end Web Developement Databases Flashcards

24 sample cards from the Databases deck

Sampled from the end of the deck, so these are different cards from the ones shown on the syllabus page.

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

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

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

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

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

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

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

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

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

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

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

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

11. Give three common use cases for Redis.

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

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

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

14. State the CAP theorem.

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

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

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

    Basically Available, Soft state, Eventual consistency.

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

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

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

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

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

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

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

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

What this deck covers

Databases flashcards FAQ