Free Data Warehousing on AWS: Data Modeling Questions and Answers — Questions and Answers

Question 1: What are the atomic facts about entities that we need to know?

Entity relationship
Classes
Attributes (Correct answer)
Entity integrity

Correct answer: Attributes

In data modeling, attributes are the specific properties or characteristics that describe an entity. They represent the atomic facts about an entity, such as a customer's name, address, or product price. Understanding attributes is fundamental to defining the granular data points that will be stored and managed within a database or data warehouse.

Question 2: A data model is a picture of data.

FALSE (Correct answer)
TRUE

Correct answer: FALSE

A data model is more than just a 'picture'; it is a conceptual representation that defines the structure, relationships, and constraints of data within an information system. While it can be visually represented, its core purpose is to provide a blueprint for how data is organized, stored, and accessed. It outlines the logical and physical design rather than merely being an image.

Question 3: Data modeling aids in avoiding storing unnecessary data.

FALSE
TRUE (Correct answer)

Correct answer: TRUE

Data modeling helps in precisely defining the data requirements and relationships, which in turn prevents the storage of redundant or irrelevant information. By clearly identifying entities, attributes, and their connections, data models promote efficient data organization and reduce storage waste. This leads to cleaner, more manageable, and cost-effective data systems.

Question 4: Instead than focusing on a data requirement, data modelers concentrate on the operations that are carried out on the data.

FALSE (Correct answer)
TRUE

Correct answer: FALSE

Data modelers primarily focus on understanding and defining the data requirements of an organization, not solely on the operations performed on the data. While operations are considered for performance, the core task is to accurately represent the business entities, their attributes, and relationships. This ensures the data model effectively captures and stores the necessary information to support various business processes and analytical needs.

Question 5: The data model's information is used to define

RDBMS used to store the data
Primary and foreign keys (Correct answer)
Data type and length of the column
Requirements

Correct answer: Primary and foreign keys

A data model explicitly defines the relationships between entities, which are enforced in a relational database through primary and foreign keys. Primary keys uniquely identify records within a table, while foreign keys establish links between tables by referencing primary keys in other tables. This structural definition is crucial for maintaining data integrity and enabling efficient querying across related datasets.

Question 6: Data abstraction's physical level addresses

How data is going to store (Correct answer)
Part of the data in the table
The relationship between the tables
What data is going to store

Correct answer: How data is going to store

The physical level of data abstraction describes the actual storage of data on physical devices, including details like file organization, indexing, and storage structures. It focuses on the 'how' data is stored, such as block sizes, record placement, and access paths. This level is concerned with the low-level implementation details that optimize performance and storage efficiency.

Question 7: An OLTP database is made to handle a business's daily requirements.

FALSE
TRUE (Correct answer)

Correct answer: TRUE

OLTP (Online Transaction Processing) databases are specifically designed to manage and process day-to-day operational transactions quickly and efficiently. They are optimized for high volumes of concurrent read, insert, update, and delete operations, making them ideal for applications like e-commerce, banking, and point-of-sale systems that require real-time data updates.

Question 8: Another name for online analytical processing is

Concurrent control processing
Transaction control processing
Online transactional processing
Decision support processing (Correct answer)

Correct answer: Decision support processing

Online Analytical Processing (OLAP) is primarily used for complex queries, data analysis, and reporting to support business intelligence and strategic decision-making. It involves analyzing historical data from multiple perspectives to uncover trends, patterns, and insights. Therefore, OLAP is often referred to as Decision Support Processing because its main goal is to aid in informed decision-making.

Question 9: A dimensional model is used to store recent data.

FALSE (Correct answer)
TRUE

Correct answer: FALSE

Dimensional models, commonly used in data warehousing, are designed to store historical and aggregated data for analytical purposes, not primarily recent or real-time operational data. They are optimized for querying large datasets to support business intelligence and reporting, often containing data that has been transformed and loaded from OLTP systems over time.

Question 10: A high amount of detail is stored in OLTP.

FALSE
TRUE (Correct answer)

Correct answer: TRUE

OLTP (Online Transaction Processing) systems are designed to capture and store fine-grained, atomic transactional data at the highest level of detail. Each individual transaction, such as a purchase or a booking, is recorded with all its specific attributes. This detailed information is crucial for operational efficiency and ensuring data integrity for daily business operations.

Question 11: An illustration of PRODUCT database table design Product Name Product Feature Description Product Identifier(PK) Product Category Name Product SubCategory Name Location Identifier (PK): Country Name, State Name, City Name, Ate Timestamp Location Identifier, Product Identifier, and Sales Organization Identifier (PK) Average sales in dollars Date and timestamp Identifier for the organization (PK) Name of the corporate office, Region, Brach, Employer, Date, and Timestamp

None of the above
Unable to interpret the input data
Dimensional model (Correct answer)
Online transaction processing model

Correct answer: Dimensional model

The description provided, with distinct entities like Product, Location, and Sales Organization acting as potential dimensions, and 'Average sales in dollars' as a clear measure, strongly suggests a dimensional model. Dimensional models typically separate descriptive attributes (dimensions) from quantitative facts (measures) to optimize for analytical queries. The presence of multiple identifiers (PKs) referencing different entities further supports this structure, where a fact table would link these dimensions and store measures.

Question 12: You are hired by one customer to create a database for airline transactions. The database must store the fine-grained information pertaining to the user's timestamped updates and ticket bookings. Which database architecture do you favor?

None of the above
It can be either a Dimensional model or an Online transaction processing model.
Online transaction processing model (Correct answer)
Dimensional model

Correct answer: Online transaction processing model

For storing fine-grained, timestamped updates and ticket bookings, an Online Transaction Processing (OLTP) model is most suitable. OLTP systems are optimized for high-volume, concurrent insert, update, and delete operations, which are characteristic of real-time transactional data like airline bookings. They prioritize data integrity and quick processing of individual transactions, unlike dimensional models which are designed for analytical queries on historical data.

Question 13: There isn't a lot of attribute-specific information in the logical Data model.

FALSE (Correct answer)
TRUE

Correct answer: FALSE

The Logical Data Model (LDM) provides a detailed representation of data entities, their attributes, and relationships, independent of specific database technology. It includes comprehensive attribute-specific information such as data types, lengths, and constraints. This level of detail is crucial for translating business requirements into a structured database design, making the statement false.

Question 14: The Conceptual Data Model (CDM) is the ancestor of the Logical Data Model (LDM) (CDM).

FALSE
TRUE (Correct answer)

Correct answer: TRUE

The Conceptual Data Model (CDM) is the highest level of abstraction, focusing on business concepts and relationships without technical details. The Logical Data Model (LDM) then refines the CDM by adding more detail, including attributes, primary keys, and foreign keys, while still being independent of a specific database system. Thus, the CDM serves as the foundation or 'ancestor' for the LDM, guiding its development.

Question 15: The records in a dimensional table are specific to that dimension; no measures are kept in these tables.

FALSE
TRUE (Correct answer)

Correct answer: TRUE

In a dimensional model, dimensional tables (e.g., Product, Customer, Time) store descriptive attributes related to a specific dimension. They contain detailed information about that entity, such as product names, customer addresses, or date components. Measures, which are quantitative facts like sales amount or quantity, are stored separately in fact tables, linked to the dimensional tables via foreign keys.

Question 16: modeling data The process of building data models is known as data modeling.

A verbal description of the data needed
Physical elements of the information system
A graphic representation of data contained in an information system (Correct answer)
An orderly arrangement of data elements

Correct answer: A graphic representation of data contained in an information system

Data modeling is the process of creating a visual representation of an organization's data, including entities, their attributes, and the relationships between them. While it involves an orderly arrangement and is based on data requirements, its core output is a graphical blueprint. This visual model helps in understanding, designing, and communicating the structure of a database or data warehouse effectively.

Free Data Warehousing on AWS: Data Modeling Questions and Answers

What are the atomic facts about entities that we need to know?