@zareen said in CS614 Assignment No.2 Solution and Discussion:
Question No. 2
Consider the following normalized tables for a telecommunication company showing the daily call record details of customers:
Customer_ID Customer Phone No. Balance
1 033XXXXX 300
2 033YYYYY 250
3 033ZZZZZZ 300
4 033AAAAA 1000
5 033BBBBB 80
6 033CCCCC 554
…
… …Call_ID Customer_ID Dialed Phone Number Duration Call Charges
1 1 032ABCVD 1 minute 2 RS
2 1 032ABCVG 2 minutes 4 RS
3 1 032ABCVD 1 minute 2 RS
4 2 032ANNNN 3 minutes 6 RS
5 2 032AMMM 4 minutes 8 RS
6 3 033RRRRR 1 minute 2 RS
… … … … …Due to certain performance factors company wants to de-normalize the tables using pre-joining technique.
Table Information is given below:
• Assume 1:4 record count ratio between customer Info (master) and Call record detail (detail).
• Assume 15 million customers.
• Assume 10 byte Customer_ID.
• Assume 50 byte header for customer Info (master) and 80 byte header for Call record detail (detail) tables.
You are required to perform the following tasks:
• Calculate the Total space in GBs used with normalization.
• Calculate the Total space in GBs used after de-normalization.
Solution:
With normalization:
Total space used = 15 x 50 + 60 x 80 = 5.55 GB
After de-normalization:
Total space used = (80 + 50 – 10) x 60 = 7.2 GB