@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: 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 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.
Solution file link
If you want to know how you can join us and get 100% Discount on your FEE ask Cyberian in Chat Room!
