Skip to content

Quiz: Relational Database Fundamentals

Test your understanding of relational database concepts, SQL, joins, indexing, and the limitations of RDBMS for IT management with these review questions.

1. What is the primary role of a foreign key in a relational database?

  1. To uniquely identify each row in a table and prevent duplicate entries
  2. To store encrypted values for sensitive columns such as passwords and credit card numbers
  3. To reference the primary key of another table, establishing a relationship between tables
  4. To speed up query performance by creating an auxiliary index on frequently searched columns
Show Answer

The correct answer is C. A foreign key is a column in one table that references the primary key of another table, establishing the relationship between those tables. For example, the server_id column in the Applications table references the Servers table, indicating which server hosts each application. Foreign keys also enforce referential integrity by preventing references to non-existent rows.

Concept Tested: Foreign Key

2. Which SQL JOIN type returns all rows from the left table, including those with no matching rows in the right table, filling in NULL for missing right-side values?

  1. INNER JOIN
  2. LEFT OUTER JOIN
  3. CROSS JOIN
  4. RIGHT OUTER JOIN
Show Answer

The correct answer is B. A LEFT OUTER JOIN returns all rows from the left table regardless of whether a matching row exists in the right table. When no match exists in the right table, NULL values fill in for the right table's columns. This is useful for finding entities without relationships—for example, servers with no applications assigned.

Concept Tested: Outer Join

3. In the context of IT infrastructure management, what is a transitive dependency?

  1. A constraint that prevents circular references between tables in a relational schema
  2. A situation where an indirect relationship exists, such as when Service A depends on Application B which depends on Database C
  3. A database feature that automatically propagates changes from parent tables to child tables
  4. A type of index that connects multiple columns to improve query performance across several tables
Show Answer

The correct answer is B. A transitive dependency exists when entity A relates to entity B, and entity B relates to entity C, creating an indirect relationship between A and C. In IT infrastructure, these chains are common: a business service depends on an application, which depends on a database, which depends on a server. Understanding these chains is essential for change impact analysis and incident root cause diagnosis.

Concept Tested: Transitive Dependency

4. A database query must follow dependency relationships from a business service through 5 levels of infrastructure components. In a relational database, how does this affect query complexity and performance?

  1. Performance remains constant because modern RDBMS systems use parallel query execution to handle any number of JOIN operations
  2. Performance improves with more hops because the result set narrows with each join, reducing the total rows processed
  3. Each additional hop requires another JOIN operation, with performance degrading exponentially as intermediate result sets multiply
  4. The RDBMS automatically rewrites multi-hop queries into single-table scans using materialized views, so there is no performance penalty
Show Answer

The correct answer is C. Multi-hop queries require one JOIN per hop, and each JOIN can multiply the size of intermediate result sets. As hop count increases, performance degrades exponentially—a 5-hop query might require 6 table joins, with intermediate results growing with each step. This is a fundamental architectural limitation of RDBMS for highly connected data, and is why CMDB implementations struggle with dependency impact analysis.

Concept Tested: Multi-Hop Query

5. What trade-off does creating a database index on a column introduce?

  1. Indexes improve both read and write performance but require more complex SQL syntax to use them
  2. Indexes accelerate SELECT query performance but slow INSERT, UPDATE, and DELETE operations because indexes must also be updated
  3. Indexes improve query performance only for exact equality conditions and provide no benefit for range queries or sorting
  4. Indexes eliminate the need for primary keys because they create a separate unique identifier for each row
Show Answer

The correct answer is B. A database index improves SELECT query performance by enabling O(log n) lookups instead of O(n) full table scans. However, every data modification (INSERT, UPDATE, DELETE) must also update the index structures, making write operations slower. There is also additional disk storage cost. This trade-off means that indexes must be chosen carefully based on query patterns rather than indexing every column.

Concept Tested: Database Index

6. Why does schema rigidity create challenges when building a CMDB for heterogeneous IT infrastructure that includes servers, network switches, storage arrays, and IoT devices?

  1. Relational databases cannot store more than one entity type per database instance, requiring separate installations for each device type
  2. Each device type has different attributes, forcing a sparse table with many NULL values or complex workarounds that undermine query performance
  3. Schema rigidity prevents IT teams from running queries across multiple device types simultaneously
  4. Relational schemas require all rows to have the same number of characters in each column, which conflicts with varying device attribute values
Show Answer

The correct answer is B. Schema rigidity requires all rows in a table to have the same columns, even when different device types have distinct attributes. Combining servers (with cpu_count, ram_gb, os_version) and switches (with port_count, vlan_support) into one table results in many NULL values for non-applicable attributes. As more device types are added, the table becomes increasingly sparse and queries become more complex, requiring device_type filters throughout.

Concept Tested: Schema Rigidity

7. What does a database schema define in a relational database management system?

  1. The physical storage layout of data on disk, including file sizes and block allocation strategies
  2. The access control rules determining which users can read or modify specific tables
  3. The structure of the database, including table names, column definitions, data types, and relationships between tables
  4. The execution plan the query optimizer will use when retrieving data from multiple tables
Show Answer

The correct answer is C. A database schema defines the structure of a database: table names, the columns within each table, data types for each column, constraints (NOT NULL, UNIQUE), and relationships between tables via foreign keys. Schemas must be defined before data can be inserted—this "schema-on-write" approach enforces consistency and data integrity, though it also creates challenges when requirements change.

Concept Tested: Database Schema

8. An INNER JOIN between a Servers table (1,000 rows) and an Applications table (5,000 rows) with no matching applications for 200 servers will return how many rows?

  1. 6,000 rows, because INNER JOIN combines all rows from both tables
  2. 5,000 rows, because INNER JOIN always returns the number of rows in the larger table
  3. Only rows where a matching server_id exists in both tables—rows for the 200 unmatched servers are excluded
  4. 1,000 rows, because INNER JOIN is limited by the smaller table's row count
Show Answer

The correct answer is C. An INNER JOIN returns only rows where matching values exist in both tables. The 200 servers with no applications would be excluded from the result because there are no matching application rows. The number of rows returned equals the number of successful matches, which is less than or equal to the number of rows in the smaller table. A LEFT JOIN would be needed to include the unmatched servers.

Concept Tested: Inner Join

9. The SQL query optimizer's effectiveness diminishes significantly when a query involves more than 5-6 table joins. Which of the following explains why?

  1. SQL syntax does not support more than 6 JOIN clauses in a single SELECT statement
  2. The number of possible execution plans grows exponentially with the number of tables joined, making it computationally infeasible to evaluate all options
  3. More than 6 joined tables exceed the maximum allowed memory for a single query execution
  4. The optimizer is restricted to using only one index per query, so additional joins cannot benefit from index acceleration
Show Answer

The correct answer is B. The query optimizer must evaluate possible execution plans to choose the most efficient one. With N tables to join, there are N! possible join orderings (plus variations in access method and join algorithm choices). For 6 tables this means 720 orderings; for 10 tables it exceeds 3.6 million. At some point the optimizer must stop searching and use a heuristic plan, which may be far from optimal, leading to unexpectedly slow query execution.

Concept Tested: Query Optimization

10. Schema evolution in relational databases is particularly challenging in which of the following scenarios?

  1. Adding a new optional column that allows NULL values to an existing table
  2. Renaming a column that is referenced throughout multiple application codebases and SQL queries
  3. Inserting new rows into a table that already has millions of existing records
  4. Creating a new index on a column that has never been indexed before
Show Answer

The correct answer is B. Renaming a column breaks all application code and queries that reference the old column name, requiring coordinated deployment of database changes and application updates simultaneously. In contrast, adding a nullable column (option A) is backward-compatible, inserting rows (option C) does not change the schema at all, and adding an index (option D) is transparent to applications. Column renames exemplify why schema evolution requires careful coordination across all consuming systems.

Concept Tested: Schema Evolution