Secondary File Organization Consider the following table: CREATE TABLE User ( Lo

Question

Secondary File Organization

Consider the following table:

CREATE TABLE User (

LoginID CHAR(15) PRIMARY KEY,

Password CHAR(15),

Name CHAR(25),

City CHAR(25),

State CHAR(24),

Email CHAR(20),

YearOfBirth INT );

Assume the DBMS uses 1 byte to store a character and 4 bytes to store integers. The total number of rows in the table is 4000. The size of each disk block is B = 512 bytes. Assume the size of a pointer to each block is 8 bytes and the index entries are stored as unspanned records (i.e., each index entry cannot be stored in more than one block).

(a) Suppose you have a single-level primary index on the LoginID attribute. How many disk blocks are needed to store the index?

(b) Suppose you have a two-level primary index on the LoginID attribute. How many disk blocks are needed to store the entire index (first and second levels)?

(c) Suppose the table is stored as a sequential file, ordered by the State attribute. If you construct a single-level clustering index on the State attribute, how many disk blocks are needed to store the index? Assume there are 50 distinct values for state in the table.

(d) Suppose the table is stored as a sequential file, ordered by the LoginID attribute. If you construct a single-level secondary index on the State attribute, how many disk blocks are needed to store the index? Assume there are 50 distinct values for state in the table.

(e) Suppose the table is stored as a sequential file, ordered by the LoginID attribute. If you construct a multi-level index on the State attribute, how many disk blocks are needed to store the entire index and how many levels are needed (the highest level should contain only

Explanation / Answer

(a)If the index is built on the ordering 'key-field' of the file it

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.