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.


  • Cyberian's Gold

    @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


  • Cyberian's Gold

    @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.
    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.

    8e10c083-9566-4f46-9b80-694ebfd0b0f1-image.png


  • Cyberian's Gold

    @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 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

    7fdd02b9-e8d3-4d96-a0df-34c8ab4ec10d-image.png


  • Cyberian's Gold

    please share idea



Quiz 100% Result Quiz 100% Result
| |