CS614 Assignment 2 Solution and Discussion Spring 2020
-
Re: CS614 Assignment 2 Solution and Discussion
Assignment No. 2
Semester: Spring 2020
CS614 – Data Warehousing
Total Marks: 15Due Date:
June 17, 2020Objectives:
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 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.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. -
please share idea
-
@Faaizaa said in CS614 Assignment 2 Solution and Discussion Spring 2020:
Question No. 1
Consider the following table having the information of students of a university:Student ID
Student Name
Campus ID
Student Age
Degree Program1
Ali
VLHR01
27
MS2
Kamran
VISB01
24
BS3
Akmal
VRWP01
24
BS4
Ahmad
VLHR01
26
MS5
Rehan
VISB01
23
BS6
Rizwan
VRWP01
29
MS7
Umer
VISB01
25
BS8
Javed
VLHR01
26
MS -
@Faaizaa said in CS614 Assignment 2 Solution and Discussion Spring 2020:
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.
Balance1
033XXXXX
3002
033YYYYY
2503
033ZZZZZZ
3004
033AAAAA
10005
033BBBBB
806
033CCCCC
554…
… …Call record detail
Call_ID
Customer_ID
Dialled Phone Number
Duration
Call Charges1
1
032ABCVD
1 minute
2 RS2
1
032ABCVG
2 minutes
4 RS3
1
032ABCVD
1 minute
2 RS4
2
032ANNNN
3 minutes
6 RS5
2
032AMMM
4 minutes
8 RS6
3
033RRRRR
1 minute
2 RS… … … … …
Due to certain performance factors company wants to de-normalize the tables using pre-joining technique. -
@Faaizaa said in CS614 Assignment 2 Solution and Discussion Spring 2020:
Re: CS614 Assignment 2 Solution and Discussion
Assignment No. 2
Semester: Spring 2020
CS614 – Data Warehousing
Total Marks: 15Due Date:
June 17, 2020Objectives:
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 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.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.