Theta join operations are commutative.
\(X_1 ⋈_ \theta X_2\)= \(X_2 ⋈_ \theta X_1\)
Theta joins are associative in the following manner:
\((X_1 ⋈_ {\theta _1} X_2) ⋈_ {\theta _2 \wedge \theta _3} X_3\) = \(X_1 ⋈_ {\theta _1 \wedge \theta _3} (X_2 ⋈_ {\theta _2} X_3)\)
The following relation records the age of 500 employees of a company, where empNo (Indicating the employee number) is the key:
empAge(empNo, age)
Consider the following relational algebra expression:
π_{empNo}(empAge ⋈ _{(age>age1)} ρ_{empNo 1, age1}(empAge))
What does the above expression generate?
Answer: Option 2
Explanation:
Consider the empAge(empNo, age) Table
empNo |
age |
100 |
22 |
101 |
21 |
102 |
23 |
103 |
24 |
104 |
25 |
105 |
26 |
200 |
22 |
201 |
21 |
202 |
23 |
203 |
24 |
204 |
25 |
205 |
26 |
206 |
27 |
We run the following Query on above Table
IIempNo(empAge ⋈ (age>age1) ρempNo 1, age1(empAge))
We get Output Table as
empNo |
100 |
102 |
103 |
104 |
105 |
200 |
202 |
203 |
204 |
205 |
206 |
Hence the Output is "Employees whose age greater than at least one employee" or "Employees whose age is no minimum".
Consider the relational schema given below, where eId of the relation dependent is a foreign
key referring to empId of the relation employee. Assume that every employee has at least one
associated dependent in the dependent relation.
Employee (empId, empName, empAge)
Dependent(depId, eId, depName, depAge)
Consider the following relational algebra query:
Π_{empId}(employee)-Π_{empId} (employee⋈(empId = eID)∧(empAge ≤ depAge)dependent)
The above query evaluates to the set of empIds of employees whose age is greater than that ofExplanation:
Example:
Employee Relation:
EmpID |
empName |
empAge |
e1 |
A |
20 |
e2 |
B |
25 |
e3 |
C |
24 |
Dependent Relation:
depID |
eID |
depName |
Age |
d1 |
e1 |
X |
30 |
d2 |
e1 |
Y |
25 |
d3 |
e1 |
Z |
20 |
d4 |
e2 |
P |
18 |
d5 |
e2 |
P |
17 |
d7 |
e3 |
Z |
35 |
R1=Π_{empId} (employee⋈(empId = eID)∧(empAge ≤ depAge)dependent)
Output : e1,e3
R2=Π_{empId}(employee)
Output: e1,e2,e3
R = R2 - R1= {e1,e2,e3} - {e1,e3} = e2
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
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 two relational schemas:
emp(ID, name, address, phone number, deptID)
department(ID, managerID, deptname, location)
What does the following relational expression perform?
\(π_{(name, managerID)} (σ_{emp.deptID=department.ID} (σ_{deptname="Marketing"}(emp × department)))\)
Total MCQS : 20
gradeTotal MCQS : 30
gradeTotal MCQS : 230
gradeTotal MCQS : 15
gradeTotal MCQS : 31
grade191 Points
54 Points
53 Points
52 Points
49 Points