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: 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.

Answer: A

Correct answer: Key

A key is one or more attributes whose values uniquely identify a row. A determinant is the left-hand side of a functional dependency and need not be unique - every key is a determinant, but not every determinant is a key, and that asymmetry is exactly what Boyce-Codd Normal Form addresses. A tuple is a row and a relation a table.

Answer: B

Correct answer: False

A primary key is chosen from among the candidate keys, and there is no requirement that it be composite - most primary keys are single attributes, and many are deliberately introduced surrogate keys for exactly that reason. A composite key may be selected as the primary key, but that is a possibility rather than part of the definition.