Disk Space Estimation
For this assignment task you will design a database for capturing studentâ€™s 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 fixedlength and variablelength columns in the table definition, calculate the space that each of these groups of columns occupies within the data row. The size of a column 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 fixedlength columns = Fixed_Data_Size
Number of variablelength columns = Num_Variable_Cols
Maximum size of all variablelength columns = Max_Var_Size
3. If there are fixedlength 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 variablelength columns in the table, determine how much space is used to store the columns within the row:
Total size of variablelength columns (Variable_Data_Size) = 2 + (Num_Variable_Cols x 2) + Max_Var_Size
If there are no variablelength columns, set Variable_Data_Size to 0.
This formula assumes that all variablelength columns are 100 percent full. If you anticipate that a lower percentage of the variablelength column storage space will be used, you can adjust 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
 Sep 26th, 2011
 0
 6328
This Question is not yet answered!
Related Answered Questions
Related Open Questions
Disk Space Estimation
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 fixedlength and variablelength columns in the table definition, calculate the space that each of these groups of columns occupies within the data row. The size of a column 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 fixedlength columns = Fixed_Data_Size
Number of variablelength columns = Num_Variable_Cols
Maximum size of all variablelength columns = Max_Var_Size
3. If there are fixedlength 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 variablelength columns in the table, determine how much space is used to store the columns within the row:
Total size of variablelength columns (Variable_Data_Size) = 2 + (Num_Variable_Cols x 2) + Max_Var_Size
If there are no variablelength columns, set Variable_Data_Size to 0.
This formula assumes that all variablelength columns are 100 percent full. If you anticipate that a lower percentage of the variablelength column storage space will be used, you can adjust 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
This Question is not yet answered!
Related Answered Questions
Related Open Questions