Physical Database Design Questions and Answers

Q: A multidimensional database model is used most often in which of the following models?

Correct answer: Data warehouseThe multidimensional model organises data as measures indexed by dimensions - sales by product by region by month - which is exactly how analytical questions are asked. It suits the warehouse for that reason, and suits transaction processing badly, since it is designed to be read rather than updated.

Q: It is usually not very important to design the physical database to minimize the time required by users to interact with the information systems.

Correct answer: FalseResponse time as the user experiences it is very nearly the point of physical design. The logical design is already correct; what physical design adds is the indexing, organisation and placement that decide whether the system is usable, and user interaction time is the measure that matters most.

Q: Which of the following improves a query's processing time?

Correct answer: Use compatible data types.Comparing columns of different types forces the database to convert one of them, and a converted column generally cannot use its index - so a query that should have been an index lookup becomes a full scan. The other three options all add work: complexity, self-joins and subqueries each give the optimiser more to do rather than less.

Q: A bitmap index is an index on columns from two or more tables that come from the same domain of values

Correct answer: FalseAn index on columns from two or more tables drawn from the same domain is a join index, which exists to avoid computing the join at query time. A bitmap index is something else: one bit vector per distinct value, which is compact and fast where a column has few distinct values.

Q: Which of the following is an advantage of partitioning?

Correct answer: SecurityPartitioning lets the sensitive rows or columns be placed in their own partition with its own access controls, so a user reaching one part of a table need not be given the whole of it. The other three options are the recognised disadvantages - more complexity, uneven access speed, and extra space for the partitioning structures.

Q: In general, larger block sizes are used for online transaction processing applications and smaller block sizes are used for databases with a decision support or data warehousing system.

Correct answer: FalseThe sizes are the other way round. Transaction processing touches few rows at a time and benefits from small blocks, which reduce contention and wasted transfer. Warehousing scans large ranges, so large blocks bring more useful data per read. The statement has the two applications exchanged.

Q: Selecting a data type involves which of the following?

Correct answer: Improve data integrityChoosing a type that admits only the values the attribute can legitimately take is itself a constraint: a date column will not accept a non-date, and a numeric one will not accept text. The other options are the reverse of what is wanted - storage should be minimised, and a type admitting the widest range of values weakens the check.

Q: The primary goal of physical database design is data processing efficiency.

Correct answer: TruePhysical design begins from a logical design that is already correct, so what remains to be decided is how to make it perform: indexes, file organisation, clustering, partitioning and controlled denormalisation. Correctness is settled by then; efficiency is what physical design trades against.

Q: An extent is a contiguous section of disk storage space.

Correct answer: TrueAn extent is a contiguous run of disk space allocated to a segment in one go. Allocating in extents rather than block by block keeps related data physically together, which matters for sequential reading, and reduces the bookkeeping the storage manager must do.

31.

A multidimensional database model is used most often in which of the following models?

A.Data warehouse
B.Relational
C.Hierarchical
D.Network

Answer: A

Correct answer: Data warehouse

The multidimensional model organises data as measures indexed by dimensions - sales by product by region by month - which is exactly how analytical questions are asked. It suits the warehouse for that reason, and suits transaction processing badly, since it is designed to be read rather than updated.

Discuss in Forum

32.

It is usually not very important to design the physical database to minimize the time required by users to interact with the information systems.

A.True
B.False

Answer: B

Correct answer: False

Response time as the user experiences it is very nearly the point of physical design. The logical design is already correct; what physical design adds is the indexing, organisation and placement that decide whether the system is usable, and user interaction time is the measure that matters most.

Discuss in Forum

33.

Which of the following improves a query's processing time?

A.Write complex queries.
B.Combine a table with itself.
C.Query one query within another.
D.Use compatible data types.

Answer: D

Correct answer: Use compatible data types.

Comparing columns of different types forces the database to convert one of them, and a converted column generally cannot use its index - so a query that should have been an index lookup becomes a full scan. The other three options all add work: complexity, self-joins and subqueries each give the optimiser more to do rather than less.

Discuss in Forum

34.

A bitmap index is an index on columns from two or more tables that come from the same domain of values

A.True
B.False

Answer: B

Correct answer: False

An index on columns from two or more tables drawn from the same domain is a join index, which exists to avoid computing the join at query time. A bitmap index is something else: one bit vector per distinct value, which is compact and fast where a column has few distinct values.

Discuss in Forum

35.

Which of the following is an advantage of partitioning?

A.Complexity
B.Inconsistent access speed
C.Extra space
D.Security

Answer: D

Correct answer: Security

Partitioning lets the sensitive rows or columns be placed in their own partition with its own access controls, so a user reaching one part of a table need not be given the whole of it. The other three options are the recognised disadvantages - more complexity, uneven access speed, and extra space for the partitioning structures.

Discuss in Forum

36.

In general, larger block sizes are used for online transaction processing applications and smaller block sizes are used for databases with a decision support or data warehousing system.

A.True
B.False

Answer: B

Correct answer: False

The sizes are the other way round. Transaction processing touches few rows at a time and benefits from small blocks, which reduce contention and wasted transfer. Warehousing scans large ranges, so large blocks bring more useful data per read. The statement has the two applications exchanged.

Discuss in Forum

37.

Selecting a data type involves which of the following?

A.Maximize storage space
B.Represent most values
C.Improve data integrity
D.All of the above.

Answer: C

Correct answer: Improve data integrity

Choosing a type that admits only the values the attribute can legitimately take is itself a constraint: a date column will not accept a non-date, and a numeric one will not accept text. The other options are the reverse of what is wanted - storage should be minimised, and a type admitting the widest range of values weakens the check.

Discuss in Forum

38.

The primary goal of physical database design is data processing efficiency.

A.True
B.False

Answer: A

Correct answer: True

Physical design begins from a logical design that is already correct, so what remains to be decided is how to make it perform: indexes, file organisation, clustering, partitioning and controlled denormalisation. Correctness is settled by then; efficiency is what physical design trades against.

Discuss in Forum

39.

An extent is a contiguous section of disk storage space.

A.True
B.False

Answer: A

Correct answer: True

An extent is a contiguous run of disk space allocated to a segment in one go. Allocating in extents rather than block by block keeps related data physically together, which matters for sequential reading, and reduces the bookkeeping the storage manager must do.

Discuss in Forum

Loading…

Topics

Other Subjects

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

Physical Database Design Questions and Answers

Topics

Other Subjects

About Us

Stay informed about all important Dates & Notifications