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

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.

Adata typedefines the kind of data a column can store in a database. It ensures data consistency and efficient storage.

Option A (Correct):A data type is anamed set of values, such asINTEGER, VARCHAR, DATE, etc.

Option B (Incorrect):A tuple refers to arowin a relational database, not a data type.

Option C (Incorrect):Data typesdefinecolumn values, but they do not correspond directly to columns.

Option D (Incorrect):Data types do not have avaryingset of rows; they define attributes for columns.

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.

Information independencerefers to theseparation between data storage and data access. It allows a database’slogical structureto be modifiedwithout affecting existing applications.

Types of Information Independence:

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

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

Example of Logical Data Independence:

Anew columnis added to the Customers table, but existing queriesstill work without modification.

Example of Physical Data Independence:

Data is moved to SSD storagefor performance improvement, but queriesrun the same way.

Why Other Options Are Incorrect:

Option A (Incorrect):Changing thedatabase type(e.g., MySQL to PostgreSQL) isnotinformation independence.

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

Option C (Incorrect):Interchanging databases is related todata portability, notinformation independence.

Thus, the correct answer isD - An ability to change the organization of data, asinformation independenceensuresmodifications do not disrupt database operations.

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.

Indatabase modeling, the relationship between asupertype and its subtypesis called anIsA relationship.

Example Usage:

AVehicle supertypemay haveCar and Truck subtypes.

Vehicle

├── Car

├── Truck

InER diagrams, this is represented as:

Vehicle (Supertype)

|

├── Car (Subtype)

├── Truck (Subtype)

SQL Table Implementation:

sql

CREATE TABLE Vehicle (

VehicleID INT PRIMARY KEY,

Make VARCHAR(50),

Model VARCHAR(50)

);

CREATE TABLE Car (

VehicleID INT PRIMARY KEY,

FOREIGN KEY (VehicleID) REFERENCES Vehicle(VehicleID),

EngineType VARCHAR(50)

);

CREATE TABLE Truck (

VehicleID INT PRIMARY KEY,

FOREIGN KEY (VehicleID) REFERENCES Vehicle(VehicleID),

CargoCapacity INT

);

This structurepreserves the IsA relationshipbetween Vehicle (supertype) and Car/Truck (subtypes).

Why Other Options Are Incorrect:

Option A (Strong entity) (Incorrect):Strong entitiesdo not rely on a supertype/subtype hierarchy.

Option C (Associative entity) (Incorrect):Used toresolve many-to-many relationships, not supertype/subtype relationships.

Option D (Weak entity) (Incorrect):Weak entitiesdepend on a strong entity, but supertype-subtype relations useinheritance(not dependency).

Thus, the correct answer isIsA relationship, as it describes theinheritance hierarchybetweensupertypes and subtypes.

In database indexing, abranchrefers to thepath from the top-level block (root node) to a bottom-level block (leaf node)in aB-Tree or B+ Tree index structure.

Example Usage in Indexing:

AB-Tree indexorganizes data hierarchically, withbranches leading to different parts of the tree.

When searching for a record, thequery follows a branchfrom theroot node down to the correct leaf node.

Why Other Options Are Incorrect:

Option A (Fan-out) (Incorrect):Refers tohow many children a node has, not the path.

Option B (Crow’s foot) (Incorrect):Anotation used in ER diagrams, not indexing.

Option D (Sparse index) (Incorrect):A type of indexstoring only some entries, not the path itself.

Thus, the correct answer isBranch, as it defines thepath from top to bottom in a database index.

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.

Bydatabase design conventions, theprimary key is usually placed in the first columnof a table to make it easy to identify and reference.

Example Usage:

sql

CREATE TABLE Employees (

EmpID INT PRIMARY KEY, -- First column (convention)

Name VARCHAR(50),

Salary DECIMAL(10,2)

);

EmpID is placed as the first columnfor clarity and quick access.

Why Other Options Are Incorrect:

Option A (In the table header) (Incorrect):Table headers onlydisplay column names, they do not contain values.

Option C (In the top row) (Incorrect):Thetop row contains data, not theprimary key definition.

Option D (In the last visible column) (Incorrect):While technically possible,placing a primary key at the endisuncommonin database design.

Thus, the correct answer isIn the first column, as this is the standard convention in relational databases.

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.