The WGU Data Management – Foundations Exam (Data-Management-Foundations)

A simple primary key consists of only one column that uniquely identifies each row in a table.

Example Usage:

sql

CREATE TABLE Students (

StudentID INT PRIMARY KEY,

Name VARCHAR(50)

);

StudentID is a simple primary key because it consists of only one field.

Why Other Options Are Incorrect:

Option B (Partition) (Incorrect): Refers to partitioned tables , which divide data for performance reasons but are 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 often meaningless (e.g., auto-incremented IDs) , but this is not a term used to describe their structure .

Thus, the correct answer is Simple , as a single-field primary key is referred to as a simple primary key .

Attributes in a database represent the properties (names, values, or characteristics) of an entity .

Example Usage:

In a Students table, attributes might include:

StudentID (Primary Key), Name, Age, Major

Here, Name is an attribute describing the entity Student .

Why Other Options Are Incorrect:

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

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

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

Thus, the correct answer is Names , as attributes define entity properties .

In databases, primary keys are often set to auto-increment so that new rows automatically receive unique values. However, one common error is manually inserting a value into an auto-incremented primary key column , which overrides the automatic numbering and 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');

This manually overrides the auto-increment , which can lead to duplicate key errors .

Correct Insert (Avoiding Errors):

sql

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

The database assigns UserID automatically , preventing conflicts.

Why Other Options Are Incorrect:

Option B (Failing to set a numeric value) (Incorrect): The database automatically assigns values when AUTO_INCREMENT is used.

Option C (Designating multiple primary keys) (Incorrect): While incorrect , 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 is Inserting a value and overriding auto-increment , which can cause duplicate key errors and data inconsistencies .

The ON keyword specifies the field used by an index when creating it in SQL.

Example Usage:

sql

CREATE INDEX idx_employee_name

ON Employees(Name);

Here, an index idx_employee_name is created on the Name column .

This improves query performance when filtering by Name.

Why Other Options Are Incorrect:

Option B (IN) (Incorrect): Used in queries to match values in a set, not for indexing.

Option C (UNIQUE) (Incorrect): Ensures a column has unique values but does not define an index field .

Option D (CHECK) (Incorrect): Used for validating column values , not for indexing.

Thus, the correct answer is ON , as it defines the column on which an index is created .

The correct syntax for 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 is not valid SQL syntax .

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

Option D (Incorrect): Misses the INTO keyword, which is required in standard SQL.

Thus, the correct syntax is Option B , ensuring a properly formatted insert statement .

Non-relational databases (also called NoSQL databases ) are designed for handling big data and unstructured data efficiently. They are optimized for horizontal scaling , making them ideal for large-scale distributed systems .

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

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

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

Option D (Incorrect): NoSQL databases use document, key-value, graph, or column-family storage models , not tables, columns, and rows like relational databases.

A Full Join (FULL OUTER JOIN) selects all 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 retrieves all employees and all departments , even if an employee has no assigned department or a department has no employees .

Types of Joins:

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

LEFT JOIN (Incorrect) → Includes all rows from the left table and matching rows from the right table .

RIGHT JOIN (Incorrect) → Includes all rows from the right table and matching rows from the left table .

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

Thus, the correct answer is FULL JOIN , which ensures that all rows from both tables appear in the result .

In database design , attributes of an entity become columns in a relational table.

Example Usage:

For an Employee entity , attributes might include:

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

Name VARCHAR(50),

Salary DECIMAL(10,2),

DepartmentID INT

);

Each attribute (e.g., Name, Salary) becomes a column in the table.

Why Other Options Are Incorrect:

Option A (Modality) (Incorrect): Describes optional vs. mandatory relationships, not table structure.

Option B (Uniqueness) (Incorrect): Ensures distinct values but is not a column property .

Option D (Non-null values) (Incorrect): Ensures that columns must contain data but does not define attributes .

Thus, the correct answer is Attribute , as attributes of entities become table columns .

Information independence refers to the separation between data storage and data access . It allows a database’s logical structure to be modified without affecting existing applications .

Types of Information Independence:

Logical Data Independence → Ability to change the conceptual schema (e.g., renaming columns, adding new attributes) without modifying applications .

Physical Data Independence → Ability to change the physical storage structure (e.g., indexing, partitioning) without affecting queries .

Example of Logical Data Independence:

A new column is added to the Customers table, but existing queries still work without modification .

Example of Physical Data Independence:

Data is moved to SSD storage for performance improvement, but queries run the same way .

Why Other Options Are Incorrect:

Option A (Incorrect): Changing the database type (e.g., MySQL to PostgreSQL) is not information independence.

Option B (Incorrect): Making changes to queries is unrelated to database independence.

Option C (Incorrect): Interchanging databases is related to data portability , not information independence .

Thus, the correct answer is D - An ability to change the organization of data , as information independence ensures modifications do not disrupt database operations .