Databases and SQL Concepts

ETL stands for Extract, Transform, Load - the three fundamental processes of data integration: extracting data from sources, transforming it to meet business needs, and loading it into target systems.

Parent package variable values are passed to child packages through package configurations. By configuring the child package to use parent variables, values can be inherited without direct coupling.

Partitioned Primary Index (PPI) is defined during the Physical modeling stage. PPI affects how data is physically stored on disk, making it a physical database design decision rather than logical or extended logical.

Data demographics describe statistical characteristics of a dataset. Counting NULL values provides information about data completeness and sparsity, making it a valid demographic metric.

When deciding whether to store derived data or calculate it on the fly, access frequency is a key factor - frequently accessed data benefits from storage. Change rating (volatility) is equally important - data that changes often is better calculated to avoid stale values. Maximum NULLs and rows per value are not primary considerations for this decision.

Identity columns in Teradata are independent of the Primary Index - they can be defined on non-PI columns. They are also allowed to be part of the PI, contradicting option B. Teradata enforces a strict limit of one identity column per table to maintain data integrity and avoid ambiguity in auto-generation.

A USI (Unique Secondary Index) subtable stores the index value itself, the RowID pointing to the base table row, and the secondary index's own RowID for internal navigation. AMP number buckets are part of the hash distribution mechanism, not stored in USI subtables. Value-ordered NUSIs have different structures than USIs.

USI indexes eliminate full‑table scans, avoid duplicate‑row checks, and enforce uniqueness, preventing insertion of duplicate values. These three benefits are accurately marked as correct; the other choices either claim nonexistent overhead elimination or misuse of range queries, which are incorrect.

Data type assignments are made at the column level in database schemas, not at the row level. Each column has a single data type that applies uniformly across all rows. Assigning types per row would violate fundamental relational database principles and create severe data integrity issues.

A Cover Query is optimized to retrieve all required columns directly from a Join Index without accessing the base table. Join Indexes pre-join and store frequently queried column combinations, allowing the optimizer to satisfy queries entirely from the index structure. This eliminates expensive joins and dramatically improves performance.

Join Indexes store pre-joined data from multiple tables, naturally resulting in repeating values as the join relationship creates duplicates. Teradata automatically maintains Join Indexes when underlying base table rows change, ensuring the pre-joined data stays synchronized without manual intervention.

Performance is the primary motivation for denormalization - reducing joins improves read speed. Volatility is equally critical - frequently changing data causes update anomalies and maintenance overhead in denormalized structures. Downtime and stability are operational concerns, not architectural considerations for denormalization.

The OR operator displays a record if EITHER condition is true, not necessarily both. The AND operator requires both conditions to be true. Therefore, the statement incorrectly describes OR's behavior - it's actually defining AND.

The IN operator lets you test if a value matches any value in a list. For example, WHERE city IN ('Mumbai', 'Delhi', 'Chennai') returns rows where city equals any of those three values. BETWEEN checks for a range, not a list of discrete values.

UNION combines results from multiple SELECT statements into one result set. SQL requires all SELECTs in a UNION to have identical column count, compatible data types in each position, and the same column order. These structural rules ensure rows can be stacked vertically.

Scalar functions operate on single values and return single values. MID() extracts a substring from a text value, and NOW() returns the current timestamp - both are scalar. MAX() and SUM() are aggregate functions that operate on multiple rows and return summary values.

In SQL, aggregate functions like COUNT, SUM, AVG cannot be used directly in the WHERE clause because WHERE filters rows before aggregation. Instead, use HAVING after GROUP BY to filter based on aggregate results. This statement accurately describes that limitation.

DBB (Data Base Block) mode provides optimized batch processing for IMS programs by reducing overhead and improving data access efficiency compared to DLI (Data Language/I) mode. Option A is correct. DBB's batch-oriented approach is faster for sequential processing, making the statement true.

INIT call checks database availability (A is true). In IMS COBOL, GOBACK is actually the recommended way to end programs (B is false). When an SSA (Segment Search Argument) contains only the segment name without qualification, byte 9 must be blank (C is true). Basic checkpoint calls don't support GSAM files (D is true).