Question: Consider a relational DBMS that has two relations: Emp ( employees ) and Dept ( departments ) . Emp ( id , name, age, salary,

Consider a relational DBMS that has two relations: Emp (employees) and Dept(departments).Emp(id, name, age, salary, dname)Dept(dname, location) The Emp table has 500 tuples, and each tuple has a fixed length of 500 bytes. Theprimary key attribute "id" has a length of 40 bytes. The Dept table has 100 tuples, and each tuple has 200 bytes. The primary keyattribute "dname" has a length of 20 bytes.For simplicity, we assume each employee belongs to one and only one department, andeach department has 5 employees. Each block has 8K (8192) bytes, in which 100 bytesare reserved for the block header. No records span two or more blocks. Consider thefollowing disk organization strategy: Sequential: All the Emp records are placed sequentially based on their id's.Similarly, all Dept records are stored sequentially based on their names.Suppose we want to build a primary index INDEX1 on Emp.id, and a secondary indexINDEX2 on Dept.location. Treat duplicates in the secondary index in a straightforwardway; that is, each tuple should have an index entry. Assume: Each block pointer is 6 bytes. Each record pointer is 8 bytes. Index entries do not span blocks.For each index type (dense and sparse), compute the minimum number of blocks neededfor INDEX1 and INDEX2, respectively. (Notice that some type of index may not makesense.) That is, compute the number of blocks for the following combinations: INDEX1+ Dense; INDEX1+ Sparse; INDEX2+ Dense; INDEX2+ Sparse.

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related Programming Questions!