# How to solve Relational Algebra problems for GATE

Type-1: Given a relational algebra expression, find the result.Â
Suppose you have a relation Order(Prod_Id, Agent_Id, Order_Month) and you have to find out what will the following algebra expression return.Â

Â

`?Order1.Prod_Id (?(Order1,Order) Order1.Prod_Id=Order2.Prod_Id                                           and Order1.Agent_Id?Order2.Agent_Id                                           and Order1.Order_Month=Order2.Order_Month ?(Order2,Order)) `

Process the expression starting from innermost brackets.Â

In this example, we have renamed order to Order1 and Order2 (Both represent the same relation order). Then we have applied the conditional join between Order1 and Order2. It will return those rows where Product_Id and Order_Month of Order1 and Order2 are the same but Agent_Id of Order1 and Order2 is different. It implies the rows where the same product is ordered by two different agents in the same month. Then we are projecting the Prod_Id.Â

So the final output will return the Prod_Id of products that are ordered by different agents in the same month. We can do this by taking sample data. Let Order relation consists of the following data.Â

Table – OrderÂ

Prod_IdAgent_IdOrder_Month
P001A001JAN
P002A002FEB
P002A001FEB
P001A002FEB

When we apply the following expression, the rows which are highlighted in blue will be selected.Â

`(?(Order1,Order)Order1.Prod_Id=Order2.Prod_Id                    and Order1.Agent_Id?Order2.Agent_Id                    and Order1.Order_Month=Order2.Order_Month ?(Order2,Order))`

Â

Order1.Prod_IdOrder1.Agent_IdOrder1.Order_MonthOrder2.Prod_IdOrder2.Agent_IdOrder2.Order_Month
P001A001JANP001A001JAN
P002A002FEBP001A001JAN
P002A001FEBP001A001JAN
P001A002FEBP001A001JAN
P001A001JANP002A002FEB
P002A002FEBP002A002FEB
P002A001FEBP002A002FEB
P001A002FEBP002A002FEB
P001A001JANP002A001FEB
P002A002FEBP002A001FEB
P002A001FEBP002A001FEB
P001A002FEBP002A001FEB
P001A001JANP001A002FEB
P002A002FEBP001A002FEB
P002A001FEBP001A002FEB
P001A002FEBP001A002FEB

After projecting Order1.Prod_Id, the output will be P002 which is Prod_Id of products that are ordered by at least two different agents in the same month.Â

Note – If we want to find Prod_Id which are ordered by at least three different agents in same month, it can be done as:Â
Â

`?Order1.Prod_Id (?Order1.Prod_Id=Order2.Prod_Id                                  and Order1.Prod_Id=Order3.Prod_Id                                  and Â Order1.Agent_Id?Order2.Agent_Id                                  and Order1.Agent_Id?Order3.Agent_Id                                  and Order2.Agent_Id?Order3.Agent_Id                                  and Order1.Order_Month=Order2.Order_Month                                  and Order1.Order_Month=Order3.Order_Month(?(Order1,Order)X ?(Order2,Order)X ?(Order3,Order))) `

Type-2: Given two relations, what will be the maximum and minimum number of tuples after natural join?Â

Consider the following relation R(A,B,C) and S(B,D,E) with underlined primary key. The relation R contains 200 tuples and the relation S contains 100 tuples. What is the maximum number of tuples possible in theÂ naturalÂ Join R and S?Â

To solve this type of question, first, we will see on which attribute natural join will take place. Natural join selects those rows which have equal values for common attribute. In this case, the expression would be like:Â

`?R.B=S.B (RX S) `

In relation R, attribute B is the primary key. So Relation R will have 200 distinct values of B. On the other hand, Relation S has BD as the primary key. So attribute B can have 100 distinct values or 1 value for all rows.Â

Case-1: S.B has 100 distinct values and each of these values match to R.BÂ

Â

In this case, every value of B in S will match to a value of B in R. So natural join will have 100 tuples.Â

Case-2: S.B has 1 values and this values match to R.BÂ

Â

In this case, every value of B in S will match to a value of B in R. So natural join will have 100 tuples.Â

Case-3: S.B has 100 distinct values and none of these values matches to R.BÂ

Â

In this case, no value of B in S will match to a value of B in R. So natural join will have 0 tuple.Â

Case-4: S.B has 1 value and it does not match with R.BÂ

Â

In this case, no value of B in S will match to a value of B in R. So natural join will have 0 tuples.Â

So the maximum number of tuples will be 100 and min will be 0.Â

Note – If it is explicitly mentioned that S.B is a foreign key to R.B, then Case-3 and Case-4 discussed above are not possible because the value of S.B will be from the values of R.B. So, the minimum and maximum number of tuples in natural join will be 100.Â
Consider the following three relations in a relational database.

Example:-

Q1: Employee(eId,Â Name), Brand(bId, bName), Own(eId, bId)

Which of the following relational algebra expressions return the set of eIds who own all the brands?Â (GATE 2022)

Option:

1. ?eldÂ (?eld,bld? (Own)/?bld? (Brand))

2. ?eld? (Own) –Â ?eld? ((?eld? (Own)Â Ã—Â ?bld? (Brand)) –Â ?eld,bld? (Own))

3. ?eld? ((?eld,bldÂ (Own)/?bld? (Own))

4. ?eld? ((?eld? (Own)Â Ã—Â ?bld? (Own)) /Â ?bld? (Brand))

Ans:

OptionÂ 1Â and optionÂ 2Â are correct.

Concept:

• The “Division operator” in relational algebra return all the entities that are associated with entities of different relation.

Â

The result of the query in option 1:
It will display all the eIdÂ of relation “Own” which are associated with all the bId of relation “Brand”.

The result of the query in option 2:Â

At first,Â the cartesian product is performed betweenÂ Own(eId)Â andÂ Brand(bId). Which results in the association of allÂ eIdÂ with allÂ bId.Â

Now, subtract theÂ OwnÂ relation entities from the newly generated relationÂ which contains theÂ cartesian product. This will result inÂ eIdsÂ that areÂ not associated withÂ bIds.

Subtracting these resultedÂ eIdsÂ from theÂ OwnÂ relation to retrieve theÂ eIdsÂ that are associated withÂ bIdÂ in theÂ BrandÂ relation, which is similar to the option 1 result.

?eldÂ (?eld,bld? (Own)/?eld? (Brand)) =Â ?eld? (Own) –Â ?eld? ((?eld? (Own)Â Ã—Â ?eld? (Brand)) –Â ?eld,bld? (Own))

Reference – Fundamentals of Database Systems bu Navathe

Article contributed by Sonal Tuteja.
Â

Previous
Next