View Full CompTIA DataSys+ DS0-001 Exam Dumps and Practice Test Dumps

 

Question 121.

A database administrator wants to improve the performance of a query that filters on Status and OrderDate and sorts by OrderDate. What should be reviewed FIRST?

1. Whether an appropriate composite index could support the filter and sort
2. Whether the database name should be shortened
3. Whether the backup retention period is too long
4. Whether user passwords are complex enough

Correct Answer: 1. Whether an appropriate composite index could support the filter and sort

Explanation:

A composite index can sometimes improve queries that filter and sort on multiple columns, especially when the column order matches common access patterns. The administrator should review the execution plan and workload before adding the index. Indexes consume storage and increase write-maintenance cost, so they should be designed based on evidence rather than created automatically.

Question 122.

Which SQL statement is used to create a new table?

1. INSERT TABLE
2. CREATE TABLE
3. ALTER VIEW
4. GRANT TABLE

Correct Answer: 2. CREATE TABLE

Explanation:

CREATE TABLE defines a new relational table, including column names, data types, and optional constraints. It is part of SQL data definition language. INSERT adds rows to an existing table, ALTER changes an existing object, and GRANT assigns privileges. Table creation should follow schema standards and include appropriate keys and constraints.

Question 123.

A database administrator wants to rename or change the definition of an existing database object. Which SQL command family is MOST relevant?

1. SELECT
2. COMMIT
3. ALTER
4. ROLLBACK

Correct Answer: 3. ALTER

Explanation:

ALTER statements modify the definition of existing database objects such as tables, columns, constraints, and sometimes views or other objects depending on the platform. Structural changes should be planned and tested because they can affect applications, indexes, and data compatibility. SELECT retrieves data, while COMMIT and ROLLBACK control transactions.

Question 124.

A table must store a date and time indicating when each row was created. Which design is MOST appropriate?

1. Store the value only in application logs
2. Use a free-text column populated manually
3. Use the primary key as the creation time
4. Use an appropriate date/time column with a controlled default where supported**

Correct Answer: 4. Use an appropriate date/time column with a controlled default where supported

Explanation:

A dedicated date/time column provides a structured and queryable creation timestamp. A database default such as the current timestamp can populate the field consistently when rows are inserted. The exact data type depends on time-zone and precision requirements. Structured temporal data is preferable to free text because it supports sorting, filtering, validation, and date calculations.

Question 125.

Which database design principle helps ensure each table represents one primary subject or entity?

1. Normalization
2. Replication
3. Encryption
4. Backup rotation

Correct Answer: 1. Normalization

Explanation:

Normalization organizes data into related tables so each table focuses on a coherent subject and redundant dependencies are reduced. This can improve integrity and simplify updates. Excessive normalization may increase join complexity for some workloads, so database design should balance integrity, performance, and application requirements rather than applying normalization mechanically.

Question 126.

A database contains customer address fields repeated in thousands of order rows. What issue is MOST likely?

1. Replication lag
2. Data redundancy and update anomalies
3. Encryption failure
4. Query timeout

Correct Answer: 2. Data redundancy and update anomalies

Explanation:

Repeating customer attributes in every order can create substantial redundancy. If the customer’s address changes, many rows may need updating, and inconsistent updates can leave conflicting values. A normalized design would typically separate customer data from order data and connect the tables through keys, depending on historical address requirements and business rules.

Question 127.

A database administrator wants to ensure that values in an OrderStatus column are limited to Approved, Pending, or Rejected. Which mechanism is MOST appropriate?

1. Primary key
2. Index
3. CHECK constraint
4. Backup job

Correct Answer: 3. CHECK constraint

Explanation:

A CHECK constraint can restrict accepted values to a defined set or logical condition. This prevents invalid statuses from being inserted or updated regardless of which application submits the data. Database-level constraints complement application validation and help preserve data integrity across all clients.

Question 128.

A production table is accidentally dropped. Which capability is MOST important for recovering it?

1. Query caching
2. Data masking
3. Index compression
4. Tested backup and recovery procedures**

Correct Answer: 4. Tested backup and recovery procedures

Explanation:

Recovering a dropped table generally requires a supported recovery method, often involving backups, logs, snapshots, or point-in-time recovery. The exact method depends on the platform. A tested recovery procedure is critical because merely having backup files does not guarantee administrators can restore the missing object within required recovery targets.

Question 129.

Which database feature is MOST useful for reducing storage usage when large amounts of repetitive data are stored?

1. Data compression
2. Role-based access control
3. Auditing
4. Connection pooling

Correct Answer: 1. Data compression

Explanation:

Database compression can reduce the physical storage required for tables, indexes, or backups, depending on the platform. It may also reduce I/O because more logical data can be read per physical operation. Compression consumes CPU resources, so administrators should evaluate workload impact and platform support before enabling it broadly.

Question 130.

A database server has very high disk latency during peak periods. Which action should the administrator take FIRST?

1. Add users to the database
2. Correlate storage metrics with active queries and I/O workload
3. Disable all indexes
4. Delete transaction logs manually

Correct Answer: 2. Correlate storage metrics with active queries and I/O workload

Explanation:

High disk latency may result from storage saturation, inefficient queries, insufficient memory, checkpoints, backups, or other heavy I/O activity. Correlating storage measurements with database workload helps identify the real source before changes are made. Evidence-based troubleshooting reduces the chance of applying an unrelated fix that creates additional problems.

Question 131.

A database query performs a table scan even though an index exists on the filtered column. What is the BEST next step?

1. Assume the database optimizer is defective
2. Drop the table
3. Review the execution plan, selectivity, statistics, and query predicates
4. Disable transaction isolation

Correct Answer: 3. Review the execution plan, selectivity, statistics, and query predicates

Explanation:

An index is not always the most efficient access path. If a predicate returns a large percentage of the table, a scan may be cheaper. Stale statistics, implicit conversions, functions on indexed columns, or mismatched index design may also prevent effective index use. The execution plan and query characteristics should be reviewed before changing indexes.

Question 132.

A database administrator wants to prevent a reporting query from blocking critical transactional workloads. Which approach should be considered based on platform capabilities and consistency requirements?

1. Remove all transaction controls
2. Grant the report administrator access
3. Run reports only from application source code
4. Use an appropriate read replica or row-versioning isolation strategy**

Correct Answer: 4. Use an appropriate read replica or row-versioning isolation strategy

Explanation:

Reporting workloads can sometimes be isolated from transactional activity by using a read replica or database isolation mechanisms based on row versions. These approaches may reduce lock contention, but each has trade-offs involving data freshness, storage, and consistency. The choice should reflect business requirements and the database platform’s supported architecture.

Question 133.

Which practice BEST protects sensitive database audit logs from tampering?

1. Restrict modification access and forward or store logs in a protected location
2. Allow all administrators to delete logs freely
3. Store logs only in temporary memory
4. Disable timestamps

Correct Answer: 1. Restrict modification access and forward or store logs in a protected location

Explanation:

Audit logs are valuable only if their integrity can be trusted. Access should be restricted, and logs may be forwarded to centralized or append-oriented monitoring systems where database users cannot easily modify them. Retention, time synchronization, and secure storage also support investigations and compliance requirements.

Question 134.

A company requires administrators to approve elevated database access before it becomes active. Which security capability BEST supports this process?

1. Table partitioning
2. Privileged access management
3. Query caching
4. Database compression

Correct Answer: 2. Privileged access management

Explanation:

Privileged access management can enforce approval workflows, time-limited elevation, credential vaulting, and session recording for high-impact accounts. This reduces standing administrative privilege and strengthens accountability. It is especially useful when organizations require formal approval before privileged database actions are performed.

Question 135.

Which situation MOST strongly indicates that an application service account has excessive privileges?

1. It can read only the tables required by the application
2. It can execute only approved stored procedures
3. It has database-owner permissions even though it performs only simple reads and inserts
4. Its password is rotated regularly

Correct Answer: 3. It has database-owner permissions even though it performs only simple reads and inserts

Explanation:

Granting database-owner privileges to an account that needs only narrow data access violates least privilege. If the account is compromised, an attacker could perform destructive administrative actions far beyond the application’s intended function. Permissions should be reduced to the minimum operations and objects required for the service.

Question 136.

A database administrator needs to preserve confidentiality when transferring backups to an off-site location. Which control is MOST important?

1. Increase index fill factor
2. Disable audit logging
3. Use unencrypted file transfer for speed
4. Encrypt the backup and protect the transfer channel**

Correct Answer: 4. Encrypt the backup and protect the transfer channel

Explanation:

Backups contain potentially sensitive data and should be protected both while stored and while transmitted. Encryption of the backup plus secure transport reduces the risk of exposure if the file or communication is intercepted. Encryption keys must also be managed securely so authorized recovery remains possible.

Question 137.

Which practice BEST ensures that database backups are usable after a ransomware incident?

1. Maintain protected backup copies that are isolated from ordinary production credentials and periodically test restores
2. Store all backups on the production database server only
3. Give the application account permission to delete backup files
4. Rely only on replication

Correct Answer: 1. Maintain protected backup copies that are isolated from ordinary production credentials and periodically test restores

Explanation:

Ransomware can encrypt or delete online backups if attackers gain access to production credentials or storage. Isolated, immutable, or otherwise protected backup copies reduce this risk. Periodic restore testing confirms that the backups remain readable and that recovery procedures work. Replication alone may propagate malicious or destructive changes.

Question 138.

A database administrator needs to know how much data could be lost if recovery uses the most recent backup. Which concept provides this information?

1. RTO
2. RPO
3. MTBF
4. IOPS

Correct Answer: 2. RPO

Explanation:

Recovery point objective defines the maximum acceptable amount of data loss measured in time. If backups or replicated changes are available every 15 minutes, the organization may target an RPO around that interval, depending on the recovery design. RTO addresses how long service may remain unavailable, while IOPS measures storage performance.

Question 139.

A database administrator needs to measure how long it takes to restore service during a disaster recovery exercise. Which objective is being validated?

1. Index selectivity
2. RPO
3. RTO
4. Query cardinality

Correct Answer: 3. RTO

Explanation:

Recovery time objective defines the target maximum downtime following an incident. Measuring the elapsed time from disruption to restored usable service during a recovery exercise validates whether the organization can meet its RTO. This testing may include infrastructure restoration, database recovery, validation, application connectivity, and operational handoff.

Question 140.

Which statement BEST describes effective database administration documentation?

1. It should contain only installation instructions
2. It is unnecessary for experienced administrators
3. It should be stored only in personal notes
4. It should document architecture, configuration, dependencies, maintenance, backup, recovery, security, and operational procedures**

Correct Answer: 4. It should document architecture, configuration, dependencies, maintenance, backup, recovery, security, and operational procedures

Explanation:

Comprehensive documentation reduces dependency on individual administrators and supports consistent operations. It should cover architecture, configurations, ownership, dependencies, backup and recovery procedures, security controls, monitoring, maintenance, and common troubleshooting steps. Documentation should be updated when the environment changes so it remains useful during normal operations and emergencies.