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

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

    Total space used = 10 x 40 + 110 x 70
    = 8.1 GB
    2. Calculate the total space reserved in memory after de-normalization using pre-joining technique

    Total space used = ( 70 + 40 – 10) x 110
    = 11,000
    = 11GB



    Recent Topics


  • 3
  • 1
  • 3
  • 1
  • 2
  • 2
  • 1
  • 1
|