Tables, Rows & Schemas — Explain tables, rows, columns, and schemas (conceptual) - SQL

SECTION 1

Historical Context & Motivation

Before relational databases existed, organizations stored data in flat files, hierarchical databases, and network databases — each approach entangling the physical storage layout with the logical meaning of the data. A programmer who wanted to answer a simple question like "Which customers placed orders last month?" had to write code that navigated pointers, understood file offsets, and was tightly coupled to the specific storage format on disk. Changing the structure of the data meant rewriting every program that touched it. The need for a data-independent model — one that separated what the data means from how the data is stored — drove the development of the relational model and, ultimately, the concepts of tables, rows, columns, and schemas that we study today.

1970 • Codd's Relational Model

Edgar F. Codd publishes A Relational Model of Data for Large Shared Data Banks at IBM, proposing that data be organized into relations (tables) with named attributes (columns) and tuples (rows), independent of physical storage.

1974 • System R & SEQUEL

IBM researchers develop System R, one of the first relational database prototypes, along with SEQUEL (later renamed SQL), a language designed to manipulate tables declaratively.

1979 • Oracle V2 Ships

Relational Software Inc. (later Oracle Corporation) releases the first commercially available SQL-based relational database, proving the table-based model viable for industry.

1986 • SQL Becomes a Standard

ANSI publishes the first SQL standard (SQL-86), codifying the concepts of tables, columns, rows, and schemas as the universal vocabulary for relational data management.

2003–Present • SQL Evolves

Subsequent standards (SQL:2003, SQL:2016, SQL:2023) add window functions, JSON support, and more — but the foundational abstractions of tables, rows, columns, and schemas remain unchanged.

The central question Codd's model answered was deceptively simple: How can we represent arbitrary data in a uniform structure that is mathematically precise, easy to reason about, and independent of physical storage? The answer — the humble table — has proven so powerful that over fifty years later it remains the dominant abstraction for structured data across virtually every industry.

SECTION 2

Core Principles & Definitions

A relational database is built from a small set of precisely defined abstractions. Understanding each of these — and how they compose together — is the prerequisite for writing correct SQL, designing efficient databases, and reasoning about data integrity. The four foundational concepts are the table (relation), the column (attribute), the row (tuple), and the schema that governs their structure.

Table (Relation)

A named, two-dimensional structure consisting of columns and rows. Each table represents a single entity type or relationship in the domain being modeled. In formal relational theory, a table corresponds to a relation — a set of tuples that share the same attributes.

Column (Attribute)

A named field within a table that describes one property of the entity. Every column has a data type (e.g., INTEGER, VARCHAR, DATE) that constrains the values it may contain. Columns define the shape of the data; they are analogous to fields in a struct or class.

Row (Tuple)

A single record in a table — one concrete instance of the entity. Each row supplies a value (or NULL) for every column. In the relational model, a relation is a set of tuples, so duplicate rows are theoretically disallowed (enforced in practice via primary keys).

Schema

The structural blueprint of a database or table. A table schema specifies column names, data types, constraints (NOT NULL, UNIQUE, FOREIGN KEY), and defaults. A database schema is the collection of all table schemas plus their inter-table relationships.

✦

KEY TAKEAWAY

Think of a table as a spreadsheet with strict rules. The column headers are locked (you cannot put a name where a date belongs), every row must conform to those headers, and the entire spreadsheet has a name so other spreadsheets can reference it. The schema is the rule book that enforces all of this — it is the contract between the data and every program that reads or writes it. If a spreadsheet lets you type anything anywhere, a schema-governed table says "this column is an integer and it must not be null."

SECTION 3

Visual Explanation — Anatomy of a Table

The gradient header row shows the four columns (attributes). Each horizontal band beneath it is a row (tuple). Note that Dan Kim's major is NULL — the absence of a value. The dashed box at the bottom represents the schema, which prescribes column names, data types, and constraints before any data is inserted.

The diagram above illustrates how the three levels of abstraction compose together. At the lowest level, individual cell values live at the intersection of a column and a row. Each column constrains which values are legal (an INTEGER column rejects the string "hello"), while each row groups related values into a coherent record. The schema wraps all of this with metadata — column ordering, naming conventions, and integrity constraints such as PRIMARY KEY and NOT NULL. This layered design means you can change how you query the data (SELECT, JOIN, WHERE) without changing the schema, and you can evolve the schema (ADD COLUMN, ALTER TYPE) without rewriting every query — a property known as logical data independence.

SECTION 4

How It Works — From Theory to DDL

In Codd's relational algebra, a relation R is defined over a set of domains D₁, D₂, …, Dₙ. A relation is a subset of the Cartesian product of those domains. More precisely, R ⊆ D₁ × D₂ × … × Dₙ. Each element of R is an n-tuple, and the heading (schema) assigns a name and domain to each position in the tuple. This formal foundation is what gives SQL its mathematical rigor: every SQL query is ultimately an expression in relational algebra or relational calculus that produces a new relation (i.e., a new table).

RELATION DEFINITION

R ⊆ D₁ × D₂ × … × Dₙ

R is a relation (table). Dᵢ is the domain (data type) of the i-th attribute (column). Each element of R is an n-tuple (row). The heading assigns a name to each Dᵢ.

DEGREE AND CARDINALITY

degree(R) = n | cardinality(R) = |R|

The degree of a relation is the number of columns (n). The cardinality is the number of rows currently in the table. Degree is fixed by the schema; cardinality changes with every INSERT or DELETE.

In practice, the formal relation is realized through SQL's Data Definition Language (DDL). A CREATE TABLE statement translates the mathematical heading into a concrete schema stored in the database's catalog (also called the information schema or data dictionary). The catalog itself is composed of tables — a beautifully recursive design where the database's metadata is stored in the same format as user data. When you write CREATE TABLE students (student_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, major VARCHAR(50), gpa DECIMAL(3,2)), the RDBMS records the column names, data types, and constraints in catalog tables such as INFORMATION_SCHEMA.COLUMNS and INFORMATION_SCHEMA.TABLE_CONSTRAINTS.

⚠

SQL vs. Relational Theory

SQL tables differ from mathematical relations in a few important ways. SQL allows duplicate rows (unless a UNIQUE or PRIMARY KEY constraint forbids them), permits NULL values (which have no counterpart in classical set theory), and defines a default column ordering. Purists sometimes call SQL's tables "multisets" or "bags" rather than true sets. Being aware of this gap helps you understand why some SQL behaviors — such as the three-valued logic introduced by NULL — can seem counterintuitive.

SECTION 5

Schema Layers — From Column to Catalog

The word "schema" is used at multiple levels in database systems, and conflating them leads to confusion. At the narrowest level, a column definition specifies a name, data type, and optional constraints for a single attribute. A table schema is the ordered list of all such column definitions plus table-level constraints (composite keys, CHECK constraints). A database schema (sometimes called a "namespace" in PostgreSQL or a "schema" in SQL Server) is a named collection of tables, views, and other objects. Finally, the catalog is the metadata repository that stores all schema definitions for an entire database server. Understanding these layers is essential when you encounter SQL statements like CREATE SCHEMA university or fully qualified names like university.students.student_id.

This nested diagram shows how a catalog contains a database schema, which in turn contains multiple table schemas. The orange arrow between students and enrollments represents a foreign key relationship — a constraint declared in the schema that links rows across tables.

Schema Level	Scope	SQL Syntax / Access
Column Definition	Single attribute	`name VARCHAR(100) NOT NULL`
Table Schema	All columns + table constraints	`CREATE TABLE students (...)`
Database Schema	Named collection of tables, views, etc.	`CREATE SCHEMA university`
Catalog	All schemas for the server/database	`SELECT * FROM INFORMATION_SCHEMA.TABLES`

The four levels of schema in a typical RDBMS

SECTION 6

Worked Example — Designing a Table from Requirements

Suppose a university registrar asks you to store information about courses. Each course has a unique code (e.g., "CS301"), a title, a number of credits (always between 1 and 6), and an optional description. Let us walk through the process of translating these requirements into a well-defined table schema.

Creating the courses Table

1
Step 1 — Identify the Entity and Attributes

The entity is a course. Its attributes are: course_code (unique identifier), title, credits, and description. Each attribute maps to a column.

Columns: course_code, title, credits, description
2
Step 2 — Choose Data Types

course_code is a short, fixed-length string → CHAR(8). title is a variable-length string → VARCHAR(150). credits is a small integer → SMALLINT. description is potentially long text → TEXT. Selecting the right type ensures the database can validate inputs and optimize storage.

CHAR(8), VARCHAR(150), SMALLINT, TEXT
3
Step 3 — Define Constraints

course_code uniquely identifies each course, so it becomes the PRIMARY KEY. title must always be provided → NOT NULL. credits must be between 1 and 6 → CHECK (credits BETWEEN 1 AND 6). description is optional, so no NOT NULL constraint is needed (NULL is the default).

PK on course_code, NOT NULL on title, CHECK on credits
4
Step 4 — Write the DDL Statement

Combining the above, the complete SQL statement is:CREATE TABLE courses ( course_code CHAR(8) PRIMARY KEY, title VARCHAR(150) NOT NULL, credits SMALLINT NOT NULL CHECK (credits BETWEEN 1 AND 6), description TEXT );

Table courses created with 4 columns, degree = 4, cardinality = 0 (no rows yet).
5
Step 5 — Insert a Row and Verify

We insert a sample row: INSERT INTO courses VALUES ('CS301', 'Database Systems', 3, 'Relational model, SQL, normalization.'); After insertion, the table has cardinality = 1. A query like SELECT * FROM courses; will return one row with four cell values.

cardinality(courses) = 1

SECTION 7

Strengths and Limitations of the Tabular Model

The table abstraction has endured for over fifty years because of its remarkable combination of simplicity and power, but it is not a perfect fit for every data problem. Appreciating both its strengths and its limitations helps you make informed architectural decisions about when to use a relational database and when an alternative model — document, graph, key-value — might serve you better.

Strengths	Limitations
Rigorous mathematical foundation (set theory, relational algebra) enables formal reasoning and optimization.	Fixed schema makes evolving the structure painful for rapidly changing domains (schema migrations can be complex).
Declarative querying — you specify what you want, not how to get it. The query optimizer handles execution.	Deeply nested or hierarchical data (e.g., JSON trees, XML documents) maps awkwardly to flat tables without many JOINs.
Strong data integrity through constraints (PK, FK, UNIQUE, CHECK, NOT NULL) enforced at the database level.	Horizontal scaling (distributing data across many servers) is harder than with document or key-value stores.
Mature ecosystem — decades of tooling, indexing strategies, and battle-tested implementations (PostgreSQL, MySQL, Oracle, SQL Server).	NULL semantics introduce three-valued logic (TRUE, FALSE, UNKNOWN), which complicates predicates and aggregations.

Strengths vs. limitations of the relational table model

✦

KEY TAKEAWAY

The tabular model is like a strongly typed programming language: the compiler (schema) catches many errors before runtime (data entry), and the type system (column data types and constraints) guides correct usage. Just as strong typing adds friction when prototyping but pays off in large, long-lived systems, a rigid schema imposes upfront design cost but dramatically reduces data inconsistencies in production. Choosing between a relational model and a schema-less model is analogous to choosing between a statically typed language and a dynamically typed one — each has contexts where it shines.

SECTION 8

Connection to Advanced Theory — Normalization and Beyond

Once you understand tables, rows, columns, and schemas, the next conceptual frontier is normalization — the process of organizing tables to reduce redundancy and prevent update anomalies. Normalization is defined through a series of normal forms (1NF through 5NF and beyond), each imposing stricter rules on how columns depend on the primary key. Understanding that a table is a set of tuples with a well-defined heading is the prerequisite for reasoning about functional dependencies (the mathematical relationships between columns) and deciding how to decompose a badly designed table into several well-structured ones.

Concept (This Lesson)	Advanced Extension
Table as a set of rows with a fixed schema	Normal forms — 1NF requires atomic column values, no repeating groups
Primary key uniquely identifies each row	Functional dependencies — non-key columns depend on the whole key (2NF, 3NF, BCNF)
Foreign key links rows across tables	Referential integrity, cascading updates/deletes, join algebra
Schema as a blueprint with constraints	Schema evolution, migrations, online DDL, schema versioning
Catalog stores metadata	Query optimizer uses catalog statistics (row counts, value distributions) for plan selection

How foundational concepts extend into advanced database theory

Beyond normalization, the table abstraction underpins views (virtual tables defined by queries), materialized views (cached query results stored as physical tables), and the entire ACID transaction model. Every time you write a SELECT statement, the query optimizer consults the catalog (schema metadata) to choose indexes, join orders, and access paths. In this sense, the schema is not just documentation — it is an active participant in query execution. As you advance into topics like distributed databases, columnar storage, and data warehousing, you will find that the conceptual vocabulary of tables, rows, columns, and schemas remains the lingua franca, even when the physical implementation diverges dramatically from a simple row-by-row layout on disk.

SECTION 9

Practice Problems

Question 1: CONCEPTUAL

In relational theory, a relation is defined as a subset of a Cartesian product of domains. Explain, in your own words, why a relation is a set of tuples rather than a list, and what practical consequence this has for duplicate rows in SQL tables.

Show answer

A mathematical set, by definition, contains no duplicate elements and has no inherent ordering. Codd adopted set semantics so that the result of any relational algebra expression (selection, projection, join) would itself be a valid relation — this closure property is what makes the algebra composable. In practice, SQL relaxes this: tables are technically multisets (bags) and can contain duplicate rows unless a PRIMARY KEY or UNIQUE constraint is declared. This is why best practice dictates always defining a primary key — without one, you may introduce duplicates that violate the relational model's assumptions and cause ambiguity in UPDATE and DELETE operations.

Question 2: BASIC CALCULATION

A table employees has columns emp_id INT, first_name VARCHAR(50), last_name VARCHAR(50), department_id INT, salary DECIMAL(10,2), hire_date DATE. What is the degree of this relation? If the table currently holds 2,500 records, what is its cardinality? How many cells (individual values) does the table contain?

Show answer

The degree is the number of columns, which is 6 (emp_id, first_name, last_name, department_id, salary, hire_date). The cardinality is the number of rows, which is 2,500. The total number of cells is degree × cardinality = 6 × 2,500 = 15,000. Note that some of these 15,000 cells may contain NULL if the corresponding column allows it, but NULL still occupies a cell position in the tuple.

Question 3: INTERMEDIATE

Given the following requirements, write a CREATE TABLE statement: A library tracks books. Each book has an ISBN (exactly 13 characters, unique), a title (required, up to 300 characters), a publication year (integer, must be between 1450 and the current year), and an optional page count (positive integer). Identify the primary key and all constraints you would apply.

Show answer

A suitable DDL statement is:

CREATE TABLE books (isbn CHAR(13) PRIMARY KEY, title VARCHAR(300) NOT NULL, publication_year INT NOT NULL CHECK (publication_year BETWEEN 1450 AND 2025), page_count INT CHECK (page_count > 0));

The ISBN is the natural primary key since it uniquely identifies each book. CHAR(13) is appropriate because ISBN-13 is fixed-length. title has NOT NULL because every book must have a title. publication_year uses a CHECK constraint to enforce domain validity. page_count is nullable (optional) but when provided must be positive. This schema has degree 4, and its cardinality starts at 0.

Question 4: APPLIED

A startup is building an e-commerce platform. They initially store all order data in a single table: orders(order_id, customer_name, customer_email, product_name, product_price, quantity, order_date). Identify at least two problems with this single-table design, and propose a multi-table schema that resolves them. Specify primary and foreign keys.

Show answer

The single-table design suffers from data redundancy and update anomalies. First, customer information (name, email) is duplicated across every order row — if a customer changes their email, you must update many rows. Second, product information (name, price) is repeated for every order containing that product, wasting space and risking inconsistency. A normalized design would use three tables: customers(customer_id INT PK, name VARCHAR(100) NOT NULL, email VARCHAR(255) NOT NULL UNIQUE), products(product_id INT PK, name VARCHAR(200) NOT NULL, price DECIMAL(10,2) NOT NULL), and

orders(order_id INT PK, customer_id INT NOT NULL REFERENCES customers, product_id INT NOT NULL REFERENCES products, quantity INT NOT NULL CHECK(quantity > 0), order_date DATE NOT NULL)

. The foreign keys on customer_id and product_id enforce referential integrity, ensuring that every order references a valid customer and product. This decomposition eliminates redundancy and confines updates to a single row.

Question 5: CRITICAL THINKING

SQL tables allow NULL values, but Codd's original relational model was based on set theory, where every element in a tuple must come from its domain. Argue for or against the inclusion of NULL in a relational database system. Consider the impact on query semantics (three-valued logic), constraint enforcement, and practical usability. Support your argument with at least one concrete example.

Show answer

This is an open-ended question with valid arguments on both sides. An argument in favor of NULL: real-world data is inherently incomplete — a student may not yet have declared a major, or a sensor reading may be missing. Without NULL, the designer would have to invent sentinel values (e.g., -1 for 'unknown salary'), which are fragile and application-specific. NULL provides a standardized, type-independent way to represent missing data. An argument against NULL: NULL introduces three-valued logic (TRUE, FALSE, UNKNOWN), which violates the Law of the Excluded Middle that underpins classical set theory. For example, the expression WHERE major = 'CS' OR major <> 'CS' does NOT return rows where major is NULL, which surprises many programmers. NULLs also complicate aggregates — AVG(salary) silently ignores NULLs, potentially skewing results. Some researchers (including C.J. Date) advocate for a NULL-free relational model using default values and separate flag columns. In practice, NULLs are deeply embedded in the SQL standard, so understanding their semantics is essential regardless of one's philosophical stance.

SUMMARY

Summary

Relational databases organize data into tables (relations), where each table models a single entity type or relationship. A table's columns (attributes) define the properties of that entity — each with a name, data type, and optional constraints — while rows (tuples) are individual records that supply a value for every column. The schema is the structural blueprint that governs all of this: it specifies column definitions, primary keys, foreign keys, and CHECK constraints, and it exists at multiple levels — from a single column definition up through the database catalog. The number of columns is the table's degree (fixed by the schema), and the number of rows is its cardinality (variable with data manipulation).

These four abstractions — originating from Codd's 1970 paper and formalized in the SQL standard — remain the foundation upon which all SQL operations, normalization theory, query optimization, and transaction management are built. Mastering them is the essential first step toward designing correct, efficient, and maintainable database systems.