Relational Model, Keys, Tables, Tuples, and Schema Design
Move from conceptual design to relational thinking by understanding tables, tuples, domains, keys, constraints, and relation-based schema structure.
Inside this chapter
- What the Relational Model Says
- Core Terms
- Primary, Candidate, Composite, and Foreign Keys
- Schema vs Instance
- Integrity Constraints
- Why Good Schema Design Matters
Series navigation
Study the chapters in order for the clearest path from database fundamentals and SQL to transactions, indexing, recovery, distributed systems, tuning, and advanced DBMS engineering understanding. Use the navigation at the bottom to move smoothly across the full tutorial series.
What the Relational Model Says
The relational model organizes data into relations, usually represented as tables. Each relation contains tuples, usually represented as rows, and attributes, usually represented as columns. Although tables look simple, the relational model is mathematically disciplined and forms the basis of most enterprise database systems.
Core Terms
- Relation: table-like structure
- Tuple: row in a relation
- Attribute: column in a relation
- Domain: allowed set of values for an attribute
- Degree: number of attributes
- Cardinality: number of tuples
Primary, Candidate, Composite, and Foreign Keys
Keys are central to relation design. A candidate key is any minimal unique identifier. One candidate key becomes the primary key. A composite key uses multiple columns. A foreign key links rows across tables and supports referential integrity.
Orders(order_id, customer_id, order_date)
Customers(customer_id, customer_name, email)Here customer_id in Orders is a foreign key that references Customers.
Schema vs Instance
The schema is the design or blueprint of the database. The instance is the actual data currently stored. Students should keep this distinction clear because schema changes are structural, while instance changes are daily operational updates.
Integrity Constraints
- Domain constraints restrict valid values
- Entity integrity ensures primary keys are not null
- Referential integrity keeps foreign key references valid
Why Good Schema Design Matters
A poor schema causes duplicates, unclear ownership, difficult joins, weak validation, and reporting confusion. A strong schema reduces defects, supports analytics, and makes application development smoother.