1Z0-006 Data Modeling 5 — Questions and Answers
Question 1: In data modeling terminology, what is a 'candidate key'?
- Any foreign key that could be promoted to a primary key
- A minimal set of attributes that can uniquely identify every row in a table (Correct answer)
- A key used only during the design phase before a primary key is chosen
- A composite key made up of two or more foreign keys
Correct answer: A minimal set of attributes that can uniquely identify every row in a table
A candidate key is any minimal set of attributes that uniquely identifies each row; one candidate key is chosen as the primary key, and the rest become alternate keys.
Question 2: What does 'participation constraint' (also called 'existence dependency') define in an ERD?
- The maximum number of attributes an entity can have
- Whether every entity instance must participate in a relationship (total) or may optionally participate (partial) (Correct answer)
- The order in which relationships are created in the schema
- The data type constraints on foreign key columns
Correct answer: Whether every entity instance must participate in a relationship (total) or may optionally participate (partial)
Participation constraints specify whether all (total/mandatory) or only some (partial/optional) entity instances must be involved in a relationship.
Question 3: A PRODUCT table has columns: ProductID, CategoryID, CategoryName, Price. CategoryName depends on CategoryID, not directly on ProductID. Which normal form violation does this represent?
- 1NF violation due to non-atomic values
- 2NF violation due to a partial dependency
- 3NF violation due to a transitive dependency (Correct answer)
- BCNF violation due to a non-superkey determinant
Correct answer: 3NF violation due to a transitive dependency
CategoryName transitively depends on ProductID through CategoryID (ProductID → CategoryID → CategoryName), which violates 3NF.
Question 4: Which modeling concept does an ERD's 'IS-A' (generalization/specialization) hierarchy represent?
- A recursive relationship where an entity manages itself
- An inheritance relationship where subtypes share attributes of a supertype (Correct answer)
- A many-to-many relationship resolved through an associative entity
- A derived attribute calculated from subtype entities
Correct answer: An inheritance relationship where subtypes share attributes of a supertype
An IS-A hierarchy represents generalization/specialization, where subtypes (e.g., HOURLY_EMPLOYEE) inherit common attributes from a supertype (e.g., EMPLOYEE) and add their own specific attributes.
Question 5: What is a 'natural key' in database design?
- A system-generated sequential integer with no business meaning
- An identifier derived from existing real-world data attributes that naturally uniquely identifies a row (Correct answer)
- A key that is automatically indexed by the database engine
- A composite key formed from two surrogate keys
Correct answer: An identifier derived from existing real-world data attributes that naturally uniquely identifies a row
A natural key uses real-world data (such as a Social Security Number or ISBN) that inherently and uniquely identifies an entity.
Question 6: When should you consider denormalization in a database design?
- To eliminate all redundancy for data integrity purposes
- To improve read query performance by intentionally introducing controlled redundancy (Correct answer)
- To satisfy 3NF requirements in an OLTP system
- To reduce storage by removing duplicate rows
Correct answer: To improve read query performance by intentionally introducing controlled redundancy
Denormalization intentionally introduces redundancy to reduce costly joins, improving read performance in data warehouse or reporting scenarios at the cost of some update complexity.
Question 7: In Boyce-Codd Normal Form (BCNF), what must be true of every functional dependency X → Y?
- Y must be a primary key attribute
- X must be a superkey of the table (Correct answer)
- X and Y must both be non-key attributes
- Y must not depend on any other non-key attribute
Correct answer: X must be a superkey of the table
BCNF requires that for every non-trivial functional dependency X → Y, X must be a superkey, making BCNF stricter than 3NF.
1Z0-006 Data Modeling 5
In data modeling terminology, what is a 'candidate key'?