Chapter 2 Quiz: The Relational Model

Test your understanding of the relational model, keys, normalization, data types, and constraints in DB2. Try to answer each question before revealing the answer.

Question 1

What was the primary limitation of hierarchical databases (like IMS) that the relational model was designed to overcome?

Show Answer The hierarchical model tightly coupled the physical storage structure to the logical access path. Queries that followed the tree hierarchy were efficient, but queries that cut across the hierarchy were slow or impractical. The relational model introduced **physical data independence** — separating the logical view of data from its physical storage, so any query could be expressed without regard to how the data was physically organized.

Question 2

In relational theory, what are the formal names for: (a) a table, (b) a row, and (c) a column?

Show Answer (a) **Relation**, (b) **Tuple**, (c) **Attribute**

Question 3

What two properties must a candidate key satisfy?

Show Answer 1. **Uniqueness**: No two rows can have the same value(s) for the candidate key columns. 2. **Irreducibility (minimality)**: No proper subset of the candidate key columns also satisfies uniqueness. You cannot remove any column from the key and still have it be unique.

Question 4

A table has columns (W, X, Y, Z). The combination (W, X) is unique, and neither W alone nor X alone is unique. Column Y is unique by itself. How many candidate keys does this table have?

Show Answer **Two candidate keys**: (W, X) and (Y). (W, X) is a candidate key because it is unique and minimal (neither W nor X alone is unique). Y is a candidate key because it is unique and minimal (it is a single column). Z is not a candidate key by itself (no information given about its uniqueness), and combinations like (W, Y) are not candidate keys because they are not minimal (Y alone is already unique).

Question 5

Why should you use DECIMAL rather than DOUBLE for monetary values in DB2?

Show Answer DOUBLE is a binary floating-point type that cannot represent many decimal fractions exactly. Values like 0.1 (ten cents) become repeating binary fractions, introducing tiny rounding errors. Over millions of transactions, these errors accumulate and cause balances to not reconcile. DECIMAL stores numbers as exact decimal digits with a fixed scale, so $0.10 is stored as exactly 0.10 with no rounding. This is essential for financial accuracy.

Question 6

What is the difference between a surrogate key and a natural key? Give one advantage of each.

Show Answer A **surrogate key** is an artificial system-generated identifier (like an auto-incrementing integer). Advantage: it never changes, so foreign key references remain stable even if business data is corrected. A **natural key** is derived from real-world data (like SSN or email). Advantage: it carries inherent meaning, so you can identify what a row refers to without performing a JOIN.

Question 7

What is referential integrity, and what DB2 mechanism enforces it?

Show Answer **Referential integrity** is the guarantee that every foreign key value in a child table corresponds to an existing primary key value in the parent table. It prevents orphan rows — rows that reference non-existent parent records. DB2 enforces referential integrity through **FOREIGN KEY constraints** declared in CREATE TABLE or ALTER TABLE statements. When an INSERT or UPDATE would create an invalid reference, or a DELETE would orphan child rows (with ON DELETE RESTRICT), DB2 rejects the operation.

Question 8

A table stores customer orders with the column ITEMS containing "Laptop, Mouse, Keyboard" as a comma-separated string. Which normal form does this violate, and why?

Show Answer This violates **First Normal Form (1NF)**. 1NF requires that every column contain only atomic (indivisible) values. A comma-separated list of items is a multi-valued field — it stores multiple values in a single cell. The correct design separates each item into its own row in a related ORDER_ITEM table.

Question 9

A table has composite primary key (STUDENT_ID, COURSE_ID) and a column STUDENT_NAME that depends only on STUDENT_ID. Which normal form does this violate?

Show Answer This violates **Second Normal Form (2NF)**. 2NF requires that every non-key column depend on the *entire* primary key, not just a part of it. STUDENT_NAME depends only on STUDENT_ID (a partial dependency), not on the full key (STUDENT_ID, COURSE_ID). The fix is to move STUDENT_NAME to a separate STUDENT table.

Question 10

In a table with primary key EMP_ID, the column DEPT_NAME depends on DEPT_ID (a non-key column), not directly on EMP_ID. Which normal form does this violate, and what is this type of dependency called?

Show Answer This violates **Third Normal Form (3NF)**. The dependency is called a **transitive dependency**: EMP_ID -> DEPT_ID -> DEPT_NAME. The non-key column DEPT_NAME depends on EMP_ID only *through* another non-key column DEPT_ID. The fix is to create a separate DEPARTMENT table with DEPT_ID as the primary key and DEPT_NAME as an attribute.

Question 11

Name the three types of data anomalies that normalization prevents, and give a one-sentence example of each.

Show Answer 1. **Update anomaly**: A customer's address is stored in every order row; changing the address requires updating multiple rows, and missing one creates inconsistency. 2. **Insert anomaly**: You cannot add a new department to the database until at least one employee is assigned to it, because department data only exists in the employee table. 3. **Delete anomaly**: Deleting the last order for a product destroys all information about that product (name, price, description), because product data was stored only in the order table.

Question 12

What is the difference between ON DELETE RESTRICT and ON DELETE CASCADE on a foreign key? When would you use each?

Show Answer **ON DELETE RESTRICT**: DB2 refuses to delete the parent row if any child rows reference it. The DELETE fails with an error. Use when child rows must be explicitly handled before the parent can be removed — for example, you should not be able to delete a customer while their accounts still exist. **ON DELETE CASCADE**: DB2 automatically deletes all child rows when the parent row is deleted. Use when child rows have no meaning without the parent — for example, deleting a shopping cart could cascade to delete all items in the cart. **Use with extreme caution in financial systems** — cascading deletes of accounts or transactions could cause data loss.

Question 13

On DB2 for z/OS, there is no native BOOLEAN data type. How does the Meridian Bank schema handle true/false flags, and why?

Show Answer The Meridian Bank schema uses **SMALLINT** with a CHECK constraint limiting values to 0 and 1 (e.g., `IS_ACTIVE SMALLINT NOT NULL DEFAULT 1, CHECK (IS_ACTIVE IN (0, 1))`). This approach works identically on both DB2 for z/OS and DB2 for LUW, ensuring cross-platform compatibility. While DB2 for LUW (version 11.1+) does support a native BOOLEAN type, using SMALLINT is the portable choice.

Question 14

You run SELECT * FROM CUSTOMER and the rows come back in a different order than you inserted them. Is this a bug?

Show Answer **No.** In relational theory, tuples (rows) within a relation are unordered. DB2 does not guarantee any particular row order unless you specify an **ORDER BY** clause. The rows may come back in insertion order, index order, or whatever order DB2's optimizer finds most efficient. If you need a specific order, you must always use ORDER BY.

Question 15

What is a Materialized Query Table (MQT) in DB2, and how does it relate to denormalization?

Show Answer A **Materialized Query Table (MQT)** is a table whose content is defined by a query and pre-computed from base tables. It stores the results of aggregations or joins so they do not have to be recalculated for every query. MQTs are a form of **controlled denormalization** — they introduce data redundancy (the aggregated data exists both in the base tables and the MQT), but DB2 can maintain them automatically and the optimizer can route queries to them transparently. This provides the performance benefit of denormalization with less risk of data inconsistency than manual redundant columns.

Question 16

Can two rows in a DB2 table both have NULL in a column with a UNIQUE constraint? Explain why.

Show Answer **Yes.** In DB2 (and the SQL standard), NULL represents an unknown value. Two NULLs are not considered equal — they are both "unknown," so comparing them does not produce a match. Therefore, multiple NULL values do not violate a UNIQUE constraint. If you need a column to have no NULLs and be unique, you must declare it with both NOT NULL and UNIQUE (or make it a PRIMARY KEY, which implies both).

Question 17

The Meridian Bank ACCOUNT table stores CURRENT_BALANCE directly rather than computing it from the TRANSACTION table. This is a denormalization. What two things must the design guarantee to make this safe?

Show Answer 1. **A synchronization mechanism**: There must be a reliable process (triggers, application logic, or batch reconciliation) that updates CURRENT_BALANCE whenever a transaction is added, modified, or reversed. If this mechanism fails silently, balances will drift. 2. **Documentation**: The denormalization must be explicitly documented so that future developers know that CURRENT_BALANCE is a derived value, understand how it is maintained, and know to update the synchronization mechanism if the business rules change. Additionally, periodic reconciliation (comparing stored balances against computed-from-transactions balances) is strongly recommended as a safety net.

Question 18

What is the difference between the degree and the cardinality of a table?

Show Answer **Degree** is the number of columns in a table (its "width"). **Cardinality** is the number of rows in a table (its "height"). For example, a CUSTOMER table with 15 columns and 2 million rows has a degree of 15 and a cardinality of 2,000,000. DB2's optimizer uses cardinality statistics (updated via RUNSTATS) to choose efficient execution plans.

Question 19

Which of Codd's rules states that the database description (metadata) must be stored in the same relational format as ordinary data? Where can you see this in DB2?

Show Answer **Rule 4 — Active Online Catalog.** In DB2 for LUW, you can query the **SYSCAT** views (e.g., `SELECT * FROM SYSCAT.TABLES`, `SELECT * FROM SYSCAT.COLUMNS`). In DB2 for z/OS, you query the **SYSIBM** catalog tables (e.g., `SELECT * FROM SYSIBM.SYSTABLES`). These catalog tables store all table definitions, column definitions, index definitions, and statistics as ordinary relational data, queryable with standard SQL.

Question 20

A CHECK constraint on the ACCOUNT table reads: CHECK (CURRENT_BALANCE >= -OVERDRAFT_LIMIT). In plain English, what business rule does this enforce?

Show Answer This enforces that an account's balance can go negative (overdrawn) **but only up to the overdraft limit**. If the overdraft limit is $500.00, the balance can drop to -$500.00 but no further. The constraint compares two columns in the same row: the current balance must be greater than or equal to the negative of the overdraft limit. For accounts with an overdraft limit of $0.00, this means the balance cannot go below zero.

Question 21

In relational algebra, what are the three core operations, and what SQL clauses do they correspond to?

Show Answer 1. **SELECT (Restriction)** — choosing rows that satisfy a condition — corresponds to the SQL **WHERE** clause. 2. **PROJECT (Projection)** — choosing specific columns — corresponds to the **column list** in the SQL SELECT clause. 3. **JOIN** — combining rows from two tables based on a matching condition — corresponds to the SQL **JOIN ... ON** clause.

Question 22

A developer writes SELECT c.NAME, a.BALANCE FROM CUSTOMER c, ACCOUNT a without a WHERE or JOIN clause. What happens?

Show Answer This produces a **Cartesian product** (also called a cross join) — every row in CUSTOMER is matched with every row in ACCOUNT. If CUSTOMER has 2 million rows and ACCOUNT has 5 million rows, the result is **10 trillion rows**. The query will consume enormous resources and likely never complete or will be cancelled by a timeout. The fix is to add a JOIN condition: `JOIN ACCOUNT a ON c.CUSTOMER_ID = a.CUSTOMER_ID`.

Question 23

Why does the Meridian Bank ACCOUNT table use ON DELETE RESTRICT for its foreign key to CUSTOMER, rather than ON DELETE CASCADE?

Show Answer In a banking system, deleting a customer should never automatically delete their accounts. Accounts contain financial records that may be subject to regulatory retention requirements, have outstanding balances, or be needed for audit trails. **ON DELETE RESTRICT** ensures that a customer cannot be deleted while they have any accounts — the accounts must be explicitly closed and handled first. ON DELETE CASCADE would silently destroy account records, which could violate banking regulations and cause financial data loss.

Question 24

What is an "informational constraint" in DB2 for LUW, and when would you use one?

Show Answer An **informational constraint** (declared with the NOT ENFORCED clause) is a foreign key or check constraint that DB2 records in the catalog but does **not** enforce during INSERT, UPDATE, or DELETE operations. The optimizer can still use the constraint information to generate better query plans (e.g., eliminating unnecessary joins). Use informational constraints when: (a) the application layer guarantees data integrity, (b) constraint checking overhead is unacceptable for the workload, and (c) you still want the optimizer to benefit from knowing about relationships. **Use with extreme caution** — if the application fails to maintain integrity, you will have corrupt data with no database-level protection.

Question 25

You are designing a table to track which Meridian Bank employees have which professional certifications. An employee can hold multiple certifications, and a certification can be held by multiple employees. What kind of table do you need, and what is its primary key?

Show Answer You need an **association table** (also called a junction table or bridge table) — for example, EMPLOYEE_CERTIFICATION. Its primary key would be a **composite key**: (EMPLOYEE_ID, CERTIFICATION_ID). Each row represents one employee holding one certification. The table would also have foreign keys to both the EMPLOYEE table and a CERTIFICATION reference table. Additional columns might include DATE_EARNED, EXPIRATION_DATE, and ISSUING_BODY.

Scoring Guide

  • 22-25 correct: Excellent command of relational fundamentals. Ready for Chapter 3.
  • 18-21 correct: Solid understanding. Review the sections for any missed questions.
  • 14-17 correct: Good start. Re-read Sections 2.4 (Keys) and 2.6 (Normalization) carefully.
  • Below 14: Spend additional time with this chapter before moving on. The relational model is the foundation for everything that follows.