101
0/241
Loading content...
If schema notation describes the blueprint, instance notation describes the building. While a schema specifies what attributes exist and what types they accept, an instance contains the actual tuples—the real data stored in the database at a specific point in time.
Instance notation provides precise ways to represent:
This page explores the conventions for representing relation instances, from formal mathematical notation to practical tabular formats, ensuring you can read and write data representations with precision and clarity.
After studying this page, you will be able to:
• Represent tuples using ordered and named notation • Write relation instances using set notation • Use tabular representation for practical documentation • Apply mapping notation for tuple access • Distinguish between relation state snapshots and temporal evolution
A tuple is the fundamental unit of data in the relational model—a single row representing one entity instance. Precise tuple notation is essential for formal reasoning.
The simplest form represents a tuple as an ordered sequence of values:
t = (v₁, v₂, ..., vₙ)
Where vᵢ is the value for the i-th attribute. This notation assumes a fixed attribute ordering defined by the schema.
Example:
Schema: EMPLOYEE(emp_id, name, department, salary)
Tuple: t = (101, 'Alice Johnson', 'Engineering', 85000.00)
A more explicit form associates each value with its attribute name:
t = {A₁: v₁, A₂: v₂, ..., Aₙ: vₙ} or t = ⟨A₁ = v₁, A₂ = v₂, ..., Aₙ = vₙ⟩
Example:
t = {emp_id: 101, name: 'Alice Johnson', department: 'Engineering', salary: 85000.00}
Named notation is preferred in formal contexts because it eliminates dependence on attribute ordering.
123456789101112131415161718192021222324252627
// TUPLE NOTATION EXAMPLES// ======================== // Schema DefinitionEMPLOYEE(emp_id: INT, name: VARCHAR, dept: VARCHAR, salary: DECIMAL) // Ordered Tuple Notationt₁ = (101, 'Alice Johnson', 'Engineering', 85000.00)t₂ = (102, 'Bob Smith', 'Marketing', 72000.00)t₃ = (103, 'Carol Davis', 'Engineering', 91000.00) // Named Tuple Notationt₁ = {emp_id: 101, name: 'Alice Johnson', dept: 'Engineering', salary: 85000.00}t₂ = ⟨emp_id = 102, name = 'Bob Smith', dept = 'Marketing', salary = 72000.00⟩ // Tuple Component Accesst₁[emp_id] = 101 // Access by attribute namet₁.name = 'Alice Johnson' // Dot notation accesst₁[2] = 'Alice Johnson' // Positional access (1-indexed) // Subtuple / Projection on Tuplet₁[{name, salary}] = ('Alice Johnson', 85000.00)t₁[{emp_id, dept}] = (101, 'Engineering') // NULL Value Representationt₄ = (104, 'David Lee', NULL, 65000.00) // dept is NULLt₄ = {emp_id: 104, name: 'David Lee', dept: ⊥, salary: 65000.00} // ⊥ = NULLA relation instance is a set of tuples conforming to a schema. Since it's a set, no duplicate tuples exist, and the order of tuples is undefined.
The most formal representation uses set notation:
r(R) = {t₁, t₂, ..., tₘ}
Where each tᵢ is a tuple conforming to schema R, and m = |r| is the cardinality.
For small instances, explicit enumeration is practical:
r(EMPLOYEE) = {
(101, 'Alice Johnson', 'Engineering', 85000.00),
(102, 'Bob Smith', 'Marketing', 72000.00),
(103, 'Carol Davis', 'Engineering', 91000.00)
}
For describing instances by properties:
r = {t ∈ R | P(t)}
Where P(t) is a predicate that tuples must satisfy.
123456789101112131415161718192021222324252627282930313233
// RELATION INSTANCE NOTATION// =========================== // SchemaDEPARTMENT(dept_id: INT, name: VARCHAR, budget: DECIMAL, location: VARCHAR) // Set Enumeration Notationr(DEPARTMENT) = { (10, 'Engineering', 500000.00, 'Building A'), (20, 'Marketing', 300000.00, 'Building B'), (30, 'Finance', 400000.00, 'Building A'), (40, 'HR', 200000.00, 'Building C')} // Properties of Instance|r(DEPARTMENT)| = 4 // Cardinality: 4 tuplesdeg(DEPARTMENT) = 4 // Degree: 4 attributes // Empty Instance (valid state)s(DEPARTMENT) = ∅ // Empty set, no tuples|s| = 0 // Cardinality is zero // Set-Builder Notation// "All engineering employees with salary > 80000"result = {t ∈ r(EMPLOYEE) | t.dept = 'Engineering' ∧ t.salary > 80000} // Instance State at Time Tr(EMPLOYEE)ᵀ¹ = { ... } // State at time T1r(EMPLOYEE)ᵀ² = { ... } // State at time T2 (after modifications) // Membership Notationt ∈ r(EMPLOYEE) // Tuple t is in instance rt ∉ r(EMPLOYEE) // Tuple t is not in instance rBecause a relation instance is a set, it has these properties:
• No duplicates: Each tuple appears at most once • No ordering: Tuples have no inherent sequence • Membership: A tuple either is or isn't in the set
SQL tables violate these in practice (allowing duplicates unless constrained), but the theoretical model maintains set semantics.
Tabular notation represents relation instances as tables—the most intuitive and widely-used format for documentation, examples, and communication with non-technical stakeholders.
| Column headers represent attributes |
|---|
| Each row represents one tuple |
| Cell values are attribute values |
| emp_id* | name | department | salary | manager_id (FK) |
|---|---|---|---|---|
| 101 | Alice Johnson | Engineering | 85,000.00 | NULL |
| 102 | Bob Smith | Marketing | 72,000.00 | 105 |
| 103 | Carol Davis | Engineering | 91,000.00 | 101 |
| 104 | David Lee | Finance | 78,000.00 | 105 |
| 105 | Eve Wilson | Executive | 150,000.00 | NULL |
When documenting databases, use tabular notation with representative sample data (3-5 rows). Include edge cases: NULL values, maximum-length strings, boundary numeric values. This sample data serves as implicit specification of expected data patterns.
From a mathematical perspective, a tuple can be viewed as a function that maps attribute names to values. This mapping notation provides powerful formal properties.
A tuple t over schema R(A₁, A₂, ..., Aₙ) is a function:
t: {A₁, A₂, ..., Aₙ} → D
where D = ⋃ᵢ dom(Aᵢ) is the union of all domains, and t(Aᵢ) ∈ dom(Aᵢ).
t(emp_id) = 101
t(name) = 'Alice Johnson'
t(salary) = 85000.00
This functional view supports:
123456789101112131415161718192021222324252627282930313233343536
// TUPLE AS MAPPING NOTATION// ========================== // SchemaR = {emp_id, name, department, salary} // Tuple as mapping (function from attributes to values)t: R → Dt(emp_id) = 101t(name) = 'Alice Johnson't(department) = 'Engineering't(salary) = 85000.00 // Restriction (projection on tuple)// t|{name, salary} restricts mapping to subset of attributest|{name, salary}(name) = 'Alice Johnson't|{name, salary}(salary) = 85000.00t|{name, salary}(emp_id) = undefined (not in restricted domain) // Tuple Extension// t ⊕ {bonus: 5000} adds new attribute-valuet' = t ⊕ {bonus: 5000.00}t'(bonus) = 5000.00t'(salary) = 85000.00 // Original mappings preserved // Tuple Update (immutable - creates new tuple)t'' = t[salary ← 90000.00] // New tuple with updated salaryt''(salary) = 90000.00t(salary) = 85000.00 // Original unchanged // Concatenation of Tuples// Given t₁ over R₁ and t₂ over R₂ where R₁ ∩ R₂ = ∅// t₁ ∘ t₂ is tuple over R₁ ∪ R₂t₁ = {name: 'Alice', age: 30}t₂ = {city: 'NYC', country: 'USA'}t₁ ∘ t₂ = {name: 'Alice', age: 30, city: 'NYC', country: 'USA'}Databases change over time. Temporal notation explicitly represents the time dimension of relation instances.
The state of relation r at time T:
r(R)ᵀ or rᵀ(R) or r(R, T)
Showing how instances change:
r(R)ᵀ¹ → r(R)ᵀ² (after INSERT)
r(R)ᵀ² → r(R)ᵀ³ (after UPDATE)
r(R)ᵀ³ → r(R)ᵀ⁴ (after DELETE)
Changes between states:
Δ⁺(r) = r(R)ᵀ² - r(R)ᵀ¹ (inserted tuples)
Δ⁻(r) = r(R)ᵀ¹ - r(R)ᵀ² (deleted tuples)
| Time | Operation | State r(EMPLOYEE) | |r| |
|---|---|---|---|
| T₀ | Initial | {t₁, t₂, t₃} | 3 |
| T₁ | INSERT t₄ | {t₁, t₂, t₃, t₄} | 4 |
| T₂ | DELETE t₂ | {t₁, t₃, t₄} | 3 |
| T₃ | UPDATE t₁→t₁' | {t₁', t₃, t₄} | 3 |
Full temporal database systems extend this notation to store and query historical states. SQL:2011 introduced temporal table support. Notation like r^[T1,T2] represents valid-time intervals during which a tuple was/is true.
Let's apply instance notation to practical scenarios demonstrating its use in documentation, query results, and formal reasoning.
123456789101112131415161718192021222324252627282930313233
// PRACTICAL INSTANCE NOTATION EXAMPLES// ===================================== // EXAMPLE 1: Query Result Representation// Query: SELECT name, salary FROM EMPLOYEE WHERE dept = 'Engineering'// Result relation (unnamed):{ ('Alice Johnson', 85000.00), ('Carol Davis', 91000.00)} // EXAMPLE 2: Join Result// EMPLOYEE ⋈ DEPARTMENT (natural join on dept_id)r(EMPLOYEE) ⋈ r(DEPARTMENT) = { (101, 'Alice', 10, 85000, 10, 'Engineering', 500000), (102, 'Bob', 20, 72000, 20, 'Marketing', 300000), ...} // EXAMPLE 3: Sample Data Documentation// For schema: ORDER(order_id, customer_id, date, status, total)Sample r(ORDER) = { (1001, 501, '2024-01-15', 'delivered', 150.00), (1002, 502, '2024-01-16', 'processing', 89.50), (1003, 501, '2024-01-16', 'pending', 220.00), (1004, 503, '2024-01-17', 'cancelled', 45.00) -- Edge: cancelled}|r| = 4, includes: repeat customer (501), all statuses // EXAMPLE 4: Before/After State (for triggers, constraints)// Before UPDATE: SET salary = salary * 1.1 WHERE dept = 'Engineering'r(EMPLOYEE)_before = {(101, 'Alice', 'Eng', 85000), (103, 'Carol', 'Eng', 91000)}r(EMPLOYEE)_after = {(101, 'Alice', 'Eng', 93500), (103, 'Carol', 'Eng', 100100)}You can now represent relation instances using formal set notation, practical tabular format, and mathematical mapping conventions. Next, we explore constraint notation—how to formally specify the rules that valid instances must satisfy.