CS614 Assignment No.2 Solution and Discussion

zareen

Re: CS614 Assignment No.2 Solution and Discussion

Assignment No.  2

Semester: Spring 2020
CS614 – Data Warehousing
Total Marks: 15

Due Date:
June 17, 2020

Objectives:
After completing this assignment, the students will be able to:
• De-Normalize the given table using horizontal splitting technique
• Calculate the Total space used with normalization.
• Calculate the Total space used after de-normalization.

Instructions
Please read the following instructions carefully before submitting assignment:
It should be clear that your assignment will not get any credit if:

• Assignment is submitted after due date.
• Submitted assignment does not open or file is corrupt.
• Assignment is copied (From internet/ to from students).
• Assignment is submitted other than word format (.doc, .docx).

Assignment

Question No. 1

Consider the following table having the information of students of a university:

Student ID	Student Name	Campus ID	Student Age	Degree Program
1	Ali	VLHR01	27	MS
2	Kamran	VISB01	24	BS
3	Akmal	VRWP01	24	BS
4	Ahmad	VLHR01	26	MS
5	Rehan	VISB01	23	BS
6	Rizwan	VRWP01	29	MS
7	Umer	VISB01	25	BS
8	Javed	VLHR01	26	MS

You are required to completely de-normalize the above table using “horizontal splitting” on the basis of Degree Program.

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.

Deadline:
Your assignment must be uploaded on VULMS on or before June 17, 2020. While June 18, 2020 will be a bonus day for assignment submission. After the bonus day, no assignment would be entertained via email.

zareen

@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

zareen

@zareen said in CS614 Assignment No.2 Solution and Discussion:

Question No. 1
Consider the following table having the information of students of a university:

Solution:

Student ID	Student Name	Campus ID	Student Age	Degree Program
2	Kamran	VISB01	24	BS
3	Akmal	VRWP01	24	BS
5	Rehan	VISB01	23	BS
7	Umer	VISB01	25	BS

Student ID	Student Name	Campus ID	Student Age	Degree Program
1	Ali	VLHR01	27	MS
4	Ahmad	VLHR01	26	MS
6	Rizwan	VRWP01	29	MS
8	Javed	VLHR01	26	MS

zareen

Please share idea