Database Administration and Data Warehousing

The Database Maintenance Plan Wizard provides a step-by-step interface specifically designed for creating backup and maintenance plans. It is the most appropriate tool for a junior DBA because it simplifies the process of creating a reliable backup strategy. Database Engine Tuning Advisor is for query optimization, and Configuration Manager manages server services.

Activity Monitor is the SQL Server tool that provides real-time monitoring of running processes and includes the ability to kill blocking processes. System Monitor (perfmon) is for performance counters, SQL Profiler traces queries, and SSRS is for reporting. Activity Monitor is the correct tool for process management.

SQL's declarative nature allows the same query to be written in multiple syntactically different ways, which can result in vastly different execution plans and performance. This is a well-known challenge in SQL optimization. SQL does support object-oriented features through extensions, its syntax is widely used by non-programmers, and locking behavior is configurable.

An active data warehouse architecture includes all the listed components: data marts for focused subject areas, data extraction from multiple sources (internal and external), and near real-time update capabilities. These are all essential characteristics of an active data warehouse. The question asks 'which of the following' making 'All of the above' the correct answer.

Reconciled data is current, consistent data that serves as the single, authoritative source for all decision support systems in an organization. It is not the raw data in operational systems (which may be inconsistent), nor is it data formatted for specific end-user applications. The reconciliation process creates this unified, current data source.

Transient data holds only the current state; any update or change to an existing record overwrites and eliminates the previous version. Periodic or historical data retains past versions, while persistent data is characterized by never being altered or deleted after addition.

The extract process captures a subset of data from operational systems for loading into the data warehouse, not all data. Decision support systems are the targets, not sources, of the extract process. Selecting and extracting only relevant data is fundamental to ETL processes.

Data scrubbing is the process of cleaning and improving data quality BEFORE it is loaded into the data warehouse. It involves correcting errors, standardizing formats, and resolving inconsistencies during the ETL process. Scrubbing happens as part of the transformation phase, not after loading.

The load and index process involves importing data into the data warehouse (loading) and building the indexes required for query performance. Data quality upgrades are part of cleansing and transformation processes that occur before or during the staging phase rather than the loading phase.

Data transformation includes converting detailed data into summarized data, such as aggregating transaction-level data into daily or monthly totals. The other options are incorrect: transformation doesn't convert summary to detail, join from one to various sources, or split data arbitrarily. Aggregation is a core transformation function.

A multifield transformation (like Normalizer) can perform all three patterns: splitting one field into many (parsing), combining multiple fields into one (concatenation/aggregation), and mapping multiple fields to multiple fields (complex restructuring). Informatica's Normalizer is the classic multifield transformation.

In a star schema, each dimension table relates to the fact table in a one-to-many relationship - one dimension record (like Product ID) links to many fact table records (transactions). The dimension is the 'one' side, facts are the 'many' side. Many-to-many would require a bridge table, and one-to-one is atypical in dimensional modeling.

Fact tables are typically completely normalized (to 3NF) in dimensional modeling to avoid redundancy and update anomalies. Each fact row represents a single measurement event and contains only foreign keys to dimensions and numeric measures. Denormalization is reserved for dimension tables (for performance), not fact tables.

A snowflake schema extends the star schema by normalizing dimension tables into multiple levels. This introduces all three table types: fact tables (central), dimension tables (primary and secondary), and helper/bridge tables (additional normalization levels). The 'helper' tables are the normalized-out dimension attributes.

Data mining aims to discover previously unknown patterns and explain observed phenomena in data. It's about finding insights that weren't explicitly sought (explaining why something happened). Confirming data existence is trivial, analyzing for 'expected' relationships is standard reporting, and data warehouse creation is an ETL task, not mining.

The 'invalid object or does not exist' error in the APPS schema occurs when the database synonyms or grants pointing from the APPS schema to the underlying product schemas (like GL) are missing or broken. Recreating grants and synonyms using the adadmin utility restores these links. Relinking, patching, or reinstalling is premature.

In Oracle Applications 11i, the Oracle 8.0.6 database files and iAS (Oracle Internet Application Server) files are stored in the ORA directory. The DB directory doesn't exist in the standard structure. COMN contains common files, FND_TOP is for Application Object Library, and APPL_TOP contains product-specific application files.

Running validation on the APPS schema is most critical after applying patches (when schema changes are most likely) and when encountering run-time errors suggesting AD_DDL package issues (indicating potential corruption). The other options like before export/import or during organizational conversion are not the primary validation triggers - validation should be done after changes, not before them.