CompTIA DataSys+ DS0-002: Database Fundamentals

Topic snapshot

Sample questions

These original IT Mastery practice questions are aligned to this topic area. Use them for self-assessment, scope review, and deciding what to drill next.

Question 1

Topic: Database Fundamentals

A DBA is investigating a web application that became slow after a release that changed only the object-relational mapping (ORM) configuration. Individual queries still run quickly when tested manually, and database CPU is not saturated. Which evidence would best support ORM behavior as the root cause of the performance issue?

Options:

• A. A firewall log showing intermittent dropped connections

• B. A storage report showing database files are 85% full

• C. An execution plan showing one query uses a missing index

• D. A trace showing many repeated child-row SELECTs per parent record

Best answer: D

Explanation: ORM-related performance issues are often proven by observing the SQL actually generated by the application, not just by testing isolated statements. A common symptom is an N+1 query pattern, where the ORM retrieves parent rows and then issues one additional query for each related child set. Each statement may be fast, but the total number of round trips and executions creates user-visible latency. In this scenario, the release changed only ORM configuration, individual queries are fast, and CPU is not saturated, so a trace that links repeated generated SQL to application behavior is the strongest evidence. The key takeaway is to validate ORM impact with generated SQL traces, query counts, and execution patterns.

• Storage fullness may require capacity planning, but it does not directly connect the slowdown to ORM-generated SQL.
• Missing index could explain one slow query, but the stem says individual queries run quickly when tested manually.
• Dropped connections would suggest a network or perimeter issue, not an ORM query-generation pattern.

Question 2

Topic: Database Fundamentals

A team is designing a database for a customer profile service. Each profile is read and written as a single record by customer_id, and optional nested attributes vary widely by customer type and change often. The service does not need cross-profile joins, but it must return the full profile with low latency. Which database model best fits this workload?

Options:

• A. Document database

• B. Graph database

• C. Column-oriented analytical database

• D. Normalized relational database

Best answer: A

Explanation: A document database is a good fit when each business entity can be stored as a self-contained, semistructured document with variable attributes. In this scenario, the profile is accessed by customer_id, changes shape frequently, and is returned as a whole record. That favors flexible schema and document-level reads over normalized tables and joins. A relational model would be stronger if the workload required strict relationships, referential integrity across many tables, or frequent SQL joins. The key takeaway is to match the model to the access pattern and schema stability, not just to the data being customer-related.

• Relational normalization adds value for stable schemas and join-heavy workloads, which the profile service explicitly does not require.
• Graph modeling is useful for traversing relationships, but the workload is key-based profile retrieval rather than relationship exploration.
• Column-oriented analytics supports large analytical scans and aggregations, not low-latency retrieval of a single nested customer profile.

Question 3

Topic: Database Fundamentals

A DBA is reviewing SQL captured from an ORM after a product list page began generating high database activity. The page shows 50 products and each product’s category name. Which interpretation is best supported by the exhibit?

Exhibit: ORM-generated SQL trace

SELECT product_id, product_name, category_id
FROM products
WHERE active = 1
ORDER BY product_name;

-- Repeated once for each returned product row
SELECT category_id, category_name
FROM categories
WHERE category_id = ?;

Options:

• A. The ORM is failing to commit a transaction.

• B. The ORM is creating a Cartesian product.

• C. The ORM is causing an N+1 query pattern.

• D. The ORM is using an unnecessary aggregate function.

Best answer: C

Explanation: The trace indicates an ORM N+1 query pattern. The application first retrieves the 50 product rows, then issues one additional query per product to fetch the related category. That means the page can generate 51 queries instead of a smaller set-based query, such as a join or an eager-loaded relationship. This creates unnecessary database load through repeated parsing, execution, network round trips, and connection usage. The issue is not that the SQL is invalid; it is that the access pattern is inefficient for related data that can be retrieved in bulk.

• Cartesian product would involve an unconstrained join multiplying rows, but the exhibit shows repeated single-row lookups.
• Aggregate function is not present in the SQL, so aggregation is not the source of load.
• Transaction commit is unrelated because the trace shows read-only SELECT statements, not uncommitted writes.

Question 4

Topic: Database Fundamentals

A DBA tests a Python import script from a workstation and it succeeds. The same script fails after being scheduled to run next to the database engine on the database server. What is the most likely issue?

Exhibit: Run comparison

Workstation run
User: DOMAIN\dba1
Runtime host: DBA-LAPTOP
Input path: \\files01\imports\customers.csv
Result: 12,000 rows loaded

Scheduled run
User: DOMAIN\svc-dbjob
Runtime host: DB-SERVER-01
Input path: \\files01\imports\customers.csv
Error: Access is denied
Rows loaded: 0

Options:

• A. The Python script requires a different SQL join type.

• B. The CSV file contains invalid customer rows.

• C. The scheduled runtime identity lacks file share permission.

• D. The database server is missing a primary key index.

Best answer: C

Explanation: Script runtime location matters because the script inherits the permissions, network reachability, drivers, and resource limits of the environment where it executes. In the exhibit, the same input path works from the DBA workstation as DOMAIN\dba1, but fails on DB-SERVER-01 as DOMAIN\svc-dbjob with Access is denied. That points to an execution-context problem, not a data-quality or SQL logic problem. The best next check is whether the scheduled job’s service account and server host have permission to access \\files01\imports\customers.csv. A script that runs successfully interactively can fail when automated if the runtime identity changes.

• Bad data is unlikely because the successful workstation run loaded the same file.
• Missing index would affect performance or constraints, not normally cause a network file access denial.
• Join logic is not supported by the exhibit because the failure occurs before any rows load from the file.

Question 5

Topic: Database Fundamentals

A facilities team is choosing a database model for building sensor data. Each record has a device ID, timestamp, metric name, numeric value, and location tag. The main queries retrieve recent time ranges, calculate hourly rollups, and expire older raw readings after retention limits. Which database model best fits this workload?

Options:

• A. Object-oriented database

• B. Time series database

• C. Document database

• D. Graph database

Best answer: B

Explanation: A time series database is designed for data organized around timestamps, frequent inserts, and queries over time windows. The sensor workload is append-heavy and uses access patterns such as recent ranges, hourly aggregation, and retention-based expiration. Those requirements align with time series features such as efficient timestamp indexing, downsampling, and time-based partitioning or retention. An object-oriented database is more appropriate when the data is naturally persisted as complex application objects with identity, inheritance, and object navigation. Here, the deciding factor is not object behavior or class structure; it is timestamped measurement data queried by time.

• Object persistence fails because the records are simple measurements, not complex objects with inheritance and object identity.
• Relationship traversal fails because the main access pattern is time-window analysis, not exploring connected entities.
• Flexible documents may store semistructured records, but it is not the best match for time-based rollups and retention-heavy sensor data.

Question 6

Topic: Database Fundamentals

A DBA is configuring a new ingestion job for a supplier feed. The source file contains part_code values such as 00127, A1045, and 09-BETA. The target relational table currently defines part_code as an integer. Test loads either fail validation or remove leading zeros. Which implementation choice best prevents the ingestion and query problems?

Options:

• A. Increase part_code to a larger integer type

• B. Store each row as an unparsed JSON document

• C. Change part_code to a text-based column

• D. Trim nonnumeric characters before loading

Best answer: C

Explanation: A data type mismatch occurs when the defined storage type does not match the real meaning and format of the incoming data. Here, part_code is an identifier that can contain leading zeros, letters, and hyphens. An integer column is appropriate for numeric values used in arithmetic, but it cannot represent A1045 or 09-BETA, and it will not preserve 00127 as entered. Defining the column as text allows validation, joins, lookups, and filters to use the supplier’s actual identifier values without losing information.

The key takeaway is to model identifiers by their required format and use, not by whether some examples happen to contain digits.

• Larger integer still rejects letters and hyphens and still cannot preserve leading zeros.
• Trimming characters changes the business identifier and can create incorrect matches or duplicates.
• Unparsed JSON avoids the immediate type error but gives up structured validation and efficient relational querying for this field.

Question 7

Topic: Database Fundamentals

A DBA must automate a nightly purge of expired session records after business hours. The task must run even when administrator workstations are offline, read database server log files, and write an audit row in the same database before deleting rows. Which scripting approach is the BEST professional decision?

Options:

• A. Run a client-side Python script from the DBA workstation

• B. Schedule a server-side script or stored procedure on the database host

• C. Add the purge logic to the reporting application’s ORM layer

• D. Use browser-based JavaScript in the administration portal

Best answer: B

Explanation: Server-side scripting runs on or near the database server and can access server-side resources such as database files, local logs, scheduler services, and privileged database operations, depending on permissions. In this scenario, the job must run after hours without relying on a user’s workstation, read server log files, and make database changes as part of the same operational task. A scheduled server-side script, database job, or stored procedure is the best fit because execution is tied to the server environment rather than a client session. Client-side scripts are useful for user-driven tasks or external automation, but they depend on the client runtime and normally cannot directly access database server files.

• Workstation dependency fails because the task must run when administrator workstations are offline.
• Browser execution fails because browser scripts run in the client context and are restricted from accessing server log files directly.
• Application ORM logic fails because purge scheduling and server log access are operational database tasks, not reporting application behavior.

Question 8

Topic: Database Fundamentals

A sales analyst needs a report that lists every order row and, on the same row, shows the customer’s running order total and rank within each sales region. The report must not update tables or store new summary values. Which implementation concept best fits this requirement?

Options:

• A. Use a grouped aggregate query by customer

• B. Create a trigger to maintain summary columns

• C. Normalize the order table into additional tables

• D. Use windowing functions with partitions and ordering

Best answer: D

Explanation: Windowing functions are designed for analytic queries that need calculations across a set of related rows without collapsing the result or changing stored data. A function can operate over a partition, such as sales region or customer, and use an order to calculate running totals, ranks, or moving averages. The query still returns the row-level order details, with the analytic value added to the result set at read time.

A grouped aggregate is useful for summaries, but it would typically reduce many order rows into fewer grouped rows. Triggers or schema changes would persist data or alter design, which is not needed for a read-only analytic report.

• Grouped aggregate fails because grouping would summarize rows instead of keeping every order row visible.
• Trigger-based summary fails because it changes stored data to solve a reporting-only need.
• Normalization change fails because the requirement is an analytic query result, not a schema design problem.

Question 9

Topic: Database Fundamentals

A DBA is reviewing a reporting request. The analyst needs the query to return every order line while adding per-customer analytics. The source table must not be altered.

Exhibit:

Which SQL approach best fits the request?

Options:

• A. Use an INNER JOIN between order-line copies

• B. Aggregate with GROUP BY customer_id

• C. Use SUM() and ROW_NUMBER() with OVER partitions

• D. Store derived columns with an UPDATE

Best answer: C

Explanation: Windowing functions are designed for analytic calculations that need context from other rows without collapsing the result set. In this request, SUM(line_amount) OVER (PARTITION BY customer_id ORDER BY order_date, line_id) can produce a running total, and ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date, line_id) can produce a sequence number. Each original line_id can still appear as its own output row because the functions operate over a window of rows in the query result. No UPDATE, trigger, or new stored column is needed. The key distinction is that window functions add calculated query output, not persistent stored values.

• Grouping rows fails because GROUP BY customer_id summarizes rows and would not preserve one output row per line_id.
• Persisting calculations fails because the requirement says not to alter the source table or write derived values back.
• Self-joining rows may compare rows but is less direct and does not inherently provide running totals or row sequence calculations.

Question 10

Topic: Database Fundamentals

A DBA needs to automate a daily report that reads row counts and index usage from several production databases. The script must not change data, must run without an administrator being signed in, and must avoid exposing database ports or reusable passwords to user workstations. Which scripting approach is the BEST professional decision?

Options:

• A. Install the reporting script directly on each database server and run it with the database owner account

• B. Open the database ports to the DBA subnet and let each administrator run the report from an IDE

• C. Run a Python script from the DBA laptop with the production admin password saved in a local config file

• D. Run a scheduled script on a secured management host using a least-privileged service account and a credential vault

Best answer: D

Explanation: Administrative scripts should run from a controlled location with only the access they need. For this read-only reporting task, a scheduled script on a secured management host reduces workstation exposure, avoids requiring an interactive administrator session, and keeps database access inside a controlled network path. A least-privileged service account limits impact if the script or account is misused, and a credential vault avoids hard-coded or locally stored reusable passwords. The key is matching the runtime location and credential handling to the operational risk, not just choosing a language.

• Local saved password fails because it places a reusable production credential on a user workstation.
• Open database ports fails because it broadens network exposure and makes execution dependent on individual admin workstations.
• Database owner account fails because the task is read-only and does not need owner-level privileges on each server.

Continue with full practice

Use the CompTIA DataSys+ DS0-002 Practice Test page for the full IT Mastery practice bank, mixed-topic practice, timed mock exams, explanations, and web/mobile app access.

Try CompTIA DataSys+ DS0-002 on Web View CompTIA DataSys+ DS0-002 Practice Test

Related focused pages

• Free CompTIA DataSys+ DS0-002 Full-Length Practice Exam
• Database Deployment
• Database Management and Maintenance
• Data and Database Security
• Business Continuity
• Data Integration

Free review resource

Use the full IT Mastery practice page above for the latest review links and practice page.