Deck 8: Physical Database Design

Question 1

The ideal Btree structure is wide,i.e.it has many arrows branching from each node,and short,i.e.it has a small number of levels between the root and leaf nodes.

Answer 1

True

Question 2

In a hash file,if two keys hash to the same physical address it is referred to as a contraction.

Answer 2

True
Explanation: It is referred to as a collision.

Question 3

In index matching,the condition in the WHERE clause can never contain pattern matching (using the keyword LIKE),regardless of whether the index is a single-column or a composite index.

Answer 3

False
Explanation: In a single-column index, LIKE is acceptable as long as the pattern searched for does not contain a meta character as the first part of the pattern.

Question 4

A logical record may be split across several physical records.

Question 5

Unordered sequential files are sometimes known as heap files.

Question 6

To improve the response time to access and change a database,consumption of all of the resources used by database processing should be minimized,including physical record transfers,main memory and disk space.

Question 7

A Sequential File is a simple file organization in which records are stored in insertion order or by key value.

Question 8

A bitmap join index is actually a precomputed join from a column in a parent table to the rows of the child table that join with rows of the parent table.

Question 9

In a Btree structure,the height of the tree determines the number of physical record accesses required to locate a particular key,with the cost of access being less than or equal to the height.

Question 10

Physical records are synonymous to blocks or pages.

Question 11

INSERT and DELETE statements affect only the indexes of the records being added or deleted.

Question 12

Query binding indicates how to implement a query as operations on a file.

Question 13

Periodic reorganization of a file as it becomes too full is necessary for both static hashing and Btree file structures.

Question 14

Although they are useful when more than 5% of the rows of a file must be retrieved,clustering indexes are harder to maintain than a non-clustering index when data is modified.

Question 15

The B+tree structure improves the performance of sequential searches over Btree because the leaf nodes are connected together so that the search does not have to move up and down the tree.

Question 16

Different hash functions may be used on different attributes of the same table.

Question 17

In order to optimize queries,some DBMSs allow the programmer to use hints that influence the access plan,but these should be used cautiously because a hint will override the optimizer.

Question 18

In the processing of an SQL statement by the DBMS,a parsed query is the output of the code generation task.

Question 19

Bitmap indexes are a good alternative to Btree index nodes when the columns to be indexed contain only a few values.

Question 20

In hash files,if a collision occurs because two keys hash to the same physical address,one method for locating a physical record with free space is a linear probe.

Question 21

Chose the correct statement regarding memory access time and disk access time:

A) memory access time is typically a million times faster than disk access time
B) disk access time is typically a million times faster than memory access time
C) memory access time is essentially the same as disk access time
D) memory access time is determined by the speed of the disk rotation and magnetic head movement.

Question 22

To maintain the number of collisions at an adequate level without wasting too much storage,the ratio of used records to the total number of records should be as follows:

A) At least 90%
B) At most 90%
C) At most 70%
D) At most 20%

Question 23

In selecting an index,a column that has almost unique values is a good choice for a clustering index if it is used in equality conditions.

Question 24

During the query optimization process,which phase analyzes your query to ensure that you have not violated language constraints (example: misspelled key words)or misused columns and tables (example: trying to compare columns with incompatible data types)?

A) Syntax and semantic analysis
B) Query transformation
C) Access plan evaluation
D) Access plan interpretation
E) Code generation

Question 25

Which of the following is not an output of physical design?

A) Choice of index files
B) Choice of candidate keys
C) Placing rows from two tables in the same physical record
D) Lowering the normal form level of two tables by combining them

Question 26

During the query optimization process,which phase generates and evaluates access plans and chooses the access plan with the lowest cost?

A) Syntax and semantic analysis
B) Query transformation
C) Access plan evaluation
D) Access plan interpretation
E) Code generation

Question 27

Figuer:
Each node of the B-tree can contain at most 4 keys.After inserting into the B-tree a record with key = 62 and a record with key = 85,the B-tree will have the following number of nodes (including the root):

A) 3
B) 4
C) 5
D) 6

Question 28

You should avoid creating too many indexes on tables which will have many insertion and/or deletion operations.

Question 29

Which one of the following is true about B-trees (including B-tree variations)?

A) The B-tree structure is used by many DBMS
B) The B-tree is rarely used because it is hard to implement
C) The B-tree is a theoretical model not used at all
D) The B-tree has been replaced in new relational DBMS by radically different data structures.

Question 30

An index on a combination of columns is just as flexible as multiple indexes on the individual columns,so either choice would be appropriate.

Question 31

Which of the following is used in establishing table profiles?

A) Number of rows
B) Number of physical records
C) Distribution of values in a column
D) All of the above

Question 32

A physical record:

A) May contain exactly one logical record
B) May contain several logical records from one table
C) May contain several logical records from different tables
D) All of the above

Question 33

Which of the following contributes to the difficulty of physical database design?

A) Complexity of the specific DBMS environment
B) Application profiles are hard to collect and changing
C) Uncertainty about the number of physical record accesses
D) All of the above

Question 34

Which of the following types of file is usually not provided by relational DBMS?

A) Heap
B) Hash file
C) Stack
D) B-tree

Question 35

Figuer:
Each node of the B-tree can contain at most 4 keys.To delete the records with key = 30 and key = 20,do the following:

A) Delete keys 20 and 30 from node B
B) Delete keys 20 and 30 from node B, move key 15 from B to A and delete node B
C) Delete keys 20 and 30 from node B, and move key = 10 from A to B
D) Delete keys 20 and 30 from node B, and move 50 from C to A

Question 36

To retrieve logical record 2312,how many physical records will need to be accessed?

A) 1
B) 2
C) 3
D) 4

Question 37

In RAID technology,striping involves the allocation of logical records to different disks.

Question 38

Figuer:
Each node of the B-tree can contain at most 4 keys.To insert into the B-tree a record with key = 25,do the following:

A) Split the node labeled B
B) Insert the record into the node labeled B
C) Insert the record into the node labeled A
D) Split the node labeled C

Question 39

In the processing of an SQL statement by the DBMS,code generation:

A) Comes first
B) Follows query transformation
C) Follows syntax and semantics analysis
D) Follows access plan evaluation

Question 40

Which logical record (identified by empno)is stored at physical address 13?

A) 2313
B) 2211
C) 2012
D) 2311

Question 41

A simple file structure in which records are stored by a key value is a(n)_________________ file.

Question 42

Query binding:

A) Saves the access plan selected for a query
B) Reduces optimization time
C) May be changed if table profiles change
D) All of the above

Question 43

A(n)______________ is a collection of bytes that are transferred between volatile storage in main memory and stable storage on a disk.

Question 44

Which of the following is not true about the difficulty of index selection?

A) The number of choices of indexes is linear in the number of columns
B) The query optimizer may have subtle reasons not to use an index
C) Values of parameters used in SQL statements of forms often vary from highly selective to not very selective
D) Index choices can be interrelated in a subtle way

Question 45

A(n)_____________ file is a specialized file structure that transforms a key value into a physical record address to provide fast access to the data.

Question 46

File structures,data placement,data formatting,and denormalization are all ____________ of the physical database design process.

Question 47

To analyze value distribution in a table,a ___________________ is often used,which is a two-dimensional graph where the x-axis represents the column ranges and the y-axis represents the number of rows containing the range of values.

Question 48

A(n)____________ is a collection of physical records organized for efficient access.

Question 49

Which of the following is not true of having sname as an attribute of ENROLLMENT?

A) This will facilitate retrieving grades of students in different offerings
B) This will result in update anomalies when the student's grades are updated
C) This will cause ENROLLMENT to violate 2NF
D) This will violate referential integrity in table ENROLLMENT

Question 50

One of the inputs to the physical database design is the _________________,which are statistics for each table such as the number of rows and unique column values.

Question 51

Figuer:
The restriction operation for RENTALS.PICKUP = 'ERIE' OR RENTALS.PICKUP = 'CARY'
Should be performed:

A) In AA
B) In BB
C) In CC
D) In DD

Question 52

Which of the following is true about access plan execution?

A) Machine code generated from the access plan executes faster than interpreting the access plan
B) The difference in execution time of machine code and of interpretation is usually small for most users
C) DBMS usually interpret access plan rather than generate machine code because of the great variety of hardware
D) All of the above

Question 53

Figuer:
The following operation should be performed in BB:

A) Sort(CID)
B) Sort(MAKE)
C) Merge join
D) Restriction operations

Question 54

Which of the following statements is not true when having overall_gpa and gpa_in_major as attributes of the STUDENT table?

A) This will increase the time of retrieving the overall_gpa and gpa_in_major for students
B) This will increase the time of updates whenever a grade is changed
C) This will not change the normal form of the table STUDENT
D) This will avoid join operations when retrieving the gpa_in_major for a student

Question 55

Figuer:
The following operation should be performed in AA:

A) Sort(CID)
B) Sort(MAKE)
C) Merge join
D) Restriction operations

Question 56

There is a DBMS with 3 disks that can be read and written in parallel and a file that contains the following sequential physical records: PR1,PR2,PR3,PR4,PR5,PR6,PR7,PR8,PR9.The records are allocated in sequence to disk stripes.Select the correct statement:

A) PR1, PR2, PR3 are located each on a different disk
B) PR1, PR2, PR3 may be read in parallel from disks
C) PR1, PR2, PR3 may be written in parallel to disks
D) All of the above

Question 57

In the processing of an SQL statement by the DBMS,a relational algebra query is the output of:

A) Code generation
B) Query transformation
C) Syntax and semantics analysis
D) Access plan evaluation

Question 58

A simple file structure in which records are stored in the order they are inserted is a(n)_________________ file.

Question 59

Figuer:
What programmer's or user's decision typically will affect the performance of the query?

A) Placing the condition "PICKUP = 'ERIE' OR PICKUP = 'CARY'" before the condition "CUSTOMER.CID = RENTALS.CID" in the query.
B) Placing the condition "PICKUP = 'ERIE' OR PICKUP = 'CARY'" after the condition "CUSTOMER.CID = RENTALS.CID" and before the condition "RENTALS.MAKE = RENTCOST.MAKE" in the query.
C) Selecting the attributes CID in CUSTOMERS and CID in RENTALS as indexes on the tables.
D) None of the above

Question 60

One of the inputs to the physical database design is the _________________,which are statistics for each form,report and query,such as the tables accessed/updated and the frequency of access/update.

Question 61

Using the index matching rules on a table with indexes created on columns C1,C2,C3,and C4,list the column(s)that would be matched if the WHERE clause contained the conditions "C2 = 10 AND C3 = 15 AND C4 = 4"._____________

Question 62

A(n)______________________ is a tree that encodes decisions about file structures to access individual tables,the order of joining tables,and the algorithm to join tables.

Question 63

In Btree file structures,the physical record size,the key size and the pointer size determine node capacity.How many pairs can be stored in a node if the physical record size is 1024 bytes,the key size is 8 bytes,and the pointer size is 8 bytes? __________

Question 64

When a condition in the WHERE clause of a query references a column that has been indexed,the DBMS performs an operation known as ____________________ to determine if the index can be used.

Question 65

In a(n)_________________ file,the most popular variation of the Btree,all keys are stored redundantly in the leaf nodes.

Question 66

A(n)_________________ index references the rows of a child table that join with rows of the parent table containing the column.

Question 67

A dominant RAID architecture which uses both data and error-correcting pages to improve reliability is ____________.

Question 68

One of the dominant RAID architectures,___________ involves a full mirror or redundant array of disks,on which each physical record is written to both arrays in parallel.

Question 69

In a ____________ index,the order of the data records is unrelated to the index order.

Question 70

The process of associating a query with an access plan is known as ____________________.

Question 71

A(n)________________ is a situation that occurs when a hash function assigns the same physical record address to more than one key in a file.

Question 72

A popular tree-shaped file structure used by many DBMSs that demonstrates good performance on both sequential searches and key searches is a(n)________________.

Question 73

List three characteristics of a Btree file structure that lend themselves to the possible meaning of the B in the name: _________________________

Question 74

In a ____________ index,the order of the data records is close to the index order.

Question 75

Using the index matching rules on a table with indexes created on columns C1,C2,C3,and C4,list the column(s)that would be matched if the WHERE clause contained the conditions "C2 = 10 AND C1 BETWEEN 15 AND 20 AND C3 <= 4"._____________

Question 76

______________ is a collection of disks that operates as a single disk.