Database Management Systems MCQ

Explanation

A database is a structured collection of data organized for efficient retrieval, modification, and management. A DBMS manages the database, providing abstraction over raw storage.

Explanation

A primary key ensures row uniqueness. It cannot be NULL and must be unique. A table can have only one primary key (though it may span multiple columns — composite key).

Explanation

Foreign keys enforce referential integrity: the value must exist in the referenced primary key column. They define relationships between tables (one-to-many, many-to-many via junction tables).

Explanation

SQL (Structured Query Language) is the standard language for relational database operations: querying (SELECT), inserting (INSERT), updating (UPDATE), deleting (DELETE), and defining schemas (DDL).

Explanation

SELECT * retrieves all columns. FROM users specifies the table. Adding WHERE, ORDER BY, LIMIT refines the result set. SELECT * is avoided in production due to performance and coupling concerns.

Explanation

Normalization applies normal forms (1NF, 2NF, 3NF, BCNF) to eliminate data redundancy. It prevents update, insertion, and deletion anomalies.

Explanation

DDL: CREATE TABLE, ALTER TABLE, DROP TABLE — changes database structure. DML: SELECT, INSERT, UPDATE, DELETE — works with data. DCL (GRANT/REVOKE) controls permissions.

Explanation

JOINs combine rows based on matching column values. INNER JOIN returns only matching rows. LEFT JOIN returns all left-table rows plus matching right rows. RIGHT JOIN is the reverse.

Explanation

INNER JOIN: intersection of both tables. LEFT (OUTER) JOIN: all left table rows, with NULLs where no right-table match. Used when you want all records from one table regardless of matches.

Explanation

Indexes (B-tree by default in most RDBMS) speed up WHERE, JOIN, and ORDER BY on indexed columns. They consume storage and slow down writes — add them for frequently queried columns.

Explanation

Atomicity: all-or-nothing. Consistency: valid state before and after. Isolation: concurrent transactions don't interfere. Durability: committed transactions survive failures.

Explanation

Transactions group operations (e.g., debit one account, credit another). BEGIN/START TRANSACTION...COMMIT persists changes. ROLLBACK undoes all changes if any step fails.

Explanation

Stored procedures are compiled once and stored server-side, reducing network round-trips and allowing code reuse. They can accept parameters and return results.

Explanation

Views simplify complex queries, restrict column/row access for security, and provide abstraction. Most views are not materialized (no data stored) — the query runs at access time.

Explanation

GROUP BY creates groups of rows with identical values. Aggregate functions compute per-group. SELECT dept, COUNT(*) FROM employees GROUP BY dept counts employees per department.

Explanation

WHERE filters rows before GROUP BY aggregation. HAVING filters groups after aggregation. You can't use aggregate functions in WHERE: use HAVING COUNT(*) > 5 not WHERE COUNT(*) > 5.

Explanation

ORDER BY col ASC (default) or DESC. Multiple columns: ORDER BY last_name, first_name. Without ORDER BY, result order is undefined in SQL.

Explanation

NULL represents missing or unknown data. NULL arithmetic returns NULL. Use IS NULL / IS NOT NULL (not = NULL). Three-valued logic: TRUE, FALSE, UNKNOWN (NULL comparisons return UNKNOWN).

Explanation

CHAR(10) always stores 10 bytes (padded). VARCHAR(100) stores up to 100 bytes, using only what's needed plus 1-2 bytes for length. CHAR is slightly faster for fixed-length data like codes.

Explanation

A schema is the blueprint of a database: table definitions, column types, primary/foreign keys, indexes, and constraints. It is separate from the data itself.

Explanation

Denormalization (used in OLAP, data warehouses, and high-read systems) adds redundant columns or pre-joined tables to avoid expensive JOINs, trading storage/write overhead for faster reads.

Explanation

A composite (compound) key uses multiple columns to form a unique identifier. Example: in an enrollment table, (student_id, course_id) together form the primary key.

Explanation

SELECT DISTINCT city FROM customers returns each city name only once. Without DISTINCT, duplicates appear for each row with that city.

Explanation

Triggers fire automatically BEFORE or AFTER INSERT/UPDATE/DELETE events. Used for auditing, enforcing complex constraints, or maintaining derived data.

Explanation

TRUNCATE is DDL (not DML), resets the table to empty quickly (minimal logging), and cannot fire row-level triggers. DELETE is DML, supports WHERE, fires triggers, and is fully transactional.

Explanation

Relational databases (MySQL, PostgreSQL, Oracle, SQL Server) are based on Codd's relational model. Data is stored in tables with defined schemas and relationships.

Explanation

A table can have only one clustered index (determines physical row order — typically the primary key in MySQL InnoDB). Non-clustered indexes are separate B-trees with row pointers.

Explanation

Subqueries can appear in SELECT, FROM, WHERE, and HAVING clauses. Example: SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees).

Explanation

Replication synchronizes data across multiple database servers. Master-slave (primary-replica) replication sends writes to primary; replicas handle reads, improving scalability and providing failover.

Explanation

SELECT * FROM products ORDER BY price DESC LIMIT 10 returns the 10 most expensive products. MySQL/PostgreSQL use LIMIT; SQL Server uses TOP; Oracle uses ROWNUM or FETCH FIRST.

Explanation

Cursors iterate over result sets one row at a time in stored procedures. Generally avoided for set-based operations (SQL thinks in sets), but useful for row-by-row processing when necessary.

Explanation

In relational theory, a relation is a set of tuples (no duplicates, no order). SQL tables may have duplicate rows (multisets) and ordered result sets, relaxing strict mathematical relations.

Explanation

The transaction log (write-ahead log / WAL) records all changes before applying them to data files. On crash recovery, the DBMS replays committed transactions and undoes uncommitted ones using this log.

Explanation

Self-join: SELECT e.name AS employee, m.name AS manager FROM employees e JOIN employees m ON e.manager_id = m.id — finds each employee's manager from the same employees table.

Explanation

ERDs (Chen notation, Crow's foot notation) help design databases before implementation. Entities become tables, attributes become columns, and relationships define foreign keys.

Explanation

COUNT(*) counts rows. SUM(salary) totals salary. AVG(price) averages price. MAX/MIN find extremes. Used with GROUP BY to compute per-group statistics.

Explanation

OLTP: order processing, banking — high concurrency, simple queries, normalized schema. OLAP: data warehousing, BI — complex aggregations, denormalized star/snowflake schema, fewer concurrent users.

Explanation

UNIQUE constraint prevents duplicate non-NULL values. Unlike PRIMARY KEY, multiple UNIQUE columns are allowed per table, and they can accept NULL values (behavior varies by DBMS).

Explanation

WHERE age BETWEEN 18 AND 65 is equivalent to WHERE age >= 18 AND age <= 65. Works with numbers, dates, and strings. BETWEEN is inclusive on both ends.

Explanation

NoSQL databases (MongoDB, Redis, Cassandra, Neo4j) sacrifice some ACID guarantees for horizontal scalability and flexible schemas. Best for unstructured data, high write throughput, or specific access patterns.

Explanation

1NF: atomic values (no lists/arrays in cells), unique column names, order of rows doesn't matter. If a column holds comma-separated values, it violates 1NF.

Explanation

3NF: all non-key columns must be directly dependent on the primary key (not on other non-key columns). Example: if ZIP→City, store ZIP and City in a separate table.

Explanation

SQL isolation levels: READ UNCOMMITTED (dirty reads), READ COMMITTED (default, no dirty reads), REPEATABLE READ (no non-repeatable reads), SERIALIZABLE (no phantom reads). Higher isolation = lower concurrency.

Explanation

A dirty read occurs at READ UNCOMMITTED isolation: Transaction A reads data written by Transaction B before B commits. If B rolls back, A has read invalid data.

Explanation

Pessimistic: acquire row locks before modifying. Optimistic: read with a version number, check at commit that no other transaction changed the data. Optimistic has higher throughput but may require retries.

Explanation

B-tree (balanced tree) indexes support equality (=), range (<, >, BETWEEN), ORDER BY, and GROUP BY operations efficiently. The tree height is O(log n), so queries touch few disk pages.

Explanation

Hash indexes provide O(1) equality lookups but cannot support range queries, ORDER BY, or partial key lookups. In PostgreSQL they're useful for equality-only workloads; MySQL InnoDB doesn't support them.

Explanation

Sharding scales writes horizontally. Shard key determines which shard holds a row (e.g., user_id % N). Challenges: cross-shard queries, rebalancing, and maintaining ACID across shards.

Explanation

Partitioning (range, hash, list, composite) splits table data physically. Queries on the partition key skip non-relevant partitions (partition pruning), dramatically reducing I/O for large tables.

Explanation

Uncorrelated: SELECT * FROM t WHERE salary > (SELECT AVG(salary) FROM t) — inner query runs once. Correlated: per-outer-row execution — often slow, replaceable with JOINs.

Explanation

Cassandra/DynamoDB choose Availability+Partition (AP). HBase/Zookeeper choose Consistency+Partition (CP). No practical distributed database can be fully consistent and available during network partitions.

Explanation

Materialized views store query results on disk. Unlike regular views (recomputed each access), they are precomputed. Used in data warehouses for aggregation queries. Require refresh strategies (on demand, periodic, on-change).

Explanation

The query optimizer (cost-based optimizer) evaluates multiple execution plans, estimating cost using statistics (row counts, cardinality, data distribution) and choosing the cheapest plan.

Explanation

Statistics (e.g., pg_statistics in PostgreSQL) inform the optimizer about actual data distribution. Stale statistics cause bad query plans. ANALYZE (PostgreSQL) or UPDATE STATISTICS (SQL Server) refresh them.

Explanation

Database deadlocks: TXN A holds lock on row 1, wants row 2; TXN B holds lock on row 2, wants row 1. DBMS detects the cycle and kills one transaction (the "victim").

Explanation

MVCC (PostgreSQL, Oracle, MySQL InnoDB) stores old row versions for concurrent reads. Readers see a consistent snapshot at transaction start without blocking writers. PostgreSQL uses tuple versioning; MySQL uses undo logs.

Explanation

Cursors enable procedural processing of query results. They are generally inefficient (row-by-row vs. set operations). Server-side cursors (PL/pgSQL, T-SQL) run in DB; client-side cursors fetch to client memory.

Explanation

Connection pools (PgBouncer, HikariCP, c3p0) reuse connections. Each new connection takes ~50ms and resources. Pools maintain N ready connections for instant checkout, improving throughput dramatically.

Explanation

A covering index includes all SELECT, WHERE, and JOIN columns for a query. The query can be answered from the index alone (index-only scan), avoiding expensive heap fetches.

Explanation

SQL ACID constraints make distributed horizontal scaling hard (distributed transactions are expensive). NoSQL (Cassandra, DynamoDB) sacrifices strong consistency for linear horizontal write scaling.

Explanation

ON DELETE CASCADE: deleting parent deletes all children. ON DELETE SET NULL: sets FK to NULL. ON DELETE RESTRICT (default): prevents parent delete if children exist. Cascading prevents orphaned records.

Explanation

Data warehouses (Snowflake, Redshift, BigQuery) are optimized for OLAP queries with star/snowflake schemas, columnar storage, and MPP (massively parallel processing) for fast aggregations.

Explanation

Star schema: fact table + denormalized dimension tables. Simple JOINs, faster queries, more storage. Snowflake: normalized dimensions into hierarchies. More JOINs but better data integrity and less storage.

Explanation

Window functions (ROW_NUMBER(), RANK(), LAG(), SUM() OVER()) perform calculations across related rows while returning individual rows. Essential for running totals, rankings, and lag/lead analysis.

Explanation

UNION performs a DISTINCT sort to remove duplicates (slower). UNION ALL appends all rows from both queries without duplicate removal (faster). Use UNION ALL when duplicates are acceptable or not possible.

Explanation

Triggers make debugging hard (invisible logic), can cause cascading effects, and impact performance. Use for audit logging, but prefer application-layer logic for business rules to maintain transparency.

Explanation

Columnar storage (Parquet, ORC, Redshift, BigQuery) reads only queried columns (not entire rows), enables high compression ratios of similar data, and uses SIMD operations for vectorized processing.

Explanation

2NF removes partial dependencies that occur with composite keys: a non-key column depending on only part of the composite key must be moved to its own table. This applies only when the primary key has multiple columns.

Explanation

EXPLAIN reveals how the database intends to execute a query: which indexes it uses, the join order, and the access methods (sequential scan vs. index scan). It is the primary tool for diagnosing slow queries.

Explanation

Non-repeatable reads occur at READ COMMITTED isolation: re-querying the same row mid-transaction can return updated values from a transaction that committed in between. REPEATABLE READ isolation prevents this by maintaining a consistent snapshot.

Explanation

Phantom reads happen when a range query (e.g., WHERE age > 30) returns a different set of rows on re-execution because rows matching the predicate were inserted or deleted by other committed transactions. SERIALIZABLE isolation prevents this.

Explanation

EXISTS returns TRUE as soon as the subquery produces one matching row, short-circuiting further evaluation. This often makes it faster than IN for large correlated subqueries, especially when the optimizer can use semi-join strategies.

Explanation

Referential integrity is enforced through foreign key constraints: a row cannot reference a non-existent parent row, and the DBMS rejects operations (or cascades them) that would break this relationship.

Explanation

Anti-joins find "rows in A without a match in B" — for example, customers who have never placed an order. NOT EXISTS and LEFT JOIN/IS NULL are common implementations, and the optimizer can rewrite NOT IN into an efficient anti-join plan.

Explanation

Plain EXPLAIN shows only estimated costs and row counts from the optimizer. EXPLAIN ANALYZE runs the query and adds actual elapsed time and row counts, making it possible to see where estimates diverge sharply from reality, often the root cause of a bad plan.

Explanation

Surrogate keys (e.g., auto-increment IDs, UUIDs) have no business meaning and remain stable even if real-world attributes change. This avoids problems that arise when natural keys (like email or SSN) are later edited or reused.

Explanation

GRANT SELECT ON table TO user and REVOKE SELECT ON table FROM user manage access permissions at the object level. Together with DENY in some systems, they form DCL, the part of SQL responsible for security and authorization.

Explanation

Caching layers (application-level caches, query caches) avoid redundant computation for repeated reads, greatly reducing load. The trade-off is staleness: if the cache isn't invalidated when data changes, clients may see outdated results.

Explanation

NATURAL JOIN implicitly joins on all identically named columns, which is convenient but fragile — adding a new column with a matching name in either table silently changes the join semantics. Explicit ON clauses are preferred because they document intent and avoid such surprises.

Explanation

COALESCE(phone, mobile, 'N/A') evaluates its arguments left to right and returns the first one that is not NULL. It is a portable, standard-SQL alternative to vendor-specific functions like ISNULL or NVL for handling missing data gracefully.

Explanation

Strict 2PL: all locks released at commit, preventing cascading aborts. 2PL blocks readers with writers. MVCC lets readers see a consistent snapshot without blocking writers, improving concurrency at the cost of storage for old versions.

Explanation

ARIES (Algorithms for Recovery and Isolation Exploiting Semantics): uses WAL, steal/no-force buffer policy. Three phases: Analysis (determine dirty pages/active TXNs), Redo (redo from last checkpoint), Undo (roll back uncommitted TXNs).

Explanation

Lock escalation trades fine-grained concurrency for lower memory overhead. SQL Server escalates to table locks after ~5,000 row locks. Can cause unexpected blocking; disable per-table when problematic.

Explanation

Read committed: re-reading a row may return updated data from committed TXNs (non-repeatable read). Repeatable read: row values don't change during a TXN, but new rows (phantoms) may appear. Serializable prevents phantoms too.

Explanation

EXPLAIN (PostgreSQL) or EXPLAIN PLAN (Oracle) shows the execution plan. EXPLAIN ANALYZE actually runs and shows real vs. estimated row counts. Look for sequential scans on large tables, nested loops over large sets, and incorrect cardinality estimates.

Explanation

PgBouncer in transaction mode multiplexes: many clients share few server connections (one per concurrent transaction, not per client). This allows 10,000 app connections with only 100 PostgreSQL connections.

Explanation

Synchronous replication (sync standby in PostgreSQL) waits for replica to write WAL before returning. Zero RPO but adds replica latency to every write. Async replication may lose committed data if primary crashes before replica catches up.

Explanation

LSM-trees (RocksDB, Cassandra, LevelDB, HBase) convert random writes to sequential I/O by appending to memtables flushed as SSTables. Compaction merges SSTables. High write throughput; reads require checking multiple levels.

Explanation

A single 4KB write may cause 20KB of actual storage writes (WAL write, B-tree page write, checkpoint). LSM-tree compaction amplifies writes. High write amplification wears SSDs faster and reduces throughput.

Explanation

The Saga pattern (choreography or orchestration) handles distributed transactions without 2PC. Each step has a compensating transaction for undo. Used in microservices where ACID across services is impractical.

Explanation

PostgreSQL: dead tuples stay in heap pages until VACUUM reclaims them (table bloat risk). InnoDB: undo log stores versions, purged by background threads. Different approaches to old version management.

Explanation

WAL ensures durability and enables recovery. Before writing a dirty page, its log record is flushed (log-ahead). On recovery: redo committed TXNs from log, undo uncommitted ones. Enables ARIES recovery protocol.

Explanation

Temporal databases track "valid time" (when a fact is true in the real world) and "transaction time" (when data was stored). SQL:2011 adds PERIOD, VERSIONING, and temporal JOINs for bitemporal queries.

Explanation

PostgreSQL RLS: CREATE POLICY defines row-visibility predicates applied automatically to all queries on the table. Users cannot bypass RLS even with direct table access. Views require denying direct table access separately.

Explanation

Spanner (Corbett et al., 2012) provides global SQL with ACID transactions using Paxos consensus and TrueTime. TrueTime bounds clock uncertainty, enabling globally consistent reads and serializable distributed transactions.

Explanation

HTAP systems (TiDB, SingleStore, CockroachDB, SQL Server with columnstore) eliminate the ETL pipeline to the data warehouse, enabling real-time analytics on live transactional data.

Explanation

2PC has a prepare phase (participants vote to commit or abort) and a commit phase (coordinator broadcasts the final decision). If the coordinator crashes after participants vote "yes" but before sending the decision, those participants remain blocked holding locks — the protocol's well-known "blocking problem," which Paxos/Raft-based commit protocols aim to avoid.

Explanation

The cost-based optimizer compares estimated I/O and CPU costs for each join strategy given current statistics. Nested loop joins excel when the outer side is small and the inner side has a usable index; hash joins are typically chosen for large, unindexed equi-joins because building an in-memory hash table avoids repeated scans.

Explanation

When a hot cache entry expires, every waiting request can fall through to the database at once, causing a load spike. Common mitigations include having only one request regenerate the value while others wait (request coalescing/locking), serving slightly stale data while refreshing, and jittering expiration times so entries don't all expire together.

Explanation

Under snapshot isolation (used by PostgreSQL's REPEATABLE READ and Oracle's SERIALIZABLE), each transaction sees a consistent snapshot, so it never observes the other's uncommitted writes. However, two transactions can both read the same snapshot, each decide an action is safe based on it, and then commit writes that collectively break an invariant — the classic example being two on-call doctors each independently giving up their shift because the snapshot showed another doctor still on call.