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. The primary key attribute "id" has a length of 40 bytes.
- The Dept table has 100 tuples, and each tuple has 200 bytes. The primary key attribute "dname" has a length of 20 bytes.
For simplicity, we assume each employee belongs to one and only one department, and each department has 5 employees. All the records are stored in 8K (8192 bytes) disk blocks. Each block uses 100 bytes for its header. Consider the following three disk-organization strategies:
- Sequential/Nonspan: All the Emp records are placed sequentially based on their id's. Similarly, all Dept records are stored sequentially based on their names. Records may not span two or more blocks.
- Sequential/Span: All the Emp records are placed sequentially based on their id's. Similarly, all Dept records are stored sequentially based on their names. Records are spanned, and spanning between blocks does not introduce additional storage cost.
- Clustered/Nonspan: For each department, its department record and its 5 employee records are stored in the same disk block. All department records are stored sequentially based on their names. We do NOT store Emp records separately. No records span two or more blocks.
For each storage organization, compute the total number of disk blocks required to store the two relations. Which of the following statements is true?
a) Sequential/Nonspan: 34 blocks.
b) Sequential/Nonspan: 30 blocks.
c) Clustered/Nonspan: 50 blocks.
d) Sequential/Nonspan: 38 blocks.