GK One-Line Question & Answer

Static SQL: SQL statements written directly in PL/SQL code at compile time (SELECT col INTO var FROM t WHERE id=x); validated and optimized at compile time gives better performance and error detection. Dynamic SQL (EXECUTE IMMEDIATE NDS): SQL constructed as strings at runtime; necessary when table/column names or statement type are not known until runtime but has higher overhead and risk of SQL injection if not using bind variables

FORALL sends the entire collection of DML operations to the SQL engine in a single context switch (one round-trip) vs. a regular FOR loop which sends each DML operation individually (one round-trip per iteration); FORALL is typically 10-100x faster for bulk DML because it eliminates N-1 context switches for N rows

A PL/SQL record type is a composite data type that groups related fields of different data types into a single structure (similar to a struct in C or row in a table); a collection is an ordered set of elements all of the same type. Records group heterogeneous fields; collections group homogeneous elements

DBMS provides built-in concurrency control ensuring consistent simultaneous multi-user access; file systems provide no such mechanism leading to race conditions and data corruption under concurrent access

The process of transforming a conceptual ER model into a concrete relational schema following systematic rules for entities, relationships, and attributes

An architectural pattern separating applications into presentation tier (UI), application/logic tier (business rules), and data tier (DBMS); enables independent scaling, better security, and technology flexibility per tier

The process of copying and maintaining database objects in multiple databases simultaneously; primary use cases: high availability (failover replica), read scalability (read replicas), geographic distribution, backup, and analytics reporting on replica

A group of database servers working together to provide higher availability, scalability, and fault tolerance than a single instance; includes shared-nothing clusters (sharding), shared-disk clusters (Oracle RAC), and active-passive failover clusters

A software layer between the application code and the database that abstracts specific database implementation details allowing the application to work with multiple database systems or switch databases without major code changes; examples include ORM frameworks and database adapter libraries

The process of transferring data between storage types, formats, or systems while ensuring data integrity, consistency, and completeness; key challenges include schema mapping differences, data type incompatibilities, referential integrity violations, handling NULL values, and performance for large datasets

Hot standby: a replica that is continuously synchronized and can accept connections immediately on failover (seconds); warm standby: synchronized but not fully ready until activated (minutes); cold standby: requires restoration from backup (hours)

A technique that maintains a pool of pre-established database connections that can be reused by application threads, avoiding the expensive cost of creating and destroying a new connection for each request; solves connection overhead, connection limits, and connection storms under high load

A pattern where write operations go to the primary database and read operations go to one or more replica databases; trade-off: replicas may lag behind primary so reads may return slightly stale data - applications must decide which reads require strong consistency versus which can tolerate stale data

A middleware component that sits between the application and the database server providing connection pooling, query routing (read-write splitting), load balancing, query caching, query filtering/firewall, and transparent failover without application changes

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