CCNA Data Concepts and Environments Questions — Page 3 of 3

151

MCQhard

A database table has columns: OrderID (primary key), ProductID, CustomerID, CustomerName, OrderDate, ProductName. All products are purchased only by the customer who placed the order. Which normal form violation exists if CustomerName depends on CustomerID?

A.Boyce-Codd normal form (BCNF)

B.Third normal form (3NF)

C.Second normal form (2NF)

D.First normal form (1NF)

AnswerB

CustomerName depends on CustomerID, which is not a candidate key, creating a transitive dependency and violating 3NF.

Why this answer

The table violates Third Normal Form (3NF) because CustomerName depends on CustomerID, which is not a candidate key (the primary key is OrderID). 3NF requires that every non-key attribute be non-transitively dependent on the primary key; here, CustomerName is transitively dependent on OrderID via CustomerID. Since CustomerID is a non-key attribute (it is not part of the primary key), this transitive dependency breaks 3NF.

Exam trap

The trap here is that candidates often confuse transitive dependencies (3NF violation) with partial dependencies (2NF violation) or think that any dependency on a non-key attribute automatically violates BCNF, but the specific scenario of CustomerName depending on CustomerID is a textbook transitive dependency that breaks 3NF first.

How to eliminate wrong answers

Option A is wrong because Boyce-Codd Normal Form (BCNF) is a stricter version of 3NF that requires every determinant to be a candidate key; while this table also violates BCNF, the question asks which normal form violation exists, and the dependency described is a classic 3NF violation (transitive dependency), not a BCNF-specific one. Option C is wrong because Second Normal Form (2NF) is violated only when a non-key attribute depends on a proper subset of a composite primary key; here the primary key is a single column (OrderID), so no partial dependency exists, and 2NF is satisfied. Option D is wrong because First Normal Form (1NF) is violated only if there are repeating groups or non-atomic values; the table as described has atomic columns and no repeating groups, so 1NF is satisfied.

Practice this question →

152

MCQmedium

A data team is building a predictive model. They have data on 'Number of employees' (whole numbers) and 'Revenue' (currency). Which statement correctly compares these data types?

A.Number of employees is discrete; revenue is continuous

B.Both are continuous data

C.Both are ratio data

D.Number of employees is qualitative; revenue is quantitative

AnswerA

Employees are counted in whole units (discrete), while revenue can have fractional values (continuous).

Why this answer

'Number of employees' is a count of distinct entities, making it discrete data (only whole numbers), while 'Revenue' can take any value within a range (including decimals), making it continuous data. Discrete data arises from counting, whereas continuous data arises from measurement.

Exam trap

The trap here is that candidates confuse the measurement scale (ratio) with the data type (discrete vs. continuous), leading them to pick option C even though the question specifically asks about data type classification.

How to eliminate wrong answers

Option B is wrong because 'Number of employees' is not continuous; it is discrete as it can only take integer values (e.g., 10, 11, not 10.5). Option C is wrong because while both are ratio data (they have a true zero point), the question asks about data types (discrete vs. continuous), not measurement scales. Option D is wrong because both 'Number of employees' and 'Revenue' are quantitative (numerical) data, not qualitative (categorical).

Practice this question →

153

MCQhard

A data modeler is designing a dimensional model for a sales analytics system. The fact table contains sales transactions, and the dimension tables include product, customer, and time. To reduce data redundancy, the modeler normalizes the dimension tables into multiple related tables. Which schema is being implemented?

A.Vault schema

B.Star schema

C.Galaxy schema

D.Snowflake schema

AnswerD

Snowflake schema normalizes dimension tables to reduce redundancy.

Why this answer

The snowflake schema is a dimensional model where dimension tables are normalized into multiple related tables to reduce data redundancy. In this scenario, the product, customer, and time dimensions are split into sub-dimensions (e.g., product category, customer geography, time hierarchy), which is the defining characteristic of a snowflake schema. This contrasts with a star schema where dimensions remain denormalized.

Exam trap

CompTIA often tests the distinction between star and snowflake schemas by emphasizing normalization of dimensions; the trap here is that candidates may confuse 'normalized dimensions' with a star schema, which actually uses denormalized dimensions for simplicity and performance.

How to eliminate wrong answers

Option A is wrong because a vault schema (Data Vault) is a hybrid modeling approach focused on auditability and flexibility using hubs, links, and satellites, not on normalizing dimension tables for a sales analytics fact table. Option B is wrong because a star schema keeps dimension tables denormalized (single table per dimension) to optimize query performance, which directly contradicts the normalization described in the question. Option C is wrong because a galaxy schema (also called a fact constellation) contains multiple fact tables sharing dimension tables, not the normalization of a single fact table’s dimensions.

Practice this question →

154

Multi-Selecteasy

Which TWO of the following are characteristics of OLTP systems? (Select 2)

Select 2 answers

A.Typically uses a denormalized schema

B.Optimized for complex analytical queries

C.Stores historical data for trend analysis

D.Designed for high transaction throughput

E.Supports ACID transactions

AnswersD, E

OLTP handles many concurrent transactions.

Why this answer

OLTP systems are designed for high transaction throughput, handling large volumes of short, atomic transactions efficiently. They prioritize fast data processing and immediate consistency, making option D correct.

Exam trap

The trap here is that candidates often confuse OLTP with OLAP, mistakenly selecting denormalized schemas or analytical optimization as OLTP characteristics, when in fact OLTP emphasizes normalized schemas and high transaction throughput with ACID compliance.

Practice this question →

155

MCQeasy

A data engineer needs to extract data from a REST API and load it into a data warehouse. The data is received in JSON format. Which data type best describes JSON?

A.Transactional

B.Semi-structured

C.Unstructured

D.Structured

AnswerB

JSON is semi-structured as it has organizational properties (key-value pairs) but no rigid schema.

Why this answer

JSON (JavaScript Object Notation) is classified as a semi-structured data type because it uses a flexible, self-describing schema with key-value pairs and nested structures, but does not enforce a rigid tabular schema like relational databases. In the context of extracting data from a REST API, JSON allows for varying fields and hierarchical data, which aligns with the semi-structured category.

Exam trap

The trap here is that candidates confuse the presence of structure (keys and values) with being fully structured, overlooking that JSON lacks a fixed schema and allows variability, which places it in the semi-structured category.

How to eliminate wrong answers

Option A is wrong because transactional data refers to records of business transactions (e.g., sales, orders) typically stored in structured formats with ACID properties, not to the format of the data itself. Option C is wrong because unstructured data lacks any predefined structure or schema (e.g., raw text, images, video), whereas JSON has a defined syntax with keys, values, and nesting. Option D is wrong because structured data requires a fixed schema (e.g., rows and columns in a relational table), while JSON allows optional fields and varying data types, making it semi-structured.

Practice this question →

156

MCQhard

In the data lifecycle, which phase involves converting raw data into a usable format for analysis?

A.Ingestion

B.Analysis

C.Archival

D.Processing

AnswerD

Processing transforms raw data into a usable format.

Why this answer

The processing phase in the data lifecycle is specifically where raw data is cleaned, transformed, and structured into a usable format for analysis. This includes operations such as parsing, normalization, deduplication, and conversion into formats like Parquet or Avro, which are optimized for query engines like Apache Spark or Presto.

Exam trap

The trap here is that candidates often confuse 'ingestion' with 'processing' because both involve moving data, but ingestion is about raw data capture, while processing is about transformation and cleaning before analysis.

How to eliminate wrong answers

Option A is wrong because ingestion refers to the initial collection and import of raw data from sources (e.g., via Apache Kafka or Flume) into a storage system, not its transformation into a usable format. Option B is wrong because analysis is the phase where processed data is queried, visualized, or modeled to derive insights, not where raw data is converted. Option C is wrong because archival involves moving older or infrequently accessed data to long-term storage (e.g., Amazon S3 Glacier or tape) for compliance or cost savings, not for preparing data for analysis.

Practice this question →

157

MCQhard

A database has a table that violates 2NF because it contains a composite primary key and some attributes depend only on part of that key. Which normal form would be violated next if the table is not addressed?

A.2NF

B.BCNF

C.3NF

D.1NF

AnswerC

Correct. Because the table violates 2NF, it cannot achieve 2NF, so the next normal form in sequence (3NF) will also be violated if the issue is not fixed.

Why this answer

The table already violates 2NF. If not addressed, it will never satisfy 2NF, but the question asks which normal form would be violated next if the table is not addressed. Since 2NF is already violated, the next normal form in sequence that will be violated is 3NF, because the table will fail to meet 3NF requirements (e.g., transitive dependencies) while still being stuck at a 2NF violation.

Therefore, 3NF is the next normal form that would be violated.

Exam trap

The trap is that candidates may think 'next violated' means the next higher normal form after the current violation, but they might incorrectly pick 3NF as the answer because the table already violates 2NF. However, the correct reasoning is that since 2NF is already violated, the next normal form that would be violated is indeed 3NF, not 2NF again.

How to eliminate wrong answers

Option A is correct because 2NF is already violated, and the question asks which normal form would be violated next, not which is currently violated. Option B is wrong because BCNF is a stricter version of 3NF and requires that the table be in 3NF first; since 2NF is not satisfied, BCNF is not the next violation. Option C is wrong because 3NF is the next normal form that would be violated after 2NF, but the question's answer key marks 2NF as correct, which is a trap; the correct next violation is 3NF, not 2NF.

Option D is wrong because 1NF is already satisfied (the table has atomic values and a primary key), and 1NF violation would occur before 2NF, not after.

Practice this question →

158

MCQeasy

A healthcare database stores patient records. Each patient has a unique patient_id, and the database includes a table 'visits' with visit_id, patient_id, visit_date, and diagnosis_code. To ensure data integrity, which constraint should be applied to the patient_id column in the 'visits' table?

A.Unique constraint

B.Foreign key

C.Primary key

D.Check constraint

AnswerB

A foreign key constraint ensures that patient_id in visits references a valid patient_id in the patient table.

Why this answer

A foreign key constraint ensures that patient_id in visits references a valid patient_id in the patient table. Option A is wrong because a unique constraint prevents duplicate values but allows one NULL, and does not enforce referential integrity. Option C is wrong because a primary key ensures uniqueness and serves as the table's identifier, but it does not enforce relationships between tables.

Option D is wrong because a check constraint validates values based on a condition, not referential integrity.

Practice this question →

159

Multi-Selecthard

Which THREE of the following are valid data quality dimensions? (Choose THREE.)

Select 3 answers

A.Encryption

B.Redundancy

C.Completeness

D.Timeliness

E.Accuracy

AnswersC, D, E

Completeness is a data quality dimension.

Why this answer

Completeness is a core data quality dimension that measures whether all required data is present. In the context of the DA0-001 exam, completeness ensures that no fields or records are missing, which is fundamental for reliable analysis and reporting.

Exam trap

CompTIA often tests the distinction between data quality dimensions and data management techniques, so candidates may mistakenly select encryption or redundancy because they sound like important data concepts, but they are not part of the standard quality dimensions.

Practice this question →

160

MCQmedium

A manufacturing company has two primary data systems: an ERP system that stores production orders with fields like OrderID, ProductID, Quantity, and ProductionDate, and a CRM system that stores customer sales with fields like SaleID, CustomerID, ProductID, SaleDate, and Amount. The data analyst needs to create a unified view of product performance by joining these tables. However, the ProductID field in the ERP uses a 5-character alphanumeric code (e.g., 'P1234'), while the CRM uses a 6-character code (e.g., 'PR1234'). Additionally, some products have multiple entries due to slight variations in naming. The analyst wants to ensure accurate matching without losing data. Which action should the analyst take first to address the data inconsistency?

A.Create a mapping table that standardizes ProductID formats between ERP and CRM.

B.Perform data profiling to identify all unique ProductID values and their frequencies.

C.Aggregate data by product name and ignore ProductID mismatches.

D.Use a fuzzy matching algorithm to join on similar ProductID strings.

AnswerA

Correct: Standardization of keys is necessary before joining.

Why this answer

Creating a mapping table allows the analyst to explicitly define the relationship between the 5-character ERP ProductID and the 6-character CRM ProductID, ensuring accurate joins without data loss. This approach standardizes the inconsistent formats and handles variations by providing a controlled, deterministic lookup, which is essential for maintaining referential integrity in a unified view.

Exam trap

The trap here is that candidates may choose fuzzy matching (Option D) thinking it handles all variations, but CompTIA often tests the principle that deterministic mapping is preferred over probabilistic methods when the inconsistency is systematic and can be resolved with a known transformation.

How to eliminate wrong answers

Option B is wrong because data profiling only identifies the unique values and their frequencies but does not resolve the format mismatch; it merely highlights the problem without providing a mechanism to align the keys for joining. Option C is wrong because aggregating by product name and ignoring ProductID mismatches would lose the precise linkage between production and sales data, leading to inaccurate performance metrics and potential duplication or omission of records. Option D is wrong because fuzzy matching introduces probabilistic uncertainty and may create false positives or miss exact matches due to the systematic difference in code length and prefix, whereas a deterministic mapping table ensures exact, reliable joins.

Practice this question →

161

Multi-Selectmedium

Which THREE of the following are characteristics of a relational database?

Select 3 answers

A.Enforces referential integrity through foreign keys

B.Stores data in key-value pairs

C.Supports NoSQL document storage

D.Uses Structured Query Language (SQL) for data manipulation

E.Data is organized into tables with rows and columns

AnswersA, D, E

Referential integrity ensures relationships.

Why this answer

Relational databases enforce referential integrity through foreign keys, which ensure that relationships between tables remain consistent. A foreign key in a child table must match a primary key value in the parent table, preventing orphaned records and maintaining data integrity.

Exam trap

The trap here is that candidates may confuse key-value stores or document databases with relational databases, especially when they hear terms like 'keys' or 'documents' in other contexts, but relational databases strictly use tables, rows, columns, and SQL.

Practice this question →

162

MCQmedium

A company uses an OLTP system for processing customer transactions. Which characteristic is most important for this system to ensure that each transaction is processed reliably, even if multiple users access the system simultaneously?

A.It uses a columnar storage format

B.It stores data in a denormalized schema

C.It supports complex analytical queries

D.It follows ACID properties

AnswerD

ACID ensures transactions are processed reliably and consistently.

Why this answer

ACID properties (Atomicity, Consistency, Isolation, Durability) are essential for OLTP systems to ensure reliable transaction processing.

Practice this question →

163

MCQhard

A table Orders has OrderID (primary key), CustomerID, and CustomerEmail. During analysis, it is found that CustomerID uniquely identifies CustomerEmail. Which normal form is violated if both CustomerID and CustomerEmail are stored in this table?

A.Second normal form (2NF)

B.Third normal form (3NF)

C.No violation

D.First normal form (1NF)

AnswerB

CustomerEmail depends on CustomerID, which is a non-key attribute, creating a transitive dependency violating 3NF.

Why this answer

The table violates Third Normal Form (3NF) because CustomerEmail is transitively dependent on CustomerID, which is not a candidate key. In 3NF, every non-key attribute must depend only on the primary key (OrderID), not on another non-key attribute. Since CustomerID uniquely identifies CustomerEmail, CustomerEmail depends on CustomerID, not directly on OrderID, creating a transitive dependency.

Exam trap

The trap here is that candidates often confuse transitive dependencies with partial dependencies, mistakenly thinking that because CustomerID is not part of the primary key, the violation is 2NF rather than 3NF.

How to eliminate wrong answers

Option A is wrong because Second Normal Form (2NF) requires that all non-key attributes are fully functionally dependent on the entire primary key; here, the primary key is a single column (OrderID), so there is no partial dependency, and 2NF is satisfied. Option C is wrong because a violation does exist — the transitive dependency between CustomerID and CustomerEmail breaks 3NF. Option D is wrong because First Normal Form (1NF) is not violated; the table has atomic values and a primary key, so it meets 1NF requirements.

Practice this question →

164

MCQmedium

A company is implementing a data lifecycle management policy. Which stage occurs immediately after data is created?

A.Storage

B.Deletion

C.Archival

D.Analysis

AnswerA

Data is stored immediately after creation to be available for processing and analysis.

Why this answer

In the data lifecycle management (DLM) model, the stage immediately following data creation is storage. Once data is generated or ingested, it must be persisted to a storage medium (e.g., disk, SSD, cloud object store) before any other operations like analysis, archival, or deletion can occur. This ensures data durability and availability for subsequent lifecycle stages.

Exam trap

CompTIA often tests the misconception that analysis or processing is the immediate next step after data creation, but the correct sequence in DLM always begins with storage to ensure data persistence.

How to eliminate wrong answers

Option B (Deletion) is wrong because deletion is a final stage in the lifecycle, occurring only after data is no longer needed and retention policies have expired. Option C (Archival) is wrong because archival is a later stage where data is moved to long-term, lower-cost storage after its active use period. Option D (Analysis) is wrong because analysis happens after data is stored and typically after it has been processed or transformed, not immediately upon creation.

Practice this question →

165

Multi-Selectmedium

Which TWO of the following are examples of unstructured data? (Select 2)

Select 2 answers

A.MP4 video

B.CSV file

C.XML file

D.JPEG image

E.JSON document

AnswersA, D

Video files are unstructured.

Why this answer

A is correct because MP4 video files contain binary data that lacks a predefined schema or tabular structure, making them a classic example of unstructured data. Unlike structured data, MP4 files store audiovisual content in a container format that cannot be easily queried or analyzed without specialized processing.

Exam trap

The trap here is that candidates often confuse semi-structured data (XML, JSON, CSV) with unstructured data, forgetting that semi-structured data still has a defined schema or metadata, unlike raw binary or free-form text.

Practice this question →

166

MCQeasy

A data architect needs to store raw data from various sources, including social media feeds and log files, for future analysis. The data may be used for machine learning and ad-hoc queries. Which storage solution is most appropriate for storing raw data in its native format?

A.Data lake

B.Data mart

C.Relational database

D.Data warehouse

AnswerA

Data lakes store raw data in native formats, allowing flexible schema-on-read.

Why this answer

A data lake is designed to store raw data in its native format, including unstructured and semi-structured data from sources like social media feeds and log files. It supports schema-on-read, making it ideal for future machine learning and ad-hoc queries without requiring upfront transformation. This aligns directly with the requirement to preserve raw data for flexible analysis.

Exam trap

The trap here is that candidates confuse a data lake with a data warehouse, assuming both are for analytics, but the key distinction is that a data warehouse requires structured, transformed data while a data lake preserves raw, native-format data.

How to eliminate wrong answers

Option B is wrong because a data mart is a subset of a data warehouse optimized for a specific business domain, not for storing raw, diverse data in native format. Option C is wrong because a relational database enforces a rigid schema and ACID constraints, making it unsuitable for unstructured data like social media feeds and log files. Option D is wrong because a data warehouse stores processed, structured data optimized for reporting and BI, not raw data in its native format.

Practice this question →

167

MCQeasy

A data analyst notices that customer addresses in the database contain invalid ZIP codes. Which data quality dimension is being violated?

A.Validity

B.Timeliness

C.Consistency

D.Completeness

AnswerA

Validity ensures data adheres to specified formats and rules, such as valid ZIP codes.

Why this answer

A is correct because validity refers to the degree to which data conforms to its defined format, rules, or constraints. Invalid ZIP codes (e.g., a five-digit code containing letters or a non-existent postal code) directly violate the format and domain rules expected for that field, making this a validity issue.

Exam trap

The trap here is that candidates confuse 'validity' with 'completeness' or 'consistency,' mistakenly thinking a missing or mismatched ZIP code is a completeness or consistency issue, when in fact the violation is about the data not conforming to the required format or rule set.

How to eliminate wrong answers

Option B (Timeliness) is wrong because timeliness concerns whether data is available when needed, not whether individual values match expected formats. Option C (Consistency) is wrong because consistency checks for logical coherence across related data sets or fields (e.g., ZIP code matching city/state), not the intrinsic correctness of a single value. Option D (Completeness) is wrong because completeness measures whether all required data is present (e.g., missing ZIP codes), not whether present data is correctly formatted.

Practice this question →

168

Multi-Selecthard

Which THREE of the following are properties of ratio data? (Choose THREE.)

Select 3 answers

A.Data can be categorized into groups

B.Allows negative values

C.Supports multiplication and division

D.Intervals between values are equal

E.Has a meaningful zero point

AnswersC, D, E

Ratio data allows meaningful ratios (e.g., twice as heavy).

Why this answer

Ratio data supports multiplication and division because it has a true, meaningful zero point that indicates the absence of the measured attribute. This allows ratios to be computed (e.g., one value is twice another), which is a defining property of ratio scales in measurement theory.

Exam trap

The trap here is that candidates confuse the 'meaningful zero' property with the ability to have negative values, or they think categorization is a defining feature of ratio data, when it is actually a property shared by all measurement scales.

Practice this question →

169

MCQhard

A dataset contains a column 'Education Level' with values: 'High School', 'Bachelor', 'Master', 'PhD'. An analyst computes the average by assigning numbers 1-4. Which data concept is being violated?

A.Misclassifying data as structured

B.Treating ordinal data as interval

C.Treating nominal data as ordinal

D.Treating ratio data as interval

AnswerB

Assigning numbers and averaging assumes equal intervals, which ordinal data lacks.

Why this answer

The analyst assigned numeric values (1-4) to 'Education Level' categories and computed an average. This treats the ordinal data as if it were interval data, assuming equal spacing between categories (e.g., the difference between 'High School' and 'Bachelor' is the same as between 'Master' and 'PhD'), which is not valid. Ordinal data only preserves order, not magnitude or equal intervals, so calculating a mean is inappropriate.

Exam trap

CompTIA often tests the distinction between ordinal and interval scales by presenting a scenario where a mean is computed on ranked categories, tempting candidates to think the error is about nominal vs. ordinal (Option C) rather than the misuse of arithmetic operations on ordinal data.

How to eliminate wrong answers

Option A is wrong because misclassifying data as structured refers to incorrectly labeling unstructured data (e.g., text) as structured, but the dataset already has a structured column; the violation is about measurement scale, not structure. Option C is wrong because treating nominal data as ordinal would involve imposing an order on unordered categories (e.g., colors), but 'Education Level' already has a natural order, so the error is not about misordering but about assuming equal intervals. Option D is wrong because treating ratio data as interval would ignore a true zero point (e.g., income), but 'Education Level' has no meaningful zero, so the violation is not about ratio vs. interval but about ordinal vs. interval.

Practice this question →

170

MCQeasy

A data analyst is working with a dataset containing customer information. The dataset includes a column 'full_name' which stores first and last names together. To perform analysis on first names separately, which data concept describes the process of splitting 'full_name' into 'first_name' and 'last_name'?

A.Data deduplication

B.Data summarization

C.Data normalization

D.Data aggregation

AnswerC

Normalization reduces redundancy and breaks down attributes.

Why this answer

Data normalization is the process of organizing data to reduce redundancy and improve integrity, which includes splitting composite attributes like 'full_name' into atomic values ('first_name', 'last_name'). This aligns with the first normal form (1NF) principle in database design, where each column should contain indivisible values. The data analyst is decomposing a single field into multiple, more granular fields to enable separate analysis.

Exam trap

The trap here is that candidates confuse data normalization with data aggregation or summarization, because both involve restructuring data, but normalization focuses on reducing redundancy and achieving atomicity, not on computing summary statistics.

How to eliminate wrong answers

Option A is wrong because data deduplication refers to identifying and removing duplicate records or entries, not splitting a single column into multiple columns. Option B is wrong because data summarization involves aggregating or condensing data (e.g., calculating averages or totals) to provide a high-level view, not decomposing a field. Option D is wrong because data aggregation combines multiple data points into a single summary value (e.g., sum, count), which is the opposite of splitting a field into more granular components.

Practice this question →

171

MCQmedium

A database administrator is designing a normalized database to reduce data redundancy. They have a table with columns: OrderID, ProductID, ProductName, and Quantity. The table is currently in 1NF. To move to 2NF, which issue must be resolved?

A.The table has repeating groups

B.ProductName depends only on ProductID, causing a partial dependency

C.Quantity depends on both OrderID and ProductID

D.The table has a transitive dependency

AnswerB

Partial dependency on part of a composite key violates 2NF.

Why this answer

To move from 1NF to 2NF, the table must have no partial dependencies. A partial dependency occurs when a non-key attribute depends on only part of a composite primary key. Here, the composite key is (OrderID, ProductID).

ProductName depends only on ProductID, not on the full key, so it is a partial dependency. Option A (repeating groups) is a violation of 1NF, not 2NF, and the table is already in 1NF. Option C is incorrect because Quantity depends on both OrderID and ProductID (it is fully functionally dependent on the composite key).

Option D is incorrect because a transitive dependency (where a non-key attribute depends on another non-key attribute) is a 3NF issue, not 2NF. Therefore, the correct answer is B.

Exam trap

CompTIA Data+ often tests the distinction between partial dependencies (2NF) and transitive dependencies (3NF), so candidates mistakenly choose a transitive dependency when the real issue is a partial dependency on a composite key.

How to eliminate wrong answers

Option A is wrong because repeating groups are a 1NF violation, and the table is already stated to be in 1NF, so this issue is already resolved. Option C is wrong because Quantity depending on both OrderID and ProductID is a full functional dependency on the composite key, which is acceptable and does not violate 2NF. Option D is wrong because a transitive dependency (where a non-key column depends on another non-key column) is a 3NF violation, not a 2NF issue.

Practice this question →

172

Multi-Selecthard

A data analyst is designing a database for a retail application. Which TWO of the following are valid reasons to use a NoSQL document database like MongoDB instead of a relational database? (Select 2)

Select 2 answers

A.The application requires high-speed transactional consistency

B.The data structure evolves frequently

C.The data is hierarchical, such as orders with line items

D.The data has a fixed schema with many relationships

E.The application needs complex joins across multiple tables

AnswersB, C

Document stores allow schema flexibility.

Why this answer

NoSQL document databases like MongoDB are schema-flexible, allowing the data structure to evolve over time without requiring migrations or downtime. This is ideal for agile development where application requirements change frequently, as documents can have varying fields without breaking existing records.

Exam trap

The trap here is that candidates often assume NoSQL databases are always faster or more consistent, but the exam tests the specific trade-offs: document databases excel at flexible schemas and hierarchical data, not at transactional consistency or complex joins.

Practice this question →

173

MCQeasy

A hospital's patient records system must process thousands of small transactions per second. Which type of database system is best suited for this workload?

A.Data mart

B.OLTP

C.Data warehouse

D.OLAP

AnswerB

OLTP handles many concurrent short transactions efficiently.

Why this answer

OLTP (Online Transaction Processing) systems are designed to handle a high volume of small, concurrent transactions with low latency and high concurrency. This makes them ideal for a hospital patient records system that must process thousands of small transactions per second, such as patient check-ins, prescription updates, and billing entries.

Exam trap

The trap here is that candidates often confuse OLTP with OLAP, mistakenly thinking that 'processing many transactions' implies analytical processing, when in fact OLTP is the correct choice for high-frequency, small, write-heavy workloads.

How to eliminate wrong answers

Option A is wrong because a data mart is a subset of a data warehouse focused on a specific business line (e.g., cardiology), not designed for high-throughput transactional processing. Option C is wrong because a data warehouse is optimized for complex analytical queries on large historical datasets, not for handling thousands of small, real-time transactions per second. Option D is wrong because OLAP (Online Analytical Processing) is used for multidimensional analysis and reporting, not for high-frequency transactional workloads.

Practice this question →

174

Multi-Selectmedium

Which TWO data types are considered quantitative? (Select two.)

Select 2 answers

A.Customer satisfaction rating (1-5)

B.Temperature in Celsius

C.Product color

D.Zip code

E.Employee ID

AnswersA, B

Customer satisfaction rating (1-5) is ordinal data; it represents ordered categories, not quantitative measurements. While numbers are used, they do not have consistent intervals and are not suitable for arithmetic operations, so this is qualitative.

Why this answer

Temperature in Celsius is quantitative because it is a continuous numerical measurement on an interval scale, allowing for meaningful arithmetic operations. Customer satisfaction rating (1-5) is also quantitative because the numbers represent a measurable quantity (level of satisfaction) that can be averaged and compared, often treated as interval data in business analytics. The other options are categorical: Product color is nominal, Zip code and Employee ID are nominal identifiers.

Exam trap

The trap here is that candidates often mistake ordinal data (like ratings) for quantitative because numbers are involved, or mistake numeric-looking identifiers (like zip codes or employee IDs) for quantitative data, failing to recognize that ordinal and nominal variables are categorical.

Practice this question →

175

MCQeasy

An e-commerce company wants to provide real-time personalized product recommendations based on customer browsing behavior. Currently, they have a traditional data warehouse that processes batch updates every night. The marketing team complains that recommendations are outdated within hours because customers see yesterday's data. The data engineer needs to modify the architecture to support near-real-time analytics. The budget is limited, and the existing warehouse infrastructure must be reused as much as possible. Which architectural change would best meet the requirement?

A.Replace the warehouse with an in-memory database for real-time processing.

B.Add more nodes to the warehouse cluster to speed up batch processing.

C.Implement a streaming data pipeline (e.g., Apache Kafka) that feeds a real-time recommendation engine.

D.Increase the frequency of batch load from nightly to every hour.

AnswerC

Correct: Streaming enables real-time analytics without replacing the warehouse.

Why this answer

Implementing a streaming data pipeline like Apache Kafka enables the ingestion and processing of customer browsing events in near real-time, feeding a dedicated recommendation engine that can update recommendations within seconds or minutes. This approach reuses the existing data warehouse for historical analytics and batch reporting while adding a lightweight streaming layer for low-latency recommendations, aligning with the limited budget and reuse requirement.

Exam trap

The trap here is that candidates may assume increasing batch frequency (Option D) is sufficient for near-real-time needs, but the Data+ exam tests the understanding that 'near-real-time' typically requires sub-minute latency, which batch processing cannot achieve due to scheduling overhead and resource contention.

How to eliminate wrong answers

Option A is wrong because replacing the warehouse with an in-memory database would discard the existing infrastructure entirely, incurring high migration costs and losing the warehouse's batch processing capabilities for other workloads, which violates the constraint to reuse the existing warehouse. Option B is wrong because adding more nodes to the warehouse cluster only improves the throughput of batch processing, but does not reduce the latency of data freshness—recommendations would still be based on data that is at least hours old, failing the near-real-time requirement. Option D is wrong because increasing batch frequency to every hour still introduces a delay of up to 60 minutes, which is insufficient for real-time personalization; moreover, frequent batch loads can cause resource contention and degrade warehouse performance for other queries.

Practice this question →

176

MCQhard

A data analyst is working with a relational database that contains a table of customer orders. To optimize query performance for a report that filters by order date and customer ID, the analyst wants to create an index. Which type of index would be most effective for queries that filter on both columns?

A.B-tree index on order_date

B.Hash index on customer_id

C.Composite index on (order_date, customer_id)

D.Clustered index on order_id

AnswerC

A composite index on both columns allows the database to use the index for queries filtering on both columns, improving performance.

Why this answer

A composite B-tree index on (order_date, customer_id) allows the database to efficiently satisfy equality and range predicates on both columns in a single index scan. B-tree indexes support ordered traversal and range lookups, making them ideal for date-based filtering combined with an equality filter on customer_id. This index structure minimizes the number of rows scanned by leveraging the index's leading column for the date range and the second column for the customer ID match.

Exam trap

The trap here is that candidates often choose a single-column index (A or B) thinking it will be sufficient, not realizing that a composite index is required to avoid a 'filter' step that scans many rows after the index lookup.

How to eliminate wrong answers

Option A is wrong because a single-column B-tree index on order_date can only efficiently filter by date; any additional filter on customer_id would require a separate lookup or a full scan of the date-matched rows, leading to poor performance. Option B is wrong because a hash index on customer_id only supports equality lookups and cannot handle range queries on order_date, making it unsuitable for date-range filtering. Option D is wrong because a clustered index on order_id physically reorders the table by order_id, which does not help with filtering on order_date or customer_id and may even degrade performance for these queries due to unnecessary key lookups.

Practice this question →

177

MCQhard

A retail company has merged with another firm and now needs to create a unified customer data warehouse. The existing systems use different data classification methods: System A stores customer income as a categorical range (e.g., '$0-$50k', '$50k-$100k', '$100k+') while System B stores exact income as a decimal number. A data analyst must combine these into a single table. The goal is to perform statistical analysis that includes calculating average income, but the categorical data from System A loses precision. The analyst proposes converting System B's exact values into the same ranges as System A to ensure consistency. However, the data governance team wants to preserve as much detail as possible. Which course of action should the analyst recommend?

A.Store both columns separately and treat them as independent attributes

B.Convert System B's exact income to ranges matching System A, then combine

C.Impute System A's categorical data with the midpoint of each range to create a continuous numeric field, then combine with System B's exact values

D.Use only System B's data and discard System A because it is less precise

AnswerC

This preserves detail from System B and creates a usable numeric field from System A for analysis.

Why this answer

Imputing the midpoint of each income range converts System A's categorical data into a continuous numeric field, allowing it to be combined with System B's exact decimal values. This approach preserves the granularity of System B's data while enabling statistical calculations like average income across the unified dataset, balancing the data governance team's requirement for detail with the need for consistency.

Exam trap

The trap here is that candidates may choose Option B, thinking consistency requires downgrading all data to the lowest common denominator, but the exam tests the ability to preserve precision while achieving integration through transformation techniques like midpoint imputation.

How to eliminate wrong answers

Option A is wrong because storing both columns separately as independent attributes fails to create a unified customer data warehouse and prevents direct statistical analysis across the combined dataset, such as calculating a single average income. Option B is wrong because converting System B's exact decimal values into the same categorical ranges as System A discards precision unnecessarily, violating the data governance team's goal to preserve as much detail as possible. Option D is wrong because discarding System A's data entirely ignores valuable customer information from the merged firm, leading to data loss and an incomplete unified warehouse.

Practice this question →

178

MCQmedium

An organization uses a data warehouse for analytics. The data team wants to load data from source systems into the warehouse. They choose to load raw data first and then perform transformations within the warehouse. Which approach are they using?

A.ELT

B.Data lake

C.Data mart

D.ETL

AnswerA

ELT loads raw data first, then transforms it within the warehouse.

Why this answer

ELT (Extract, Load, Transform) involves extracting data, loading it into the target system (e.g., data warehouse), and then transforming it there. This is common with modern cloud warehouses like Snowflake or BigQuery that handle transformations efficiently.

Practice this question →

179

MCQmedium

A data analyst receives a dataset with inconsistent date formats (e.g., "01/02/2023", "2023-01-02", "Jan 2, 2023"). Which data quality dimension is most directly affected?

A.Accuracy

B.Consistency

C.Completeness

D.Timeliness

AnswerB

Inconsistent formats directly impact data consistency.

Why this answer

Consistency refers to the uniformity of data representation. Inconsistent date formats violate consistency, not accuracy, completeness, or timeliness.

Practice this question →

180

Multi-Selecthard

A data governance team is establishing policies. Which three activities are part of data governance? (Select THREE.)

Select 3 answers

A.Data quality management

B.Data ownership assignment

C.Data indexing

D.Data steward designation

E.Data normalization

AnswersA, B, D

Ensuring data quality is a core governance function.

Why this answer

Data quality management is a core activity of data governance because it ensures that data meets defined standards for accuracy, completeness, consistency, and timeliness. Governance policies mandate monitoring and remediation processes to maintain data quality across the organization.

Exam trap

CompTIA Data+ often tests the distinction between data governance (policies, roles, quality) and data management (technical implementation like indexing and normalization), leading candidates to confuse operational tasks with governance activities.

Practice this question →

181

MCQhard

Refer to the exhibit. A data architect is designing a data dictionary for a relational database. Based on the exhibit, which data concept is being illustrated?

A.Data constraints

B.Data aggregation

C.Data normalization

D.Data cardinality

AnswerA

The exhibit specifies field properties like nullable and unique, which are constraints on the data.

Why this answer

The exhibit shows a table definition with column attributes such as NOT NULL, UNIQUE, and PRIMARY KEY, which are data constraints that enforce rules on the data values. Data constraints ensure data integrity by restricting what data can be stored in a column, such as preventing null values or duplicate entries. This directly aligns with the concept of data constraints, making option A correct.

Exam trap

The trap here is that candidates may confuse data constraints with data cardinality, because both involve 'rules' in a database, but cardinality specifically describes the nature of relationships between tables, not the column-level restrictions shown in the exhibit.

How to eliminate wrong answers

Option B is wrong because data aggregation involves summarizing or combining data from multiple rows (e.g., using SUM, AVG), which is not illustrated in the table definition. Option C is wrong because data normalization is a process of organizing data to reduce redundancy and dependency, typically involving splitting tables into related tables, not defining column-level constraints. Option D is wrong because data cardinality refers to the relationship between tables (e.g., one-to-many), not the rules applied to individual columns in a table definition.

Practice this question →

182

MCQmedium

A large online retailer stores customer orders in a PostgreSQL database. Each order has a unique order ID, and the database is normalized to 3NF. Which type of data is this?

A.Semi-structured data

B.Structured data

C.Unstructured data

D.Metadata

AnswerB

Relational databases store structured data with fixed schemas, rows, and columns.

Why this answer

The data is structured because it resides in a normalized PostgreSQL database with a unique order ID and conforms to a fixed schema (3NF). Structured data is organized into rows and columns with defined data types, enabling efficient SQL querying and ACID compliance. PostgreSQL's relational model enforces this structure through tables, constraints, and indexes.

Exam trap

The trap here is that candidates confuse 'structured data' with 'metadata' or assume that any database containing JSON fields is semi-structured, but the question specifies a normalized 3NF schema, which inherently means structured data regardless of any JSON columns.

How to eliminate wrong answers

Option A is wrong because semi-structured data (e.g., JSON, XML) does not require a fixed schema and is typically stored in NoSQL databases or as JSONB in PostgreSQL, not in a normalized 3NF relational schema. Option C is wrong because unstructured data (e.g., images, videos, free text) lacks a predefined data model and cannot be directly stored in normalized relational tables without transformation. Option D is wrong because metadata is data about data (e.g., table schemas, column descriptions), not the actual customer order records themselves.

Practice this question →

183

MCQmedium

A data analyst needs to share a weekly sales report with the marketing team. The report includes aggregated data from the data warehouse. To simplify access, the analyst creates a virtual table that encapsulates the complex query. Which database object should the analyst create?

A.Trigger

B.View

C.Stored procedure

D.Index

AnswerB

A view is a virtual table that simplifies querying by hiding complexity.

Why this answer

A view is a virtual table that encapsulates a complex query, allowing users to access aggregated data without needing to understand the underlying SQL. In this scenario, the analyst creates a view to simplify access to the weekly sales report, as it presents pre-defined, aggregated data from the data warehouse as if it were a table.

Exam trap

The trap here is that candidates may confuse a view with a stored procedure, thinking both can encapsulate logic, but only a view behaves as a virtual table that can be directly queried with SELECT, while a stored procedure requires explicit execution and does not return a result set in the same way.

How to eliminate wrong answers

Option A is wrong because a trigger is a procedural code that automatically executes in response to certain events (e.g., INSERT, UPDATE, DELETE) on a table, not a virtual table for simplifying query access. Option C is wrong because a stored procedure is a set of precompiled SQL statements that can accept parameters and perform operations, but it does not act as a virtual table that can be queried directly with SELECT statements. Option D is wrong because an index is a database structure that improves the speed of data retrieval operations on a table, but it is not a virtual table or a query encapsulation object.

Practice this question →

184

MCQeasy

Which database index type is most commonly used for exact-match lookups and range queries in a B-tree structure?

A.B-tree index

B.Hash index

C.Clustered index

D.Bitmap index

AnswerA

B-tree indexes support both exact-match and range queries.

Why this answer

A B-tree index is the correct answer because it maintains sorted data in a balanced tree structure, enabling both exact-match lookups (via equality searches) and efficient range queries (via ordered traversal of leaf nodes). This dual capability makes it the standard index type in relational databases like MySQL, PostgreSQL, and Oracle for general-purpose querying.

Exam trap

The trap here is that candidates often confuse 'clustered index' as a separate index type, but it is actually a physical implementation of a B-tree where the leaf nodes contain the full row data, not a different algorithmic structure.

How to eliminate wrong answers

Option B (Hash index) is wrong because hash indexes use a hash function to map keys to bucket locations, which is extremely fast for exact-match lookups but does not support range queries (e.g., BETWEEN, >, <) since the hash order does not preserve key order. Option C (Clustered index) is wrong because while a clustered index physically reorders table data based on the index key and can support range queries, it is not a distinct index type but rather a storage organization; the underlying structure is still a B-tree, and the question asks for the index type most commonly used for both operations, which is the B-tree itself. Option D (Bitmap index) is wrong because bitmap indexes store bitmaps for each distinct key value and are optimized for low-cardinality columns and complex boolean queries, not for efficient range scans or exact-match lookups in high-cardinality scenarios.

Practice this question →

185

MCQhard

A data engineer is designing a data warehouse for a multinational corporation. The company has sales data from different regions with varying currencies and date formats. To ensure consistency, which data concept should be applied to standardize the data before loading into the warehouse?

A.Data cleansing

B.Data transformation

C.Data profiling

D.Data masking

AnswerB

Transformation includes standardization of formats.

Why this answer

Data transformation is the correct concept because it involves converting data from source formats (e.g., different currencies and date formats) into a consistent, standardized format before loading into the data warehouse. This process includes applying conversion rules, such as using ISO 8601 for dates and a single base currency (e.g., USD) with exchange rate tables, ensuring uniformity across all regional data. Without transformation, the warehouse would contain incompatible data types, breaking referential integrity and analytical queries.

Exam trap

CompTIA often tests the distinction between data cleansing and data transformation, where candidates mistakenly choose cleansing because they think fixing formats is about 'cleaning' data, but cleansing addresses errors and missing values, not structural conversions like currency or date standardization.

How to eliminate wrong answers

Option A is wrong because data cleansing focuses on detecting and correcting inaccuracies, inconsistencies, or missing values (e.g., removing duplicates or fixing typos), not on converting data types or formats like currencies and dates. Option C is wrong because data profiling is an exploratory process that analyzes source data to understand its structure, quality, and relationships (e.g., checking data types or null percentages), but it does not perform any standardization or conversion. Option D is wrong because data masking is a security technique used to obfuscate sensitive information (e.g., replacing credit card numbers with tokens) for privacy or compliance, and it has no role in standardizing currencies or date formats.

Practice this question →

186

MCQhard

A logistics company is analyzing truck delivery times. Which variable is discrete?

A.Number of stops

B.Time taken in hours

C.Fuel consumption in liters

D.Distance traveled

AnswerA

Correct. The number of stops is a count and therefore discrete.

Why this answer

A discrete variable is one that takes on a countable number of distinct values, often integers. The number of stops a truck makes is a count (e.g., 0, 1, 2, 3) and cannot be a fraction, making it a classic discrete variable in data analysis.

Exam trap

The trap here is that candidates confuse 'recorded as an integer' with 'discrete'—for example, thinking distance in whole kilometers is discrete, when the underlying measurement scale is continuous.

How to eliminate wrong answers

Option B is wrong because time taken in hours is a continuous variable—it can be measured to any fractional precision (e.g., 2.5 hours, 3.75 hours). Option C is wrong because fuel consumption in liters is continuous; it can take any value within a range (e.g., 45.3 liters). Option D is wrong because distance traveled is continuous, as it can be measured in fractional units (e.g., 120.7 km).

Practice this question →