Set Difference Operator in Relational Algebra
Relational Algebra is used to play with the data stored in relational databases. Relational Algebra has many operations to play with tables. One of the fundamental operations is set difference. This article will discuss Set Difference, its condition, and its examples.
Key Terms Used in Set Difference Operator
- Relation- In Relational Algebra ‘Table’ is called a Relation.
- Tuple- In Relational Algebra ‘Row’ is called as Tuple.
- Attribute- In Relational Algebra ‘Column’ is called an Attribute.
Set Difference(-)
A binary operation that gives tuples in one relation but not present in another relation. In simple terms, it returns all the rows from the first relation, which is not present in the second one. For Example: Perform Set Difference Operation in relation R and Relation S.
Relation R-S, After Performing Set Difference operation
Here all the Alphabet which are in R but not in S are shown. Note Preference will be given to the first relation attributes in this case ‘Alphabet’ of relation R.
Condition for Set Difference
1. Both relations must have the same number of attributes.
2. The data type of their corresponding attributes must be the same.
Examples
Question 1: Consider the below tables Employee and Student. Perform Set Difference operation as (Employee-Student).
Employee |
|
---|---|
Employee Id |
Employee Name |
1 |
Manish |
2 |
Rohit |
3 |
Shubhum |
4 |
Manish |
Student |
|
---|---|
Student Id |
Student Name |
6 |
Sudesh |
5 |
Deepak |
2 |
Rishav |
4 |
Manish |
9 |
Aman |
Solution:
Step 1: Check the condition for the set difference
- Both the relation have the same number of attributes
- Data Types of Corresponding Attributes are the same
Step 2: Result after performing Set difference (Employee-Student)
Employee-Student |
|
---|---|
Employee Id |
Employee Name |
1 |
Manish |
2 |
Rohit |
3 |
Shubhum |
Detailed Explanation
- In the question, As no specific column is mentioned, we compare both the values in each row.
- Since it is given that we need to show (Employee-Student). So, the new relation will have the attribute name of the Employee i.e.; Employee id and Employee Name.
- From the the employee table only that row will be printed in which none of the value matches in the entire row of student table.
- For Employee IDthe ‘1’, is not present in the student id. So, the entire row is printed.
- For Employee ID ‘2’, it is present in Student ID. So, we check for the Employee Name ‘Ronit’, it is not present for the same student id. So, the entire row is printed.
- For the Employee ID ‘3’, it is not present in the Student ID. So, the entire row is printed.
- For the Employee ID ‘4’, it is present in Student ID. So, check for the Employee Name ‘Manish’, it is also present for the same student Name for same student id. So, the entire row not printed.
Question 2: Consider the below tables Depositer and Borrower. List all the customers names who have accounts but have not taken any loans.
Depositor |
|
---|---|
Customer Name |
Account No |
Jack |
A007 |
Harry |
A001 |
Sam |
A006 |
Alex |
A003 |
Borrower |
|
---|---|
Customer Name |
Loan No |
Smith |
L201 |
Jack |
L211 |
John |
L216 |
Alex |
L214 |
Solution:
Step 1: Check the condition for the set difference
- Both the relation have the same number of attributes
- Data Types of Corresponding Attributes are the same
Step 2: Result after performing Set difference (Depositor-Borrower)
Depositor-Borrower |
---|
Customer Name |
Harry |
Sam |
Detailed Explanation
- In the given question, we asked about the customer name of the Depositor. So, we focus on customer name column.
- Since, the question is interested in Depositor’s Customer Name, we do (Depositor-Borrower).
- Print the Customer name from the depositor table when it is not matched with the customer name in borrower table.
- In the given case, only ‘Harry’ and ‘Sam’ names are not matched. So, we printed their names only.
Conclusion
The Set Difference operator is a powerful tool when you want to know the uncommon element present in the table. Having a good understanding and the application of this operator can help in filtering the records and values from the given Relations.