Free WGU Data-Management-Foundations Practice Exam with Questions & Answers | Set: 2

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.

Aprimary keyuniquely identifies each row in a table. When a primary key consists ofonly one field, it is called aSimple Primary Key.

Types of Primary Keys:

Simple Primary Key (Correct Answer):

Contains onlyone column.

Example:

sql

CREATE TABLE Students (

StudentID INT PRIMARY KEY,

Name VARCHAR(50)

);

Composite Primary Key:

Usesmultiple columnsto ensure uniqueness.

Example:

sql

CREATE TABLE Orders (

OrderID INT,

ProductID INT,

PRIMARY KEY (OrderID, ProductID)

);

Surrogate Primary Key:

A system-generatedunique identifier(e.g., UUID or AUTO_INCREMENT).

Why Other Options Are Incorrect:

Option B (Duplicate) (Incorrect):A primary keymust be unique, so itcannot be duplicate.

Option C (Numeric) (Incorrect):While primary keyscan be numeric, they can also be alphanumeric (VARCHAR).

Option D (Composite) (Incorrect):Acompositekey consists ofmultiple fields, whereas a simple key isa single field.

Thus, the correct answer isSimple, since aone-field primary keyis a simple primary key.

TheORDER BYclause in SQL is used tosort query resultsinascending (ASC) or descending (DESC) order.

Example Usage:

sql

SELECT Name, Salary FROM Employees ORDER BY Salary DESC;

This retrieves all employees, sorted bysalary in descending order.

Why Other Options Are Incorrect:

Option A (BETWEEN) (Incorrect):Used for filtering ranges butdoes not order results.

Option B (DISTINCT) (Incorrect):Removes duplicate rows butdoes not control order.

Option D (LIKE) (Incorrect):Used forpattern matching, not sorting.

Thus,ORDER BY is the correct choicefor defining the sequence of query results.

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.

TheWHEREclause is used inSQL SELECT statementstolimitthe number of rows that match a specific condition. It helps filter data based on given criteria before retrieving the results.

Example Usage:

sql

SELECT *

FROM Employees

WHERE Salary > 50000;

This querylimitsthe result set to employees whose salary isgreater than 50,000.

Why Other Options Are Incorrect:

Option A (FROM) (Incorrect):Specifies the table from which data is retrieved butdoes not limit results.

Option B (DROP) (Incorrect):Used fordeleting tables, databases, or views, not filtering rows.

Option C (INTO) (Incorrect):Used in statements like INSERT INTO or SELECT INTO, whichdo not filter results.

Thus,WHERE is the correct keywordfor applying alimiter in a SELECT statement.

Binary searchis an algorithm that finds a search value byrepeatedly dividing the search space into two halves. It is commonly used inindexed searches.

Example Usage in Databases:

B-Trees and B+ Trees(used in database indexing) applybinary searchto navigate the index efficiently.

If searching for ID = 50 in asorted list of IDs, binary search:

Splits the list into two halves.

Checks the middle value.

Eliminates half of the dataset.

Repeats until the value is found.

Why Other Options Are Incorrect:

Option A (Table scan) (Incorrect):Readsevery row,much slowerthan binary search.

Option C (Index scan) (Incorrect):Uses indexes but does not necessarily apply binary search.

Option D (Fan-out) (Incorrect):Describesbranching in B-Trees, not searching.

Thus, the correct answer isBinary search, as it repeatedlysplits the indexin two.

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.

Atangible entityis a real-world object that can bemeasured and storedin a database.

Example Usage:

In an inventory system,tangible entitiesinclude:

Products, Orders, Customers

Why Other Options Are Incorrect:

Option A (Logical entity) (Incorrect):Exists logically butmay not have a physical presence(e.g.,views, categories).

Option C (Virtual entity) (Incorrect):Existsonly in queries or reports, not stored as real data.

Option D (Conceptual entity) (Incorrect):Abstract idea used indesign modeling, not astored entity.

Thus, the correct answer isTangible entity, as it representsmeasurable, real-world objects.