Chapter 13 Exercises: Logical Database Design
These exercises progress from foundational recall through complex design challenges. Difficulty ratings: [Beginner], [Intermediate], [Advanced], [Challenge].
Section A: Entities and Attributes (Exercises 1-8)
Exercise 1 — Entity Identification from Narrative [Beginner]
Read the following business description and identify all candidate entities:
"Meridian National Bank operates 50 branches across three states. Each branch offers a selection of financial products. Customers open accounts at branches. Each account is associated with a specific product type. Customers may hold multiple accounts, and some accounts (such as joint checking) may be held by multiple customers. Employees work at branches. Some employees are designated as loan officers and are assigned to manage specific loans. The bank tracks every transaction on every account for regulatory and reporting purposes."
a) List every candidate entity you identified. b) For each entity, write one sentence describing what it represents. c) Identify which entities might be "weak" (dependent on another entity for identification).
Exercise 2 — Attribute Classification [Beginner]
For the EMPLOYEE entity, classify each of the following as simple, composite, derived, or multi-valued:
a) Employee ID b) Full name (first, middle, last) c) Years of service (calculated from hire date) d) Phone numbers (work, personal, mobile) e) Email address f) Age (calculated from date of birth) g) Mailing address (street, city, state, zip) h) Certifications held
Exercise 3 — Domain Definition [Intermediate]
Define precise domains for the following attributes at Meridian National Bank. For each, specify: (1) the data type, (2) the set or range of valid values, and (3) whether NULL is permitted.
a) ACCOUNT.STATUS b) TRANSACTION.AMOUNT c) CUSTOMER.DATE_OF_BIRTH d) BRANCH.STATE_CODE e) LOAN.INTEREST_RATE f) EMPLOYEE.TERMINATION_DATE
Exercise 4 — Primary Key Selection [Intermediate]
A colleague proposes the following primary keys. For each, evaluate the choice and suggest an alternative if appropriate. Justify your reasoning.
a) CUSTOMER table: Primary key = SSN (Social Security Number) b) TRANSACTION table: Primary key = (ACCOUNT_ID, TRANSACTION_DATE, AMOUNT) c) BRANCH table: Primary key = BRANCH_NAME d) EMPLOYEE table: Primary key = EMPLOYEE_ID (auto-generated integer) e) LOAN_PAYMENT table: Primary key = (LOAN_ID, PAYMENT_DATE)
Exercise 5 — Discovering Hidden Entities [Intermediate]
A junior developer has designed the following table:
LOAN
----
LOAN_ID (PK)
ACCOUNT_ID (FK)
LOAN_TYPE
ORIGINAL_AMOUNT
INTEREST_RATE
COLLATERAL_TYPE
COLLATERAL_DESCRIPTION
COLLATERAL_VALUE
COLLATERAL_APPRAISAL_DATE
COLLATERAL_APPRAISED_BY
CO_BORROWER_NAME
CO_BORROWER_SSN
CO_BORROWER_ADDRESS
CO_BORROWER_PHONE
a) Identify the normalization violations in this design. b) What hidden entities are embedded in this table? c) Redesign the table(s) to eliminate the violations.
Exercise 6 — Derived vs. Stored Attributes [Intermediate]
For each of the following attributes, determine whether it should be stored or derived (calculated at query time). If stored, explain what mechanism keeps it in sync. If derived, write the SQL expression that calculates it.
a) CUSTOMER.TOTAL_BALANCE (sum of all account balances) b) ACCOUNT.CURRENT_BALANCE c) CUSTOMER.AGE d) LOAN.REMAINING_PAYMENTS (number of payments left) e) BRANCH.TOTAL_DEPOSITS (sum of all account balances at branch) f) TRANSACTION.RUNNING_BALANCE (account balance after this transaction)
Exercise 7 — Multi-Valued Attribute Resolution [Beginner]
A CUSTOMER table contains the following column:
CUSTOMER_INTERESTS VARCHAR(500)
-- Sample values: "checking,savings,mortgage,investment"
a) Why does this violate First Normal Form? b) Design a proper relational solution. c) Write a SQL query against your new design that finds all customers interested in both "mortgage" and "investment."
Exercise 8 — Composite Attribute Decomposition [Beginner]
An existing system stores customer names in a single column:
CUSTOMER_NAME VARCHAR(200)
-- Sample values: "Dr. Margaret Q. Chen-Williams, Jr."
a) What problems arise from storing the full name as a single column? b) Propose a decomposed design. What columns would you create? c) What edge cases does your design need to handle? (Hint: think about international names.)
Section B: Relationships and Cardinality (Exercises 9-16)
Exercise 9 — Cardinality Determination [Beginner]
For each pair of entities, determine the cardinality (1:1, 1:N, or M:N) and state the participation (mandatory or optional) from each side. Justify your choice.
a) COUNTRY and CUSTOMER (where the bank operates in multiple countries) b) EMPLOYEE and PARKING_SPACE (each employee may be assigned one reserved space) c) PATIENT and DOCTOR (in a hospital — each patient has one primary doctor; each doctor has many patients) d) STUDENT and COURSE (university enrollment) e) HUSBAND and WIFE (in a monogamous marriage)
Exercise 10 — Reading ER Diagrams [Beginner]
Given the following Crow's foot diagram snippet (described textually):
DEPARTMENT ──||────────O<── EMPLOYEE
a) Read this relationship in both directions (from DEPARTMENT to EMPLOYEE and from EMPLOYEE to DEPARTMENT). b) Is the relationship identifying or non-identifying? c) What is the minimum number of employees a department can have? d) What is the maximum number of departments an employee can belong to?
Exercise 11 — Relationship Discovery [Intermediate]
A business analyst provides this description:
"Each customer has a credit score that is updated periodically. The bank uses credit scores from three different bureaus: Equifax, Experian, and TransUnion. We need to track the score from each bureau separately, along with the date the score was pulled. A customer may have scores from one, two, or all three bureaus."
a) Identify the entities and relationships. b) Draw the ER diagram (using textual Crow's foot notation). c) Define the attributes for each entity. d) Is there a many-to-many relationship here? Why or why not?
Exercise 12 — Self-Referencing Relationships [Intermediate]
Design the data model for the following self-referencing scenarios:
a) Employee hierarchy: Each employee reports to one manager (who is also an employee). The CEO reports to no one.
b) Account referral: Some accounts were opened as referrals from existing accounts. Track which account referred which.
c) Transaction links: Transfer transactions involve two accounts (source and destination). Model a transaction that references both.
For each, show the entity with its attributes and explain how the self-reference is implemented.
Exercise 13 — Identifying vs. Non-Identifying Relationships [Intermediate]
For each relationship, determine whether it is identifying or non-identifying. Explain your reasoning.
a) LOAN to LOAN_PAYMENT (payments are numbered sequentially within a loan) b) CUSTOMER to ACCOUNT (an account exists independently) c) ORDER to ORDER_LINE (a line item has no meaning without its order) d) BRANCH to EMPLOYEE (an employee exists independently of a branch) e) INVOICE to INVOICE_LINE_ITEM
Exercise 14 — Junction Table Design [Advanced]
Design a junction table for each M:N relationship. Include attributes that describe the relationship, not just the foreign keys.
a) EMPLOYEE and SKILL (employees have skills with proficiency levels) b) ACCOUNT and SERVICE (accounts subscribe to optional services like overdraft protection, paper statements) c) CUSTOMER and COMMUNICATION_PREFERENCE (customers opt in/out of communication channels for different message types)
Exercise 15 — Optional vs. Mandatory Participation [Intermediate]
For the Meridian Bank model, determine whether each side of the relationship has mandatory or optional participation. Explain the business rule that drives your answer.
a) CUSTOMER to ACCOUNT (via ACCOUNT_HOLDER) b) ACCOUNT to TRANSACTION c) EMPLOYEE to BRANCH d) LOAN to LOAN_COLLATERAL e) CUSTOMER to ONLINE_PROFILE f) TRANSACTION to EMPLOYEE (teller)
Exercise 16 — Ternary Relationships [Advanced]
A university needs to model the following: "A student takes a course from a specific instructor. The same course can be taught by different instructors in different semesters."
a) Is this a binary relationship or a ternary relationship? Explain. b) Design the data model. How many entities and junction tables do you need? c) What is the primary key of the junction table? d) What additional attributes belong on the junction table?
Section C: Normalization Validation (Exercises 17-22)
Exercise 17 — First Normal Form Violations [Beginner]
Identify the 1NF violation in each table and fix it:
a)
CUSTOMER
--------
CUSTOMER_ID NAME PHONE_NUMBERS
10001 M. Chen 555-1234, 555-5678
10002 J. Smith 555-9012
b)
ORDER
-----
ORDER_ID ITEM1 QTY1 ITEM2 QTY2 ITEM3 QTY3
5001 Pen 10 Paper 20 Ink 5
5002 Desk 1 NULL NULL NULL NULL
Exercise 18 — Second Normal Form Violation [Intermediate]
The following table has composite primary key (ORDER_ID, PRODUCT_ID):
ORDER_LINE
----------
ORDER_ID (PK)
PRODUCT_ID (PK)
ORDER_DATE
CUSTOMER_NAME
PRODUCT_NAME
UNIT_PRICE
QUANTITY
LINE_TOTAL
a) Identify all attributes that violate 2NF (partial dependencies). b) Explain what anomalies these violations cause. c) Decompose the table into 2NF.
Exercise 19 — Third Normal Form Violation [Intermediate]
The following table has primary key EMPLOYEE_ID:
EMPLOYEE
--------
EMPLOYEE_ID (PK)
EMPLOYEE_NAME
DEPARTMENT_ID
DEPARTMENT_NAME
DEPARTMENT_BUDGET
MANAGER_ID
MANAGER_NAME
a) Identify all transitive dependencies. b) Explain the update anomaly that would occur if the department name changes. c) Decompose into 3NF tables.
Exercise 20 — BCNF Analysis [Advanced]
Consider a table COURSE_ASSIGNMENT with functional dependencies:
- {STUDENT, COURSE} -> INSTRUCTOR
- {INSTRUCTOR} -> COURSE (each instructor teaches exactly one course)
COURSE_ASSIGNMENT
-----------------
STUDENT (part of PK)
COURSE (part of PK)
INSTRUCTOR
a) Is this table in 3NF? Explain. b) Is this table in BCNF? Explain. c) If not in BCNF, decompose it. What trade-off results from the decomposition?
Exercise 21 — Normalization Walkthrough [Advanced]
A startup gives you this flat table for a small e-commerce system:
ORDER_DATA
----------
ORDER_ID, ORDER_DATE, CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_EMAIL,
CUSTOMER_CITY, CUSTOMER_STATE, PRODUCT_ID, PRODUCT_NAME, PRODUCT_CATEGORY,
CATEGORY_DESCRIPTION, QUANTITY, UNIT_PRICE, LINE_TOTAL, ORDER_TOTAL,
SHIPPING_ADDRESS, SHIPPING_CITY, SHIPPING_STATE, SHIPPING_ZIP
Normalize this table step by step through 1NF, 2NF, 3NF. Show all intermediate results and identify every functional dependency.
Exercise 22 — Validate the Meridian Bank Model [Advanced]
Take the LOAN entity from Section 13.11 and verify it against 1NF, 2NF, and 3NF. Assume the primary key is LOAN_ID.
a) Are there any 1NF violations? b) Are there any 2NF violations? (Hint: the PK is a single column.) c) Are there any 3NF violations? (Hint: does any non-key attribute depend on another non-key attribute?) d) If you find violations, how would you fix them?
Section D: Design from Requirements (Exercises 23-30)
Exercise 23 — Hotel Reservation System [Intermediate]
Design a logical data model for a hotel chain:
"Guests make reservations at specific hotels for date ranges. Each hotel has rooms of various types (single, double, suite). Room rates vary by room type and season. Guests can add services to their stay (breakfast, parking, spa). Each stay generates a bill."
a) Identify all entities. b) Define relationships with cardinality. c) Specify primary keys and critical attributes for each entity. d) Identify any M:N relationships and design junction tables.
Exercise 24 — University Course Registration [Intermediate]
Design a logical model for university registration:
"Students enroll in courses. Each course has sections taught by instructors in specific classrooms at specific times. Students receive grades for completed courses. Some courses have prerequisites (other courses that must be completed first). Instructors belong to departments."
a) Create the full ER model with at least 7 entities. b) Show all relationships with cardinality. c) Identify the self-referencing relationship and explain how it works.
Exercise 25 — Healthcare Patient Records [Advanced]
Design a logical model for a medical clinic:
"Patients visit the clinic for appointments with doctors. Each appointment results in a diagnosis (possibly multiple) and prescribed medications. Doctors specialize in specific areas. Medications have potential interactions with other medications. Insurance companies cover specific procedures at different rates."
a) Design the complete logical model (minimum 10 entities). b) Identify all M:N relationships. c) Design the medication interaction model (hint: self-referencing M:N). d) How would you model the insurance coverage rates?
Exercise 26 — Anti-Pattern Detection [Intermediate]
Examine each table design and identify the anti-pattern. Propose a fix.
a)
PRODUCT
-------
PRODUCT_ID, PRODUCT_NAME,
TAG1, TAG2, TAG3, TAG4, TAG5
b)
ENTITY_DATA
-----------
ID, ENTITY_TYPE, ATTR1, ATTR2, ATTR3, ATTR4, ATTR5, ATTR6
-- When ENTITY_TYPE='CUSTOMER': ATTR1=Name, ATTR2=DOB, ATTR3=Phone
-- When ENTITY_TYPE='ORDER': ATTR1=OrderDate, ATTR2=Total, ATTR3=Status
c)
CODES
-----
CODE_TYPE, CODE_VALUE, CODE_DESCRIPTION
-- Contains: countries, statuses, account types, transaction types, etc.
d)
CUSTOMER
--------
CUSTOMER_ID, NAME, ADDRESSES
-- ADDRESSES contains JSON: {"home":"123 Main St","work":"456 Oak Ave"}
Exercise 27 — Effective Dating Design [Advanced]
Meridian Bank needs to track interest rate changes for savings accounts. The business rules are:
- Each savings account has an interest rate that can change over time.
- When the rate changes, the old rate must be preserved with its date range.
- The system must be able to determine the applicable rate for any historical date.
- Rate changes take effect at the start of a business day.
- No two rates can be in effect for the same account on the same day.
a) Design the entity (or entities) needed. b) Write the DDL including CHECK constraints that prevent overlapping date ranges. c) Write a query that returns the interest rate applicable to account 1000042 on 2024-06-15. d) Write the SQL to add a new rate of 3.25% effective 2024-07-01 for account 1000042, including updating the previous rate's end date.
Exercise 28 — Supertype/Subtype Decision [Advanced]
Meridian Bank is introducing three new product categories: Money Market Accounts, Health Savings Accounts (HSA), and Individual Retirement Accounts (IRA). Each has unique attributes:
- Money Market: tiered interest rates, check-writing privileges, minimum balance for each tier
- HSA: annual contribution limit, employer contribution amount, eligible expenses list
- IRA: IRA type (Traditional/Roth), annual contribution limit, beneficiary designation, required minimum distribution age
a) Extend the ACCOUNT supertype/subtype hierarchy to include these three new types. b) For each new subtype, define the specific attributes. c) Which implementation strategy (single table, table-per-type, table-per-concrete-class) would you recommend and why? d) What changes (if any) are needed in the ACCOUNT_TYPE lookup table?
Exercise 29 — Audit Trail Design [Advanced]
The compliance team at Meridian Bank requires:
- Every change to CUSTOMER, ACCOUNT, and LOAN must be tracked.
- The audit trail must record: who made the change, when, from which application, and the before/after values.
- The compliance team must be able to reconstruct the complete state of any customer as of any date.
- Audit records must be immutable — no one can modify or delete them.
a) Design the audit entities. b) Would you use a centralized audit log, shadow tables, or both? Justify. c) How would you enforce immutability on audit records in DB2? (Hint: think about triggers and privileges.) d) Write the trigger DDL that populates the CUSTOMER shadow table on UPDATE.
Exercise 30 — Complete Design Challenge [Challenge]
You are designing the database for a new Meridian Bank feature: loan origination workflow. The business description:
"A customer applies for a loan. The application goes through stages: SUBMITTED, UNDER_REVIEW, CREDIT_CHECK, APPRAISAL (for secured loans), APPROVED, DENIED, COUNTER_OFFERED, ACCEPTED, FUNDED, WITHDRAWN. At each stage, an employee performs an action and may attach documents (pay stubs, tax returns, property appraisals). The system must track the complete history of each application's journey through the workflow, including time spent at each stage, the employee responsible, and any notes or conditions attached."
a) Design the complete logical model with all entities and relationships. b) Include at least two junction tables. c) Identify any supertype/subtype opportunities. d) Apply normalization validation to your design. e) Write the DDL for all tables.
Section E: Meridian Bank Extensions (Exercises 31-35)
Exercise 31 — Credit Card Subsystem [Advanced]
Design the logical model for Meridian Bank's credit card operations:
"The bank issues credit cards linked to credit accounts. Each card has a card number, expiration date, CVV, and credit limit. A single credit account can have multiple cards (primary and authorized users). Merchants are identified by merchant ID and category. Each credit card transaction records the merchant, amount, and authorization code. Monthly statements summarize transactions and calculate minimum payment."
Design at least 5 entities with full attributes and relationships.
Exercise 32 — Wire Transfer System [Intermediate]
Model the wire transfer process:
"Customers initiate wire transfers to domestic or international recipients. Each transfer has a sender account, recipient bank (identified by routing number/SWIFT code), recipient account number, amount, currency, and purpose. International transfers require additional information: intermediary bank, IBAN, and compliance screening status."
Design the entities, paying special attention to the supertype/subtype opportunity for domestic vs. international transfers.
Exercise 33 — Customer Relationship Management [Advanced]
Model the CRM extension:
"Relationship managers are assigned to high-value customers. Each interaction (call, email, meeting, branch visit) is logged. Customers are categorized into tiers (Bronze, Silver, Gold, Platinum) based on total relationship value. The bank runs marketing campaigns targeted at specific customer segments. Campaign responses are tracked."
Design at least 6 entities and explain the relationship between the CRM model and the core banking model from Section 13.11.
Exercise 34 — Regulatory Reporting Extension [Advanced]
Extend the regulatory domain:
"The bank files quarterly Call Reports to the FDIC. Each report aggregates data across all accounts: total deposits by type, total loans by category, delinquency rates, capital ratios. The bank must also track Suspicious Activity Reports (SARs) filed for individual customers, including the reason, supporting transactions, and filing status with FinCEN."
Design the entities needed and show how they reference the core banking entities.
Exercise 35 — Model Review Exercise [Challenge]
Exchange your solution to Exercise 30 (or any other design exercise) with a classmate or colleague. Conduct a formal design review:
a) Check every entity for proper primary key selection. b) Verify that all relationships have correct cardinality and participation. c) Run a normalization check (1NF through 3NF) on every table. d) Look for anti-patterns (EAV, OTLT, mega-table, comma-separated lists). e) Verify that the model can answer at least five business questions the stakeholders would ask. f) Write a one-page review document with findings and recommendations.
Answer Key Notes
Detailed solutions for Exercises 1-8, 17-19, and 23 are provided in Appendix: Answers to Selected Exercises. For design exercises (23-35), multiple valid solutions exist. The key criteria are: correct normalization, appropriate cardinality, no anti-patterns, and the ability to answer common business queries without undue complexity.