- 1.
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 -
filter_dramaExplanationAnswer is : B
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:
- Basic operations in relational algebra are: Select, project, union, set difference, Cartesian product, rename.
- Other operations are natural join, intersection, division.
- From the above options, operator that are included from mathematical set theory are intersection and Cartesian product.
- In mathematical set theory, intersection is used to find the common elements between two sets. It works same in relational algebra.
- Cartesian product is used to find the multiplication of two sets by including common elements only once.
- Basic operations in relational algebra are: Select, project, union, set difference, Cartesian product, rename.
- 2.
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\) ?
-
filter_dramaExplanationAnswer is : A
The correct answer is option 1Explanation:
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: 42) 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 - 3.
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.
-
filter_dramaExplanationAnswer is : B
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
- table_name: name of the table.
- column_name: name of the column according to which the data is needed to be arranged.
- ASC: to sort the data in ascending order.
- DESC: to sort the data in descending order.
- | : use either ASC or DESC to sort in ascending or descending order
- 4.
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)
-
filter_dramaExplanationAnswer is : C
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
- 5. Consider Join of a relation R with a relation S. If R has m tuples and S has n tuples, then maximum and minimum sizes of the Join respectively are
-
filter_dramaExplanationAnswer is : B
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.
- 6.
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. -
filter_dramaExplanationAnswer is : B
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
- 7.
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”.
-
filter_dramaExplanationAnswer is : A
Key Points
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).
- 8.
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
-
filter_dramaExplanationAnswer is : C
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
- 9.
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? -
filter_dramaExplanationAnswer is : C
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
- 10. Relation algebra query that finds customer's who have a balance more than 1000, from deposit table is:
-
filter_dramaExplanationAnswer is : C
π 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
-
Rahul Kumar
191 Points
-
VIKRAM JEET
54 Points
-
GEETHIKA CHOWDARY
53 Points
-
sunita saini
52 Points
-
Zain
49 Points
