The Relational Model and Normalization Questions and Answers

Q: Which of the following is NOT a superkey in a relational schema with attributes \(V, W, X, Y, Z\) and primary key \(VY\)?

The attribute set that is NOT a superkey is \(VWXZ\).A superkey is any attribute set that contains a candidate key. The primary key is \(VY\), so any set containing both \(V\) and \(Y\) is a superkey.\(VXYZ\), \(VWXY\) and \(VWXYZ\) all contain \(V\) and \(Y\). \(VWXZ\) is missing \(Y\); with no other candidate key given, it cannot be assumed to identify tuples uniquely, so it is not a superkey.

Q: Consider two relations describing teams and players in a sports league:teams(tid, tname): tid, tname are team-id and team-name, respectivelyplayers(pid, pname, tid): pid, pname, and tid denote player-id, player-name and the team-id of the player, respectivelyWhich ONE of the following tuple relational calculus queries returns the name of the players who play for the team having tname as 'MI'?

The correct query is { p.pname | p ∈ players &and; ∃t (t ∈ teams &and; p.tid = t.tid &and; t.tname = 'MI')}.The result must range over players, so p must be a tuple of players. A player qualifies if there exists a team tuple t that is the player's own team (p.tid = t.tid, the join condition) and whose name is 'MI'. Option A states exactly this.Options B and D take p from teams, which has no pname attribute, so they do not even return player names. Option C drops the join condition p.tid = t.tid: as long as some team called 'MI' exists, it returns the name of every player in the league, whichever team they play for.

Q: Consider a relational database schema with two relations R(P, Q) and S(X, Y).Let \[ \begin{aligned} E = \{\langle u\rangle \mid\ & \exists v\, \exists w\ \langle u, v\rangle \in R \\ & \wedge \langle v, w\rangle \in S\} \end{aligned} \] be a tuple relational calculus expression.Which one of the following relational algebraic expressions is equivalent to E ?

The equivalent expression is \(\Pi_{P}(S \bowtie_{S.X=R.Q} R)\).E returns each u such that some tuple \(\langle u, v\rangle\) is in R and some tuple \(\langle v, w\rangle\) is in S. The shared variable v is R's second attribute Q and S's first attribute X, so the join condition is R.Q = S.X, and the result keeps u, which is R.P.Joins are commutative, so writing S first does not matter. The other options join on R.P, S.Y or a different pairing, each linking the wrong attributes: R.P = S.X matches u with S's first attribute, and S.Y = R.Q links v to S's second attribute.

Q: The relation schema, Person(pid,city), describes the city of residence for every person uniquely identified by pid. The following relational algebra operators are available: selection, projection, cross product, and rename.To find the list of cities where at least 3 persons reside, using the above operators, the minimum number of cross product operations that must be used is

The minimum is 2 cross products.Without aggregation, 'at least 3 persons' must be expressed by finding three distinct persons in the same city. Rename gives three copies of Person, P1, P2 and P3, and two cross products combine them:\[ \pi_{P1.city}(\sigma_{\theta}(P1 \times P2 \times P3)) \]where \(\theta\) requires equal cities and pairwise different pids (P1.pid \(\lt\) P2.pid \(\lt\) P3.pid will do).One cross product only compares two tuples at a time, which can show a city with at least 2 persons but not 3. Three or four cross products would work but are not the minimum.

Q: In a relational data model, which one of the following statements is TRUE?

The true statement is A relation with only two attributes is always in BCNF.For a relation R(A, B), the only possible non-trivial dependencies are A → B and B → A. If A → B holds, A is a key; if B → A holds, B is a key; if neither holds, the key is AB and there is no non-trivial dependency to violate BCNF. In every case each determinant is a superkey.The others are false. R(A, B, C) with AB → C and C → B has keys AB and AC, so every attribute is prime, yet C → B violates BCNF because C is not a superkey. A relation whose key is all of its attributes has no non-prime attribute at all. And BCNF decomposition can lose dependencies: decomposing that same relation into BCNF loses AB → C.

Q: Which one of the options given below refers to the degree (or arity) of a relation in relational database systems?

The degree (arity) is the number of attributes of its relation schema.A relation schema R(A1, A2, ..., An) has degree n: a binary relation has two attributes, a ternary one three. The degree is fixed by the schema and does not change as rows are inserted or deleted.The number of tuples stored in the relation is its cardinality, which changes with every insert and delete, and 'number of entries' describes the same thing. The number of distinct domains is not a defined property: several attributes can share one domain (two date columns, say), so it can be smaller than the degree.

Q: It can be proved that the additional rule of Union is also implied by the three rules of Armstrong’s Axioms. Listed below are four combinations of these three rules. Which one of these combinations is both necessary and sufficient for the proof ?

The necessary and sufficient combination is Augmentation and Transitivity.A proof of the Union rule from X → Y and X → Z:Augment X → Y with X: XX → XY, that is X → XY.Augment X → Z with Y: XY → YZ.Transitivity on X → XY and XY → YZ gives X → YZ.So augmentation and transitivity suffice, and reflexivity is not needed. Both are necessary: transitivity alone can only chain dependencies that already exist and can never put Y and Z together on one right-hand side, and without transitivity the two augmented dependencies cannot be joined. Reflexivity and augmentation alone cannot link X → XY to XY → YZ. All three rules work, but the question asks for the combination that is also necessary.

Q: Consider the following set of functional dipendencies of the schema (A, B, C) :A → BC, B → C, A → B, AB → CThen the canonical cover for this set is :

The canonical cover is A → B & B → C.Split right sides: A → B, A → C, B → C, A → B, AB → C.In AB → C, B is extraneous, because A → C already holds; it becomes A → C.A → C is redundant: it follows from A → B and B → C by transitivity.What remains is A → B and B → C, which still implies everything in the original set. Options containing A → BC keep a redundant attribute (C is derivable from B), and AB → C has an extraneous attribute, so A, B and C are not minimal.

Q: A table has single valued attributes A, B, C, D and E, with the following functional dependencies{A → C, B → D, AB → E}In terms of normalization, this table is in :

The table is in 1 NF.Since every attribute is single-valued, the table is at least in first normal form. From A → C, B → D and AB → E, the closure of AB is {A, B, C, D, E}, so AB is the candidate key.Second normal form forbids partial dependencies of a non-key attribute on part of a candidate key. Here C depends on A alone and D on B alone, both proper parts of the key AB. These partial dependencies violate 2NF, so the table is not in 2NF, and therefore not in 3NF or BCNF either.

Which of the following is NOT a superkey in a relational schema with attributes \(V, W, X, Y, Z\) and primary key \(VY\)?

A.\(VXYZ\)
B.\(VWXZ\)
C.\(VWXY\)
D.\(VWXYZ\)

Exam Year(s)

GATE CS - 2016,

Answer: B

The attribute set that is NOT a superkey is \(VWXZ\).

A superkey is any attribute set that contains a candidate key. The primary key is \(VY\), so any set containing both \(V\) and \(Y\) is a superkey.

\(VXYZ\), \(VWXY\) and \(VWXYZ\) all contain \(V\) and \(Y\). \(VWXZ\) is missing \(Y\); with no other candidate key given, it cannot be assumed to identify tuples uniquely, so it is not a superkey.

Discuss in Forum

Consider two relations describing teams and players in a sports league:

teams(tid, tname): tid, tname are team-id and team-name, respectively
players(pid, pname, tid): pid, pname, and tid denote player-id, player-name and the team-id of the player, respectively

Which ONE of the following tuple relational calculus queries returns the name of the players who play for the team having tname as 'MI'?

A.{ p.pname | p ∈ players ∧ ∃t (t ∈ teams ∧ p.tid = t.tid ∧ t.tname = 'MI')}
B.{ p.pname | p ∈ teams ∧ ∃t (t ∈ players ∧ p.tid = t.tid ∧ t.tname = 'MI')}
C.{ p.pname | p ∈ players ∧ ∃t (t ∈ teams ∧ t.tname = 'MI')}
D.{ p.pname | p ∈ teams ∧ ∃t (t ∈ players ∧ t.tname = 'MI')}

Exam Year(s)

GATE CS - 2025,

Answer: A

The correct query is { p.pname | p ∈ players ∧ ∃t (t ∈ teams ∧ p.tid = t.tid ∧ t.tname = 'MI')}.

The result must range over players, so p must be a tuple of players. A player qualifies if there exists a team tuple t that is the player's own team (p.tid = t.tid, the join condition) and whose name is 'MI'. Option A states exactly this.

Options B and D take p from teams, which has no pname attribute, so they do not even return player names. Option C drops the join condition p.tid = t.tid: as long as some team called 'MI' exists, it returns the name of every player in the league, whichever team they play for.

Discuss in Forum

Consider a relational database schema with two relations R(P, Q) and S(X, Y).

Let \[ \begin{aligned} E = \{\langle u\rangle \mid\ & \exists v\, \exists w\ \langle u, v\rangle \in R \\ & \wedge \langle v, w\rangle \in S\} \end{aligned} \] be a tuple relational calculus expression.

Which one of the following relational algebraic expressions is equivalent to E ?

A.\(\Pi_{P}(R \bowtie_{R.P=S.X} S)\)
B.\(\Pi_{P}(S \bowtie_{S.X=R.Q} R)\)
C.\(\Pi_{P}(R \bowtie_{R.P=S.Y} S)\)
D.\(\Pi_{P}(S \bowtie_{S.Y=R.Q} R)\)

Exam Year(s)

GATE CS - 2026,

Answer: B

The equivalent expression is \(\Pi_{P}(S \bowtie_{S.X=R.Q} R)\).

E returns each u such that some tuple \(\langle u, v\rangle\) is in R and some tuple \(\langle v, w\rangle\) is in S. The shared variable v is R's second attribute Q and S's first attribute X, so the join condition is R.Q = S.X, and the result keeps u, which is R.P.

Joins are commutative, so writing S first does not matter. The other options join on R.P, S.Y or a different pairing, each linking the wrong attributes: R.P = S.X matches u with S's first attribute, and S.Y = R.Q links v to S's second attribute.

Discuss in Forum

The relation schema, Person(pid,city), describes the city of residence for every person uniquely identified by pid. The following relational algebra operators are available: selection, projection, cross product, and rename.

To find the list of cities where at least 3 persons reside, using the above operators, the minimum number of cross product operations that must be used is

Exam Year(s)

GATE CS - 2024,

Answer: B

The minimum is 2 cross products.

Without aggregation, 'at least 3 persons' must be expressed by finding three distinct persons in the same city. Rename gives three copies of Person, P1, P2 and P3, and two cross products combine them:

\[ \pi_{P1.city}(\sigma_{\theta}(P1 \times P2 \times P3)) \]

where \(\theta\) requires equal cities and pairwise different pids (P1.pid \(\lt\) P2.pid \(\lt\) P3.pid will do).

One cross product only compares two tuples at a time, which can show a city with at least 2 persons but not 3. Three or four cross products would work but are not the minimum.

Discuss in Forum

Consider the relations r(A, B) and s(B, C), where s.B is a primary key and r.B is a foreign key referencing s.B. Consider the query

Q: \(r \bowtie (\sigma_{B \lt 5}(s))\)

Let LOJ denote the natural left outer-join operation. Assume that r and s contain no null values.

Which one of the following queries is NOT equivalent to Q?

A.\(\sigma_{B \lt 5}(r \bowtie s)\)
B.\(\sigma_{B \lt 5}(r\ \mathit{LOJ}\ s)\)
C.\(r\ \mathit{LOJ}\ (\sigma_{B \lt 5}(s))\)
D.\(\sigma_{B \lt 5}(r)\ \mathit{LOJ}\ s\)

Exam Year(s)

GATE CS - 2018,

Answer: C

The query that is NOT equivalent is \(r\ LOJ\ (\sigma_{B \lt 5}(s))\).

Q keeps only the r tuples whose B value is below 5, joined with their s tuple. Because r.B is a foreign key into s.B and there are no nulls, every r tuple has exactly one matching s tuple.

The left outer join of r with the filtered s keeps every r tuple: those with B below 5 join normally, but those with B of 5 or more find no partner and are kept with C padded with null. Those extra rows are not in Q.

The others match Q. Selecting \(B \lt 5\) after the join gives the same rows. With the foreign key, r LOJ s equals the plain join, so selecting after it is the same again. And \(\sigma_{B \lt 5}(r)\ LOJ\ s\) joins only the small r tuples, each of which has its partner in s.

Discuss in Forum

In a relational data model, which one of the following statements is TRUE?

A.A relation with only two attributes is always in BCNF.
B.If all attributes of a relation are prime attributes, then the relation is in BCNF.
C.Every relation has at least one non-prime attribute.
D.BCNF decompositions preserve functional dependencies.

Exam Year(s)

GATE CS - 2022,

Answer: A

The true statement is A relation with only two attributes is always in BCNF.

For a relation R(A, B), the only possible non-trivial dependencies are A → B and B → A. If A → B holds, A is a key; if B → A holds, B is a key; if neither holds, the key is AB and there is no non-trivial dependency to violate BCNF. In every case each determinant is a superkey.

The others are false. R(A, B, C) with AB → C and C → B has keys AB and AC, so every attribute is prime, yet C → B violates BCNF because C is not a superkey. A relation whose key is all of its attributes has no non-prime attribute at all. And BCNF decomposition can lose dependencies: decomposing that same relation into BCNF loses AB → C.

Discuss in Forum

Which one of the options given below refers to the degree (or arity) of a relation in relational database systems?

A.Number of attributes of its relation schema.
B.Number of tuples stored in the relation.
C.Number of entries in the relation.
D.Number of distinct domains of its relation schema.

Exam Year(s)

GATE CS - 2023,

Answer: A

The degree (arity) is the number of attributes of its relation schema.

A relation schema R(A1, A2, ..., An) has degree n: a binary relation has two attributes, a ternary one three. The degree is fixed by the schema and does not change as rows are inserted or deleted.

The number of tuples stored in the relation is its cardinality, which changes with every insert and delete, and 'number of entries' describes the same thing. The number of distinct domains is not a defined property: several attributes can share one domain (two date columns, say), so it can be smaller than the degree.

Discuss in Forum

Direction for [ Question No: 8 To 8 ] :

In the context of schema normalization in relational DBMS, consider a set F of functional dependencies. The set of all functional dependencies implied by F is called the closure of F. To compute the closure of F, Armstrong’s Axioms can be applied. Consider X, Y, and Z as sets of attributes over a relational schema. The three rules of Armstrong’s Axioms are described as follows.

Reflexivity: If \(Y \subseteq X\), then \(X \rightarrow Y\)
Augmentation: If \(X \rightarrow Y\), then \(XZ \rightarrow YZ\) for any Z
Transitivity: If \(X \rightarrow Y\) and \(Y \rightarrow Z\), then \(X \rightarrow Z\)

The additional rule of Union is defined as follows.
Union: If \(X \rightarrow Y\) and \(X \rightarrow Z\), then \(X \rightarrow YZ\)

It can be proved that the additional rule of Union is also implied by the three rules of Armstrong’s Axioms. Listed below are four combinations of these three rules. Which one of these combinations is both necessary and sufficient for the proof ?

A.Reflexivity, Augmentation, and Transitivity
B.Reflexivity and Augmentation
C.Transitivity
D.Augmentation and Transitivity

Exam Year(s)

GATE CS - 2026,

Answer: D

The necessary and sufficient combination is Augmentation and Transitivity.

A proof of the Union rule from X → Y and X → Z:

Augment X → Y with X: XX → XY, that is X → XY.
Augment X → Z with Y: XY → YZ.
Transitivity on X → XY and XY → YZ gives X → YZ.

So augmentation and transitivity suffice, and reflexivity is not needed. Both are necessary: transitivity alone can only chain dependencies that already exist and can never put Y and Z together on one right-hand side, and without transitivity the two augmented dependencies cannot be joined. Reflexivity and augmentation alone cannot link X → XY to XY → YZ. All three rules work, but the question asks for the combination that is also necessary.

Discuss in Forum

Consider the following set of functional dipendencies of the schema (A, B, C) :

A → BC, B → C, A → B, AB → C

Then the canonical cover for this set is :

A.A → BC & AB → C
B.A → BC & A → B
C.A → BC & B → C
D.A → B & B → C

Exam Year(s)

MH SET - 2018, SET Exam - 2018,

Answer: D

The canonical cover is A → B & B → C.

Split right sides: A → B, A → C, B → C, A → B, AB → C.
In AB → C, B is extraneous, because A → C already holds; it becomes A → C.
A → C is redundant: it follows from A → B and B → C by transitivity.

What remains is A → B and B → C, which still implies everything in the original set. Options containing A → BC keep a redundant attribute (C is derivable from B), and AB → C has an extraneous attribute, so A, B and C are not minimal.

Discuss in Forum

10.

A table has single valued attributes A, B, C, D and E, with the following functional dependencies

{A → C, B → D, AB → E}

In terms of normalization, this table is in :

A.1 NF
B.2 NF
C.3 NF
D.BCNF

Exam Year(s)

MH SET - 2019, SET Exam - 2019,

Answer: A

The table is in 1 NF.

Since every attribute is single-valued, the table is at least in first normal form. From A → C, B → D and AB → E, the closure of AB is {A, B, C, D, E}, so AB is the candidate key.

Second normal form forbids partial dependencies of a non-key attribute on part of a candidate key. Here C depends on A alone and D on B alone, both proper parts of the key AB. These partial dependencies violate 2NF, so the table is not in 2NF, and therefore not in 3NF or BCNF either.

Discuss in Forum

Loading…

Topics

Other Subjects

Tags: The Relational Model and Normalization Questions and Answers || The Relational Model and Normalization MCQ Questions and Answers || The Relational Model and Normalization GK Questions and Answers || The Relational Model and Normalization GK MCQ Questions || The Relational Model and Normalization Multiple Choice Questions and Answers || The Relational Model and Normalization GK || GK on The Relational Model and Normalization || Database Questions and Answers || Database MCQ Questions and Answers || Database GK Questions and Answers || GK on Database

The Relational Model and Normalization Questions and Answers

Direction for [ Question No: 8 To 8 ] :

Topics

Other Subjects

About Us

Stay informed about all important Dates & Notifications