CS614 ASSIGNMENT NO. 1 SPRING 2023 || 100% RIGHT SOLUTION || DATA WAREHOUSING || BY VuTech
Visit Website For More Solutions
www.vutechofficial.blogspot.com
KINDLY, DON’T COPY PASTE
SUBSCRIBE, SHARE, LIKE AND COMMENTS FOR MORE UPDATES
QUESTION-1
Fig. 1 given below reflects the relationship of some specific scenario related with Customer-Order.
 |
Fig. 1: Customer-Order relationship |
You are required to transform the given relations into De-Normalized form and provide the resultant table.
SOLUTION:
Here is the given relation's De-Normalized
table_customer_order:
ID FirstName Surname Address1 City PostCode ItemOrdered Date Price
QUESTION-2
Consider the following information about Customer and Order tables.
Table1 (Master):
Header Size of the Customer table: = 50 Bytes
Number of records stored in Customer table: = 60000
Table2 (Detail):
Header size Order table: = 70 Bytes
Number of records stored in Order table: = 5000000
Suppose you have applied the pre-joining De-Normalization technique on given tables. You are now required to calculate the size of resultant table in Megabytes (MB), Gigabytes (GB) and Terabytes (TB). The reference column in these two tables is of 8 bytes.
Hint: There would be 5000000 records in De-normalize table.
ANSWER:
To calculate the size of the resultant table after applying pre-joining de-normalization, we need to consider the sizes of the original tables and the additional denormalized columns.
Table1 (Customer):
Header size: 50 bytes
Number of records: 60,000
Reference column size: 8 bytes
Table2 (Order):
Header size: 70 bytes
Number of records: 5,000,000
Reference column size: 8 bytes
After de-normalization, the resultant table will have 5,000,000 records.
To calculate the size of the resultant table, we can sum up the sizes of the headers, record sizes, and the additional denormalized columns.
Size of the resultant table = (Header size + Record size + Additional column size) * Number of records
For the header size:
Resultant table header size = Table1 header size + Table2 header size
= 50 bytes + 70 bytes
= 120 bytes
For the record size:
Resultant table record size = Sum of record sizes in Table1 and Table2
= (Customer table record size + Order table record size) - Reference column size
= (50 bytes + 70 bytes) - 8 bytes
= 112 bytes
For the additional column size:
In this case, there is only one additional column (the reference column) with a size of 8 bytes.
Now we can calculate the size of the resultant table in different units:
In Megabytes (MB):
Size of the resultant table (MB) = (Resultant table header size + Resultant table record size + Additional column size) * Number of records / (1024 * 1024)
= (120 bytes + 112 bytes + 8 bytes) * 5,000,000 / (1024 * 1024)
= 652.313 MB
In Gigabytes (GB):
Size of the resultant table (GB) = (Resultant table header size + Resultant table record size + Additional column size) * Number of records / (1024 * 1024 * 1024)
= (120 bytes + 112 bytes + 8 bytes) * 5,000,000 / (1024 * 1024 * 1024)
= 0.608 GB
In Terabytes (TB):
Size of the resultant table (TB) = (Resultant table header size + Resultant table record size + Additional column size) * Number of records / (1024 * 1024 * 1024 * 1024)
= (120 bytes + 112 bytes + 8 bytes) * 5,000,000 / (1024 * 1024 * 1024 * 1024)
= 0.000593 TB
Therefore, the size of the resultant table after de-normalization is approximately 652.313 megabytes (MB), 0.608 gigabytes (GB), or 0.000593 terabytes (TB).