WGU D427 DATA MANAGEMENT
APPLICATIONS COMPREHENSIVE
EXAM
1. Which normal form is achieved when all partial functional dependencies are removed and
every non-key attribute is fully functionally dependent on the primary key?
A. First Normal Form (1NF)
B. Third Normal Form (3NF)
C. Second Normal Form (2NF)
D. Boyce-Codd Normal Form (BCNF)
Answer: C
Conceptual Explanation: Second Normal Form (2NF) specifically addresses partial
functional dependencies, requiring that every non-key attribute depends on the entire
primary key, not just a portion of it.
2. In a SQL query, which clause is used to filter results based on an aggregate function value?
A. WHERE
B. GROUP BY
,C. ORDER BY
D. HAVING
Answer: D
Conceptual Explanation: The HAVING clause was added to SQL because the WHERE
keyword could not be used with aggregate functions.
3. What is the effect of using the ‘UNION’ operator instead of ‘UNION ALL’ on two result sets?
A. UNION combines all rows including duplicates.
B. UNION sorts the data but keeps all duplicates.
C. UNION only returns rows that exist in both sets.
D. UNION combines all rows and removes duplicates.
Answer: D
Conceptual Explanation: UNION removes duplicate rows from the final result set,
whereas UNION ALL includes all rows from both queries regardless of duplicates.
4. Which of the following describes a ‘Transitive Dependency’ in the context of database
normalization?
A. A primary key determines a non-key attribute.
B. A non-key attribute determines another non-key attribute.
C. A part of a composite key determines a non-key attribute.
, D. Two tables share the same primary key.
Answer: B
Conceptual Explanation: Transitive dependency occurs when a non-key attribute
depends on another non-key attribute rather than the primary key. Removing these is
required for 3NF.
5. When defining a Foreign Key in a CREATE TABLE statement, which clause ensures that if a
parent row is deleted, the child rows are also deleted?
A. ON DELETE RESTRICT
B. ON DELETE CASCADE
C. ON DELETE SET NULL
D. ON DELETE NO ACTION
Answer: B
Conceptual Explanation: ON DELETE CASCADE automatically deletes the rows in the
child table when the corresponding row in the parent table is deleted.
6. Which SQL statement is used to remove all records from a table without deleting the table
structure itself, usually faster than DELETE?
A. DROP TABLE
B. REMOVE TABLE
C. UPDATE TABLE
APPLICATIONS COMPREHENSIVE
EXAM
1. Which normal form is achieved when all partial functional dependencies are removed and
every non-key attribute is fully functionally dependent on the primary key?
A. First Normal Form (1NF)
B. Third Normal Form (3NF)
C. Second Normal Form (2NF)
D. Boyce-Codd Normal Form (BCNF)
Answer: C
Conceptual Explanation: Second Normal Form (2NF) specifically addresses partial
functional dependencies, requiring that every non-key attribute depends on the entire
primary key, not just a portion of it.
2. In a SQL query, which clause is used to filter results based on an aggregate function value?
A. WHERE
B. GROUP BY
,C. ORDER BY
D. HAVING
Answer: D
Conceptual Explanation: The HAVING clause was added to SQL because the WHERE
keyword could not be used with aggregate functions.
3. What is the effect of using the ‘UNION’ operator instead of ‘UNION ALL’ on two result sets?
A. UNION combines all rows including duplicates.
B. UNION sorts the data but keeps all duplicates.
C. UNION only returns rows that exist in both sets.
D. UNION combines all rows and removes duplicates.
Answer: D
Conceptual Explanation: UNION removes duplicate rows from the final result set,
whereas UNION ALL includes all rows from both queries regardless of duplicates.
4. Which of the following describes a ‘Transitive Dependency’ in the context of database
normalization?
A. A primary key determines a non-key attribute.
B. A non-key attribute determines another non-key attribute.
C. A part of a composite key determines a non-key attribute.
, D. Two tables share the same primary key.
Answer: B
Conceptual Explanation: Transitive dependency occurs when a non-key attribute
depends on another non-key attribute rather than the primary key. Removing these is
required for 3NF.
5. When defining a Foreign Key in a CREATE TABLE statement, which clause ensures that if a
parent row is deleted, the child rows are also deleted?
A. ON DELETE RESTRICT
B. ON DELETE CASCADE
C. ON DELETE SET NULL
D. ON DELETE NO ACTION
Answer: B
Conceptual Explanation: ON DELETE CASCADE automatically deletes the rows in the
child table when the corresponding row in the parent table is deleted.
6. Which SQL statement is used to remove all records from a table without deleting the table
structure itself, usually faster than DELETE?
A. DROP TABLE
B. REMOVE TABLE
C. UPDATE TABLE
