The Relational Model and Normalization Questions and Answers

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.

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.

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.

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.

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.

Answer: B

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

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.

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.

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.

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.

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.