Pass the WGU Courses and Certificates Data-Management-Foundations Questions and answers with ExamsMirror

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.

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.

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.

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.

Thecorrect syntaxfor inserting a new row into a table follows this structure:

Standard SQL INSERT Syntax:

sql

INSERT INTO TableName (Column1, Column2, ...)

VALUES (Value1, Value2, ...);

For this scenario:

sql

INSERT INTO Employee (Name) VALUES ('John Doe');

Why Other Options Are Incorrect:

Option A (Incorrect):Uses incorrect syntax { ... }, which isnot valid SQL syntax.

Option C (Incorrect):Does not specify the column name, whichcauses an error.

Option D (Incorrect):Misses theINTOkeyword, which is required in standard SQL.

Thus, the correct syntax isOption B, ensuring aproperly formatted insert statement.

In databases, primary keys are oftenset to auto-incrementso that new rows automatically receive unique values. However,one common error is manually inserting a value into an auto-incremented primary key column, whichoverrides the automatic numberingand may cause conflicts.

Example of Auto-Increment Setup:

sql

CREATE TABLE Users (

UserID INT AUTO_INCREMENT PRIMARY KEY,

Username VARCHAR(50)

);

Incorrect Insert (Error-Prone Approach):

sql

INSERT INTO Users (UserID, Username) VALUES (100, 'Alice');

Thismanually overrides the auto-increment, which can lead toduplicate key errors.

Correct Insert (Avoiding Errors):

sql

INSERT INTO Users (Username) VALUES ('Alice');

Thedatabase assigns UserID automatically, preventing conflicts.

Why Other Options Are Incorrect:

Option B (Failing to set a numeric value) (Incorrect):The databaseautomatically assignsvalues when AUTO_INCREMENT is used.

Option C (Designating multiple primary keys) (Incorrect):Whileincorrect, most databases will prevent this at creation time.

Option D (Forgetting to specify which is the auto-increment column) (Incorrect):If AUTO_INCREMENT is set, the database handles numbering automatically.

Thus, the most common error isInserting a value and overriding auto-increment, which can causeduplicate key errors and data inconsistencies.

AFull Join (FULL OUTER JOIN)selectsall records from both tables, filling in NULL values where there is no match. This ensures that no data is lost from either table.

Example Usage:

sql

SELECT Employees.Name, Departments.DepartmentName

FROM Employees

FULL OUTER JOIN Departments ON Employees.DeptID = Departments.ID;

This query retrievesall employees and all departments, even if an employeehas no assigned departmentor a departmenthas no employees.

Types of Joins:

FULL OUTER JOIN (Correct Answer)→ Includesall rows from both tables, filling missing values with NULL.

LEFT JOIN (Incorrect)→ Includesall rows from the left tableandmatching rows from the right table.

RIGHT JOIN (Incorrect)→ Includesall rows from the right tableandmatching rows from the left table.

CROSS JOIN (Incorrect)→ Produces aCartesian product(each row from one table is combined with every row from another table).

Thus, the correct answer isFULL JOIN, whichensures that all rows from both tables appear in the result.

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.

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.

This query performs ajoin operationwhere records from the Make table and Model table are combined based on the condition Make.ModelID = Model.ID. This conditiontests for equality, which is the definition of anEQUIJOIN.

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):

Usescomparison 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 withitselfusing 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):

ProducesCartesian 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 anequality condition (=) to join two tables, it is anEQUIJOIN.