Describe Characteristics and Performance Considerations of a Relational Database DATA-ENGINEERING-OPTIMIZE Practice Questions (Page 1)

Questions 1–5

1. 1foundation · medium

   A database administrator is analyzing why a database server is experiencing slow query performance. Which storage or memory consideration is most likely to be the cause?

   • AThe database uses solid-state drives (SSDs) instead of traditional hard disk drives (HDDs), which reduces I/O throughput.
   • BThe buffer pool is too small, causing frequent disk I/O because frequently accessed data pages are not cached in memory.
   • CThe database is configured to use a write-ahead log, which doubles the amount of disk I/O for every transaction.
   • DThe database is running on a server with too much RAM, causing the buffer pool to be underutilized and leading to inefficient memory usage.
   Reveal Solution Discussion Select an answer first
2. 2foundation · medium

   A database administrator is tuning a system with many concurrent transactions. Which statement correctly describes how transaction management and concurrency control affect performance?

   • AConcurrency control affects only write transactions; read transactions are never blocked or delayed.
   • BLower isolation levels always improve performance because they eliminate the need for locking entirely.
   • CTransaction management has no impact on performance because the database engine handles all concurrency automatically without trade-offs.
   • DHigher isolation levels provide stronger data consistency but can reduce concurrency and increase the likelihood of blocking and deadlocks.
   Reveal Solution Discussion Select an answer first
4. 3foundation · medium

   A database designer is deciding between a normalized and a denormalized schema for a reporting system that runs complex aggregate queries over large volumes of data. Which statement correctly describes the impact of denormalization in this context?

   • ADenormalization has no impact on query performance; it only affects storage requirements by compressing data.
   • BDenormalization reduces the number of joins needed for queries, which can improve read performance, but it increases the risk of data anomalies and requires more careful update logic.
   • CDenormalization guarantees data integrity by enforcing referential integrity constraints across all tables.
   • DDenormalization eliminates all data redundancy, which improves write performance but makes queries more complex due to the need for many joins.
   Reveal Solution Discussion Select an answer first
5. 4foundation · medium

   A data engineer is evaluating the use of an index on a large table that receives frequent INSERT, UPDATE, and DELETE operations. Which trade-off of indexing should the engineer consider?

   • AIndexes speed up SELECT queries but add overhead to write operations because each index must be updated when data changes.
   • BIndexes speed up write operations but slow down SELECT queries because the query optimizer must scan the index before accessing the table.
   • CIndexes improve both read and write performance equally by storing redundant copies of the data in a sorted order.
   • DIndexes have no effect on write performance because they are updated asynchronously by the database engine.
   Reveal Solution Discussion Select an answer first
6. 5foundation · medium

   A data modeler is designing a new relational database for a customer relationship management (CRM) system. Which set of elements is fundamental to the relational database model?

   • ATables composed of rows and columns, with a primary key uniquely identifying each row, and relationships defined between tables using foreign keys
   • BDocuments stored in collections, each with a unique identifier, and relationships established by embedding or referencing other documents
   • CKey-value pairs stored in a distributed hash table, with each key mapping to a value and relationships resolved by application code
   • DGraphs of nodes and edges, where each node has properties and edges define relationships that are traversed during queries
   Reveal Solution Discussion Select an answer first