Database Management Systems

SQL Server provides multiple tools for data import and export: BULK INSERT is a T-SQL command for bulk data loading, BCP (Bulk Copy Program) is a command-line utility, and DTS (Data Transformation Services) is a graphical tool for data transfer and transformation. All three are valid options.

The generic two-level data warehouse architecture consists of a central data warehouse that extracts and consolidates data from multiple internal and external sources. Data marts (A) are derived from the warehouse, not part of the core architecture definition, and near real-time updates (C) are not a required feature of the basic architecture.

When a user creates an object in Oracle without specifying a TABLESPACE clause, Oracle stores the segment in that user's default tablespace. This is a user-specific default, not the system tablespace. Each user can be assigned a specific default tablespace for their objects.

DBA_SYS_PRIVS provides information about all system privileges granted in the database. The other options are more specific: DBA_COL_PRIVS for column-level privileges, DBA_OBJ_PRIVS for object privileges, and DBA_TAB_PRIVS for table privileges. System privileges are the broadest category of grants.

In database mirroring, the mirror database remains in a restoring/standby state and cannot be directly read or written to by users, representing idle capacity. Identical hardware is not strictly mandatory, automatic failover is supported, and a witness server is optional.

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.

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.

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.