DS0-001 CompTIA DataSys+ Certification Exam Questions and Answers

The correct answer is B. To specify how data is sorted in a database . CompTIA DataSys+ explains that the COLLATE clause defines the collation rules applied to character-based data in a column. Collation determines how string data is compared, sorted, and evaluated, including rules for alphabetical order, case sensitivity, accent sensitivity, and character encoding . These rules directly affect query results, indexing behavior, and comparison operations involving textual data.

When a COLLATE clause is included in a column definition, it overrides the database or server default collation for that specific column. This is especially important in environments that support multiple languages or regional settings, where sorting and comparison rules may differ. For ex ample, case-insensitive collations treat uppercase and lowercase letters as equivalent, while case-sensitive collations do not. DataSys+ highlights that incorrect collation settings can lead to unexpected query results, inefficient indexing, or inconsistent application behavior.

Option A is incorrect because computed columns and indexing behavior are defined through expressions and index definitions, not collation rules. Option C , functional dependency, relates to normalization theory and the relationship between attributes in a relational schema; collation has no impact on dependency enforcement. Option D describes data validation, which is handled through constraints such as CHECK , NOT NULL , or data types—not collation.

CompTIA DataSys+ emphasizes that DBAs must understand how collation affects sorting, grouping, and comparison operations, especially for reporting and internationalized applications. Proper use of the COLLATE clause ensures consistent behavior across queries and prevents subtle bugs related to string comparisons.

Therefore, the primary purpose of including a COLLATE clause in a column definition is to control how textual data is sorted and compared , making option B the correct and verified answer.

A potential issue raised by enterprise database users is the need for concurrent access and multiuser updates. Concurrent access means that multiple users can access the same data at the same time, while multiuser updates mean that multiple users can modify the same data at the same time. These features are essential for enterprise database users who need to share and collaborate on data in real time. However, they also pose challenges such as maintaining data consistency, preventing conflicts or errors, and ensuring transaction isolation and durability. The other options are either not issues or not specific to enterprise database users. For example, the need for multiple views or windows into the same database may be a preference or a convenience, but not an issue; the need to manage long transactions may be a challenge for any database user, not just enterprise ones; the need to manually transfer records to paper may be an outdated or inefficient practice, but not an issue. References:  CompTIA DataSys+ Course Outline , Domain 1.0 Database Fundamentals, Objective 1.3 Given a scenario, identify common database issues.

The correct answer is C. Application high availability . CompTIA DataSys+ describes a multizone region as an architecture that deploys systems across multiple availability zones within the same geographic region to improve availability, fault tolerance, and resilience. Availability zones are physically separate data centers with independent power, cooling, and networking, but they are connected through low-latency links.

A key characteristic of a multizone disaster recovery (DR) design is high availability of applications . By distributing application components and databases across multiple zones, the system can continue operating even if one zone experiences a failure. DataSys+ emphasizes that multizone architectures are primarily designed to protect against localized infrastructure failures , such as power outages or hardware issues within a single zone, while maintaining service continuity.

Option A , three or more availability zones, is incorrect because a multizone region does not require a specific number of zones. Most implementations use two or more zones, and the exact count depends on the cloud provider and design requirements. Option B , a single geographic location, is misleading; while availability zones are within the same broader region, they are still separate physical locations and not a single data center. Option D , active/active deployment on secondary zones, more accurately describes a multi-region or advanced global DR strategy, not a basic multizone regional design. Secondary zones imply geographically separate regions used for regional disaster recovery.

CompTIA DataSys+ clearly distinguishes high availability from disaster recovery . Multizone deployments primarily focus on availability and fault tolerance within a region, while multi-region strategies address large-scale regional outages. In a multizone DR policy, applications are architected to remain available despite zone-level failures.

Therefore, the defining characteristic of a multizone region in a disaster recovery policy is application high availability , making option C the correct and fully verified answer.

The best option for the database administrator to use to update an organization’s ERD is a UML tool. A UML tool is a software application that allows users to create, edit, and visualize diagrams using the Unified Modeling Language (UML). UML is a standard language for modeling software systems and their components, such as classes, objects, relationships, behaviors, etc. UML can also be used to create entity relationship diagrams (ERDs), which are graphical representations of the entities (tables), attributes (columns), and relationships (constraints) in a database. A UML tool can help the administrator to update an organization’s ERD by providing features such as drag-and-drop, templates, symbols, validation, etc. The other options are either not suitable or not optimal for this task. For example, a word processor is a software application that allows users to create and edit text documents; a spreadsheet is a software application that allows users to organize and manipulate data in rows and columns; an HTML editor is a software application that allows users to create and edit web pages using HyperText Markup Language (HTML). References:  CompTIA DataSys+ Course Outline , Domain 2.0 Database Deployment, Objective 2.2 Given a scenario, create database objects using scripting and programming languages.

A reason to create a stored procedure is to improve performance. A stored procedure is a set of SQL statements or commands that are stored and compiled in the database server, and can be executed by name or by a trigger. A stored procedure can improve performance by reducing the network traffic between the client and the server, as only the name or the parameters of the stored procedure need to be sent, rather than the entire SQL code. A stored procedure can also improve performance by reusing the same execution plan, as the stored procedure is compiled only once and cached in the server memory. The other options are either not true or not relevant for this purpose. For example, a stored procedure does not necessarily minimize storage space, as it still occupies space in the database server; a stored procedure does not bypass case sensitivity requirements, as it still follows the rules of the database system; a stored procedure does not give control of the query logic to the user, as it is defined and maintained by the database administrator or developer. References:  CompTIA DataSys+ Course Outline , Domain 2.0 Database Deployment, Objective 2.2 Given a scenario, create database objects using scripting and programming languages.

The document that should be used to analyze the database’s structure is an ERD. An ERD, or Entity Relationship Diagram, is a graphical representation of the entities (tables), attributes (columns), and relationships (constraints) in a database. An ERD helps to visualize the structure and design of the database, as well as the dependencies and associations among the tables. An ERD can also help to evaluate the level of normalization of the database, which is a process that organizes data into tables and columns to reduce redundancy and improve consistency. By using an ERD, a database professional can consider denormalizing a database, which is a process that introduces some redundancy or duplication of data to improve performance or simplify queries. The other options are either different types of documents or not related to the database’s structure. For example, an SOP, or Standard Operating Procedure, is a document that describes the steps and procedures for performing a specific task or operation; a data dictionary is a document that describes the metadata (information about data) of a database; a UML diagram is a graphical representation of a software system or its components using the Unified Modeling Language (UML). References:  CompTIA DataSys+ Course Outline , Domain 2.0 Database Deployment, Objective 2.2 Given a scenario, create database objects using scripting and programming languages.

The most likely causes of the reporting application not working are that the access permissions for the service account used by the reporting application were not changed, and that the reporting application’s mapping to the database location was not updated. These two factors could prevent the reporting application from accessing the new database server. The other options are either irrelevant or unlikely to cause the problem. References:  CompTIA DataSys+ Course Outline , Domain 3.0 Database Management and Maintenance, Objective 3.2 Given a scenario, troubleshoot common database issues.

The characteristic of all non-relational databases is unstructured data. Unstructured data is data that does not have a predefined or fixed format, schema, or structure. Unstructured data can include various types of data, such as text, images, audio, video, etc. Non-relational databases, also known as NoSQL databases, are databases that store and manage unstructured data using different models, such as key-value, document, graph, columnar, etc. Non-relational databases are suitable for handling large volumes, variety, and velocity of data that do not fit well in the relational model. The other options are either characteristics of relational databases or not related to database types at all. For example, columns with the same data type, logical record groupings, and tabular schema are characteristics of relational databases, which are databases that store and manage structured data using tables, rows, columns, and constraints. References:  CompTIA DataSys+ Course Outline , Domain 1.0 Database Fundamentals, Objective 1.1 Given a scenario, identify common database types.

The option that is recommended in order to provide encrypted data communication pathways for information as it is transmitted over a network is TLS. TLS, or Transport Layer Security, is a protocol that provides secure communication over the internet by encrypting the data using cryptographic algorithms and keys. TLS also provides authentication and integrity by verifying the identity of the parties involved and ensuring that the data has not been altered or tampered with. TLS can be used to protect various types of data, such as web traffic, email, instant messaging, voice over IP, etc. The other options are either not related or not sufficient for this purpose. For example, TCP/IP, or Transmission Control Protocol/Internet Protocol, is a set of protocols that defines how data is transmitted and routed over the internet, but does not provide encryption or security; NFS, or Network File System, is a protocol that allows users to access and share files over a network, but does not provide encryption or security; SMB, or Server Message Block, is a protocol that allows users to access and share files, printers, and other resources over a network, but does not provide encryption or security. References:  CompTIA DataSys+ Course Outline , Domain 4.0 Data and Database Security, Objective 4.2 Given a scenario, implement security controls for databases.

The factor that must be considered to ensure data integrity during transmission is encryption. Encryption is a process that transforms data into an unreadable or scrambled form using an algorithm and a key. Encryption helps protect data integrity during transmission by preventing unauthorized access or modification of data by third parties, such as hackers, eavesdroppers, or interceptors. Encryption also helps verify the identity and authenticity of the source and destination of the data using digital signatures or certificates. The other options are either not related or not sufficient for this purpose. For example, bandwidth is the amount of data that can be transmitted over a network in a given time; redundancy is the duplication of data or components to provide backup or alternative sources in case of failure; masking is a technique that replaces sensitive data with fictitious but realistic data to protect its confidentiality or compliance. References:  CompTIA DataSys+ Course Outline , Domain 4.0 Data and Database Security, Objective 4.2 Given a scenario, implement security controls for databases.

The first task that the administrator should perform is to create a new service account exclusively for the business intelligence application. This will ensure that the application has the appropriate permissions and credentials to access the company’s transactional data. The other options are either unnecessary, inefficient, or insecure. For example, building a separate data warehouse would require additional resources and time, setting up a nightly FTP data transfer would expose the data to potential breaches, sending the TNS names file would not guarantee that the application can connect to the database, and opening a new port on the database server would create a vulnerability for attackers. References:  CompTIA DataSys+ Course Outline , Domain 2.0 Database Deployment, Objective 2.1 Given a scenario, install and configure database software and tools.

The correct answer is B. The rule set was configured to allow specific IP addresses . CompTIA DataSys+ materials emphasize the importance of network security controls, particularly firewall rules and access control lists (ACLs), in regulating remote database access. In this scenario, the database server is reachable by users in Brazil but not by users in Canada, which strongly indicates that access is being selectively permitted rather than globally blocked.

Firewall rule sets are often configured using IP-based allowlists , where only approved IP addresses or IP ranges are permitted to establish a connection. If the analyst allowed the IP address range associated with Brazil but did not include the IP range used by Canadian users, connections from Canada would fail while Brazilian users would still have access. This behavior aligns precisely with selective rule-based access control, a common best practice highlighted in DataSys+ for reducing attack surfaces.

Option A is incorrect because if the perimeter network were rejecting remote connections entirely, users in Brazil would also be unable to connect. Option C is incorrect for the same reason: a firewall rule denying all connections would block access from all locations, not just Canada. Option D , server port security being disabled, would typically prevent all inbound connections to the database service, again affecting all users regardless of geography.

CompTIA DataSys+ stresses that troubleshooting connectivity issues requires identifying whether failures are global or location-specific . Location-specific failures are most commonly caused by misconfigured firewall rules, IP filtering, or regional access restrictions. In database environments that support remote access, DBAs must ensure that firewall rules are consistently applied to all authorized networks.

Therefore, the most accurate and verified explanation is that the rule set was configured to allow specific IP addresses , and the Canadian users’ IP range was not included. This makes option B the correct answer.

A scenario in which a database administrator would use a relational database rather than a non-relational database is when an organization wants to maintain consistency among the data in the database. A relational database is a type of database that organizes data into tables with predefined columns and rows, and enforces rules and constraints to ensure data integrity and accuracy. A relational database also supports transactions, which are sets of operations that must be executed as a whole or not at all, to prevent data corruption or inconsistency. The other options are either not exclusive to relational databases or not relevant to the choice of database type. For example, data encryption can be applied to both relational and non-relational databases, processing complex data sets may require specialized tools or techniques that are not dependent on the database type, and storing a large number of videos, photos, and documents may be better suited for a non-relational database that can handle unstructured or semi-structured data. References:  CompTIA DataSys+ Course Outline , Domain 1.0 Database Fundamentals, Objective 1.1 Given a scenario, identify and apply database structure types.

The option that is part of logical database infrastructure security is perimeter network. Perimeter network, also known as DMZ (Demilitarized Zone), is a network segment that lies between an internal network and an external network, such as the internet. Perimeter network provides an additional layer of security for the internal network by isolating and protecting the servers or services that are exposed to the external network, such as web servers, email servers, database servers, etc. Perimeter network also helps prevent unauthorized access or attacks from the external network to the internal network by using firewalls, routers, proxies, etc. The other options are either part of physical database infrastructure security or not related to database infrastructure security at all. For example, surveillance is a method of monitoring and recording physical activities or events in a location or resource; biometric access is a device that uses biological characteristics to control access to a physical location or resource; cooling system is a device or system that regulates the temperature and humidity of a location or resource. References:  CompTIA DataSys+ Course Outline , Domain 4.0 Data and Database Security, Objective 4.1 Given a scenario, implement database infrastructure security.

The correct answer is D. The continuity of operations policy . CompTIA DataSys+ emphasizes that when an unexpected system failure occurs—especially during critical activities such as database migrations—the primary concern is restoring services, minimizing downtime, and ensuring data availability. The continuity of operations policy (often aligned with business continuity and disaster recovery planning) provides documented guidance on how to respond to system outages and operational disruptions.

A continuity of operations policy outlines procedures, roles, and responsibilities for maintaining or rapidly restoring essential systems after an incident. This includes steps for failover, rollback procedures, recovery prioritization, communication protocols, and escalation paths. During a migration-related crash, administrators rely on this policy to determine whether to restore from backups, revert to a previous database version, activate standby systems, or follow defined recovery time objectives (RTOs) and recovery point objectives (RPOs). DataSys+ highlights these policies as critical for ensuring predictable and coordinated responses during system failures.

Option A , the data retention policy, focuses on how long data must be stored and when it can be archived or deleted. It does not provide operational guidance for handling system crashes. Option B , the access control policy, governs authentication and authorization rules and is unrelated to recovery actions. Option C , the database compliance policy, addresses regulatory and audit requirements but does not define technical recovery procedures during an outage.

CompTIA DataSys+ clearly distinguishes governance and security policies from operational resilience policies . When a database crashes during a migration, the administrator’s priority is service restoration and data integrity, both of which are addressed by continuity of operations planning.

Therefore, the systems administrator should refer to the continuity of operations policy , making option D the correct and fully verified answer.

The two options that can be used to protect physical database appliances from damage in a server room are fire suppression systems and cooling systems. Fire suppression systems are systems that detect and extinguish fires in a server room using water, gas, foam, or other agents. Fire suppression systems help prevent damage to physical database appliances caused by fire hazards such as overheating, electrical faults, or flammable materials. Cooling systems are systems that regulate the temperature and humidity in a server room using fans, air conditioners, chillers, or other devices. Cooling systems help prevent damage to physical database appliances caused by excessive heat or moisture that may affect their performance or lifespan. The other options are either not related or not effective for this purpose. For example, biometric access systems, camera systems, and key card systems are systems that control the access to a server room using fingerprints, facial recognition, video surveillance, or magnetic card s; these systems help prevent unauthorized entry or theft of physical database appliances, but not damage caused by environmental factors; database control systems are systems that manage the functionality and security of databases using software tools or commands; these systems help protect logical database appliances from errors or attacks, but not physical damage caused by environmental factors. References:  CompTIA DataSys+ Course Outline , Domain 5.0 Business Continuity, Objective 5.4 Given a scenario, implement disaster recovery methods.

The correct answer is C. View creation . CompTIA DataSys+ places strong emphasis on protecting personally identifiable information (PII) while still enabling developers and analysts to perform their required tasks. In environments such as financial institutions, direct access to tables containing sensitive data presents a significant security and compliance risk, especially for junior developers who do not require full visibility of production data.

Creating views is a best-practice solution that allows database administrators to expose only the necessary columns and rows while hiding or excluding sensitive fields such as Social Security numbers, account numbers, or personal contact details. Views act as a controlled abstraction layer over the underlying tables, enabling developers to work with realistic data structures without direct access to raw PII. DataSys+ highlights views as an effective method for enforcing the principle of least privilege while maintaining usability and performance.

Option A , role assignment, is important for access control but is too broad on its own. Assigning a role may still allow access to sensitive columns unless combined with views or column-level permissions. Option B , data masking, is often used for non-production environments or dynamic data protection, but it may not be the best solution when developers need structured access to table data for development or testing purposes. Option D , policy enforcement, refers to governance and administrative controls but does not directly provide a technical mechanism for safe table access.

CompTIA DataSys+ stresses that database security should balance protection with operational efficiency. Views allow DBAs to centrally manage what data is exposed, simplify auditing, and reduce the risk of accidental data leakage. They are especially valuable in regulated industries where compliance and data minimization are critical.

Therefore, the best option for providing junior developers with access while protecting PII is view creation , making option C the correct and fully verified answer.

The correct answers are C. Entity Framework and E. Hibernate . CompTIA DataSys+ defines Object-Relational Mapping (ORM) tools as software frameworks that allow developers to interact with relational databases using object-oriented programming concepts instead of writing raw SQL queries. ORM tools map database tables to application objects, rows to object instances, and columns to object attributes, simplifying database access and improving developer productivity.

Entity Framework is a widely used ORM tool in the Microsoft ecosystem, commonly paired with .NET applications. It enables developers to define data models in code and allows the framework to automatically generate SQL queries, manage relationships, and handle CRUD (Create, Read, Update, Delete) operations. DataSys+ highlights ORM tools like Entity Framework as a way to reduce repetitive SQL coding while maintaining consistency and abstraction between application logic and the database layer.

Hibernate is a popular ORM framework used primarily in Java-based applications. Similar to Entity Framework, Hibernate manages database interactions by mapping Java objects to relational database structures. It handles SQL generation, transaction management, and caching, making it a core example of an ORM solution referenced in DataSys+ learning objectives.

Option A , PL/SQL, and option D , T-SQL, are procedural SQL languages , not ORM tools. They are used to write stored procedures, functions, and scripts that execute directly within the database. Option B , XML, is a data format used for data representation and exchange, not for object-relational mapping. Option F , PHP, is a general-purpose programming language that can use ORM tools but is not itself an ORM framework.

CompTIA DataSys+ emphasizes understanding the distinction between database languages , programming languages , and abstraction frameworks like ORMs. Entity Framework and Hibernate clearly fit the definition of ORM tools by bridging object-oriented applications and relational databases.

Therefore, the correct and fully verified answers are C and E .

The option that sets the age requirement for data that should be recovered after a major disaster is RPO. RPO, or Recovery Point Objective, is a metric that defines the maximum amount of data that can be lost or acceptable data loss in the event of a disaster or disruption. RPO indicates how frequently the data should be backed up or replicated to minimize the risk of data loss. RPO also sets the age requirement for data that should be recovered after a major disaster, as it determines how far back in time the recovery process should go. For example, if the RPO is one hour, then the data should be backed up or replicated every hour, and the recovery process should restore the data to the state it was in one hour before the disaster. The other options are either different metrics or not related to data recovery at all. For example, MTBF, or Mean Time Between Failures, is a metric that measures the average time that a system or component operates without failure; RTO, or Recovery Time Objective, is a metric that defines the maximum amount of time that can be taken to restore a system or service after a disaster or disruption; MTTF, or Mean Time To Failure, is a metric that measures the average time that a system or component operates until it fails. References:  CompTIA DataSys+ Course Outline , Domain 5.0 Business Continuity, Objective 5.3 Given a scenario, implement backup and restoration of data.

The advantage of creating indexes is to provide quick and efficient access to data. An index is a data structure that stores the values of one or more columns of a table in a sorted order, along with pointers to the corresponding rows in the table. An index helps to speed up queries that search, filter, sort, or join data based on the indexed columns, as it reduces the number of disk accesses or scans required to locate the desired data. An index also helps to enforce uniqueness or referential integrity constraints on the indexed columns. The other options are either not true or not relevant for this purpose. For example, an index does not help with space allocation, as it consumes additional space in the database; an index does not reduce memory, as it may use memory for caching or buffering purposes; an index does not update the query plan, as it is an input or a factor for the query optimizer to generate the query plan. References:  CompTIA DataSys+ Course Outline , Domain 1.0 Database Fundamentals, Objective 1.2 Given a scenario, execute database tasks using scripting and programming languages.

The correct answer is D. Password policies . CompTIA DataSys+ emphasizes that password policies are a core component of authentication and security controls within database systems and enterprise environments. When new users are required to change their credentials for the first time, the system typically enforces predefined password rules. If the new credentials do not meet these rules, the system will reject them automatically.

Password policies define specific requirements such as minimum password length, complexity (use of uppercase letters, lowercase letters, numbers, and special characters), password history, expiration intervals, and restrictions against using previously compromised or common passwords. In many environments, first-time password changes are subject to the strictest enforcement of these rules to ensure strong baseline security from the start of a user’s lifecycle.

Option A , identity management, focuses on the creation, provisioning, and lifecycle management of user identities across systems. While identity management systems initiate the credential change process, they do not usually enforce the detailed rules that cause a password to be rejected. Option B , access controls, govern what resources a user can access after authentication, not whether a password itself is valid. Option C , service accounts, are non-interactive accounts used by applications or services and are unrelated to new end-user credential changes.

DataSys+ documentation highlights that authentication failures during credential updates are most commonly tied to policy enforcement , not permissions or account type. This includes scenarios where users unknowingly violate complexity rules or reuse default or temporary passwords that are explicitly disallowed.

Therefore, the rejection of new credentials during a first-time password change is best explained by password policies , making option D the most accurate and verified answer according to CompTIA DataSys+ principles.

The most likely cause of the issue is that the SQL Server ports to the main machine are closed. SQL Server uses TCP/IP ports to communicate with clients and other servers. If these ports are blocked by a firewall or other network device, the connection will fail. The administrator should check the port configuration on both the server and the user’s workstation, and make sure that they are open and match the expected values. The other options are either unlikely or unrelated to the issue. For example, the SQL Server codes performing badly or having many concurrent users may affect the performance or availability of the server, but not prevent the connection entirely; the SQL Server not being tested properly may cause errors or bugs in the functionality or security of the server, but not affect the connection unless there is a configuration problem. References:  CompTIA DataSys+ Course Outline , Domain 2.0 Database Deployment, Objective 2.3 Given a scenario, troubleshoot common database deployment issues.

The SQL syntax that the administrator should use to create an object is option B. This syntax uses the  CREATE TABLE  statement to define a new table named  customer  with four columns:  customer_id ,  name ,  email , and  phone . Each column has a data type and a constraint, such as  NOT NULL  or  PRIMARY KEY . The other options either have syntax errors, use incorrect keywords, or do not specify the table name or columns correctly. References:  CompTIA DataSys+ Course Outline , Domain 1.0 Database Fundamentals, Objective 1.1 Given a scenario, identify and apply database structure types.

The correct answer is B. Checkpoints . CompTIA DataSys+ explains that checkpoints play a critical role in database recovery mechanisms by limiting the amount of transaction log data that must be processed during recovery . Log-based recovery relies on transaction logs to restore the database to a consistent state after a failure. If checkpoints are infrequent, the database engine must replay a large portion of the transaction log, which increases recovery time and system overhead.

A checkpoint is a process where the database writes all modified (dirty) pages from memory to disk and records a synchronization point in the transaction log. After a checkpoint completes, the database knows that all changes up to that point are safely stored on disk. During recovery, the system only needs to process log entries that occurred after the most recent checkpoint , significantly reducing recovery workload and time. DataSys+ highlights checkpoints as a key performance and availability feature, especially in systems with high transaction volumes.

Option A , deadlocks, are concurrency issues that occur when two or more transactions block each other indefinitely. While deadlock handling is important for transaction throughput, it does not reduce log-based recovery overhead. Option C , locks, control concurrent access to data and help maintain consistency, but they do not affect how much log data must be replayed during recovery. Option D , indexes, improve query performance but can actually increase logging activity because index changes are also logged.

CompTIA DataSys+ emphasizes that effective recovery planning includes optimizing logging behavior and checkpoint frequency. Properly configured checkpoints strike a balance between runtime performance and recovery efficiency by reducing excessive log replay without causing unnecessary disk I/O.

Therefore, to best mitigate the overhead caused by log-based recoveries, the database manager should use checkpoints , making option B the correct and fully verified answer.

The statement that the administrator should use to remove inactive customers from a database is option A. This statement uses the  DELETE  command to delete all the rows from the  customer  table where the  status  column is equal to  ' inactive ' . The other options either have syntax errors, use incorrect commands, or do not specify the condition correctly. References:  CompTIA DataSys+ Course Outline , Domain 1.0 Database Fundamentals, Objective 1.2 Given a scenario, execute database tasks using scripting and programming languages.

The constraint that is used to enforce referential integrity is foreign key. A foreign key is a column or a set of columns in a table that references the primary key of another table. A primary key is a column or a set of columns in a table that uniquely identifies each row in the table. Referential integrity is a rule that ensures that the values in the foreign key column match the values in the primary key column of the referenced table. Referential integrity helps maintain the consistency and accuracy of the data across related tables. The other options are either different types of constraints or not related to referential integrity at all. For example, a surrogate key is a column that is artificially generated to serve as a primary key, such as an auto-increment number or a GUID (Globally Unique Identifier); a unique key is a column or a set of columns in a table that uniquely identifies each row in the table, but it can have null values unlike a primary key; there is no such constraint as TID. References:  CompTIA DataSys+ Course Outline , Domain 1.0 Database Fundamentals, Objective 1.2 Given a scenario, execute database tasks using scripting and programming languages.

The two factors that the database administrator should monitor to gauge server health are CPU usage and memory usage. CPU usage is the percentage of time that the processor (CPU) of the server is busy executing instructions or processes. CPU usage indicates how much workload the server can handle and how fast it can process requests. High CPU usage may affect the performance or availability of the server and cause delays or errors. Memory usage is the amount of physical memory (RAM) or virtual memory (swap space) that the server uses to store data or run applications. Memory usage indicates how much space the server has to store temporary or intermediate data or results. High memory usage may affect the performance or availability of the server and cause swapping or paging. The other options are either not relevant or not direct indicators of server health. For example, transaction logs are files that record the changes made by transactions on the database; network sniffer is a tool that captures and analyzes network traffic; domain controllers are servers that manage user authentication and authorization in a network; firewall traffic is the amount of data that passes through a firewall device or software. References:  CompTIA DataSys+ Course Outline , Domain 3.0 Database Management and Maintenance, Objective 3.2 Given a scenario, monitor database performance.

The correct answer is A. Columnstore . According to CompTIA DataSys+, a columnstore index stores data in a column-oriented, tabular structure , where each column of a table is stored separately rather than storing entire rows together. This design is particularly optimized for analytical and reporting workloads , which commonly scan large datasets and perform aggregations on specific columns.

In a columnstore index, data is logically still represented in a table-like (tabular) format, but physically organized by columns instead of rows. This allows the database engine to read only the required columns for a query, significantly reducing disk I/O and improving query performance. DataSys+ highlights columnstore indexes as a best practice for data warehousing, business intelligence, and read-heavy analytical queries .

Option B , a non-clustered index, stores a separate index structure that contains indexed column values and pointers to the underlying table rows. While it improves lookup performance, it does not store data in a tabular columnar format. Option C , a unique index, is a constraint-based index that enforces uniqueness on column values but does not define how data is physically stored. Option D , a secondary index, is a general term for indexes that are not primary and does not specifically describe column-based tabular storage.

Sys+ emphasizes understanding different index types and their appropriate use cases. Columnstore indexes are distinct because they physically reorganize data by columns , compress data efficiently, and support high-performance analytics. This makes them especially effective in scenarios involving large datasets and complex queries.

Therefore, the index type that stores records in a tabular, column-oriented format is the columnstore index , making option A the correct and fully verified answer aligned with CompTIA DataSys+ objectives.

The correct answer is D. It allows connections to databases through scripts . CompTIA DataSys+ documentation describes PowerShell as a powerful automation and scripting language primarily used in Windows-based environments to manage systems, applications, and databases. In a database context, PowerShell enables administrators to automate repetitive tasks, execute administrative commands, and interact with databases programmatically through scripted workflows.

PowerShell is commonly used by database administrators to perform tasks such as running scheduled queries, managing database services, monitoring performance metrics, automating backups and restores, and handling user or permission management. Its strength lies in scripting and orchestration rather than providing a specific database connectivity protocol. Through scripts, PowerShell can leverage database providers, command-line utilities, or modules to connect to and manage database systems efficiently.

Option A is incorrect because Python database connectivity is handled through Python libraries and drivers, not PowerShell. Option B is also incorrect because PowerShell is not designed to enable Linux systems to connect to databases; while PowerShell Core is cross-platform, its func tion is still automation and scripting, not system-level database connectivity. Option C is misleading because ODBC is a database connectivity standard that can be used by many tools and languages, but PowerShell itself is not defined by its use of ODBC. While PowerShell scripts can invoke ODBC connections, that is not its primary or defining function.

CompTIA DataSys+ emphasizes the role of automation tools in database operations. PowerShell fits squarely into this category by enabling DBAs to create repeatable, auditable, and scalable scripts that interact with databases and supporting infrastructure. Its use improves operational efficiency, reduces manual errors, and supports consistent database administration practices.

Therefore, the best and most accurate description of PowerShell’s function in a database environment is allowing connections and management operations through scripts , making D the correct answer.

The NoSQL database type that best categorizes MongoDB is document. Document databases are databases that store and manage data as documents, which are collections of fields and values in formats such as JSON (JavaScript Object Notation) or XML (Extensible Markup Language). Document databases do not use any schema or structure to organize data, but rather use identifiers or indexes to enable flexible and dynamic access to data based on fields or values. Document databases are suitable for storing large amounts of complex or unstructured data that have variable attributes or nested structures. MongoDB is an example of a document database that uses JSON-like documents to store and query data. The other options are either different types of NoSQL databases or not related to NoSQL databases at all. For example, column-oriented databases are databases that store and manage data as columns rather than rows; graph databases are databases that store and manage data as nodes and edges that represent entities and relationships; key-value stores are databases that store and manage data as pairs of keys and values. References:  CompTIA DataSys+ Course Outline , Domain 1.0 Database Fundamentals, Objective 1.1 Given a scenario, identify common database types.

The correct answer is D. Envelope encryption . CompTIA DataSys+ describes envelope encryption as a key management and data protection method that combines the efficiency of symmetric encryption with the security and manageability of hierarchical key structures. In this approach, data is first encrypted using a data encryption key (DEK) , and then the DEK itself is encrypted (or “wrapped”) using a key encryption key (KEK) , often referred to as a managed root key.

Envelope encryption is widely used in enterprise database systems and cloud platforms because it provides strong security while simplifying key rotation and management. Encrypting large volumes of data directly with a root or master key would be inefficient and risky. Instead, DataSys+ explains that symmetric DEKs are used for fast data encryption, while the root key is used only to protect the DEKs. If a root key must be rotated or compromised, only the wrapped DEKs need to be re-encrypted—not the underlying data.

Option A , asymmetric encryption, uses public and private key pairs but is computationally expensive and not typically used to encrypt large datasets directly. Option B , DEK-based encryption, is incomplete because it describes only the use of data encryption keys and does not account for the additional wrapping layer that defines envelope encryption. Option C , symmetric encryption, correctly describes how data is encrypted but does not include the managed key hierarchy required by the question.

CompTIA DataSys+ emphasizes envelope encryption as a best practice for data-at-rest protection , particularly in environments that require compliance, auditing, and centralized key management. It is commonly implemented using hardware security modules (HSMs) or cloud key management services.

Therefore, the method where data is encrypted with a DEK and then wrapped with a managed root key is envelope encryption , making option D the correct and fully verified answer.

The script that would set the database recovery model for sys.database is option A. This script uses the  ALTER DATABASE  statement to modify the recovery model of the sys.database to full with no wait. The other options either have syntax errors, use incorrect keywords, or do not specify the recovery model correctly. References:  CompTIA DataSys+ Course Outline , Domain 3.0 Database Management and Maintenance, Objective 3.1 Given a scenario, perform common database maintenance tasks.