Introduction

Projection (π) is a fundamental operation in relational algebra used for vertical partitioning. It selects specific columns (attributes) from a relation, thereby reducing the degree of the relation while preserving its cardinality as much as possible.

Key Characteristics

• Vertical Partitioning: Reduces the number of columns while keeping rows intact.
• Elimination of Duplicates: Automatically removes duplicate rows due to its foundation in set theory.
• Non-Commutativity: The order of projection operations matters.
• Changes in Degree: The degree of the resulting relation depends on the number of attributes in the projection.

Examples

Example 1: Basic Projection

Given the Employee table:

emp_id | name   | dept_id | salary
101    | Alice  | 1       | 12000
102    | Bob    | 3       | 15000
103    | Carol  | 2       | 10000
104    | David  | 3       | 18000
105    | Eve    | 1       | 9000
            

Query: Project emp_id and name.

π(emp_id, name)(Employee)

Result:

emp_id | name
101    | Alice
102    | Bob
103    | Carol
104    | David
105    | Eve
            

Example 2: Duplicate Elimination

Query: Project dept_id.

π(dept_id)(Employee)

Result:

dept_id
1
2
3
            

Degree and Cardinality

• Degree: The number of attributes in the resulting relation. This decreases with projection based on selected attributes.
• Cardinality: The number of tuples (rows). This may decrease due to duplicate elimination.

Projection with Non-Super Key

When projection is applied to attributes that are not super keys, duplicates are removed. For example:

Query: Project dept_id.
Result: Only distinct dept_id values are retained.
            

Applications

Projection is widely used in database queries to extract relevant columns, especially when optimizing data retrieval or reducing the dimensionality of a relation. It is also fundamental in SQL’s SELECT DISTINCT operation.