Backend Development Databases and Data Modeling Syllabus

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.

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.

Explain the roles of PARTITION BY and ORDER BY within an OVER() clause.

PARTITION BY divides rows into independent groups within which the window function is computed separately (like a per-group reset). ORDER BY defines the ordering of rows inside each partition, which matters for ranking and running/cumulative calculations.

Differentiate the ranking window functions ROW_NUMBER, RANK, and DENSE_RANK.

ROW_NUMBER assigns a unique sequential number regardless of ties. RANK gives tied rows the same rank but leaves gaps afterward (e.g., 1,1,3). DENSE_RANK gives tied rows the same rank with no gaps (e.g., 1,1,2).

What do the LAG and LEAD window functions do?

LAG(col, n) returns the value of the column from n rows before the current row within the partition; LEAD(col, n) returns the value n rows after. They are used to compare a row with previous or following rows, e.g., period-over-period differences.

What is a database view, and what distinguishes a materialized view from a standard view?

A view is a named, stored SELECT query that behaves like a virtual table; it holds no data and is computed on access. A materialized view physically stores the query result on disk and must be refreshed to reflect underlying data changes, trading freshness for read speed.

What is a stored procedure and how does it differ from a function in SQL?

A stored procedure is a precompiled set of SQL statements stored in the database and invoked with CALL/EXECUTE; it can perform actions and return multiple results via output parameters. A function returns a single value (or table) and can typically be used inside SQL expressions.

What is a database trigger?

A trigger is a stored procedure that automatically executes in response to a table event—BEFORE or AFTER an INSERT, UPDATE, or DELETE. Triggers are used to enforce complex rules, maintain audit logs, or keep derived data consistent.

In Entity-Relationship modeling, define entity, attribute, and relationship.

An entity is a real-world object or concept about which data is stored (becomes a table). An attribute is a property of an entity (becomes a column). A relationship is an association between entities (e.g., 'a customer places an order'), often implemented via foreign keys.

What are the three cardinality types of relationships in ER modeling?

One-to-one (1:1), one-to-many (1:N), and many-to-many (M:N). A 1:N is implemented with a foreign key on the 'many' side; an M:N requires a junction/associative table holding foreign keys to both entities.

What is a weak entity in ER modeling?

A weak entity cannot be uniquely identified by its own attributes alone and depends on a related 'strong' (owner) entity. Its full key is a partial key combined with the owner's key, and it is connected by an identifying relationship.

Define First Normal Form (1NF).

A relation is in 1NF if every attribute holds only atomic (indivisible) single values, there are no repeating groups or arrays within a column, and each row is unique. Essentially, no multivalued or composite cells.

Define Second Normal Form (2NF).

A relation is in 2NF if it is in 1NF and every non-key attribute is fully functionally dependent on the whole primary key—there are no partial dependencies on part of a composite key. It becomes relevant only when the primary key is composite.

Define Third Normal Form (3NF).

A relation is in 3NF if it is in 2NF and has no transitive dependencies—no non-key attribute depends on another non-key attribute. Every non-key attribute must depend only on the primary key, informally 'the key, the whole key, and nothing but the key.'

What is Boyce-Codd Normal Form (BCNF) and how does it strengthen 3NF?

BCNF requires that for every non-trivial functional dependency $X \to Y$, $X$ must be a superkey. It is stricter than 3NF because it removes anomalies that remain when a non-key attribute determines part of a candidate key.

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

A functional dependency $X \to Y$ means that each value of attribute set $X$ uniquely determines the value of attribute set $Y$; whenever two tuples agree on $X$ they must agree on $Y$. Functional dependencies are the basis for normalization.

What update anomalies does normalization aim to eliminate?

Insertion anomalies (cannot add data without unrelated data), update anomalies (redundant data must be changed in many places, risking inconsistency), and deletion anomalies (removing a row unintentionally loses other needed facts).

What is denormalization and what are its main tradeoffs?

Denormalization deliberately introduces redundancy (e.g., duplicated columns, precomputed aggregates) to reduce expensive joins and speed reads. The tradeoff is faster reads versus slower/complex writes, extra storage, and the risk of data inconsistency that must be managed.

When is denormalization typically justified over a fully normalized schema?

When read performance is critical and reads vastly outnumber writes, when joins are expensive at scale, or in analytical/reporting (OLAP) systems and caches. Normalized schemas are preferred for write-heavy transactional (OLTP) systems needing integrity.

What is a schema migration and why is it version-controlled?

A schema migration is a controlled, incremental change to the database structure (adding tables/columns, altering types, adding indexes). It is version-controlled so changes are reproducible, ordered, reviewable, and applied consistently across environments alongside application code.

What is the difference between an 'up' migration and a 'down' (rollback) migration?

An 'up' migration applies a schema change to move the database forward to a new version. A 'down' migration reverses that change to roll back to the previous version. Paired scripts allow safe forward and backward transitions.

Describe the expand-and-contract (parallel change) pattern for zero-downtime migrations.

Expand: add the new schema element (e.g., new column) without removing the old. Then deploy code that writes to both and backfill data. Contract: once all code uses the new element, remove the old one. This avoids breaking running application versions.

What is a document store, and how does MongoDB represent data?

A document store is a NoSQL database that stores semi-structured, self-contained documents. MongoDB stores documents as BSON (binary JSON) with flexible schemas, grouped into collections; related data can be nested (embedded) rather than split across joined tables.

In MongoDB, contrast embedding documents versus referencing them.

Embedding nests related data inside a single document for fast atomic reads of a whole entity (good for one-to-few, tightly coupled data). Referencing stores an ObjectId pointer to another document and requires a lookup/join, preferred for large, shared, or independently growing data.

What is Redis and what core data structures does it provide?

Redis is an in-memory key-value store known for very low latency. Beyond simple strings, it supports rich value types: lists, sets, sorted sets (with scores), hashes, bitmaps, HyperLogLogs, and streams, making it useful for caching, queues, leaderboards, and counters.

Name common use cases where Redis is chosen as a key-value store.

Caching (with TTL expiry), session storage, rate limiting, real-time leaderboards (sorted sets), pub/sub messaging, distributed locks, and job queues. Its in-memory design gives speed but limits total data to available RAM (with optional persistence).

What kind of database is Cassandra, and what is its data model built around?

Cassandra is a wide-column (column-family) NoSQL store designed for massive write throughput and horizontal scale. Its data model is query-driven: tables are organized around a partition key (which determines data distribution) plus clustering columns that order rows within a partition.

How does Cassandra achieve high availability and scalability architecturally?

Cassandra uses a masterless (peer-to-peer) ring architecture with consistent hashing to partition data across nodes, tunable replication factor, and tunable consistency levels (e.g., ONE, QUORUM, ALL). There is no single point of failure, favoring availability and partition tolerance.

What is a graph database like Neo4j optimized for, and what are its core elements?

A graph database is optimized for highly connected data and relationship traversal. Neo4j stores nodes (entities) and edges/relationships (typed, directed connections), both carrying properties. It excels at queries like shortest path, recommendations, and social/network analysis using the Cypher query language.

According to the CAP theorem, what tradeoff must a distributed database make during a network partition?

CAP states a distributed system can guarantee at most two of Consistency, Availability, and Partition tolerance. Since partitions are unavoidable, during one the system must choose between Consistency (reject/stale-free) and Availability (respond, possibly with stale data)—i.e., CP versus AP.

List key criteria for choosing SQL (relational) versus NoSQL databases.

Choose SQL for structured data, complex queries/joins, strong ACID transactions, and stable schemas (e.g., PostgreSQL, MySQL). Choose NoSQL for flexible/evolving schemas, very large scale, high write throughput, or specialized access patterns—document, key-value, wide-column, or graph—often trading strict consistency for scalability.