# CS614 Assignment 2 Solution and Discussion Spring 2020

• ``````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
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 Info 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 record detail Call_ID Customer_ID Dialled 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.

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.

• 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
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 Info 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 record detail Call_ID Customer_ID Dialled 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.

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.

• Question No. 2
Consider the following normalized tables for a telecommunication company showing the daily call record details of customers:

Customer Info

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 record detail

Call_ID
Customer_ID
Dialled 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.

• 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
VLHR01
26
MS

5
Rehan
VISB01
23
BS

6
Rizwan
VRWP01
29
MS

7
Umer
VISB01
25
BS

8
Javed
VLHR01
26
MS

2

3

3

1

30

2

1

2
| |