DBM lecture notes

Projection Operator

• Definition: The projection operator is used for selecting specific columns from a table without affecting the rows (does not manipulate rows).

Example Table: Instructor Table

• Purpose of ID Column:

  • The ID column differentiates between rows within the instructor table.

  • Other attributes like name, department, and salary can repeat across different IDs.

  • Example: Different individuals can have the same name and salary but different IDs.

Application of Projection Operator

• If you apply the projection operator to select attributes:

  • Example Output: {Name, Department Name, Salary}

  • The ID column is dropped, leading to potential duplicate rows in the resulting set.

  • Duplicate tuples are not allowed since relations (or relation instances) are defined as sets.

Relations and Tuples

• Definition of Relation:

  • Relation instances are considered as sets and cannot have duplicate tuples.

• Visual Representation:

  • Each row of a table can be visualized as a tuple in a set.

Composition of Operators

• Operators Introduced:

  • Select Operator: Used to filter rows based on a specified condition.

  • Projection Operator: Used to filter columns based on specified attributes.

• Operator Composition:

  • Example Process:

  • First, apply the Select operator based on a condition, e.g., department = Physics.

  • This does not change the number of columns, only the rows selected remain where the condition is true.

  • Then, apply the Projection operator on the result to retrieve only specific columns, e.g., Name.

Outcome of Composition

• The output of the combination ensures that your end result contains only the names of instructors attached to the specified department filtering.

Further Examples of Operators

• Implementing Select and Project with Conditions:

  • Example Condition:

  • Select instructors where department name = Business and then project their names.

• Salary Condition:

  • Select based on a salary higher than 50,000, then perform projection to get department names.

Concepts of Functions and Relations

• The composition can be thought of as passing outputs between functions in mathematics (e.g., g(f(x))).

• Sample conditions can be:

  • Department Name should be Computer Science.

  • Salary should be greater than 90,000.

• This exemplifies the concept of pulling desired rows and columns together.

Cartesian Product

• Definition:

  • A Cartesian product between two sets (relations) couples every row in the first set with every row in the second set.

• Visualization Example:

  • Set A: {(a1, a2)}, Set B: {(b1, b2)}.

  • The Cartesian product generates combinations, resulting in tuples formatted like this:

  • {(a1, a2, b1, b2), (a1, a2, b2, b2)}.

• Table Dimensions:

  • The output will have rows equal to the product of the number of rows in both tables, and columns equal to the sum of the columns in each table.

Example: Calculating Rows and Columns

• Given:

  • Table A has 2 rows and 2 columns.

  • Table B has 2 rows and 1 column.

• Result from Cartesian Product:

  • Rows: 2 rows (A) * 2 rows (B) = 4 rows in total.

  • Columns: 2 columns (A) + 1 column (B) = 3 columns in total.

Joining Two Tables

• Purpose of Join Operation:

  • To combine two tables based on shared attributes, allowing for more comprehensive data insights.

• Example: Combining the instructor table with a department table using the unique department key ensures the relationships remain intact.

  • Significance of Keys: Keys ensure unique identification of rows, like primary keys in the database structure.