Deck 5: Physical Database Design and Performance

Question 1

The value a field will assume unless the user enters an explicit value for an instance of that field is called a:

A) default value.
B) null value.
C) range control.
D) gurand.

Answer 1

A

Question 2

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

Answer 2

D

Question 3

Database access frequencies are estimated from:

A) transaction volumes.
B) user logins.
C) security violations.
D) random number generation.

Answer 3

A

Question 4

Sensitivity testing involves:

A) checking to see if your teeth hurt when you brush.
B) seeing how accurate data are.
C) checking to see if missing data will greatly impact results.
D) deleting the associated row.

Question 5

A key decision in the physical design process is:

A) ignoring the size of the database.
B) selecting structures.
C) deciding on the monitor.
D) deciding the e-r diagrams.

Question 6

All of the following are objectives when selecting a data type EXCEPT:

A) represent all possible values.
B) improve data integrity.
C) support all data manipulations.
D) use a lot of storage space.

Question 7

An integrity control supported by a DBMS is:

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

Question 8

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

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

Question 9

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

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

Question 10

An appropriate datatype for one wanting a fixed-length type for last name would include:

A) VarChar.
B) Char.
C) Blob.
D) Date.

Question 11

A method for handling missing data is to:

A) substitute with random numbers for the missing data.
B) track missing data with special reports.
C) perform insensitivity testing.
D) delete the associated row.

Question 12

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

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

Question 13

An appropriate datatype for adding a sound clip would be:

A) VarChar.
B) Char.
C) Blob.
D) Date.

Question 14

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

Question 15

A requirement to begin designing physical files and databases is:

A) normalized relations.
B) physical tables created.
C) implementation complete.
D) all datatypes determined.

Question 16

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

A) user.
B) CEO.
C) XML.
D) operating system.

Question 17

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

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

Question 18

In which data model would a code table appear?

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

Question 19

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 20

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 21

Which type of file is easiest to update?

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

Question 22

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 only one category is allowed.

Question 23

A file organization where files are not stored in any particular order is considered a:

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

Question 24

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 25

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 26

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 27

A factor to consider when choosing a file organization is:

A) inefficient data retrieval.
B) DDL.
C) efficient storage.
D) DML.

Question 28

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 29

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 30

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 31

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 32

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 33

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

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

Question 34

An advantage of partitioning is:

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

Question 35

A disadvantage of partitioning is:

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

Question 36

Distributing the rows of data into separate files is called:

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

Question 37

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

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

Question 38

Which type of file is most efficient with storage space?

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

Question 39

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

Requirements for response time, data security, backup and recovery are all requirements for physical design.

Question 42

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

Question 43

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

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

Question 44

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

Question 45

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

Question 46

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 47

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

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

Question 48

SOX stands for the Sorbet-Oxford Act.

Question 49

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

Question 50

The logical database design always forms the best foundation for grouping attributes in the physical design.

Question 51

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

Question 52

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

Question 53

The Number datatype would be appropriate for a zip code.

Question 54

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

Question 55

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

Question 56

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 57

Efficient database structures will be beneficial only if queries and the underlying database management system are tuned to properly use the structures.

Question 58

VarChar2 would be appropriate for a user that wanted a text datatype for LastName that would only consume the required space.

Question 59

One decision in the physical design process is selecting structures.

Question 60

The smallest unit of named application data is a record.

Question 61

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

Question 62

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

Question 63

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

Question 64

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

Question 65

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

Question 66

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 67

In a Heap file organization, files are not stored in any particular order:

Question 68

Indexes are most useful on small, clustered files.

Question 69

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

Question 70

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

Question 71

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

Question 72

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 73

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

Question 74

Security is one advantage of partitioning.

Question 75

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

Question 76

A file organization is a named portion of primary memory.

Question 77

Reduced uptime is a disadvantage of partitioning.

Question 78

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

Question 79

Keeping the zip code with the city and state in a table is a typical form of denormalization.

Question 80

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