Deck 8: Physical Database Design

Question 1

$\begin{array}{lllll}\begin{array}{lll}\text { Course } \quad\quad \text { Record }\\\text { Name } \quad \quad \text { Address }\\\begin{array}{|l|l|}\hline \text { Anatomy } & 2 \\\hline \text { Biology I } & 1 \\\hline \text { Calculus I } & 4 \\\hline \text { Shakespeare } & 3 \\\hline \text { Statistics II } & 5 \\\hline\end{array}\\\text { Index }\end{array}&\begin{array}{lllll}\text { Record }&\text { Course\quad\quad\quad Course\quad\quad\quad\quad Number of\quad Professor }\\\text { Number }&\text { Number\quad\quad\quad Name\quad\quad\quad\quad Credits\quad\quad in Charge }\\\begin{array}{lr}1\\2\\3\\4\\5\end{array}&\begin{array}{|l|l|l|l|}\hline \text { BIOL1001 } & \text { Biology I } & 4 \quad\quad\quad& \text { Smith } \\\hline \text { BIOL3020 } & \text { Anatomy } & 3 & \text { Jones } \\\hline \text { ENGL2540 } & \text { Shakespeare } & 3 & \text { Duncan } \\\hline \text { MATH2000 } & \text { Calculus I } & 4 & \text { Stevens } \\\hline \text { MATH3500 } & \text { Statistics II } & 3 & \text { Jones } \\\hline\end{array}\\\end{array}\end{array}$

-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

Answer 1

is not an indexed sequential file because the indexed field is not the one on which the file is sequenced

Question 2

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

Answer 2

B

Question 3

$\begin{array}{lllll}\begin{array}{lll}\text { Course } \quad\quad \text { Record }\\\text { Name } \quad \quad \text { Address }\\\begin{array}{|l|l|}\hline \text { Anatomy } & 2 \\\hline \text { Biology I } & 1 \\\hline \text { Calculus I } & 4 \\\hline \text { Shakespeare } & 3 \\\hline \text { Statistics II } & 5 \\\hline\end{array}\\\text { Index }\end{array}&\begin{array}{lllll}\text { Record }&\text { Course\quad\quad\quad Course\quad\quad\quad\quad Number of\quad Professor }\\\text { Number }&\text { Number\quad\quad\quad Name\quad\quad\quad\quad Credits\quad\quad in Charge }\\\begin{array}{lr}1\\2\\3\\4\\5\end{array}&\begin{array}{|l|l|l|l|}\hline \text { BIOL1001 } & \text { Biology I } & 4 \quad\quad\quad& \text { Smith } \\\hline \text { BIOL3020 } & \text { Anatomy } & 3 & \text { Jones } \\\hline \text { ENGL2540 } & \text { Shakespeare } & 3 & \text { Duncan } \\\hline \text { MATH2000 } & \text { Calculus I } & 4 & \text { Stevens } \\\hline \text { MATH3500 } & \text { Statistics II } & 3 & \text { Jones } \\\hline\end{array}\\\end{array}\end{array}$

-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.

Answer 3

The Course Name field in the index must be in sorted order.

Question 4

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.

Question 5

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

Question 6

$\begin{array}{lllll}\begin{array}{lll}\text { Course } \quad\quad \text { Record }\\\text { Name } \quad \quad \text { Address }\\\begin{array}{|l|l|}\hline \text { Anatomy } & 2 \\\hline \text { Biology I } & 1 \\\hline \text { Calculus I } & 4 \\\hline \text { Shakespeare } & 3 \\\hline \text { Statistics II } & 5 \\\hline\end{array}\\\text { Index }\end{array}&\begin{array}{lllll}\text { Record }&\text { Course\quad\quad\quad Course\quad\quad\quad\quad Number of\quad Professor }\\\text { Number }&\text { Number\quad\quad\quad Name\quad\quad\quad\quad Credits\quad\quad in Charge }\\\begin{array}{lr}1\\2\\3\\4\\5\end{array}&\begin{array}{|l|l|l|l|}\hline \text { BIOL1001 } & \text { Biology I } & 4 \quad\quad\quad& \text { Smith } \\\hline \text { BIOL3020 } & \text { Anatomy } & 3 & \text { Jones } \\\hline \text { ENGL2540 } & \text { Shakespeare } & 3 & \text { Duncan } \\\hline \text { MATH2000 } & \text { Calculus I } & 4 & \text { Stevens } \\\hline \text { MATH3500 } & \text { Statistics II } & 3 & \text { Jones } \\\hline\end{array}\\\end{array}\end{array}$

-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

Question 7

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.

Question 8

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

Question 9

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

Question 10

$\begin{array}{lllll}\begin{array}{lll}\text { Course } \quad\quad \text { Record }\\\text { Name } \quad \quad \text { Address }\\\begin{array}{|l|l|}\hline \text { Anatomy } & 2 \\\hline \text { Biology I } & 1 \\\hline \text { Calculus I } & 4 \\\hline \text { Shakespeare } & 3 \\\hline \text { Statistics II } & 5 \\\hline\end{array}\\\text { Index }\end{array}&\begin{array}{lllll}\text { Record }&\text { Course\quad\quad\quad Course\quad\quad\quad\quad Number of\quad Professor }\\\text { Number }&\text { Number\quad\quad\quad Name\quad\quad\quad\quad Credits\quad\quad in Charge }\\\begin{array}{lr}1\\2\\3\\4\\5\end{array}&\begin{array}{|l|l|l|l|}\hline \text { BIOL1001 } & \text { Biology I } & 4 \quad\quad\quad& \text { Smith } \\\hline \text { BIOL3020 } & \text { Anatomy } & 3 & \text { Jones } \\\hline \text { ENGL2540 } & \text { Shakespeare } & 3 & \text { Duncan } \\\hline \text { MATH2000 } & \text { Calculus I } & 4 & \text { Stevens } \\\hline \text { MATH3500 } & \text { Statistics II } & 3 & \text { Jones } \\\hline\end{array}\\\end{array}\end{array}$

-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

Question 11

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

Question 12

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.

Question 13

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

Question 14

In a magnetic disk device, the number of tracks in a cylinder is equal to the number of _____.

A) tracks on a recording surface
B) cylinders
C) recording surfaces
D) read/write heads
E) platters

Question 15

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

Question 16

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.

Question 17

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

Question 18

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.

Question 19

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.

Question 20

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

Question 21

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.

Question 22

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.

Question 23

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
E) cannot also have indexes built over them

Question 24

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

Question 25

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.

Question 26

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

Question 27

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.

Question 28

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

Question 29

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

Question 30

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
E) the number of main record storage locations (not including overflow locations)

Question 31

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

Question 32

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

Question 33

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

Question 34

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.

Question 35

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

Question 36

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.

Question 37

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

Question 38

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
D) the unnormalized data
E) the set of entities, attributes, and relationships

Question 39

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

Question 40

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
D) All of the above.
E) None of the above.

Question 41

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

Question 42

One of the reasons for having secondary memory is that primary memory cannot possibly hold all of a company's programs and data.

Question 43

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
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.

Question 44

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

Question 45

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.

Question 46

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

Question 47

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.

Question 48

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.

Question 49

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.

Question 50

Programs and data are loaded into primary memory from secondary memory when they are required for processing.

Question 51

Tracks are concentric circles of data on a recording surface of a disk.

Question 52

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

Question 53

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.

Question 54

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.

Question 55

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.

Question 56

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.

Question 57

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.

Question 58

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.

Question 59

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.

Question 60

A "fixed" or "hard" disk drive operates with removable diskettes.

Question 61

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.

Question 62

The three ways of achieving direct access are with an index, with a tape drive, and with a hashing method.

Question 63

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.

Question 64

One of the advantages of B+-tree indexes is that they can handle record insertions more easily than simple linear indexes.

Question 65

A file with a B+-tree index is an indexed-sequential file.

Question 66

The number of levels in a B+-tree index is permanently fixed at the time the file is created.

Question 67

The way that data is retrieved, based on it being stored with a particular access method, is called its file organization.

Question 68

In an indexed-sequential file, an index is built over the same field on which the file is sequenced.

Question 69

The movement of a disk drive's access arm mechanism takes place at electronic, as opposed to mechanical, speeds.

Question 70

The way that data is stored for subsequent retrieval is known as its file organization.

Question 71

Retrieval performance of a B+-tree indexed file can be improved by keeping the upper part of the index tree resident in main memory.

Question 72

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.

Question 73

The average rotational delay is one-half of a disk revolution.

Question 74

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.

Question 75

Every record insertion in a B+-tree indexed file requires a track split and some may also require cylinder splits.

Question 76

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.

Question 77

A cylinder is a collection of contiguous tracks on a recording surface of a disk.

Question 78

An index can be built over a combination of fields of a file.

Question 79

The cylinder concept exists because access arm mechanism movement is so slow that it affects computer and application performance.

Question 80

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.