1Z0-006 Data Modeling 4 — Questions and Answers
Question 1: A table stores OrderID, ProductID, ProductName, and Quantity. OrderID+ProductID form the primary key. ProductName depends only on ProductID. Which normal form is violated?
- 1NF, because of repeating groups
- 2NF, because ProductName is partially dependent on the composite key (Correct answer)
- 3NF, because of a transitive dependency
- BCNF, because a non-key attribute determines a key attribute
Correct answer: 2NF, because ProductName is partially dependent on the composite key
ProductName depends only on ProductID (part of the composite key), creating a partial dependency that violates 2NF.
Question 2: Which ERD notation uses 'crows foot' symbols combined with a vertical bar and circle to show minimum and maximum cardinality?
- Chen notation
- UML class diagram notation
- IE (Information Engineering) / Crow's Foot notation (Correct answer)
- IDEF1X notation
Correct answer: IE (Information Engineering) / Crow's Foot notation
Information Engineering (Crow's Foot) notation uses symbols at each end of a relationship line to indicate minimum (optional/mandatory) and maximum (one/many) cardinality.
Question 3: What is a self-referencing (recursive) relationship in an ERD?
- A relationship where an entity relates to itself (Correct answer)
- A relationship between two identical entities in different schemas
- A table that contains its own primary key as a default value
- A relationship that loops back through multiple intermediate tables
Correct answer: A relationship where an entity relates to itself
A recursive relationship occurs when an entity has a relationship with itself, such as an EMPLOYEE who manages other EMPLOYEEs.
Question 4: In Oracle SQL Developer Data Modeler, what is the difference between a 'logical' and a 'relational' model?
- A logical model uses entity/attribute terminology; a relational model maps these to tables/columns with keys (Correct answer)
- A logical model includes physical storage; a relational model is DBMS-agnostic
- A logical model can only represent one-to-one relationships
- A relational model is identical to a conceptual model
Correct answer: A logical model uses entity/attribute terminology; a relational model maps these to tables/columns with keys
The logical model uses abstract entity and attribute concepts, while the relational model transforms these into tables, columns, primary keys, and foreign keys for implementation.
Question 5: Which of the following best describes entity integrity?
- Every foreign key must reference a valid primary key
- Every table must have at least one column with a UNIQUE constraint
- No primary key column can contain a NULL value (Correct answer)
- All attribute values must fall within a defined domain
Correct answer: No primary key column can contain a NULL value
Entity integrity requires that primary key columns never contain NULL, ensuring every row can be uniquely identified.
Question 6: What is an associative entity (also called an intersection entity)?
- An entity that stores aggregate summary data
- An entity created to represent a many-to-many relationship, containing foreign keys to both related entities (Correct answer)
- An entity that only has derived attributes
- An entity shared between two separate databases
Correct answer: An entity created to represent a many-to-many relationship, containing foreign keys to both related entities
An associative entity resolves a many-to-many relationship by acting as a junction, holding foreign keys to both parent entities and often adding its own attributes.
Question 7: Which statement about First Normal Form (1NF) is correct?
- All non-key attributes must depend on the whole primary key
- Each column must contain atomic (indivisible) values and each row must be unique (Correct answer)
- Transitive dependencies between non-key attributes must be removed
- Every determinant must be a candidate key
Correct answer: Each column must contain atomic (indivisible) values and each row must be unique
1NF requires that each column holds a single, atomic value and that there are no repeating groups, ensuring the table structure is flat and relational.
1Z0-006 Data Modeling 4
A table stores OrderID, ProductID, ProductName, and Quantity.
OrderID+ProductID form the primary key.
ProductName depends only on ProductID.
Which normal form is violated?