Chapter 8 Physical Database Design
8-1
Last Update: November 13, 2011 — 1PM
Chapter 8: Physical Database Design
Multiple Choice
1. All of the following are true about secondary memory devices, except _____.
a. they include magnetic disk
b. they are necessary because primary memory cannot hold all of a company’s data
and programs
c. they are non-volatile
d. data and programs are loaded from secondary memory into primary memory for
processing
e. they are volatile
2. In a magnetic disk device, a single circle of stored data is called a _____.
a. track
b. cylinder
c. recording surface
d. read/write head
e. platter
3. In a magnetic disk device, a set of tracks, one above the other (and one from each
recording surface) is called a _____.
a. CD
b. cylinder
c. hard disk
d. read/write head
e. platter
4. In a magnetic disk device, the number of tracks in a cylinder is equal to the number of
_____.
Chapter 8 Physical Database Design
a. tracks on a recording surface
b. cylinders
c. recording surfaces
d. read/write heads
e. platters
5. In a magnetic disk device, the number of positions to which the access arm
mechanism can move is equal to the number of _____.
a. tracks in a cylinder
b. tracks on a recording surface
c. tracks in the entire disk device
d. read/write heads
e. platters
6. In a magnetic disk device, the reason for storing data on a cylinder-wise basis is
_____.
a. to avoid scratching the recording surfaces which would result in lost data
b. because more data can fit on a cylinder than on a recording surface
c. to minimize access arm movement in order to improve performance
d. because cylinders are the most stable of the disk device features
e. to be able to store the maximum amount of data on the disk device
7. All of the following are steps in finding and transferring data from disk, except
_____.
a. seek time
b. head switching
c. rotational delay
d. scan time
e. transfer time
8-3
8. In terms of time, rotational delay in a disk device takes on average roughly _____.
a. no time
b. one quarter of a revolution
c. one half of a revolution
d. one revolution
e. two revolutions
9. An access method is _____.
a. the way that data is retrieved based on the file organization in use
b. the same thing as a file organization
c. the series of steps: seek time, head switching, rotational delay, and transfer time,
involved in retrieving data from a disk.
d. established prior to the establishment of a file organization
e. only used in tape data storage
10. Direct access can be achieved _____.
a. with an index but not with a hashing method
b. with a hashing method but not with an index
c. with neither an index nor a hashing method
d. with either an index or a hashing method
e. only with the combined use of an index and a hashing method
11. All of the following are characteristics of an indexed computer file and its index,
except _____.
a. the records of the file are sorted into the same order as the values in the index
b. a set of field values is copied from the file into the index
c. the field values copied into the index are sorted in the index
d. the field values in the index are associated with the location of the records in the
file from which they were copied
e. the index consists of two columns, one for the field values copied from the file
and one for the pointers back to the records of the file.
Chapter 8 Physical Database Design
Course
Name
Record
Address
Record
Number
Course
Number
Course
Name
Number of
Credits
Professor
in Charge
2
1
BIOL1001
Biology I
4
Smith
1
2
BIOL3020
Anatomy
3
Jones
4
3
ENGL2540
Shakespeare
3
Duncan
3
4
MATH2000
Calculus I
4
Stevens
5
5
MATH3500
Statistics II
3
Jones
Index Course file
12. Which of the following is true regarding the Course file and its index?
a. The index shown is the only index that can be built over this file.
b. The Professor in Charge field cannot be indexed because it is not unique.
c. Because the Course Name field is indexed, the Course file should be sorted so
that it is in order by Course Name.
d. The Number of Credits field cannot be indexed because it has only two values.
e. The Course Name field in the index must be in sorted order.
13. The Course file and its index _____.
a. is an indexed-sequential file because the index is in sequence by Course Name
b. is an indexed sequential file because the index and the file are each in sequence
by some field
c. is an indexed sequential file because an index is built over one of the file’s fields
d. is not an indexed sequential file because the indexed field is not the one on which
the file is sequenced
e. is not an indexed sequential file because only one of the file’s fields is indexed
14. In an indexed sequential file _____.
a. only one field may be indexed
b. the field on which the file is sequenced must be indexed
c. a field other than the one on which the file is sequenced must be indexed
d. it is possible that there is no index
e. the record addresses in the index must be sorted
8-5
15. The most common data indexing system in use today is the _____.
a. simple linear index
b. sequential access index
c. B+-tree index
d. C+-tree index
e. complex linear index
16. Which of the following is true regarding indexed sequential files and B+-trees?
a. A B+-tree indexed file is an indexed sequential file.
b. A B+-tree indexed file can never be an indexed sequential file.
c. Only a file with a simple linear index can be an indexed sequential file.
d. All indexed files are indexed-sequential files.
e. An indexed sequential file requires a hashing algorithm.
17. All of the following are true about B+-tree indexes and their files, except _____.
a. The records of the file are stored in order by key value.
b. Both a number of tracks and a number of cylinders are held empty in reserve for
new record insertion purposes.
c. Every insertion of a new record requires the use of an empty reserve track or
cylinder.
d. It is possible that a new record insertion can increase the number of levels of the
index tree.
e. The lowest level of the index is stored on the same cylinders as the data.
18. All of the following are true about B+-tree indexes and their files, except _____.
a. A file with a B+-tree index built over the field by which the file is stored in
sequence is an indexed sequential file.
b. A track split might lead to a cylinder split.
c. A track split might not lead to a cylinder split.
d. A cylinder split must lead to a change in the tree index.
e. A cylinder split must lead to an increase in the number of levels of the index tree.
Chapter 8 Physical Database Design
8-6
19. All of the following are advantages of the B+-tree index arrangement, compared to
the simple linear index arrangement, except _____.
a. The upper portion of the tree index can be held in main memory indefinitely to
improve performance.
b. The insertion of a new record requires either a track split or a cylinder split, but
not both for a single insertion.
c. Even for large files, only a small number of index records have to be read for a
direct search.
d. The nature of the index together with the track split and cylinder split capabilities
make record insertions manageable.
e. Some insertions can be made without track splits or cylinder splits if reserve
space has been provided on the data tracks.
20. Which of the following is true about indexes, in general?
a. An index can only be built over a field that has unique values.
b. Only one field of a file can be indexed.
c. An index can be used to retrieve the records of a file in logical sequence based on
the indexed field.
d. An index cannot be built over a combination of fields of a file.
e. The updating of an indexed field in a record requires that the index be updated but
record insertions and record deletions do not require any index to be updated.
21. Which of the following is true regarding hashed files?
a. The hashing algorithm may direct more than one record to the same location.
b. The number of record storage locations allocated is typically much fewer than the
maximum number possible based on the range of values of the key field.
c. The same hashing algorithm is used for both record insertions and record
retrievals.
d. All of the above.
e. None of the above.
22. In the division remainder hashing algorithm, the divisor (the number divided into the
other number) is _____.
a. the key value of the record being inserted or retrieved
b. one less than the key value of the record being inserted or retrieved
c. the total number of record storage locations, including the overflow locations
d. the maximum number of key values that the key field could generate
Chapter 8 Physical Database Design
8-7
e. the number of main record storage locations (not including overflow locations)
23. Using the division remainder algorithm for a hashed file _____.
a. the record insertion location is based on the division’s remainder
b. the record insertion location is based on the division’s quotient
c. the record insertion location is based on the combination of the division’s quotient
and the division’s remainder
d. if two or more records hash to the same location they are all stored there
e. if two or more records hash to the same location only the first one can be stored in
the file and the rest of the insertion attempts are rejected
24. In a hashed file using the division remainder method _____.
a. a collision results from the key values of two records having the same quotient in
the hashing calculation
b. a collision results from the key values of two records having the same remainder
in the hashing calculation
c. a collision results from the key values of two records having a combination of the
same quotient and the same remainder in the hashing calculation
d. All of the above.
e. None of the above.
25. In a hashed file, synonym pointer chains _____.
a. begin in the record overflow area
b. end in the main record storage area
c. can be a maximum of two record locations in length
d. require an index
e. can include only one record in the main storage area
26. Hashed files _____.
a. are stored in physical sequence based on the hashed field values
b. can be hashed separately on different fields simultaneously, just as indexed files
can have indexes built separately on different fields simultaneously
c. never need to be reorganized
d. can also have indexes built over them
Chapter 8 Physical Database Design
8-8
e. cannot also have indexes built over them
27. Physical database design is necessary ____.
a. to eliminate the data redundancy left over from logical database design
b. to make the first pass at arranging the attributes into related groupings
c. to improve the performance of the run-time environment.
d. to place foreign keys where they need to be to model the relationships in the
business environment
e. to prepare for logical database design
28. All of the following are factors affecting application and database performance,
except ____.
a. the length of attribute names
b. large data volumes
c. the number of data access operations
d. the need for joins
e. related data dispersed on the disk
29. All of the following are factors affecting application and database performance,
except ____.
a. overly liberal data access
b. the need to calculate totals
c. clumsy primary keys
d. the lack of direct access
e. the presence of primary keys
30. The starting point of the physical database design process is ____.
a. the creation of indexes over the relational tables
b. the output of the logical database design process
c. the denormalized tables
Chapter 8 Physical Database Design
8-9
d. the unnormalized data
e. the set of entities, attributes, and relationships
31. All of the following are inputs into the physical database design process, except ____.
a. application data requirements
b. throughput requirements
c. hardware characteristics
d. functional dependencies
e. application priorities
32. The delay from the time that the Enter Key is pressed to execute a query until the
result appears on the screen is called ____.
a. response time
b. satisfaction time
c. throughput
d. data volatility
e. data volume
33. The frequency with which stored data is updated is known as ____.
a. response time
b. satisfaction time
c. throughput
d. data volatility
e. data volume
34. Application data requirements that enter into the physical database design process
include ____.
a. the number of applications that will share particular database tables
b. the frequency with which applications that utilize particular tables are run
c. whether or not particular applications require that tables be joined
Chapter 8 Physical Database Design
d. All of the above.
e. None of the above.
35. Taking application priorities into account in physical database design is important
because ____.
a. a modification to a table that’s designed to help the performance of one
application may hinder the performance of another application
b. application priorities determine the relative status and rank of the application
owners in the organization
c. the current priority of an application can affect database performance in the future
d. All of the above.
e. None of the above.
36. All of the following are good candidates for having an index built over them, except
____.
a. foreign key
b. search attribute
c. primary key
d. All of the above.
e. None of the above.
37. Which of the following is a potential problem caused by adding too many indexes to
a relational database?
a. Too many indexes can slow the performance of the database when there is a large
amount of direct retrieval activity.
b. Updating the indexes when the data is updated can slow down queries executing
at the same time.
c. Maintaining the security of the database can be adversely affected by the presence
of a large number of indexes.
d. All of the above.
e. None of the above.
38. In terms of performance, there is no point in indexing a table if ____.
a. several of its attributes are not used as search fields
b. the number of rows of the table is very large
c. the number of rows of the table is very small
d. the number of columns of the table is fairly large
e. the number of columns of the table is fairly small
39. A logical view is an important device in ____.
a. making the database perform faster on data update
b. making the database perform faster on data retrieval
c. protecting the security of the data
d. All of the above.
e. None of the above.
40. In the clustering files physical database design technique, ____ are clustered near
each other on a disk.
a. the records of a table
b. the records of a table with common values of a particular attribute
c. the columns of a table that are indexed
d. the columns of a table that are located next to each other
e. related records from different tables
41. The clustering files physical database design technique ____.
a. is designed to improve the performance of a join operation
b. is likely to hurt the performance of a join operation
c. is designed to improve the performance of data retrievals from a single table
d. is designed to improve the security of the database environment
e. None of the above.
8-12
42. The common advantage of separating portions of a table’s data using either horizontal
or vertical partitioning is that ____.
a. performance will be improved when indexes are in use
b. performance will be improved when the entire table must be retrieved
c. the data in a partition can be stored in a concentrated space on the disk, which will
speed up its retrieval
d. All of the above.
e. None of the above.
43. Horizontal partitioning ____.
a. means dividing the rows of a table into groups and storing the groups separately,
on different areas of a disk or on different disks
b. means dividing the columns of a table into groups and storing the groups
separately, on different areas of a disk or on different disks
c. may involve splitting large text attributes off from the rest of a table
d. a & c above
e. b & c above
44. Vertical partitioning ____.
a. means dividing the rows of a table into groups and storing the groups separately,
on different areas of a disk or on different disks
b. means dividing the columns of a table into groups and storing the groups
separately, on different areas of a disk or on different disks
c. may involve splitting large text attributes off from the rest of a table
d. a & c above
e. b & c above
45. Consider Table A on the “one side” of a one–to-many relationship and Table B on the
“many side” of the relationship. The primary key of Table A has been placed in
Table B as a foreign key. The substituting foreign keys physical database design
technique ____.
a. can be performed if Table A has only one candidate key
b. cannot be performed if Table B has only one foreign key
Chapter 8 Physical Database Design
c. can only be performed if Table A has more than one candidate key
d. can only be performed if Table B has more than one foreign key
e. None of the above.
46. The advantage of using the substituting foreign keys physical database design
technique is that it is designed to ____.
a. reduce the number of joins
b. reduce the number of indexes
c. reduce the number of attributes in large, clumsy keys
d. All of the above.
e. None of the above.
47. The creating a new primary key physical database design technique is designed to
____.
a. reduce the number of joins
b. reduce the number of indexes
c. replace clumsy keys that have a large number of attributes
d. All of the above.
e. None of the above.
48. If the primary key of a table is clumsy because it has too many attributes and is
replaced by a new, single attribute key, ____.
a. it becomes the new primary key of its own table
b. it should replace the old primary key where it was used as a foreign key for one-
to-many relationships
c. it should replace the old primary key where it was used as a foreign key for many-
to-many relationships
d. All of the above.
e. None of the above.
8-14
49. Consider Table A on the “one side” of a one–to-many relationship and Table B on the
“many side” of the relationship. In the storing derived data physical database design
technique, the best place to store the derived data from Table B is ____.
a. in a new column in Table A
b. in a new column in Table B
c. in a new record type in Table A
d. in a new record type in Table B
e. None of the above.
50. The storing derived data physical database design technique is designed to ____.
a. reduce the number of joins
b. reduce the number of indexes
c. replace clumsy keys that have a large number of attributes
d. All of the above.
e. None of the above.
51. Combining two tables that represent a one-to-one relationship ____.
a. is designed to reduce the number of joins in the database environment
b. can result in the introduction of redundant data
c. can result in an increase in the total number of attributes involved in an individual
data retrieval operation
d. All of the above.
e. None of the above.
52. Consider Table A on the “one side” of a one–to-many relationship and Table B on the
“many side” of the relationship. The “repeating groups” in Table B may be
considered for merging into the related records of Table A if ____.
a. there is a large number of occurrences of the repeating groups of Table B for each
record of Table A
b. there is a small number of occurrences of the repeating groups of Table B for each
record of Table A
c. there is a significant number of indexes built over the repeating groups of Table B
d. All of the above.
e. None of the above.
Chapter 8 Physical Database Design
8-15
53. Consider Table A on the “one side” of a one–to-many relationship and Table B on the
“many side” of the relationship. Denormalization ____.
a. maintains the data in the two tables
b. combines the data in the two tables into one table
c. creates a third table
d. creates additional indexes
e. creates additional repeating groups
54. Denormalization can improve the ____.
a. performance of the database by reducing the number of join operations
b. integrity of the database by reducing the amount of redundant data
c. accuracy of the database by reducing the number of repeating groups
d. All of the above.
e. None of the above.
55. The drawback of denormalization is that it ____.
a. increases the number of join operations
b. introduces data redundancy
c. limits the number of indexes in use
d. increases the number of repeating groups
e. impedes the relational query optimizer’s performance
56. In the adding new tables physical database design technique ____.
a. subsets of the most heavily accessed portions of existing tables can be copied and
created as new tables
b. entire tables can be copied and new tables created
c. data redundancy across tables is traded-off against improved performance
d. All of the above.
e. None of the above.
Chapter 8 Physical Database Design
True/False
1. One of the reasons for having secondary memory is that primary memory cannot
possibly hold all of a company’s programs and data.
2. Programs and data are loaded into primary memory from secondary memory when
they are required for processing.
3. A “fixed” or “hard” disk drive operates with removable diskettes.
4. Tracks are concentric circles of data on a recording surface of a disk.
5. A cylinder is a collection of contiguous tracks on a recording surface of a disk.
6. The movement of a disk drive’s access arm mechanism takes place at electronic, as
opposed to mechanical, speeds.
7. In a disk, once the access arm mechanism moves to a particular cylinder, every track
of that cylinder can be accessed without further access arm mechanism movement.
8. The cylinder concept exists because access arm mechanism movement is so slow that
it affects computer and application performance.
9. The average rotational delay is one-half of a disk revolution.
10. Transfer time is the time it takes to actually move the data from the disk to primary
memory before the seek, head switching, and rotational delay operations take place.
11. The way that data is retrieved, based on it being stored with a particular access
method, is called its file organization.
12. The three ways of achieving direct access are with an index, with a tape drive, and
with a hashing method.
13. The way that data is stored for subsequent retrieval is known as its file organization.
8-18
14. The first step in indexing a file using a simple linear index is to copy a set of field
values from the file into the index and to sort them.
15. Each entry in a simple linear index is associated with the record that it came from in
the indexed file by recording the record’s key field value in the index entry.
16. In an indexed-sequential file, an index is built over the same field on which the file is
sequenced.
17. A file with a B+-tree index is an indexed-sequential file.
18. The number of levels in a B+-tree index is permanently fixed at the time the file is
created.
19. Retrieval performance of a B+-tree indexed file can be improved by keeping the
upper part of the index tree resident in main memory.
20. In the B+-tree index arrangement described in the text, the lowest level of the index
tree is stored using a track in each data cylinder.
Chapter 8 Physical Database Design
21. Every record insertion in a B+-tree indexed file requires a track split and some may
also require cylinder splits.
22. One of the advantages of B+-tree indexes is that they can handle record insertions
more easily than simple linear indexes.
23. A record insertion in a B+-tree index may require either a track split or a cylinder
split, but not both for a given insertion.
24. An index can be built over a combination of fields of a file.
25. A file can have only one index built over it.
26. When a new record is inserted into a file, an existing record is deleted, or an indexed
field is updated, all of the affected indexes must be updated.
27. An index can only be built over a field that has unique values.