Step2Study

CompTIA DataSys+ (DS0-001) practice questions

231 free questions with answers and explanations.

Practice test
  1. 151.A database administrator is tasked with implementing a new security policy for a critical production database. The policy mandates that all user accounts must be automatically locked after three consecutive failed login attempts within a five-minute window. Additionally, accounts should remain locked for a minimum of 30 minutes before being eligible for unlock. Which access control mechanism and associated configuration settings are being described?Data and Database Security
  2. 152.A database developer is writing SQL queries for a new reporting module. They need to retrieve the total sales for each product category over the last year. The database contains millions of sales records. The current query uses a `GROUP BY` clause on `product_category` and `SUM(sales_amount)`. To optimize this query for faster execution, which of the following techniques should the developer consider?Database Management and Maintenance
  3. 153.A data engineer is designing a disaster recovery strategy for a critical OLTP database. The business requires that in the event of a disaster, the maximum acceptable data loss is 15 minutes. Which metric does this requirement directly define?Business Continuity
  4. 154.A database administrator is evaluating different database technologies for a new application that will handle massive volumes of unstructured and semi-structured data, such as social media posts, sensor data, and IoT logs. The application requires extreme scalability, high availability, and flexible schema design, often at the expense of strict ACID compliance. Which type of database would be most suitable for this scenario?Database Fundamentals
  5. 155.A data engineer is working with a large dataset of customer transactions. The data currently has redundant information, such as repeating customer addresses for every transaction made by the same customer. The engineer wants to restructure the database to eliminate this redundancy and improve data integrity. Which database design process should the engineer apply?Database Fundamentals
  6. 156.A healthcare organization is developing a new patient record system. Due to strict HIPAA compliance requirements, all patient health information (PHI) must be protected during transmission between the web application servers and the database servers. Which of the following protocols is best suited to ensure data confidentiality and integrity during this transit?Data and Database Security
  7. 157.A database administrator is reviewing the database's performance during peak hours. They notice that the `buffer cache hit ratio` is consistently below 80%, and disk I/O for data reads is very high. This indicates that the database is frequently retrieving data directly from disk rather than from memory. Which of the following actions would MOST directly improve the `buffer cache hit ratio` and reduce disk I/O for reads?Database Management and Maintenance
  8. 158.A database administrator is setting up a new PostgreSQL database server on a Linux host. After installation, the administrator attempts to connect to the database from a remote client but receives a connection refused error. The administrator has verified network connectivity and firewall rules. Which PostgreSQL configuration parameter is MOST likely preventing the remote connection?Database Deployment
  9. 159.A healthcare provider is upgrading its patient management system. The new system will allow authorized medical staff to access patient records from various remote clinics over a public network. Due to HIPAA regulations, all data exchanged between the remote clinics and the central database must be protected from eavesdropping. Which protocol or technology is MOST effective for securing this data in transit?Data and Database Security
  10. 160.A company is subject to GDPR regulations and needs to ensure that customer data is deleted upon request ('right to be forgotten'). However, certain historical transaction records must be retained for seven years for financial auditing purposes. Which compliance strategy best balances these conflicting requirements for data lifecycle management?Data and Database Security
  11. 161.A data engineer is working with a large dataset of customer transactions. The current `Transactions` table includes `TransactionID`, `CustomerID`, `CustomerName`, `CustomerAddress`, `ProductID`, `ProductName`, `ProductPrice`, and `Quantity`. The engineer observes that `CustomerName` and `CustomerAddress` are repeated for every transaction made by the same customer, and `ProductName` and `ProductPrice` are repeated for every transaction involving the same product. Which normalization form would address these repeating non-key attributes by ensuring that all non-key attributes are fully functionally dependent on the primary key?Database Fundamentals
  12. 162.A financial institution is implementing a new database system to comply with PCI DSS requirements for protecting cardholder data. The standard mandates that sensitive authentication data must not be stored after authorization. Which database security measure directly addresses this requirement?Data and Database Security
  13. 163.A database administrator is planning the network configuration for a new database server. The database will handle sensitive customer data and connect to an application server. To ensure secure communication between the application and the database, which port configuration is generally considered a best practice for MySQL?Database Deployment
  14. 164.A large e-commerce platform experienced a data breach where an attacker gained unauthorized access to their customer database through a misconfigured API endpoint. The database administrator needs to implement a solution that specifically monitors and analyzes database traffic, detects suspicious activities, and can alert security personnel in real-time. Which security tool would be most effective for this purpose?Data and Database Security
  15. 165.A database administrator is evaluating a database server for a new application that will experience predictable, high sustained read and write throughput, but with occasional spikes in demand. The current plan is to use a virtual machine (VM) with dedicated resources. Which of the following virtualization features is MOST important to ensure consistent performance during peak loads?Database Deployment
  16. 166.A data analyst needs to retrieve the average salary of employees in each department, but only for departments where the average salary is above $60,000. Which clause should be used after the `GROUP BY` clause to filter these aggregated results?Database Fundamentals
  17. 167.A data analyst needs to work with a production database containing sensitive customer information for development and testing purposes. To comply with privacy regulations and prevent exposure of real customer data, the analyst requires a dataset that retains the structural characteristics and data types of the original but contains fictitious values. Which data security technique should be used?Data and Database Security
  18. 168.A financial institution is implementing a new database system that will store highly sensitive customer financial data. Regulatory compliance requires that even if an unauthorized individual gains access to the database files directly, the data must remain unreadable. Which security measure is MOST appropriate to meet this specific requirement?Data and Database Security
  19. 169.A database administrator is tasked with setting up a new production database server for an application that requires high availability and minimal downtime for maintenance. They need a strategy to apply database patches and upgrades without taking the entire system offline. Which deployment strategy best fits these requirements?Database Management and Maintenance
  20. 170.A data analytics team frequently needs to view a combined dataset of `CustomerName`, `ProductName`, and `OrderDate` from the `Customers`, `Products`, and `Orders` tables. They want to avoid writing complex JOIN statements every time and also want to restrict access to only these specific columns. Which database object should the database administrator create to meet these requirements?Database Fundamentals
  21. 171.A software development team is building an application that frequently needs to retrieve user profile information based on a `UserID`. The `Users` table contains millions of records, and queries for `UserID` are becoming slow. Which SQL Data Definition Language (DDL) command should the team use to speed up these lookups?Database Fundamentals
  22. 172.A database administrator is setting up a high availability solution for a read-heavy application. The goal is to distribute read queries across multiple database instances to improve performance and provide fault tolerance for read operations, without impacting write performance on the primary. Which replication strategy is most suitable for this scenario?Business Continuity
  23. 173.A data engineer is optimizing a large `Transactions` table with millions of records. Queries frequently filter by `TransactionDate` and then sort by `TransactionAmount`. To speed up these specific queries, which type of index would be most beneficial?Database Fundamentals
  24. 174.A data engineer is designing a data warehouse and needs to implement a mechanism to track changes to slowly changing dimensions (SCDs) Type 2. They want to maintain a full history of changes for analytical purposes. Which of the following approaches should they use to achieve this?Database Management and Maintenance
  25. 175.A healthcare provider is upgrading its patient management system. The new system will allow authorized medical staff to access patient records from various clinics over a wide area network. Due to HIPAA regulations, all data exchanged between clinics and the central database must be protected from eavesdropping and tampering. Which of the following security measures is MOST appropriate to ensure data confidentiality and integrity during transmission?Data and Database Security
  26. 176.A database administrator is configuring security for a new customer relationship management (CRM) database. The company's policy states that after five consecutive failed login attempts, a user account must be temporarily disabled to prevent brute-force attacks. Which security control should the administrator implement to enforce this policy?Data and Database Security
  27. 177.A database administrator is planning the deployment of a new transactional database system that will experience high write concurrency. Which of the following database configuration parameters is MOST critical to optimize for performance and prevent contention?Database Deployment
  28. 178.A database administrator is planning the storage for a new database that will host a real-time analytics application. The application frequently performs complex aggregations and joins across very large datasets, where query performance is paramount, but data updates are infrequent. Which database indexing strategy would be most beneficial for this workload?Database Deployment
  29. 179.A company is planning to deploy a critical reporting database that must be available 24/7 with minimal downtime. The database will serve read-heavy analytical queries. To achieve high availability and improve read performance, the architect proposes a solution involving multiple database instances. Which deployment strategy is most appropriate?Database Deployment
  30. 180.A database administrator is setting up a new production database server. To ensure optimal performance and long-term stability, they need to configure the database to automatically reclaim unused space from deleted or updated rows without requiring manual intervention during peak hours. Which of the following mechanisms best achieves this goal?Database Management and Maintenance
  31. 181.A development team has requested a new database instance for a highly sensitive application. The database will store personally identifiable information (PII) and must comply with strict regulatory requirements. As part of the installation and configuration, the database administrator needs to ensure that data at rest is encrypted. Which of the following is the most effective method to achieve this for the database files?Database Deployment
  32. 182.A data engineer is designing a new database for a global e-commerce application. To ensure data integrity, they need to prevent duplicate entries in the 'customer_email' column, while also allowing some historical records to exist where the email might be NULL if it was not collected. Which constraint is the most appropriate for the 'customer_email' column?Database Management and Maintenance
  33. 183.A company is developing a new analytics platform that requires processing large volumes of sensitive customer data. To protect the privacy of individuals while still allowing data scientists to perform statistical analysis, the company wants to transform the data so that individual records cannot be linked back to a specific person, but the statistical properties of the dataset remain intact. Which data transformation technique would best achieve this goal?Data and Database Security
  34. 184.A data engineering team is deploying a new data lake solution that requires a database capable of handling petabytes of unstructured and semi-structured data, supporting flexible schema, and integrating seamlessly with various data processing frameworks like Spark. Which type of database would be most suitable for this scenario?Database Deployment
  35. 185.A database administrator is setting up a new database system for a highly available, globally distributed application. The application needs to handle millions of simultaneous users and tolerate node failures without data loss. Data consistency is important but can be eventually consistent, meaning that updates might not be immediately visible across all replicas but will propagate over time. Which architectural principle is being prioritized for this database system?Database Fundamentals
  36. 186.A database developer is writing a script to create a new table called `AuditLog` that will record every user login attempt. This table needs to store the `LoginTime` (timestamp), `UserID` (integer), `IPAddress` (string), and `Success` (boolean). Which SQL DDL statement correctly defines this table structure?Database Fundamentals
  37. 187.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 reporting tools. To improve performance and reusability, the developer wants to encapsulate this query logic and make it callable with different parameters. Which database object should be used?Database Fundamentals
  38. 188.A research institution is sharing a dataset containing patient health information with external collaborators for statistical analysis. To comply with privacy regulations like HIPAA, the institution needs to ensure that individual patients cannot be identified from the shared dataset, even by combining information from multiple sources. However, the statistical integrity of the data must be preserved. Which data privacy technique is most suitable for this scenario?Data and Database Security
  39. 189.A large enterprise database contains millions of records, including sensitive customer data and intellectual property. The security team wants to implement a solution that provides real-time, continuous monitoring of all SQL statements executed against the database, including stored procedures, and the ability to detect anomalous behavior without significantly impacting database performance. Which advanced security solution is BEST suited for this requirement?Data and Database Security
  40. 190.A database administrator is configuring a database for a new application that processes sensitive financial transactions. They need to ensure that database changes are recorded in a way that provides an immutable, auditable trail of all operations, including who made them and when. Which database feature is primarily designed for this purpose?Database Management and Maintenance
  41. 191.A database administrator is investigating slow query performance for a critical report. The report query involves joining several large tables and filtering on a date range. The administrator observes that the query execution plan consistently shows a 'Table Scan' operation on one of the largest tables, even though an index exists on the date column used in the WHERE clause. What is the MOST likely reason for the optimizer to choose a table scan over an index seek in this scenario?Database Management and Maintenance
  42. 192.A database administrator is designing a backup strategy for a mission-critical database where storage space is a significant concern. The business requires the ability to restore data from any point in time within the last 30 days, with a maximum data loss of 1 hour. Which backup type, when combined with a weekly full backup, would be the most storage-efficient while meeting the RPO?Business Continuity
  43. 193.A database administrator is configuring a new production database that stores sensitive financial transactions. Due to regulatory requirements, all access to this database must be recorded, including successful and failed login attempts, as well as modifications to critical tables. Which of the following database features should be primarily configured to meet these requirements?Data and Database Security
  44. 194.A multinational corporation is implementing a new customer relationship management (CRM) system that will store customer personal data across various regions. To comply with diverse data protection regulations (e.g., GDPR, CCPA), the company needs a mechanism to apply different data handling rules to data originating from different jurisdictions while keeping it within a single logical database. Which security concept BEST supports this requirement?Data and Database Security
  45. 195.A database administrator is planning to deploy a new database system that will primarily store unstructured documents, such as articles and user-generated content, and requires flexible schema capabilities. Which of the following database types is BEST suited for this requirement?Database Deployment
  46. 196.A database administrator is planning to deploy a new PostgreSQL database server on a Linux host. The application requires that only connections originating from a specific subnet (192.168.1.0/24) are allowed to access the database. Which configuration file and setting should the administrator modify to enforce this network access control?Database Deployment
  47. 197.A database administrator is planning to deploy a new highly available database system for an e-commerce platform. The requirement is that in case of a primary database failure, a standby replica must automatically take over with minimal downtime and no data loss. Which replication strategy BEST meets these requirements?Database Deployment
  48. 198.A database administrator is deploying a new database server for a financial application that requires extremely low latency for complex analytical queries involving multiple joins across large datasets. The primary concern is query performance, and the data is mostly read-intensive. Which type of database index would be MOST beneficial for this scenario?Database Deployment
  49. 199.A database administrator is planning the storage for a new transactional database system that requires high IOPS and low latency for small, random read/write operations. Which storage solution would provide the BEST performance for this workload?Database Deployment
  50. 200.A database administrator is tasked with deploying a new PostgreSQL database server for a critical financial application. The application requires high availability and automatic failover in case of a primary server failure. Which architectural component is typically used in conjunction with PostgreSQL streaming replication to achieve automatic failover?Database Deployment