Test: Joins in Relational Algebra - Computer Science Engineering (CSE) MCQ

For given relation R1(A, B, C) and R2(B, D, E), functional dependencies are given only for relation R1, not for R2 And the candidate key for R1 is B, so all 35 value must be unique in R1. To get the maximum number of tuples in output, there can be two possibilities.

• All 34 values of B in R2 are the same and there is an entry in R1 that matches with this value. In this case, we get 34 tuples in output.

Example:

Consider an example, suppose there is two tables R1(A, B, C) consists of 5 tuples and R2( B, D, E) consists of 4 tuples only.

Natural join of R1 and R2 (R1 * R2) gives: 4

Natural join is used to join two relations having any number of attributes. It is denoted by a symbol ( ⨝). It is the combination of projection and filtered cartesian product. It optimizes the query as the cartesian product gives unnecessary results and set union and set intersection operations are application only on those relations that have an equal number of attributes with the same data types. 

Example: Given two relation employee and departments. 

Employee:

Department:

Query: Find the name of all employees from Employee with their respective Department names using natural join :

The result will be: 

Consider an example, suppose there is two tables Employee(Eid, Ename) consists of 5 tuples and department(Eid, Did) consists of 3 tuples only.

Natural join of employee and department(Employee * Department) gives:
m×n = 5 × 3 = 15
Therefore maximum size is m × n

Minimum size: 
When Both the relations have a common attribute but no tuple in both relations match.
∴ minimum size = 0.

R relation:

S relation:

Natural join of Relation R and S, assuming C as A in Relation S

Full outer join of Relation R and S

So we can say that outer join operation preserves those tuples that would be lost in natural join so option 2 is the correct answer.

will include record with b> = 5 but other three queries will not.

Given: two relations R and S having tuples 3 and 2 respectively. 
So, their natural join will contain maximum of 3 * 2= 6 tuples.

Example:
Consider table R (a, b) as ;

Relation S(a, c) as :

Here, the natural join of R and S will be :

So, it contains total of 6 tuples.

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
Here are the different types of the JOINs in SQL:

1. (INNER) JOIN: Returns records that have matching values in both tables
2. LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
3. RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
4. FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
5. We can see that left join keeps all the elements from the first relation irrespective of their presence in second relation.

Diagram to visualize joins:

Consider the empAge(empNo, age) Table

We run the following Query on above Table 

We get Output Table as 

Hence the Output is "Employees whose age greater than at least one employee" or "Employees whose age is no minimum".

Full natural outer join of R₁ and R₂ is :

Hence option 3 is correct:

Self-Join:

A self-join is a join in which the table is joined with itself to get the appropriate results. In this case, it is necessary to ensure that the join statement defines an ALIAS name for both copies of the tables to avoid column ambiguity. Example:

Query:
SELECT A. Coursename AS Course, B. Coursename AS Prerequisite_Course
FROM Course A, Course B
WHERE, A.Precourse = B.CourseID;

OUTPUT