DMC Conceptual and Logical Data Design 1 — Questions and Answers
Question 1: Which of the following best distinguishes a logical data model from a conceptual data model?
- A logical model includes physical storage parameters
- A logical model adds attributes, primary keys, and data types without specifying a DBMS (Correct answer)
- A logical model is less detailed than a conceptual model
- A logical model only documents relationships, not entities
Correct answer: A logical model adds attributes, primary keys, and data types without specifying a DBMS
A logical data model refines the conceptual model by adding detailed attributes, primary and foreign keys, and data types while remaining DBMS-independent.
Question 2: In logical data modeling, what is normalization primarily used to achieve?
- Improve query execution speed
- Eliminate data redundancy and update anomalies (Correct answer)
- Add indexes to frequently queried columns
- Partition large tables across multiple servers
Correct answer: Eliminate data redundancy and update anomalies
Normalization is the process of organizing data to eliminate redundancy and dependency anomalies, ensuring each fact is stored in one place.
Question 3: What is a functional dependency in the context of normalization?
- A dependency between two database servers
- A situation where the value of one attribute determines the value of another (Correct answer)
- A referential integrity constraint between tables
- A trigger that enforces a business rule
Correct answer: A situation where the value of one attribute determines the value of another
A functional dependency (A → B) means that knowing the value of attribute A uniquely determines the value of attribute B.
Question 4: Which normal form eliminates partial dependencies on a composite 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)
2NF requires that every non-key attribute is fully functionally dependent on the entire primary key, eliminating partial dependencies.
Question 5: Third Normal Form (3NF) eliminates which type of dependency?
- Multi-valued dependencies
- Partial dependencies
- Transitive dependencies (Correct answer)
- Recursive dependencies
Correct answer: Transitive dependencies
3NF removes transitive dependencies, ensuring that non-key attributes depend only on the primary key and not on other non-key attributes.
Question 6: What makes Boyce-Codd Normal Form (BCNF) stricter than 3NF?
- BCNF also eliminates multi-valued dependencies
- BCNF requires every determinant to be a candidate key, including in cases 3NF misses (Correct answer)
- BCNF eliminates all repeating groups
- BCNF requires surrogate keys instead of natural keys
Correct answer: BCNF requires every determinant to be a candidate key, including in cases 3NF misses
BCNF strengthens 3NF by requiring that every determinant in the relation is a candidate key, handling edge cases where 3NF still allows anomalies.
DMC Conceptual and Logical Data Design 1
Which of the following best distinguishes a logical data model from a conceptual data model?