GK One-Line Question & Answer

A cluster where multiple nodes simultaneously accept read and write operations; challenges include write-write conflicts (two nodes update same row simultaneously), conflict resolution strategies, increased network overhead for synchronization, and consistency vs availability trade-offs

An architecture that combines OLTP and OLAP workloads in the same database system, eliminating the need for separate data warehouse ETL pipelines; achieves this through in-memory row store for OLTP plus columnar store for OLAP, or real-time replication from row store to column store

Sharding horizontally distributes data across multiple database servers; range sharding: assigns rows based on key value ranges enabling efficient range queries but risking hot spots; hash sharding: distributes rows using hash(key) mod num_shards providing even distribution but preventing efficient range scans

The conceptual gap between object-oriented programming (inheritance, associations, identity, encapsulation) and relational databases (tables, foreign keys, joins, set-based operations); ORMs address this by providing mapping configurations that translate between object graphs and table rows

In document databases: embedding stores related data as nested sub-documents within a parent document (denormalized, one read, no joins but large documents); referencing stores related data in separate collections with explicit references (normalized, requires multiple reads but smaller documents and shared data); the choice depends on access patterns and update frequency

A storage format where data for each column is stored contiguously rather than row-by-row; Apache Parquet implements this with row groups (horizontal partitions), column chunks (vertical partitions within a row group), nested encoding (Dremel levels for nested schemas), and column statistics enabling predicate pushdown

A data model optimized for append-only time-stamped measurements with automatic time-based partitioning, compressed columnar storage (delta encoding compresses well), downsampling of old data, and specialized time functions such as interpolation, moving averages, and gap filling

Schema-on-write (traditional RDBMS): schema is defined and enforced before data is written; data must conform to schema at write time (strict, consistent but less flexible). Schema-on-read (Hadoop/data lake): raw data is stored without enforcing schema; schema is applied when data is read (flexible, accepts any data format)

A pattern that ensures atomicity between database writes and message/event publishing: write the event to an outbox table in the same DB transaction as the business data, then have a separate process reliably publish from the outbox, ensuring events are never lost even if the broker is temporarily unavailable

A hybrid data modeling methodology for enterprise data warehouses that separates hubs (business keys), links (relationships between hubs), and satellites (descriptive attributes with history); preferred when requirements change frequently, full historical tracking is needed, or multiple source systems need to be integrated incrementally

Conceptual ER: high-level diagram with entities and relationships only (no attributes or keys), technology-agnostic, used for stakeholder communication. Logical ER: adds attributes, primary/foreign keys, cardinality, normalization, still DBMS-agnostic. Physical ER: DBMS-specific implementation (actual table names, data types, indexes, constraints, partitioning)

Symbols in ER diagrams that specify how many instances of one entity can be associated with instances of another entity; main systems: Chen notation (1, N, M near relationship diamonds), Crow Foot notation (symbols on relationship lines used in most modern tools), UML class diagram notation (1, 0..1, *, 1..*), and IDEF1X notation

Entity subtyping (specialization/generalization) maps a supertype-subtype hierarchy to relational tables using: Single Table Inheritance (one table, nulls for subtype-specific columns), Table Per Type (supertype table plus subtype tables with FK to supertype), or Table Per Concrete Type (one table per concrete subtype with no joins but redundant common columns)

Surrogate key (system-generated integer/UUID): stable (never changes even if business data changes), simple (single column joins), private (no business meaning exposed); Natural key (business identifier like SSN, ISBN): meaningful, already unique, no extra column needed but may change, may be complex, may expose sensitive data. Best practice: surrogate PK with unique constraint on natural key

The set of assumptions made about the nature and categories of entities in the domain; in ER design this means deciding what constitutes an entity vs an attribute vs a relationship - different ontological choices lead to fundamentally different schemas that are logically equivalent but have different query performance and flexibility trade-offs

Codds 12 rules define what constitutes a truly relational DBMS; Rule 0 (Foundation Rule): a system that claims to be a relational database management system must be able to manage databases entirely through its relational capabilities - no special non-relational mechanisms should be required

Both are non-procedural query languages equivalent in power to relational algebra. Tuple relational calculus: variables range over tuples (rows). Domain relational calculus: variables range over individual attribute values (domains). Both are the theoretical foundation for SQL

A query language is relationally complete if it is at least as expressive as relational algebra; i.e., it can express any query that relational algebra can express. SQL is relationally complete (and more, with aggregate functions, recursion, etc.). A language that cannot express certain relational algebra operations is not relationally complete

Query Q1 is contained in Q2 (Q1 <= Q2) if for every database instance D the result of Q1 on D is a subset of the result of Q2 on D; important for: query optimization (replace Q1 with cheaper equivalent Q2), semantic caching (if cached result of Q2 contains all results of Q1 use cache), and query rewriting using materialized views

Normalizing beyond what is practically necessary, resulting in too many tables that require expensive JOIN operations for every query; symptoms include 5+ table joins for common queries, poor OLTP performance due to join overhead, complex application code to reconstruct objects, and negligible storage savings that do not justify the performance cost

Lossless join: joining decomposed relations returns exactly the original relation (no spurious tuples) - mandatory for correctness. Dependency-preserving: all original FDs can be verified in decomposed relations without joins - desirable but not always achievable with BCNF. 3NF always achieves both but BCNF may sacrifice dependency preservation for stronger redundancy elimination

Synthesis (3NF synthesis algorithm): constructs normalized relations from scratch using minimal cover of FDs; bottom-up approach that creates one relation per FD in canonical cover and ensures lossless plus dependency-preserving result. Decomposition (BCNF decomposition): starts with one large unnormalized relation and repeatedly splits it by finding violating FDs; top-down approach

Window frame defines the subset of rows within a partition considered for each calculation. ROWS: physical row offset (always a fixed number of rows). RANGE: logical value range (all rows within a specified value distance of current row's ORDER BY value, which may include many rows if duplicates exist)

GROUPING SETS allows computing multiple GROUP BY aggregations in a single query pass; ROLLUP generates subtotals and grand total for a hierarchy; CUBE generates all possible combinations of group-by columns; more efficient than multiple UNION ALL queries because the data is scanned only once

LATERAL (PostgreSQL/SQL standard) or CROSS APPLY/OUTER APPLY (SQL Server): allows a subquery or table-valued function in the FROM clause to reference columns from preceding tables in the same FROM clause; a regular derived table subquery cannot reference outer columns - LATERAL enables correlated subqueries in the FROM clause

A constraint declared with DEFERRABLE INITIALLY DEFERRED is checked at COMMIT time rather than after each statement; used for circular foreign key references, bulk loading where intermediate states temporarily violate constraints, and complex multi-step operations where the final committed state is valid

DROP TABLE fails with an error if the specified table does not exist; DROP TABLE IF EXISTS silently succeeds (no error) if the table does not exist - useful in scripts that should be idempotent (safe to run multiple times) like migration scripts or setup scripts that may run on databases where the table may or may not already exist

A column whose value is automatically derived from an expression involving other columns in the same row; restrictions include: expression must be deterministic (no RAND() or NOW()), cannot reference other generated columns, cannot reference other tables, STORED generated columns cannot be updated directly by users

INSERT IGNORE: on unique key violation, silently skips the insert and keeps the original row unchanged (no error); INSERT OR REPLACE (REPLACE INTO): on unique key violation, deletes the conflicting row then inserts the new one (old row is gone, new auto-increment value generated, ON DELETE triggers fire)

Row-level locking: only the rows being modified are locked allowing other transactions to modify different rows concurrently (high concurrency, higher overhead). Table-level locking: the entire table is locked during DML (simple, low overhead, but blocks all other DML on the table); appropriate for bulk operations

In a nested loop join the driving table (outer loop) is the table whose rows are iterated one by one with the inner table looked up for each outer row; the optimizer chooses the smaller or more filtered table as the driving table to minimize iterations; an index on the inner table's join column is critical

Moving filter conditions (WHERE predicates) to be applied as early as possible in the execution plan - ideally directly on the base table scan before the join - so that fewer rows participate in the join operation; this is one of the most impactful query optimizer transformations and can reduce join input size by orders of magnitude

When the build-side relation is too large to fit in the allocated hash join memory, the DBMS performs a grace hash join: partitions both relations to disk using the hash function (ensuring matching rows land in same partition files), then processes each partition pair independently in memory; this increases I/O but prevents out-of-memory errors

The process of maintaining records of which views depend on which base tables (and which views depend on other views), enabling DBAs to identify the impact of schema changes before making them, determine the correct order to drop/recreate objects, and automatically invalidate dependent objects when base schemas change

Deep view nesting adds query parsing and optimization overhead; the query optimizer must recursively expand all view definitions before planning; deep nesting can prevent some optimizer transformations (predicate pushdown, join reordering) if intermediate view definitions use features that block merging such as aggregates, DISTINCT, or LIMIT

The query optimizer estimates how many rows an index access will return using column statistics (histogram, distinct value count, null fraction); stale statistics cause the optimizer to generate suboptimal plans such as choosing a full table scan when a highly selective index exists or choosing an index scan when most rows match and a full scan would be faster

Index range scan: uses the B-tree to navigate to the start of a range and reads sequentially until the range end; efficient for selective range conditions. Full index scan: reads all leaf pages of the index from start to end; used when ORDER BY matches index, covering index answers query without heap access, or for small tables

A compensation transaction is a semantically inverse operation that undoes the effects of a previously committed transaction when a later step in a distributed saga or workflow fails; unlike physical undo (only possible for uncommitted transactions), compensation is a new positive transaction that reverses the business effect such as issuing a refund to compensate for a completed charge

The lost update problem: T1 reads value X=100, T2 reads value X=100, T1 writes X+=50 (X=150), T2 writes X+=30 (X=130 based on its stale read) - T1 update is lost. Prevented by: REPEATABLE READ (read locks prevent T2 from reading until T1 commits), SERIALIZABLE, or optimistic locking (T2 update fails because X changed since T2 read it)

Lock contention occurs when multiple transactions compete for the same locks causing waiting and reducing throughput; strategies to reduce it: shorter transactions, access patterns that minimize lock scope, optimistic locking, MVCC (readers never block writers), partitioning hot data across shards, and batch versus row-level updates