Step2Study

CompTIA DataSys+ (DS0-001) practice questions

231 free questions with answers and explanations.

Practice test
  1. 101.A banking application uses a database to store credit card numbers. To reduce the scope of PCI DSS compliance and minimize the risk associated with storing sensitive cardholder data, the development team decides to replace actual credit card numbers with unique, non-sensitive surrogate values. These surrogate values can be used for internal processing, but the original credit card numbers are stored separately in a highly secure vault and are only retrieved when absolutely necessary. Which data security technique is being implemented?Data and Database Security
  2. 102.A web application developer suspects that a recent increase in database errors and unexpected query results is due to malicious input. The application uses dynamically generated SQL queries based on user input for search functions. Which vulnerability is MOST likely being exploited?Data and Database Security
  3. 103.A database administrator is routinely performing maintenance tasks. They need to analyze the performance of a specific SQL query, including how it accesses data, which indexes it uses, and the estimated cost of each operation. Which SQL DDL/DML command or statement prefix would provide this detailed information?Database Fundamentals
  4. 104.A database administrator is performing routine maintenance on a PostgreSQL database and notices that several large tables have significantly more disk space allocated than is used by their actual data. This is causing unnecessary I/O and performance degradation. Which PostgreSQL-specific maintenance operation should they perform to reclaim this wasted space?Database Management and Maintenance
  5. 105.A database administrator is reviewing the database's error logs and notices a recurring warning message: 'Deadlock found when trying to get lock; try restarting transaction'. This indicates frequent contention between transactions. To address this, the administrator decides to implement a strategy to minimize deadlocks. Which of the following approaches is MOST effective in preventing deadlocks?Database Management and Maintenance
  6. 106.A financial institution is implementing a new database system to store sensitive customer financial data. Regulatory compliance dictates that this data must be protected even if the underlying storage media is compromised. Which security measure is MOST appropriate to ensure data confidentiality in this scenario?Data and Database Security
  7. 107.A database administrator is planning the storage requirements for a new analytics database. The database will store 100 million records annually, and each record is estimated to be 500 bytes. The company needs to retain data for 5 years. What is the minimum raw storage capacity (in TB) required for this database, assuming no compression and a 20% overhead for indexes and system files?Database Deployment
  8. 108.A data architect is designing a high-availability solution for a critical database cluster. The requirement is for automatic failover without manual intervention and the ability to continue operations even if one node fails, with no data loss. Which high-availability component is primarily responsible for detecting a node failure and initiating the failover process?Business Continuity
  9. 109.A database administrator is tasked with implementing a new security policy for a critical customer database. The policy states that after five consecutive failed login attempts within a 30-minute window, a user account must be temporarily disabled for 15 minutes. Which security control directly implements this policy?Data and Database Security
  10. 110.A database administrator is planning the storage requirements for a new analytics database. The database will store 500 million records, with each record averaging 2KB in size. Additionally, indexes are estimated to consume 15% of the data size, and transaction logs are projected to be 10% of the combined data and index size. What is the MINIMUM estimated total storage requirement, in GiB, for this database?Database Deployment
  11. 111.A database administrator is configuring a database for high availability using a cluster with shared storage. To prevent data corruption during a split-brain scenario where both nodes attempt to write to the shared storage simultaneously, which mechanism is essential?Business Continuity
  12. 112.A database administrator is troubleshooting a performance issue where a specific query intermittently runs very slowly. Upon inspection of the query execution plan, they find that sometimes the query uses an efficient index seek, but other times it defaults to a less efficient table scan. This behavior is inconsistent and seems to occur more frequently after large data loads. What is the MOST likely cause of this intermittent plan change?Database Management and Maintenance
  13. 113.A database architect is designing a schema for a new online learning platform. They need to represent that a 'Student' can enroll in multiple 'Courses', and a 'Course' can have multiple 'Students' enrolled. Which type of relationship best describes this scenario?Database Fundamentals
  14. 114.A data analyst is working with a `Sales` table that contains `SaleID`, `ProductID`, `CustomerID`, `SaleDate`, and `Amount`. The analyst frequently needs to retrieve `SaleID`, `SaleDate`, and `Amount` for sales made after a specific date, ordered by `SaleDate`. To optimize this specific query without impacting other queries on the `Sales` table, which type of index would be most beneficial?Database Fundamentals
  15. 115.A database developer is creating a new `Products` table. They want to ensure that every product has a unique identifier and that this identifier is automatically generated when a new product is added, without manual intervention. Which SQL DDL statement component best achieves this?Database Fundamentals
  16. 116.A database administrator is configuring a new MySQL server. To ensure optimal performance for a read-heavy OLAP (Online Analytical Processing) workload, the administrator needs to adjust the buffer pool size. The server has 64GB of RAM. What is a common and recommended starting point for the `innodb_buffer_pool_size` parameter in this scenario?Database Deployment
  17. 117.A data scientist frequently runs complex analytical queries involving multiple joins and aggregations on a `Sales` database. To simplify these queries and improve reusability, the scientist wants to create a persistent stored block of SQL code that can be invoked by name. Which database object should the scientist create?Database Fundamentals
  18. 118.A database administrator is configuring a high-availability solution for a critical production database. The requirement is to maintain continuous operations with no data loss and minimal downtime in the event of a primary server failure. Which replication strategy best meets these objectives?Business Continuity
  19. 119.A database developer is designing a new `Employees` table and wants to ensure that the `Email` column always contains unique values for each employee. Which constraint should be applied to the `Email` column to enforce this requirement without making it the primary identifier for the table?Database Fundamentals
  20. 120.A database administrator is evaluating backup strategies for a large production database (10 TB). Daily full backups take 8 hours to complete and require significant storage. The business requires an RPO of 24 hours. To reduce backup window and storage, differential backups are proposed. If the initial full backup is done on Sunday, and daily differential backups are taken Monday through Saturday, how much data, on average, would need to be restored if a failure occurs on Wednesday morning before the daily differential backup?Business Continuity
  21. 121.A global e-commerce company is implementing a new payment processing system. Due to PCI DSS compliance requirements, sensitive credit card numbers (PANs) must be protected such that they are never stored in their original form within the company's internal databases, even for analytics. However, the system still needs to link transactions to specific payment methods and perform certain operations like refunds. Which data security technique allows for this functionality while keeping the original PANs out of scope?Data and Database Security
  22. 122.A database administrator is performing routine maintenance and notices that several tables have become significantly fragmented. This fragmentation is impacting query performance due to increased I/O operations as data blocks are scattered across disk. Which of the following maintenance tasks should the administrator perform to address this issue?Database Management and Maintenance
  23. 123.A database administrator is investigating a series of unauthorized login attempts to a critical production database. The attempts originate from various IP addresses and involve common usernames and password combinations. To mitigate this type of attack, which security control should the administrator implement to automatically block further attempts after a specified number of failed logins?Data and Database Security
  24. 124.A data engineer is implementing a disaster recovery solution for a critical database. The business has a Recovery Point Objective (RPO) of 4 hours and a Recovery Time Objective (RTO) of 8 hours. Which solution would be most cost-effective while meeting these requirements?Business Continuity
  25. 125.A database administrator is deploying a new database server for a critical financial application. The application requires extremely low latency for read operations and must guarantee that data written by one transaction is immediately visible to subsequent transactions globally, even in a distributed setup. Which of the following consistency models is LEAST suitable for this requirement?Database Deployment
  26. 126.A junior database administrator is tasked with improving the performance of a frequently executed query that joins two large tables, `Orders` and `Customers`, on the `CustomerID` column. The `CustomerID` column in both tables is already indexed. However, the query still experiences slow execution times. Upon closer inspection, it's noted that the `Orders` table has millions of rows, and the `CustomerID` column in `Orders` has many duplicate values, while in `Customers` it is unique. What is the MOST likely reason for the continued slow performance?Database Fundamentals
  27. 127.A database administrator needs to create a new user account with privileges to only read data from the `Sales` and `Customers` tables. The user should not be able to modify or delete any data. Which SQL DDL command, along with appropriate clauses, should the administrator use?Database Fundamentals
  28. 128.A data engineer is examining a database table named `Orders` with columns `OrderID`, `CustomerID`, `OrderDate`, `ProductID`, `Quantity`, `Price`, and `CustomerName`. They notice that `CustomerName` is repeated for every order placed by the same `CustomerID`. To achieve Second Normal Form (2NF), which action is primarily required?Database Fundamentals
  29. 129.A data engineer is implementing a data archiving strategy. The business requires that historical data, older than two years, be moved to a lower-cost storage tier. This archived data must still be accessible for auditing and reporting, but with a longer retrieval time (e.g., 24-48 hours). Which backup type is most suitable for this archiving requirement?Business Continuity
  30. 130.A database administrator is deploying a new version of their database management system (DBMS) to apply critical security patches and performance enhancements. The deployment process requires careful planning to minimize downtime and ensure data consistency. Which of the following patching strategies is BEST suited for an environment requiring high availability and zero downtime?Database Management and Maintenance
  31. 131.A database administrator is tasked with installing a new MySQL server on a Linux host. After completing the installation, the administrator needs to secure the default root user. Which of the following commands is the most appropriate first step to secure the MySQL installation?Database Deployment
  32. 132.A database developer is creating a new `Orders` table. Each order must be uniquely identified, and the system should automatically generate a sequential number for each new order. Additionally, this unique identifier should be the primary means of looking up individual orders efficiently. Which type of key should be implemented for the `OrderID` column to meet these requirements?Database Fundamentals
  33. 133.An organization is migrating its customer relationship management (CRM) database to a new platform. During the migration, a security audit reveals that many database users have excessive privileges, including `DELETE` and `DROP` permissions on critical tables, even if their job function does not require them. What principle of database security is being violated, and what should the database administrator implement to correct this?Data and Database Security
  34. 134.A company is subject to strict data residency laws, requiring all customer data to remain within specific geographic boundaries. They are considering using a cloud-based database service. Which architectural consideration is MOST critical to ensure compliance with these laws?Data and Database Security
  35. 135.A database administrator is configuring a new production database that will store sensitive customer financial information. The organization has a strict regulatory requirement to protect data even if the underlying storage media is compromised or stolen. Which of the following security measures would best address this specific requirement for data at rest?Data and Database Security
  36. 136.A government agency is migrating a legacy database containing classified information to a new platform. Strict regulations mandate that all data, even when residing in backup files or temporary storage, must be completely unintelligible to unauthorized individuals. The chosen encryption method must also be robust enough to withstand future cryptographic advancements. Which type of encryption is MOST suitable for ensuring the long-term confidentiality of this highly sensitive data at rest?Data and Database Security
  37. 137.A database administrator is configuring a new production database server. To ensure optimal performance and stability, they need to separate different types of database files onto distinct physical disks or disk arrays. Which of the following file types should ideally be placed on its OWN dedicated, fast storage?Database Management and Maintenance
  38. 138.A database administrator needs to test the disaster recovery plan for a critical database. The test involves simulating a complete data center outage and verifying that the standby database can take over operations within the defined RTO. What type of backup and restore testing does this scenario represent?Business Continuity
  39. 139.A database administrator is investigating a report of slow data retrieval for a specific table (`orders`) that has millions of rows. Queries frequently filter by `customer_id` and then sort by `order_date`. The current index is only on `customer_id`. To significantly improve the performance of these queries, which of the following index types would be MOST effective?Database Management and Maintenance
  40. 140.A database administrator is deploying a new database server for a financial application that requires extremely low latency for transaction processing. After initial installation, the DBA observes higher-than-expected disk I/O latency. Which of the following storage configurations would be MOST effective in reducing disk I/O latency for this workload?Database Deployment
  41. 141.A data analyst is working with a `Sales` table that contains `SaleID`, `ProductID`, `CustomerID`, `SaleDate`, and `SaleAmount`. The analyst frequently needs to retrieve `SaleID` and `SaleAmount` for specific `SaleDate` ranges. To optimize these queries, which type of index would be most beneficial if `SaleDate` is the primary column for filtering and sorting, and `SaleID` and `SaleAmount` are frequently retrieved alongside it?Database Fundamentals
  42. 142.A database developer is writing a Python script to interact with a PostgreSQL database. The script needs to insert a new customer record with `CustomerID`, `FirstName`, `LastName`, and `Email`. To prevent SQL injection vulnerabilities and handle special characters correctly, which approach should the developer use when passing the customer data to the SQL `INSERT` statement?Database Fundamentals
  43. 143.A database administrator is tasked with optimizing a critical batch process that involves inserting millions of rows into a large table daily. The process is currently slow due to excessive logging and I/O. The database system allows for different recovery models or logging modes. Which recovery model or logging mode should the administrator consider to minimize logging overhead during this specific bulk insert operation, while still allowing for full point-in-time recovery for the rest of the database?Database Management and Maintenance
  44. 144.A company is evaluating disaster recovery solutions for its primary database. The business requires the ability to switch to a fully functional replica database in a secondary data center with minimal data loss (RPO < 5 minutes) and a recovery time of less than 1 hour (RTO < 1 hour). Which combination of replication and high-availability features would best meet these objectives?Business Continuity
  45. 145.A developer is writing a script to insert new customer records into a `Customers` table. Each customer record must include a unique `CustomerID`. Which SQL Data Definition Language (DDL) constraint ensures that no two customer records can have the same `CustomerID`?Database Fundamentals
  46. 146.A database administrator is reviewing the performance of a critical reporting database. They observe that many complex queries involving multiple `JOIN`s and `GROUP BY` clauses are consistently slow, even after ensuring proper indexing. The database has sufficient memory and CPU. The DBA suspects that the sheer volume of data being processed for each query is the bottleneck, requiring extensive temporary disk space for intermediate results. Which database tuning approach should they investigate to reduce the need for temporary disk space and speed up these complex queries?Database Management and Maintenance
  47. 147.A junior database administrator is tasked with ensuring the referential integrity of a customer database. They are specifically concerned about preventing a situation where a customer record is deleted, but associated order records remain, leading to 'orphan' data. Which of the following mechanisms should the administrator implement to enforce referential integrity and prevent this issue?Database Management and Maintenance
  48. 148.A database administrator is tasked with implementing a security measure that ensures users can only access the specific rows and columns of data relevant to their job function, even within a table they are authorized to view. For example, a regional sales manager should only see sales data for their region, and not for other regions. What advanced access control mechanism would best achieve this granular level of data restriction?Data and Database Security
  49. 149.A database architect is designing a schema for a new online library system. The system needs to store information about `Books` and `Authors`. Each book can have multiple authors, and each author can write multiple books. Which type of relationship is described between `Books` and `Authors`?Database Fundamentals
  50. 150.A database developer is writing a complex SQL query that involves joining five different tables and performing several aggregations. This query is executed frequently by multiple applications. To improve performance and reduce the parsing/compilation overhead each time it runs, what database object should the developer consider creating?Database Fundamentals