Deck 5: Physical Database Design and Performance

Question 1

A detailed coding scheme recognized by system software for representing organizational data is called a(n):

A) DBMS code.
B) data type.
C) SQL.
D) DB layouts.

Answer 1

B

Question 2

A method for handling missing data is to:

A) substitute and estimate for the missing data.
B) track missing data with special reports.
C) perform sensitivity testing.
D) all of the above.

Answer 2

D

Question 3

The smallest unit of application data recognized by system software is a:

A) field.
B) row.
C) data type.
D) column.

Answer 3

A

Question 4

The storage format for each attribute from the logical data model is chosen to maximize ________ and minimize storage space.

A) query design
B) programmer productivity
C) data integrity
D) data integration

Question 5

All of the following are common denormalization opportunities EXCEPT:

A) two entities with a one-to-one relationship.
B) a one-to-many relationship.
C) a many-to-many relationship with nonkey attributes.
D) reference data.

Question 6

A key decision in the physical design process is:

A) knowing the user base.
B) selecting structures.
C) deciding on the monitor.
D) all of the above.

Question 7

Horizontal partitioning makes sense:

A) when different categories of a table's rows are processed separately.
B) when less security is needed.
C) when partitions must be organized the same.
D) when all of the above are true.

Question 8

A disadvantage of partitioning is:

A) simplicity.
B) remote optimization.
C) extra space and update time.
D) shorter technology spans.

Question 9

All of the following are horizontal partitioning methods in Oracle EXCEPT:

A) key range partitioning.
B) hash partitioning.
C) multivalued partitioning.
D) composite partitioning.

Question 10

In most cases the goal of ________ dominates the design process.

A) efficient data processing
B) security
C) quick pointer updates
D) shorter design times

Question 11

An integrity control supported by a DBMS is:

A) substitute estimates.
B) security.
C) range control.
D) GUI guards.

Question 12

A requirement to begin designing physical files and databases is:

A) normalized relations.
B) definitions of each attribute.
C) technology descriptions.
D) all of the above.

Question 13

Designing physical files requires ________ of where and when data are used in various ways.

A) maps
B) descriptions
C) keys
D) hints

Question 14

Database access frequencies are estimated from:

A) transaction volumes.
B) user logins.
C) security violations.
D) none of the above.

Question 15

An advantage of partitioning is:

A) efficiency.
B) remote optimization.
C) extra space and update time.
D) both A and B.

Question 16

Which of the following is an objective of selecting a data type?

A) Represent a small number of possible values
B) Maximize storage space
C) Limit security
D) Improve data integrity

Question 17

Distributing the rows of data into separate files is called:

A) normalization.
B) horizontal partitioning.
C) vertical partitioning.
D) file allocation.

Question 18

Sensitivity testing involves:

A) checking to see if you teeth hurt when you brush.
B) seeing how accurate data are.
C) checking to see if missing data will greatly impact results.
D) none of the above.

Question 19

All of the following are valid datatypes in Oracle 11g EXCEPT:

A) varchar2.
B) booolean.
C) blob.
D) number.

Question 20

In which data model would a code table appear?

A) Conceptual
B) Logical
C) Physical
D) Data layout

Question 21

A(n)________ is a field of data used to locate a related field or record.

A) key
B) index
C) lock
D) pointer

Question 22

An index on columns from two or more tables that come from the same domain of values is called a:

A) bitmap index.
B) multivalued index.
C) join index.
D) transaction index.

Question 23

A factor to consider when choosing a file organization is:

A) fast data retrieval.
B) security.
C) efficient storage.
D) all of the above.

Question 24

A contiguous section of disk storage space is called a(n):

A) track.
B) sector.
C) extent.
D) tablespace.

Question 25

Within Oracle,the named set of storage elements in which physical files for database tables may be stored is called a(n):

A) extent.
B) table.
C) tablespace.
D) partition.

Question 26

A rule of thumb for choosing indexes is to:

A) be careful indexing attributes that may be null.
B) index each primary key of each table.
C) use an index when there is variety in attribute values.
D) all of the above.

Question 27

A method to allow adjacent secondary memory space to contain rows from several tables is called:

A) cluttering.
B) clustering.
C) concatenating.
D) compiling.

Question 28

Which type of file is easiest to update?

A) Sequential
B) Hashed
C) Indexed
D) Clustered

Question 29

A method that speeds query processing by running a query at the same time against several partitions of a table using multiprocessors is called:

A) multiple partition queries.
B) perpendicular query processing.
C) parallel query processing.
D) query optimization.

Question 30

________ partitioning distributes the columns of a table into several separate physical records.

A) Horizontal
B) Crossways
C) Vertical
D) Final

Question 31

A command used in Oracle to display how the query optimizer intends to access indexes,use parallel servers and join tables to prepare query results is the:

A) explain plan.
B) show optimization.
C) explain query.
D) analyze query.

Question 32

Which type of file is most efficient with storage space?

A) Sequential
B) Hashed
C) Indexed
D) Clustered

Question 33

One field or combination of fields for which more than one record may have the same combination of values is called a(n):

A) secondary key.
B) index.
C) composite key.
D) linked key.

Question 34

While Oracle has responsibility for managing data inside a tablespace,the tablespace as a whole is managed by the:

A) user.
B) database administrator.
C) application developer.
D) operating system.

Question 35

One decision in the physical design process is selecting structures.

Question 36

In which type of file is multiple key retrieval not possible?

A) Sequential
B) Hashed
C) Indexed
D) Clustered

Question 37

A file organization that uses hashing to map a key into a location in an index where there is a pointer to the actual data record matching the hash key is called a:

A) hashed file organization.
B) hash key.
C) multi-indexed file organization.
D) hash index table.

Question 38

Another form of denormalization where the same data are stored in multiple places in the database is called:

A) data duplication.
B) data replication.
C) advanced placement.
D) horizontal partitioning.

Question 39

A(n)________ is a technique for physically arranging the records of a file on secondary storage devices.

A) physical pointer
B) retrieval program
C) file organization
D) update program

Question 40

A(n)________ is a routine that converts a primary key value into a relative record number.

A) record index calculator
B) index pointer program
C) hashing algorithm
D) pointing algorithm

Question 41

One method to handle missing values is to substitute an exact value.

Question 42

Denormalization almost always leads to more storage space for raw data.

Question 43

A default value is the value that a field will always assume,regardless of what the user enters for an instance of that field.

Question 44

A pointer is a field of data that can be used to locate a related field or record of data.

Question 45

Denormalization is the process of transforming relations with variable-length fields into those with fixed-length fields.

Question 46

Free range partitioning is a type of horizontal partitioning in which each partition is defined by a range of values for one or more columns in the normalized table.

Question 47

Hash partitioning spreads data evenly across partitions independent of any partition key value.

Question 48

Horizontal partitioning refers to the process of combining several smaller relations into a larger table.

Question 49

Sensitivity testing involves ignoring missing data unless knowing a value might significantly change results.

Question 50

The smallest unit of named application data is a record.

Question 51

Vertical partitioning means distributing the columns of a table into several separate physical records.

Question 52

A tablespace is a named set of disk storage elements in which physical files for the database tables may be stored.

Question 53

An extent is a named portion of secondary memory allocated for the purpose of storing physical records.

Question 54

Adding notations to the EER diagram regarding data volumes and usage is of no value to the physical design process.

Question 55

A range control limits the set of permissible values that a field may assume.

Question 56

A file organization is a named portion of primary memory.

Question 57

Reduced uptime is a disadvantage of partitioning.

Question 58

One objective of selecting a data type is to minimize storage space.

Question 59

Horizontal partitioning is very different from creating a supertype/subtype relationship.

Question 60

Security is one advantage of partitioning.

Question 61

Denormalization is the process of transforming normalized relations into ________ physical record specifications.

Question 62

Distributing the rows of data into separate files is called ________.

Question 63

Indexes are most useful for columns that frequently appear in WHERE clauses of SQL commands,either to qualify the rows to select or for linking.

Question 64

In a sequential file,the records are stored in sequence according to primary key.

Question 65

________ partitioning is a method in which each partition is defined by a range of values for one or more columns of a normalized table.

Question 66

A ________ limits the set of permissible values a field must assume.

Question 67

A hashing algorithm is a routine that converts a primary key value into a relative record number.

Question 68

Fast data retrieval is one factor to consider when choosing a file organization for a particular database file.

Question 69

The query processor always knows the best way to process a query.

Question 70

________ can be built into the physical structure of fields for many database management systems.

Question 71

________ is often used to decide whether or not missing data can be ignored.

Question 72

Clustering allows for adjacent secondary memory locations to contain rows from several tables.

Question 73

Parallel query processing speed is not significantly different from running queries in a non-parallel mode.

Question 74

A ________ is a detailed coding scheme recognized by system software,such as a DBMS,for representing organizational data.

Question 75

A key is a data structure used to determine the location of rows in a file that satisfy some condition.

Question 76

Using an index for attributes referenced in ORDER BY and GROUP BY clauses has no significant impact upon database performance.

Question 77

Along with table scans,other elements of a query can be processed in parallel.

Question 78

________ distributes the columns of a table into several separate physical records.

Question 79

Indexes are most useful on small,clustered files.

Question 80

A join index is a combination of two or more indexes.