The Relational Model and Normalization Questions and Answers

Answer: A

The relation is always in 2 NF.

The underlined A is the primary key, so A determines B and C. With B → C as well, C depends on the key through B: A → B → C is a transitive dependency.

Because the key is a single attribute, no non-key attribute can depend on part of it, so there is no partial dependency and the relation is in 2NF. The transitive dependency means it is not in 3NF, and therefore not in BCNF or 4NF either. Splitting it into R1(A, B) and R2(B, C) would bring it to 3NF.

Answer: A

This is 1 NF.

First normal form requires every attribute to hold a single atomic value and forbids repeating groups, such as Phone1, Phone2, Phone3 columns or a list stored in one field. A table with no repeating columns and atomic values is in 1NF.

2NF additionally removes partial dependencies on part of a composite key, 3NF removes transitive dependencies, and 4NF removes multivalued dependencies. None of these is about repeating columns, which is the defining concern of 1NF.

Answer: B

3NF requires that the relation is in 2NF and has no transitive dependencies.

A relation is in third normal form when it is in second normal form and no non-key attribute depends on the key only through another non-key attribute. For example, in Employee(EmpId, DeptId, DeptName), DeptName depends on EmpId through DeptId, which violates 3NF.

Having no partial dependencies is the condition for 2NF, and full functional dependency on the whole key restates it. 'All determinants must be candidate keys' is the stricter Boyce-Codd normal form.

Answer: A

Transitive dependencies are eliminated by 3NF.

A relation is in third normal form when it is in 2NF and no non-key attribute depends on the key only through another non-key attribute. If emp_id determines dept_id and dept_id determines dept_name, then dept_name depends transitively on emp_id; 3NF moves dept_name into a separate department table.

1NF (printed INF) removes repeating groups and requires atomic values, and 2NF removes partial dependencies on part of a composite key. BCNF is a stricter form of 3NF that deals with dependencies whose determinant is not a candidate key; the step that removes transitive dependencies is 3NF.

Answer: A

A relation in BCNF is always in 3NF. BCNF is the stricter form: every determinant must be a candidate key. 3NF only requires that for each non-trivial dependency the left side is a superkey or the right side is a prime attribute, and a relation that satisfies the first condition always satisfies 3NF.

The reverse is not true: a relation in 3NF need not be in BCNF, for instance when a non-key attribute determines a prime attribute. So 'A relation in 3NF is always in BCNF' is false, 'BCNF and 3NF are same' is false, and 'A relation in BCNF is not in 3NF' contradicts the first statement.

Answer: A

The decomposition R1(A, B) and R2(C, D) keeps both dependencies, A to B in R1 and C to D in R2, so it is dependency preserving. It is not lossless, because R1 and R2 have no attribute in common, so their natural join is a cross product that gives spurious tuples, and no dependency tells how to join them. So it is dependency preserving but not lossless join, option (A). Options (B), (C) and (D) all claim it is lossless.

Answer: A

A rule that student marks must not exceed 100 is an integrity constraint (a domain or check constraint). It restricts the values an attribute may hold so the database always stays valid.

A referential constraint concerns a foreign key that must match a primary key in another table. 'Over-defined constraint' and 'feasible constraint' are not standard database terms for a value-range rule like this one.

Answer: A

The query that finds customers with balance above 10,000 is \(\pi_{\text{customer-name}}(\sigma_{\text{balance} \gt 10000}(\text{Deposit}))\). The Deposit relation holds Customer-name and Balance, so first the selection keeps the rows with balance above 10000, and then the projection returns the customer names.

Options B and D use selection where a projection of attributes is needed, so they are not valid. Option C reads from Borrow, which has no balance attribute.

Answer: D

The match is a-ii, b-iv, c-i, d-iii. A secondary index is commonly implemented as a B-tree (ii). A nonprocedural query language is the domain calculus (iv). The closure of a set of attributes is a concept of functional dependency (i). The natural join is a relational algebraic operation (iii).

The other codes pair the closure with the B-tree or the secondary index with functional dependency, which does not agree with how these terms are used.

Answer: B

In the relational model a relation on domains D1, D2, ..., Dn is a set of n-tuples, and that is by definition a subset of the Cartesian product D1 x D2 x ... x Dn, option (B). The product holds every possible combination of one value from each domain, and a table keeps only the combinations that are actually stored. A sum, a union or a difference of the domains is not a set of tuples: the union D1 U D2 U ... U Dn would be a set of single values and not of n-tuples, and the difference or sum has no meaning for combining domains into rows. So (A), (C) and (D) are wrong.