CS614 Assignment No.2 Solution and Discussion
Assignment No. 02
Semester: Spring 2019
CS614: Data Warehousing
Total Marks: 10 Due Date: 30-May-2019
The objective of this assignment is to enhance the learning capabilities of the students about:
• Storage Issues of Pre-joining
Please read the following instructions carefully before submitting assignment:
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)
HyperMart is an online shopping store currently acquiring a large number of customers. To manage some performance issues this online store requires to de-normalize its database for order and Order detail tables.
Normalized table structures are given below:
• Assume 1:12 record count ratio between Order master and Order detail for online store’s database
• Assume 10 million orders (120 million records in order detail).
• Assume 10 byte Order_ID.
• Assume 30 byte header for master and 60 byte header for detail tables.
You are required to perform the following tasks:
- De-normalize the given tables using Pre-joining technique
- Calculate the total space used with normalization
- Calculate the total space used after de-normalization
Best of Luck!
moaaz last edited by
CS614 Assignment Solution
- DE normalization using pre-joining
In pre-joining all columns are moved from the master table into the detail table
2) Calculate the total space used with normalization
Total space with normalization
= (H.S of master table * records in master table) + (H.S of detail table * records in detail table)
= (30 * 10) + (60 * 120)
= 300 + 7200
= 7.5 GB
¥ Calculate the total space used after de-normalization
Total space after de-normalization
= (H.S of detail table + H.S of master table – common join path (orde_id)) * (records in detail table)
= (60 + 30 - 10) * 120
= 80 * 120