Database Management Systems (DBMS), Relational Model, SQL, Normalization & Advanced Database Architecture (Part 9)

Question 1

What is the difference between a "Dense Index" and a "Sparse Index"?

Accepted Answer

A Dense Index has an index record for EVERY single search-key value in the data file; a Sparse Index has index records for only some search-key values (typically one entry per physical disk data block)

Answer

Dense indexes require optical storage

Answer

Sparse indexes are stored in RAM only

Answer

Dense indexes cannot be searched using binary search

Question 2

Why do almost all modern relational database storage engines (InnoDB, PostgreSQL, SQL Server) utilize "B+ Trees" rather than standard "B-Trees" for indexing?

Accepted Answer

In B+ Trees, data record pointers reside ONLY in leaf nodes (allowing internal nodes to hold vastly more search keys with higher fan-out/shallower tree depth), and all leaf nodes are linked in a Doubly-Linked List for blazing-fast sequential range scans

Answer

B+ Trees do not require disk storage

Answer

B-Trees cannot store strings

Answer

B+ Trees eliminate the need for primary keys

Question 3

What is the output difference between `RANK()` and `DENSE_RANK()` in SQL when two rows tie for 2nd place?

Accepted Answer

`RANK()` assigns ranks 1, 2, 2, 4 (skipping rank 3); `DENSE_RANK()` assigns ranks 1, 2, 2, 3 (no gaps in rank numbering)

Answer

`DENSE_RANK()` cannot handle ties

Answer

`RANK()` assigns random decimals

Answer

`DENSE_RANK()` converts numbers to Roman numerals

Question 4

What does the SQL window function `ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC)` do?

Accepted Answer

It assigns a unique, sequential integer starting at 1 to each employee within their specific department, ordered from highest salary to lowest

Answer

It sums all salaries in the department

Answer

It deletes duplicate employee names

Answer

It calculates the department average salary

Question 5

Which SQL window function allows a query to access data from the IMMEDIATELY PRECEDING row in a result set without performing a self-join?

Accepted Answer

LAG()

Answer

LEAD()

Answer

PREVIOUS()

Answer

PRIOR()

Question 6

What is a Common Table Expression (CTE) defined with the `WITH` keyword in SQL?

Accepted Answer

A named, temporary result set that exists only within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement, improving query readability and enabling recursive queries

Answer

A permanent table stored on disk

Answer

A database trigger

Answer

A user-defined function in C++

Question 7

Which SQL query correctly finds the N-th highest salary (e.g. 3rd highest salary) from an `Employees` table using `DENSE_RANK()`?

Accepted Answer

WITH RankedSalaries AS (SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) as rnk FROM Employees) SELECT DISTINCT Salary FROM RankedSalaries WHERE rnk = 3;

Answer

SELECT TOP 3 Salary FROM Employees ORDER BY Salary;

Answer

SELECT MAX(Salary, 3) FROM Employees;

Answer

SELECT Salary FROM Employees WHERE Salary = 3;

Question 8

According to Eric Brewer's "CAP Theorem" for distributed database systems, what three properties cannot be simultaneously guaranteed in the presence of a network partition?

Accepted Answer

Consistency, Availability, and Partition Tolerance

Answer

Confidentiality, Authentication, and Privacy

Answer

Concurrency, Atomicity, and Performance

Answer

Capacity, Accessibility, and Persistence

Question 9

What does the "BASE" acronym in NoSQL and distributed database systems stand for?

Accepted Answer

Basically Available, Soft state, and Eventual consistency

Answer

Binary Access, Synchronized Execution, and Encryption

Answer

Buffered Array, Sequential Entry, and Durability

Answer

Backup, Archive, Store, and Export

Question 10

Which category of NoSQL database stores data as flexible, schema-less JSON, BSON, or XML documents (e.g. MongoDB, Couchbase)?

Accepted Answer

Document-Oriented Database

Answer

Graph Database

Answer

Key-Value Store

Answer

Column-Family Store

Question 11

Which category of NoSQL database represents data as "Nodes", "Edges" (relationships), and "Properties", making it optimal for social networks, recommendation engines, and fraud detection (e.g. Neo4j)?

Accepted Answer

Graph Database

Answer

Key-Value Store

Answer

Document Store

Answer

Relational Database

Question 12

Which in-memory NoSQL database stores data as associative Key-Value pairs with sub-millisecond read/write latencies, widely used for session caching and real-time leaderboards?

Accepted Answer

Redis

Answer

Oracle Database

Answer

Microsoft Access

Answer

SQLite

Question 13

What is "Database Sharding" (Horizontal Partitioning) in massive scale database architectures?

Accepted Answer

Splitting large database tables horizontally by rows across multiple independent physical server nodes based on a "Shard Key" (e.g. UserID)

Answer

Backing up tables to optical discs

Answer

Dividing columns between two hard drives

Answer

Deleting historical records older than 1 year

Question 14

What is the difference between "Synchronous Replication" and "Asynchronous Replication" in high-availability database clusters?

Accepted Answer

Synchronous Replication waits for confirmation from standby replicas before committing on the primary node (zero data loss, higher write latency); Asynchronous replication commits on the primary immediately and copies data in the background (lower latency, risk of minor data loss during failover)

Answer

Synchronous replication is for text only

Answer

Asynchronous replication works only on Wi-Fi

Answer

Synchronous replication does not require networks

Question 15

What does the SQL `EXPLAIN` (or `EXPLAIN PLAN`) command output when prepended to a query?

Accepted Answer

It displays the execution plan chosen by the database Query Optimizer, showing whether Table Scans, Index Seeks, Nested Loops, or Hash Joins will be used and their estimated costs

Answer

It explains the SQL syntax rules in plain English

Answer

It translates the query to Python

Answer

It fixes syntax errors automatically