WGU Data Management - Foundations Exam Questions

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.

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.

A foreign key is a column (or a set of columns) that establishes a relationship between two tables by referencing the primary key of another table.

Example Usage:

sql

CREATE TABLE Departments (

DeptID INT PRIMARY KEY,

DeptName VARCHAR(50)

);

CREATE TABLE Employees (

EmpID INT PRIMARY KEY,

Name VARCHAR(50),

DeptID INT,

FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)

);

DeptID in Employees is a foreign key, referencing DeptID in Departments.

Ensures referential integrity DeptID in Employees must exist in Departments.

Why Other Options Are Incorrect:

Option A (Super key) (Incorrect): A super key is any set of columns that uniquely identifies a row, but it does not reference another table.

Option B (Simple key) (Incorrect): A simple key is a single-column primary key, not a reference to another table.

Option C (Composite key) (Incorrect): A composite key consists of multiple columns but does not necessarily reference another table.

Thus, the correct answer is Foreign key, as it establishes a connection between two tables.