Database Redesign Questions and Answers

Q: A regular subquery can be processed:

Correct answer: from the bottom up.A regular subquery does not depend on the outer query, so it can be evaluated once, on its own, and its result then used by the query above it - bottom up. A correlated subquery references a column of the outer query and must therefore be re-evaluated for each candidate row, which is why it is described as nested and why it is generally the more expensive of the two.

Q: Changing cardinalities in a database is:

Correct answer: a common database design task.Cardinalities change because requirements change - a one-to-many relationship becomes many-to-many when the business decides an item may belong to more than one category, or a maximum cardinality of one becomes optional. Such changes are routine, and they are handled by adding or removing an intersection table, or by altering a foreign key and its constraints, not by rebuilding the database.

Q: Information systems and organizations do not just influence each other; they create each other.

Correct answer: TrueThe relationship runs both ways. An organisation’s structure and processes shape the systems built for it, and those systems then constrain and reshape how the organisation works - which is why a database redesign is rarely a purely technical exercise and why the organisation often has to change alongside it.

Q: A tool that can help designers understand the dependencies of database structures is a:

Correct answer: dependency graph.A dependency graph shows the database’s structures as nodes and their dependencies as arcs, so that before changing anything the designer can see what refers to it - the views, constraints, triggers, stored procedures and application queries that would break. It is the tool that turns “what does this column touch?” from a search into a reading.

Q: Which SQL command removes a table together with all its data and its definition ?

Correct answer: DROP TABLE STUDENTDROP TABLE deletes the rows and the table structure itself. DELETE removes rows subject to a WHERE clause and TRUNCATE removes all rows quickly, but both leave the empty table in place.

Q: What SQL command can be used to add columns to a table?

Correct answer: ALTER TABLE TableName ADD ColumnNameAdding a column is done with ALTER TABLE, and in most implementations the keyword COLUMN is optional on the ADD clause though required on DROP COLUMN. MODIFY TABLE is not a command in any dialect. Note also that a column added with a NOT NULL constraint and no default will fail on a table that already contains rows.

Q: The data model produced from the reverse engineering process is a true logical model.

Correct answer: FalseThe model produced is not a true logical model. It reflects the schema as built, including denormalisations, physical compromises and implementation artefacts, and it cannot recover the business meaning that was never recorded in the schema - intersection tables appear as ordinary entities, and optionality and cardinality often cannot be inferred. It is a starting point requiring interpretation, not a finished model.

Q: Correlated subqueries are similar to a regular subquery.

Correct answer: FalseThe two are quite different. A regular subquery is independent and evaluated once; a correlated subquery references the outer query and must be re-evaluated for every candidate row. The difference shows in the processing order, in the cost, and in what each can express - only the correlated form can compare a row against others in the same table.

Q: Which of the following SQL statements are helpful in database redesign?

Correct answer: Both of the above are helpfulBoth belong to the same family and both are used in redesign. A correlated subquery is evaluated once for each candidate row of the outer query, which is what lets it compare a row against others in the same table - the way a functional dependency is tested. EXISTS and NOT EXISTS are the standard form such a subquery takes, since they ask only whether any matching row exists rather than what it contains.

Q: Because of the need for extensive knowledge of SQL, many database developers choose to automate the redesign process.

Correct answer: FalseThe extensive SQL knowledge required is a reason for caution rather than for automation. A generated script may be syntactically correct and still lose data or violate a business rule the tool knows nothing about, and against live data such a mistake may be irreversible. Developers therefore use tools to draft changes and then review and test them by hand.

31.

A regular subquery can be processed:

A.from the top down.
B.from the bottom up.
C.by nesting.
D.None of the above is correct.

Answer: B

Correct answer: from the bottom up.

A regular subquery does not depend on the outer query, so it can be evaluated once, on its own, and its result then used by the query above it - bottom up. A correlated subquery references a column of the outer query and must therefore be re-evaluated for each candidate row, which is why it is described as nested and why it is generally the more expensive of the two.

Discuss in Forum

32.

Changing cardinalities in a database is:

A.a common database design task.
B.a rare database design task, but does occur.
C.a database design task that never occurs.
D.is impossible to do, so a new database must be constructed and the data moved into it.

Answer: A

Correct answer: a common database design task.

Cardinalities change because requirements change - a one-to-many relationship becomes many-to-many when the business decides an item may belong to more than one category, or a maximum cardinality of one becomes optional. Such changes are routine, and they are handled by adding or removing an intersection table, or by altering a foreign key and its constraints, not by rebuilding the database.

Discuss in Forum

33.

Information systems and organizations do not just influence each other; they create each other.

A.True
B.False

Answer: A

Correct answer: True

The relationship runs both ways. An organisation’s structure and processes shape the systems built for it, and those systems then constrain and reshape how the organisation works - which is why a database redesign is rarely a purely technical exercise and why the organisation often has to change alongside it.

Discuss in Forum

34.

A tool that can help designers understand the dependencies of database structures is a:

A.dependency graph.
B.data model.
C.graphical display.
D.None of the above is correct.

Answer: A

Correct answer: dependency graph.

A dependency graph shows the database’s structures as nodes and their dependencies as arcs, so that before changing anything the designer can see what refers to it - the views, constraints, triggers, stored procedures and application queries that would break. It is the tool that turns “what does this column touch?” from a search into a reading.

Discuss in Forum

35.

Which SQL command removes a table together with all its data and its definition ?

A.DELETE FROM STUDENT
B.TRUNCATE TABLE STUDENT
C.DROP TABLE STUDENT
D.REMOVE TABLE STUDENT

Answer: C

Correct answer: DROP TABLE STUDENT

DROP TABLE deletes the rows and the table structure itself. DELETE removes rows subject to a WHERE clause and TRUNCATE removes all rows quickly, but both leave the empty table in place.

Discuss in Forum

36.

What SQL command can be used to add columns to a table?

A.MODIFY TABLE TableName ADD COLUMN ColumnName
B.MODIFY TABLE TableName ADD ColumnName
C.ALTER TABLE TableName ADD COLUMN ColumnName
D.ALTER TABLE TableName ADD ColumnName

Answer: D

Correct answer: ALTER TABLE TableName ADD ColumnName

Adding a column is done with ALTER TABLE, and in most implementations the keyword COLUMN is optional on the ADD clause though required on DROP COLUMN. MODIFY TABLE is not a command in any dialect. Note also that a column added with a NOT NULL constraint and no default will fail on a table that already contains rows.

Discuss in Forum

37.

The data model produced from the reverse engineering process is a true logical model.

A.True
B.False

Answer: B

Correct answer: False

The model produced is not a true logical model. It reflects the schema as built, including denormalisations, physical compromises and implementation artefacts, and it cannot recover the business meaning that was never recorded in the schema - intersection tables appear as ordinary entities, and optionality and cardinality often cannot be inferred. It is a starting point requiring interpretation, not a finished model.

Discuss in Forum

38.

Correlated subqueries are similar to a regular subquery.

A.True
B.False

Answer: B

Correct answer: False

The two are quite different. A regular subquery is independent and evaluated once; a correlated subquery references the outer query and must be re-evaluated for every candidate row. The difference shows in the processing order, in the cost, and in what each can express - only the correlated form can compare a row against others in the same table.

Discuss in Forum

39.

Which of the following SQL statements are helpful in database redesign?

A.Correlated subqueries only
B.EXISTS/NOT EXISTS expressions only
C.Both of the above are helpful
D.None of the above are helpful.

Answer: C

Correct answer: Both of the above are helpful

Both belong to the same family and both are used in redesign. A correlated subquery is evaluated once for each candidate row of the outer query, which is what lets it compare a row against others in the same table - the way a functional dependency is tested. EXISTS and NOT EXISTS are the standard form such a subquery takes, since they ask only whether any matching row exists rather than what it contains.

Discuss in Forum

40.

Because of the need for extensive knowledge of SQL, many database developers choose to automate the redesign process.

A.True
B.False

Answer: B

Correct answer: False

The extensive SQL knowledge required is a reason for caution rather than for automation. A generated script may be syntactically correct and still lose data or violate a business rule the tool knows nothing about, and against live data such a mistake may be irreversible. Developers therefore use tools to draft changes and then review and test them by hand.

Discuss in Forum

Loading…

Topics

Other Subjects

Tags: Database Redesign Questions and Answers || Database Redesign MCQ Questions and Answers || Database Redesign GK Questions and Answers || Database Redesign GK MCQ Questions || Database Redesign Multiple Choice Questions and Answers || Database Redesign GK || GK on Database Redesign || Database Questions and Answers || Database MCQ Questions and Answers || Database GK Questions and Answers || GK on Database

Database Redesign Questions and Answers

Topics

Other Subjects

About Us

Stay informed about all important Dates & Notifications