Databases: Oracle, SQL Server, and Data Warehousing

Snapshot replication is ideal for this scenario because it distributes a complete copy of the publication at scheduled intervals (daily 5 a.m.). The publication is not large, and while there are many changes during the day, snapshot replication completely refreshes subscriber data each time. Transactional and merge replication are more complex and unnecessary for this daily refresh requirement.

Truncate is a DDL statement that quickly deletes all rows in a table without logging individual row deletions. Delete is a DML statement that logs every row deletion in the transaction log, while drop completely deletes the table structure from the database.

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.

SQL Server indexes do use B-TREE structures (A is true), allow multiple nonclustered indexes per table (B is true), and create a nonclustered index by default when not specified (D is true). However, the limit for composite index columns is much lower than 256 - it's 16 columns (32 in some newer versions with limitations), making option C incorrect.

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.

Truncate is a DDL command that deletes all rows from a table by deallocating the data pages, bypassing the rollback/undo segments for speed. Delete is a DML command that logs each row deletion, and Drop removes the entire table structure, not just the data.

ROWID is the direct physical address of a row's location on disk, making it the fastest access method. Primary key and unique index accesses require an index lookup first, then ROWID access. Full table scan reads every row in the table, making it the slowest method.