Relational Algebra (Union Operation)

Summary

This video script focuses on the Union operation in relational algebra, one of the six fundamental operations. It explains that Union is a binary operation, combining two relations without duplicate tuples. The script uses examples to demonstrate how Union works, emphasizing the need for compatible relations with the same number of attributes and domains. It also introduces the University database schema and poses homework questions to practice the concept.

Takeaways

• 😀 Relational algebra's Union operation combines tuples from two relations into a single set, eliminating duplicates.
• 🔑 The Union operation is a binary operator, requiring two inputs (relations) to perform.
• 📚 Union in relational algebra mirrors the set theory's union operation, focusing on combining sets of tuples.
• 🍎 Example given: Union of relations containing fruits results in a unique set of fruits, with duplicates like 'apple' appearing only once.
• 📐 To perform a Union, both relations must have the same number of attributes (arity) and corresponding domains must match.
• 📝 The script introduces the concept with a simple example using single-attribute relations and progresses to more complex scenarios.
• 🏫 The script uses a university database schema to demonstrate how to find customer names associated with a bank as either account holders or loan borrowers.
• 🌐 It explains the importance of projection before Union when dealing with relations that have multiple attributes.
• 📈 The script also covers how to find all courses taught in specific semesters using the Union operation.
• 🎓 Homework questions are provided to practice finding course IDs and instructor IDs for specific semesters using the Union operation.

Q & A

What is the Union operation in relational algebra?

The Union operation in relational algebra is an operation that combines two relations (tables) into a single relation containing all distinct rows from the input relations. It is similar to the union operation in set theory.

Is the Union operation a unary or binary operator?

The Union operation is a binary operator because it requires at least two tables to perform the operation.

What is the purpose of eliminating duplicate values in the Union operation?

Duplicate values are eliminated in the Union operation to ensure that each row (tuple) appears only once in the resultant relation, maintaining the set nature of relations in DBMS.

What symbol is used to denote the Union operation?

The symbol used to denote the Union operation is the union symbol, which is represented by the word 'Union' or the symbol '∪'.

What are the conditions that must be met before performing a Union operation on two relations?

To perform a Union operation, the two relations must have the same number of attributes, and the corresponding attributes must be of the same domain.

Can you perform a Union operation directly on two different relations with different numbers of attributes?

No, you cannot directly perform a Union operation on two relations with different numbers of attributes. The relations must be compatible in terms of the number of attributes and the domain of each attribute.

How does the Union operation handle duplicate rows when combining two relations?

The Union operation eliminates duplicate rows when combining two relations, ensuring that each row appears only once in the resulting relation.

What is the difference between the Union operation and the Project operation in relational algebra?

The Union operation combines two relations into one by including all distinct rows from both, while the Project operation selects specific columns from a relation and creates a new relation with only those columns.

Can you provide an example of how the Union operation works with two relations?

Sure. If you have two relations, one with fruits like 'apple', 'mango', and 'grapes', and another with 'apple', 'strawberry', when you perform a Union operation, the result will be a relation with 'apple', 'mango', 'grapes', and 'strawberry', with 'apple' only appearing once.

What is the significance of the projection operation in the context of the Union operation?

The projection operation is often used in conjunction with the Union operation to ensure that only the relevant attributes are considered for the Union. This helps in aligning the relations to have the same schema before performing the Union.

How does the Union operation relate to the concept of sets in mathematics?

The Union operation in relational algebra is closely related to the concept of sets in mathematics. It follows the set theory principle of combining two sets into a single set that contains all the distinct elements from both sets.

Outlines

00:00

📚 Introduction to Union Operation in Relational Algebra

The paragraph introduces the concept of the Union operation in relational algebra, which is the third fundamental operation after select and project. It explains that a relation in a database management system (DBMS) is akin to a set in mathematics, and the Union operation combines two relations into a single set of tuples without duplicates. The Union operator is a binary operator, requiring two inputs, and the output is a set containing all tuples from both relations. An example is provided to illustrate how duplicates are eliminated, such as combining a list of fruits from two sets into a single list without repeating any fruit. The paragraph also discusses the syntax and conditions required for performing a Union operation, emphasizing that the relations must have the same number of attributes and compatible domains for the attributes.

05:01

🔗 Applying Union Operation with Simple Examples

This paragraph demonstrates the Union operation with a simple example using two relations, R and Yes, which both have a single attribute. It explains the conditions required for the Union operation, such as having the same number of attributes and compatible domains for each attribute. The example shows how the Union of R and Yes results in a new relation that includes all unique values from both relations. The paragraph also discusses a practical scenario where the Union operation is used to list customer names associated with a bank as either account holders or loan borrowers, emphasizing the use of projection before applying the Union to ensure the relations have compatible structures.

10:02

📈 Projection and Union in Relational Algebra

The paragraph continues the discussion on the Union operation by explaining how it is applied in conjunction with projection. It uses an example of finding all courses taught in specific semesters by first selecting the relevant courses from a 'section' relation and then projecting the course IDs. The example illustrates how to retrieve and combine data from a database using relational algebra operations. The paragraph also introduces the concept of making assumptions about table names when they are not explicitly mentioned and proceeds with expressions to understand the operation clearly.

15:05

🏫 Union Operation in the Context of University Database

This paragraph applies the Union operation in the context of a university database schema. It presents a scenario where the goal is to find all courses taught in specific semesters by using the 'section' relation. The paragraph explains how to perform a select operation to filter the courses by semester and year, followed by a projection to retrieve only the course IDs. The Union operation is then used to combine the results from different semesters into a single set of course IDs. The paragraph concludes with a discussion of two homework questions that involve using the Union operation to solve practical database queries, emphasizing the importance of understanding relational algebra for competitive examinations.

Keywords

💡Relational Algebra

Relational algebra is a theory that deals with mathematical operations performed on relations (which in this context are similar to tables in a database). It is a fundamental concept in database management systems (DBMS). The video script focuses on explaining various operations within relational algebra, specifically the union operation. This concept is foundational to understanding how data can be manipulated and retrieved from a database.

💡Union Operation

The union operation in relational algebra combines two relations into a single one, containing all the tuples from both relations without duplicates. It is likened to the union operation in set theory. In the script, the union operation is the main focus of the lecture, with examples provided to illustrate how it works, such as combining a list of fruits from two different sets.

💡Binary Operator

A binary operator is an operation that takes two operands to produce a result. In the context of the video, the union operation is described as a binary operator because it requires two relations (tables) as input to perform the union. The script explains that the union operator is not unary (single input) but binary, necessitating at least two tables.

💡Duplicate Values

Duplicate values refer to the occurrence of the same data point more than once. The script clarifies that the union operation in relational algebra, much like the project operation, eliminates duplicate rows. This ensures that the resultant relation only contains unique tuples, even if the original relations had overlapping data.

💡Projection Operation

The projection operation is one of the fundamental operations in relational algebra that creates a new relation with a subset of attributes from an existing relation. The script mentions the projection operation in the context of preparing data for the union operation, where only certain columns (attributes) from relations are selected before performing the union.

💡Set Theory

Set theory is a branch of mathematical logic that studies sets, which are collections of distinct objects. The script draws a parallel between the union operation in relational algebra and the union operation in set theory, emphasizing the concept of combining sets without duplicate elements, which is a key principle in both areas.

💡Relation

In the context of the video, a relation is analogous to a table in a relational database and is treated as a set in mathematical terms. The script explains that any relation in a DBMS corresponds closely with the mathematical concept of a set, which is why operations like union can be applied.

💡Attributes

Attributes in a relation (table) are the columns that define the properties of the tuples (rows). The script discusses the importance of having the same number of attributes in two relations before they can be unioned. It also mentions that the domain (type of data) of corresponding attributes must match.

💡Domain

The domain of an attribute refers to the set of possible values that the attribute can take. The script explains that for the union operation to be valid, the domain of the ith attribute in one relation must be the same as that in the other relation. This ensures compatibility between the attributes of the two relations being unioned.

💡Schema

A schema is a blueprint that defines the structure of a database, including the relations, attributes, and types of data each attribute can contain. The script suggests pausing to consider the schema when dealing with database queries, which is crucial for understanding how data is organized and how operations like union can be applied.

💡Example Relations

Example relations are hypothetical sets of data used to illustrate how database operations work. The script uses example relations such as 'depositor' and 'borrower' to demonstrate the union operation, helping viewers understand how to apply these operations to real-world data scenarios.

Highlights

Introduction to the Union operation in relational algebra

Union operation is similar to the union operation in set theory

Union operation requires at least two tables

Union operator is a binary operator

Output of Union operation contains members from both relations without duplicates

Example of Union operation with lists of fruits

Symbol used to denote the Union operation

Union cannot be directly applied on two different relations

Union operation requires the relations to have the same number of attributes

Projection operation is often used before Union

Simple example of Union operation with alphabets

Formal example of Union operation with customer names

Union operation is used to find customer names associated with the bank as account holders or loan borrowers

Example of Union operation with courses taught in different semesters

Two important conditions for performing Union operation

Condition 1: Relations must have the same number of columns

Condition 2: Domain of corresponding attributes must be the same

Homework questions related to Union operation

Next presentation will focus on the set difference operation

Transcripts

foreign

we will see the next relational algebra

fundamental operation the relational

algebra Union operation we know

basically there are six fundamental

relational algebra operations and we

have already seen about the first two

operations the select and project

operations in the previous lectures now

we are going to focus on the third

fundamental operation which is the union

operation let's see the theoretical

aspects of this Union operation now and

we have already seen this point in the

last presentation that any relation is a

set a relation in dbms is having a close

correspondence or close association with

the mathematical concept called the set

and when we talk about the union

operation in relational algebra this

Union operation is similar to the union

operation in the set theory and that's

why I told you any relation is a set and

this Union operation what we are

focusing on in this presentation is

similar to the union operation a insert

Theory and what type of operator is this

Union operator is it a unary or a binary

operator the name itself says that it is

going to do the union operation it means

it requires at least two tables to

perform the operation isn't it so it is

a binary operator where it requires two

inputs to be precise it requires two

tables or two relations to perform the

operation say we have two relations A

and B and what's the output of a union B

that's what we are going to see now it

is a set of all objects that are a

member of a or b or both if we take a as

a relation and B as another relation and

we perform a union B then the output is

going to be the set of all objects or

all tuples that are a member of a or b

or both no worries when we see an

example at the time it will be easy to

understand but for now just understand

when we are are performing a union B the

output will be containing the members

from a as well as the members from B and

there are situations where the values

may be appearing in both A and B will we

be having duplicate values no we will

not be having duplicate values so like

the project operation here also the

duplicate rows are eliminated let's say

a is containing some list of fruits say

for example apple mango and grapes and B

contains apple and strawberry and when

we perform EA Union B we will be getting

Apple only once why even though the

fruit apple is in both the sets A and B

but still we will be getting only one

apple in the resultant and that's why I

told you the values in the output will

be the member of a or b or even both but

if there are duplicate values these

duplicate values are eliminated in The

resultant table and what is the symbol

which is used to denote the union

operation this is the symbol the union

symbol which is used to denote the union

operation let's see the syntax now when

we talk about Union we cannot directly

apply Union on two different relations

let's say a is a relation and b is

another relation can we directly perform

a union B we need to ensure certain

conditions are met because we cannot

directly perform a union B the number of

attributes in EA may be different from

the number of attributes in B say for

example if a contains five attributes

and B contains four attributes in this

case a union B cannot be performed then

how it will be helpful just see the

syntax it will be easy we are not

directly performing relation One Union

relation to rather we are performing the

projection of a single column or

multiple columns from relation one that

is performed with the union with another

projection it may be a single column or

a group of columns from another relation

which is relation 2 in this example so

what we are actually doing here is we

are going to project a column from

relation 1 Union and we are going to

project another column from relation to

and the output of this will be single

column and the output of this will be

single column and we are going to

perform form one column Union another

column remember the output of this will

be a relation only and the output of

this is also going to be a relation only

so we are going to perform Union between

relations but not directly when we see

an example we will be able to understand

things clearly let's first understand

the operation of Union with simple

example

let's say we have a relation R which

contain only one attribute and this

attribute is alphabets a b c e and f

let's take another relation which is yes

and this relation is having a column or

an attribute which is characters and the

characters are a b dollar e f 1 and hash

symbol now what we are going to perform

is R Union Yes can we do our Union Yes

yes there are certain conditions to be

made but at the basic level the first

condition will be satisfied the number

of attributes in R is same as that of

the number of attributes in yes so here

also we have only one attribute in the

relation here also we have only one

attribute in the relation so our Union

Yes can be performed but still we have

another condition to be satisfied before

applying Union which we will see at the

end of this presentation for now just

understand I am going to apply R Union

yes let's see the output of our Union

Yes and the output is going to be our

Union is let's see what's the data

present in this our Union s relation say

a b c e f so all these value will appear

in the output relation a b c e and f now

the members of R is present all the

members of R is present in the output

relation now we need to ensure whether

all the members of Yes are also present

in the output relation talking about the

first member a which is already already

there in the output relation as per the

concept of uni and the duplicate values

are eliminated so this a will not be

appearing again in this output relation

likewise B Because B is also already

there in the output relation and talking

about dollar dollar is not there in R

but it is there in yes so dollar will be

there in the output relation coming to e

and f e and f are already there in R

which is already sent to the output R

Union Yes I mean which is already

available in the output relation so E

and F will not be appearing again

because it's a duplicate value now so

this will be eliminated then coming to 1

if you see one is there only in yes and

not in R so one will also be there in

the output and coming to the hash symbol

this hash symbol is not available in the

relation R but available in s so this

will also be available in the output

table so this is the working of R Union

s in simple terms if we consider two

relations r is the output will be

containing the members of r or the

members of Yes or both so this example

is just for understanding Purpose with

this knowledge let's see the formal

example

the question is list all customer names

associated with the bank either as an

account holder or a loan borrower just

pause this video for a while and think

what are the two relations that we are

talking about in this question

I hope you are done the example

relations that we are talking here is

one is related to the account holder and

the other one is related to the loan so

what we are assuming here is there are

two relations one is the deposited

relation so I am saying if somebody is

having an account in the bank it means

that person is having a deposited

relationship with the bank so here is

the depositor relation and if somebody

has awailed a loan from the bank so

obviously he is a borrower right so he

is the loan borrower so I am considering

these two relations are existing number

one the depositor and number two the

borrower and what we are required to

find we are required to find the list of

all customer names associated with the

bank either as an account holder or as a

loan borrower let's say there is a

customer X if that name X it should

appear either in the account relation or

in the depository relation it may even

appear in both the relations but our

output should contain the value X

let's solve this problem now so what we

are doing is we are considering this

depositor relation and we are retrieving

only customer name from the depositor

relation which is this part

so all the depositor's name will be

appearing here just see what operator I

have used the project operator so this

project retrieves only the customer

names from this depositor relation and

the output of this is going to be a

relation a temporary relation which

contains only one attribute which is

customer name and coming to this side

here I am focusing on the borrower

relation and in the borrower relation I

am retrieving only the customer names

from the borrower table or borrower

relation and here what operator I am

using the same project operator please

note the depositor relation may contain

X number of attributes and the borrow

relation may contain y number of

attributes Y and X may not be the same

but here we are not doing depositor

Union borrower we are doing customer

name Union customer name only where this

customer name is from depositor relation

and this customer name is from borrower

relation and the output of this

expression is going to be a table with

only one attribute and the o output of

this expression is going to be only one

relation with one attribute and we can

perform Union

so here is the output for this query so

generally when questions are asked just

make an assumption what could be the

table name and just start proceeding

with the expression if table names are

not explicitly mentioned we can make

assumptions on the table name and we can

proceed with the Expressions to

understand things clearly let me

elaborate the solution with an example

table let's take this is the depositor

table which contains two attributes

customer name and account number which

is this relation let's take this

relation the borrower relation which

contains two attributes customer name

and loan number are we going to do

depositor Union borrower depositor Union

borrower may be proceeded with because

it also has two attributes and this also

has two attributes but still we have

other conditions to be checked before

proceeding with depositor Union borrower

let's not touch upon that part now but

for now just understand we are not going

to proceed with the Positive Union

borrower rather we are going to proceed

with projection of customer name from

depositor Union projecting customer name

from borrower it means I am going to do

Union only for these two columns this

one Union this one let me give a naming

convention for easy readability let this

be R and let this be yes so what is r

it's the customer name from depositor

relation so what I am doing is I am just

renaming this output as R which is this

so This R contains only the customer

names from depositor relation and this s

contains the customer name only from

borrower relation and this is R this is

s so what I'm gonna perform is R Union

Yes which is the customer name so Tom

Amy Rose John Tom Amy Rose John it's

appearing here then coming to this place

John John is already there in the table

so this will be ignored this will be

eliminated because this is a duplicate

value coming to Smith Smith is not there

already in the output table so Smith

will be added coming to rows Rose is

already there in the output table so

rows will also be eliminated and coming

to Jack Jack is not already there in the

output table in other words jack is not

there in the first table and hence Jack

will be added to the output and this is

the output of R Union Yes

before seeing the other example I will

show you the schema

just pause this video for a while and

have a look at the schema

I hope you all done now let's proceed

with the next example the question is

find the set of all courses taught in

the fall 2009 semester the spring 2010

semester or both

what we are required to find we are

required to find the set of all courses

and these courses may be taught either

in Fall 2009 semester or in the spring

2010 semester or both now from which

table we need to perform all this

operation if you want I'll just show you

the table just think about it

so from which relation we are going to

retrieve the data

section is the relation where this

contains the course as well as the

semester as well as the year which has

been taught right so I am going to take

the list of all courses that are taught

in this particular semester and in this

particular Year from this relation

section

so the answer for this part is going to

be the project operation and before that

what we need to do from the section

relation we are going to retrieve all

the courses that are taught in the

semester fall and in the year 2009 so

what I am doing is first I am retrieving

all the tuples where the semester is

fall and giving and condition because in

this form and 2009 year so I am

selecting all the tuples where the

semester is fall and the year is 2009

from the section relation so this will

give all the tuples and what I need only

the set of all courses right so course

ID I need the course ID so I'm just

projecting only course ID from this

output temporary relation remember this

temporary relation will contain the set

of all tuples with all attributes from

those attributes I am retrieving only

course ID

so this is for this semester fall 2009

we need to do the same for spring 2010

so the query goes like this to find the

set of all courses taught in the spring

2010 semester we write select semester

is equal to spring because we are

focusing on this part and here is equal

to 2010 and year is equal to 2010 from

the section relation the output of this

inner query is going to be a temporary

relation with the tuples with all

attributes and from this I am going to

project only one attribute which is

course side now this query gives the

output of all the course ID that is dot

in Fall 2009 this query gives the output

of all course ID that are taught in

Spring 2010 and what's the question we

need to list all the courses that are

taught in Fall 2009 spring 2010 or both

in that case to answer this query we

need the union of these two sets so the

output of this is this part which is

this part Union this part which is this

so the output is going to be the set of

all courses taught in Fall 2009 semester

the spring 2010 semester or both before

we sign out let's see the two important

conditions to be valid for performing

the union between two tables

the two important conditions for what

for our Union Yes to be valid

I mean r is a relation here and S is a

relation here for our Union Yes to be

valid then these two conditions should

be met first R and S must be of same r80

what do we mean by this it means the

number of columns in R and the number of

columns in yes must be the same

in simple terms the number of columns in

both the relations R and S should be the

same for example if the relation R

contains 10 attribute and the relation s

contains five attributes R Union Yes is

not possible because the first condition

itself is the number of attributes on R

and S must be the same

let's say if the number of attributes

are matching

then can we directly perform R Union Yes

No we cannot directly perform R Union

yes so the next condition is for all I

the domain of the ith attribute of R

should be the same as that of the domain

of ith attribute of yes

what do we mean by this let's say there

are two relations R and S and R contains

three attributes and S also contains

three attributes so the first condition

is satisfied both R and S must be of

same rity yes R and S has three columns

each and coming to the second condition

the domain of ith attribute of R should

be the same as that of the domain of ith

attribute of yes

it means the domain of the first

attribute of the relation R should be

the same as that of the domain of the

first attribute of yes the domain of the

second attribute of the relation R

should be the same as that of the domain

of the second attribute of the relation

yes so likewise all the attributes

according to their position should be

belonging to the same domain when these

two conditions are made R Union s is

possible but in our example we have not

simply performed Union between two

relations directly rather we have

projected the output the temporary

output and then we perform the union

among these two outputs

and that's it guys before we sign out

let's see the homework questions here we

have two homework questions let's see

what are they before seeing the

questions I wanted to introduce the

schema that we are familiar with this is

the University database schema where we

have multiple relations instructor

course Department section teachers

student advisor takes classroom and time

slot question number one which is list

all the course IDs which are taken in

Spring 2020 or fall 2021 semester so

this is a pretty straightforward

question because we are going to find

all the course IDs which are taken in

spring semester year 2020 or fall

semester year 2021

we can easily answer this question

number one because it's a simple

projection along with Union only thing

is we are required to find the right

table or the right relation and the

right attributes for the condition

spring 2020 or fall 2021

and coming to question number two list

all the instructors ID who taught

courses in Spring 2020 or fall 2021 and

question number two is a bit tricky

question because we are required to find

all the instructor IDs but the tricky

part is the instructor's ID are

available in multiple relations can you

see here this instructor relation also

has ID and coming to teachers relation

this also has ID and coming to advisor

relation this also has ID I am not

bothering about this because this ID

represents student so what I mean to say

here is we have instructors ID in

multiple relations and the challenge is

to find the appropriate table that

contains the information about the

courses that the instructor has thought

in Spring 2020 semester or fall 2021

semester I request you to meticulously

look into the questions and post your

answers in the comment section and these

kind of questions will definitely help

us to crack the competitive examination

questions

and that's it guys in today's

presentation we have focused on the

union operation in relational algebra in

the next presentation we will focus on

the set difference operation which is

also one of the important operations in

relational algebra and that's it guys I

hope you guys enjoyed this presentation

and thank you for watching

[Music]

[Applause]

[Music]