Section 4.1-4.5

Question 1 (4.1.1)
Which database is relational?
• Oracle Database
• IDMS
• MongoDB

Correct answer: Oracle Database

ZyBooks Explanation:
Oracle Database, initially released in 1979, is the first commercial database to support Structured Query Language (SQL). Oracle Database is now one of the most widely used relational database systems.

Second Explanation:
Oracle Database follows the relational model by storing data in tables with rows and columns and using SQL to manage relationships between tables.

Question 2 (4.1.1)
The relational model was originally developed for which types of applications?
• Big data storage and analysis
• Transactional applications like banking and airline reservations
• Desktop applications with small databases

Correct answer: Transactional applications like banking and airline reservations

ZyBooks Explanation:
At the time the relational model was designed, transactional applications were most important. Today most transactional systems like banking and airline reservations are built on relational databases.

Second Explanation:
Early databases needed to reliably handle frequent updates and transactions, such as money transfers or flight bookings, which made relational databases ideal.

Question 3 (4.1.1)
What was the initial impediment to commercial adoption of relational databases in the early 1980s?
• Reliability
• Processing speed
• Cost

Correct answer: Processing speed

ZyBooks Explanation:
Early relational products were not as fast as more established non-relational products. However, as relational databases matured, processing speed rapidly caught up with non-relational databases.

Second Explanation:
Early relational systems traded speed for flexibility, but performance improved as hardware and database engines advanced.

Question 1 (4.1.3)
Which terms are commonly used in database processing?
• Tuple, relation, attribute
• Row, table, column
• Record, file, field

Correct answer: Row, table, column

ZyBooks Explanation:
The most commonly used terms in relational database are row, column, and table. Occasionally tuple, relation, attribute and record, file, field are also used.

Second Explanation:
In practice, databases usually describe data using tables made of rows and columns rather than formal mathematical terms.

Question 2 (4.1.3)
Are these tables the same?
{ (8, mango, FALSE), (-11, watermelon, FALSE) }
{ (-11, watermelon, FALSE), (8, mango, FALSE) }
• Yes
• No

Correct answer: Yes

ZyBooks Explanation:
Both tables contain the same rows. Since a table is a set, the order of the rows does not matter.

Second Explanation:
Tables behave like sets, so rearranging rows does not change the data.

Question 3 (4.1.3)
In the relational data structure, which components are named?
• Data type, row, table
• Data type, table
• Data type, table, column

Correct answer: Data type, table, column

ZyBooks Explanation:
Data type, table, and column are all named. Rows are not named.

Second Explanation:
Columns and tables have identifiers, but rows are identified by their values rather than names.

Question 4 (4.1.3)
Can a query select one specific row from a table?
• Yes, by specifying the row name
• Yes, by specifying one or more row values
• No

Correct answer: Yes, by specifying one or more row values

ZyBooks Explanation:
A query selects a specific row by specifying one or more row values. Ex:
SELECT EmployeeName
FROM Employee
WHERE EmployeeID = 2594;

selects rows with the value 2594 in the EmployeeID column. If each row has a different value, only one row is selected.

Second Explanation:
SQL filters rows using conditions, and if the condition uniquely identifies a row, only that row is returned.

Question 1 (4.1.5)
What is the result of a relational operation?
• A row
• A column
• A table

Correct answer: A table

ZyBooks Explanation:
All relational operations result in a table. The result table is not stored in the database but has the same structure as stored tables.

Second Explanation:
Every relational operation produces a table, even if it contains only one row or column.

Question 2 (4.1.5)
Name three relational operations.
• Select, project, and union
• Square root, exponent, and logarithm
• Integrate and differentiate

Correct answer: Select, project, and union

ZyBooks Explanation:
Relational algebra is based on operations of mathematical set theory, such as select, project, and union.

Second Explanation:
These operations filter rows, choose columns, and combine tables.

Question 3 (4.1.5)
An SQL statement can implement only one relational operation.
• True
• False

Correct answer: False

ZyBooks Explanation:
Most SQL queries implement several operations. Ex:
SELECT Name
FROM Employee
WHERE Salary > 50000;

implements both a select and a project operation.

Second Explanation:
A single SQL query often performs multiple relational operations at once.

Question 1 (4.1.7)
Unique primary key is an example of a relational rule.
• True
• False

Correct answer: True

ZyBooks Explanation:
All relational tables should have a column with unique values, called the primary key. The primary key is used to identify individual rows. Creating tables without a primary key usually causes data management problems.

Second Explanation:
Primary keys enforce uniqueness, which is a fundamental rule of relational databases.

Question 2 (4.1.7)
Delete cascade is an example of a relational rule.
• True
• False

Correct answer: False

ZyBooks Explanation:
Delete cascade is a business rule, not a relational rule. The delete cascade rule requires that, when a row of a particular table is deleted, all related rows are also deleted. Deleting related rows depends on business policy, and may apply to some tables but not others.

Second Explanation:
Cascade deletes depend on business decisions, not on the relational model itself.

Question 3 (4.1.7)
Data in a relational database can violate relational rules.
• True
• False

Correct answer: True

ZyBooks Explanation:
Relational rules are implemented as SQL constraints. If constraints are not specified correctly, relational rules can be violated. Ex: A table with no primary key can be created. Relational rule violations create data management problems and should be avoided.

Second Explanation:
If constraints are missing or poorly designed, databases can store invalid or inconsistent data.

Question 1 (4.2.2)
What does a NULL in the Name column represent?
• Unknown
• Inapplicable
• Either unknown or inapplicable

Correct answer: Unknown
ZyBooks Explanation:
Every employee has a name, so NULL indicates the name exists but has not been entered. The name is unknown to the database.
Second Explanation:
The name should exist for every employee, but the database does not currently know it. That makes the value unknown, not inapplicable.

Question 2 (4.2.2)
In the Bonus column of the Lisa Ellison row, what does the zero represent?
• Lisa Ellison's bonus is unknown.
• Lisa Ellison is not eligible for a bonus.
• Lisa Ellison has earned no bonus.

Correct answer: Lisa Ellison has earned no bonus.
ZyBooks Explanation:
Zero is a valid bonus amount. Zero is not the same as unknown or inapplicable data.
Second Explanation:
A value of 0 is real data. It means the bonus was calculated and the result was zero, not missing or unknown.

Question 1 (4.2.4)
Which columns may contain NULL values?
• Code
• Code and Name
• Name and ManagerID

Correct answer: Name and ManagerID
ZyBooks Explanation:
The Name and ManagerID columns may contain NULL values, by default.
Second Explanation:
Only Code is explicitly marked NOT NULL. Columns without that constraint allow NULL by default.

Question 2 (4.2.4)
Which alteration to the CREATE TABLE statement prevents ManagerID from being NULL?
• ManagerID NOT NULL SMALLINT
• ManagerID NOT NULL
• ManagerID SMALLINT NOT NULL

Correct answer: ManagerID SMALLINT NOT NULL
ZyBooks Explanation:
The NOT NULL constraint must be listed after the column name and data type.
Second Explanation:
SQL syntax requires the data type first. Constraints like NOT NULL come after.

Question 3 (4.2.4)
What happens when a user attempts to insert a new department without a Code value?
• The database accepts the insert and assigns Code with zero.
• The database accepts the insert and assigns Code with NULL.
• The database rejects the insert.

Correct answer: The database rejects the insert.
ZyBooks Explanation:
Since Code is NOT NULL, the database does not accept an insert without a Code value. The insert statement fails.
Second Explanation:
NOT NULL means the value must be provided. The database will not invent one for you.

Question 1 (4.2.6)
What name is selected by the following statement?

SELECT Name
FROM Compensation
WHERE Salary = Bonus;

(no mcq)

Correct answer: Sam Snead
ZyBooks Explanation:
Jiho Chen is not selected because NULL = NULL is NULL.
Second Explanation:
Any comparison involving NULL evaluates to NULL, not TRUE. Rows are selected only when the condition is TRUE.

Question 2 (4.2.6)
What name is selected by the following statement?

SELECT Name
FROM Compensation
WHERE (Salary / Bonus) < 1.0;

(no mcq)

Correct answer: Maria Rodriguez
ZyBooks Explanation:
The / and < operators return NULL when either operand is NULL. As a result, Lisa Ellison and Jiho Chen are not selected.
Second Explanation:
Arithmetic with NULL produces NULL. Only rows where the full expression evaluates to TRUE are returned.

Question 1 (4.2.8)
How many rows are returned?

SELECT *
FROM Country
WHERE Population = NULL;

• 0

• 1

• 3

Correct answer: 0
ZyBooks Explanation:
The = operator evaluates to NULL when either operand is NULL, so Population = NULL is always NULL. Rows are selected only when the WHERE clause is TRUE. Since the WHERE clause is always NULL, no rows are selected.
Second Explanation:
You can never find NULL using =. The comparison never becomes TRUE.

Question 2 (4.2.8)
How many rows are returned?

SELECT *
FROM Country
WHERE Population IS NULL;

• 0

• 1

• 3

Correct answer: 1
ZyBooks Explanation:
Only Anguilla has NULL in the Population column.
Second Explanation:
IS NULL is the correct way to test for missing values.

Question 3 (4.2.8)
What is missing to select all rows except Aruba?

SELECT *
FROM Country
WHERE IndepYear _____;

• != NULL

• IS NULL

• IS NOT NULL

Correct answer: IS NOT NULL
ZyBooks Explanation:
Only Aruba has a NULL value in IndepYear, so IS NOT NULL selects all the non-NULL rows.
Second Explanation:
IS NOT NULL filters out the one row where the value is missing.

Question 1 (4.2.9)
In MySQL, what names are selected by the following query?

SELECT Name
FROM Compensation
WHERE Salary > 30000 OR Bonus > 1000;

• Lisa Ellison
• Sam Snead and Maria Rodriguez
• Lisa Ellison, Sam Snead, and Maria Rodriguez

Correct answer: Lisa Ellison, Sam Snead, and Maria Rodriguez
ZyBooks Explanation:
OR is TRUE when either operand is TRUE. Either Salary > 30000 or Bonus > 1000 is TRUE for all rows, so all names are selected.
Second Explanation:
With OR, only one side needs to be TRUE. Every row satisfies at least one condition.

Question 2 (4.2.9)
In MySQL, what names are selected by the following query?

SELECT Name
FROM Compensation
WHERE Salary > 30000 AND Bonus > 1000;

• Lisa Ellison
• Sam Snead and Maria Rodriguez
• Lisa Ellison, Sam Snead, and Maria Rodriguez

Correct answer: Sam Snead and Maria Rodriguez
ZyBooks Explanation:
The WHERE clause is TRUE for Sam Snead and Maria Rodriguez, so both are selected. For Lisa Ellison, 115000 > 30000 is TRUE but NULL > 1000 is NULL. Since TRUE AND NULL is NULL, Lisa Ellison is not selected.
Second Explanation:
AND requires both sides to be TRUE. NULL breaks that requirement.

Question 3 (4.2.9)
In MySQL, what names are selected by the following query?

SELECT Name
FROM Compensation
WHERE NOT (Salary > 30000 AND Bonus > 1000);

• No names are selected
• Lisa Ellison
• Lisa Ellison, Sam Snead, and Maria Rodriguez

Correct answer: No names are selected
ZyBooks Explanation:
The WHERE clause for Lisa Ellison evaluates to NOT(NULL). Since NOT(NULL) is NULL, Lisa Ellison is not selected. The WHERE clause for Sam Snead and Maria Rodriguez evaluates to NOT(TRUE). Since NOT(TRUE) is FALSE, neither Sam Snead nor Maria Rodriguez is selected.
Second Explanation:
NOT does not turn NULL into TRUE. NULL stays NULL, and only TRUE rows are returned.

Question 1 (4.3.1)
Name is a good primary key.
• True
• False

Correct answer: False
ZyBooks Explanation:
New employees with the same name might be added to the table. Since the Name column might contain duplicate values, the Name column is not a good primary key.
Second Explanation:
Primary keys must be unique. Names are not guaranteed to be unique, so they are unreliable identifiers.

Question 2 (4.3.1)
A new employee can be added without an ID value.
• True
• False

Correct answer: False
ZyBooks Explanation:
Primary keys may not be NULL. When a new employee is added to the table, the database requires a non-NULL value for ID.
Second Explanation:
Because ID is the primary key, every row must have a value. NULL is not allowed.

Question 3 (4.3.1)
A new employee can be added with ID 5384.
• True
• False

Correct answer: False
ZyBooks Explanation:
Sam Snead already has ID 5384. ID is the primary key, and duplicate primary key values are not allowed.
Second Explanation:
Primary keys must be unique. Reusing an existing ID violates that rule.

Question 1 (4.3.3)
Can (ID, Relationship) be the primary key of Family?
• Yes
• No
• Cannot determine answer from data in the table

Correct answer: No
ZyBooks Explanation:
(ID, Relationship) cannot be the primary key because (6381, Daughter) is repeated. Composite primary keys must be unique.
Second Explanation:
A composite key still has to uniquely identify each row. Since that pair appears twice, it fails.

Question 2 (4.3.3)
The primary key of the PhoneNumber table is not indicated with a bullet. What is the primary key of PhoneNumber?
• Table has no primary key
• (AreaCode, Number)
• (AreaCode, Exchange, Number)

Correct answer: (AreaCode, Exchange, Number)
ZyBooks Explanation:
(AreaCode, Exchange, Number) is unique, non-null, and minimal, so (AreaCode, Exchange, Number) is a composite primary key. Occasionally, a primary key includes all table columns.
Second Explanation:
All three columns together are needed to uniquely identify a phone number.

Question 1 (4.3.5)
Lisa, Sam, and Maria must have unique IDs and names.
• True
• False

Correct answer: False
ZyBooks Explanation:
The ID column is the Employee table's primary key, so only the ID must be unique. Ex: An employee with ID 7654 and name Lisa Ellison may be added to the Employee table.
Second Explanation:
Only the primary key column is required to be unique. Names can repeat.

Question 2 (4.3.5)
The PRIMARY KEY constraint may include multiple columns.
• True
• False

Correct answer: True
ZyBooks Explanation:
The PRIMARY KEY constraint includes multiple columns when the primary key is composite. In the animation above, two columns are listed to create the composite primary key ID and Number for the Family table.
Second Explanation:
Composite keys are still primary keys, just made of multiple columns.

Question 3 (4.3.5)
Assuming the Family table has the five rows shown above, a new row with values (2538, 2, 'Daughter', 'Ella Ellison') may be added to the Family table.
• True
• False

Correct answer: False
ZyBooks Explanation:
The ID and Number are the Family table's composite primary key, so only rows with unique ID and Number values may be added. Edward Ellison already has the ID 2538 and Number 2, so Ella must use a different Number.
Second Explanation:
The composite key (2538, 2) already exists, so inserting another row with the same pair violates the primary key.

Question 1 (4.3.7)
Which statement illustrates good programming practice?
• INSERT INTO Department (Code, Name, ManagerID) VALUES (44, 'Engineering', 2538);
• INSERT INTO Department (Name, ManagerID) VALUES ('Engineering', 2538);
• INSERT INTO Department VALUES ('Engineering', 2538);

Correct answer:

INSERT INTO Department (Name, ManagerID)
VALUES ('Engineering', 2538);

ZyBooks Explanation:
The Code column is auto-increment, so the database chooses a unique integer for Code.
Second Explanation:
Letting the database handle auto-increment values avoids conflicts and errors.

Question 2 (4.3.7)
Which statement results in a MySQL error?
• INSERT INTO Department (Name, ManagerID) VALUES ('Engineering', NULL);
• INSERT INTO Department VALUES (NULL, 'Engineering', NULL);
• INSERT INTO Department (Name, ManagerID) VALUES ('Engineering');

Correct answer:

INSERT INTO Department (Name, ManagerID)
VALUES ('Engineering');

ZyBooks Explanation:
This statement has incorrect syntax and generates a MySQL error. The INSERT INTO lists two column names, but the VALUES clause lists only one column value.
Second Explanation:
Column list count must match the VALUES count exactly, or MySQL throws an error.

Question 1 (4.4.2)
The data type of Manager and ID must be the same.
• True
• False

Correct answer: True
ZyBooks Explanation:
Manager is a foreign key, which refers to the ID primary key. Data types of foreign and primary key must be the same.
Second Explanation:
Foreign keys must match the referenced primary key’s data type so the database can enforce the relationship.

Question 2 (4.4.2)
NULL in the Manager column refers to an Employee row with a NULL ID.
• True
• False

Correct answer: False
ZyBooks Explanation:
ID is the primary key of Employee and thus cannot be NULL. So NULL in the Manager column does not refer to an Employee row. A NULL manager indicates a department has no manager, or the manager is unknown.
Second Explanation:
NULL means “no value,” not “point to a row with NULL.” Primary keys cannot be NULL.

Question 3 (4.4.2)
Sam Snead does not manage a department.
• True
• False

Correct answer: True
ZyBooks Explanation:
The Department table's Manager column lists the employee IDs of all managers, and Sam Snead's employee ID 5384 is not listed in the Manager column.
Second Explanation:
If an employee’s ID does not appear in the Manager column, they manage no department.

Question 4 (4.4.2)
Replacing NULL in the Manager column with 5384 assigns Sam Snead as the manager of the Technical Support department.
• True
• False

Correct answer: True
ZyBooks Explanation:
The Manager cell on the row with Technical Support indicates the manager's ID. Replacing NULL with Sam Snead's ID 5384 makes Sam the manager.
Second Explanation:
Foreign key values directly identify the related row in the parent table.

Question 5 (4.4.2)
The NULL in the Manager column may be replaced with 9876.
• True
• False

Correct answer: False
ZyBooks Explanation:
A non-NULL foreign key value must match some primary key value. The foreign key value 9876 does not match any values in the Employee table's ID column.
Second Explanation:
Foreign keys cannot reference rows that do not exist.

Question 6 (4.4.2)
Values in a foreign key must be unique.
• True
• False

Correct answer: False
ZyBooks Explanation:
Unlike primary keys, foreign key values may be repeated. Repeated Manager values indicate several departments have the same manager.
Second Explanation:
Multiple rows can reference the same parent row.

Question 1 (4.4.3)
Which family member has the Blue Shield health plan?
• Henry Ellison
• Jose Rodriguez
• Clara Rodriguez

Correct answer: Jose Rodriguez
ZyBooks Explanation:
In the HealthPlan table, the Blue Shield row contains the value (6381, 1) for the composite foreign key (EmployeeID, DependentNumber). (6381, 1) refers to Jose Rodriguez in the Family table.
Second Explanation:
The composite foreign key must match the composite primary key in Family exactly.

Question 2 (4.4.3)
Can the HealthPlan table contain the value (2538, 0) in (EmployeeID, DependentNumber)?
• Yes, with the Family data shown above
• Yes, if Family had additional rows and primary key values
• No

Correct answer: Yes, if Family had additional rows and primary key values
ZyBooks Explanation:
If a primary key row of Family contained the value (2538,0), this value would be allowable in the foreign key (EmployeeID, DependentNumber).
Second Explanation:
Foreign keys are allowed only if a matching primary key row exists.

Question 3 (4.4.3)
Assuming NULL is allowed in both columns, can (EmployeeID, DependentNumber) contain the value (NULL, NULL)?
• Yes, with the Family data shown above
• Yes, if Family had additional rows and primary key values
• No

Correct answer: Yes, with the Family data shown above
ZyBooks Explanation:
All columns of a composite foreign key may be NULL. So (EmployeeID, DependentNumber) can contain (NULL, NULL).
Second Explanation:
Foreign key constraints allow all-NULL composite values if NULLs are permitted.

Question 1 (4.4.4)
Which department has the same manager and assistant?
• Engineering
• Sales
• No department has the same manager and assistant

Correct answer: No department has the same manager and assistant
ZyBooks Explanation:
In the Department table, all rows have different values for the Manager and Assistant foreign keys. Therefore no department has the same manager and assistant.
Second Explanation:
Comparing the two foreign key columns shows no row where the values match.

Question 2 (4.4.4)
Who is Lisa Ellison's manager?
• Sam Snead
• Maria Rodriguez
• Jiho Chen

Correct answer: Jiho Chen
ZyBooks Explanation:
In the Employee table, Lisa Ellison's Manager foreign key is 8820. The value 8820 refers to Jiho Chen.
Second Explanation:
Foreign key values map directly to the referenced primary key row.

Question 1 (4.4.6)
In a CREATE TABLE statement, the FOREIGN KEY constraint must follow all column declarations.
• True
• False

Correct answer: False
ZyBooks Explanation:
The order of constraints and column declarations is not significant. For readability, however, column declarations normally appear first, followed by the PRIMARY KEY constraint and the FOREIGN KEY constraint.
Second Explanation:
SQL allows flexibility in order, but convention improves readability.

Question 2 (4.4.6)
In a FOREIGN KEY constraint, parentheses are required around the foreign key column name.
• True
• False

Correct answer: True
ZyBooks Explanation:
Parentheses are required around both the foreign key and primary key column names. Ex: FOREIGN KEY (ShipmentID) REFERENCES Shipment(ID)
Second Explanation:
SQL syntax requires parentheses even for single-column keys

Question 3 (4.4.6)
In a FOREIGN KEY constraint, data types of the foreign key and primary key columns must be the same.
• True
• False

Correct answer: True
ZyBooks Explanation:
When primary and foreign key data types do not match, the CREATE TABLE statement containing the FOREIGN KEY constraint fails.
Second Explanation:
Type mismatches prevent reliable enforcement of referential integrity.

Question 4 (4.4.6)
Adding a FOREIGN KEY constraint to a table only affects inserting new rows into the table.
• True
• False

Correct answer: False
ZyBooks Explanation:
Inserting and updating rows are affected. Ex: Updating the ManagerID 2538 to 9999 is rejected if Employee ID 9999 does not exist.
Second Explanation:
Foreign keys are enforced on both INSERT and UPDATE operations.

Question 1 (4.5.2)
In the Department table, which foreign key value violates referential integrity?
• 2538
• 3829
• NULL

Correct answer: 3829
ZyBooks Explanation:
Since no employee has ID 3829, 3829 violates referential integrity.
Second Explanation:
A non-NULL foreign key must match an existing primary key value in the referenced table.

Question 2 (4.5.2)
Does the NULL in the Manager column violate referential integrity?
• Yes
• No

Correct answer: No
ZyBooks Explanation:
Manager is a single-column, or simple, foreign key. A NULL in a simple foreign key does not violate referential integrity.
Second Explanation:
NULL means “no relationship recorded,” which is allowed unless the foreign key column is defined NOT NULL.

Question 1 (4.5.3)
In the HealthPlan table, which foreign key value violates referential integrity?
• (NULL, NULL) only
• (6381, NULL) only
• (6381, 4) only
• Both (6381, NULL) and (6381, 4)

Correct answer: Both (6381, NULL) and (6381, 4)
ZyBooks Explanation:
(6381, 4) does not match any (ID, Number) in a Family row and thus violates referential integrity. (6381, NULL) is partially null and thus violates referential integrity.
Second Explanation:
Composite foreign keys must either fully match a parent key, or be fully NULL. Partial NULL breaks the match.

Question 2 (4.5.3)
In the HealthPlan table, which foreign key value is fully NULL?
• (6381, NULL)
• (NULL, NULL)
• No foreign key values are fully NULL

Correct answer: (NULL, NULL)
ZyBooks Explanation:
(NULL, NULL) is fully NULL, since all foreign key columns are NULL.
Second Explanation:
“Fully NULL” means every column in the composite key is NULL.

Question 1 (4.5.7)
RESTRICT, when the row containing Maria Rodriguez is deleted.
• The Sales and Marketing managers are set to NULL.
• The Sales and Marketing departments are deleted.
• The delete is rejected.

Correct answer: The delete is rejected.
ZyBooks Explanation:
RESTRICT rejects referential integrity violations. If Maria Rodriguez is deleted, the managers of Sales and Marketing would not match any ID, so the delete is rejected.
Second Explanation:
RESTRICT blocks the parent-row delete if child rows would be left with invalid foreign keys.

Question 2 (4.5.7)
SET NULL, when Lisa Ellison's ID is changed to 1001.
• The Engineering manager is set to NULL.
• The Engineering manager is set to 1001.
• The change is rejected.

Correct answer: The Engineering manager is set to NULL.
ZyBooks Explanation:
SET NULL nullifies matching foreign keys. Since Lisa Ellison manages Engineering, changing Lisa's ID sets the Engineering manager to NULL.
Second Explanation:
SET NULL does not “follow” the new ID. It wipes the referencing foreign key value instead.

Question 3 (4.5.7)
SET DEFAULT, when Lisa Ellison's ID is changed to 1001.
• The Engineering manager is set to NULL.
• The Engineering manager is set to the Manager default value.
• The change is rejected.

Correct answer: The Engineering manager is set to the Manager default value.
ZyBooks Explanation:
SET DEFAULT sets matching foreign keys to a default value. Since Lisa Ellison manages Engineering, the Engineering manager is set to a default value, which must be a valid primary key in the Employee table.
Second Explanation:
SET DEFAULT replaces the foreign key with the column’s default, but that default must still reference a real Employee ID.

Question 4 (4.5.7)
CASCADE, when Maria Rodriguez' ID is changed to 2022.
• The Sales and Marketing managers are set to NULL.
• The Sales and Marketing managers are set to 2022.
• The change is rejected.

Correct answer: The Sales and Marketing managers are set to 2022.
ZyBooks Explanation:
CASCADE propagates primary key changes to matching foreign keys. Maria Rodriguez manages Sales and Marketing, so Sales and Marketing managers are set to 2022.
Second Explanation:
CASCADE “follows” the updated primary key and updates all matching foreign keys to keep references valid.

Question 5 (4.5.7)
CASCADE, when Maria Rodriguez is deleted.
• The Sales and Marketing managers are set to NULL.
• The Sales and Marketing departments are deleted.
• The delete is rejected.

Correct answer: The Sales and Marketing departments are deleted.
ZyBooks Explanation:
CASCADE propagates primary key deletes to matching foreign keys. Maria Rodriguez manages Sales and Marketing, so the Sales and Marketing rows are deleted.
Second Explanation:
ON DELETE CASCADE deletes the child rows that referenced the deleted parent row.

Question 1 (4.5.9)
Delete Lisa Ellison.
• Lisa Ellison is deleted.
• Lisa Ellison is deleted, and the Engineering ManagerID is set to NULL.
• The delete is rejected.

Correct answer: Lisa Ellison is deleted, and the Engineering ManagerID is set to NULL.
ZyBooks Explanation:
ON DELETE SET NULL sets the foreign key to NULL when the matching primary key is deleted.
Second Explanation:
Lisa is a referenced parent row for Engineering’s ManagerID. Deleting her triggers SET NULL on that foreign key.

Question 2 (4.5.9)
Update Lisa Ellison's ID to 1000.
• Lisa Ellison's ID is set to 1000.
• Lisa Ellison's ID and the Engineering ManagerID are set to 1000.
• The update is rejected.

Correct answer: Lisa Ellison's ID and the Engineering ManagerID are set to 1000.
ZyBooks Explanation:
ON UPDATE CASCADE updates the foreign key to the same value used to update the primary key.
Second Explanation:
CASCADE keeps all references consistent when the parent key value changes.

Question 3 (4.5.9)
Update the Engineering ManagerID to 9999.
• The Engineering ManagerID is set to 9999.
• Lisa Ellison's ID and the Engineering ManagerID are set to 9999.
• The update is rejected.

Correct answer: The update is rejected.
ZyBooks Explanation:
ON UPDATE only applies to updating the primary key, not the foreign key. 9999 is an invalid foreign key since 9999 does not exist in Employee ID. The database does not allow invalid foreign keys.
Second Explanation:
You are trying to directly set a foreign key to a value that has no matching parent row, so it violates referential integrity.

Question 4 (4.5.9)
Delete Engineering.
• Engineering is deleted.
• Engineering is deleted, and Lisa Ellison's ID is set to NULL.
• The delete is rejected.

Correct answer: Engineering is deleted.
ZyBooks Explanation:
Deleting a foreign key has no effect on the primary key.
Second Explanation:
Deleting a child row does not impact the parent table. The relationship points from Department to Employee, not the other way around.