Practice Questions for GATE, UGC NET, ISRO, and NIELIT | DBMS and SQL with Solutions

Are you preparing for GATE CSE, NTA UGC NET, ISRO, or NIELIT Computer Science exams? Understanding DBMS and mastering SQL is crucial for scoring high in competitive exams. This article contains carefully selected practice questions on functional dependencies, normalization (3NF and BCNF), relational algebra, and complex SQL queries.

Each question is accompanied by a detailed solution, making this resource perfect for competitive exams like GATE, NTA NET, ISRO, and NIELIT. Practice with these high-quality questions to solidify your understanding of database management systems and boost your exam preparation. Let’s start solving!

Question

Consider a relation R(A, B, C, D, E) with functional dependencies F:
F = {AB → C, C → D, D → B, D → E}
If the number of keys in R is a and number of relation in the 3NF decomposition is b what is the value of (a – b)?

Solution:

R(A, B, C, D, E)
Closure of (AB)+ = {A, B, C, D, E}
Closure of (AC)+ = {A, B, C, D, E}
Closure of (AD)+ = {A, B, C, D, E}

All of 3 {AB, AC, AD} are key for R.
Functional dependencies {D → E} is not in 3NF
    R₁(ABCD)                R₂(DE)
    {AB → C}                {D → E}
    C → D
    D → B

All attributes are prime attributes in R₁.
Total 2 relations required
    a = 3, b = 2
    3 – 2 = 1

---

Question

Consider the following relational schema Adjacency (x, y) used to state edges of the directed graph.
Which of the following relational algebra expression results vertices set which forms loop with at least two vertices?

1. π_x( Adjacency ⋈_{Y=X₁ ∧ Y₁=X} ρ_X₁,Y₁(Adjacency) )
2. π_x( Adjacency ⋈_{Y=Y₁ ∧ X=X₁} ρ_X₁,Y₁(Adjacency) )
3. π_x( Adjacency ⋈_{Y=X₁ ∧ Y₁≠X} ρ_X₁,Y₁(Adjacency) )
4. π_x( Adjacency ⋈_{Y≠X₁ ∧ Y₁=X} ρ_X₁,Y₁(Adjacency) )

Solution: (A)

---

Question

Consider the following relation R(A₁, A₂, A₃, … A₁₅) with {A₁, A₂, … A₆} of relation R are simple candidate keys. The number of possible superkeys in relation R is _______.

Solution:

32256
m simple candidate key forms (2m - 1) superkey
m = 6
2m - 1 = 63
Total 15 - 6 = 9 non prime attributes
Total superkey = 63 × 29 = 63 × 512
              = 32256

---

Question

Consider the following statements:

1. S₁: Not every relation is possible to decompose into 3NF with dependency preserving.
2. S₂: An attribute declared as UNIQUE can have NULL as its value.
3. S₃: From A → B we can derive AC → BC which further leads to A → BC.

How many of the above statements are correct?

Solution:

S₁: The decomposition of 3NF is always lossless and dependency preserving.
S₂: An attribute declared as UNIQUE can have NULL as its value.
S₃: A → B
    AC → BC
    but it cannot derive A → BC

Only S₂ is correct.

---

Question

Consider a relation R(A, B, C, D, E) with functional dependencies F:
F = {AB → C, C → D, D → B, D → E}
If the number of keys in R is a and the number of relation in the 3NF decomposition is b, what is the value of (a – b) ______.

Solution:

• R(A, B, C, D, E)
• Closure of (AB)⁺ = {A, B, C, D, E}
• Closure of (AC)⁺ = {A, B, C, D, E}
• Closure of (AD)⁺ = {A, B, C, D, E}

All of 3 {AB, AC, AD} are key for R.

Functional dependencies {D → E} is not in 3NF.

Decomposition:

• R₁(ABCD)
• R₂(DE)

All attributes are prime attributes in R₁.
Total relations required = 2.

Answer: a = 3, b = 2 → 3 – 2 = 1

---

Relational Algebra on Directed Graph

Consider the following relational schema Adjacency (x, y) used to state edges of the directed graph. Which of the following relational algebra expressions results in vertices set which forms a loop with at least two vertices?

Options:

1. πx (Adjacency ⨝ ρx1,y1 (Adjacency))
where Y=x1 ∧ Y1=X
2. πx (Adjacency ⨝ ρx1,y1 (Adjacency))
where Y=Y1 ∧ X≠x1
3. πx (Adjacency ⨝ ρx1,y1 (Adjacency))
where Y≠x1 ∧ Y1≠X
4. πx (Adjacency ⨝ ρx1,y1 (Adjacency))
where Y≠x1 ∧ Y1=X

Correct Answer: Option (A)

---

Question

Consider the following relation R(A₁, A₂, A₃, … A₁₅) with {A₁, A₂, …. A₆} of relation R being simple candidate keys. The number of possible superkeys in relation R is ______.

Solution:

• m simple candidate key forms (2^m – 1) superkeys
• m = 6 → 2⁶ – 1 = 63
• Total attributes: 15 – 6 = 9 non-prime attributes
• Total superkeys = 63 × 2⁹ = 63 × 512 = 32256

Answer: 32256

---

Normalization Statements

Consider the following statements:

• S₁: Not every relation possible to decompose into 3NF with dependency preserving.
• S₂: An attribute declared as UNIQUE can have NULL as its value.
• S₃: From A → B, we can derive AC → BC which further leads to A → BC.

How many of the above statements are correct ______?

Solution:

• S₁: The decomposition of 3NF is always lossless and dependency preserving.
• S₂: An attribute declared as UNIQUE can have NULL as its value.
• S₃: A → B, AC → BC, but we cannot derive A → BC.

Answer: Only S₂ is correct.

---

Question

Consider the given query on the relation P(r, s) and R(s, t)

Q1: πr(P ∩ (πr(P) × πs(R)))
Q2: πr((P × πt(R)) ∩ (πs(P) × R))
Q3: πr(P ⨝ R)

Which of the above query produces same result?

1. Q₁ and Q₂ only
2. Q₂ and Q₂ only
3. Q₁ and Q₃ only
4. All of Q₁, Q₂ and Q₃

Correct Answer: D

Solution:
All relational algebra queries give the same result.

---

Question

Consider the following queries on the relation P(A, B) and Q(R, S):

Query 1: πA(P) − πA(P ⨝ Q)
Query 2: Select A FROM P WHERE NOT EXISTS 
(SELECT * FROM Q WHERE P.A ≤ Q.R)
Query 3: Select A FROM P WHERE A ≤ ALL (SELECT R FROM Q)

Which of the above queries gives the same result?

1. Query 1 and Query 2
2. Query 1 and Query 3
3. Query 2 and Query 3
4. All of Query 1, Query 2 and Query 3

Correct Answer: A

Solution:
Query 1: Gives result set of A values which are greater than every value of Q.
Query 2: Gives result same as Query 1.
Query 3: Gives result set of A values which are less than or equal to all values of Q.

---

Question

Consider the following relation with functional dependencies R{A, B, C, D, E, F, G}:

FD’s F = {AB → C, AC → B, AD → E, B → D, BC → A, E → G}

If we decompose the relation R into lossless dependency preserving BCNF then which of the following is not a sub relation?

1. R₁(A, B, C)
2. R₂(A, B, D, E)
3. R₃(E, G)
4. Both (b) and (c)

Correct Answer: B

Solution:
R[A, B, C, D, E, F, G]
F = {AB → C, AC → B, AD → E, B → D, BC → A, E → G}
{AC, AB, BC} is keys so AD → E, B → D, E → G is not in BCNF. So decompose relation R:
R₁(A, B, C), R₂(B → D), R₃(E → G), R₄(A, D, E), R₅(ABF)
So in all options, (A, B, D, E) is not a sub relation.

---

Question

The order of an internal node in a B+ tree is the maximum number of child pointers a node can have. Suppose that a child pointer takes 10 bytes, the search field value takes 13 bytes, and the record pointer is 12 bytes. The block size is 1024 bytes. What is the order of internal node ______?

Correct Answer: 45

Solution:
Let n be the order of the internal node:
10n + 13(n − 1) ≤ 1024
10n + 13n − 13 ≤ 1024
23n ≤ 1037
n ≤ 45.08
n = 45

---

Question

Consider a relation R(A, B, C, D) with functional dependencies:

A → B, B → C, C → D

Which of the following decomposition is not lossless?

1. R₁(A, B), R₂(B, C), R₃(C, D)
2. R₁(A, B), R₂(A, C), R₃(A, D)
3. R₁(A, D), R₂(B, D), R₃(C, D)

Solution:

(c) Consider an instance of relation R:

| A | B | C | D |
|---|---|---|---|
| 3 | 3 | 3 | 3 |
| 5 | 3 | 3 | 3 |

The join of decomposed relations R₁, R₂, and R₃ contains some extra tuples, so it is not lossless.

---

Question

Consider the following relation:

Sailors (sid, sname, rating, age)

Instance of sailors:

| sid | sname | rating | age |
|-----|-------|--------|-----|
| 1   | X     | 11     | 19  |
| 2   | A     | 4      | 15  |
| 8   | X     | 7      | 33  |
| 10  | C     | 5      | 17  |
| 12  | Y     | 8      | 16  |
| 18  | D     | 15     | 25  |
| 25  | Y     | 6      | 21  |

How many number of tuples is returned by the following SQL query when executed on the given instance of Sailors?

Select S.sname 
FROM Sailors S 
WHERE NOT EXISTS (
    SELECT * FROM Sailors S1
    WHERE S1.age < 18 AND S.rating <= S1.rating
);

1. 1
2. 2
3. 3
4. 4

Solution:

SQL query returns the sname of sailors with a higher rating than all sailors with age less than 18. The relation returned by the SQL query:

| sname |
|-------|
| X     |
| Y     |
| D     |

Correct Option: 3 tuples are returned.

---