WGU Data Management - Foundations Exam Questions

The logical design phase in database development focuses on structuring data efficiently to eliminate redundancy and ensure integrity. The final step in logical design is to apply normalization (normal forms) to optimize the database schema.

Steps in Logical Database Design:

Discover entities Identify real-world objects (e.g., Customers, Orders).

Determine cardinality Define relationships between entities (one-to-one, one-to-many).

Analyze data requirements Determine the attributes each entity needs.

Apply normal forms Eliminate redundancy and improve data consistency.

Example Usage:

After identifying entities like Students and Courses, applying 3rd Normal Form (3NF) ensures that data is organized without redundancy.

Why Other Options Are Incorrect:

Option A (Analyze data requirements) (Incorrect): Done earlier to define attributes.

Option C (Determine cardinality) (Incorrect): Done before normalization to establish relationships.

Option D (Discover entities) (Incorrect): Done at the beginning of database design.

Thus, the correct answer is Apply a normal form, as normalization is the last step in logical design.

In database design, an entity is classified as strong or weak based on whether it has a primary key that uniquely identifies its records.

Differences Between Strong and Weak Entities:

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,

CustomerID INT

);

CREATE TABLE OrderDetails (

OrderDetailID INT,

OrderID INT,

ProductID INT,

PRIMARY KEY (OrderDetailID, OrderID),

FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)

);

Orders is a strong entity (has OrderID as its own primary key).

OrderDetails is a weak entity (depends on OrderID for uniqueness).

Why Other Options Are Incorrect:

Option A (Association) (Incorrect): Associations describe relationships but do not define strong/weak entities.

Option C (Foreign key) (Incorrect): Weak entities depend on foreign keys, but primary keys define their status.

Option D (Cardinality) (Incorrect): Cardinality defines relationship constraints but does not differentiate entity types.

Thus, the correct answer is Primary key, as it is the defining characteristic between strong and weak entities.

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.