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

Question 1

What is a database "Trigger"?

Accepted Answer

A procedural block of code stored in the database that automatically executes (fires) in response to specified Data Manipulation Language events (`INSERT`, `UPDATE`, `DELETE`) on a particular table

Answer

A physical switch on the server chassis

Answer

A button that reboots the database

Answer

A keyboard shortcut for SQL queries

Question 2

What is a primary advantage of using compiled "Stored Procedures" over submitting raw dynamic SQL strings from client applications?

Accepted Answer

Reduced network traffic, pre-compiled query execution plans for faster performance, and strong security protection against SQL Injection

Answer

Stored procedures eliminate the need for databases

Answer

Stored procedures work without memory in the server

Answer

Stored procedures convert SQL into video games

Question 3

How does a DBMS Concurrency Controller detect and resolve Deadlocks occurring between concurrent locking transactions?

Accepted Answer

By maintaining a "Wait-For Graph" (WFG) to detect cycles, selecting a "Victim Transaction" based on cost, and rolling it back to release its locks

Answer

By shutting down the database server

Answer

By deleting all locked tables

Answer

By ignoring the deadlock indefinitely

Question 4

What is "Thomas' Write Rule" in timestamp-based concurrency control?

Accepted Answer

If a transaction $T_i$ attempts to write $W(Q)$ and its timestamp is older than the largest write timestamp on the item ($TS(T_i) < 	ext{W-timestamp}(Q)$), the write operation is simply IGNORED (obsolete write) rather than aborting $T_i$

Answer

All transactions must be stamped with Greenwich Mean Time

Answer

Writing data is forbidden after 5:00 PM

Answer

Transactions with odd timestamps are deleted

Question 5

How does Multi-Version Concurrency Control (MVCC, implemented in PostgreSQL, MySQL InnoDB, and Oracle) eliminate read-write contention?

Accepted Answer

Readers do not block writers, and writers do not block readers, because read queries access an immutable point-in-time historical "Snapshot" (version) of data rows while writes create new row versions

Answer

By locking the entire database during reads

Answer

By running multiple copies of the operating system

Answer

By deleting old data versions immediately

Question 6

What is the primary difference between "OLTP" (Online Transaction Processing) and "OLAP" (Online Analytical Processing) systems?

Accepted Answer

OLTP databases are normalized (3NF/BCNF) and optimized for high-volume, fast atomic read/write transactions (day-to-day operations); OLAP systems (Data Warehouses) are denormalized (Star/Snowflake schema) and optimized for complex analytical aggregation queries over massive historical data

Answer

OLTP is for cloud; OLAP is for on-premises

Answer

OLAP cannot store numbers

Answer

OLTP is strictly for mobile phones

Question 7

What constitutes a "Star Schema" in a Data Warehouse design?

Accepted Answer

A single centralized "Fact Table" (containing numeric transactional metrics and foreign keys) connected directly to multiple completely denormalized "Dimension Tables"

Answer

A schema shaped like a circle

Answer

A database for astronomy data only

Answer

A database that connects to 5 routers

Question 8

Why do modern analytical data warehouses (like Snowflake, Google BigQuery, and Amazon Redshift) store data in "Columnar Formats" rather than traditional row-oriented formats?

Accepted Answer

Analytical queries read only a few specific columns across billions of rows; columnar storage drastically reduces disk I/O by scanning only the requested columns and enables extreme data compression ratios on homogeneous column data

Answer

Columnar formats eliminate SQL syntax

Answer

Row-oriented storage is illegal in data warehousing

Answer

Columnar storage works only on Apple computers

Question 9

Which internal Join algorithm is executed by database query engines when joining two massive, unsorted tables without indexes by building an in-memory hash table on the smaller relation?

Accepted Answer

Hash Join

Answer

Nested Loop Join

Answer

Sort-Merge Join

Answer

Cartesian Loop

Question 10

When is a "Sort-Merge Join" the most efficient join algorithm for a relational database query optimizer?

Accepted Answer

When both input relations are already sorted on their join keys (e.g. via an existing clustered B+ tree index)

Answer

When both relations have zero rows

Answer

When joining tables on non-equality operators (like `>` or `<`)

Answer

When memory is completely full

Question 11

What does `NTILE(4) OVER (ORDER BY Revenue DESC)` calculate for rows in a sales table?

Accepted Answer

It divides all sorted rows evenly into 4 quartiles (assigning bucket numbers 1, 2, 3, or 4 to each row)

Answer

It multiplies revenue by 4

Answer

It selects the top 4 rows only

Answer

It deletes 4 rows from the table

Question 12

What is the difference between "Tuple Relational Calculus" (TRC) and "Domain Relational Calculus" (DRC)?

Accepted Answer

TRC formulates non-procedural declarative queries using tuple variables {t | P(t)}; DRC formulates queries using domain variables over individual attribute columns {<x1, x2, ..., xn> | P(x1, x2, ..., xn)}

Answer

TRC is procedural; DRC is non-procedural

Answer

DRC is for web browsers; TRC is for databases

Answer

TRC does not support predicate logic

Question 13

What does a "Second-Order SQL Injection" (Stored SQLi) attack involve?

Accepted Answer

Malicious payload input is initially stored safely in the database without immediate exploitation, and is later retrieved and executed dynamically by a different backend process or query

Answer

An SQL injection performed by two hackers simultaneously

Answer

An attack that targets secondary index files

Answer

An SQL query executed on a secondary server

Question 14

Which SQL construct allows querying recursive hierarchical data structures (such as an organizational management tree or bill of materials)?

Accepted Answer

Recursive Common Table Expression (`WITH RECURSIVE CTE_Name AS (Anchor Member UNION ALL Recursive Member)`)

Answer

A continuous `WHILE` loop

Answer

A nested `GROUP BY` query

Answer

An infinite `CROSS JOIN`

Question 15

In Relational Algebra query optimization, what is the heuristic optimization rule "Push Down Selections (Predicate Pushdown)"?

Accepted Answer

Moving Selection operators (σ) as deep down the algebraic operator tree as possible to execute row filtering BEFORE expensive Cartesian Products or Joins are computed

Answer

Moving all select queries to the bottom of the SQL script

Answer

Deleting where clauses from queries

Answer

Sorting tables before creating primary keys