Tables

Introduction

Table: fundamental relational database structure. Two-dimensional: rows (records), columns (fields). Stores data logically. Named collection with defined schema.

"Tables organize data logically: rows are entities, columns are attributes. Foundation of relational databases, simple yet powerful abstraction." -- Relational model

Table Definition

Concept

Table: named relation. Rows: tuples (individual records). Columns: attributes (properties). Schema: defines structure (columns, types, constraints). Named: identified by name in database.

Example

Employees table:
EmployeeID | Name | Salary | DepartmentID
1 | Alice | 50000 | 10
2 | Bob | 60000 | 20
3 | Carol | 55000 | 10

Properties

Unordered: row sequence irrelevant (logically). Unique rows: no duplicates (enforced by key). Dynamic: rows added/modified. Persistent: survives sessions.

Columns and Data Types

Column Definition

Column: attribute (named field). Data type: specified (INT, VARCHAR, DATE, etc.). Size: for VARCHAR (max length). Constraints: NULL, NOT NULL, DEFAULT.

Data Types

Numeric: INT, DECIMAL, FLOAT. String: VARCHAR, CHAR, TEXT. Date: DATE, DATETIME, TIME. Boolean: BOOLEAN, BIT. Binary: BLOB.

Column Constraints

NOT NULL: value required. DEFAULT: default value (if not provided). CHECK: condition (e.g., age > 0). UNIQUE: distinct values. PRIMARY KEY: unique identifier.

Example Column

CREATE TABLE employees (
 employee_id INT NOT NULL PRIMARY KEY,
 name VARCHAR(100) NOT NULL,
 salary DECIMAL(10, 2) CHECK (salary > 0),
 hire_date DATE DEFAULT CURRENT_DATE
);

Rows and Records

Row Definition

Row: single record (instance). Values: one per column. Insertion: adds new row. Update: modifies existing. Deletion: removes row.

Uniqueness

Primary key: uniquely identifies row. No duplicates: enforced. Candidate keys: alternative unique identifiers.

Integrity

Foreign keys: reference other tables. Referential integrity: enforced (no orphan records). Constraints: prevent invalid data.

Schema and Metadata

Schema Definition

Table schema: describes structure. Columns: names, types, constraints. Metadata: stored in system catalog. Query: information_schema tables.

System Catalog

Database stores: table definitions, column info, constraints. Queries: metadata (not data). Tool support: schema visualization, documentation.

ALTER TABLE

Modify: column (add, drop, rename), constraints, indexes. Careful: may require downtime (or online, DBMS-dependent). Plan: avoid frequent changes.

Table Constraints

Primary Key

Unique identifier: one per table (or composite). Not NULL: required. Clustered by default: affects physical order.

Unique Constraint

Alternate key: distinct values (allows NULLs). Multiple allowed: per table. Indexed: for performance.

Foreign Key

References: primary key of other table. Referential integrity: enforced. Cascading: ON DELETE/UPDATE actions.

Check Constraint

CHECK (age >= 18) -- enforces value range
CHECK (salary > 0) -- prevents negative

Default Values

status VARCHAR(10) DEFAULT 'active'
created_date DATETIME DEFAULT CURRENT_TIMESTAMP

Creating Tables

CREATE TABLE Syntax

CREATE TABLE table_name (
 column1 type constraints,
 column2 type constraints,
 PRIMARY KEY (column1),
 FOREIGN KEY (column2) REFERENCES other_table(id)
);

Example

CREATE TABLE departments (
 dept_id INT PRIMARY KEY,
 dept_name VARCHAR(50) NOT NULL,
 location VARCHAR(50)
);

Temporary Tables

CREATE TEMPORARY TABLE: exists only during session. Useful: intermediate results. Cleaned: on disconnect.

Modifying Tables

ALTER TABLE

ALTER TABLE employees ADD salary_increase DECIMAL(5, 2);
ALTER TABLE employees DROP COLUMN salary_increase;
ALTER TABLE employees MODIFY name VARCHAR(100);
ALTER TABLE employees RENAME TO staff;

Adding Constraints

ALTER TABLE employees ADD CONSTRAINT fk_dept
FOREIGN KEY (dept_id) REFERENCES departments(dept_id);

Performance Impact

Large tables: ALTER may lock. Online: some systems allow (adds overhead). Plan: during low-traffic periods.

Dropping Tables

DROP TABLE table_name;
DROP TABLE IF EXISTS table_name; -- doesn't error if not exists

Design Principles

Atomic Values

Columns: single value (not composite). Example: separate first_name, last_name (not full_name). Simplifies: queries, updates.

Minimize Redundancy

Normalize: avoid repeated data. Use foreign keys: reference, not duplicate. Consistency: easier maintenance.

Meaningful Names

Tables: plural (employees, departments). Columns: descriptive (employee_id, not id). Readability: helps understanding.

Proper Keys

Primary key: always define. Natural or surrogate: choose wisely. Immutable: stable identifier.

Scalability

Performance: consider indexing (key columns). Growth: can table grow (disk space)? Partitioning: for very large tables.

Best Practices

Schema Design

Normalize: eliminate anomalies. Document: schema comments. Review: design before implementation.

Constraints

Define: all relevant constraints (database enforces). NOT NULL: when required. DEFAULT: sensible defaults (not NULL-by-default).

Performance

Indexes: on foreign keys, frequently filtered. Monitor: query performance. Optimize: schema based on actual queries.

Maintenance

Backups: regular (protect data). Changes: document (audit trail). Testing: test schema changes (DDL scripts).

References

• Ramakrishnan, R., and Gehrke, J. "Database Management Systems." McGraw-Hill, 3rd edition, 2003.
• Silberschatz, A., Korth, H. F., and Sudarshan, S. "Database System Concepts." McGraw-Hill, 6th edition, 2010.