With respect to relational algebra, which of the following operations are included from mathematical set theory
(a) join
(b) Intersection
(c) Cartesian product
(d) Project
Concept:
Relational algebra: It is a procedure-oriented language. In this, we use relation as values instead of numbers. It works on relational model. Main work is to retrieve data, perform operations such as insert, delete, update etc.
Explanation:
The following functional dependencies hold for relations R(A, B, C) and S(B, D, E)
B → A,
A → C
The relation R contains 200tuples and the relation S contains 100tuples. What is the maximum number of tuples possible in the natural join \(R \bowtie S\) ?
Explanation:
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 values must be unique in R1. To get the maximum number of tuples in output, there can be two possibilities.
1) All 100 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 100 tuples in output.
Example:
Consider an example, suppose there is two tables R1(A, B, C) consisting of 5 tuples and R2( B, D, E) consisting of 4 tuples only.
B |
A |
C |
1 |
E1 |
P1 |
2 |
E2 |
P2 |
3 |
E3 |
P3 |
4 |
E4 |
P4 |
5 |
E5 |
P5 |
B |
D |
E |
1 |
M1 |
K1 |
1 |
M2 |
K2 |
1 |
M3 |
K3 |
1 |
M4 |
K4 |
Natural join of R1 and R2 (R1 * R2) gives: 4
2) All 100 values of B in R2 are different and these values are present in R1 also. In this case, also we get 100 tuples.
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.
B |
A |
C |
1 |
E1 |
P1 |
2 |
E2 |
P2 |
3 |
E3 |
P3 |
4 |
E4 |
P4 |
5 |
E5 |
P5 |
B |
D |
E |
1 |
M1 |
K1 |
2 |
M2 |
K2 |
3 |
M3 |
K3 |
4 |
M4 |
K4 |
Natural join of R1 and R2 (R1 * R2) gives: 4
Consider the following SQL query:
SELECT title, name
FROM Employee
ORDER BY title, name DESC:
In output of above query the different titles are listed in ___________ order the names of employees having same title are listed in ___________ order.
The ORDER BY command is used to sort the result set in ascending or descending order.
The ORDER BY command sorts the result set in ascending order by default. To sort the records in descending order, use the DESC keyword
Syntax
SELECT * FROM table_name ORDER BY column_name ASC | DESC
Let the two-relation schema be R (A, B,) and S (A, B). which of the following is equivalent to R ∩ S
I. R – (R – S)
II. S – (S – R)
III. R U S – ((S – R) – (R – S))
IV. ((R U S – (S – R)) – (R – S)
Let us take example to prove:
R:
A |
B |
1 |
3 |
2 |
4 |
S:
A |
B |
2 |
4 |
8 |
5 |
A U B
A |
B |
1 |
3 |
2 |
4 |
8 |
5 |
R – S
A |
B |
1 |
3 |
S – R
A |
B |
8 |
5 |
Statement -4 : R ∩ S ≡ R – (R – S) ≡ S – (S – R) ≡ ((R U S – (S – R)) – (R – S)
A |
B |
2 |
4 |
Statement 3:- R U S – ((S – R) – (R – S))
A |
B |
1 |
3 |
2 |
4 |
Hence R U S – ((S – R) – (R – S)) is not equivalent to R ∩ S
Concept:
A natural join (join) is based on common attributes or common columns to join two tables or relations.Explanation:
Consider an example, suppose there is two tables Employee(Eid, Ename) consists of 5 tuples and department(Eid, Did) consists of 3 tuples only.
Eid |
Ename |
1 |
E1 |
1 |
E2 |
1 |
E3 |
1 |
E4 |
1 |
E5 |
Eid |
Did |
1 |
D1 |
1 |
D2 |
1 |
D3 |
Natural join of employee and department(Employee * Department) gives:
m×n = 5 × 3 = 15Therefore 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.
Which is/are the correct statement given about a relation?
I. In relational algebra, selection may return duplicate tuples.
II. In relational algebra, projection will never return duplicate column.In relational algebra, selection and projection will never return duplicate entries.
Symbol |
Name |
Example |
σ |
selection |
σ_{A = 500}(R) → Return rows whose A attribute is equal to 500 and no duplicates are allowed |
∏ |
projection |
∏_{B}(R) → Output the column B and no duplicate are allowed |
Consider the following relation schema of students.
STUDENT (Rollno, Name, DOB, Marks, Gender) Which of the given query is equivalent to this query in English? “Find the tuples of student having marks above 80”.
Option 1:σMARKS>80 (STUDENT)
True, Find the number of students having marks above 80. A selection operator is used to select tuples from a relation based on some condition.
Option 2: ΠMarks<80 (STUDENT)
The projection operator is used to project particular columns from a relation but here condition is used so wrong statement.
Option 3: σMARKS<80 (STUDENT)
Find the number of students having marks less than 80. A selection operator is used to select tuples from a relation based on some condition.
Option 4: ΠMarks>80 (STUDENT)
The projection operator is used to project particular columns from a relation but here condition is used so wrong statement.
Hence the correct answer is σMARKS>80 (STUDENT).
What is the number of rows returned for the given Relation R if query passed is “σ_{salary ≥ 5000 }(R)”
ID |
salary |
1 |
3000 |
2 |
5000 |
3 |
4999 |
2 |
5000 |
5 |
10000 |
Symbol |
Name |
Example |
σ |
selection |
σ_{A ≥ 5000}(R) → Return rows whose A attribute is equal to 5000 and no duplicates are allowed |
Output:
ID |
Salary |
2 |
5000 |
5 |
10000 |
Output contains 2 rows
Consider the following relation schema R and S along with their tuple sets.
R(A, B) = {<a1, b1 >, <a2, b1>, <a3, b1>, <a4, b1>, <a1, b2>, <a3, b2>, <a2, b3>, <a3, b3>, <a4, b3>, <a1, b4>, <a2, b4>, <a3, b4>}
S(A) = {a1, a2, a3}
What is the value of T ← R/S, where “/” represents the Relational Algebra “division” operation?The relation returned by the division operator between two relations A and B are those tuples from relation A which are associated to every B’s tuple.
R:
A |
B |
a1 |
b1 |
a2 |
b1 |
a3 |
b1 |
a4 |
b1 |
a1 |
b2 |
a3 |
b2 |
a2 |
b3 |
a3 |
b3 |
a4 |
b3 |
a1 |
b4 |
a2 |
b4 |
a3 |
b4 |
S:
A |
a1 |
a2 |
a3 |
Now check which values of A in S is associated with B in R.
a1 is associated with [b1, b2, b4]
a2 is associated with [b1, b3, b4]
a3 is associated with [b1, b2, b3, b4]
Now, [b1, b4] are the common elements that are associated with values of A in S.
T = R/S
T:
B |
b1 |
b4 |
π customer_name (σ balance > 1000 (Deposit)) will be the correct answer
Customer_id | Customer_name | Balance |
1 | Suvarna | 2000 |
2 | Richa | 1000 |
3 | Deepu | 900 |
4 | Ankit | 3000 |
Important Points
Project Operation (π)
It projects column(s) that satisfy a given predicate.
Notation − πA1, A2, An (r)
Where A1, A2 , An are attribute names of relation r.
Duplicate rows are automatically eliminated, as the relation is a set.
Select Operation (σ)It selects tuples that satisfy the given predicate from a relation
191 Points
54 Points
53 Points
52 Points
49 Points