WGU Pre-Assessment: Data Management -
Applications (FJO1) (PFJO) 2025/2026 |
Western Governors
Exam
This document provides comprehensive preparation for the Western Governors
University Data Management - Applications Pre-Assessment Examination,
featuring complete questions with verified answers for the 2025/2026 academic cycle. It
covers database design principles, SQL query development, data normalization,
transaction management, and security protocols aligned with WGU's performance
assessment requirements and competency-based standards. This essential tool offers
authentic pre-assessment simulation and systematic content review to ensure readiness
for the objective assessment and success in your WGU course progression.
1. Which SQL command permanently removes both table rows and
structure?
Answer: DROP TABLE table_name;
Big Rationale:
• DROP removes the table definition and all its data from the
database.
• It cannot be rolled back unless the DBMS supports Flashback.
• DELETE removes rows but keeps the structure.
• TRUNCATE removes rows but still keeps the table.
2. A foreign key must always reference which type of column?
,Answer: A primary key or unique key in the parent table.
Big Rationale:
Foreign keys enforce referential integrity. They must reference a column
that guarantees uniqueness, so no child row refers to a non-existent
parent. PKs and UNIQUE constraints meet that requirement.
3. What type of join returns all rows from the left table and matching
rows from the right?
Answer: LEFT OUTER JOIN
Big Rationale:
• A left join returns all rows from the left table even if no match exists
in the right.
• Non-matching right-side columns become NULL.
• Used for reports that must include all items from one side (e.g., all
employees even if no sales).
4. Which normal form removes partial dependencies on part of a
composite primary key?
Answer: Second Normal Form (2NF)
Big Rationale:
• A table is in 2NF if it's in 1NF and every non-key attribute depends on
the whole primary key.
• Applies only when the primary key is composite.
• This eliminates repeating groups dependent on only one portion of
the multi-column key.
,5. What is the main purpose of indexing in a relational database?
Answer: To improve query performance by speeding up data retrieval.
Big Rationale:
Indexes function like a book’s index, allowing the DB to jump directly to
rows instead of scanning the entire table.
Tradeoff: faster reads, but slower writes (INSERT/UPDATE/DELETE).
6. In an ERD, what does cardinality represent?
Answer: The number of relationships between two entities (1:1, 1:M,
M:N).
Big Rationale:
• Cardinality indicates how many instances of one entity relate to
another.
• Guides primary/foreign key selection and table creation.
• Example: A customer (1) places many (M) orders.
7. Which SQL command is used to modify an existing table’s structure
by adding a column?
Answer:
ALTER TABLE table_name
ADD column_name datatype;
Big Rationale:
ALTER allows changes to schema elements like adding columns,
modifying types, or adding constraints without dropping the table.
, 8. Which ACID property ensures that a transaction’s effects survive
system failures?
Answer: Durability
Big Rationale:
After a transaction commits, its results are permanently written to disk or
log files. Even if the system crashes, committed data must not be lost.
9. A data warehouse is optimized for which type of workload?
Answer: Analytical processing (OLAP)
Big Rationale:
• Data warehouses store large historical datasets for reporting and
analytics.
• Workloads involve scans, aggregations, and business intelligence
queries, not transactional writes.
10. What is the function of a surrogate key?
Answer: An artificial key used to uniquely identify a row when no
natural key exists or is suitable.
Big Rationale:
• Typically auto-increment or GUID.
• Prevents issues with changing natural keys (e.g., email or phone
number).
• Simplifies joins and indexing.
11. Which SQL function returns the number of rows in a table?
Applications (FJO1) (PFJO) 2025/2026 |
Western Governors
Exam
This document provides comprehensive preparation for the Western Governors
University Data Management - Applications Pre-Assessment Examination,
featuring complete questions with verified answers for the 2025/2026 academic cycle. It
covers database design principles, SQL query development, data normalization,
transaction management, and security protocols aligned with WGU's performance
assessment requirements and competency-based standards. This essential tool offers
authentic pre-assessment simulation and systematic content review to ensure readiness
for the objective assessment and success in your WGU course progression.
1. Which SQL command permanently removes both table rows and
structure?
Answer: DROP TABLE table_name;
Big Rationale:
• DROP removes the table definition and all its data from the
database.
• It cannot be rolled back unless the DBMS supports Flashback.
• DELETE removes rows but keeps the structure.
• TRUNCATE removes rows but still keeps the table.
2. A foreign key must always reference which type of column?
,Answer: A primary key or unique key in the parent table.
Big Rationale:
Foreign keys enforce referential integrity. They must reference a column
that guarantees uniqueness, so no child row refers to a non-existent
parent. PKs and UNIQUE constraints meet that requirement.
3. What type of join returns all rows from the left table and matching
rows from the right?
Answer: LEFT OUTER JOIN
Big Rationale:
• A left join returns all rows from the left table even if no match exists
in the right.
• Non-matching right-side columns become NULL.
• Used for reports that must include all items from one side (e.g., all
employees even if no sales).
4. Which normal form removes partial dependencies on part of a
composite primary key?
Answer: Second Normal Form (2NF)
Big Rationale:
• A table is in 2NF if it's in 1NF and every non-key attribute depends on
the whole primary key.
• Applies only when the primary key is composite.
• This eliminates repeating groups dependent on only one portion of
the multi-column key.
,5. What is the main purpose of indexing in a relational database?
Answer: To improve query performance by speeding up data retrieval.
Big Rationale:
Indexes function like a book’s index, allowing the DB to jump directly to
rows instead of scanning the entire table.
Tradeoff: faster reads, but slower writes (INSERT/UPDATE/DELETE).
6. In an ERD, what does cardinality represent?
Answer: The number of relationships between two entities (1:1, 1:M,
M:N).
Big Rationale:
• Cardinality indicates how many instances of one entity relate to
another.
• Guides primary/foreign key selection and table creation.
• Example: A customer (1) places many (M) orders.
7. Which SQL command is used to modify an existing table’s structure
by adding a column?
Answer:
ALTER TABLE table_name
ADD column_name datatype;
Big Rationale:
ALTER allows changes to schema elements like adding columns,
modifying types, or adding constraints without dropping the table.
, 8. Which ACID property ensures that a transaction’s effects survive
system failures?
Answer: Durability
Big Rationale:
After a transaction commits, its results are permanently written to disk or
log files. Even if the system crashes, committed data must not be lost.
9. A data warehouse is optimized for which type of workload?
Answer: Analytical processing (OLAP)
Big Rationale:
• Data warehouses store large historical datasets for reporting and
analytics.
• Workloads involve scans, aggregations, and business intelligence
queries, not transactional writes.
10. What is the function of a surrogate key?
Answer: An artificial key used to uniquely identify a row when no
natural key exists or is suitable.
Big Rationale:
• Typically auto-increment or GUID.
• Prevents issues with changing natural keys (e.g., email or phone
number).
• Simplifies joins and indexing.
11. Which SQL function returns the number of rows in a table?
