cs430

For a set of 100 data records with 5 fields per record, how many clustered indexes can there be?

1 (There can only be one clustered index for any file.)

A B+ tree with a typical order of 100 and fill factor of 2/3 (i.e. fanout of 133), with a height of 4 can handle how many records? 133^4 133^3 3^133 4^133 133*4=532

133^4

Match the operation while deleting a data entry from a B+ tree to its order of sequence.

1Find Leaf L where entry exist 2Remove entry, replace index if necessary 3if L falls below d, try to redistribute 4if L falls below d and redistribution fails, merge L with siblings 5recursively merge index nodes where necessary

When a file is organized so that the ordering of data records is the same as or close to the ordering of data entries in some index, we say that is:(need to spell by youserlf)

A clustered index

Which of the following is true about primary and secondary indexes?

A primary index is one that not allow duplicates(correct)

Under what circumstances must a database index be updated? (Multiple choice) A record is inserted into a databse. A record is deleted from the database. A record is updated in the database. A security key is added to the database.

A record is inserted into a databse. A record is deleted from the database.

The time it takes for a particular hashing or indexing technique to find a particular data item, or set of items.

Access time

Match the operations to their effect. Aggregate function Outer join

Aggregate function---- takes a collection of values and returns a single value as a result. Outer join---- extension of the join operation to deal with missing information.

A query evaluation plan consists of which of the following?

An extended relational algebra tree with additional annotations at each node. Implementation methods to use for each relational operator.

Static hashing is done by performing which of the following?

Applying a hash function h to the search key to identify the bucket to which it belongs and then searching this bucket.

Which of the following are true about B+ tree index files?

B+ tree is the most widely used index. Insert / delete at logFN cost where F = fanout and N = # leaf pages. Each node contains d

. A B+ tree index takes the form of a tree in which every path from the root of the tree to a leaf of the tree is of the same length.

Balanced

Which of the following accurately describes the following: ρ(R1, πnameσage=23(Employees))

Create a relation instance called R1 containing the names of all employees that are 23 years old.

The time it takes for a particular hashing or indexing technique to delete a data item.

Deletion time

. The ability to double the directory by simply copying the directory to the lower part is accomplished because we are using the most significant bits.

FALSE

A search key is the same as a candidate key.

FALSE

.If you perform a relational algebra cross-product on two relation instances that contain exactly the same attributes (and their associated domains) and exactly the same values in those relation instances, you will get the same result as performing a natural join on those relation instances.

False

.The natural-join algorithm (also called the sort-merge-join algorithm) can be used to compute natural joins and equi-joins.

False

.The project operation is a tertiary operation that returns its argument relation, the predicate is was called with, and the lead attribute.

False

A conjuction of conditions of the form where op is a comparison operator is said to be in Bryce Codd Normal Form.

False

A marginalized temporary table is one that is created to pass intermediate tables between operators.

False

A primary index is one that includes the primary key, indexes that include only other candidate keys are called secondary indexes.

False

A search key is the same as a candidate key.

False

If we are inserting a data entry into a bucket in a static hashing table and there is no space in the bucket, we split the bucket and put the entry into the new bucket.

False

Two scan algorithms to implement a selection operation are a linear search and a bubble search.

False

. Match the steps to insert a data entry into a B+ tree to their order

Find the correct leaf, If room in L, put data entry into L; done, Split L..., Insert index entry..., If necessary,split index node recursively

How would you translate the following relational algebra expression: πperson-name(σcompany-name="Alpha"(works))

Find the names of all employees who work for the company named Alpha.

A global depth indicator N in an extendible hashing scheme is used to identify which of the following?

Identifies that there are 2**N number of entries in the directory.

.What is a materialized table used for?

It is a temporary table used to pass tuples between operations.

.Which of the following are common techniques used to develop algorithms for each relational operator evaluation? Normalization - providing normalization of the data as the query is processed Iteration - examining all tuples in an input table, one after another Partitioning - decomposing an operation into a less expensive collection of operations. Selection operations - selecting only those operations that can run efficiently Indexing - using an index to examine just those tuples that satisfy the condition

Iteration - examining all tuples in an input table, one after another Partitioning - decomposing an operation into a less expensive collection of operations. Indexing - using an index to examine just those tuples that satisfy the condition

An indexing technique based on a sorted ordering of the values.

Ordered index

Which of the following are steps involved in processing a query?

Parsing and translation. Optimization. Evaluation.

refers to the range of activities involved in extracting data from a database.

Query processing

The key advantage of using a B+ tree for an index file as opposed to a hashing scheme is which of the following? Range searches Duplicate keys Equality searches Ascending keys Descending keys

Range searches

Pipelining is important in an execution plan because of which of the following?

Reduces the cost by passing the tables directly from one operator to another.

Any attribute or set of attributes with which a file may locate a record or set of records.

Search Key

. A type of dense index, with an index entry for every search-key value and a pointer to every record in the file, where the records pointed to by successive values in the index are not stored sequentially.

Secondary index

Match the operations to their effect.

Set-intersection operation： a more convenient way to write r-(r-s). Natural join operation a binary operation that allows us to combine selections and a cartesian-product into a single operation. Division operation suitable to queries that include the phrase "for all". Assignment operation assigns parts of a relational-algebra expression to temporary relation variables.

The additional space occupied by an index structure for a particular hashing or indexing technique.

Space overhead

Which of the following best describes the advantage of hashing over a tree structure for indexing ?

Storing indexes in hash structures could result in less disk I/Os.

Which of the following is true about tree-structured indexing techniques?

Support both range searches and equality searches.

. Extendible hashing schemes solve the problems of a static hash scheme by implementing a directory of pointers to buckets and growing the directory appropriately.

TRUE

Dynamic hashing techniques allow the hash function to be modified dynamically to accommodate the growth or shrinkage of the database.

TRUE

. The average number of children in a B+ tree is called what?

The fan-out of the tree.

. Which of the following are true about B+ tree-based indexing?

The lowest level of the tree - called the leaf level - contains the data entries. It is an index structure that ensures that all paths from the root to a leaf in a given tree are of the same height. Finding the correct leaf page is faster than a binary search.

Both the global and the local depth use the value to identify which of the following?

The number of bits of the value returned from the hash table to use.(Correct)

In an extendible hashing scheme, the directory structure is grown when which of the following is true?

The size of the local depth indicatory exceeds the global indicator after the insert.

Which of the following is the responsibility of the query optimizer?

To identify an efficient execution plan.

.An access path is a way of retrieving tuples from a table and consists of either 1) a file scan or 2) and index plus a matching selection condition.

True

.The select operation selects tuples that satisfy a given predicate.

True

.When building an access path, an index matches a selection criteria if the index can be used to retrieve just the tuples that satisfy the condition.

True

1.Division is rarely implemented as a distinct operator in DBMS systems.

True

An item in the select list can be in the form of expression AS column_name, where expression is any arithmetic or string expression. TRUE

True

If the evaluation of a query requires repeated passes to accomplish the partitioning it is called recursive partitioning.

True

Several operations in a query plan may be grouped together in a pipeline in which each of the operations starts working on its input tuples even as they are being generated by another operation.

True

.Match the operation to its effect. Union operation Set-difference operations Cartesion-product operation Rename operations: 1)provides a unique identifier to the results of a relational-algebra expression provides a mechanism to move directories. 2)finds tuples that are in one relation but not another. 3)combines information from any two relations. 4)binary operation that returns the values present in both tables allows threads to communicate through combining signals.

Union operation:binary operation that returns the values present in both tables Set-difference operations:finds tuples that are in one relation but not another Cartesion-product operation:combines information from any two relations Rename operations:provides a unique identifier to the results of a relaional-algebra expression

. The primary difference between a B tree indices and a B+ tree indices is which of the following?

a B-tree eliminates the redundant storage of search-key values.

Index structures are referred to as _______, since they provide a path through which data can be located and accessed.

access paths

The key attribute of a hashing function is which of the following? even distribution of the entries throughout the table. the ability to work recurrence relations. even distribution of the sizes of the search keys. the ability of the function to grow the table appropriately. the ability for the function to work with minimal memory.

even distribution of the entries throughout the table.

Sorting of relations that do not fit in memory is called ________________sorting.

external

The main advantage to a static hashing schemes is that the number of buckets is variable and memory allocation becomes simplified.

false

.In query processing, the ____________ is the lowest-level operator to access data.

file scan

An indexing technique based on a uniform distribution of values across a range of buckets. access time insertion time deletion time space overhead access type search key ordered index hash index skew secondary index

hash index

The time it takes for a particular hashing or indexing technique to insert a new data item.

insertion time

.A sequence of primitive operations that can be used to evaluate a query is called a ______________ plan.

query evaluation

Match the terms associated with queries to its definition. query language relational algebra

query language-----a specialized language in which queries can be posted. relational algebra-----a formal query language based on a collection of operators for manipulationg realtions.

. When some hash buckets are assigned more records than are others, and a bucket may overflow even as other buckets still have space, it is called ________________.

skew

. One of the problems with a static hashing scheme is that long overflow chains develop.

true

A range search is the concept of finding all records where the seach key value is less than some value and/or greater than another value.

true

Which of the following relational algebra operations are binary and which are unary?

unary: select, project, rename binary: Cartesian product, assignment, set difference, divide, natural join, left outer join, right outer join, full outer join

When choosing a hash function to assign search key values to buckets, the function should have which of the following qualities?

uniform distribution random distribution