D427 WGU DATABASE MANAGEMENT APPLICATIONS COMPREHENSIVE QUESTIONS WITH VERIFIED ANSWERS NEWEST UPDATE

D427 WGU DATABASE MANAGEMENT APPLICATIONS COMPREHENSIVE QUESTIONS
WITH VERIFIED ANSWERS NEWEST UPDATE



Question 1
In the address fragment "San Francisco, CA 94110", how many attributes are represented?
A) 1
B) 2
C) 3
D) 4
E) 5
Correct Answer: C) 3
Rationale: The fragment contains three distinct attributes: the City (San Francisco), the
State (CA), and the Zip Code (94110). In database design, these should be stored in
separate columns to ensure atomicity and allow for better sorting and filtering.

Question 2
The Package table has the following columns: Weight (decimal), Description (variable length
string), LastChangedDate (date), and TrackingNumber (integer). Which column should be
designated as the primary key?
A) Weight
B) Description
C) LastChangedDate
D) TrackingNumber
E) A composite of Weight and Description
Correct Answer: D) TrackingNumber
Rationale: A primary key must be unique and non-null. Tracking numbers are unique
identifiers assigned to specific packages, whereas weights, descriptions, and dates are likely
to have duplicate values across different records.

Question 3
Which SQL data type is most appropriate for storing a value like "2022-01-10 14:22:12" as a
temporal value without loss of information?
A) DATE
B) TIME
C) DATETIME
D) TIMESTAMP
E) YEAR
Correct Answer: C) DATETIME
Rationale: The DATE type only stores the year, month, and day. The TIME type only stores
hours, minutes, and seconds. DATETIME (and TIMESTAMP) stores both the date and the
time components, ensuring no information is lost from the provided string.

, 2



Question 4
Which of the following SQL commands is classified as Data Definition Language (DDL)?
A) SELECT
B) INSERT
C) UPDATE
D) ALTER
E) DELETE
Correct Answer: D) ALTER
Rationale: DDL commands (CREATE, ALTER, DROP) are used to define or modify the
structure of database objects like tables and indexes. DML commands (SELECT, INSERT,
UPDATE, DELETE) are used to manage the data within those structures.
Question 5
How does a database engine handle an UPDATE or DELETE statement that violates a
RESTRICT referential integrity constraint?
A) The engine sets the offending value to NULL.
B) The engine allows the change and updates the foreign key automatically.
C) The engine rejects the statement and an error is returned.
D) The engine deletes the parent row and all associated child rows.
E) The engine sets the value to a pre-defined database default.
Correct Answer: C) The engine rejects the statement and an error is returned.
Rationale: The RESTRICT constraint prevents the modification or deletion of a parent key
if a corresponding foreign key exists in a child table. Unlike CASCADE, which propagates
the change, RESTRICT stops the action to maintain integrity.

Question 6
Which of the following is a specific requirement or restriction when using a materialized view?
A) It cannot be used in a SELECT statement.
B) It does not store data on the disk.
C) The underlying data must be periodically refreshed.
D) It can only reference one table.
E) It must be deleted before the base table can be updated.
Correct Answer: C) The underlying data must be periodically refreshed.
Rationale: A materialized view stores the result of a query physically. Because it is a
"snapshot," it can become stale if the base tables change. Therefore, it must be refreshed
(manually or on a schedule) to stay current.

Question 7
In a JOIN operation between Table A and Table B, which rows are included in the result set for
an INNER JOIN?
A) All rows from Table A regardless of matches.

, 3



B) All rows from Table B regardless of matches.
C) Only rows where the join condition is met in both tables.
D) All rows from both tables, even without matches.
E) Only rows that do not have a match in the other table.
Correct Answer: C) Only rows where the join condition is met in both tables.
Rationale: An INNER JOIN returns only the intersection of the two tables. If a row in Table
A does not have a matching value in the specified column of Table B, that row is excluded
from the final result set.

Question 8
In the fragment "Portland, OR 97212", how many attributes are present?
A) 1
B) 2
C) 3
D) 4
E) 0
Correct Answer: C) 3
Rationale: This fragment represents a city (Portland), a state abbreviation (OR), and a
postal code (97212). In a normalized database, these are three separate attributes.

Question 9
Which data type should be used for a column that will store numbers with fractional components,
such as "19.99"?
A) INTEGER
B) VARCHAR
C) DECIMAL
D) BIGINT
E) BINARY
Correct Answer: C) DECIMAL
Rationale: The DECIMAL (or NUMERIC) type is used for exact fixed-point numbers where
precision is required (like currency). INTEGER and BIGINT only store whole numbers.

Question 10
A database contains a Patient table (parent) and an Exam table (child). The Exam table is defined
with "FOREIGN KEY (patient_id) REFERENCES Patient(patient_id) ON DELETE
CASCADE". What happens if a patient is deleted?
A) The deletion is blocked by the database.
B) The patient_id in the Exam table is set to NULL.
) All exams associated with that patient_id are automatically deleted.
D) The exams remain but are orphaned.
E) The engine asks the user to manually delete the exams first.