Data-Management-Foundations WGU Data Management – Foundations Exam Questions and Answers

TheMERGEstatement, also known asUPSERT, combinesINSERT, UPDATE, and DELETEoperations into asingle statementbased on a given condition. It is commonly used indata warehouses and large-scale databases.

Example Usage:

sql

MERGE INTO Employees AS Target

USING NewEmployees AS Source

ON Target.ID = Source.ID

WHEN MATCHED THEN

UPDATE SET Target.Salary = Source.Salary

WHEN NOT MATCHED THEN

INSERT (ID, Name, Salary) VALUES (Source.ID, Source.Name, Source.Salary);

If a match is found, the UPDATE clause modifies the existing record.

If no match is found, the INSERT clause adds a new record.

Why Other Options Are Incorrect:

Option A (INTO) (Incorrect):Used in INSERT INTO, butdoes not combine operations.

Option B (JOIN) (Incorrect):Used to combine rows from multiple tables, butnot for merging data.

Option D (DROP) (Incorrect):Deletes database objects liketables, views, and indexes, butdoes not merge data.

Thus, the correct answer isMERGE, as itcombines inserts, updates, and deletes into a single operation.

When a VIEW is created in SQL, users mayinsert or updatedata through that view. However, if a row is inserted or updated in such a way that itviolates the conditionof the VIEW’s WHERE clause, it can lead to inconsistencies.

Topreventsuch unwanted modifications,SQL provides the WITH CHECK OPTION clause.

How WITH CHECK OPTION Works:

Ensures thatany new data (INSERT/UPDATE) still fits within the defined constraints of the VIEW.

If a user tries to insert or update a row that would not appear in the VIEW,the operation isrejected.

Example:

sql

CREATE VIEW HighSalaryEmployees AS

SELECT * FROM Employees WHERE Salary > 50000

WITH CHECK OPTION;

Now, if someone attempts:

sql

INSERT INTO HighSalaryEmployees (ID, Name, Salary)

VALUES (101, 'Alice', 30000);

Thisfailsbecause 30000 does not satisfy Salary > 50000.

Why Other Options Are Incorrect:

Option B (Incorrect):JOIN VIEWS isnot a valid SQL clause.

Option C (Incorrect):MATERIALIZED VIEW refers tostored viewsin some databases, but it doesnotreject incorrect inserts/updates.

Option D (Incorrect):BASE TABLE refers to theoriginal table from which a VIEW is created.

Thus, the correct answer is WITH CHECK OPTION, whichensures that only valid data modifications occur.

TheALTER TABLEstatement is used to modify an existing database table structure. One common clause isCHANGE, which allows renaming a column and modifying its data type.

Example:

sql

ALTER TABLE Employees CHANGE COLUMN OldName NewName VARCHAR(50);

Option A (Incorrect):DELETE is used to removerows, not alter table structure.

Option B (Correct):CHANGE is avalid clausefor renaming and modifying columns in MySQL and some other databases.

Option C (Incorrect):STOP is not a valid SQL keyword for altering tables.

Option D (Incorrect):AGGREGATE refers to functions like SUM() and AVG(), not table alterations.

TheBETWEENkeyword in SQL is used tofilter values within a specified range. It is particularly useful for numeric and date-based queries.

Syntax of BETWEEN:

sql

SELECT * FROM Employees WHERE Salary BETWEEN 50000 AND 100000;

Retrievesall employees whose salary is between $50,000 and $100,000 (inclusive).

Why Other Options Are Incorrect:

Option A (LIKE) (Incorrect):Used forpattern matchingwith wildcards (%, _). Example:

sql

SELECT * FROM Customers WHERE Name LIKE 'A%';

Option B (IN) (Incorrect):Used tomatch a value in a specified set, butnot a range. Example:

sql

SELECT * FROM Employees WHERE Department IN ('HR', 'Finance', 'IT');

Option C (OR) (Incorrect):Used forlogical conditions, but doesnot check a range. Example:

sql

SELECT * FROM Products WHERE Price < 10 OR Price > 50;

Thus, the correct choice is BETWEEN for filtering values within arange.

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.

Avalid SELECT statementin SQL follows this basic syntax:

sql

SELECT column1, column2

FROM table_name

WHERE condition;

The correct optionDfollows this syntaxcorrectly.

Why Other Options Are Incorrect:

Option A (Incorrect):SQL does not usedouble quotes(") around column/table names unless explicitly required in some databases.

Option B (Incorrect):The WHERE clausemust appear after the FROM clause.

Option C (Incorrect):ALL isnota valid keyword in standard SQL queries.

Thus,Option Dfollows the correct SQL syntax.

Indatabase modeling, the relationship between asupertype and its subtypesis called anIsA relationship.

Example Usage:

AVehicle supertypemay haveCar and Truck subtypes.

Vehicle

├── Car

├── Truck

InER diagrams, this is represented as:

Vehicle (Supertype)

|

├── Car (Subtype)

├── Truck (Subtype)

SQL Table Implementation:

sql

CREATE TABLE Vehicle (

VehicleID INT PRIMARY KEY,

Make VARCHAR(50),

Model VARCHAR(50)

);

CREATE TABLE Car (

VehicleID INT PRIMARY KEY,

FOREIGN KEY (VehicleID) REFERENCES Vehicle(VehicleID),

EngineType VARCHAR(50)

);

CREATE TABLE Truck (

VehicleID INT PRIMARY KEY,

FOREIGN KEY (VehicleID) REFERENCES Vehicle(VehicleID),

CargoCapacity INT

);

This structurepreserves the IsA relationshipbetween Vehicle (supertype) and Car/Truck (subtypes).

Why Other Options Are Incorrect:

Option A (Strong entity) (Incorrect):Strong entitiesdo not rely on a supertype/subtype hierarchy.

Option C (Associative entity) (Incorrect):Used toresolve many-to-many relationships, not supertype/subtype relationships.

Option D (Weak entity) (Incorrect):Weak entitiesdepend on a strong entity, but supertype-subtype relations useinheritance(not dependency).

Thus, the correct answer isIsA relationship, as it describes theinheritance hierarchybetweensupertypes and subtypes.

TheHAVINGclause is used in SQL to filtergrouped resultsgenerated by the GROUP BY clause. Unlike WHERE, which filters individual rowsbeforegrouping, HAVING filtersafter aggregationhas been performed.

Example Usage:

sql

SELECT Department, AVG(Salary) AS AvgSalary

FROM Employees

GROUP BY Department

HAVING AVG(Salary) > 50000;

This query first groups employees by Department, calculates theaverage salary per department, and then filters onlythose departments where the average salary is greater than 50,000.

Why Other Options Are Incorrect:

Option A (REPLACE) (Incorrect):Used for string substitution, not filtering.

Option C (WITH) (Incorrect):Used inCommon Table Expressions (CTEs), not for filtering.

Option D (WHERE) (Incorrect):Used forrow-level filtering before aggregation, but itcannot be used on aggregate functions like SUM() or AVG().

Thus,HAVING is the correct answerfor filtering after grouping.

ATransaction ManagerensuresACID (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 thequery 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):Thestorage manageris responsible for translating queries into filesystem commands.

In MySQL, theBIGINTdata type is a64-bit integerthat requires8 bytes (64 bits) of storage. It is used to store large numerical values beyond the range of INT (4 bytes).

Option A (Incorrect):1 byte corresponds toTINYINT, which can store values from -128 to 127.

Option B (Incorrect):3 bytes is not a standard integer storage size in MySQL.

Option C (Incorrect):4 bytes corresponds toINT, which has a range of -2,147,483,648 to 2,147,483,647.

Option D (Correct):BIGINT takes8 bytesand supports a massive range of numbers from -2^63 to 2^63 -1.

Aunary relationship(also known as arecursive relationship) occurs when an entityrelates to itself.

Example Usage:

Employees and Managers:

sql

CREATE TABLE Employees (

EmpID INT PRIMARY KEY,

Name VARCHAR(50),

ManagerID INT,

FOREIGN KEY (ManagerID) REFERENCES Employees(EmpID)

);

Here, ManagerIDreferences another Employee→ aunary (self-referential) relationship.

Why Other Options Are Incorrect:

Option A (Modality) (Incorrect):Describesoptional vs. mandatoryrelationships, not self-referencing.

Option C (Cardinality) (Incorrect):Defines how many instances relate,not the type of relationship.

Option D (Binary) (Incorrect):Binary relationships involvetwo different entities, not self-referencing.

Thus, the correct answer isUnary, as it describesrelationships within the same entity type.

Thefirst stepin database analysis isidentifying entities, which are thereal-world objectsthat need to be represented in the database.

Example Usage:

In a school system, the mainentitiescould be:

Students, Courses, Instructors

Why Other Options Are Incorrect:

Option A (Determine cardinality) (Incorrect):Determining relationships comesafteridentifying entities.

Option C (Draw an ER diagram) (Incorrect):ER diagramsvisualizeentities but arenot the first step.

Option D (Implement attributes) (Incorrect):Attributes aredefined afterentities are identified.

Thus, the correct answer isIdentify entities, asentities form the foundation of the databasemodel.

Attributesin a databaserepresent the properties (names, values, or characteristics) of an entity.

Example Usage:

In aStudentstable, attributes might include:

StudentID (Primary Key), Name, Age, Major

Here,Name is an attributedescribing the entityStudent.

Why Other Options Are Incorrect:

Option A (Synonyms) (Incorrect):Synonyms in SQL allow different names for the same object but arenot attributes.

Option C (Repositories) (Incorrect):A repository stores data butdoes not define attributes.

Option D (Relationships) (Incorrect):Relationships defineconnections between entities, not their attributes.

Thus, the correct answer isNames, asattributes define entity properties.

In SQL, aprimary key is explicitly defined using the PRIMARY KEY keywordwhen creating a table.

Example Usage:

sql

CREATE TABLE Products (

ProductID INT PRIMARY KEY,

Name VARCHAR(100),

Price DECIMAL(10,2)

);

Here,PRIMARY KEY is the SQL keyword that designates ProductID as the primary key.

Why Other Options Are Incorrect:

Option A (Formula in SQL) (Incorrect):SQLdoes not use formulas to define primary keys.

Option C (Bold typeface) (Incorrect):SQL syntax does not rely on text formatting.

Option D (Diamond symbol) (Incorrect):ER diagramsmight use symbols, but SQLdoes not use diamonds to indicate keys.

Thus, the correct answer isSQL keyword, as primary keys are explicitly defined using PRIMARY KEY.

TheCASCADEconstraint ensures thatupdates or deletions in the primary key table automatically reflect in the foreign key 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) ON UPDATE CASCADE ON DELETE CASCADE

);

If DeptIDchangesin Departments, itautomatically updatesin Employees.

If a DeptID isdeleted, all employees in that departmentare also deleted.

Why Other Options Are Incorrect:

Option A (SET DEFAULT) (Incorrect):Sets foreign key values to adefaultvalue, rather than propagating changes.

Option B (SET NULL) (Incorrect):When the referenced key is deleted, dependent records areset to NULLinstead of being updated/deleted.

Option C (RESTRICT) (Incorrect):Prevents deletion of a referenced row if dependent foreign key rows exist.

Thus, the correct answer isCASCADE, as itpropagates primary key changes to dependent foreign keys.