Relational database concepts

Tables, rows, and columns

In the relational model, data is perceived as existing in tables. Each table has a name (for example CUSTOMER) and a fixed set of columns that describe attributes (CUST_ID, CUST_NAME, CITY). At any moment the table holds zero or more rows, each row storing one value (or NULL) per column. Rows are not inherently ordered; if you need a display order, you ask for it with ORDER BY in SQL.

Columns have data types. Db2 needs to know whether a column is character, integer, decimal, date, and so on, because storage, comparison, and arithmetic rules depend on type. NULL means “unknown or not applicable,” which is different from blank or zero—a critical idea when you write predicates.

1
2
3
4
5
6
7
CREATE TABLE CUSTOMER (
    CUST_ID     INTEGER     NOT NULL,
    CUST_NAME   VARCHAR(40) NOT NULL,
    CITY        VARCHAR(30),
    STATUS      CHAR(1)     NOT NULL,
    PRIMARY KEY (CUST_ID)
);

You can picture one row as one customer. Unlike a COBOL FD that is private to a program’s file section, the table definition lives in Db2’s catalog and is shared. Privileges control who can SELECT or UPDATE. Many programs can use CUSTOMER at once.

Base tables vs views (preview)

A base table stores the data. A view is a named SELECT that presents columns—and often a filtered subset of rows—as if they were a table. Views help security and simplicity (expose only certain columns) without duplicating the base data. You will study views in a dedicated page; for relational thinking, remember that users still query something that looks like a table.

Keys and relationships

Relationships connect data across tables using values, not physical parent/child pointers like older hierarchical databases. Keys make those relationships reliable.

Example: every order belongs to a customer. ORDERS stores CUST_ID. That column is a foreign key referencing CUSTOMER. If referential integrity is enforced, Db2 rejects an order for a customer id that does not exist, and it can restrict or cascade deletes according to how the constraint was defined.

1
2
3
4
5
6
7
8
CREATE TABLE ORDERS (
    ORDER_ID    INTEGER     NOT NULL,
    CUST_ID     INTEGER     NOT NULL,
    ORDER_DATE  DATE        NOT NULL,
    ORDER_AMT   DECIMAL(11,2) NOT NULL,
    PRIMARY KEY (ORDER_ID),
    FOREIGN KEY (CUST_ID) REFERENCES CUSTOMER (CUST_ID)
);

Indexes often support keys for fast lookup and to enforce uniqueness. An index is not the same thing as a key: a key is a logical rule; an index is a physical access structure. In practice Db2 uses unique indexes to implement unique and primary key constraints.

Cardinality of relationships

• One-to-many — one customer has many orders (most common business pattern)
• One-to-one — rare; sometimes used for extension tables
• Many-to-many — implemented with an intersection table (for example STUDENT_COURSE with student id and course id)

Getting cardinality right matters more than memorizing jargon. If you store the wrong grain—for example stuffing many phone numbers into one column—you fight the relational model instead of using it.

Set-based thinking vs record-at-a-time

COBOL file programs train you to think record-at-a-time: open file, read next record, decide, write, loop until end. That model maps to cursors when you must process query results in a host language. Relational SQL encourages set-based thinking: describe the set of rows to change or return, then let Db2 evaluate the set.

Set-based example—close all open orders for one customer in a single statement:

1
2
3
4
UPDATE ORDERS
   SET STATUS = 'C'
 WHERE CUST_ID = 1001
   AND STATUS = 'O';

A record-at-a-time approach would declare a cursor for those orders, fetch each row, and update one by one. Sometimes you need that (complex per-row logic, writing a report line per fetch). Often the set-based UPDATE is clearer, shorter, and easier for Db2 to optimize. Beginners coming from files should practice asking, “Can I express this as one SQL statement against a set?” before writing a cursor loop.

Joins as set combinations

Joins combine rows from related tables into a wider set:

1
2
3
4
5
SELECT c.CUST_NAME, o.ORDER_ID, o.ORDER_AMT
  FROM CUSTOMER c
  INNER JOIN ORDERS o
    ON c.CUST_ID = o.CUST_ID
 WHERE c.CITY = 'LONDON';

Mentally: start from customers in London (a set), combine with matching orders (another set) using the key relationship, and project the columns you need. That is relational thinking in one query.

How Db2 fits the relational model

Db2 for z/OS implements the relational model for enterprise systems:

• Logical interface — applications see tables, views, and SQL
• Integrity — constraints, unique indexes, triggers (where used), and transactional COMMIT/ROLLBACK
• Concurrency — locks and isolation so many users share tables
• Physical layer — table spaces, indexes, buffer pools, logs—managed so the logical model stays stable even when storage is reorganized
• Catalog — metadata describing objects, so the system—and tools—know the shape of your data

IBM’s introduction materials emphasize that in a relational database all data is logically contained in tables, and that referential integrity, check constraints, and triggers help keep data valid inside the DBMS rather than only inside each application. That is a major upgrade from “every COBOL program validates the file layout its own way.”

Db2 also extends beyond pure textbook relational features (large objects, XML, and other capabilities), but tables and SQL remain the center of gravity for most application work on z/OS.

From concept to your next lessons

Upcoming pages drill into tables, rows, columns, schemas, databases, and table spaces as concrete Db2 objects. Keep this mental model: relational concepts are how you design and query; Db2 objects are how the product stores, secures, and tunes that design on z/OS.

Explain It Like I'm Five

Think of a binder of baseball cards. Each page is a table. Each card is a row. Each fact printed on the card—name, team, batting average—is a column. A special number on the card that no other card shares is like a primary key. If another binder of “games schedules” writes that same player number to show who is playing, that number is like a foreign key. Instead of flipping every card by hand to find all Yankees hitters, you can ask a smart helper (SQL) to bring you the whole set at once. That helper is what Db2 is good at.

Exercises

1. Design two tables—STUDENT and ENROLLMENT—with a primary key and a foreign key. List the columns you would include.
2. Rewrite this record-at-a-time idea as a set-based SQL statement: “Read each order; if amount is greater than 1000 and status is O, set status to H.”
3. Explain why ORDER BY is needed if you want rows displayed alphabetically by customer name.
4. Give one reason NULL is not the same as a blank customer middle name stored as spaces.
5. Why might a many-to-many relationship need a third table instead of foreign keys alone on two tables?