Relational Algebra (Union Operation)
Summary
TLDRThis 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
๐ 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.
๐ 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.
๐ 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.
๐ซ 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.
Mindmap
Keywords
๐กRelational Algebra
๐กUnion Operation
๐กBinary Operator
๐กDuplicate Values
๐กProjection Operation
๐กSet Theory
๐กRelation
๐กAttributes
๐กDomain
๐กSchema
๐กExample Relations
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]
5.0 / 5 (0 votes)