Chapter 11 Exercises: Data Definition Language

These exercises reinforce and extend the DDL concepts from Chapter 11. They progress from foundational recall through hands-on creation to advanced design analysis. For exercises that ask you to write DDL, test your statements against a DB2 instance if you have one available — syntax errors are best caught by the database engine, not by staring at paper.

Difficulty Ratings: - Beginner — Recall and comprehension. If you read the chapter, you can answer these. - Intermediate — Application and analysis. Requires writing DDL and thinking about trade-offs. - Advanced — Synthesis and evaluation. Requires multi-table design, platform-specific knowledge, or operational judgment.

Part A: CREATE TABLE Fundamentals

Exercise 11.1 — Data Type Selection (Beginner)

For each of the following business attributes, specify the most appropriate DB2 data type and justify your choice in one sentence:

  1. A customer's Social Security Number (always 9 digits, often stored with hyphens)
  2. An account balance that can reach $10 billion with cents
  3. A flag indicating whether an account is active
  4. The number of employees at a branch (never more than 500)
  5. A customer's date of birth
  6. A free-text note about a customer interaction (could be up to 50,000 characters)
  7. A unique transaction identifier for a system processing 200 million transactions per year
  8. A two-letter U.S. state code
  9. The timestamp when a row was last modified
  10. An XML document representing a SWIFT payment message

Exercise 11.2 — Constraint Identification (Beginner)

Examine the following table and identify each constraint by name and type (PK, FK, UNIQUE, CHECK, NOT NULL). Then identify one additional constraint that should exist but is missing.

CREATE TABLE order_detail (
    order_id       INTEGER       NOT NULL,
    line_number    SMALLINT      NOT NULL,
    product_id     INTEGER       NOT NULL,
    quantity       INTEGER       NOT NULL,
    unit_price     DECIMAL(10,2) NOT NULL,
    discount_pct   DECIMAL(5,2)  DEFAULT 0.00,
    CONSTRAINT pk_order_detail PRIMARY KEY (order_id, line_number),
    CONSTRAINT fk_od_order FOREIGN KEY (order_id)
        REFERENCES orders (order_id) ON DELETE CASCADE,
    CONSTRAINT fk_od_product FOREIGN KEY (product_id)
        REFERENCES product (product_id) ON DELETE RESTRICT,
    CONSTRAINT chk_quantity CHECK (quantity > 0),
    CONSTRAINT chk_discount CHECK (discount_pct BETWEEN 0.00 AND 100.00)
);

Exercise 11.3 — Write a CREATE TABLE (Intermediate)

Write the complete CREATE TABLE statement for an EMPLOYEE table at Meridian National Bank with the following requirements:

  • Employee ID: auto-generated integer, always generated by the system
  • Social Security Number: unique, required
  • First name, last name: required, variable length up to 50 characters
  • Hire date: required, defaults to today
  • Salary: required, up to $999,999.99
  • Department code: required, must be one of 'IT', 'OPS', 'FIN', 'HR', 'MGT', 'SEC'
  • Manager employee ID: optional, references the same table
  • Employment status: required, default 'A', must be 'A' (active), 'T' (terminated), or 'L' (leave)
  • Row created and updated timestamps

Include all appropriate constraints with descriptive names. Place the table in the MERIDIAN schema.

Exercise 11.4 — ON DELETE Behavior (Intermediate)

For each of the following foreign key relationships in the Meridian Bank schema, specify which ON DELETE rule you would choose and explain your reasoning:

  1. ACCOUNT.branch_id references BRANCH.branch_id
  2. TRANSACTION.account_id references ACCOUNT.account_id
  3. ACCOUNT_HOLDER.customer_id references CUSTOMER.customer_id
  4. ACCOUNT_HOLDER.account_id references ACCOUNT.account_id
  5. LOAN_PAYMENT.loan_id references LOAN.loan_id
  6. CARD.account_id references ACCOUNT.account_id

Exercise 11.5 — Constraint Violation Scenarios (Intermediate)

For each of the following INSERT statements against the Meridian CUSTOMER table (as defined in Section 11.1), predict the exact SQLSTATE error code and the constraint that is violated. If the INSERT would succeed, say so.

-- Customer table has: customer_id (IDENTITY), tax_id (UNIQUE, NOT NULL),
-- first_name (NOT NULL), last_name (NOT NULL), date_of_birth (NOT NULL),
-- customer_status (CHECK: A/I/C/S, DEFAULT 'A')

-- Statement A
INSERT INTO meridian.customer (tax_id, first_name, last_name, date_of_birth)
VALUES ('123-45-6789', 'Alice', 'Smith', '1990-05-15');

-- Statement B
INSERT INTO meridian.customer (tax_id, first_name, last_name, date_of_birth)
VALUES ('123-45-6789', 'Bob', 'Jones', '1985-11-20');

-- Statement C
INSERT INTO meridian.customer (customer_id, tax_id, first_name, last_name, date_of_birth)
VALUES (999, '999-99-9999', 'Carol', 'Davis', '1975-03-10');

-- Statement D
INSERT INTO meridian.customer (tax_id, first_name, last_name, date_of_birth, customer_status)
VALUES ('555-55-5555', 'Dave', NULL, '2000-01-01', 'A');

-- Statement E
INSERT INTO meridian.customer (tax_id, first_name, last_name, date_of_birth, customer_status)
VALUES ('777-77-7777', 'Eve', 'Wilson', '1988-07-22', 'Z');

Part B: Tablespace and Physical Design

Exercise 11.6 — z/OS Storage Hierarchy (Intermediate)

Write the complete DDL to create the following z/OS storage infrastructure for Meridian Bank's transaction processing:

  1. A storage group named TRNGRP using volumes VOL010, VOL011, VOL012
  2. A database named TRNDB in that storage group
  3. A partition-by-range tablespace named TRNTS for the TRANSACTION table with: COMPRESS YES, LOCKSIZE ROW, buffer pool BP1, DSSIZE 4G, SEGSIZE 32
  4. A separate tablespace named TRNTYPES for the TRANSACTION_TYPE reference table with: LOCKSIZE PAGE (it is rarely updated), buffer pool BP0

Exercise 11.7 — LUW Tablespace Design (Intermediate)

A new Meridian Bank application requires three types of tablespaces on LUW:

  1. A high-performance tablespace for the TRANSACTION table (16K pages, large extent size)
  2. A general-purpose tablespace for reference data (8K pages, moderate extent size)
  3. A temporary tablespace for sort operations

Write the CREATE TABLESPACE statements for all three. Justify your page size and extent size choices.

Exercise 11.8 — Platform Comparison Table (Beginner)

Without looking at the chapter, fill in this comparison table from memory:

Feature z/OS LUW
Storage hierarchy levels ? ?
Default tablespace management ? ?
BOOLEAN data type support ? ?
LOB storage location ? ?
Maximum schema name length ? ?
Partition-by-growth support ? ?
Table partitioning mechanism ? ?

Part C: Indexes

Exercise 11.9 — Index Design (Intermediate)

For the Meridian Bank TRANSACTION table (columns: transaction_id, account_id, transaction_date, transaction_type, amount, description, status, created_at), design the following indexes:

  1. Primary key index
  2. Clustering index (justify your column choice)
  3. An index to support the query: SELECT * FROM transaction WHERE account_id = ? AND transaction_date BETWEEN ? AND ?
  4. An index with INCLUDE columns to enable index-only access for the query: SELECT transaction_date, amount, status FROM transaction WHERE account_id = ?

Write the CREATE INDEX statements for each.

Exercise 11.10 — Index Trade-Off Analysis (Advanced)

A colleague proposes adding the following 8 indexes to the CUSTOMER table (columns: customer_id, tax_id, first_name, last_name, email_address, phone_primary, date_of_birth, customer_status, customer_since, credit_score):

  1. ix_cust_pk ON (customer_id) -- PK
  2. ix_cust_taxid ON (tax_id) -- UNIQUE
  3. ix_cust_email ON (email_address) -- UNIQUE
  4. ix_cust_lastname ON (last_name, first_name)
  5. ix_cust_phone ON (phone_primary)
  6. ix_cust_dob ON (date_of_birth)
  7. ix_cust_status ON (customer_status)
  8. ix_cust_score ON (credit_score)

Evaluate each index. Which would you keep, which would you remove, and why? Consider cardinality, query patterns, and INSERT overhead.

Part D: ALTER TABLE

Exercise 11.11 — Safe ALTER Operations (Intermediate)

Classify each of the following ALTER TABLE operations as "safe" (catalog-only, no data movement), "requires REORG" (z/OS), or "requires table rebuild":

  1. ADD COLUMN middle_name VARCHAR(50)
  2. ALTER COLUMN email_address SET DATA TYPE VARCHAR(200) (was VARCHAR(100))
  3. ALTER COLUMN customer_status SET DATA TYPE VARCHAR(5) (was CHAR(1))
  4. ADD CONSTRAINT chk_email CHECK (email_address LIKE '%@%')
  5. DROP COLUMN phone_secondary
  6. ALTER COLUMN first_name SET DATA TYPE VARCHAR(30) (was VARCHAR(50) -- decreasing)

Exercise 11.12 — Schema Evolution Script (Advanced)

Meridian Bank needs the following changes to their production CUSTOMER table:

  1. Add a preferred_language column (CHAR(2), default 'EN', must be a valid ISO 639-1 code from a list of 10 supported languages)
  2. Increase the email_address column from VARCHAR(100) to VARCHAR(200)
  3. Add a UNIQUE constraint on email_address (allowing NULLs)
  4. Add a last_login TIMESTAMP column that defaults to NULL
  5. Drop the deprecated fax_number column

Write the complete ALTER TABLE script. Include comments explaining the operational considerations for each change (locking, REORG requirements, data validation).

Part E: DROP and Safety

Exercise 11.13 — DROP Dependency Analysis (Intermediate)

The following objects exist in the Meridian schema:

  • Table: CUSTOMER
  • Table: ACCOUNT_HOLDER (FK to CUSTOMER, FK to ACCOUNT)
  • Table: ACCOUNT (FK from ACCOUNT_HOLDER, FK from TRANSACTION)
  • View: V_CUSTOMER_ACCOUNTS (joins CUSTOMER, ACCOUNT_HOLDER, ACCOUNT)
  • Index: IX_CUST_NAME on CUSTOMER
  • Alias: CUST for CUSTOMER

If you execute DROP TABLE meridian.customer, describe what happens to each dependent object on: 1. DB2 for z/OS 2. DB2 for LUW

Exercise 11.14 — DROP Recovery Plan (Advanced)

At 2:15 PM on a Tuesday, a junior DBA accidentally executes DROP TABLE meridian.transaction on the production z/OS system. The table contained 450 million rows. The last full image copy was taken at 2:00 AM. The last incremental copy was at noon.

Write a recovery plan that includes: 1. Immediate actions (first 5 minutes) 2. Recovery steps using z/OS utilities 3. Data verification after recovery 4. Post-incident process improvements

Part F: Sequences, Identity, and Generated Columns

Exercise 11.15 — Sequence vs. Identity Decision (Intermediate)

For each of the following scenarios, decide whether a sequence or an identity column is more appropriate and explain why:

  1. A customer_id primary key for the CUSTOMER table
  2. A transaction reference number that must be unique across all transaction-related tables (TRANSACTION, PENDING_TRANSACTION, ARCHIVED_TRANSACTION)
  3. An invoice number that customers see and reference in communications
  4. A surrogate key for a junction table (ACCOUNT_HOLDER)
  5. An audit trail entry ID that must be strictly monotonically increasing (no gaps)

Exercise 11.16 — Generated Column Design (Intermediate)

Design generated columns for the following requirements:

  1. A full_address column that concatenates street, city, state, and zip_code with appropriate formatting
  2. A hash_key column that produces a consistent hash of customer_id and account_id for use in data comparison
  3. An age_at_account_opening column derived from date_of_birth and account_open_date
  4. A risk_category column that returns 'HIGH', 'MEDIUM', or 'LOW' based on credit_score ranges

Write the column definitions with the GENERATED ALWAYS AS clause.

Part G: Comprehensive Design

Exercise 11.17 — Design a Schema from Requirements (Advanced)

A Meridian Bank business analyst provides these requirements for a new credit card rewards system:

  • Customers can enroll in one or more rewards programs
  • Each program has a name, point multiplier, annual fee, and status
  • Customers earn points for each qualifying transaction
  • Points can be redeemed for statement credits, gift cards, or travel
  • Redemptions must be tracked with date, amount, type, and approval status
  • Program terms can change; historical terms must be preserved

Design the complete DDL for this subsystem: tables, constraints, indexes, and sequences. Write production-quality DDL with named constraints, appropriate data types, and explanatory comments. Estimate row volumes and choose INTEGER vs. BIGINT accordingly.

Exercise 11.18 — DDL Review (Advanced)

Review the following DDL written by a junior developer. Identify at least 10 problems (errors, anti-patterns, missing elements, or suboptimal choices) and rewrite the corrected version.

CREATE TABLE TRANSACTIONS (
    id              INT,
    acct_num        VARCHAR(4000),
    txn_date        VARCHAR(10),
    amt             FLOAT,
    txn_type        VARCHAR(100),
    status          VARCHAR(20) DEFAULT 'pending',
    notes           VARCHAR(32000),
    PRIMARY KEY (id)
);

Exercise 11.19 — Cross-Platform DDL Translation (Advanced)

Take the Meridian Bank CUSTOMER table DDL (as written for LUW in Section 11.1) and translate it to production-quality DB2 for z/OS DDL. Include:

  1. The IN clause specifying database and tablespace
  2. All necessary z/OS-specific parameters
  3. Conversion of any LUW-only features (e.g., BOOLEAN)
  4. ROW CHANGE TIMESTAMP column
  5. Explicit CREATE INDEX statements for the primary key and unique constraint

Exercise 11.20 — Impact Analysis (Advanced)

The Meridian Bank data architect proposes changing the TRANSACTION table's transaction_id column from INTEGER to BIGINT. The table currently has 200 million rows, 12 indexes, and 15 dependent views. It runs on z/OS with a partition-by-range tablespace with 64 partitions.

Write a detailed impact analysis that covers: 1. Why the change might be necessary 2. The steps required to make the change 3. The estimated downtime 4. The impact on dependent objects 5. An alternative approach that avoids the change

Part H: Integration and Spaced Review

Exercise 11.21 — Spaced Review: SELECT and DDL (Intermediate)

Write a query against the DB2 catalog tables to find all tables in the MERIDIAN schema that have no primary key constraint. (Hint: use SYSIBM.SYSTABLES and SYSIBM.SYSCOLUMNS or SYSIBM.SYSINDEXES on z/OS; SYSCAT.TABLES and SYSCAT.TABCONST on LUW.)

Exercise 11.22 — Spaced Review: INSERT and Constraints (Intermediate)

Write an INSERT statement that populates all 50 rows of the TRANSACTION_TYPE reference table. Use a single INSERT with a VALUES clause (DB2 supports multi-row INSERT). Ensure that every row satisfies the table's constraints.

Exercise 11.23 — Spaced Review: Joins and Schema (Intermediate)

Using the complete Meridian Bank schema from Section 11.12, write a query that joins CUSTOMER, ACCOUNT_HOLDER, ACCOUNT, and TRANSACTION to produce a report showing each customer's total transaction amount by account type for the current month. Verify that your join conditions align with the foreign key definitions in the DDL.

Exercise 11.24 — DDL Script Ordering (Intermediate)

The Meridian Bank DDL script creates 13 tables. The tables have foreign key dependencies on each other. Write out the correct order in which the CREATE TABLE statements must execute so that every FOREIGN KEY reference can be resolved. Identify any circular dependencies and explain how to handle them.

Exercise 11.25 — Full Schema Build and Verify (Advanced)

Execute the complete Meridian Bank DDL script (code/meridian-complete-ddl.sql) against your DB2 instance. Then:

  1. Query the catalog to verify all tables were created
  2. Query the catalog to list all foreign key constraints and their parent-child relationships
  3. Attempt to insert a row into TRANSACTION without a matching ACCOUNT row — verify the FK violation
  4. Attempt to insert a CUSTOMER with a duplicate tax_id — verify the UNIQUE violation
  5. Drop all objects in reverse order and verify cleanup

Document any errors you encounter and how you resolved them.

Return to Chapter 11 | Continue to Quiz