CAF-3 · Chapter 6
Data Warehouse Schemas & Data Marts MCQs with Answers
15 multiple-choice questions on Data Warehouse Schemas & Data Marts for CAF-3 Data, Systems and Risks. Try each one before revealing the answer and explanation.
Practise this chapter interactivelyQuestion 1
An organization’s customer database frequently suffers from update anomalies because the same customer’s address is stored in multiple different tables. To minimize this data redundancy and ensure data integrity, the database administrator should apply which process?
- A) Data Extraction
- B) Database Normalization
- C) Data Warehousing
- D) Horizontal Scaling
Show answer & explanation
Answer: B) Database Normalization
Database normalization is a systematic process used to organize data in relational databases to minimize redundancy, avoid anomalies, and ensure data integrity
Question 2
A bank's database table stores a customer's 'Phone Number' attribute. However, several customers have submitted two or three phone numbers, which the data entry clerk has entered into a single cell separated by commas. To achieve First Normal Form (1NF), what must be done?
- A) Ensure all non-key attributes depend on the entire primary key.
- B) Eliminate all foreign keys.
- C) Ensure there are no repeating groups and all attributes are atomic (single-valued).
- D) Remove transitive dependencies.
Show answer & explanation
Answer: C) Ensure there are no repeating groups and all attributes are atomic (single-valued).
A table is in 1NF only if it has no repeating groups and all attributes are atomic, meaning each cell contains only a single, indivisible value
Question 3
A retail company’s 'Order Details' table uses a composite primary key consisting of both OrderID and ProductID. However, the attribute ProductName depends only on the ProductID and not the OrderID. This violates which normal form?
- A) First Normal Form (1NF)
- B) Second Normal Form (2NF)
- C) Third Normal Form (3NF)
- D) Boyce-Codd Normal Form (BCNF)
Show answer & explanation
Answer: B) Second Normal Form (2NF)
This is a partial dependency. To achieve Second Normal Form (2NF), no non-key attribute should depend on only a part of a composite primary key
Question 4
In an employee database, the EmployeeID is the primary key. The table includes a DepartmentID column and a DepartmentLocation column. The DepartmentLocation depends on the DepartmentID, which in turn depends on the EmployeeID. To achieve Third Normal Form (3NF), what must the database administrator eliminate?
- A) Atomic values
- B) Partial dependencies
- C) Transitive dependencies
- D) Composite primary keys
- D) .
Show answer & explanation
Answer: C) Transitive dependencies
In 3NF, a table must not have transitive dependencies. Non-key attributes (like DepartmentLocation) must not depend on other non-key attributes (like DepartmentI
Question 5
When designing a database, an IT team debates whether to fully normalize the data or leave it partially denormalized. What is the primary trade-off of high normalization?
- A) It increases data redundancy but speeds up all queries.
- B) It increases storage efficiency and data integrity, but complex queries may run slower due to multiple table joins.
- C) It entirely eliminates the need for a primary key.
- D) It allows the database to process unstructured data like videos.
Show answer & explanation
Answer: B) It increases storage efficiency and data integrity, but complex queries may run slower due to multiple table joins.
Normalization trade-offs include increased storage efficiency and simplified maintenance, but it can lead to slower query performance because fetching related data requires joining multiple tables . --------------------------------------------------------------------------------
Question 6
A nationwide supermarket chain processes tens of thousands of customer checkout transactions every hour. The system must quickly insert, update, and delete these short, atomic records in real-time. Which type of system is the supermarket using?
- A) Online Analytical Processing (OLAP)
- B) Online Transaction Processing (OLTP)
- C) Data Warehouse
- D) Data Mart
Show answer & explanation
Answer: B) Online Transaction Processing (OLTP)
Online Transaction Processing (OLTP) systems are optimized for handling a large volume of short, fast, and atomic real-time transactions, such as processing daily sales
Question 7
The executive board wants to analyze historical sales trends over the past ten years to make strategic business decisions. They query a system that contains integrated, time-variant, and non-volatile data. Which system are they querying?
- A) An operational ERP system
- B) A staging area
- C) A Data Warehouse
- D) An OLTP database
Show answer & explanation
Answer: C) A Data Warehouse
A data warehouse is subject-oriented, integrated, time-variant, and non-volatile, making it specifically designed for historical data analysis and business intelligence rather than daily transactions . --------------------------------------------------------------------------------
Question 8
During the creation of a data warehouse, raw data is pulled from a CRM system, an ERP system, and several flat files. Before this data is loaded into the main warehouse, it is temporarily stored in an intermediate zone to be scrubbed for duplicates. This zone is called the:
- A) Presentation Layer
- B) Staging Area
- C) Operational Environment
- D) Data Mart
Show answer & explanation
Answer: B) Staging Area
The Staging Area is used to temporarily store and clean data (e.g., removing duplicates) before loading it into the data warehouse, preventing corruption of the main warehouse during processing
Question 9
In the ETL process, standardizing date formats across different systems (e.g., converting DD/MM/YYYY to MM/DD/YYYY) and calculating new metrics from raw figures occurs during which phase?
- A) Extract
- B) Transform
- C) Load
- D) Presentation
Show answer & explanation
Answer: B) Transform
The "Transform" phase involves cleaning, standardizing, and enriching the raw data to ensure it is structured and consistent before loading
Question 10
To save processing time and bandwidth, a company updates its data warehouse every midnight by only bringing in the new transactions that occurred that specific day, rather than reloading the entire 5-year history. This method is known as:
- A) Full loading
- B) Real-time analytical processing
- C) Incremental loading
- D) Data purging
Show answer & explanation
Answer: C) Incremental loading
Incremental loading in ETL refers to adding only new or updated records to the data warehouse, which is much faster than doing a full data load . --------------------------------------------------------------------------------
Question 11
A data architect designs a schema for a new data warehouse. The design features a single central 'Fact Table' containing total sales figures, which is directly linked to denormalized 'Dimension Tables' (like Time, Product, and Customer) radiating outward. This describes a:
- A) Snowflake Schema
- B) Star Schema
- C) Galaxy Schema
- D) Hierarchical Schema
Show answer & explanation
Answer: B) Star Schema
The Star Schema is the most basic and widely used structure, featuring a central fact table surrounded by denormalized dimension tables, visually resembling a star
Question 12
To save storage space and ensure stricter data integrity, a database engineer modifies a Star Schema by breaking down the 'Product' dimension table into further sub-tables (e.g., separating 'Product Category' into its own table). This normalized branching structure is a:
- A) Star Schema
- B) Galaxy Schema
- C) Flat File Schema
- D) Snowflake Schema
Show answer & explanation
Answer: D) Snowflake Schema
The Snowflake Schema is an extension of the Star Schema where the dimension tables are highly normalized into multiple related sub-tables, resembling a snowflake's branching pattern
Question 13
An enterprise wants to integrate its 'Sales' process and its 'Inventory' process into one analytical model. The database designer creates an architecture with two separate fact tables that share the same dimension tables (like Time and Product). This complex structure is known as a:
- A) Star Schema
- B) Snowflake Schema
- C) Galaxy Schema (Fact Constellation)
- D) Data Mart
Show answer & explanation
Answer: C) Galaxy Schema (Fact Constellation)
A Galaxy Schema (or Fact Constellation) contains two or more fact tables that share common dimension tables, enabling integrated analysis across multiple business processes
Question 14
What is the primary trade-off between choosing a Star Schema versus a Snowflake Schema for a data warehouse?
- A) A Star schema saves storage space, while a Snowflake schema improves query speed.
- B) A Star schema prioritizes fast query speed at the cost of storage efficiency, while a Snowflake schema saves space but slows down queries due to multiple joins.
- C) A Star schema is used for real-time transactions, while a Snowflake schema is used for historical data.
- D) There is no trade-off; they function exactly the same.
Show answer & explanation
Answer: B) A Star schema prioritizes fast query speed at the cost of storage efficiency, while a Snowflake schema saves space but slows down queries due to multiple joins.
Star schemas prioritize query speed over storage efficiency because they are denormalized. Snowflake schemas save storage space through normalization, but the complex joins slow down query performance
Question 15
The Marketing Department of a large corporation finds the main enterprise data warehouse too massive and slow for their specific daily campaign reporting. To solve this, IT builds them a smaller, customized subset of the warehouse containing only marketing-related data. This subset is called a:
- A) Data Lake
- B) Data Mart
- C) Staging Area
- D) Relational Database
Show answer & explanation
Answer: B) Data Mart
A Data Mart is a tailored subset of a data warehouse designed to provide faster, focused access to specific business units or departments (like Marketing or HR)