Physical Database Design Questions and Answers

Q: If a denormalization situation exists with a many-to-many or associative binary relationship, which of the following is true?

Correct answer: All fields are stored in two relations.A many-to-many relationship denormalises into the associative relation and one of the participants, giving two relations rather than three. It cannot be collapsed into one, because doing so would repeat every attribute of one participant for each of its associations - which is the redundancy normalisation exists to prevent.

Q: Which of the following is not a factor to consider when switching from small to large block size?

Correct answer: The number of columnsBlock size decisions turn on how much data a read brings in and how many rows share a block, so row length, block contention and random access speed all bear on it. The number of columns matters only through its effect on the row length, and so is not a separate factor.

Q: What is the best data type definition for Oracle when a field is alphanumeric and has a length that can vary?

Correct answer: VARCHAR2VARCHAR2 stores only the characters actually supplied, so a column declared long enough for the worst case costs nothing for the shorter values. CHAR pads every value to the full declared width, which wastes space and can complicate comparison when the data varies in length.

Q: The blocking factor is:

Correct answer: the number of physical records per page.The blocking factor is how many records fit in one block or page, and it follows from the record length and the block size. It governs how many records one read brings in, which is why sequential processing is faster with a high blocking factor and why block size is a physical design decision.

Q: Denormalization and clustering can work well to minimize data access time for small records.

Correct answer: TrueClustering places records that are read together on the same page, so one physical read brings in all of them, and denormalisation removes the join that would otherwise have been needed. Both work best on small records, since more of them fit in a block and the saving per read is correspondingly greater.

Q: When storage space is scarce and physical records cannot span pages, creating multiple physical records from one logical relation will minimize wasted storage space.

Correct answer: TrueWhere a record cannot span a page, any space left at the end of a page after the last whole record is wasted. Splitting the relation into several shorter physical records lets more of them fit, so less is left over. It is a genuine trade - the retrieval now needs more than one record - and it is worth making only when space is the binding constraint.

Q: Distributing the columns of a table into several separate physical records is known as horizontal partitioning.

Correct answer: FalseDistributing the columns of a table is vertical partitioning. Horizontal partitioning distributes the rows, typically by a value such as region or date, so each partition holds complete records for part of the population. The statement has given the definition of one under the name of the other.

Q: If a denormalization situation exists with a one-to-one binary relationship, which of the following is true?

Correct answer: All fields are stored in one relation.In a one-to-one relationship each row on either side matches at most one on the other, so the two relations can be merged with no duplication of any value. That is why this is the safest case for denormalisation - the join is removed and nothing is repeated in exchange.

Q: A field represents each component of a composite attribute.

Correct answer: TrueA composite attribute is stored as one field per component - street, town and postcode rather than a single address field - whenever the parts will be used separately. Storing it whole is the alternative, and the choice turns on whether anything will ever need to search or sort on a part.

Q: Which of the following are integrity controls that a DBMS may support?

Correct answer: All of the above.All three are integrity controls the DBMS itself can enforce: a default supplied when no value is given, a domain constraint limiting the permissible values, and a not-null constraint requiring one. Placing them in the database rather than in each application means every route to the data is checked identically.

21.

If a denormalization situation exists with a many-to-many or associative binary relationship, which of the following is true?

A.All fields are stored in one relation.
B.All fields are stored in two relations.
C.All fields are stored in four relations.
D.All fields are stored in three relations.

Answer: B

Correct answer: All fields are stored in two relations.

A many-to-many relationship denormalises into the associative relation and one of the participants, giving two relations rather than three. It cannot be collapsed into one, because doing so would repeat every attribute of one participant for each of its associations - which is the redundancy normalisation exists to prevent.

Discuss in Forum

22.

Which of the following is not a factor to consider when switching from small to large block size?

A.The length of all of the fields in a table row.
B.The number of columns
C.Block contention
D.Random row access speed

Answer: B

Correct answer: The number of columns

Block size decisions turn on how much data a read brings in and how many rows share a block, so row length, block contention and random access speed all bear on it. The number of columns matters only through its effect on the row length, and so is not a separate factor.

Discuss in Forum

23.

What is the best data type definition for Oracle when a field is alphanumeric and has a length that can vary?

A.VARCHAR2
B.CHAR
C.LONG
D.NUMBER

Answer: A

Correct answer: VARCHAR2

VARCHAR2 stores only the characters actually supplied, so a column declared long enough for the worst case costs nothing for the shorter values. CHAR pads every value to the full declared width, which wastes space and can complicate comparison when the data varies in length.

Discuss in Forum

24.

The blocking factor is:

A.a group of fields stored in adjacent memory.
B.the number of physical records per page.
C.attributes grouped together by the same primary key.
D.attributes grouped together by the same secondary key.

Answer: B

Correct answer: the number of physical records per page.

The blocking factor is how many records fit in one block or page, and it follows from the record length and the block size. It governs how many records one read brings in, which is why sequential processing is faster with a high blocking factor and why block size is a physical design decision.

Discuss in Forum

25.

Denormalization and clustering can work well to minimize data access time for small records.

A.True
B.False

Answer: A

Correct answer: True

Clustering places records that are read together on the same page, so one physical read brings in all of them, and denormalisation removes the join that would otherwise have been needed. Both work best on small records, since more of them fit in a block and the saving per read is correspondingly greater.

Discuss in Forum

26.

When storage space is scarce and physical records cannot span pages, creating multiple physical records from one logical relation will minimize wasted storage space.

A.True
B.False

Answer: A

Correct answer: True

Where a record cannot span a page, any space left at the end of a page after the last whole record is wasted. Splitting the relation into several shorter physical records lets more of them fit, so less is left over. It is a genuine trade - the retrieval now needs more than one record - and it is worth making only when space is the binding constraint.

Discuss in Forum

27.

Distributing the columns of a table into several separate physical records is known as horizontal partitioning.

A.True
B.False

Answer: B

Correct answer: False

Distributing the columns of a table is vertical partitioning. Horizontal partitioning distributes the rows, typically by a value such as region or date, so each partition holds complete records for part of the population. The statement has given the definition of one under the name of the other.

Discuss in Forum

28.

If a denormalization situation exists with a one-to-one binary relationship, which of the following is true?

A.All fields are stored in one relation.
B.All fields are stored in two relations.
C.All fields are stored in three relations.
D.All fields are stored in four relations.

Answer: A

Correct answer: All fields are stored in one relation.

In a one-to-one relationship each row on either side matches at most one on the other, so the two relations can be merged with no duplication of any value. That is why this is the safest case for denormalisation - the join is removed and nothing is repeated in exchange.

Discuss in Forum

29.

A field represents each component of a composite attribute.

A.True
B.False

Answer: A

Correct answer: True

A composite attribute is stored as one field per component - street, town and postcode rather than a single address field - whenever the parts will be used separately. Storing it whole is the alternative, and the choice turns on whether anything will ever need to search or sort on a part.

Discuss in Forum

30.

Which of the following are integrity controls that a DBMS may support?

A.Assume a default value in a field unless a user enters a value for that field.
B.Limit the set of permissible values that a field may assume.
C.Limit the use of null values in some fields.
D.All of the above.

Answer: D

Correct answer: All of the above.

All three are integrity controls the DBMS itself can enforce: a default supplied when no value is given, a domain constraint limiting the permissible values, and a not-null constraint requiring one. Placing them in the database rather than in each application means every route to the data is checked identically.

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