1Z0-006 Data Modeling 3 — Questions and Answers
Question 1: Which of the following is an example of a multivalued attribute in an ERD?
- Employee's date of birth
- Employee's salary
- Employee's phone numbers (can have several) (Correct answer)
- Employee's department ID
Correct answer: Employee's phone numbers (can have several)
A multivalued attribute can hold more than one value for a single entity instance, such as an employee having multiple phone numbers.
Question 2: In Third Normal Form (3NF), which type of dependency must be eliminated?
- Partial dependency on a composite key
- Transitive dependency of a non-key attribute on another non-key attribute (Correct answer)
- Dependency of the primary key on a foreign key
- Multi-valued dependency across unrelated attributes
Correct answer: Transitive dependency of a non-key attribute on another non-key attribute
3NF requires that no non-key attribute depends on another non-key attribute (transitive dependency); all non-key attributes must depend directly on the primary key.
Question 3: What distinguishes a physical data model from a logical data model?
- A physical model includes specific DBMS data types, indexes, and storage details (Correct answer)
- A logical model includes SQL DDL statements ready for execution
- A physical model omits primary and foreign key constraints
- A logical model is only used for NoSQL databases
Correct answer: A physical model includes specific DBMS data types, indexes, and storage details
A physical data model extends the logical model with DBMS-specific details such as data types, indexes, tablespaces, and partitioning strategies.
Question 4: An EMPLOYEE entity has a 'Years_of_Service' attribute that is always calculated from 'Hire_Date'. What type of attribute is 'Years_of_Service'?
- Stored attribute
- Composite attribute
- Derived attribute (Correct answer)
- Multivalued attribute
Correct answer: Derived attribute
A derived attribute is one whose value is computed from other stored attributes and is typically shown with a dashed oval in ERDs.
Question 5: Which constraint ensures that a foreign key value in a child table must match an existing primary key value in the parent table?
- Entity integrity constraint
- Domain constraint
- Referential integrity constraint (Correct answer)
- Check constraint
Correct answer: Referential integrity constraint
Referential integrity ensures that a foreign key either matches a primary key in the referenced table or is NULL, preventing orphaned records.
Question 6: In conceptual data modeling, what is the primary goal?
- Define physical storage structures for the database
- Capture business concepts and rules independent of any technology (Correct answer)
- Write SQL CREATE TABLE statements
- Optimize query execution plans
Correct answer: Capture business concepts and rules independent of any technology
A conceptual model captures high-level business entities, relationships, and rules without concern for how data will be physically stored or which DBMS will be used.
Question 7: What does cardinality specify in a data model?
- The data type and length of each attribute
- The number of instances of one entity that can relate to instances of another entity (Correct answer)
- The order in which tables are created in the schema
- The number of indexes required for a table
Correct answer: The number of instances of one entity that can relate to instances of another entity
Cardinality defines the numerical relationship between entity occurrences, such as one-to-one, one-to-many, or many-to-many.
1Z0-006 Data Modeling 3
Which of the following is an example of a multivalued attribute in an ERD?