WGU Data Management - Foundations Exam Questions

In database design, attributes of an entity become columns in a relational table.

Example Usage:

For an Employee entity, attributes might include:

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

Name VARCHAR(50),

Salary DECIMAL(10,2),

DepartmentID INT

);

Each attribute (e.g., Name, Salary) becomes a column in the table.

Why Other Options Are Incorrect:

Option A (Modality) (Incorrect): Describes optional vs. mandatory relationships, not table structure.

Option B (Uniqueness) (Incorrect): Ensures distinct values but is not a column property.

Option D (Non-null values) (Incorrect): Ensures that columns must contain data but does not define attributes.

Thus, the correct answer is Attribute, as attributes of entities become table columns.

The UNION keyword in SQL is used to combine the results of two or more SELECT queries into a single result set while removing duplicate rows.

Example:

sql

SELECT Name FROM Employees

UNION

SELECT Name FROM Managers;

Option A (Correct): UNION combines results from multiple queries into one set, removing duplicates.

Option B (Incorrect): MERGE is not a valid SQL keyword for combining result sets (it is used in some database systems for data merging).

Option C (Incorrect): INTEGRATE is not a SQL keyword.

Option D (Incorrect): CONSOLIDATE is not an SQL keyword.

This query performs a join operation where records from the Make table and Model table are combined based on the condition Make.ModelID = Model.ID. This condition tests for equality, which is the definition of an EQUIJOIN.

Types of Joins in SQL:

EQUIJOIN (Correct Answer):

Uses an equality operator (=) to match rows between tables.

Equivalent to an INNER JOIN ON condition.

Example:

sql

SELECT *

FROM Employees

JOIN Departments ON Employees.DeptID = Departments.ID;

NON-EQUIJOIN (Incorrect):

Uses comparison operators other than = (e.g., <, >, BETWEEN).

Example:

sql

SELECT *

FROM Employees e

JOIN Salaries s ON e.Salary > s.MedianSalary;

SELF JOIN (Incorrect):

A table is joined with itself using table aliases.

Example:

sql

SELECT e1.Name, e2.Name AS Manager

FROM Employees e1

JOIN Employees e2 ON e1.ManagerID = e2.ID;

CROSS JOIN (Incorrect):

Produces Cartesian product (each row from Table A combines with every row from Table B).

Example:

sql

SELECT *

FROM Employees

CROSS JOIN Departments;

Thus, since our given query uses an equality condition (=) to join two tables, it is an EQUIJOIN.

The given diagram represents a unary relationship (also called a recursive relationship), which occurs when an entity is related to itself. In this case, salespersons back each other up, meaning a salesperson is associated with another salesperson from the same entity.

Key Observations from the Diagram:

Single Entity (Salesperson)

The table contains only one entity type, Salesperson, which has attributes such as Salesperson Number, Name, Commission, Percentage, and Year of Hire.

Self-Referencing Relationship (Backs-up and Backed-up by)

The diagram shows that a salesperson can back up another salesperson.

This means that the relationship exists within the same entity rather than between two different entities.

Cardinality (One-to-One Relationship in a Unary Form)

The notation ( |---| ) in the ER diagram indicates a one-to-one recursive relationship.

One salesperson can back up only one other salesperson, and each salesperson is backed up by only one.

A Transaction Manager ensures ACID (Atomicity, Consistency, Isolation, Durability) properties in database transactions. It manages concurrent transactions, ensuring no conflicts occur and logs modifications to support recovery mechanisms.

Option A (Incorrect): Query optimization is managed by the query processor, not the transaction manager.

Option B (Incorrect): The transaction manager is a component of the database architecture but is not composed of the entire system (query processor, storage manager, etc.).

Option C (Correct): The transaction manager logs transactions like INSERT, UPDATE, and DELETE, ensuring consistency and recoverability.

Option D (Incorrect): The storage manager is responsible for translating queries into file system commands.