SQL: Changing Data & Database Design

Chapter roadmap

Insert, update, and delete safely; enforce integrity with constraints; normalize designs; and group related changes into transactions. Work through the concepts in order, type every example yourself, and keep a short debugging log that records predictions, errors, fixes, and what each fix taught you.

Learning objectives

Use INSERT, UPDATE, and DELETE safely
Apply constraints and relationships
Normalize repeated data
Explain transaction guarantees

05.1

INSERT, UPDATE, and DELETE

INSERT creates rows, UPDATE changes matching rows, and DELETE removes matching rows. An UPDATE or DELETE without WHERE may affect every row, so preview the target with SELECT and use a transaction. Explicit column lists make inserts resilient to schema changes.

Before running the example, trace each expression and write down the expected state or result. Then run it, compare the actual result, and change one input or rule to confirm that you understand which part controls the behavior.

Example

BEGIN;

UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = 42
  AND quantity > 0;

COMMIT;

Result

The product quantity decreases by one only when stock is available.

Check your understanding

Explain the example in plain language, identify one boundary or failure case, and revise it so the output changes in a predictable way.

05.2

Constraints and integrity

NOT NULL requires a value, UNIQUE prevents duplicates, CHECK enforces a rule, PRIMARY KEY identifies rows, and FOREIGN KEY enforces relationships. Constraints protect data regardless of which application writes it and should express rules the database can reliably know.

Example

CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students(student_id),
    course_id INTEGER REFERENCES courses(course_id),
    enrolled_on DATE NOT NULL,
    status VARCHAR(20) CHECK (status IN ('active', 'dropped', 'complete')),
    PRIMARY KEY (student_id, course_id)
);

Result

Duplicate student-course pairs and unsupported status values are rejected.

Check your understanding

Explain the example in plain language, identify one boundary or failure case, and revise it so the output changes in a predictable way.

05.3

Normalization

Normalization separates facts so each is stored in one appropriate place. First normal form removes repeating groups, second normal form removes dependence on part of a composite key, and third normal form removes dependence on non-key columns. Denormalization is an intentional performance choice, not a shortcut around design.

Example

-- Instead of repeating customer details on every order:
customers(customer_id, full_name, email)
orders(order_id, customer_id, ordered_at)

-- orders.customer_id references customers.customer_id

Result

One customer update changes the single source of truth.

Check your understanding

Explain the example in plain language, identify one boundary or failure case, and revise it so the output changes in a predictable way.

05.4

Transactions and ACID

A transaction treats related statements as one unit. Atomicity means all or none, consistency preserves rules, isolation controls concurrent interaction, and durability keeps committed results. Roll back when a required step fails or validation reveals an unexpected row count.

Example

BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE account_id = 10;
UPDATE accounts SET balance = balance + 50 WHERE account_id = 20;
-- Verify both updates succeeded.
COMMIT;

Result

A $50 transfer is committed as one unit instead of two unrelated changes.

Check your understanding

Explain the example in plain language, identify one boundary or failure case, and revise it so the output changes in a predictable way.

Chapter assignments

Complete these in order. Later assignments assume that earlier skills are working. Do not only test the sample values; design tests that challenge boundaries, missing values, incorrect types, empty collections, and other likely failures.

A5.1
Write safe INSERT statements for a related three-table dataset using explicit columns and valid foreign keys.
Submit readable source code, a brief design note, and evidence from at least three tests including one boundary or invalid case.
A5.2
Create preview SELECT statements and matching transactional UPDATE and DELETE statements for a cleanup task.
Submit readable source code, a brief design note, and evidence from at least three tests including one boundary or invalid case.
A5.3
Normalize a spreadsheet-style table through third normal form and explain every new key and relationship.
Submit readable source code, a brief design note, and evidence from at least three tests including one boundary or invalid case.
A5.4
Design constraints for a reservation system, including date, capacity, uniqueness, and relationship rules.
Submit readable source code, a brief design note, and evidence from at least three tests including one boundary or invalid case.

Chapter project

Transactional Store Schema

Transactional Store Schema — Design customers, products, orders, and order_items through third normal form. Create constraints, insert test data, and write a transaction that creates an order and reduces inventory, rolling back when stock is insufficient.

Required process

Write a short specification listing inputs, outputs, rules, and failure cases.
Break the work into small functions, queries, modules, or classes appropriate to the language.
Build the smallest working version before adding optional features.
Test normal, boundary, empty, and invalid cases and record the results.
Refactor names and duplication, then write a concise user guide.

Key terms

DML
constraint
integrity
normalization
dependency
transaction
ACID
commit
rollback