Physical Database Design Questions and Answers

Q: B+ tree are preferred to binary tree in Database because

B+ trees are preferred because disk access is much slower than memory access.A B+ tree node is made as large as a disk block and holds many keys, so the tree is shallow, and a search reads only a few blocks. Since each disk read costs much more than a memory access, reducing the number of reads is what counts, and a binary tree, with one key per node, would need many more. Greater disk capacity (A), lower transfer rates (C) and reliability (D) are facts or claims that do not bear on the choice of a tree with a large fan-out.

Q: A secondary key is which of the following?

Correct answer: Nonunique keyA secondary key identifies a set of rows rather than a single one - all customers in a city, all orders on a date - and is used to speed retrieval on a common search condition. It is a candidate for an index precisely because it is not unique, and it plays no part in identifying rows.

Q: Sequential retrieval on a primary key for sequential file storage has which of the following features?

Correct answer: Very fastSequential file storage keeps the rows in primary key order, so reading them in that order is simply reading the file from start to finish - no index lookups and no seeking. That is what the organisation is for, and it is the reason it survives for batch processing despite being poor at everything else.

Q: The fastest read/write time and most efficient data storage of any disk array type is:

Correct answer: RAID-0.RAID-0 stripes data across the disks with no redundancy at all, so every byte of capacity holds data and reads and writes are spread over all the spindles. That gives the best speed and the most efficient storage of any array type - and no protection whatever, since the failure of one disk loses the whole array.

Q: Some advantages of partitioning include: efficiency, security, and load balancing.

Correct answer: TrueAll three are among the recognised advantages, along with local optimisation and recovery. Efficiency comes from a query reading only the partition it needs; security from applying different controls to different partitions; and load balancing from placing partitions on separate devices so their accesses do not contend.

Q: Data-volume and frequency-of-use statistics are not critical inputs to the physical database design process.

Correct answer: FalseData volumes and access frequencies are among the most critical inputs to physical design: they decide which tables are large enough to warrant indexing, which queries run often enough to be worth optimising for, and where the storage and contention will fall. Without them the design decisions are guesses.

Q: Two basic constructs to link one piece of data with another piece of data: sequential storage and pointers.

Correct answer: TrueEvery physical structure reduces to one of the two. Sequential storage links records by placing them next to each other, so the next record is found by continuing to read. Pointers link them by holding an address, so the next record can be anywhere. Indexes, hashing and chains are all built from these two ideas.

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

Correct answer: CHARCHAR is fixed width and pads every value to the declared length, which is exactly right for data that genuinely has one length - a two-character state code, a fixed-format reference. For anything varying, the padding is waste, and VARCHAR2 is the correct choice instead.

11.

B+ tree are preferred to binary tree in Database because

A.Disk capacity are greater than memory capacities
B.Disk access is much slower than memory access
C.Disk data transfer rates are much less than memory data transfer rate
D.Disks are more reliable than memory

Exam Year(s)

UGC NET - 2012,

Answer: B

B+ trees are preferred because disk access is much slower than memory access.

A B+ tree node is made as large as a disk block and holds many keys, so the tree is shallow, and a search reads only a few blocks. Since each disk read costs much more than a memory access, reducing the number of reads is what counts, and a binary tree, with one key per node, would need many more. Greater disk capacity (A), lower transfer rates (C) and reliability (D) are facts or claims that do not bear on the choice of a tree with a large fan-out.

Discuss in Forum

12.

A clustering index is created when _______.

A.primary key is declared and ordered
B.no key ordered
C.foreign key ordered
D.there is no key and no order

Exam Year(s)

UGC NET - 2014,

Answer: A

A clustering index is created when the primary key is declared and the file is ordered on it. A clustering index is built on an ordering field of the file, so the records are physically sorted on the key, and when that field is the primary key the index is of this kind. If no key is ordered, or there is no key and no order, there is nothing to cluster on, and the foreign key is not the ordering field of the file. The index is called clustering because records with the same or neighbouring key values are kept together on the disk.

Discuss in Forum

13.

A secondary key is which of the following?

A.Nonunique key
B.Primary key
C.Useful for denormalization decisions
D.Determines the tablespace required

Answer: A

Correct answer: Nonunique key

A secondary key identifies a set of rows rather than a single one - all customers in a city, all orders on a date - and is used to speed retrieval on a common search condition. It is a candidate for an index precisely because it is not unique, and it plays no part in identifying rows.

Discuss in Forum

14.

Sequential retrieval on a primary key for sequential file storage has which of the following features?

A.Very fast
B.Moderately fast
C.Slow
D.Impractical

Answer: A

Correct answer: Very fast

Sequential file storage keeps the rows in primary key order, so reading them in that order is simply reading the file from start to finish - no index lookups and no seeking. That is what the organisation is for, and it is the reason it survives for batch processing despite being poor at everything else.

Discuss in Forum

15.

The fastest read/write time and most efficient data storage of any disk array type is:

A.RAID-0.
B.RAID-1.
C.RAID-2.
D.RAID-3.

Answer: A

Correct answer: RAID-0.

RAID-0 stripes data across the disks with no redundancy at all, so every byte of capacity holds data and reads and writes are spread over all the spindles. That gives the best speed and the most efficient storage of any array type - and no protection whatever, since the failure of one disk loses the whole array.

Discuss in Forum

16.

A rule of thumb for choosing indexes for a relational database includes which of the following?

A.Indexes are more useful on smaller tables.
B.Indexes are more useful for columns that do not appear frequently in the WHERE clause in queries.
C.Do not specify a unique index for the primary key of each table.
D.Be careful indexing attributes that have null values.

Answer: D

Correct answer: Be careful indexing attributes that have null values.

Nulls complicate an index, since how they are stored and whether they can be searched for varies between products, and a column with many of them may not benefit from indexing at all. The other three rules are each stated backwards: indexes help most on large tables, on columns that do appear in WHERE clauses, and a unique index on the primary key is essential rather than avoidable.

Discuss in Forum

17.

Some advantages of partitioning include: efficiency, security, and load balancing.

A.True
B.False

Answer: B

Correct answer: True

All three are among the recognised advantages, along with local optimisation and recovery. Efficiency comes from a query reading only the partition it needs; security from applying different controls to different partitions; and load balancing from placing partitions on separate devices so their accesses do not contend.

Discuss in Forum

18.

Data-volume and frequency-of-use statistics are not critical inputs to the physical database design process.

A.True
B.False

Answer: B

Correct answer: False

Data volumes and access frequencies are among the most critical inputs to physical design: they decide which tables are large enough to warrant indexing, which queries run often enough to be worth optimising for, and where the storage and contention will fall. Without them the design decisions are guesses.

Discuss in Forum

19.

Two basic constructs to link one piece of data with another piece of data: sequential storage and pointers.

A.True
B.False

Answer: A

Correct answer: True

Every physical structure reduces to one of the two. Sequential storage links records by placing them next to each other, so the next record is found by continuing to read. Pointers link them by holding an address, so the next record can be anywhere. Indexes, hashing and chains are all built from these two ideas.

Discuss in Forum

20.

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

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

Answer: A

Correct answer: CHAR

CHAR is fixed width and pads every value to the declared length, which is exactly right for data that genuinely has one length - a two-character state code, a fixed-format reference. For anything varying, the padding is waste, and VARCHAR2 is the correct choice instead.

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