-
Re: CS614 Assignment 3 Solution and Discussion
Assignment No. 3Semester: Spring 2020
CS614 – Data Warehousing
Total Marks: 10Due Date:
July 27, 2020Objectives:
After completing this assignment, the students will be able to:
• compare Parallel Processing and Serial Processing
• describe what and when to Parallelize
• calculate Speed up using Amdahl’s LawInstructions
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
Scenario:
XYZ is a fabric manufacturing company. This company is planning to implement DWH for its existing OLTP system implemented in 300 + stores all over Pakistan. Size of company’s Database is approx. 298 GB that grows at the rate of 0.8 GB per day approximately. Company has designed a state of the art DWH in its head office in terms of hardware and software. Hardware consists of 7 Systems having quad-core processors. In ideal situation, a complex query would return results in 27 minutes on single processor (serial execution).
After analyzing the scenario given above, you are required to answer the following questions;
Question # 1 – If a complex query is executed parallel on 6 single core processors then what would be the quantified speed up time.
Question # 2 – Calculate Speed up ratio using Amdahl’s Law, if 80% of query processing is done through parallel execution on DWH hardware.
Deadline:
Your assignment must be uploaded on VULMS on or before July 27, 2020. While July 28, 2020 will be a bonus day for assignment submission. After the bonus day, no assignment would be entertained via email. -
Re: CS614 Assignment No.2 Solution and Discussion
Assignment No. 2Semester: Spring 2020
CS614 – Data Warehousing
Total Marks: 15Due Date:
June 17, 2020Objectives:
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 MSYou are required to completely de-normalize the above table using “horizontal splitting” on the basis of Degree Program.
Question No. 2
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 … … … .. …
Consider the following normalized tables for a telecommunication company showing the daily call record details of customers: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. -
Re: CS614 Assignment 2 Solution and Discussion
Assignment No. 2Semester: Spring 2020
CS614 – Data Warehousing
Total Marks: 15Due Date:
June 17, 2020Objectives:
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 MSYou are required to completely de-normalize the above table using “horizontal splitting” on the basis of Degree Program.
Question No. 2
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
Consider the following normalized tables for a telecommunication company showing the daily call record details of customers:…
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. -
Re: CS614 Assignment 1 Solution and Discussion
Assignment No. 1
Semester: Spring 2020
CS614 – Data Warehousing
Total Marks: 20Due Date:
June 01, 2020Objectives:
After completing this assignment, the students will be able to:
• Identify Database entities from a given scenario
• Understanding and designing system’s constraints from given scenario
• Understand the database table structure
• Normalize a database table up to 2nd Normal Form (2NF)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
XYZ polyclinic is a well-reputed clinic providing good medical facilities in a posh area of Lahore. They also have a facility to admit a patient if the treatment requires. XYZ polyclinic was working manually for the last 10 years. You are hired by XYZ polyclinic management to design a database by reading the system’s requirements. Management of XYZ polyclinic decides to improve their system that would lead to convert the Database system to Data Warehouse for XYZ polyclinic in the future.
Database Requirements:
The organization is interested in storing name, patient’s CNIC, address, city, zip, province, country, phone number, referred by, admission date, and discharge date for storing patient’s information in the database. The referred by (would contain doctor’s id), admission date and discharge date attributes are only used in case of patient’s admission in a specific room. From the doctor’s point of view, they are interested to store doctor name, CNIC of doctor, address, city, zip, province, country, doctor’s phone number, area of specialization. We must store the treatment information that is suggested by the doctor for a specific patient which includes prescribed medicines. Medicine details must contain medicine id, name, dosage, and potency as attributes along with reference of treatment that is suggested by a doctor. If a patient would require getting admit in polyclinic then the room type (executive/common), phone extension and charges per day are going to be stored in the system as room attributes.
Some additional constraints about the system are as under;
One patient is referred to one/more doctor at a time. Multiple patients would be examined by the doctor in a day One patient may have multiple visits to a single doctor Room capacity/facilities for attendants would not be handled at this time One patient may use many medicines as suggested by doctor Every time visit to a doctor may result in a change of medicine or admitted to the polyclinicTASK to Perform and Submit:
Identify relevant entities, primary keys, foreign keys, proper attributes and relations as per 2NF for the above scenario and provide database/schema/table-structure in MS-Word format that is Normalized up to 2nd Normal Form.
Important Note:
There’s no need to implement the solution using any DBMS Attribute Names should be clearly mentioned
Deadline:
Your assignment must be uploaded on VULMS on or before June 01, 2020. While June 02, 2020 will be a bonus day for assignment submission. After the bonus day, no assignment would be entertained via email. -
Assignment No. 03
Semester: Fall 2019
CS614: Data Warehousing Total Marks: 15Due Date: 27-Jan-2020
Objective:
The objective of this assignment is to enhance the learning capabilities of the students about:
• Join Techniques
• Hash based join
• Sort-Merge joinInstructions:
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
HyperMart is an online shopping store currently acquiring a large number of customers.
Normalized table structures for the shopping store are given below:
Order Table
Order_ID Order_Date 100 2-Feb-2019 201 2-Feb-2019 300 4-Feb-2019 …. …Order details Table
Detail_ID Order_ID Product_ID Product_Quantity Sale_Amount 100 100 2 1 100 101 100 3 2 200 200 201 5 1 50 201 201 7 1 90 300 300 15 1 600 301 300 56 1 800 302 300 57 1 850 …. … … …. …Table Information
• Assume 1:12 record count ratio between Order and Order detail for online store’s database
• Assume 10,000 ordersTask
For the given relations you are required to calculate the following costs in terms of I/O operations and find out which joining technique is better for given scenario. Tasks you are required to perform are as under;
Cost of Sort-Merge Join Cost of Hash-Join On the basis of your calculations, suggest better joining technique between sort-merge and hash join for the given scenarioBest of Luck!
-
Total Marks 5
Starting Date Wednesday, January 15, 2020
Closing Date Thursday, January 16, 2020
Status Open
Question Title GDB-CS614
Question DescriptionScenario
Pulse Globe Energy Limited (PGEL) is involved in the undertaking of gas exploration, development and production activities in Australia and Asia. PGEL has investments in upstream gas activities and electricity generation that complement wholesale energy contracts to support the retail customer base.
For one of the projects PGEL has already acquired the Seismic data which provides a “time picture” of subsurface structure to aid in gas exploration.
You are hired by the company as Data Analyst to suggest / identify the most desirable areas for gas exploration by using your analytical stills, as company would have to invest a huge budget of 1 million dollar for this task.
After reading the above scenario you are required to answer the following question:
Suggest the most suitable data mining technique for the given scenario and also support your answer with one valid reason (Not lengthy more than 2 – 3 lines).
Format of your answer would be as given below:
Technique Name: ________________________
1 Strong Reason to Choose: _______________________________________________________
-
Assignment No. 02
Semester: Fall 2019
CS614: Data Warehousing Total Marks: 10Due 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-joiningInstructions:
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:
Calculate the total space reserved in memory using normalization Calculate the total space reserved in memory after de-normalization using pre-joining techniqueBest of Luck!
-
Assignment No. 1
Semester: Fall 2019
CS614 – Data Warehousing
Total Marks: 15Due Date:
November 14, 2019Objectives:
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 techniqueInstructions
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. -
Please share your current Quiz. 2 to help the students…
-
Assignment No. 3 Semester: Spring 2019
CS614 – Data WarehousingTotal Marks: 20
Due Date:
August 01, 2019Objectives:
After completing this assignment the students will be able to:
• Understand parallelism
• Calculate speed-up time for pipelining
• Calculate Costs associated with nested loop joins while indexing is not usedInstructions
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 a garments factory where a Men’s casual shirts are manufactured. Each Shirt requires 15 minutes for completion. The factory management is now interested in introducing a pipeline where cutting, stitching and finalizing the shirt will be done in parallel.
You are required to calculate the Speed-up that could be achieved after implementing pipeline for manufacturing of 25 shirts in a row.
Question No. 2
Consider two database tables X and Y, we are required to join these tables using Nested-Loop Join, Assume that indexing is not used in mentioned tables.
The table information is given as:
Size of Table X = 600 blocks
Size of Table Y = 900 blocks
Qualifying blocks for Table X = 50
Qualifying blocks for Table Y = 100Calculate the join cost for X&Y and also for Y&X; after calculations mention which Table should be the outer one X or Y.
Deadline:
Your assignment must be uploaded on VULMS on or before August 01, 2019. While August 02, 2019 will be a bonus day for assignment submission. After the bonus day, no assignment would be entertained via email. -
Total Marks 5
Starting Date Monday, July 15, 2019
Closing Date Tuesday, July 16, 2019
Status Open
Question Title GDB - CS614
Question Description
Scenario“Choice Restaurant” is a chain of restaurants having five branches across the country. This restaurant typically deals in fast food and has 40 to 100 customers a day in each branch.
The Management of this company has introduced an Online Transaction Processing (OLTP) system to handle the daily transactions. The Management is very concerned for cutting up the cost and attain customer satisfaction, therefore currently there is only one computer system per branch which is handling all the computing tasks (including transaction processing) but in future they have plans to induce DSS.
To speed up the online transaction processing the database manager is compelling the management to implement parallelism for this OLTP system during business hours for daily sales calculations.
Management is yet to decide that whether they should invest on the parallelism or not.
GDB Question:
Keeping the above scenario in mind do you think that implementing parallelism in above situation is a good option? Justify your answer with appropriate reason(s) in either case.
GDB Answer Template:
Choice: Parallelism used or not
Points of Justification:
Point 1:
Point 2:
Important Notes:
NO GDB is accepted via e-mail in any case
Lengthy replies of GDB will cause in deduction of marks. So you write your answer precisely within 3 to 5 lines in 2 points along with choice.
If you would not mention your choice at start of your answer, your GDB points would not be marked. -
Anybody can describe?
-
True
False
-
Change Data Capture (CDC) can be challenging task because:
Aggregates don’t change in real time
Transformation of extracted data is difficult
Identifying the recently modified data may be difficult
Source systems may not support extraction of changed aggregates
De-normalization is the process of selectively transforming normalized relations into un-normalized physical record specifications, with the aim to:
Well structure the data Well model the data Reduce query processing time noneAn OLTP system is always good at ___________
Evolving data
Keeping static data
_______ can be used when some columns are rarely accessed rather than other columns or when the table has wide rows or header or both.
Horizontal Splitting
Pre-joining
Vertical
In a fully normalized form:
To many Joins are required
Relationship lose their significance
no joins are required
_____________ facilitates a mobile computing paradigm.
DOLAP
HOLAP
ROLAP
MOLAP
Enrichment is one of the basic tasks in data ___________.
Extraction*** Transformation**
Loading
Summarization
The online high performance transaction processing was evolved in _________
1980 1975 1977 1965The process of converting entity relationship model in to dimensional model comprises of ___ steps:
Two
Three
Four
Five
Suppose the size of the attribute “Computerized National Identity Card (CNIC) no” is changed in NADRA database. This transformation refers to:
Format revision
Field splitting
Field decoding
Calculation of derived value
-
Assignment No. 02 Semester: Spring 2019
CS614: Data Warehousing
Total Marks: 10 Due Date: 30-May-2019Objective:
The objective of this assignment is to enhance the learning capabilities of the students about:
• De-Normalization
• Pre-Joining
• Storage Issues of Pre-joiningInstructions:
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)
AssignmentQuestion
HyperMart is an online shopping store currently acquiring a large number of customers. To manage some performance issues this online store requires to de-normalize its database for order and Order detail tables.
Normalized table structures are given below:Table Information
• Assume 1:12 record count ratio between Order master and Order detail for online store’s database
• Assume 10 million orders (120 million records in order detail).
• Assume 10 byte Order_ID.
• Assume 30 byte header for master and 60 byte header for detail tables.Task
You are required to perform the following tasks:
De-normalize the given tables using Pre-joining technique Calculate the total space used with normalization Calculate the total space used after de-normalizationBest of Luck!
-
Assignment No. 1 Semester: Spring 2019
CS614 – Data WarehousingTotal Marks: 20
Due Date:
May 15, 2019Objectives:
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 3rd normal formInstructions
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
XYZ company was established in 1989 in Karachi and deals in the business of garments export. There are 250 permanent employees that are working with XYZ company. XYZ’s admin department is maintaining employee’s leave records in hard form. The leave form currently in practice is shown below:
Now XYZ company wants you to design a Database solution for their organization, you are required to develop a database that is normalized up to the 3rd Normal form. In the future, this database can be used for developing front end application.
A raw table structure along with attributes are provided by the XYZ’s management to start your working on it, is as following;
employeeLeaveForm (employeeID, empName, designation, officeName, dateOfFill, leaveType, leaveFromDate, leaveToDate, leaveReason, leaveAddress, leaveContactNo, leaveProcessDate, leaveStatus, leaveProcessedBy),
Sample data for the table is provided on VULMS for your reference. To view / download this data, use the following link;
https://vulms.vu.edu.pk/Courses/CS614/Downloads/CS614 - SP 2019 - Assignment 1 - Sample Data.xlsx
You have to use exact given attributes names while normalizing this table to 3rd Normal form.
Instructions to solve assignment:
Use table employeeLeaveForm and attributes in it. You are not allowed to remove any of the provided attributes You may introduce new tables by using the same attributes in employeeLeaveForm and also add new attributes for indexing / normalization purpose You are also required to declare Primary and Foreign Keys from the provided list of attributes (Also represent them with suitable DB notation while writing table schema) Transform the table in 3rd normal form step by step i.e. First Normal form transformation then into 2nd normal form and then into 3rd. Also, mention the title of Normal Form before the transformation. Direct transform relation into 3rd normal form would result in zero marks whether your solution is correct. And this will be imposed strictly while marking this assignment with no later excuses. Example of resultant tables / solution should be in the form provided below:
tableNo1 (attribute1, attribute2, …)
tableNo2 (attributeX, attributeY, …)
tableNo3 (attributeI, attributeX, …)attribute1, attributeX and attributeI (with solid underline) are primary keys in tableNo1, tableNo2 and tableNo3 respectively while attribute (With dotted underline) is foreign key in tableNo3 from referee tableNo2.
Deadline:
Your assignment must be uploaded on VULMS on or before May 15, 2019. While May 16, 2019 will be a bonus day for assignment submission. After the bonus day, no assignment would be entertained via email.
CS614 Assignment No.2 Solution and Discussion
-
Re: CS614 Assignment No.2 Solution and Discussion
Assignment No. 2Semester: Spring 2020
CS614 – Data Warehousing
Total Marks: 15Due Date:
June 17, 2020Objectives:
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 MSYou 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. -
@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 GBAfter de-normalization:
Total space used = (80 + 50 – 10) x 60 = 7.2 GB -
@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 -
Please share idea