CS614 Assignment No.2 Solution and Discussion


  • Cyberian's Gold

    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.


  • Cyberian's Gold

    @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


  • Cyberian's Gold

    @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

  • Cyberian's Gold

    Please share idea



Quiz 100% Result Quiz 100% Result
| |