Overview
Database Fundamentals covers what a database administrator must read and write without hesitation. It starts with structure types: relational versus non-relational, linear versus non-linear data, and the four NoSQL families (document, key-value, column-oriented and graph) with tools such as MongoDB, DynamoDB, Cassandra, Neo4j and Cosmos DB. It then moves to SQL itself: Data Definition Language (DDL), Data Manipulation Language (DML), Transaction Control Language (TCL), set-based logic, ACID properties, ANSI SQL, and programmable objects such as triggers, stored procedures, functions and views.
The last two objectives look outward from the database. Scripting covers where code runs (server-side or client-side) and how PowerShell, Python and Linux or Windows command lines automate work. The programming objective asks you to judge how object-relational mapping (ORM) frameworks such as Hibernate, Entity Framework and Ebean affect the server. Domain 3 relies on all of this when you tune queries and automate maintenance.
How it's tested
Domain 1 mixes short compare-and-contrast items with scenario questions that show a requirement or a piece of SQL. You may be asked which database model fits a data shape, which statement category a command belongs to, what a transaction does when it fails, or why ORM-generated queries slow the server. Read the requirement first, then the code.
-
Match the model to the data
A workload is described, such as relationship-heavy fraud links, session tokens looked up by key, or high-volume time-series writes, and you pick the database family or product that fits.
-
Read the SQL
A statement or short script is shown and you identify its category (DDL, DML, TCL), predict its effect, or choose the fix for a missing clause, wrong join or unhandled transaction.
-
Find the code-side cause
Server load or slow pages are traced to application code, a client-side script or ORM behavior, and you choose the change that reduces round trips or load on the database.
A payments service debits one account and credits another; a crash between the two statements leaves money missing. Options offer a trigger, a view, an index and an explicit transaction. The clue is that both changes must succeed or fail together, which is atomicity. Wrapping both updates in a transaction that commits only after the second succeeds, and rolls back on error, is the only option that fixes it.
Objectives 4
Official objectives from the CompTIA outline, in outline order.
1.1 Compare and contrast database structure types.Relational vs. non-relational, linear vs. non-linear, and the four NoSQL families with example tools.
- What it means
- Relational databases store rows in tables with a fixed schema and enforce relationships with keys; non-relational (NoSQL) databases trade some of that structure for flexible schemas and horizontal scale. NoSQL splits into document (MongoDB), key-value (DynamoDB), column-oriented or wide-column (Cassandra) and graph (Neo4j) stores, with Azure Cosmos DB offering several models through different APIs.
- What to understand
- Choose by access pattern: document stores fit nested, variable records read as a whole; key-value stores fit very fast lookups by a single key; wide-column stores fit heavy write volumes across many nodes; graph stores fit queries that traverse relationships. Linear data structures (arrays, lists, queues) are sequential, while non-linear ones (trees, graphs) branch, which is why indexes use trees and social networks use graphs.
- How it shows up
- A stem describes data shape and query pattern, for example friend-of-a-friend recommendations or product catalogs with different attributes per item, and asks for the best structure type or product.
- Common confusion
- Candidates pick a relational database for every scenario because it is familiar, or call Cassandra a document store; it is a wide-column store.
1.2 Given a scenario, develop, modify, and run SQL code.DDL, DML, TCL, set-based logic, ACID, ANSI SQL, and triggers, stored procedures, functions and views.
- What it means
- DDL defines structures (CREATE, ALTER, DROP, TRUNCATE), DML reads and changes data (SELECT, INSERT, UPDATE, DELETE), and TCL controls transactions (BEGIN or START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT). Programmable objects package logic inside the database: a trigger fires automatically on an event, a stored procedure runs on call, a function returns a value usable in a query, and a view is a saved query.
- What to understand
- ACID means atomicity (all or nothing), consistency (constraints hold), isolation (concurrent transactions do not see each other's partial work) and durability (committed data survives a crash). Set-based logic means one statement acts on a whole set of rows, which almost always beats looping row by row with a cursor. ANSI SQL is the portable standard; vendor dialects add extensions such as T-SQL or PL/SQL.
- How it shows up
- Performance-based items may show a script and ask what it does, what is missing, or which statement completes a task such as adding a column, undoing a partial change or reusing a query safely.
- Common confusion
- UPDATE or DELETE without a WHERE clause changes every row, and TRUNCATE is usually treated as DDL that removes all rows without per-row logging, unlike DELETE.
1.3 Compare and contrast scripting methods and scripting environments.Server-side vs. client-side scripts, PowerShell and Python, and command-line scripting on Linux and Windows.
- What it means
- A server-side script runs on or next to the database server, such as a scheduled job, a stored procedure or a cron task on the host; a client-side script runs on a user's machine or in the application tier and sends requests over the network. PowerShell and Python are the named languages, alongside shell scripting on Linux and command-line tools on Windows.
- What to understand
- Server-side scripts keep processing close to the data, cut network round trips and centralize control, but they consume server resources. Client-side scripts spread load and suit user-specific tasks, but every call crosses the network and the client must hold credentials. Python reaches most engines through drivers such as pyodbc or psycopg2, and PowerShell has modules for SQL Server administration.
- How it shows up
- A scenario describes a nightly bulk job, a cleanup task or a user-facing validation and asks where the script should run or which environment fits the operating system.
- Common confusion
- Assuming client-side means a web browser only; any code running outside the database server, including an admin's laptop script, is client-side from the server's view.
1.4 Explain the impact of programming on database operations.ORM frameworks such as Hibernate, Entity Framework and Ebean, and how to assess the SQL they generate.
- What it means
- An object-relational mapping (ORM) framework lets developers work with objects while it writes the SQL; Hibernate and Ebean serve Java, and Entity Framework serves .NET. The objective expects the DBA to review the SQL an ORM produces, validate it, judge its effect on the server, and propose alternatives.
- What to understand
- ORMs can generate inefficient patterns: the N+1 problem (one query for a list, then one per row for related data), SELECT of every column, missing parameterization or unindexed filters. Fixes include eager loading or explicit joins, projections that fetch only needed columns, indexes that match the generated predicates, or a stored procedure or hand-written query for hot paths.
- How it shows up
- A stem reports a page that issues hundreds of near-identical queries or a sudden CPU spike after a release, and you pick the code-side cause or the most effective change.
- Common confusion
- Blaming hardware and adding CPU or memory when the root cause is the volume or shape of ORM-generated queries.
High-yield concepts
The terms, rules and formulas this domain turns into questions most often.
-
ACID
Atomicity, consistency, isolation and durability are the four guarantees a transactional database gives: changes commit as a unit, constraints hold, concurrent work stays separated, and committed data survives failure.
- Remember
- A partial update after a crash is an atomicity failure; lost committed data after a restart is a durability failure.
- Tested as
- Transaction scenarios ask which property was violated or which control restores it.
- Trap
- Confusing consistency (rules and constraints remain valid) with isolation (concurrent transactions do not interfere).
-
DDL vs. DML vs. TCL
DDL statements (CREATE, ALTER, DROP, TRUNCATE) change structure, DML statements (SELECT, INSERT, UPDATE, DELETE) work with data, and TCL statements (COMMIT, ROLLBACK, SAVEPOINT) control transactions.
- Remember
- Ask whether the command changes the table's definition, its rows, or the transaction state.
- Tested as
- Classification items list a command and ask its category; performance-based items ask which statement completes a task.
- Trap
- Classing TRUNCATE as DML because it removes rows; most engines treat it as DDL.
-
Set-based logic
Set-based SQL describes the result for a whole set of rows in one statement and lets the optimizer choose how, instead of looping through rows one at a time with a cursor.
- Remember
- One UPDATE with a join usually outperforms a cursor that updates rows individually.
- Tested as
- Shows up when a slow procedure loops over rows and you choose the rewrite.
- Trap
- Keeping a cursor because it mirrors how application code iterates.
-
Trigger
A trigger is code the database runs automatically before or after an INSERT, UPDATE or DELETE on a table, often used for auditing or enforcing a rule.
- Remember
- Triggers fire on events without being called, and they run inside the triggering transaction, so a slow trigger slows every write.
- Tested as
- Scenarios ask how to record every change to a salary column automatically.
- Trap
- Picking a stored procedure for automatic auditing; a procedure only runs when something calls it.
-
Stored procedure vs. function
A stored procedure is a named routine stored in the database and called explicitly that can modify data and return result sets; a function returns a single value or table and can be used inside a SELECT.
- Remember
- If it must appear inside a query expression, it is a function.
- Tested as
- Tested when a requirement says reusable logic must be called from within a query or run as a scheduled task.
- Trap
- Assuming functions are the right place for data modification; most engines restrict side effects in functions.
-
View
A view is a stored SELECT that presents data as a virtual table, used to simplify complex joins and to expose only permitted columns.
- Remember
- A standard view stores the query, not the data, so it reflects current table contents each time it is read.
- Tested as
- Appears as a way to hide sensitive columns or give analysts a simpler object.
- Trap
- Expecting a standard view to speed up a slow query by itself; it runs the underlying query each time.
-
Document vs. key-value vs. wide-column vs. graph
Document stores keep self-describing JSON-like records, key-value stores map a unique key to an opaque value, wide-column stores group columns in families across distributed nodes, and graph stores model nodes and edges.
- Remember
- MongoDB is document, DynamoDB is key-value (with document support), Cassandra is wide-column, and Neo4j is graph.
- Tested as
- Product-to-model matching and data-shape scenarios.
- Trap
- Choosing a document store for deep relationship traversal, which a graph database handles far more efficiently.
-
Linear vs. non-linear data structures
Linear structures such as arrays, linked lists, stacks and queues hold elements in sequence; non-linear structures such as trees and graphs connect each element to several others.
- Remember
- B-tree indexes and graph databases are non-linear; a log or queue is linear.
- Tested as
- Compare-and-contrast items ask which structure suits hierarchical or networked data.
- Trap
- Equating non-linear with unstructured; trees and graphs are highly structured.
-
ANSI SQL
ANSI SQL is the standardized core of the language that every major relational engine implements, extended by dialects such as T-SQL (SQL Server) and PL/SQL (Oracle).
- Remember
- Standard syntax ports across engines; dialect features such as TOP or proprietary functions may not.
- Tested as
- Scenarios ask why a script that ran on one engine fails on another.
- Trap
- Assuming any SQL that runs on one platform is ANSI-compliant.
-
Server-side vs. client-side scripting
Server-side scripts execute on the database host or within the engine; client-side scripts execute on a workstation or application server and connect over the network.
- Remember
- Bulk, data-heavy work belongs close to the data; per-user tasks and validation can run client-side.
- Tested as
- A scenario describes a job and asks where it should run to cut network traffic or protect credentials.
- Trap
- Moving millions of rows to a client to process them, then writing them back.
-
ORM N+1 problem
The N+1 pattern happens when an ORM runs one query to load a list and then one extra query per item to load related data, multiplying round trips.
- Remember
- Hundreds of near-identical small queries per page is the signature; eager loading or a join fixes it.
- Tested as
- The usual scenario for objective 1.4 is a slow page after a release.
- Trap
- Adding an index or more hardware, which shortens each query but leaves the round-trip count unchanged.
-
Parameterized queries
A parameterized query sends SQL text and values separately, so the engine treats input strictly as data and can reuse the cached execution plan.
- Remember
- Parameters help both security and performance through plan reuse.
- Tested as
- Links code-review items here with SQL injection items in Domain 4.
- Trap
- Thinking string concatenation with escaping is equivalent; it is fragile and defeats plan reuse.
Side by side
Concepts candidates mix up, compared on the point that decides the answer.
DDL vs. DML vs. TCL
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| DDL | Defines and changes structures: CREATE, ALTER, DROP, TRUNCATE | Acts on schema objects, not on chosen rows | Classing TRUNCATE as DML because it empties a table |
| DML | Reads and changes data: SELECT, INSERT, UPDATE, DELETE | Acts on rows; can be rolled back inside a transaction | Running UPDATE or DELETE without a WHERE clause |
| TCL | Controls transactions: COMMIT, ROLLBACK, SAVEPOINT | Decides whether DML changes become permanent | Forgetting that uncommitted changes vanish on rollback or disconnect |
NoSQL families
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| Document (MongoDB) | JSON-like documents with flexible fields | Whole record read and written as one unit | Using it for many-hop relationship queries |
| Key-value (DynamoDB) | Unique key mapped to a value | Fastest lookups by key; limited querying by value | Expecting rich ad hoc queries on value contents |
| Wide-column (Cassandra) | Rows with column families spread across nodes | Built for high write volume and horizontal scale | Calling it a document or relational store |
| Graph (Neo4j) | Nodes and edges with properties | Relationship traversal is the primary operation | Choosing it for simple, high-volume key lookups |
Trigger vs. stored procedure vs. function vs. view
| Concept | Meaning | Key difference | Common trap |
|---|---|---|---|
| Trigger | Code fired automatically by a data event | No caller; runs inside the triggering transaction | Hidden cost on every write |
| Stored procedure | Named routine run on demand | Can change data and return result sets | Expecting it to run automatically on an insert |
| Function | Routine that returns a value or table | Usable inside SELECT and WHERE clauses | Using it for data modification |
| View | Saved SELECT exposed as a virtual table | Stores the query, not the data | Assuming it speeds up the query it wraps |
Exam traps
Each trap is an answer that looks right. Learn the clue that should change your mind.
-
Relational by default
- What candidates pick
- A relational database for a fraud-ring analysis that follows links several hops deep.
- Why it looks right
- Relational engines can join anything and are the familiar choice.
- Why it's wrong
- Each extra hop adds a self-join, and performance degrades quickly; a graph database stores relationships directly and traverses them efficiently.
Clue to watch for Words like connections, hops, friends of friends, or paths between entities.
-
Index to fix N+1
- What candidates pick
- Add an index on the foreign key the ORM filters on.
- Why it looks right
- Indexes are the standard response to slow queries.
- Why it's wrong
- Each of the hundreds of queries gets slightly faster, but the round-trip count stays the same; eager loading or a join removes the extra queries.
Clue to watch for The stem reports many small, nearly identical queries per request rather than one slow query.
-
Procedure for automatic auditing
- What candidates pick
- A stored procedure that logs changes to the payroll table.
- Why it looks right
- Procedures are the usual home for reusable database logic.
- Why it's wrong
- Nothing calls a procedure automatically, so direct updates bypass it; a trigger fires on every insert, update or delete.
Clue to watch for Requirements such as every change, automatically, or regardless of which application writes.
-
Commit too early
- What candidates pick
- Commit after each of two related updates so progress is saved.
- Why it looks right
- Frequent commits look safer after a crash.
- Why it's wrong
- If the second update fails, the first is already permanent and data is inconsistent; both belong in one transaction with a rollback path.
Clue to watch for Two changes that must succeed or fail together, such as a debit and a credit.
-
Client-side bulk processing
- What candidates pick
- Pull the full table into a Python script on an admin workstation, transform it, and write it back.
- Why it looks right
- Python is flexible and the admin is comfortable with it.
- Why it's wrong
- Moving millions of rows across the network twice is slow and exposes data; a set-based statement or server-side job processes it in place.
Clue to watch for Large row counts, nightly windows, or network bandwidth concerns.
-
View for speed
- What candidates pick
- Create a view to make a slow multi-table report faster.
- Why it looks right
- Views simplify the query the report runs.
- Why it's wrong
- A standard view only stores the SELECT text; the joins still execute each time. Speed comes from indexes, query rewrites or a materialized view.
Clue to watch for The requirement is performance, not simplicity or column hiding.
Answer framework
Use this sequence when a Domain 1 question asks you to choose a database model, a SQL construct or a code-side fix.
- Restate the requirement in one line: data shape, access pattern, automatic versus on-demand behavior, or a failure to prevent.
- Classify the option types offered, such as DDL versus DML versus TCL, or document versus key-value versus wide-column versus graph, and drop any from the wrong category.
- Check transaction safety: if several changes must stand or fall together, the answer needs explicit transaction control and a rollback path.
- Prefer set-based, server-side processing over row-by-row loops or bulk transfers to client machines when volume is high.
- For performance stems, count queries before timing them; many small repeated queries point to ORM or application code, while one slow query points to indexing or rewriting.
Practice question
Want more like this? The CompTIA DataSys+ practice test has questions for every domain.
Checklist
0 of 14
Tick each one you can do without notes. Your ticks stay in this browser.
Study strategy
Begin with objective 1.2, because SQL fluency carries the most weight in practice and feeds Domains 3 and 4. Install a free engine such as PostgreSQL, MySQL or SQL Server Developer Edition and write every statement type by hand: create tables, alter them, load rows, update with joins, and wrap changes in transactions you deliberately roll back. Then build one trigger, one stored procedure, one function and one view so their differences are concrete.
Next, study 1.1 with a one-page matrix of the four NoSQL families: data model, typical query, scaling approach, and the named product. Add a column for when a relational design is still the better choice.
Candidates tend to underestimate 1.3 and 1.4 because they read like developer topics. Write a short PowerShell or Python script that connects to your database, runs a parameterized query and logs the result, then schedule it. For ORM, read about the N+1 problem and look at the SQL an ORM generates using its logging option or a database trace.
Afterward, practice reading unfamiliar SQL quickly and predicting its effect before running it, then move to Domain 3, where these objects become maintenance and tuning work.
FAQ
How much of DataSys+ DS0-001 is Database Fundamentals?
CompTIA weights it at 24% of the exam, second only to Database Management and Maintenance at 25%. CompTIA does not publish per-domain question counts, so any number per domain is an estimate.
Do I need to write SQL on the exam?
The objective is phrased as develop, modify and run SQL code in a scenario, and the exam includes performance-based items, so expect to read SQL closely and choose or complete statements. Hands-on practice is the most reliable preparation.
Which SQL dialect does the exam use?
The objectives name ANSI SQL rather than a vendor dialect. Focus on standard syntax and know that products add extensions such as T-SQL or PL/SQL.
How deep is the NoSQL content?
You need to compare the document, key-value, column-oriented and graph models and match named tools such as MongoDB, DynamoDB, Cassandra, Neo4j and Cosmos DB to them. Configuration of those products is not listed in the objectives.
Do I need to be a programmer for objective 1.4?
No. The objective asks you to review ORM-generated SQL, judge its impact on the server and propose alternatives, which is a DBA's view of application code rather than application development.
What is the difference between a trigger and a stored procedure?
A trigger runs automatically when a specified data event occurs on a table, while a stored procedure runs only when called. Requirements that say every change must be captured automatically point to a trigger.
How does Domain 1 connect to the other domains?
Indexes, views and procedures from this domain become tuning and maintenance work in Domain 3, and dynamic SQL and scripting choices become security concerns such as SQL injection and credential storage in Domain 4.
Other Data & Analytics certifications
Where the same subject shows up in other exams. Cards marked "Same subject" open the matching domain.
Test your Domain 1 knowledge
You have just compared database models, SQL statement types, transactions and the load that scripts and ORM code create. See whether you can spot the right construct when a scenario hides it in a few lines of code.
Keep going
- Training Training Built on your Weak areas
- Practice test Questions by domain, with explanations
- Mock exam Full length, timed like the real exam
- Readiness test Find your weakest domain in minutes
- Cheat sheet Key terms and formulas on one page
- Ask the CompTIA DataSys+ AI tutor Get any concept explained your way