Exam 8: Physical Database Design

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.

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.

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

A database consists of the tables STUDENT (PK = sno, sname, major), COURSE (PK = cno, major, ), OFFERINGS (PK = cno and ofno, ), ENROLLMENT (sno, cno, ofno, grade). All tables satisfy BCNF. Overall_gpa and gpa_in_major are computed for each student. -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:

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

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

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

In Btree file structures,the physical record size,the key size and the pointer size determine node capacity.How many <key,pointer> 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? __________

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

Figuer: -The following operation should be performed in AA:

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:

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

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

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

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.

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

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.

A database consists of the tables STUDENT (PK = sno, sname, major), COURSE (PK = cno, major, ), OFFERINGS (PK = cno and ofno, ), ENROLLMENT (sno, cno, ofno, grade). All tables satisfy BCNF. Overall_gpa and gpa_in_major are computed for each student. -Which of the following is not true of having sname as an attribute of ENROLLMENT?

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

Unordered sequential files are sometimes known as heap files.