Database Design Using Normalization Questions and Answers

Q: One common design problem when designing a database from existing data is the use of a single cell in one column to store multiple values of an attribute.

Correct answer: TrueThis is the multivalue problem, and it is a first normal form violation: a cell must hold a single value. Several telephone numbers or skills packed into one cell cannot be searched, sorted or counted reliably, and the remedy is to move the repeating values into a separate table with one row for each.

Q: Most of the time, modification anomalies cause problems that are severe enough that a table should be normalized into BCNF.

Correct answer: TrueModification anomalies are not a theoretical nicety: they cause facts to be lost on deletion, facts to be unrecordable until unrelated ones are known, and the same value to be updated inconsistently in different rows. Normalizing to BCNF removes all of them, and that is why BCNF rather than 3NF is the working target.

Q: The phrase "SALE.CNumber must exist in CUSTOMER.CNumber" is a referential integrity constraint.

Correct answer: TrueA referential integrity constraint requires that every value appearing in a foreign key already exist as a key value in the referenced relation, which is exactly what this phrase states. It is enforced by the database rather than by the application, and it is what prevents a sale from referring to a customer who does not exist.

Q: Needing to using more complicated SQL in database applications is a(n) ________ of normalization.

Correct answer: disadvantageNormalization spreads data across more tables, so a query that once read a single table now needs joins and often subqueries. That extra complexity is a real cost - harder to write, harder to read and slower to run - and it is one of the two standard disadvantages set against the elimination of modification anomalies. It is why denormalization is sometimes chosen deliberately.

Q: Proper normalization eliminates duplicated data.

Correct answer: TrueA normalized design stores each fact in one place, which is what makes update anomalies impossible: there is no second copy to fall out of step. Foreign key values are repeated, but they are the mechanism by which relations are linked rather than duplicated data, and they are maintained by referential integrity rather than by hand.

Q: For a number of reasons, normalizations is not often an advantage for a(n) ________ database.

Correct answer: read-onlyNormalization exists to prevent modification anomalies, and a database that is never updated cannot suffer them. What remains is the cost: more tables, more joins, more complex and slower queries. So for a read-only database the balance tips the other way, and deliberately denormalized structures are the norm - which is exactly what a data warehouse star schema is.

Q: Normalization requires programmers to write more complex SQL.

Correct answer: TrueNormalization distributes data across more tables, so retrieving what was once a single row now requires joins, and more complex conditions and subqueries besides. This is one of the two standard disadvantages set against the elimination of modification anomalies, and it is the reason denormalization is sometimes chosen for read-only data.

Q: When you are given a set of tables and asked to create a database to store their data, the first step is to create the new database.

Correct answer: FalseCreating the database comes last. The first step is to examine what you have been given - count the rows, look at the columns and the data in them - then work out the dependencies and keys, then test the referential integrity constraints. Building the new database before understanding the old data means building the old problems into it.

Q: Surrogate keys have much meaning for users.

Correct answer: FalseA surrogate key is an artificial identifier - typically a system-generated integer - with no relationship to the data it identifies, which is exactly the point: it never changes, it is short, and it is guaranteed unique. The cost is that it means nothing to a user, so a meaningful candidate key usually has to be kept alongside it and given a unique constraint.

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

Correct answer: second step.Determining functional dependencies, primary keys and foreign keys all belong to the second step, in which the structure implied by the data is worked out. The first step is simply to see what is there - the row count and the columns - and the third is to test whether the referential integrity constraints implied by the foreign keys actually hold.

11.

One common design problem when designing a database from existing data is the use of a single cell in one column to store multiple values of an attribute.

A.True
B.False

Answer: B

Correct answer: True

This is the multivalue problem, and it is a first normal form violation: a cell must hold a single value. Several telephone numbers or skills packed into one cell cannot be searched, sorted or counted reliably, and the remedy is to move the repeating values into a separate table with one row for each.

Discuss in Forum

12.

Most of the time, modification anomalies cause problems that are severe enough that a table should be normalized into BCNF.

A.True
B.False

Answer: A

Correct answer: True

Modification anomalies are not a theoretical nicety: they cause facts to be lost on deletion, facts to be unrecordable until unrelated ones are known, and the same value to be updated inconsistently in different rows. Normalizing to BCNF removes all of them, and that is why BCNF rather than 3NF is the working target.

Discuss in Forum

13.

The phrase "SALE.CNumber must exist in CUSTOMER.CNumber" is a referential integrity constraint.

A.True
B.False

Answer: A

Correct answer: True

A referential integrity constraint requires that every value appearing in a foreign key already exist as a key value in the referenced relation, which is exactly what this phrase states. It is enforced by the database rather than by the application, and it is what prevents a sale from referring to a customer who does not exist.

Discuss in Forum

14.

Needing to using more complicated SQL in database applications is a(n) ________ of normalization.

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

Answer: B

Correct answer: disadvantage

Normalization spreads data across more tables, so a query that once read a single table now needs joins and often subqueries. That extra complexity is a real cost - harder to write, harder to read and slower to run - and it is one of the two standard disadvantages set against the elimination of modification anomalies. It is why denormalization is sometimes chosen deliberately.

Discuss in Forum

15.

Proper normalization eliminates duplicated data.

A.True
B.False

Answer: B

Correct answer: True

A normalized design stores each fact in one place, which is what makes update anomalies impossible: there is no second copy to fall out of step. Foreign key values are repeated, but they are the mechanism by which relations are linked rather than duplicated data, and they are maintained by referential integrity rather than by hand.

Discuss in Forum

16.

For a number of reasons, normalizations is not often an advantage for a(n) ________ database.

A.read-only
B.updateable
C.either a read-only or an updateable
D.None of the above is correct.

Answer: A

Correct answer: read-only

Normalization exists to prevent modification anomalies, and a database that is never updated cannot suffer them. What remains is the cost: more tables, more joins, more complex and slower queries. So for a read-only database the balance tips the other way, and deliberately denormalized structures are the norm - which is exactly what a data warehouse star schema is.

Discuss in Forum

17.

Normalization requires programmers to write more complex SQL.

A.True
B.False

Answer: A

Correct answer: True

Normalization distributes data across more tables, so retrieving what was once a single row now requires joins, and more complex conditions and subqueries besides. This is one of the two standard disadvantages set against the elimination of modification anomalies, and it is the reason denormalization is sometimes chosen for read-only data.

Discuss in Forum

18.

When you are given a set of tables and asked to create a database to store their data, the first step is to create the new database.

A.True
B.False

Answer: B

Correct answer: False

Creating the database comes last. The first step is to examine what you have been given - count the rows, look at the columns and the data in them - then work out the dependencies and keys, then test the referential integrity constraints. Building the new database before understanding the old data means building the old problems into it.

Discuss in Forum

19.

Surrogate keys have much meaning for users.

A.True
B.False

Answer: B

Correct answer: False

A surrogate key is an artificial identifier - typically a system-generated integer - with no relationship to the data it identifies, which is exactly the point: it never changes, it is short, and it is guaranteed unique. The cost is that it means nothing to a user, so a meaningful candidate key usually has to be kept alongside it and given a unique constraint.

Discuss in Forum

20.

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

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

Answer: B

Correct answer: second step.

Determining functional dependencies, primary keys and foreign keys all belong to the second step, in which the structure implied by the data is worked out. The first step is simply to see what is there - the row count and the columns - and the third is to test whether the referential integrity constraints implied by the foreign keys actually hold.

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