1Z0-006 Entity-Relationship Modeling 5 — Questions and Answers
Question 1: When mapping a many-to-many (M:N) binary relationship to relational tables, the correct approach is to:
- Add a foreign key to one of the two entity tables
- Create a new junction table with foreign keys referencing both entity tables (Correct answer)
- Merge both entities into one large table
- Add a foreign key to both entity tables pointing at each other
Correct answer: Create a new junction table with foreign keys referencing both entity tables
An M:N relationship requires a junction (cross-reference) table whose composite primary key consists of the primary keys of both participating entities.
Question 2: Which of the following best describes 'cardinality ratio' in an ER relationship?
- The number of attributes an entity can have
- The maximum number of relationship instances in which an entity instance can participate (Correct answer)
- The minimum number of entities required to form a relationship
- The total number of entity types in a database schema
Correct answer: The maximum number of relationship instances in which an entity instance can participate
Cardinality ratio (1:1, 1:N, M:N) specifies the maximum number of relationship instances an entity can participate in on each side.
Question 3: What is the purpose of a 'surrogate key' in a relational table derived from an ER model?
- To serve as a natural business identifier such as Social Security Number
- To provide a system-generated, artificial primary key when no suitable natural key exists (Correct answer)
- To enforce referential integrity between two tables
- To store derived values computed from other columns
Correct answer: To provide a system-generated, artificial primary key when no suitable natural key exists
A surrogate key is a system-generated identifier (often an auto-incremented integer) used when a natural primary key is unavailable or impractical.
Question 4: In an ER diagram, a multivalued attribute is depicted as:
- A single ellipse with a solid border
- A double ellipse (Correct answer)
- A dashed ellipse
- A rectangle connected to an ellipse
Correct answer: A double ellipse
A double ellipse represents a multivalued attribute, indicating that an entity instance can have multiple values for that attribute.
Question 5: When a multivalued attribute is converted to a relational schema, the standard practice is to:
- Store all values in a single column separated by commas
- Create a separate table for the multivalued attribute with a foreign key referencing the original entity (Correct answer)
- Ignore the multivalued nature and store only one value
- Add multiple columns (one per possible value) to the original entity table
Correct answer: Create a separate table for the multivalued attribute with a foreign key referencing the original entity
To maintain 1NF, a multivalued attribute is placed in its own relation (table) that references the owning entity via a foreign key.
Question 6: Which scenario best illustrates a 'total participation' constraint?
- Some employees may or may not work on a project
- Every employee must be assigned to at least one department (Correct answer)
- A department can exist without any employees
- A customer can optionally place an order
Correct answer: Every employee must be assigned to at least one department
Total participation means every entity instance must participate in the relationship, so every employee is required to belong to a department.
Question 7: An ER model is typically created during which phase of the database development lifecycle?
- Physical design phase
- Implementation (coding) phase
- Conceptual design phase (Correct answer)
- Testing and maintenance phase
Correct answer: Conceptual design phase
ER models are produced during the conceptual design phase, before physical or logical schema details are determined.
1Z0-006 Entity-Relationship Modeling 5
When mapping a many-to-many (M:N) binary relationship to relational tables, the correct approach is to: