Database Management SystemsTuples and Rows

Tuples and Rows: The Atomic Units of Relational Data

LevelBeginner

Duration60 mins

TopicTuples and Rows

5 / 5

Tuple Operations

Operating on the Fundamental Unit

Tuples are the fundamental units of data in the relational model, but data is rarely static. Databases must create new tuples to capture new facts, retrieve existing tuples to answer questions, modify tuples to reflect changes, and delete tuples when facts are no longer true.

Beyond these basic CRUD operations, the relational model defines precise mathematical operations on tuples: projection to extract subsets of attributes, concatenation to combine tuples, and comparison to determine equality. Understanding these operations at the tuple level provides the foundation for understanding relational algebra and SQL.

In this page, we explore tuple operations from both theoretical and practical perspectives, bridging the gap between the abstract mathematical model and real-world database manipulation.

What You Will Learn

By the end of this page, you will understand the full spectrum of tuple operations: creation, retrieval, update, and deletion; projection and concatenation; comparison and ordering; and the constraints that govern valid tuple manipulations. You will see how SQL DML statements map to these fundamental operations.

Tuple Creation

Tuple creation is the act of introducing a new tuple into a relation, asserting a new fact about the world. In the formal model, this means adding an element to the set; in SQL, it's the INSERT statement.

Formal Definition:

Given a relation R and a new tuple t (where t is valid for R's schema), the creation operation produces a new relation R' = R ∪ {t}.

Note that if t is already in R (a duplicate), then R' = R—no change occurs. This is set behavior; real databases typically reject the duplicate with an error.

Tuple Creation Requirements:

Requirements for Valid Tuple Creation

•Schema Conformance: The tuple must have a value for each attribute in the relation's schema (or NULL if permitted).
•Domain Constraint: Each attribute value must belong to the attribute's domain (e.g., integers must be integers).
•Key Uniqueness: The tuple's key values must be unique within the relation—no existing tuple can have the same key.
•Referential Integrity: If the tuple contains foreign keys, the referenced tuples must exist in the target relations.
•User-Defined Constraints: CHECK constraints, triggers, and other rules must be satisfied.

tuple_creation.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- Basic tuple creation (INSERT)
INSERT INTO Employees (emp_id, name, department, salary)
VALUES (101, 'Alice Chen', 'Engineering', 95000);
 
-- Creating tuple by specifying all columns in order
INSERT INTO Employees VALUES (102, 'Bob Kumar', 'Marketing', 78000);
 
-- Creating tuple with partial values (NULLs or defaults for others)
INSERT INTO Employees (emp_id, name)
VALUES (103, 'Carol Smith');
-- department and salary get NULL or DEFAULT
 
-- Creating multiple tuples in one statement
INSERT INTO Employees (emp_id, name, department, salary)
VALUES 
    (104, 'David Lee', 'Sales', 85000),
    (105, 'Eve Wilson', 'HR', 72000),
    (106, 'Frank Brown', 'Engineering', 98000);
 
-- Creating tuples from a query (INSERT...SELECT)
INSERT INTO HighEarners (emp_id, name, salary)
SELECT emp_id, name, salary FROM Employees WHERE salary > 100000;
 
-- Tuple creation can fail:
-- 1. Primary key violation
INSERT INTO Employees VALUES (101, 'Duplicate', 'Test', 0);
-- ERROR: Duplicate entry '101' for key 'PRIMARY'
 
-- 2. Foreign key violation
INSERT INTO Orders (order_id, customer_id, total)
VALUES (1, 9999, 100.00);  -- customer_id 9999 doesn't exist
-- ERROR: Foreign key constraint fails
 
-- 3. Check constraint violation
INSERT INTO Employees VALUES (107, 'Test', 'Dept', -5000);
-- ERROR: Check constraint 'salary_positive' violated

Atomic Creation

Tuple creation is atomic: the tuple is either fully added or not added at all. If any constraint fails, the entire INSERT is rejected. Multi-tuple INSERTs (with VALUES lists) may either all succeed or all fail, depending on database settings.

Tuple Retrieval

Tuple retrieval is the process of extracting tuples from a relation that satisfy specified conditions. In relational algebra, this is the selection operation; in SQL, it's the SELECT statement with a WHERE clause.

Formal Definition:

The selection σ_condition(R) returns a new relation containing all tuples t ∈ R where condition(t) is true.

σ_{salary > 80000}(Employees) = {t ∈ Employees | t.salary > 80000}

Types of Retrieval:

Point Query:

Retrieves a single tuple (or a small set of tuples) by key:

-- Retrieve one specific tuple
SELECT * FROM Employees WHERE emp_id = 101;

-- Point lookup on unique column
SELECT * FROM Users WHERE email = 'alice@example.com';

Characteristics:

Returns exactly 0 or 1 tuple (for primary key lookups)
Highly efficient with index support (O(log n) or O(1))
Most common operation in transactional systems
The foundation of key-value access patterns

Complex Retrieval Conditions:

Retrieval conditions can be arbitrarily complex, combining:

Comparison operators: =, <>, <, <=, >, >=
Logical operators: AND, OR, NOT
Pattern matching: LIKE, SIMILAR TO, regex
Membership tests: IN, EXISTS, ANY, ALL
NULL tests: IS NULL, IS NOT NULL

-- Complex condition
SELECT * FROM Employees
WHERE department IN ('Engineering', 'Research')
  AND salary >= 90000
  AND name LIKE 'A%'
  AND manager_id IS NOT NULL;

Each condition narrows the selection, reducing the result cardinality.

Tuple Update

Tuple update modifies one or more attribute values of existing tuples. From the formal perspective, tuples are immutable values—"updating" a tuple really means replacing it with a new tuple that differs in some attributes.

Formal Model:

Update isn't a primitive relational operation. In pure relational algebra, we would:

Delete the old tuple: R' = R − {old_tuple}
Insert the new tuple: R'' = R' ∪ {new_tuple}

SQL's UPDATE statement provides a convenient syntax for this logical replacement.

Update Semantics:

tuple_update.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- Basic update: change one attribute of one tuple
UPDATE Employees
SET salary = 100000
WHERE emp_id = 101;
 
-- Update multiple attributes
UPDATE Employees
SET salary = 105000, department = 'Research'
WHERE emp_id = 101;
 
-- Update multiple tuples (all matching the condition)
UPDATE Employees
SET salary = salary * 1.05  -- 5% raise
WHERE department = 'Engineering';
 
-- Conditional update with CASE
UPDATE Employees
SET salary = CASE
    WHEN performance_rating = 'Excellent' THEN salary * 1.10
    WHEN performance_rating = 'Good' THEN salary * 1.05
    ELSE salary
END;
 
-- Update with subquery
UPDATE Employees e
SET department_name = (
    SELECT d.name FROM Departments d WHERE d.id = e.department_id
);
 
-- Update can fail due to constraints:
-- Primary key change violates uniqueness
UPDATE Employees SET emp_id = 102 WHERE emp_id = 101;
-- ERROR if 102 already exists
 
-- Foreign key violation after update
UPDATE Employees SET department_id = 9999 WHERE emp_id = 101;
-- ERROR if department 9999 doesn't exist
 
-- Check constraint violation
UPDATE Employees SET salary = -1000 WHERE emp_id = 101;
-- ERROR: salary must be positive

Key Updates Are Dangerous

Updating primary key values is generally discouraged. It requires updating all foreign keys that reference the old value (if cascading) and can cause significant database churn. Prefer unchanging surrogate keys and update only non-key attributes.

Update Anomalies:

Poorly designed schemas can lead to update anomalies:

Redundancy Anomaly: If employee department name is stored in every employee tuple, updating the department name requires updating all employees in that department.
Inconsistent Update: If some tuples are updated and others are missed, the same department has different names in different rows.
Lost Update: In concurrent scenarios, two transactions might read the same tuple, both update it independently, and the earlier write is lost.

Normalization and proper transaction isolation address these issues.

Tuple Deletion

Tuple deletion removes tuples from a relation, retracting facts from the database. In the formal model, this is set subtraction: R' = R − {tuple_to_delete}.

Formal Definition:

Deletion based on a condition: Delete all tuples matching condition is equivalent to:

R' = R − σ_condition(R)

The result R' contains only tuples that do NOT match the deletion condition.

Deletion in SQL:

tuple_deletion.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Delete a specific tuple by key
DELETE FROM Employees WHERE emp_id = 101;
 
-- Delete multiple tuples matching a condition
DELETE FROM Employees WHERE department = 'Discontinued';
 
-- Delete all tuples (but keep the table structure)
DELETE FROM Logs WHERE log_date < '2023-01-01';
 
-- Delete all tuples (dangerous!)
DELETE FROM TempTable;
 
-- Delete with subquery
DELETE FROM Orders
WHERE customer_id IN (
    SELECT customer_id FROM Customers WHERE status = 'inactive'
);
 
-- Deletion can fail due to referential integrity:
-- Parent tuple has children
DELETE FROM Departments WHERE dept_id = 10;
-- ERROR: Cannot delete - employees reference this department
 
-- Options for handling referenced tuples:
-- ON DELETE CASCADE: Delete children automatically
-- ON DELETE SET NULL: Set foreign key to NULL
-- ON DELETE RESTRICT: Prevent deletion (default)
-- ON DELETE SET DEFAULT: Set foreign key to default value

Deletion Considerations

•Referential Integrity: Deleting a tuple referenced by foreign keys requires cascade, set null, or explicit child deletion first.
•No UNDO: In most configurations, DELETE is permanent (within the transaction). Test conditions carefully before executing.
•Triggers: DELETE triggers may execute additional logic—auditing, cascading, validation—before or after the deletion.
•Soft Delete Alternative: Many applications use a 'deleted_at' timestamp instead of actual deletion, preserving history and enabling recovery.
•Performance: Deleting many tuples can be slow. Consider TRUNCATE for deleting all rows, or batch deletes for large volumes.

DELETE vs TRUNCATE

DELETE removes tuples one by one, logging each removal and firing triggers. TRUNCATE removes all tuples in a single operation, is much faster, but doesn't fire row-level triggers and typically can't be rolled back. Use DELETE for selective removal, TRUNCATE for complete table clearing.

Tuple Projection

Tuple projection extracts a subset of attributes from a tuple, creating a narrower tuple. This is the tuple-level operation underlying the relational projection operator π.

Formal Definition:

For a tuple t over schema R = {A₁, A₂, ..., Aₙ} and an attribute set X ⊆ R, the projection t[X] is a new tuple over schema X containing only the attributes in X.

t = {A: 1, B: 2, C: 3, D: 4}
t[{A, C}] = {A: 1, C: 3}  -- Projection onto attributes A and C

Properties of Tuple Projection:

Projection Properties

•Reduces Degree: t[X] has degree |X|, which is ≤ degree of t.
•Preserves Values: For each attribute A in X, t[X][A] = t[A].
•Idempotent: Projecting on the same attributes twice gives the same result: (t[X])[X] = t[X].
•Monotonic: If X ⊆ Y, then (t[Y])[X] = t[X]. Projecting then projecting again is the same as projecting to the smaller set.

tuple_projection.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- SQL projection: SELECT specific columns
SELECT name, salary FROM Employees WHERE emp_id = 101;
-- Returns: {name: 'Alice', salary: 95000}
-- Original tuple had more attributes, but we projected to just two
 
-- Projection with all tuples (relation-level projection)
SELECT name, department FROM Employees;
-- Projects every tuple to just {name, department}
 
-- Note: Relation projection may eliminate duplicates
SELECT department FROM Employees;
-- If multiple employees share a department, duplicates appear
SELECT DISTINCT department FROM Employees;
-- DISTINCT enforces set semantics
 
-- Computed projection (derived attributes)
SELECT name, salary * 12 AS annual_salary FROM Employees;
-- annual_salary is computed, not stored
 
-- Projection reordering (columns can appear in any order)
SELECT salary, name FROM Employees;  -- Same as SELECT name, salary

Projection and Duplicate Elimination:

When projecting a relation, multiple tuples may project to identical subtuples:

Original tuples:
  {name: 'Alice', dept: 'Eng'}
  {name: 'Bob', dept: 'Eng'}
  {name: 'Carol', dept: 'Sales'}

π_{dept}(R) before duplicate elimination:
  {dept: 'Eng'}
  {dept: 'Eng'}  -- Duplicate!
  {dept: 'Sales'}

π_{dept}(R) after duplicate elimination:
  {dept: 'Eng'}
  {dept: 'Sales'}

In the pure relational model, duplicates are automatically eliminated (relations are sets). In SQL, duplicates remain unless DISTINCT is specified.

Tuple Concatenation

Tuple concatenation (also called tuple union or tuple merge) combines two tuples over disjoint schemas into a single wider tuple. This is the tuple-level operation underlying Cartesian product and join.

Formal Definition:

For tuples t₁ over schema R and t₂ over schema S (where R ∩ S = ∅, i.e., no shared attributes), the concatenation t₁ ∘ t₂ is a tuple over schema R ∪ S:

t₁ = {A: 1, B: 2}
t₂ = {C: 3, D: 4}
t₁ ∘ t₂ = {A: 1, B: 2, C: 3, D: 4}

For Natural Join (overlapping schemas):

If t₁ and t₂ share attributes X, concatenation is valid only if they agree on X:

t₁ = {A: 1, B: 2, C: 3}  -- schema {A, B, C}
t₂ = {B: 2, D: 4}         -- schema {B, D}, overlapping on B

Since t₁[B] = t₂[B] = 2, we can concatenate:
t₁ ⋈ t₂ = {A: 1, B: 2, C: 3, D: 4}  -- B appears once

Converting Mermaid diagram...

Concatenation in SQL:

SQL produces concatenated tuples through joins:

-- Cartesian product: concatenate every tuple from A with every tuple from B
SELECT * FROM Employees, Departments;
-- Each result row is a concatenation of one Employees tuple with one Departments tuple

-- Inner join: concatenate matching tuples
SELECT e.*, d.*
FROM Employees e
JOIN Departments d ON e.dept_id = d.dept_id;
-- Concatenation for pairs where dept_id matches

Concatenation Properties:

Increases Degree: degree(t₁ ∘ t₂) = degree(t₁) + degree(t₂) for disjoint schemas
Commutative (logically): t₁ ∘ t₂ contains same information as t₂ ∘ t₁
Associative: (t₁ ∘ t₂) ∘ t₃ = t₁ ∘ (t₂ ∘ t₃)

Attribute Name Conflicts

When concatenating tuples from relations with same-named attributes (but different meanings), renaming is required. SQL uses table aliases and column qualifiers (e.g., e.name, d.name) to distinguish. In pure relational algebra, the rename operator ρ handles this.

Tuple Comparison and Ordering

Tuple comparison determines relationships between tuples—equality, inequality, and ordering. These comparisons underpin query predicates, join conditions, and duplicate detection.

Equality Comparison:

Two tuples t₁ and t₂ over the same schema R are equal (t₁ = t₂) iff:

For every attribute A ∈ R: t₁[A] = t₂[A]

If any attribute values differ, the tuples are not equal.

Ordering Comparison:

Unlike sets (which are unordered), tuples can be ordered when a comparison criterion is defined. SQL's ORDER BY induces an ordering on tuples based on specified attributes.

tuple_comparison.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- Tuple equality in conditions (comparing attribute values)
SELECT * FROM Employees WHERE name = 'Alice' AND salary = 95000;
 
-- Comparing tuples implicitly (detecting duplicates)
SELECT DISTINCT * FROM Transactions;
-- Tuples with all equal values collapse to one
 
-- Tuple ordering for result presentation
SELECT * FROM Employees ORDER BY salary DESC, name ASC;
 
-- Comparing tuples from different tables (join condition)
SELECT * FROM Orders o JOIN Customers c 
ON o.customer_id = c.customer_id;  -- Equality on key attribute
 
-- Compound tuple comparison (row constructor syntax)
SELECT * FROM Employees 
WHERE (department, salary) = ('Engineering', 95000);
-- Matches tuples where both conditions hold
 
-- Tuple comparison for range queries
SELECT * FROM Employees
WHERE (department, salary) > ('Engineering', 90000);
-- Lexicographic comparison: first by department, then by salary

Lexicographic Ordering:

When comparing tuples on multiple attributes, lexicographic order (like dictionary order) applies:

Compare the first ORDER BY attribute
If equal, compare the second attribute
Continue until a difference is found or all attributes are compared

Example:

(Alice, 95000) < (Alice, 100000)  -- First fields equal, compare second
(Alice, 95000) < (Bob, 50000)     -- 'Alice' < 'Bob' alphabetically

NULL Handling in Comparison:

NULL complicates comparisons:

NULL = NULL → UNKNOWN (not TRUE!)
NULL < any_value → UNKNOWN
NULLS FIRST / NULLS LAST in ORDER BY specifies NULL position

For grouping and DISTINCT, NULLs are typically treated as equal (grouped together).

Row Subqueries

SQL supports row-level comparison with row constructors: (a, b) = (x, y) is equivalent to a = x AND b = y. This is useful for composite key comparisons and EXISTS checks. Not all databases support full row comparison syntax equally.

Summary: The Tuple Operations Toolkit

We have explored the complete spectrum of tuple operations—from basic CRUD to mathematical transformations. Let's consolidate the essential understanding:

Key Takeaways

•Tuple creation (INSERT) adds new facts — New tuples must satisfy schema, domain, key, and referential constraints.
•Tuple retrieval (SELECT) extracts matching facts — Point queries, range queries, and full scans retrieve tuples based on predicates.
•Tuple update (UPDATE) replaces attribute values — Conceptually, this is delete + insert; constraints must be maintained.
•Tuple deletion (DELETE) removes facts — Referential integrity must be handled through cascading, nullifying, or restriction.
•Tuple projection narrows tuples — Extracting a subset of attributes creates a tuple conforming to a smaller schema.
•Tuple concatenation widens tuples — Combining tuples from different relations produces broader result tuples.
•Tuple comparison enables predicates and ordering — Equality, inequality, and lexicographic ordering underpin query logic.
•All operations respect constraints — Keys, foreign keys, domains, and checks govern what operations are valid.

Module Complete:

With this page, we conclude our deep exploration of tuples and rows. You now understand tuples from multiple perspectives: their formal definition, their orderless and unique nature, the dimensions of degree and cardinality, and the full range of operations that manipulate them.

This foundation prepares you for the upcoming modules on keys, integrity constraints, and relational algebra, where tuple-level understanding scales to relation-level reasoning.

Module Complete

Congratulations! You have mastered the fundamentals of tuples and rows in the relational model. You understand what tuples are, how they are ordered (they aren't), why they must be unique, how to measure relations in degree and cardinality, and the complete set of operations for manipulating tuples. This knowledge forms the bedrock for understanding relational databases at a deep, principled level.

5 / 5

Loading learning content...

Database Management SystemsTuples and Rows

Tuples and Rows: The Atomic Units of Relational Data

LevelBeginner

Duration60 mins

TopicTuples and Rows

5 / 5

Tuple Operations

Operating on the Fundamental Unit

In this page, we explore tuple operations from both theoretical and practical perspectives, bridging the gap between the abstract mathematical model and real-world database manipulation.

What You Will Learn

Tuple Creation

Formal Definition:

Given a relation R and a new tuple t (where t is valid for R's schema), the creation operation produces a new relation R' = R ∪ {t}.

Note that if t is already in R (a duplicate), then R' = R—no change occurs. This is set behavior; real databases typically reject the duplicate with an error.

Tuple Creation Requirements:

Requirements for Valid Tuple Creation

•Schema Conformance: The tuple must have a value for each attribute in the relation's schema (or NULL if permitted).
•Domain Constraint: Each attribute value must belong to the attribute's domain (e.g., integers must be integers).
•Key Uniqueness: The tuple's key values must be unique within the relation—no existing tuple can have the same key.
•Referential Integrity: If the tuple contains foreign keys, the referenced tuples must exist in the target relations.
•User-Defined Constraints: CHECK constraints, triggers, and other rules must be satisfied.

tuple_creation.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- Basic tuple creation (INSERT)
INSERT INTO Employees (emp_id, name, department, salary)
VALUES (101, 'Alice Chen', 'Engineering', 95000);
 
-- Creating tuple by specifying all columns in order
INSERT INTO Employees VALUES (102, 'Bob Kumar', 'Marketing', 78000);
 
-- Creating tuple with partial values (NULLs or defaults for others)
INSERT INTO Employees (emp_id, name)
VALUES (103, 'Carol Smith');
-- department and salary get NULL or DEFAULT
 
-- Creating multiple tuples in one statement
INSERT INTO Employees (emp_id, name, department, salary)
VALUES 
    (104, 'David Lee', 'Sales', 85000),
    (105, 'Eve Wilson', 'HR', 72000),
    (106, 'Frank Brown', 'Engineering', 98000);
 
-- Creating tuples from a query (INSERT...SELECT)
INSERT INTO HighEarners (emp_id, name, salary)
SELECT emp_id, name, salary FROM Employees WHERE salary > 100000;
 
-- Tuple creation can fail:
-- 1. Primary key violation
INSERT INTO Employees VALUES (101, 'Duplicate', 'Test', 0);
-- ERROR: Duplicate entry '101' for key 'PRIMARY'
 
-- 2. Foreign key violation
INSERT INTO Orders (order_id, customer_id, total)
VALUES (1, 9999, 100.00);  -- customer_id 9999 doesn't exist
-- ERROR: Foreign key constraint fails
 
-- 3. Check constraint violation
INSERT INTO Employees VALUES (107, 'Test', 'Dept', -5000);
-- ERROR: Check constraint 'salary_positive' violated

Atomic Creation

Tuple Retrieval

Formal Definition:

The selection σ_condition(R) returns a new relation containing all tuples t ∈ R where condition(t) is true.

σ_{salary > 80000}(Employees) = {t ∈ Employees | t.salary > 80000}

Types of Retrieval:

Point Query:

Retrieves a single tuple (or a small set of tuples) by key:

-- Retrieve one specific tuple
SELECT * FROM Employees WHERE emp_id = 101;

-- Point lookup on unique column
SELECT * FROM Users WHERE email = 'alice@example.com';

Characteristics:

Returns exactly 0 or 1 tuple (for primary key lookups)
Highly efficient with index support (O(log n) or O(1))
Most common operation in transactional systems
The foundation of key-value access patterns

Complex Retrieval Conditions:

Retrieval conditions can be arbitrarily complex, combining:

Comparison operators: =, <>, <, <=, >, >=
Logical operators: AND, OR, NOT
Pattern matching: LIKE, SIMILAR TO, regex
Membership tests: IN, EXISTS, ANY, ALL
NULL tests: IS NULL, IS NOT NULL

-- Complex condition
SELECT * FROM Employees
WHERE department IN ('Engineering', 'Research')
  AND salary >= 90000
  AND name LIKE 'A%'
  AND manager_id IS NOT NULL;

Each condition narrows the selection, reducing the result cardinality.

Tuple Update

Formal Model:

Update isn't a primitive relational operation. In pure relational algebra, we would:

Delete the old tuple: R' = R − {old_tuple}
Insert the new tuple: R'' = R' ∪ {new_tuple}

SQL's UPDATE statement provides a convenient syntax for this logical replacement.

Update Semantics:

tuple_update.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- Basic update: change one attribute of one tuple
UPDATE Employees
SET salary = 100000
WHERE emp_id = 101;
 
-- Update multiple attributes
UPDATE Employees
SET salary = 105000, department = 'Research'
WHERE emp_id = 101;
 
-- Update multiple tuples (all matching the condition)
UPDATE Employees
SET salary = salary * 1.05  -- 5% raise
WHERE department = 'Engineering';
 
-- Conditional update with CASE
UPDATE Employees
SET salary = CASE
    WHEN performance_rating = 'Excellent' THEN salary * 1.10
    WHEN performance_rating = 'Good' THEN salary * 1.05
    ELSE salary
END;
 
-- Update with subquery
UPDATE Employees e
SET department_name = (
    SELECT d.name FROM Departments d WHERE d.id = e.department_id
);
 
-- Update can fail due to constraints:
-- Primary key change violates uniqueness
UPDATE Employees SET emp_id = 102 WHERE emp_id = 101;
-- ERROR if 102 already exists
 
-- Foreign key violation after update
UPDATE Employees SET department_id = 9999 WHERE emp_id = 101;
-- ERROR if department 9999 doesn't exist
 
-- Check constraint violation
UPDATE Employees SET salary = -1000 WHERE emp_id = 101;
-- ERROR: salary must be positive

Key Updates Are Dangerous

Update Anomalies:

Poorly designed schemas can lead to update anomalies:

Redundancy Anomaly: If employee department name is stored in every employee tuple, updating the department name requires updating all employees in that department.
Inconsistent Update: If some tuples are updated and others are missed, the same department has different names in different rows.
Lost Update: In concurrent scenarios, two transactions might read the same tuple, both update it independently, and the earlier write is lost.

Normalization and proper transaction isolation address these issues.

Tuple Deletion

Tuple deletion removes tuples from a relation, retracting facts from the database. In the formal model, this is set subtraction: R' = R − {tuple_to_delete}.

Formal Definition:

Deletion based on a condition: Delete all tuples matching condition is equivalent to:

R' = R − σ_condition(R)

The result R' contains only tuples that do NOT match the deletion condition.

Deletion in SQL:

tuple_deletion.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Delete a specific tuple by key
DELETE FROM Employees WHERE emp_id = 101;
 
-- Delete multiple tuples matching a condition
DELETE FROM Employees WHERE department = 'Discontinued';
 
-- Delete all tuples (but keep the table structure)
DELETE FROM Logs WHERE log_date < '2023-01-01';
 
-- Delete all tuples (dangerous!)
DELETE FROM TempTable;
 
-- Delete with subquery
DELETE FROM Orders
WHERE customer_id IN (
    SELECT customer_id FROM Customers WHERE status = 'inactive'
);
 
-- Deletion can fail due to referential integrity:
-- Parent tuple has children
DELETE FROM Departments WHERE dept_id = 10;
-- ERROR: Cannot delete - employees reference this department
 
-- Options for handling referenced tuples:
-- ON DELETE CASCADE: Delete children automatically
-- ON DELETE SET NULL: Set foreign key to NULL
-- ON DELETE RESTRICT: Prevent deletion (default)
-- ON DELETE SET DEFAULT: Set foreign key to default value

Deletion Considerations

•Referential Integrity: Deleting a tuple referenced by foreign keys requires cascade, set null, or explicit child deletion first.
•No UNDO: In most configurations, DELETE is permanent (within the transaction). Test conditions carefully before executing.
•Triggers: DELETE triggers may execute additional logic—auditing, cascading, validation—before or after the deletion.
•Soft Delete Alternative: Many applications use a 'deleted_at' timestamp instead of actual deletion, preserving history and enabling recovery.
•Performance: Deleting many tuples can be slow. Consider TRUNCATE for deleting all rows, or batch deletes for large volumes.

DELETE vs TRUNCATE

Tuple Projection

Tuple projection extracts a subset of attributes from a tuple, creating a narrower tuple. This is the tuple-level operation underlying the relational projection operator π.

Formal Definition:

For a tuple t over schema R = {A₁, A₂, ..., Aₙ} and an attribute set X ⊆ R, the projection t[X] is a new tuple over schema X containing only the attributes in X.

t = {A: 1, B: 2, C: 3, D: 4}
t[{A, C}] = {A: 1, C: 3}  -- Projection onto attributes A and C

Properties of Tuple Projection:

Projection Properties

•Reduces Degree: t[X] has degree |X|, which is ≤ degree of t.
•Preserves Values: For each attribute A in X, t[X][A] = t[A].
•Idempotent: Projecting on the same attributes twice gives the same result: (t[X])[X] = t[X].
•Monotonic: If X ⊆ Y, then (t[Y])[X] = t[X]. Projecting then projecting again is the same as projecting to the smaller set.

tuple_projection.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- SQL projection: SELECT specific columns
SELECT name, salary FROM Employees WHERE emp_id = 101;
-- Returns: {name: 'Alice', salary: 95000}
-- Original tuple had more attributes, but we projected to just two
 
-- Projection with all tuples (relation-level projection)
SELECT name, department FROM Employees;
-- Projects every tuple to just {name, department}
 
-- Note: Relation projection may eliminate duplicates
SELECT department FROM Employees;
-- If multiple employees share a department, duplicates appear
SELECT DISTINCT department FROM Employees;
-- DISTINCT enforces set semantics
 
-- Computed projection (derived attributes)
SELECT name, salary * 12 AS annual_salary FROM Employees;
-- annual_salary is computed, not stored
 
-- Projection reordering (columns can appear in any order)
SELECT salary, name FROM Employees;  -- Same as SELECT name, salary

Projection and Duplicate Elimination:

When projecting a relation, multiple tuples may project to identical subtuples:

Original tuples:
  {name: 'Alice', dept: 'Eng'}
  {name: 'Bob', dept: 'Eng'}
  {name: 'Carol', dept: 'Sales'}

π_{dept}(R) before duplicate elimination:
  {dept: 'Eng'}
  {dept: 'Eng'}  -- Duplicate!
  {dept: 'Sales'}

π_{dept}(R) after duplicate elimination:
  {dept: 'Eng'}
  {dept: 'Sales'}

In the pure relational model, duplicates are automatically eliminated (relations are sets). In SQL, duplicates remain unless DISTINCT is specified.

Tuple Concatenation

Formal Definition:

For tuples t₁ over schema R and t₂ over schema S (where R ∩ S = ∅, i.e., no shared attributes), the concatenation t₁ ∘ t₂ is a tuple over schema R ∪ S:

t₁ = {A: 1, B: 2}
t₂ = {C: 3, D: 4}
t₁ ∘ t₂ = {A: 1, B: 2, C: 3, D: 4}

For Natural Join (overlapping schemas):

If t₁ and t₂ share attributes X, concatenation is valid only if they agree on X:

t₁ = {A: 1, B: 2, C: 3}  -- schema {A, B, C}
t₂ = {B: 2, D: 4}         -- schema {B, D}, overlapping on B

Since t₁[B] = t₂[B] = 2, we can concatenate:
t₁ ⋈ t₂ = {A: 1, B: 2, C: 3, D: 4}  -- B appears once

Converting Mermaid diagram...

Concatenation in SQL:

SQL produces concatenated tuples through joins:

-- Cartesian product: concatenate every tuple from A with every tuple from B
SELECT * FROM Employees, Departments;
-- Each result row is a concatenation of one Employees tuple with one Departments tuple

-- Inner join: concatenate matching tuples
SELECT e.*, d.*
FROM Employees e
JOIN Departments d ON e.dept_id = d.dept_id;
-- Concatenation for pairs where dept_id matches

Concatenation Properties:

Increases Degree: degree(t₁ ∘ t₂) = degree(t₁) + degree(t₂) for disjoint schemas
Commutative (logically): t₁ ∘ t₂ contains same information as t₂ ∘ t₁
Associative: (t₁ ∘ t₂) ∘ t₃ = t₁ ∘ (t₂ ∘ t₃)

Attribute Name Conflicts

Tuple Comparison and Ordering

Tuple comparison determines relationships between tuples—equality, inequality, and ordering. These comparisons underpin query predicates, join conditions, and duplicate detection.

Equality Comparison:

Two tuples t₁ and t₂ over the same schema R are equal (t₁ = t₂) iff:

For every attribute A ∈ R: t₁[A] = t₂[A]

If any attribute values differ, the tuples are not equal.

Ordering Comparison:

Unlike sets (which are unordered), tuples can be ordered when a comparison criterion is defined. SQL's ORDER BY induces an ordering on tuples based on specified attributes.

tuple_comparison.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- Tuple equality in conditions (comparing attribute values)
SELECT * FROM Employees WHERE name = 'Alice' AND salary = 95000;
 
-- Comparing tuples implicitly (detecting duplicates)
SELECT DISTINCT * FROM Transactions;
-- Tuples with all equal values collapse to one
 
-- Tuple ordering for result presentation
SELECT * FROM Employees ORDER BY salary DESC, name ASC;
 
-- Comparing tuples from different tables (join condition)
SELECT * FROM Orders o JOIN Customers c 
ON o.customer_id = c.customer_id;  -- Equality on key attribute
 
-- Compound tuple comparison (row constructor syntax)
SELECT * FROM Employees 
WHERE (department, salary) = ('Engineering', 95000);
-- Matches tuples where both conditions hold
 
-- Tuple comparison for range queries
SELECT * FROM Employees
WHERE (department, salary) > ('Engineering', 90000);
-- Lexicographic comparison: first by department, then by salary

Lexicographic Ordering:

When comparing tuples on multiple attributes, lexicographic order (like dictionary order) applies:

Compare the first ORDER BY attribute
If equal, compare the second attribute
Continue until a difference is found or all attributes are compared

Example:

(Alice, 95000) < (Alice, 100000)  -- First fields equal, compare second
(Alice, 95000) < (Bob, 50000)     -- 'Alice' < 'Bob' alphabetically

NULL Handling in Comparison:

NULL complicates comparisons:

NULL = NULL → UNKNOWN (not TRUE!)
NULL < any_value → UNKNOWN
NULLS FIRST / NULLS LAST in ORDER BY specifies NULL position

For grouping and DISTINCT, NULLs are typically treated as equal (grouped together).

Row Subqueries

Summary: The Tuple Operations Toolkit

We have explored the complete spectrum of tuple operations—from basic CRUD to mathematical transformations. Let's consolidate the essential understanding:

Key Takeaways

•Tuple creation (INSERT) adds new facts — New tuples must satisfy schema, domain, key, and referential constraints.
•Tuple retrieval (SELECT) extracts matching facts — Point queries, range queries, and full scans retrieve tuples based on predicates.
•Tuple update (UPDATE) replaces attribute values — Conceptually, this is delete + insert; constraints must be maintained.
•Tuple deletion (DELETE) removes facts — Referential integrity must be handled through cascading, nullifying, or restriction.
•Tuple projection narrows tuples — Extracting a subset of attributes creates a tuple conforming to a smaller schema.
•Tuple concatenation widens tuples — Combining tuples from different relations produces broader result tuples.
•Tuple comparison enables predicates and ordering — Equality, inequality, and lexicographic ordering underpin query logic.
•All operations respect constraints — Keys, foreign keys, domains, and checks govern what operations are valid.

Module Complete:

This foundation prepares you for the upcoming modules on keys, integrity constraints, and relational algebra, where tuple-level understanding scales to relation-level reasoning.

Module Complete

5 / 5