Ask Question, Ask an Expert

+61-413 786 465

Ask DBMS Expert

Home >> DBMS

Q1. Given the following file for assignment, identify data anomalies that must be removed before data can be loaded in data warehouse.

Worker_assignment ← -----------------on course web site

File is available in SAKAI

Assignment_worker(assignment_no, assignment_date, emp_number, chg_hour,assigned_hour, charges)

Where assignment number is the number assigned to an assignment
Assignment_date is the date assignment started
Emp_number is the number of employee assigned to that assignment
Chg_hour is amount paid to that employee for that assignment
Assigned_hour is the hours assigned to that employee for that assignment
Charges are the Total charges for that employee for that assignment (this is calculated as Chg_hour*assigned_hour)

- Assignment numbers always start with a letter followed by a 1 and are ALWAYS four characters long
ex: A123, Z178

- Emp No IS always 3 CHARCATER LONG

- An employee can not work more than 40 hour on a given project

Count (using EXCEL formulas -- IF, countif etc. as done in class) four types of errors:

- Missing data
- Incorrect Format
o To check length of empno--you can use LEN(cell address) to get length of item in that cell
o check for assignment number format (BONUS +1 points)
- Zero values
- Incorrect Calculations
o check for charges
charges= chg_hour*Assigned_Hour
o check for employee working more than 40 hours

Once counted

- Draw the pie chart or line chart of data anomalies and
- Discuss what errors can be corrected and how. (submit in WORD)

Must submit the EXCEL worksheet where errors are calculated and graph is drawn


Data integrity is a required feature of data warehouses. P & G is building a data warehouse and have run in data integration problems. They need to get data from 2 different users and combine them to maintain data integrity in their data warehouse.

The sources are:

Asia region
North American Region

Both region have data stored in different formats in two different files (employee_asia and emp_NA

Both tables are available in account Aggarwal as READ ONLY. You must create a copy in your account before using it.


you can create your own tables.


1. CLEAN the data in required format (for gender, country of origin, job_class and seniority)
a. Employee gender should be standardized, i.e., male should be changed to m and female to f
b. Country should be spelled completely, i.e, USA should be spelled out as United States of America
c. Ceylon no longer exists, change the name to Sri Lanka
d. Name is one attribute in dimension table, combine name as last and first, example Bora (last) and Lakshmi (first) should be modified to Bora, Lakshmi
e. Calculate both job_class and seniority

2. Create CLEAN_ASIA table

3. Create CLEAN_NA table

4. Combine the two using UNION to create following table

EMPLOYEE_DIM (Employee Id, Employee_name, seniority, gender, country, job_class)

5. Show the contents and structure of EMPLOYEE_DIM table.
6. Give a count of male and female employees

Q3 Revise the data warehouse based on new requirements (same as what we did in class)

Attachment:- Assignment.rar

Attachment:- archive

DBMS, Programming

  • Category:- DBMS
  • Reference No.:- M93132662
  • Price:- $40

Priced at Now at $40, Verified Solution

Have any Question? 

Related Questions in DBMS

Sql injection on a searchthe way search performs its task

SQL injection on a search The way Search performs its task is by executing the following query (in a php script):          $var=stripslashes($_POST['search']);          $query = "SELECT username from lab1_login where use ...

Sqlwrite a select statement that returns three columns from

SQL Write a SELECT statement that returns three columns from the Vendors table: VendorContactFName, VendorContactLName, and VendorName. Sort the result set by last name, then by first name.

In sql developercreate a table userpermissions provide

IN SQL DEVELOPER Create a table UserPermissions (provide create and insert statements code) Document UserName Policy SYSTEM Menu JDOW W2 USAM Permissions SYSTEM W2 JDOW Form 1040 USAM Policy JDOW W2 SYSTEM Write a PL/SQL ...

A schools office of the registrar maintains data about the

A School's office of the registrar maintains data about the following entities: a) courses (including course number, title, credits, syllabus and prerequisites), b) course offerings (including course number, year, semest ...

You are in a real estate business renting apartments to

You are in a real estate business renting apartments to customers. Your job is to define an appropriate schema using SQL DDL in MySQL. The relations are Property(Id, Address, NumberOfUnits), Unit(ApartmentNumber, Propert ...

Real time analytics - data analytics assignment -this is a

Real Time Analytics - Data Analytics Assignment - This is a business analytics project aimed at generating innovative analytics solutions for a Global Food Consulting firm working in the area of Animal agriculture and fa ...

Question create an erd for the following scenario once you

Question: Create an ERD for the following scenario. Once you submit you will get access to the correct way to create the ERD. Please watch the video and correct any errors in your submission and resubmit. A small company ...

Databases assignment - monash library services monlib case

Databases Assignment - Monash Library Services (MonLib) Case Study TASK 1: Data Definition For this task you are required to complete the following: 1.1 - Add to your solutions script, the CREATE TABLE and CONSTRAINT def ...

Select from e d pwhere edeptddept and dcitypcity and

Select * From E, D, P WHERE E.dept=D.dept AND AND D.budget>1M AND P.priority=A; Assuming relations have the same size and uniform distributions, what is the best plan with Nested Loop joins only? Write it a ...

Question suppose we have two kinds of doctors hospital

Question : Suppose we have two kinds of doctors: hospital doctors and family physicians. In addition to the doctor's id number, name, specialty, and years of experience, we want to record the hospital name for the hospit ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

Why might a bank avoid the use of interest rate swaps even

Why might a bank avoid the use of interest rate swaps, even when the institution is exposed to significant interest rate

Describe the difference between zero coupon bonds and

Describe the difference between zero coupon bonds and coupon bonds. Under what conditions will a coupon bond sell at a p

Compute the present value of an annuity of 880 per year

Compute the present value of an annuity of $ 880 per year for 16 years, given a discount rate of 6 percent per annum. As

Compute the present value of an 1150 payment made in ten

Compute the present value of an $1,150 payment made in ten years when the discount rate is 12 percent. (Do not round int

Compute the present value of an annuity of 699 per year

Compute the present value of an annuity of $ 699 per year for 19 years, given a discount rate of 6 percent per annum. As