CDP Data Architecture and Modeling 1 — Questions and Answers
Question 1: Which data modeling technique uses entities, attributes, and relationships to represent business concepts?
- Physical Data Modeling
- Entity-Relationship (ER) Modeling (Correct answer)
- Dimensional Modeling
- Object-Oriented Modeling
Correct answer: Entity-Relationship (ER) Modeling
Entity-Relationship modeling represents data as entities (business objects), attributes (properties), and relationships (associations) between entities.
Question 2: What is the purpose of normalization in relational database design?
- Improve query performance by adding redundancy
- Eliminate data redundancy and ensure data integrity by organizing data into related tables (Correct answer)
- Increase storage capacity
- Create indexes for faster retrieval
Correct answer: Eliminate data redundancy and ensure data integrity by organizing data into related tables
Normalization organizes data into related tables to eliminate redundancy, reduce anomalies, and ensure data integrity in relational databases.
Question 3: In a star schema, what does the 'fact table' contain?
- Descriptive attributes about business entities
- Measurable, quantitative business metrics and foreign keys to dimension tables (Correct answer)
- Configuration settings for the data warehouse
- User access permissions
Correct answer: Measurable, quantitative business metrics and foreign keys to dimension tables
The fact table in a star schema contains quantitative business metrics (measures) and foreign keys linking to surrounding dimension tables.
Question 4: What is denormalization in database design?
- The process of converting ER diagrams to physical tables
- Intentionally introducing redundancy into a database to improve read performance (Correct answer)
- Removing unused tables from a schema
- Converting a relational model to a document model
Correct answer: Intentionally introducing redundancy into a database to improve read performance
Denormalization intentionally adds redundant data to a normalized schema to reduce joins and improve query read performance, often used in data warehouses.
Question 5: Which normal form requires that every non-key attribute be fully dependent on the entire primary key?
- First Normal Form (1NF)
- Second Normal Form (2NF) (Correct answer)
- Third Normal Form (3NF)
- Boyce-Codd Normal Form (BCNF)
Correct answer: Second Normal Form (2NF)
Second Normal Form (2NF) requires that a table be in 1NF and that every non-key attribute be fully functionally dependent on the entire composite primary key.
Question 6: What is a surrogate key in database design?
- A natural business key like a Social Security Number
- A system-generated, meaningless unique identifier assigned to each row in a table (Correct answer)
- A foreign key referencing another table
- A composite key made of multiple columns
Correct answer: A system-generated, meaningless unique identifier assigned to each row in a table
A surrogate key is a system-generated, meaningless numeric identifier (like an auto-increment ID) used as a primary key independent of business data.
CDP Data Architecture and Modeling 1
Which data modeling technique uses entities, attributes, and relationships to represent business concepts?