WGU Data-Management-Foundations WGU Data Management – Foundations Exam Exam Practice Test

Non-relational databases(also calledNoSQL databases) are designed for handlingbig dataandunstructured dataefficiently. They are optimized forhorizontal scaling, making them ideal forlarge-scale distributed systems.

Option A (Correct):Non-relational databases areoptimized for big data, handling massive volumes of data across distributed architectures.

Option B (Incorrect):NoSQL databases donotuse SQL as their primary query language. They often use JSON-based queries (e.g., MongoDB).

Option C (Incorrect):Transaction-heavy applications requireACID compliance, which relational databases (SQL) handle better than NoSQL databases.

Option D (Incorrect):NoSQL databases usedocument, key-value, graph, or column-family storage models, nottables, columns, and rowslike relational databases.

Areflexive relationship(also called aunary relationship) occurs when anentity is related to itself.

Example Usage:

A screenshot of a computer AI-generated content may be incorrect.

CREATE TABLE Employees (

EmpID INT PRIMARY KEY,

Name VARCHAR(50),

ManagerID INT,

FOREIGN KEY (ManagerID) REFERENCES Employees(EmpID)

);

ManagerIDrefers to another employeewithin the same table.

Why Other Options Are Incorrect:

Option A (Repository) (Incorrect):A data storage structure, not a relationship type.

Option B (Relationship instance) (Incorrect):Describesa specific connection between entities, not a reflexive relationship.

Option D (Attribute) (Incorrect):Attributes storecharacteristics, butdo not define relationships.

Thus, the correct answer isReflexive relationship, as it relates an entity to itself.

Thelogical design phasein database development focuses onstructuring data efficientlyto eliminate redundancy and ensure integrity. Thefinal step in logical designis toapply 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 likeStudentsandCourses, applying3rd Normal Form (3NF)ensures that data isorganized without redundancy.

Why Other Options Are Incorrect:

Option A (Analyze data requirements) (Incorrect):Doneearlierto define attributes.

Option C (Determine cardinality) (Incorrect):Donebeforenormalization to establish relationships.

Option D (Discover entities) (Incorrect):Done at thebeginningof database design.

Thus, the correct answer isApply a normal form, as normalization is thelast step in logicaldesign.

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.

Anattribute typerefers to asingle, specific valuewithin a table, such as Salary, Age, or Price.

Example Usage:

A screenshot of a computer AI-generated content may be incorrect.

CREATE TABLE Employees (

EmpID INT PRIMARY KEY,

Name VARCHAR(50),

Salary DECIMAL(10,2)

);

Salary is anattribute typewith individual values for each employee.

Why Other Options Are Incorrect:

Option A (Glossary) (Incorrect):Refers todocumentation, not database values.

Option B (Entity type) (Incorrect):Representsa class of objects(e.g., Employees), not individual values.

Option D (Relationship) (Incorrect):Definesconnections between entities, not attributes.

Thus, the correct answer isAttribute type, as it represents anindividual data value.

Asimple primary keyconsists ofonly one columnthat uniquely identifies each row in a table.

Example Usage:

sql

CREATE TABLE Students (

StudentID INT PRIMARY KEY,

Name VARCHAR(50)

);

StudentIDis asimple primary keybecause it consists of only one field.

Why Other Options Are Incorrect:

Option B (Partition) (Incorrect):Refers topartitioned tables, which divide data for performance reasons butare not related to primary keys.

Option C (Stable) (Incorrect):This is not a recognized term in database keys.

Option D (Meaningless) (Incorrect):Primary keys are oftenmeaningless (e.g., auto-incremented IDs), but this isnot a term used to describe their structure.

Thus, the correct answer isSimple, as asingle-field primary keyis referred to as asimple 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.

TheLTRIM()function in SQL removesleading spaces(spaces at the beginning of a string) while keeping spaces at the end.

Example Usage:

sql

SELECT LTRIM(' Hello World') AS TrimmedText;

Output:

bash

'Hello World'

Why Other Options Are Incorrect:

Option B (LEFT) (Incorrect):Used for extracting a portion of a string. Example:

sql

SELECT LEFT('Hello World', 5); -- Output: 'Hello'

Option C (TRIM) (Incorrect):Removesboth leading and trailing spaces, not just leading ones. Example:

sql

SELECT TRIM(' Hello World '); -- Output: 'Hello World'

Option D (REPLACE) (Incorrect):Replaces occurrences of one substring with another but doesnot specifically remove spaces.

Thus, the correct answer isLTRIM(), which removes onlyleading spaces.

When performing aJOIN operationin MySQL, the ON clause specifies thejoining condition, defining which columns from both tables should be matched.

Example:

sql

SELECT Employees.Name, Departments.DepartmentName

FROM Employees

JOIN Departments ON Employees.DepartmentID = Departments.ID;

Option A (Incorrect):AS is used foraliasing tables and columns, not for specifying join conditions.

Option B (Incorrect):JOIN defines the type of join (INNER JOIN, LEFT JOIN, etc.), butdoes not specify the columns.

Option C (Correct):The ON clause is used tospecify the join conditionbetween two tables.

Option D (Incorrect):AND is used in filtering conditions, not for joining tables.

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.

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.

Anaggregate functionperforms acalculation over multiple rowsand returns asingle value. Examples includeSUM(), AVG(), MAX(), MIN(), and COUNT()in SQL.

Option A (Correct):Aggregate functions compute values over aset of rows, like summingtotal sales or averaging grades.

Option B (Incorrect):Selecting rows that appear in one table but not another is done usingset operations (EXCEPT or MINUS in SQL).

Option C (Incorrect):Eliminating columns is done using thePROJECToperation orSELECT with specific columns.

Option D (Incorrect):Combining rows from two tables refers to aJOIN operation, not aggregation.

Aggregate functionsperform calculationson a set of values and return asingle result.MAX()is one such function, returning thelargest value in a column.

Common Aggregate Functions:

A screenshot of a computer AI-generated content may be incorrect.

Example Usage:

sql

SELECT MAX(Salary) FROM Employees;

Retrieves thehighest salaryin the Employees table.

Why Other Options Are Incorrect:

Option B (TRIM) (Incorrect):Removes spaces from strings butis not an aggregate function.

Option C (ABS) (Incorrect):Returns theabsolute value of a numberbut doesnot aggregate multiple rows.

Option D (DESC) (Incorrect):Used in ORDER BY forsorting in descending order,not for aggregation.

Thus, the correct answer isMAX(), as it is atrue aggregate function.

Thesecond step in implementing relationshipsis definingone-to-one (1:1) relationshipsbetween entities.

Example Usage:

Example of a 1:1 relationship:

sql

CREATE TABLE Employees (

EmpID INT PRIMARY KEY,

Name VARCHAR(50)

);

CREATE TABLE EmployeeDetails (

EmpID INT PRIMARY KEY,

Address VARCHAR(255),

FOREIGN KEY (EmpID) REFERENCES Employees(EmpID)

);

Here, eachemployee has exactly one detail record, creating a1:1 relationship.

Why Other Options Are Incorrect:

Option A (Implement weak entities) (Incorrect):Weak entities rely on aforeign keyand are implementedlater.

Option C (Implement subtype entities) (Incorrect):Subtypes arespecial casesandnot implemented in the second step.

Option D (Specify cascade) (Incorrect):Cascade rules (ON DELETE, ON UPDATE)are defined duringforeign key implementation, not in the second step.

Thus, the correct answer isImplement one-one relationships, as it is thenext logical stepafter defining entities.

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.

MongoDB is aNoSQL (non-relational) databasethat isopen-sourceand supportsdocument-oriented storage. It allows for flexible schema design and is optimized for big data applications.

Option A (Correct):MongoDB is aNoSQL databasewith an open-sourceAGPLlicense, making it both free to use and non-relational.

Option B (Incorrect):SQL Serveris arelational database(RDBMS) developed by Microsoft and isnot open-source.

Option C (Incorrect):MySQLisopen-sourcebut is arelational database(RDBMS), not a NoSQL system.

Option D (Incorrect):Oracle Databaseisrelationalandproprietary(not open-source).

In aone-to-many (1:M) relationship, theforeign keyis placed in thetable on the "many" sideto establish the relationship with theprimary keyof the "one" side.

Example Usage:

A screenshot of a computer AI-generated content may be incorrect.

CREATE TABLE Departments (

DeptID INT PRIMARY KEY,

DeptName VARCHAR(50)

);

CREATE TABLE Employees (

EmpID INT PRIMARY KEY,

Name VARCHAR(50),

DeptID INT, -- Foreign key on the "many" side

FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)

);

Eachdepartmentcan havemany employees→ DeptID is aforeign keyin Employees.

Why Other Options Are Incorrect:

Option A (Reflexive relationship) (Incorrect):Refers tounary (self-referential) relationships, not 1:M relationships.

Option B (Binary relationship) (Incorrect):A binary relationship involvestwo entities, but does not define where the foreign key is stored.

Option C (Weak entity) (Incorrect):Weak entitiesdepend on a strong entity, but not all "many" sides are weak entities.

Thus, the correct answer isForeign key, as it is placed on the "many" side of the relationship.

Databases need to supportApplication Programming Interfaces (APIs)to enable seamless integration withgeneral-purpose programming languageslike Python, Java, and C#. APIs likeODBC (Open Database Connectivity) and JDBC (Java Database Connectivity)allow applications to interact with databases without requiring complex SQL commands.

Option A (Incorrect):Thestorage manageris responsible for managing low-levelfile system operations, but it does not simplify SQL integration with programming languages.

Option B (Incorrect):The ability toreverse results(e.g., using ORDER BY DESC) is a SQL feature but is unrelated to integration with programming languages.

Option C (Incorrect):Thequery processoroptimizes and executes SQL queries but does not provide an interface for application development.

Option D (Correct):APIsallow databases to be accessed easily from different programming environments, simplifying integration.