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).

KINDLY, DON’T COPY PASTE
SUBSCRIBE, SHARE, LIKE AND COMMENTS FOR MORE UPDATES
SEND WHATSAPP OR E-MAIL FOR ANY QUERY
0325-6644800
kamranhameedvu@gmail.com
Visit Website For More Solutions
www.vutechofficial.blogspot.com