WGU D545 OA | Objective Assessment | 100 Questions and Answers | Update | 100% Correct.

WGU D545 OA | Objective Assessment | 100 Questions
and Answers | Update | 100% Correct.




WGU D597 Task 1: Database Design and Management Solution Analysis
Practice Questions 1-50


Part 1: Data Model Analysis (Questions 1-15)
Question 1
In the provided database schema for WGU D597 Task 1, which table correctly
handles the many-to-many relationship between Orders and Products?
A. Orders table
B. Products table
C. OrderDetails table
D. Categories table
- answer-; C. OrderDetails table. The OrderDetails table serves as the associative
entity (junction table) that resolves the many-to-many relationship between
Orders and Products. It contains a composite primary key (OrderID, ProductID)
and foreign keys referencing both parent tables .


Question 2
What data type issue should be addressed when migrating the Categories table
from SQL Server to Azure SQL Database?
A. INT to BIGINT conversion
B. TEXT to NVARCHAR(MAX) conversion

,C. BIT to TINYINT conversion
D. DECIMAL to FLOAT conversion
- answer-; B. TEXT to NVARCHAR(MAX) conversion. The TEXT data type is
deprecated in modern SQL Server versions and Azure SQL Database. It should be
converted to NVARCHAR(MAX) for better compatibility and performance .


Question 3
Which of the following represents a valid justification for maintaining duplicate
shipping address information in the Orders table?
A. It violates 3NF and should be removed
B. It preserves historical order data that should not change when customer
address updates
C. It improves write performance during order updates
D. It eliminates the need for foreign key constraints
- answer-; B. It preserves historical order data that should not change when
customer address updates. This is a valid business requirement. The shipping
address at the time of order should be preserved even if the customer later
updates their address, making this a deliberate denormalization for historical
accuracy .


Question 4
In a properly normalized database, what is the primary characteristic of a table in
Third Normal Form (3NF)?
A. All attributes are atomic with no repeating groups
B. Every non-key attribute is fully functionally dependent on the primary key
C. No transitive dependencies exist where non-key attributes depend on other
non-key attributes
D. All determinants are candidate keys
- answer-; C. No transitive dependencies exist where non-key attributes depend
on other non-key attributes. 1NF requires atomic values, 2NF requires full
functional dependency on the primary key, and 3NF eliminates transitive
dependencies .

,Question 5
What is the purpose of the FOREIGN KEY constraint in the Orders table
referencing Customers?
A. To create an index on CustomerID
B. To ensure referential integrity between Orders and Customers
C. To automatically delete orders when a customer is deleted
D. To improve query performance
- answer-; B. To ensure referential integrity between Orders and
Customers. Foreign key constraints maintain data integrity by ensuring that every
CustomerID in the Orders table exists in the Customers table .


Question 6
Which column in the Products table would be most appropriate for a BIT data
type?
A. UnitPrice
B. UnitsInStock
C. Discontinued
D. ReorderLevel
- answer-; C. Discontinued. The BIT data type stores boolean values (0 or 1) and
is ideal for flag columns like Discontinued, which indicates whether a product is
active or discontinued .


Question 7
What normalization form is achieved when all attributes are atomic and there are
no repeating groups?
A. First Normal Form (1NF)
B. Second Normal Form (2NF)
C. Third Normal Form (3NF)
D. Boyce-Codd Normal Form (BCNF)

, - answer-; A. First Normal Form (1NF). 1NF requires that all columns contain
atomic (indivisible) values and that there are no repeating groups of columns .


Question 8
In the OrderDetails table, what is the correct primary key configuration?
A. OrderID as primary key
B. ProductID as primary key
C. Composite primary key (OrderID, ProductID)
D. Surrogate key OrderDetailID
- answer-; C. Composite primary key (OrderID, ProductID). The combination of
OrderID and ProductID uniquely identifies each line item in an order, making a
composite key the natural choice .


Question 9
Which of the following is NOT a component of the provided database schema?
A. Employees table
B. Categories table
C. Products table
D. OrderDetails table
- answer-; A. Employees table. While referenced in the Orders table
(EmployeeID), the Employees table itself is not defined in the provided schema.
This would need to be addressed for referential integrity .


Question 10
What is the primary purpose of the Categories table in the schema?
A. To store product inventory levels
B. To categorize products for reporting and analysis
C. To track supplier information
D. To manage customer segments