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
No comments:
Post a Comment