Question: 7 of 89
Disk Space Estimation
For this assignment task you will design a database for capturing students assessments marks and estimate the required amount of space.
Suppose we suppose that there are 5000 students at our institution and that on average each student registers for 5 courses in a semester design a database that can be used to capture the registration of courses and eventual capturing of a maximum of 4 course assessment marks per semester. Note that the institution offers about 200 courses from all the 10 departments. Think of how you would make it easy to choose courses for registration.
Further assume that the database will be used for one semester only. Capture as much information as necessary for objects involved. Further note that you would want your database to be as efficient as possible when retrieving the data.
The following steps can be used to estimate the amount of space required to store the data in a table:
1. Specify the number of rows present in the table:
Number of rows in the table = Num_Rows
2. If there are fixed-length and variable-length columns in the table definition, calculate the space that each of these groups of columns occupies within the data row. The size of a col-umn depends on the data type and length specification. For variable length Fields you can use the expected average length (Percentage) of the values stored in the columns.
Number of columns = Num_Cols
Sum of bytes in all fixed-length columns = Fixed_Data_Size
Number of variable-length columns = Num_Variable_Cols
Maximum size of all variable-length columns = Max_Var_Size
3. If there are fixed-length columns in the table, a portion of the row, known as the null bitmap, is reserved to manage column nullability. Calculate its size:
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
Only the integer portion of the above expression should be used; discard any remainder.
4. If there are variable-length columns in the table, determine how much space is used to store the columns within the row:
Total size of variable-length columns (Variable_Data_Size) = 2 + (Num_Variable_Cols x 2) + Max_Var_Size
If there are no variable-length columns, set Variable_Data_Size to 0.
This formula assumes that all variable-length columns are 100 percent full. If you anticipate that a lower percentage of the variable-length column storage space will be used, you can ad-just the result by that percentage to yield a more accurate estimate of the overall table size.
5. Calculate the row size:
Total row size (Row_Size) = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap +4
The final value of 4 represents the data row header.
6. Calculate the number of rows per page/Segment (8096 free bytes per page): (Pages are the equivalent of a Segments)
Number of rows per page (Rows_Per_Page) = ( 8096 ) / (Row_Size + 2)
Because rows do not span pages, the number of rows per page should be rounded down to the nearest whole row.
7. If a clustered index is to be created on the table, calculate the number of reserved free rows per page/Segments, based on the fill factor specified. If no clustered index is to be created, specify Fill_Factor as 100. Use 80 where you have a clustered index.
Number of free rows per page (Free_Rows_Per_Page) = 8096 x ((100 - Fill_Factor) / 100) / (Row_Size + 2)
The fill factor used in the calculation is an integer value rather than a percentage.
Because rows do not span pages, the number of rows per page should be rounded down to the nearest whole row. As the fill factor grows, more data will be stored on each page and there will be fewer pages.
8. Calculate the number of pages required to store all the rows:
Number of pages (Num_Pages) = Num_Rows / (Rows_Per_Page - Free_Rows_Per_Page)
The number of pages estimated should be rounded up to the nearest whole page.
9. Calculate the amount of space required to store the data in a table (8192 total bytes per page):
Table size (bytes) = 8192 x Num_Pages
Interview Candidate | Asked on: Sep 26th, 2011
Related Answered Questions
Related Open Questions