The Relational Model and Normalization Questions and Answers

Q: Let \(R = \{A, B, C, D, E, F\}\) be a relation schema with the following dependencies \(C \to F,\ E \to A,\ EC \to D,\ A \to B\). Which of the following is a key for R ?

EC is a key for R. Its closure contains every attribute.\[ \begin{aligned} E \to A,\ A \to B &: \ \{E, A, B, C\} \\ C \to F,\ EC \to D &: \ \{A, B, C, D, E, F\} \end{aligned} \]Neither E nor C alone gives all the attributes, so EC is minimal. The set CD cannot produce E, AE cannot produce C, and AC cannot produce E, because no dependency has E on its right side, so E and C must be in every key and EC is the only candidate among the options.

Q: Consider the following relational schemas for a library database : Book (Title, Author, Catalog_no, Publisher, Year, Price) Collection(Title, Author, Catalog_no) with the following functional dependencies :I. Title, Author → Catalog_noII. Catalog_no → Title, Author, Publisher, YearIII. Publisher, Title, Year → Price Assume (Author, Title) is the key for both schemas. Which one of the following is true ?

Book is in 2NF and Collection is in 3NF.Catalog_no determines Title, Author, Publisher and Year, and with Publisher, Title, Year \(\rightarrow\) Price it determines Price too, so Catalog_no is a candidate key beside (Title, Author). In Book the dependency Publisher, Title, Year \(\rightarrow\) Price has a left side that is not a superkey and a non-prime right side, which breaks 3NF; there is no partial dependency, so Book is in 2NF. In Collection every left side is a superkey, so it is in BCNF and therefore in 3NF.Options A and B claim Book is in BCNF or 3NF, and option D understates Collection.

Q: Consider the schema \(R = \{S, T, U, V\}\) and the dependencies \(S \rightarrow T\), \(T \rightarrow U\), \(U \rightarrow V\) and \(V \rightarrow S\). If \(R = (R_{1}\) and \(R_{2})\) be a decomposition such that \(R_{1} \cap R_{2} = \phi\) then the decomposition is

The decomposition is in both 2NF and 3NF.The dependencies S to T, T to U, U to V and V to S make every attribute determine every other attribute, so each of S, T, U and V is a candidate key. With only prime attributes there is no partial or transitive dependency on a non-prime attribute, so the relations are in 2NF, 3NF and BCNF.Options A, B and C each claim that some normal form fails, which cannot happen with all attributes prime.

Q: A relation in this form is free of all modification anomalies.

Correct answer: Domain/key normal formFagin proved that a relation in domain/key normal form has no modification anomalies of any kind, and conversely that any relation free of them is in DK/NF. It is defined by requiring that every constraint on the relation follow from its domain and key definitions alone. The difficulty is that there is no general procedure for putting a relation into it.

Q: A relation is in first normal form if every attribute value in it is atomic.

Correct answer: TrueFirst normal form forbids repeating groups and multi-valued entries, requiring each cell to hold a single indivisible value. A column storing a comma-separated list of phone numbers violates it.

41.

The "PROJECT" operator of a relational algebra creates a new table that has always

A.More columns than columns in original table
B.More rows than original table
C.Same number of rows as the original table
D.Same number of columns as the original table

Exam Year(s)

UGC NET - 2013,

Answer: C

Of the four statements only option (C), the same number of rows as in the original table, holds for the PROJECT operator: it picks out chosen columns and each row of the original gives one row of the result. In SQL, which keeps duplicate rows, the count of rows is unchanged; in a set-based relational algebra duplicates would be removed, so the count can only fall, and it could never rise.

The result cannot have more columns (A) or more rows (B) than the original, and it has the same number of columns (D) only when every column is kept, which is not what the operator always does.

Discuss in Forum

42.

For a database relation R(a, b, c, d) where the domains of a, b, c, d include only the atomic values. The functional dependency a → c, b → d holds in the following relation

A.In 1NF not in 2NF
B.In 2NF not in 3NF
C.In 3NF
D.In 1NF

Exam Year(s)

UGC NET - 2013,

Answer: A

The relation is in 1NF but not in 2NF, which is option (A). All the attributes are atomic, so it is in 1NF. The key of the relation is (a, b), since a and b determine c and d, yet a alone determines c and b alone determines d, so c and d depend on part of the key, which is a partial dependency and violates 2NF.

A relation that is not in 2NF cannot be in 3NF, so options (B) and (C) are wrong, and (D) 'in 1NF' is incomplete, since it does not say that 2NF fails.

Discuss in Forum

43.

_______ constraints ensure that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation.

A.Logical Integrity
B.Referential Integrity
C.Domain Integrity
D.Data Integrity

Exam Year(s)

UGC NET - 2013,

Answer: B

Referential integrity constraints ensure that a value that appears in a relation for a given set of attributes also appears for a certain set of attributes in another relation. This is the rule behind a foreign key.

Domain integrity restricts the values an attribute may take, data integrity is the general idea of correctness of stored data, and logical integrity is not the standard name for this constraint. So referential integrity is the answer.

Discuss in Forum

44.

Consider the following schemas : Branch_Schema = (branch_name, assets, city) Customer_Schema = (cutstomer_name, street, city) Deposit_Schema = (branch_name, account_number, customer_name, balance) Borrow_Schema = (branch_name, loan_number, customer_name, amount) Which of the following tuple relational calculus finds all customers who have loan amount more than ₹ 12,000 ?

A.{t(customer_name) | t∈borrow[?] t[amount]\(\gt\)12000}
B.{t |t(customer_name) | t∈borrow[?] t[amount]\(\gt\)12000}
C.{t |[?] ∈ borrow (t(customer_name=s(customer_ name))[?] [amount]\(\gt\)12000}
D.{t |[?] ∈ borrow (t(customer_name)[?] s[amount] \(\gt\)12000}

Exam Year(s)

UGC NET - 2013,

Answer: C

The correct expression is the third option: the set of tuples t such that there is a tuple s in borrow with the customer name of t equal to that of s and the amount of s more than 12000. The existential quantifier on s is unprinted in the booklet (the symbol appears as [?]), but the structure is that of this option, which relates t and s through the customer name and compares s's amount.

The first two options compare the amount of t, a tuple that has only the customer name, and the fourth option does not equate the customer name of t with that of s. So only the third relates the two tuples properly.

Discuss in Forum

45.

Which of the following statements is false ?

A.Any relation with two attributes is in BCNF.
B.A relation in which every key has only one attribute is in 2NF.
C.A prime attribute can be transitively dependent on a key in 3NF relation.
D.A prime attribute can be transitively dependent on a key in BCNF relation.

Exam Year(s)

UGC NET - 2014,

Answer: D

The false statement is that a prime attribute can be transitively dependent on a key in a BCNF relation. In BCNF every determinant is a candidate key, so no transitive dependency of any attribute, prime or not, on a key can occur. In 3NF, on the other hand, a prime attribute can be transitively dependent on a key. The other statements are true: a relation of two attributes is always in BCNF, and a relation whose keys have a single attribute has no partial dependency, so it is in 2NF. The reason is that BCNF is a stricter form of 3NF, so anything that fails in BCNF is allowed in 3NF but not the reverse.

Discuss in Forum

46.

Let \(R = \{A, B, C, D, E, F\}\) be a relation schema with the following dependencies \(C \to F,\ E \to A,\ EC \to D,\ A \to B\). Which of the following is a key for R ?

A.CD
B.EC
C.AE
D.AC

Exam Year(s)

UGC NET - 2014,

Answer: B

EC is a key for R. Its closure contains every attribute.

\[ \begin{aligned} E \to A,\ A \to B &: \ \{E, A, B, C\} \\ C \to F,\ EC \to D &: \ \{A, B, C, D, E, F\} \end{aligned} \]

Neither E nor C alone gives all the attributes, so EC is minimal. The set CD cannot produce E, AE cannot produce C, and AC cannot produce E, because no dependency has E on its right side, so E and C must be in every key and EC is the only candidate among the options.

Discuss in Forum

47.

Consider the following relational schemas for a library database : Book (Title, Author, Catalog_no, Publisher, Year, Price) Collection(Title, Author, Catalog_no) with the following functional dependencies :

I. Title, Author → Catalog_no
II. Catalog_no → Title, Author, Publisher, Year
III. Publisher, Title, Year → Price Assume (Author, Title) is the key for both schemas. Which one of the following is true ?

A.Both Book and Collection are in BCNF.
B.Both Book and Collection are in 3NF.
C.Book is in 2NF and Collection in 3NF.
D.Both Book and Collection are in 2NF.

Exam Year(s)

UGC NET - 2014,

Answer: C

Book is in 2NF and Collection is in 3NF.

Catalog_no determines Title, Author, Publisher and Year, and with Publisher, Title, Year \(\rightarrow\) Price it determines Price too, so Catalog_no is a candidate key beside (Title, Author). In Book the dependency Publisher, Title, Year \(\rightarrow\) Price has a left side that is not a superkey and a non-prime right side, which breaks 3NF; there is no partial dependency, so Book is in 2NF. In Collection every left side is a superkey, so it is in BCNF and therefore in 3NF.

Options A and B claim Book is in BCNF or 3NF, and option D understates Collection.

Discuss in Forum

48.

Consider the schema \(R = \{S, T, U, V\}\) and the dependencies \(S \rightarrow T\), \(T \rightarrow U\), \(U \rightarrow V\) and \(V \rightarrow S\). If \(R = (R_{1}\) and \(R_{2})\) be a decomposition such that \(R_{1} \cap R_{2} = \phi\) then the decomposition is

A.not in 2NF
B.in 2NF but not in 3NF
C.in 3NF but not in 2NF
D.in both 2NF and 3NF

Exam Year(s)

UGC NET - 2014,

Answer: D

The decomposition is in both 2NF and 3NF.

The dependencies S to T, T to U, U to V and V to S make every attribute determine every other attribute, so each of S, T, U and V is a candidate key. With only prime attributes there is no partial or transitive dependency on a non-prime attribute, so the relations are in 2NF, 3NF and BCNF.

Options A, B and C each claim that some normal form fails, which cannot happen with all attributes prime.

Discuss in Forum

49.

A relation in this form is free of all modification anomalies.

A.First normal form
B.Second normal form
C.Third normal form
D.Domain/key normal form

Answer: D

Correct answer: Domain/key normal form

Fagin proved that a relation in domain/key normal form has no modification anomalies of any kind, and conversely that any relation free of them is in DK/NF. It is defined by requiring that every constraint on the relation follow from its domain and key definitions alone. The difficulty is that there is no general procedure for putting a relation into it.

Discuss in Forum

50.

A relation is in first normal form if every attribute value in it is atomic.

A.True
B.False

Answer: A

Correct answer: True

First normal form forbids repeating groups and multi-valued entries, requiring each cell to hold a single indivisible value. A column storing a comma-separated list of phone numbers violates it.

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

Topics

Other Subjects

About Us

Stay informed about all important Dates & Notifications