Database Design Using Normalization Questions and Answers

Q: When building a database from an existing set of tables, we still need to consider normalization principles.

Correct answer: TrueThe tables you are given were designed by someone else, often for a different purpose, and may carry every problem normalization exists to prevent. Understanding their dependencies and deciding what to normalize is part of building a sound database from them - and even where you choose to leave a table denormalized, that should be a decision rather than an oversight.

Q: Needing to assess the validity of assumed referential integrity constraints on foreign keys is a(n) ________ of normalization.

Correct answer: disadvantageNormalization decomposes one relation into several linked by foreign keys, and every foreign key brings a referential integrity constraint that must be assessed and then enforced. That work would not exist without the decomposition, so it counts as a cost of normalizing - the second of the two standard disadvantages, alongside the more complicated SQL that the extra tables require.

Q: In all cases, normalization into BCNF is desirable.

Correct answer: FalseNormalization removes modification anomalies at the cost of more tables, more joins and more complex SQL, and where anomalies cannot arise the cost buys nothing. A read-only database is the standard case, and reporting and warehouse designs are deliberately denormalized. BCNF is the usual target for an updateable database, not a universal rule.

Q: Multivalued dependencies create harmless anomalies that should be noted, but do not always need to be eliminated.

Correct answer: FalseMultivalued dependency anomalies are not harmless. Two independent multivalued facts in one relation force every combination of them to be stored, so adding one value of the first requires a new row for every existing value of the second, and the redundancy grows as their product. Splitting the relation removes the problem entirely.

Q: We have normalized a table into BCNF if all candidate keys are determinants.

Correct answer: FalseThe condition runs the other way: BCNF requires every determinant to be a candidate key. Candidate keys are always determinants in any relation, since identifying a row fixes all its attributes, so the statement as written is true of every relation and tests nothing. The requirement that bites is that no determinant may be anything other than a candidate key.

Q: Read-only databases are ________ updated.

Correct answer: neverA read-only database is by definition never updated after it is loaded, which is what changes its design priorities: modification anomalies cannot occur, so normalization loses its purpose and denormalized structures may be preferred for query speed. Data is refreshed by reloading rather than by updating in place.

Q: If a table has been normalized so that all determinants are candidate keys, then that table is in:

Correct answer: BCNF.This is the definition of Boyce-Codd Normal Form: every determinant - every left-hand side of a functional dependency - must be a candidate key. A determinant that is not a key allows the same fact to be repeated across many rows, which is precisely what produces update anomalies. Note the direction: all determinants must be candidate keys, not the reverse.

Q: When assessing the table structure of an acquired set of tables with data, determining primary keys is (part of) the:

Correct answer: second step.Determining the primary keys belongs to the second step, along with the functional dependencies from which they follow and the foreign keys that depend on them. The row and column survey comes first, and testing whether the referential integrity constraints implied by those foreign keys actually hold comes third.

21.

When building a database from an existing set of tables, we still need to consider normalization principles.

A.True
B.False

Answer: A

Correct answer: True

The tables you are given were designed by someone else, often for a different purpose, and may carry every problem normalization exists to prevent. Understanding their dependencies and deciding what to normalize is part of building a sound database from them - and even where you choose to leave a table denormalized, that should be a decision rather than an oversight.

Discuss in Forum

22.

Needing to assess the validity of assumed referential integrity constraints on foreign keys is a(n) ________ of normalization.

A.advantage
B.disadvantage
C.either an advantage or disadvantage
D.neither an advantage nor disadvantage

Answer: D

Correct answer: disadvantage

Normalization decomposes one relation into several linked by foreign keys, and every foreign key brings a referential integrity constraint that must be assessed and then enforced. That work would not exist without the decomposition, so it counts as a cost of normalizing - the second of the two standard disadvantages, alongside the more complicated SQL that the extra tables require.

Discuss in Forum

23.

In all cases, normalization into BCNF is desirable.

A.True
B.False

Answer: B

Correct answer: False

Normalization removes modification anomalies at the cost of more tables, more joins and more complex SQL, and where anomalies cannot arise the cost buys nothing. A read-only database is the standard case, and reporting and warehouse designs are deliberately denormalized. BCNF is the usual target for an updateable database, not a universal rule.

Discuss in Forum

24.

Multivalued dependencies create harmless anomalies that should be noted, but do not always need to be eliminated.

A.True
B.False

Answer: B

Correct answer: False

Multivalued dependency anomalies are not harmless. Two independent multivalued facts in one relation force every combination of them to be stored, so adding one value of the first requires a new row for every existing value of the second, and the redundancy grows as their product. Splitting the relation removes the problem entirely.

Discuss in Forum

25.

Each answer below shows example data from a table. Which answer is an example of the missing values problem?

A.Three columns have the values 534-2435, 534-7867, and 546-2356 in the same row.
B.Three rows have the values Brown Small Chair, Small Chair Brown, and Small Brown Chair in the same column.
C.Three rows have the values Brown, NULL, and Blue in the same column.
D.One row has the value "He is interested in a Silver Porsche from the years 1978-1988" in a column.

Answer: C

Correct answer: Three rows have the values Brown, NULL, and Blue in the same column.

The missing values problem is the presence of nulls, and the difficulty is that a null is ambiguous: it may mean the value is unknown, or that it does not apply, or that nobody has entered it yet. Since the three call for different treatment and the data does not distinguish them, every null has to be investigated rather than assumed.

Discuss in Forum

26.

We have normalized a table into BCNF if all candidate keys are determinants.

A.True
B.False

Answer: B

Correct answer: False

The condition runs the other way: BCNF requires every determinant to be a candidate key. Candidate keys are always determinants in any relation, since identifying a row fixes all its attributes, so the statement as written is true of every relation and tests nothing. The requirement that bites is that no determinant may be anything other than a candidate key.

Discuss in Forum

27.

Read-only databases are ________ updated.

A.always
B.commonly
C.seldom
D.never

Answer: D

Correct answer: never

A read-only database is by definition never updated after it is loaded, which is what changes its design priorities: modification anomalies cannot occur, so normalization loses its purpose and denormalized structures may be preferred for query speed. Data is refreshed by reloading rather than by updating in place.

Discuss in Forum

28.

If a table has been normalized so that all determinants are candidate keys, then that table is in:

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

Answer: D

Correct answer: BCNF.

This is the definition of Boyce-Codd Normal Form: every determinant - every left-hand side of a functional dependency - must be a candidate key. A determinant that is not a key allows the same fact to be repeated across many rows, which is precisely what produces update anomalies. Note the direction: all determinants must be candidate keys, not the reverse.

Discuss in Forum

29.

When assessing the table structure of an acquired set of tables with data, determining primary keys is (part of) the:

A.first step.
B.second step.
C.third step.
D.fourth step.

Answer: B

Correct answer: second step.

Determining the primary keys belongs to the second step, along with the functional dependencies from which they follow and the foreign keys that depend on them. The row and column survey comes first, and testing whether the referential integrity constraints implied by those foreign keys actually hold comes third.

Discuss in Forum

30.

Each answer below shows example data from a table. Which answer is an example of the general-purpose remarks column problem?

A.Three columns have the values 534-2435, 534-7867, and 546-2356 in the same row.
B.Three rows have the values Brown Small Chair, Small Chair Brown, and Small Brown Chair in the same column.
C.Three rows have the values Brown, NULL, and Blue in the same column.
D.One row has the value "He is interested in a Silver Porsche from the years 1978-1988" in a column.

Answer: D

Correct answer: One row has the value "He is interested in a Silver Porsche from the years 1978-1988" in a column.

A general-purpose remarks column holds free text into which users put whatever they think worth recording, so the same column contains different kinds of fact in different rows. It cannot be queried reliably, cannot be validated, and the information in it - here a colour, a model and a year range - belongs in columns of its own.

Discuss in Forum

Loading…

Topics

Other Subjects

Tags: Database Design Using Normalization Questions and Answers || Database Design Using Normalization MCQ Questions and Answers || Database Design Using Normalization GK Questions and Answers || Database Design Using Normalization GK MCQ Questions || Database Design Using Normalization Multiple Choice Questions and Answers || Database Design Using Normalization GK || GK on Database Design Using Normalization || Database Questions and Answers || Database MCQ Questions and Answers || Database GK Questions and Answers || GK on Database

Database Design Using Normalization Questions and Answers

Topics

Other Subjects

About Us

Stay informed about all important Dates & Notifications