Chapter 2 Exercises: The Relational Model

These exercises progress from foundational recall through applied design. Difficulty ratings: [Beginner], [Intermediate], [Advanced], [Challenge].

Section A: Relational Foundations (Exercises 1-7)

Exercise 1 — Terminology Mapping [Beginner]

Match each formal relational term to its SQL/DB2 equivalent and provide a one-sentence definition.

Exercise 2 — Codd's Rules in Practice [Beginner]

For each scenario, identify which of Codd's rules is most relevant:

a) A DBA reorganizes the tablespace and rebuilds all indexes. No application queries need to change.

b) You query SYSCAT.TABLES to find all tables in the MERIDIAN schema.

c) A column allows NULL, and a query with WHERE SALARY = NULL returns no rows (the correct syntax is WHERE SALARY IS NULL).

d) You add a new column MIDDLE_NAME to the CUSTOMER table. Existing queries that do not reference MIDDLE_NAME continue to work.

Exercise 3 — Identifying Candidate Keys [Intermediate]

Consider the following table:

EMPLOYEE
---
EMP_ID (system-generated)
SSN
EMAIL
FIRST_NAME
LAST_NAME
DEPARTMENT_ID
HIRE_DATE

Assume: Every employee has a unique SSN. Every employee has a unique email address. First and last name combinations are NOT guaranteed unique.

a) List all candidate keys for this table. b) Which would you choose as the primary key? Justify your choice. c) What constraints would you add for the other candidate keys?

Exercise 4 — Surrogate vs. Natural Key Debate [Intermediate]

A startup is building a user management system. They propose using the user's email address as the primary key for the USER table.

a) List three potential problems with using email as a primary key. b) Propose an alternative design and explain its advantages. c) In what scenario might email as a primary key be acceptable?

Exercise 5 — Foreign Key Actions [Intermediate]

Given these tables at Meridian Bank:

BRANCH (BRANCH_ID PK, ...)
CUSTOMER (CUSTOMER_ID PK, HOME_BRANCH_ID FK → BRANCH)
ACCOUNT (ACCOUNT_ID PK, CUSTOMER_ID FK → CUSTOMER, BRANCH_ID FK → BRANCH)
TRANSACTION (TRANSACTION_ID PK, ACCOUNT_ID FK → ACCOUNT)

For each scenario, state which ON DELETE action (RESTRICT, CASCADE, SET NULL) is most appropriate and why:

a) Deleting a BRANCH that is referenced by ACCOUNT.BRANCH_ID b) Deleting a BRANCH that is referenced by CUSTOMER.HOME_BRANCH_ID c) Deleting an ACCOUNT that is referenced by TRANSACTION.ACCOUNT_ID d) Deleting a CUSTOMER that is referenced by ACCOUNT.CUSTOMER_ID

Exercise 6 — Composite Keys [Intermediate]

A university database tracks which students are enrolled in which courses each semester. Design a table called ENROLLMENT that captures this information.

a) What columns would the table need? b) What is the primary key? Is it a composite key? c) What foreign keys exist? d) Write the DB2 CREATE TABLE statement.

Exercise 7 — Pre-Relational Thinking [Beginner]

Explain in your own words:

a) Why the hierarchical model (like IMS) makes some queries easy and others hard. b) What "physical data independence" means and why it matters to a bank like Meridian that will run the same database for 20+ years. c) Why Codd's relational model was controversial within IBM when it was first proposed.

Section B: Data Types (Exercises 8-11)

Exercise 8 — Choosing Data Types [Beginner]

For each column described below, choose the most appropriate DB2 data type and justify your choice:

a) A customer's annual income (up to $999,999,999.99) b) A transaction description ("ATM withdrawal at 5th Ave branch") c) A US state code ("CA", "NY", "TX") d) The number of transactions in a batch (could exceed 100,000) e) A customer's date of birth f) A scanned image of a signed check g) An interest rate like 3.7500% h) A flag indicating whether an account allows overdraft (yes/no)

Exercise 9 — Why Not DOUBLE for Money? [Beginner]

A junior developer writes this query:

SELECT CAST(0.1 AS DOUBLE) + CAST(0.1 AS DOUBLE) + CAST(0.1 AS DOUBLE) AS RESULT
FROM SYSIBM.SYSDUMMY1;

a) What result would you expect mathematically? b) What result might you actually get? c) Why does this matter in a banking system? d) What data type should be used instead?

Exercise 10 — Platform Differences [Intermediate]

For each data type need, describe how you would handle it on DB2 for z/OS versus DB2 for LUW:

a) Storing a true/false flag b) Storing a timestamp with timezone information c) Storing Japanese text (double-byte characters) d) Storing 500 bytes of raw binary data (a hash value)

Exercise 11 — CHAR vs. VARCHAR Trade-offs [Intermediate]

The Meridian Bank CUSTOMER table has 2 million rows. A developer proposes changing the FIRST_NAME column from VARCHAR(50) to CHAR(50) "for simplicity."

a) Calculate the approximate additional storage cost if the average first name is 6 characters long. b) Beyond storage, what other performance implications might this have? c) Under what circumstances would CHAR(n) be a better choice than VARCHAR(n)?

Section C: Normalization (Exercises 12-20)

Exercise 12 — Identifying Normal Form Violations [Beginner]

For each table, identify the highest normal form it satisfies and explain what violation prevents it from reaching the next level:

Table A:

ORDER (ORDER_ID, CUSTOMER_NAME, ITEMS_ORDERED)
-- ITEMS_ORDERED contains: "Widget, Gadget, Sprocket"

Table B:

ORDER_ITEM (ORDER_ID, ITEM_ID, ITEM_NAME, ITEM_CATEGORY, QUANTITY)
-- Primary Key: (ORDER_ID, ITEM_ID)
-- ITEM_NAME depends on ITEM_ID alone

Table C:

EMPLOYEE (EMP_ID, EMP_NAME, DEPT_ID, DEPT_NAME, DEPT_LOCATION)
-- Primary Key: EMP_ID
-- DEPT_NAME depends on DEPT_ID, not on EMP_ID directly

Exercise 13 — Normalize a Sales Table [Intermediate]

Normalize the following table to 3NF. Show each step (1NF, 2NF, 3NF) and the tables produced at each stage.

SALES_RECORD
---
SALE_ID
SALE_DATE
CUSTOMER_ID
CUSTOMER_NAME
CUSTOMER_CITY
CUSTOMER_STATE
PRODUCT_IDS        -- "P001, P002, P003" (comma-separated)
PRODUCT_NAMES      -- "Widget, Gadget, Sprocket"
QUANTITIES         -- "2, 1, 5"
UNIT_PRICES        -- "9.99, 24.99, 4.99"
SALESPERSON_ID
SALESPERSON_NAME
SALESPERSON_REGION
REGION_MANAGER

Exercise 14 — Meridian Bank: Normalize the Loan Table [Intermediate]

A Meridian Bank analyst hands you this spreadsheet extract for loan data:

LOAN_ID | CUSTOMER_NAME | CUSTOMER_SSN | LOAN_TYPE | LOAN_TYPE_DESC | INTEREST_RATE | COLLATERAL_DESC | COLLATERAL_VALUE | BRANCH_NAME | BRANCH_CITY | OFFICER_NAME | OFFICER_PHONE
L001    | Margaret Chen | 123-45-6789  | MTG       | Mortgage       | 6.250         | 123 Oak St      | 450000.00       | Downtown    | Springfield | John Adams   | 555-0150
L001    | Margaret Chen | 123-45-6789  | MTG       | Mortgage       | 6.250         | Lot 45 Elm Rd   | 125000.00       | Downtown    | Springfield | John Adams   | 555-0150
L002    | Robert Kim    | 987-65-4321  | AUTO      | Auto Loan      | 4.500         | 2024 Honda Civic| 28000.00        | Westside    | Springfield | Jane Baker   | 555-0160

a) Identify all anomalies in this flat structure. b) Normalize to 3NF. Show your tables, columns, primary keys, and foreign keys. c) Write DB2 CREATE TABLE statements for each table.

Exercise 15 — Anomaly Identification [Beginner]

For each scenario, identify whether it is an update anomaly, insert anomaly, or delete anomaly:

a) You cannot add a new department to the company database until at least one employee is assigned to it. b) A customer changes their phone number, but the change is applied to only 3 of their 7 order rows. c) You delete the last order for a product, and you lose all information about that product (name, description, price). d) A branch manager changes, and you must update 15,000 account rows. e) You want to record a new course offering, but cannot do so until a student enrolls.

Exercise 16 — Functional Dependency Analysis [Advanced]

Given the following functional dependencies for a table SCHEDULE:

STUDENT_ID, COURSE_ID → INSTRUCTOR_ID, ROOM, GRADE
COURSE_ID → INSTRUCTOR_ID, ROOM
INSTRUCTOR_ID → ROOM

a) What is the primary key? b) Identify all partial dependencies. c) Identify all transitive dependencies. d) Decompose into BCNF. e) Is there any information lost in the decomposition? Why or why not?

Exercise 17 — BCNF vs. 3NF [Advanced]

Consider a table tracking which doctors treat which patients at a multi-location clinic:

TREATMENT (PATIENT_ID, DOCTOR_NAME, CLINIC_ID)

Assumptions: - Each patient sees exactly one doctor (PATIENT_ID → DOCTOR_NAME). - Each doctor works at exactly one clinic (DOCTOR_NAME → CLINIC_ID). - Doctor names are unique across all clinics.

a) List all candidate keys. b) Is this table in 3NF? Why or why not? c) Is this table in BCNF? Why or why not? d) If not in BCNF, decompose it.

Exercise 18 — Design a Schema from Requirements [Advanced]

Meridian Bank wants to track employee training. Requirements:

• Employees take training courses.
• Each course has a name, description, duration (hours), and a required certification level.
• Employees can take the same course multiple times (for recertification).
• Each completion is recorded with a date and a pass/fail result.
• Each course may have prerequisites (other courses that must be completed first).
• Employees belong to departments. Each department has a training coordinator.

Design a fully normalized (3NF) schema. Provide: a) An entity list with primary keys. b) A relationship diagram. c) DB2 CREATE TABLE statements. d) Justification for any design decisions that could be debated.

Exercise 19 — When to Denormalize [Intermediate]

For each scenario, decide whether denormalization is justified. Explain your reasoning.

a) A reporting dashboard queries CUSTOMER JOIN ACCOUNT JOIN BRANCH 50,000 times per day. The branch name is needed in every query, but branches are renamed approximately once every 3 years.

b) An audit report needs the total transaction amount per account per day. The TRANSACTION table has 500 million rows, and the report runs nightly.

c) A developer wants to store the customer's full name (FIRST_NAME + ' ' + LAST_NAME) as a separate column to avoid string concatenation in queries.

d) An online banking application needs to display the last 5 transactions for an account. The TRANSACTION table is very large, and the query is slow.

Exercise 20 — Denormalization Impact Analysis [Advanced]

Meridian Bank has denormalized by storing BRANCH_NAME in the ACCOUNT table (in addition to BRANCH_ID, which still references the BRANCH table).

a) Write a DB2 trigger that keeps BRANCH_NAME in ACCOUNT synchronized when a branch is renamed. b) What happens if the trigger fails? Describe the data inconsistency scenario. c) Propose an alternative to the trigger approach (e.g., MQT, application logic, batch reconciliation). d) Draft a DB2 query that checks for inconsistencies between the denormalized BRANCH_NAME in ACCOUNT and the authoritative BRANCH_NAME in BRANCH.

Section D: Relational Algebra and Querying (Exercises 21-25)

Exercise 21 — Operation Identification [Beginner]

For each SQL fragment, identify the relational algebra operation:

a) SELECT * FROM CUSTOMER WHERE STATE_CODE = 'IL' b) SELECT FIRST_NAME, LAST_NAME FROM CUSTOMER c) SELECT * FROM CUSTOMER c JOIN ACCOUNT a ON c.CUSTOMER_ID = a.CUSTOMER_ID d) SELECT CUSTOMER_ID FROM ACCOUNT EXCEPT SELECT CUSTOMER_ID FROM CUSTOMER WHERE CUSTOMER_STATUS = 'C'

Exercise 22 — JOIN Type Selection [Intermediate]

For each Meridian Bank query requirement, choose the most appropriate JOIN type (INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER) and explain why:

a) List all customers and their accounts. Only include customers who have at least one account. b) List all customers, showing their accounts if they have any. Include customers with no accounts (show NULLs for account columns). c) Find all branches that have no accounts. d) Produce a complete list of all customer-branch combinations, showing accounts where they exist and NULLs where they do not.

Exercise 23 — Cartesian Product Disaster [Beginner]

A developer writes:

SELECT c.CUSTOMER_NAME, a.ACCOUNT_NUMBER
FROM CUSTOMER c, ACCOUNT a;

a) What kind of join is this? (Hint: there is no WHERE or ON clause.) b) If CUSTOMER has 2 million rows and ACCOUNT has 5 million rows, how many rows does this query return? c) What is the correct way to write this query?

Exercise 24 — Set Operations at Meridian [Intermediate]

Write SQL using set operations (UNION, INTERSECT, EXCEPT) for each requirement:

a) Find all customer IDs who have a savings account but NOT a checking account. b) Combine a list of all active branch IDs with all branch IDs that have at least one account (remove duplicates). c) Find customer IDs that appear in both the CUSTOMER table and the ACCOUNT table (i.e., customers who have at least one account — using INTERSECT, not JOIN).

Exercise 25 — Query Translation [Advanced]

Translate each relational algebra expression into DB2 SQL:

a) σ(ACCOUNT_TYPE = 'SAV' AND BALANCE > 10000)(ACCOUNT)

b) π(CUSTOMER_ID, LAST_NAME, CITY)(CUSTOMER)

c) CUSTOMER ⋈(CUSTOMER.CUSTOMER_ID = ACCOUNT.CUSTOMER_ID) ACCOUNT

d) π(CUSTOMER_ID)(σ(ACCOUNT_TYPE = 'CHK')(ACCOUNT)) ∩ π(CUSTOMER_ID)(σ(ACCOUNT_TYPE = 'SAV')(ACCOUNT))

Section E: Constraints and Integrity (Exercises 26-30)

Exercise 26 — Write the Constraints [Intermediate]

For the Meridian Bank TRANSACTION table, write appropriate constraints for each column:

CREATE TABLE MERIDIAN.TRANSACTION (
    TRANSACTION_ID        INTEGER NOT NULL,
    ACCOUNT_ID            INTEGER NOT NULL,
    TRANSACTION_TYPE      CHAR(3),       -- 'DEP', 'WDR', 'TRF', 'FEE', 'INT'
    AMOUNT                DECIMAL(15,2),  -- Must be positive
    TRANSACTION_TIMESTAMP TIMESTAMP(6),   -- Must not be in the future
    DESCRIPTION           VARCHAR(200),
    TELLER_ID             INTEGER,        -- May be NULL for ATM/online transactions
    -- Add constraints here
);

Write: PRIMARY KEY, FOREIGN KEY (to ACCOUNT), CHECK constraints for TRANSACTION_TYPE and AMOUNT, and NOT NULL where appropriate.

Exercise 27 — Constraint Violation Scenarios [Intermediate]

For each SQL statement, predict whether DB2 will accept or reject it, and name the constraint that applies:

a) INSERT INTO MERIDIAN.ACCOUNT (ACCOUNT_ID, ACCOUNT_NUMBER, CUSTOMER_ID, BRANCH_ID, ACCOUNT_TYPE) VALUES (1, 'ACC000000001', 99999, 1, 'CHK'); — assuming CUSTOMER_ID 99999 does not exist.

b) INSERT INTO MERIDIAN.CUSTOMER (CUSTOMER_ID, SSN, FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, ADDRESS_LINE1, CITY, STATE_CODE, ZIP_CODE) VALUES (DEFAULT, '123-45-6789', 'John', 'Doe', '1990-05-15', '100 Main St', 'Springfield', 'IL', '62701'); — assuming SSN '123-45-6789' already exists.

c) UPDATE MERIDIAN.ACCOUNT SET ACCOUNT_TYPE = 'XYZ' WHERE ACCOUNT_ID = 1;

d) DELETE FROM MERIDIAN.CUSTOMER WHERE CUSTOMER_ID = 10001; — assuming customer 10001 has 3 active accounts.

Exercise 28 — Designing Business Rule Constraints [Advanced]

Meridian Bank has these business rules. For each, design a DB2 constraint (or explain why a constraint alone is insufficient):

a) An account's closed date must be after its opened date. b) A customer must be at least 18 years old to open a checking account. c) No branch can have more than 50,000 accounts. d) A customer's total balance across all accounts must not exceed $10,000,000.

Exercise 29 — Referential Integrity Repair [Intermediate]

You inherit a database that was originally built without foreign key constraints. You want to add them.

a) Write a query to find all ACCOUNT rows where CUSTOMER_ID does not exist in the CUSTOMER table. b) Write a query to find all ACCOUNT rows where BRANCH_ID does not exist in the BRANCH table. c) What strategies could you use to fix the orphan rows before adding the foreign key constraints? d) Write the ALTER TABLE statement to add the foreign key constraint.

Exercise 30 — The Great Constraint Debate [Challenge]

Two Meridian Bank DBAs disagree:

DBA A says: "We should enforce all business rules in the database using constraints, triggers, and stored procedures. The database is the single source of truth."

DBA B says: "Business rules should be in the application layer. The database should just store data. Constraints slow down inserts and make deployments harder."

Write a 300-500 word argument for each position. Then state which approach you recommend for Meridian Bank and why. Consider: performance, data integrity, multiple applications accessing the same database, regulatory requirements, operational complexity.

Section F: Design Challenges (Exercises 31-35)

Exercise 31 — Design from Scratch: Library System [Intermediate]

Design a normalized (3NF) schema for a small public library: - Books have titles, authors (multiple possible), ISBN, publisher, year, genre. - Members have names, addresses, membership dates, membership types. - Members borrow books. Track checkout date, due date, return date. - Some books have multiple copies.

Provide: Entity list, relationship diagram, CREATE TABLE statements, and constraint list.

Exercise 32 — Meridian Bank: Customer Contact Information [Intermediate]

Meridian Bank needs to store multiple phone numbers, email addresses, and mailing addresses per customer. The current CUSTOMER table has single columns for these.

a) Why does the current design violate 1NF (or does it)? b) Design additional tables to support multiple contact methods per customer. c) How would you indicate which phone number is the "primary" contact? d) Write the DB2 DDL for your new tables.

Exercise 33 — Meridian Bank: Transaction History [Advanced]

Design the TRANSACTION table for Meridian Bank with these requirements: - Each transaction is linked to exactly one account. - Transaction types: deposit, withdrawal, transfer, fee, interest credit. - For transfers, the transaction must reference both the source and destination accounts. - Each transaction has a timestamp, amount, description, and running balance. - Transactions cannot be deleted — only reversed (by a new transaction of the opposite type). - ATM transactions should store the ATM location.

Provide: CREATE TABLE statement with all constraints, an explanation of how transfers are modeled, and a discussion of whether "running balance" should be stored or computed.

Exercise 34 — Schema Review [Advanced]

A colleague presents this schema for a car dealership:

CREATE TABLE CAR (
    VIN VARCHAR(50),
    MAKE VARCHAR(50),
    MODEL VARCHAR(50),
    YEAR INTEGER,
    COLOR VARCHAR(20),
    PRICE DOUBLE,
    SALESPERSON_NAME VARCHAR(100),
    SALESPERSON_PHONE VARCHAR(20),
    CUSTOMER_NAME VARCHAR(100),
    CUSTOMER_PHONE VARCHAR(20),
    SALE_DATE DATE
);

Identify at least 8 problems with this design, covering: normalization, data types, keys, constraints, naming conventions, and missing elements.

Exercise 35 — Full Meridian Bank ERD [Challenge]

Expand the Meridian Bank schema to include ALL of the following entities. Design a fully normalized schema in 3NF. Provide a relationship diagram and CREATE TABLE statements for each table.

Entities: - BRANCH (from this chapter) - CUSTOMER (from this chapter) - ACCOUNT (from this chapter) - TRANSACTION - EMPLOYEE (works at a branch, may manage the branch) - LOAN (a type of account with additional attributes) - CREDIT_CARD (a type of account with credit limit, expiry) - CUSTOMER_CONTACT (multiple phone/email per customer) - ACCOUNT_SIGNER (joint accounts — multiple customers per account)

Constraints to enforce: - An employee can manage at most one branch. - A branch has exactly one manager (an employee). - Every account has at least one signer. - Credit cards have expiration dates that must be in the future (at time of creation). - Loan interest rates must be between 0 and 30 percent.

This exercise is intentionally large. Budget 60-90 minutes.

Answer Key Availability

Detailed solutions for exercises marked [Beginner] and [Intermediate] are available in the companion solutions guide. [Advanced] and [Challenge] exercises are designed for discussion with instructors or study groups — there are multiple valid approaches, and the design process is as valuable as the final answer.