Projection in Relational Algebra

Projection in Relational Algebra

Projection (π) is another fundamental operation in relational algebra. It is used to select specific columns (attributes) from a relation (table) while eliminating duplicate rows. Unlike selection, which works on rows based on a condition, projection works on the columns, reducing the set of attributes in the result.

Definition of Projection:

• Notation: π_column1, column2, ... (R)
  • π: The symbol for the projection operation.
  • column1, column2, ...: The list of attributes (columns) you want to include in the result. This can be one or more attributes from the relation.
  • R: The relation (table) from which the attributes are selected.

Characteristics of Projection:

• Projection removes duplicate tuples from the result (i.e., the result is a set of distinct tuples).
• The result of a projection is a relation with only the specified columns, maintaining the same number of rows as the number of distinct combinations of the projected attributes.
• Projection does not alter the number of tuples that remain in the result; it only removes the extra attributes (columns).
• It can also be combined with other operations like selection, join, and set operations to form more complex queries.

Result of Projection:

• The result of the projection operation is a relation with only the specified columns from the original relation, and duplicates (if any) are removed.

---

Example:

Consider the following relation Students:

StudentID	Name	Age	Major
1	Alice	20	CS
2	Bob	22	Math
3	Charlie	21	Physics
4	Dave	23	CS
5	Eve	19	Math

Example 1: Projection on a Single Column

If we want to project only the Name of all students from the Students relation, we would use the following projection:

π(Name)(Students)

Result:

Name
Alice
Bob
Charlie
Dave
Eve

• The result contains only the Name column, and there are no duplicates.
• If two students had the same name (e.g., two students named "Alice"), they would both appear in the result, but in our case, all names are unique.

Example 2: Projection on Multiple Columns

If we want to project the Name and Age columns from the Students relation, we would use the following projection:

π(Name, Age)(Students)

Result:

Name	Age
Alice	20
Bob	22
Charlie	21
Dave	23
Eve	19

• The result contains the Name and Age columns.
• Notice that if there were any duplicate Name-Age combinations in the original relation, they would be removed in the result. However, in this case, each combination is unique.

Example 3: Projection with Duplicates

Suppose we modify the Students relation, where there are duplicate rows for some students (same Name and Age):

StudentID	Name	Age	Major
1	Alice	20	CS
2	Bob	22	Math
3	Alice	20	CS
4	Dave	23	CS
5	Bob	22	Math

If we now project on Name and Age, the result would eliminate the duplicate rows:

π(Name, Age)(Students)

Result:

Name	Age
Alice	20
Bob	22
Dave	23

• Even though "Alice" and "Bob" appeared twice in the original relation, the result has only one occurrence of each unique (Name, Age) pair because duplicates are removed in projection.

Example 4: Projection on a Set of Attributes

Let's say we want to find the distinct Majors in the Students table, ignoring other attributes. The query would look like this:

π(Major)(Students)

Result:

Major
CS
Math
Physics

• The result only includes distinct values of the Major column.
• Even if some students have the same major (e.g., two students majoring in "CS"), they appear only once in the result.

---

Projection and Selection Together:

You can combine projection and selection to refine your queries. For example, suppose you want to find the Names of all students majoring in "CS" and who are older than 20. You would first use selection to filter the rows, and then use projection to retrieve the Name column.

π(Name)(σ(Major = 'CS' AND Age > 20)(Students))

Explanation:

• σ(Major = 'CS' AND Age > 20)(Students) selects the students who major in "CS" and are older than 20.
• π(Name) projects only the Name column of the selected rows.

Result:

Name
Dave

• In this case, only Dave satisfies both conditions, so the result only contains his name.

---

Summary of Projection (π) in Relational Algebra:

• Purpose: The projection operation is used to select specific columns from a relation and removes duplicate tuples.
• Syntax: π_column1, column2, ... (R) where column1, column2, ... are the attributes you want in the result and R is the relation.
• Effect: Projection reduces the number of attributes (columns) in the resulting relation, and eliminates duplicate rows.
• Examples:
  • π(Name)(Students) selects only the Name column.
  • π(Name, Age)(Students) selects the Name and Age columns, removing duplicates if any.
• Combining Projection and Selection: You can combine projection and selection to refine your queries further.

Key Points:

• Projection is equivalent to the SELECT clause in SQL.
• It removes duplicate tuples from the result.
• Projection operates on the columns of the relation and affects the schema of the result, not the rows.
• Projection can be combined with selection and other relational algebra operations to perform more complex queries.