Virtual University of Pakistan Fall 2024
CS614 – Data Warehousing
Assignment No.1
(Graded)
Maximum Marks: 20
Instruction Due Date:
08 November 2024
The purpose of this assignment is to give you hands-on practice. It is expected that students
will solve the assignment themselves. The following rules will apply during the evaluation
of the assignment.
● Cheating from any source will result in zero marks in
the assignment.
● The submitted assignment does NOT open or the file is corrupted.
● No assignment after the due date will be accepted
● Students can submit HTML, Images & Plain text only in this inline Mode. You
may also insert an image file/table.
● DOC/pdf File uploading option will not be available) in inline assignment
submission.
Uploading Assignment Instruction
Follow the given instructions to submit inline assignments.
● You can use MS Visio/ MS Paint for drawing the ERD Diagram etc.
● Students can insert the images of the following formats.
● Images and tables can be inserted using the following highlighted option in the
interface.
Virtual University of Pakistan Fall 2024
Virtual University of Pakistan Fall 2024
Objective & Learning Outcome
Understanding of the OLTP Query and DWH Query
Students will be able to apply Normalization and de-normalization techniques in database tables.
Question No. 1 (Marks = 10)
c
Considering the following queries:
OLTP (OnLine Transaction Processing) specific query
Select tx_date, balance from tx_table
Where account_ID = 592156;
DWH specific query
Select balance, age, sal, gender from customer_table and tx_table
Where age between (20 and 30) andEducation = ‘graduate’ and CustID.customer_table
=Customer_ID.tx_table;
Let's take a brief look at these two queries, write the results by comparing them, and
summarize them in thetable below:
Criteria OLTP Query DWH Query
Primary Key Usage
Number of Tables
Rows Returned
Query Selectivity
Indexing
Virtual University of Pakistan Fall 2024
Question No. 2 (Marks = 10)
Consider anemployee database system developed for a multi-branch company. Each branch is
based on a specific department in a different city, e.g., HR, Admin, or Head Office.
Table 1: EmployeeRecord
EmployeeI EmployeeNam Branch Departme JobTitle DateOfJoinin Salary
D e nt g
Emp32055 Salman Islamaba Head office CEO 2016-11-01 850000
Ahmed d
Emp32001 Hussain Ali Lahore Amin Admin 2019-05-01 350000
Officer
Emp32056 Uzma khan Lahore Admin Superviso 2019-05-01 180000
r
Emp32022 Zafar Ali Lahore Admin Admin 2020-03-15 220000
Officer
Emp32035 Usama Khan Karachi HR HR 2021-08-20 150000
Assistant
Emp32077 Ali Ahmed Karachi HR HR 2021-05-01 280000
Manager
Identify the normalization form(NF) of the given table and write down the anomaly type of
given statements.
Normalization Form(NF) ?
If a new branch is added (e.g., a new branch ?
in another city), it is impossible to record it
in the table unless an employee is also added.
If all employees from a particular branch are ?
removed, information about that branch (e.g.,
the city and department) will also be lost.
If a department or branch location of a ?
specific department changes (e.g., the HR
department moves to a different city), the
change must be made in multiple rows. If
any change is missed, it can lead to
inconsistent data.
Note: Plagiarism will be checked for each question. Please answer the
questions in your own words and marks will be awarded based on your
answer and plagiarism report.
For any query about the assignment, contact at email [email protected]
Virtual University of Pakistan Fall 2024
