CS614 Assignment 2 Solution and Discussion


  • Cyberian's Gold

    Assignment No. 02
    Semester: Fall 2019
    CS614: Data Warehousing Total Marks: 10

    Due Date: November 28, 2019

    Objective:
    The objective of this assignment is to enhance the learning capabilities of the students about:
    • De-Normalization
    • Pre-Joining
    • Storage Issues of Pre-joining

    Instructions:
    Please read the following instructions carefully before submitting assignment:
    You need to use MS word document to prepare and submit the assignment on VU-LMS.
    It should be clear that your assignment will not get any credit if:

     The assignment is submitted after due date.
     The assignment is not in the required format (.doc or docx)
     The submitted assignment does not open or file is corrupt.
     Assignment is copied (partial or full) from any source (websites, forums, students, etc)

    Assignment

    Question

    “Kare Pharma” is an online Medical store currently acquiring a large number of customers. To manage some performance issues this online Medical store requires to de-normalize its database using pre-joining technique for Prescription and Prescription details tables.

    Normalized table structures are given below:

    Prescription Table

    Prescription_ID Patient_Name Doctor_Name Prescription_Date
    ….

    Prescription details Table

    Transaction_ID Prescription_ID Med_ID Med_Quantity Sale_Amount
    …. ….

    Table Information

    • Assume 1:11 record count ratio between Prescription table as master table and Prescription details table for online Medical store’s database.
    • Assume 10 million records in Prescription Table.
    • Assume 10 bytes reserved for Prescription_ID in memory.
    • Assume 40 bytes header for master table and 70 bytes header for details table.

    Task

    You are required to perform the following tasks:

    1. Calculate the total space reserved in memory using normalization
    2. Calculate the total space reserved in memory after de-normalization using pre-joining technique

    Best of Luck!


  • Cyberian's Gold

    @zareen said in CS614 Assignment 2 Solution and Discussion:

    Calculate the total space reserved in memory using normalization

    Q. 1. Calculate the total space reserved in memory using normalization1. Calculate the total space reserved in memory using normalization

    Q. 1 Solution:
    Total space used
    = 10 (Million records) x 40 (Bytes header) + 110 (Million records) x 70 (Bytes header)
    = (1000000040) + (11000000070)
    =8,100,000,000
    = 8.1 GB

    Q. 2. Calculate the total space reserved in memory after de-normalization using pre-joining technique

    Q. 2 Solution:
    Total space used
    = (70 + 40 – 10) (Bytes header) x 110 (Million records)
    =100*110000000
    =11,000,000,000
    = 11 GB


  • Cyberian's Gold

    @zareen said in CS614 Assignment 2 Solution and Discussion:

    Calculate the total space reserved in memory using normalization

    Q. 1. Calculate the total space reserved in memory using normalization1. Calculate the total space reserved in memory using normalization

    Q. 1 Solution:
    Total space used
    = 10 (Million records) x 40 (Bytes header) + 110 (Million records) x 70 (Bytes header)
    = (1000000040) + (11000000070)
    =8,100,000,000
    = 8.1 GB

    Q. 2. Calculate the total space reserved in memory after de-normalization using pre-joining technique

    Q. 2 Solution:
    Total space used
    = (70 + 40 – 10) (Bytes header) x 110 (Million records)
    =100*110000000
    =11,000,000,000
    = 11 GB



Quiz 100% Result
| |