WGU D427 Data Management Applications OA
Exam Actual Exam | Complete Questions Well
Elaborated Answers Verified – Pass Guaranteed -
A+ Graded
Database Fundamentals & Relational Model
Q1: When designing a database, why might a database administrator choose to use a
surrogate key rather than a natural key as the primary key?
A. A surrogate key guarantees that the data will always be meaningful to the user.
B. A surrogate key is a single column that exists solely for database identification, never
changing regardless of real-world data changes. [CORRECT]
C. A natural key is always composite, whereas a surrogate key is always single-column.
D. A surrogate key eliminates the need for any foreign key relationships in the schema.
Correct Answer: B
Rationale: The best answer is that a surrogate key is system-generated and has no
business meaning, which means it never needs to change if the business data (like a
Social Security Number or Email Address) changes, ensuring stability in your
relationships.
Q2: You are analyzing a table where every attribute is atomic (contains only single
values) and there are no repeating groups. However, you notice that some non-key
attributes depend on only a part of a composite primary key. Which normal form is this
table violating?
A. First Normal Form (1NF)
B. Second Normal Form (2NF) [CORRECT]
C. Third Normal Form (3NF)
D. Boyce-Codd Normal Form (BCNF)
Correct Answer: B
Rationale: This query is correct because 2NF requires that a table be in 1NF AND that
all non-key attributes be fully functionally dependent on the entire primary key; partial
dependency violates this rule.
Q3: In the context of the relational model, what is the primary purpose of a Foreign Key
constraint?
A. To uniquely identify each row within a table.
,B. To ensure that a column cannot contain a NULL value.
C. To enforce referential integrity by linking data in one table to valid primary keys in
another table. [CORRECT]
D. To automatically index a column to speed up query performance.
Correct Answer: C
Rationale: The best answer is enforcing referential integrity because a foreign key
ensures that you cannot have a value in the child table (like an Order) that doesn't exist
in the parent table (like a Customer), maintaining consistency between related data.
Q4: Which of the following statements best describes Transitive Dependency in the
context of database normalization?
A. A non-key attribute depends on another non-key attribute rather than directly on the
primary key. [CORRECT]
B. A composite key has attributes that depend on only part of the key.
C. A table contains repeating groups or multi-valued attributes.
D. A row in one table relates to multiple rows in another table.
Correct Answer: A
Rationale: This is correct because transitive dependency describes a situation where A
determines B, and B determines C, which violates 3NF; the goal is for non-key
attributes to depend only on the primary key.
Q5: A database designer is considering denormalizing a sales database. What is the
most common reason to intentionally violate normalization rules?
A. To increase data redundancy and save storage space.
B. To eliminate the need for foreign key constraints.
C. To improve read performance by reducing the number of complex JOINs required for
queries. [CORRECT]
D. To ensure that every table is in First Normal Form only.
Correct Answer: C
Rationale: The best answer is improving read performance because while normalization
reduces redundancy, it requires expensive JOIN operations; denormalization can speed
up reporting queries by storing data together, even if it means duplicating some
information.
Q6: If you have a table Employees where EmployeeID is the Primary Key, and Email is
unique and not null, what is the Email column considered to be?
A. A Surrogate Key
B. A Composite Key
C. A Candidate Key [CORRECT]
D. A Foreign Key
Correct Answer: C
, Rationale: This is correct because a candidate key is a column or set of columns that
uniquely identifies a row and could potentially serve as the primary key; since Email is
unique, it qualifies as a candidate key alongside the actual Primary Key.
Q7: When mapping an Entity-Relationship (ER) diagram to a relational schema, how do
you handle a Many-to-Many relationship between two entities, such as Students and
Courses?
A. Add a Foreign Key in the Students table pointing to the Courses table.
B. Add a Foreign Key in the Courses table pointing to the Students table.
C. Create a new junction (associative) table that contains Foreign Keys referencing both
original tables. [CORRECT]
D. Merge the two tables into a single large table.
Correct Answer: C
Rationale: The best answer is creating a junction table because relational databases
cannot directly represent many-to-many relationships; you need an intermediary table to
break the relationship into two one-to-many relationships.
Q8: Which data type would be most appropriate for a column intended to store a
standardized textual description of exactly 10 characters, such as a product code
"A-102-99"?
A. VARCHAR(10)
B. CHAR(10) [CORRECT]
C. TEXT
D. BLOB
Correct Answer: B
Rationale: This is correct because CHAR is a fixed-length string type; since the product
code is always exactly 10 characters, CHAR is efficient and stores the values exactly as
defined without the overhead of variable length calculations.
Q9: You need to store a date and time value that is time-zone aware and will change
when the time zone of the database server changes. Which data type is best suited for
this?
A. DATE
B. TIMESTAMP [CORRECT]
C. DATETIME
D. TIME
Correct Answer: B
Rationale: The best answer is TIMESTAMP because unlike DATETIME, a TIMESTAMP typically
converts the current time to UTC for storage and converts back to the current time zone
on retrieval, making it useful for tracking events across different time zones.
Exam Actual Exam | Complete Questions Well
Elaborated Answers Verified – Pass Guaranteed -
A+ Graded
Database Fundamentals & Relational Model
Q1: When designing a database, why might a database administrator choose to use a
surrogate key rather than a natural key as the primary key?
A. A surrogate key guarantees that the data will always be meaningful to the user.
B. A surrogate key is a single column that exists solely for database identification, never
changing regardless of real-world data changes. [CORRECT]
C. A natural key is always composite, whereas a surrogate key is always single-column.
D. A surrogate key eliminates the need for any foreign key relationships in the schema.
Correct Answer: B
Rationale: The best answer is that a surrogate key is system-generated and has no
business meaning, which means it never needs to change if the business data (like a
Social Security Number or Email Address) changes, ensuring stability in your
relationships.
Q2: You are analyzing a table where every attribute is atomic (contains only single
values) and there are no repeating groups. However, you notice that some non-key
attributes depend on only a part of a composite primary key. Which normal form is this
table violating?
A. First Normal Form (1NF)
B. Second Normal Form (2NF) [CORRECT]
C. Third Normal Form (3NF)
D. Boyce-Codd Normal Form (BCNF)
Correct Answer: B
Rationale: This query is correct because 2NF requires that a table be in 1NF AND that
all non-key attributes be fully functionally dependent on the entire primary key; partial
dependency violates this rule.
Q3: In the context of the relational model, what is the primary purpose of a Foreign Key
constraint?
A. To uniquely identify each row within a table.
,B. To ensure that a column cannot contain a NULL value.
C. To enforce referential integrity by linking data in one table to valid primary keys in
another table. [CORRECT]
D. To automatically index a column to speed up query performance.
Correct Answer: C
Rationale: The best answer is enforcing referential integrity because a foreign key
ensures that you cannot have a value in the child table (like an Order) that doesn't exist
in the parent table (like a Customer), maintaining consistency between related data.
Q4: Which of the following statements best describes Transitive Dependency in the
context of database normalization?
A. A non-key attribute depends on another non-key attribute rather than directly on the
primary key. [CORRECT]
B. A composite key has attributes that depend on only part of the key.
C. A table contains repeating groups or multi-valued attributes.
D. A row in one table relates to multiple rows in another table.
Correct Answer: A
Rationale: This is correct because transitive dependency describes a situation where A
determines B, and B determines C, which violates 3NF; the goal is for non-key
attributes to depend only on the primary key.
Q5: A database designer is considering denormalizing a sales database. What is the
most common reason to intentionally violate normalization rules?
A. To increase data redundancy and save storage space.
B. To eliminate the need for foreign key constraints.
C. To improve read performance by reducing the number of complex JOINs required for
queries. [CORRECT]
D. To ensure that every table is in First Normal Form only.
Correct Answer: C
Rationale: The best answer is improving read performance because while normalization
reduces redundancy, it requires expensive JOIN operations; denormalization can speed
up reporting queries by storing data together, even if it means duplicating some
information.
Q6: If you have a table Employees where EmployeeID is the Primary Key, and Email is
unique and not null, what is the Email column considered to be?
A. A Surrogate Key
B. A Composite Key
C. A Candidate Key [CORRECT]
D. A Foreign Key
Correct Answer: C
, Rationale: This is correct because a candidate key is a column or set of columns that
uniquely identifies a row and could potentially serve as the primary key; since Email is
unique, it qualifies as a candidate key alongside the actual Primary Key.
Q7: When mapping an Entity-Relationship (ER) diagram to a relational schema, how do
you handle a Many-to-Many relationship between two entities, such as Students and
Courses?
A. Add a Foreign Key in the Students table pointing to the Courses table.
B. Add a Foreign Key in the Courses table pointing to the Students table.
C. Create a new junction (associative) table that contains Foreign Keys referencing both
original tables. [CORRECT]
D. Merge the two tables into a single large table.
Correct Answer: C
Rationale: The best answer is creating a junction table because relational databases
cannot directly represent many-to-many relationships; you need an intermediary table to
break the relationship into two one-to-many relationships.
Q8: Which data type would be most appropriate for a column intended to store a
standardized textual description of exactly 10 characters, such as a product code
"A-102-99"?
A. VARCHAR(10)
B. CHAR(10) [CORRECT]
C. TEXT
D. BLOB
Correct Answer: B
Rationale: This is correct because CHAR is a fixed-length string type; since the product
code is always exactly 10 characters, CHAR is efficient and stores the values exactly as
defined without the overhead of variable length calculations.
Q9: You need to store a date and time value that is time-zone aware and will change
when the time zone of the database server changes. Which data type is best suited for
this?
A. DATE
B. TIMESTAMP [CORRECT]
C. DATETIME
D. TIME
Correct Answer: B
Rationale: The best answer is TIMESTAMP because unlike DATETIME, a TIMESTAMP typically
converts the current time to UTC for storage and converts back to the current time zone
on retrieval, making it useful for tracking events across different time zones.
