Consider a relation schema TEAM. Suppose it is decomposed into two relations T1 and T2 during normalization. If the natural join (T1 and T2) reconstructs the original relation exactly, without producing any extra (spurious) tuples, then this decomposition is called:(GATE 2025 | MCQ | 1-mark)

Common attributes are primary keys in both relations

Lossless and Dependency-Preserving Design in DBMS

Last Updated :

Discuss

Comments

Question 1

Consider the relation R(P, Q, S, T, X, Y, Z, W) with the following functional dependencies:

PQ → X; P → YX; Q → Y; Y → ZW

Consider the decomposition of the relation R into the constituent relations according to the following two decomposition schemes:

D1: R = [(P,Q,S,T), (P,T,X), (Q,Y), (Y,Z,W)]

D2: R = [(P,Q,S), (T,X), (Q,Y), (Y,Z,W)]

Which one of the following options is correct?

(GATE 2021 | MCQ | 2-mark)

D1 is a lossless decomposition, but D2 is a lossy decomposition
D1 is a lossy decomposition, but D2 is a lossless decomposition
Both D1 and D2 are lossless decompositions
Both D1 and D2 are lossy decompositions

Question 2

Let r be a relation instance with schema R = (A, B, C, D). We define

r1 = 'select A,B,C from r'
r2 = 'select A, D from r'

Let s = r1 * r2 where * denotes natural join.

Given that the decomposition of r into r1 and r2 is lossy, which one of the following is TRUE?

(GATE 2015 | MCQ | 1-mark)

s is a subset of r
r is a subset of s
r = s
r and s are disjoint

Question 3

Consider a relational schema R(A, B, C, D) with the following functional dependencies:

A → B, B → C, C → D, D → B

The relation is decomposed into three sub-relations:(A, B), (B, C), and (B, D). This decomposition is:

(GATE 2018 | MCQ | 1-mark)

Lossless because shared attributes act as a superkey
Lossy because attribute A appears in only one relation
Lossless only if the original relation contains no duplicate tuples
Lossy because D → B creates a cycle.

Question 4

For a relational schema R(L, M, N, O, P), the following functional dependencies hold:

M → O, NO → P, P → L, L → MN

Suppose R is decomposed into R₁(L, M, N, P) and R₂(M, O). Which of the following statements is correct regarding the join?

(GATE 2012 | MCQ | 1-mark)

It is lossy because M cannot determine N or P
It is lossy because NO → P cannot be checked in either schema
It is lossless because all attributes are covered
It is lossless because M is a superkey for R₂

Question 5

Consider a relation schema TEAM. Suppose it is decomposed into two relations T₁ and T₂ during normalization. If the natural join (T₁ and T₂) reconstructs the original relation exactly, without producing any extra (spurious) tuples, then this decomposition is called:

(GATE 2025 | MCQ | 1-mark)

Lossless join
BCNF decomposition
Common attributes are primary keys in both relations
No foreign keys

Question 6

R(A,B,C,D) is a relation. Which of the following does NOT have a lossless join, dependency preserving BCNF decomposition?

(GATE 2011 | MCQ | 2-mark)

A → B, B → CD
A → B, B → C, C → D
AB → C, C → AD
A → BCD

Question 7

Let R = (A, B, C) be a relational schema with the functional dependencies:

F = { A → B, B → C }

The decomposition of R into R₁(A, B) and R₂(A, C) is:

(GATE 2014 | MCQ | 2-mark)

Dependency preserving and lossless join
Lossless join but not dependency preserving
Dependency preserving but not lossless join
Neither dependency preserving nor lossless join

Question 8

Which of the following statements is TRUE?

(GATE 2019 | MCQ | 1-mark)

Every BCNF decomposition is always dependency preserving
Some schemas cannot be decomposed into 3NF while preserving dependencies
Dependency preservation allows checking constraints within a single relation
All of the above

Question 9

Let the set of functional dependencies F = { QR → S, R → P, S → Q } hold on a relation schema X = (P, Q, R, S). X is not in BCNF. Suppose X is decomposed into two schemas:

Y = (P, R)
Z = (Q, R, S)

Consider the following statements:

Both Y and Z are in BCNF.
Decomposition of X into Y and Z is dependency preserving and lossless.

Which of the above statements is correct?

(GATE 2019 | MCQ | 1-mark)

Both 1 and 2
1 only
2 only
Neither 1 nor 2

Question 10

Which of the following statements represents the primary functional advantage of a dependency preserving decomposition in a database?

(GATE 2014 | MCQ | 1-mark)

Guarantees BCNF decomposition
Avoids costly JOINs for constraint checking
Does not increase storage requirements
Guarantees lossless decomposition

Tags:

Computer Subject

DBMS

There are 10 questions to complete.

Take a part in the ongoing discussion