Skip to content
  • 0 Votes
    2 Posts
    152 Views
    zareenZ

    Please share your idea solution

  • 0 Votes
    4 Posts
    100 Views
    zareenZ

    @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

  • 0 Votes
    5 Posts
    227 Views
    zaasmiZ

    @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

  • 0 Votes
    2 Posts
    447 Views
    zaasmiZ

    CS614 ASSIGNEMNT NO 01 Solution
    411dc679-4c14-45ed-9524-f7df373c44de-image.png

    b4d345a9-d296-4b5b-9ad6-b9523459976c-image.png

    3e85fea8-a27a-4b26-9590-829227619cde-image.png

    21deaaf2-238b-4330-9ebe-07a3eace6d92-image.png

    425d51f3-a1df-44b8-879b-dbb3021df40f-image.png

    52e2773b-a944-404c-9e8f-5d61cd61cc0b-image.png

    b9d3fc5b-95b8-42e2-868f-a650c5667191-image.png

    29a35db4-8f48-4045-a80f-0412997b7af7-image.png

    CS614 assignment Solution spring 2020.xlsx

  • 0 Votes
    3 Posts
    510 Views
    zareenZ

    95402d7a-b0da-4b8a-b70c-52ba4ac298fe-image.png

    0c72dda8-4e51-4d51-8585-2fcf7882b710-image.png
    2a4ac89f-cbe7-4262-beca-6e3876e68e0a-image.png 082f8ac1-7529-4db9-a900-09500328d6c1-image.png
    92668a31-5bc9-4143-aad4-1dd9ca0073e1-image.png

  • 0 Votes
    1 Posts
    94 Views
    No one has replied
  • 0 Votes
    2 Posts
    193 Views
    zareenZ

    @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

  • 0 Votes
    3 Posts
    165 Views
    zareenZ

    @zareen said in CS614 Assignment 1 Solution and Discussion:

    Assignment No. 1
    Semester: Fall 2019
    CS614 – Data Warehousing
    Total Marks: 15

    Due Date:
    November 14, 2019

    Objectives:
    After completing this assignment the students will be able to:
    • Identify Database entities from a given scenario
    • Understand the database table structure
    • Normalize a database table up to 2nd normal form
    • De-normalize relationships using collapsing table technique

    Instructions
    Please read the following instructions carefully before submitting assignment:
    It should be clear that your assignment will not get any credit if:

    o Assignment is submitted after due date.
    o Submitted assignment does not open or file is corrupt.
    o Assignment is copied (From internet/ to from students).
    o Assignment is submitted other than word format (.doc, .docx).

    Assignment

    Question No. 1

    Consider the following schema related to a Social Media website named as ‘userPosts’. You have to perform following tasks related to the provided schema:

    1- Identify appropriate keys for following structure (Primary and/or foreign key(s))
    2- Convert this schema into 2 NF
    userPosts (userID, userName, password, address, postId, postDate, postContent)

    Question No. 2

    Consider the following schemas relevant to a hotel booking website. You are required to De-Normalization the given schemas using Collapsing Tables Technique.

    roomVisitor (roomID, visitorCNIC, dateTime)

    roomCharges (roomID, spentDays, roomRent)

    Deadline:
    Your assignment must be uploaded on VULMS on or before November 14, 2019. While November 15, 2019 will be a bonus day for assignment submission. After the bonus day, no assignment would be entertained via email.

    Q.1 Answer:

    userDetails (userID, userName, password, address)
    posts(postID, postDate, postContent)
    userPosts (userID, postID)

    • userID and postID in schema / table ‘userPosts’ are foreign keys

    Q.2 Answer:
    roomVisitorCharges (roomID, visitorCNIC, dateTime, spentDays, roomRent)

  • 1 Votes
    30 Posts
    7k Views
    zareenZ

    1_ As per Bill Inmost, a data warehouse, in contrast with classical applications is:

    Data driven _ pg285

    2_ Which of the following is NOT one of the three parallel tracks in Kimball’s approach?
    Lifecycle Maintenance track

    3_ Bill Inmon argues that requirements are well understood only after
    Data warehouse is populated _ pg285

    4_ Goal driven approach of data warehouse development was result of ______ work
    Böhnlein and Ulbrich-vom _ pg285

    5_ Identify the TRUE statement:
    Clustering is unsupervised learning and classification is supervised learning _ pg 270

    6_ Normally the term “DWH face to the business user” refers to:
    Lifecycle Analytical Applications track _ pg 306

    7_ In ________learning you don’t know the number of clusters and no idea about their attributes.
    Unsupervised learning
    https://www.cs.uic.edu/~liub/teach/cs583-fall-05/CS583-unsupervised-learning.ppt

    8_ Waterfall model is appropriate when
    Requirements are clearly defined _ pg 284

    9_ Implementation of a data warehouse requires ________ activities.
    none of above

    10_ Normally the input data structure (a database table) for a data mining algorithm:
    Has more number of records than attributes (not sure)

  • 0 Votes
    5 Posts
    347 Views
    zaasmiZ

    Answer will be same after convert.

    84e0807a-616d-4c42-84a2-4f63289e5a4a-image.png
    4476461b-9e74-4125-ad00-78dac6fb19df-image.png