Recent Topics

# CS614 Assignment 2 Solution and Discussion

• Assignment No. 02
Semester: Fall 2019
CS614: Data Warehousing Total Marks: 10

Due Date: November 28, 2019

Objective:
The objective of this assignment is to enhance the learning capabilities of the students about:
• De-Normalization
• Pre-Joining
• Storage Issues of Pre-joining

Instructions:
You need to use MS word document to prepare and submit the assignment on VU-LMS.
It should be clear that your assignment will not get any credit if:

 The assignment is submitted after due date.
 The assignment is not in the required format (.doc or docx)
 The submitted assignment does not open or file is corrupt.
 Assignment is copied (partial or full) from any source (websites, forums, students, etc)

Assignment

Question

“Kare Pharma” is an online Medical store currently acquiring a large number of customers. To manage some performance issues this online Medical store requires to de-normalize its database using pre-joining technique for Prescription and Prescription details tables.

Normalized table structures are given below:

Prescription Table

Prescription_ID Patient_Name Doctor_Name Prescription_Date
….

Prescription details Table

Transaction_ID Prescription_ID Med_ID Med_Quantity Sale_Amount
…. ….

Table Information

• Assume 1:11 record count ratio between Prescription table as master table and Prescription details table for online Medical store’s database.
• Assume 10 million records in Prescription Table.
• Assume 10 bytes reserved for Prescription_ID in memory.
• Assume 40 bytes header for master table and 70 bytes header for details table.

You are required to perform the following tasks:

1. Calculate the total space reserved in memory using normalization
2. Calculate the total space reserved in memory after de-normalization using pre-joining technique

Best of Luck!

• Calculate the total space reserved in memory using normalization

Q. 1. Calculate the total space reserved in memory using normalization1. Calculate the total space reserved in memory using normalization

Q. 1 Solution:
Total space used
= 10 (Million records) x 40 (Bytes header) + 110 (Million records) x 70 (Bytes header)
= (1000000040) + (11000000070)
=8,100,000,000
= 8.1 GB

Q. 2. Calculate the total space reserved in memory after de-normalization using pre-joining technique

Q. 2 Solution:
Total space used
= (70 + 40 – 10) (Bytes header) x 110 (Million records)
=100*110000000
=11,000,000,000
= 11 GB

• Calculate the total space reserved in memory using normalization

Q. 1. Calculate the total space reserved in memory using normalization1. Calculate the total space reserved in memory using normalization

Q. 1 Solution:
Total space used
= 10 (Million records) x 40 (Bytes header) + 110 (Million records) x 70 (Bytes header)
= (1000000040) + (11000000070)
=8,100,000,000
= 8.1 GB

Q. 2. Calculate the total space reserved in memory after de-normalization using pre-joining technique

Q. 2 Solution:
Total space used
= (70 + 40 – 10) (Bytes header) x 110 (Million records)
=100*110000000
=11,000,000,000
= 11 GB | |