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

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.

TheUNIONkeyword in SQL is used tocombine the resultsof two or more SELECT queries into asingle result setwhile 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 insome database systems for data merging).

Option C (Incorrect):INTEGRATE is not a SQL keyword.

Option D (Incorrect):CONSOLIDATE is not an SQL keyword.

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.

Indatabase design, an entity is classified asstrong or weakbased on whether it has aprimary keythat 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 astrong entity(has OrderID as its own primary key).

OrderDetails is aweak entity(depends on OrderID for uniqueness).

Why Other Options Are Incorrect:

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

Option C (Foreign key) (Incorrect):Weak entitiesdepend on foreign keys, butprimary keysdefine their status.

Option D (Cardinality) (Incorrect):Cardinality definesrelationship constraintsbut doesnot differentiate entity types.

Thus, the correct answer isPrimary key, as it is the defining characteristic betweenstrong and weak entities.

TheCHECKconstraint is used in SQL toenforce ruleson a column's values. It ensures that data inserted into a table meets specified conditions, such as range restrictions or logical rules.

Example Usage:

sql

CREATE TABLE Employees (

ID INT PRIMARY KEY,

Name VARCHAR(50),

Salary INT CHECK (Salary BETWEEN 30000 AND 150000)

);

This constraint ensures thatsalary values fall between 30,000 and 150,000.

If an INSERT or UPDATE statement tries to set Salary = 20000, itfailsbecause it does notmeet the CHECK condition.

Why Other Options Are Incorrect:

Option B (FROM) (Incorrect):Used in SELECT statements, not for constraints.

Option C (WHERE) (Incorrect):Filters rows in queries butdoes not enforce constraints.

Option D (SET) (Incorrect):Used for updating records (UPDATE table_name SET column = value) butnot for defining constraints.

Thus,CHECK is the correct answer, as it ensures that column values remain within an expected range.

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.

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.

Thecorrect syntaxfor deleting records from a table in SQL is:

sql

DELETE FROM table_name WHERE condition;

This deletesonly the rowsthat match the condition.

Example Usage:

sql

DELETE FROM Employees WHERE Salary < 30000;

Deletesall employees earning less than $30,000.

Why Other Options Are Incorrect:

Option A (Incorrect):Missing FROMkeyword. The correct syntax is DELETE FROM table_name.

Option C (Partially Correct):DELETE FROM table_name;deletes all rows, but it lacks a WHERE clause.

Option D (Incorrect):DELETE *is not validin SQL. The correct command is just DELETE FROM.

Thus, the correct answer isDELETE FROM table_name WHERE condition;.

The % operator in MySQL is known as themodulus operator. It returns theremainderof a division operation between two numbers.

Example:

sql

SELECT 10 % 3; -- Output: 1 (10 divided by 3 gives remainder 1)

Option A (Incorrect):Raising a number to a power is done using the POW() function or ^ in some SQL dialects.

Option B (Incorrect):The = operator is used forequality comparisons, not %.

Option C (Correct):Themodulus operator (%)finds the remainder when one number is divided by another.

Option D (Incorrect):Subtraction is performed using the - operator.

TheABS()function in SQL returns theabsolute valueof a given number, effectively measuring itsdistance from zero.

Example Usage:

sql

SELECT ABS(-50), ABS(50);

Result:

50 | 50

This function ensures that numbers arealways positive, regardless of their original sign.

Why Other Options Are Incorrect:

Option A (CONCAT) (Incorrect):Used tocombine strings(not numbers).

Option B (LOWER) (Incorrect):Converts text tolowercase, not numerical operations.

Option C (FROM) (Incorrect):Part of SELECT FROM queries,not a function.

Thus, the correct choice isABS(), which computes the absolute value of a number.

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.

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.

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.

Toadd a primary key constraint after table creation, we use theALTER TABLEstatement.

Example Usage:

sql

ALTER TABLE Employees

ADD CONSTRAINT PK_Employees PRIMARY KEY (EmpID);

Thisaddsa primary key to the EmpID columnafter the table was created.

Why Other Options Are Incorrect:

Option B (CREATE TABLE) (Incorrect):Used for defining constraintsduringtable creation, not after.

Option C (UPDATE) (Incorrect):Modifiesrow values, not constraints.

Option D (INSERT INTO) (Incorrect):Used toadd datato a table, not modify constraints.

Thus, the correct answer isALTER TABLE, as itmodifies table structure to add a primary key constraint.

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.