Database Fundamentals and Teradata

A full table scan (FTS) in Teradata requires reading every single data block containing the table's records. During this operation, each row is accessed exactly once to evaluate filter conditions. FTS does not copy rows to temporary space, nor does it read a random subset of blocks.

When data distribution is skewed across AMPs, some AMPs receive more data rows than others. During an all-AMP operation, all AMPs must complete their work, and the operation finishes when the slowest AMP completes. Therefore skewed distribution causes uneven workload and prolongs total execution time. NUPI can cause skew and is not ideal for maximum efficiency.

Teradata's primary unique features are its highly mature, cost-based parallel query optimizer and its shared-nothing parallel architecture (AMPs/BYNET). Features like SQL support and concurrent user access are industry-wide database standards and not exclusive to Teradata.

TDQM (Teradata Query Manager) is the tool used to manage and restrict queries based on defined thresholds such as execution time, resource usage, or other performance criteria. It acts as a governor for query execution. Teradata Manager provides monitoring but not threshold-based restrictions, while BTEQ and SQL Assistant are query tools.

Teradata spool space is allocated dynamically from unused permanent disk space. Since the system has 100 GB of total space and 30 GB is used by data, 70 GB remains unused and available for spool space. Stated spool limits restrict individual consumption, but the total physical capacity available for spool queries is 70 GB.

With a Partitioned Primary Index (PPI), data distribution across AMPs is still determined by the Primary Index hash value, just like a regular PI. The partitioning component adds a secondary organization layer within each AMP, but does not affect how rows are distributed to AMPs initially. The partitioning column defines how data is organized within each AMP, not across AMPs.

PPI provides two key advantages: (C) It enables efficient range queries and eliminates full table scans without requiring a secondary index structure, and (D) It ensures all data within a specific range is stored on the same AMP, improving range query performance. Option A is incorrect because NUPI, not PPI, guarantees uniqueness. Option B is incorrect - while PPI facilitates data removal/addition by partition, it's not instantaneous.

OLTP (Online Transaction Processing) systems are characterized by individual, transactional operations that update data in real-time. Updating a checking account to reflect a deposit is a classic OLTP operation. The other options - sales comparison reports, credit limit reports, and top-selling item reports - are all OLAP (Online Analytical Processing) activities involving historical data analysis and reporting.

User A is granted 30 GB from the system's 100 GB pool. When User A creates User B with 20 GB, that 20 GB is allocated from User A's 30 GB allotment. Therefore User A retains 10 GB (30 - 20 = 10) for personal use. This is the hierarchical space allocation model in Teradata.

A Unique Secondary Index (USI) requires accessing exactly two AMPs to verify uniqueness - one AMP to check if the value exists, and a second AMP to ensure the value isn't duplicated elsewhere in the system. This differs from a Non-Unique Secondary Index (NUSI) which typically accesses all AMPs or a subset. The two-AMP access is part of the uniqueness guarantee mechanism.

A transaction is defined as a logical unit of work (LUW) - one or more SQL statements that are treated as a single atomic operation. Either all statements in the transaction complete successfully, or none do (ACID properties). A transaction is not simply a logon/logoff sequence, nor is it limited to a single SQL statement, and while it relates to recovery, it's not defined solely by recovery units.

To find employee numbers appearing more than twice, we need to: 1) Group by empno to aggregate occurrences, 2) Count occurrences with count(*), 3) Filter groups having count > 2 using HAVING clause. Option D correctly implements all three steps. Option A lacks the empno column in SELECT. Option B lacks GROUP BY. Option C incorrectly uses WHERE instead of HAVING for aggregate filtering.

In relational database terminology, a row represents a single complete record of data. Columns are called attributes or fields. The entire table represents a relation. Therefore, 'Record' is the correct term for a row in database context.

Single-value functions (also called scalar functions) operate on individual values and return a single value. SQRT() is a classic example - it takes one number and returns its square root. AVG() and COUNT() are aggregate functions that operate on multiple rows. Option D 'All the Above' is incorrect because it mixes scalar and aggregate functions.

Normalization is the process of organizing data in a database to minimize data redundancy and dependency. Views, aliases, and sequences do not reduce redundancy; they serve for abstraction, renaming, and generating unique numbers.

Joining two tables without specifying a join condition in the WHERE clause (or an ON clause) performs a Cartesian product (CROSS JOIN), resulting in the multiplication of their rows (100 * 50 = 5000 rows).

Third Normal Form (3NF) requires that all non-key attributes are functionally dependent only on the primary key, eliminating transitive dependencies where a non-key attribute depends on another non-key attribute. 2NF only addresses partial dependencies, while 5NF deals with join dependencies.

In unchained mode, explicit transactions initiated with 'begin transaction' still require an explicit 'commit transaction' to persist. A commit is not automatically implied for explicit transactions, making this statement false.

In SQL Server, the modulo operator (%) cannot be used with money or smallmoney datatypes because these represent fixed-point decimal currency values rather than integers. All other listed types (tinyint, smallint, float) support the modulo operation correctly.