Ask Question, Ask an Expert

+61-413 786 465

info@mywordsolution.com

Ask DBMS Expert


Home >> DBMS

Q1. Given the following file for assignment worker.com, 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)

Rules:
- 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

Requirement:
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

Q2

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.

Or

you can create your own tables.

SHOW ALL QUERIES AND OUTPUTS

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 2.zip

DBMS, Programming

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

Priced at Now at $40, Verified Solution

Have any Question? 


Related Questions in DBMS

We can represent a data set as a collection of object nodes

We can represent a data set as a collection of object nodes and a collection of attribute nodes, where there is a link between each object and each attribute, and where the weight of that link is the value of the object ...

The system development team at the xyz company is working

The system development team at the XYZ Company is working on developing a new customer order entry system. In the process of designing the new system, the team has identified the following data entity attributes: Invento ...

You can work on this assignment individually or in a group

You can work on this assignment individually or in a group of 2. If you are working in a group please establish a group in Assignment 2 Group on Canvas In this assignment you are asked to explore the use of neural networ ...

In sql database questions phase-1 in 100 words what steps

In SQL Database Questions: Phase-1 In 100 words, what steps can one take to avoid losing work? Which command is used to save changes to the database? What is the syntax for this command? Phase-2 In 100 words, explain the ...

Project outline and requirements provide a brief

Project Outline and Requirements Provide a brief description of the organization (can be hypothetical) that will be used as the basis for the projects in the course. Include company size, location(s), and other pertinent ...

Your taskyou have been commissioned to develop a database

Your task You have been commissioned to develop a database system that is capable of keeping records for FU's table tennis matches from now on. The database needs to keep a record of: - All team information, including pl ...

Systems analysis project scenic routes operates a bus

Systems analysis project Scenic Routes operates a bus company that specializes in travelling on secondary roads, rather than Interstate highways. Their slogan is: "It Takes a Little Longer, But It's Scenic." The firm nee ...

Q1 given the following file for assignment workercom

Q1. Given the following file for assignment worker.com, identify data anomalies that must be removed before data can be loaded in data warehouse. Worker_assignment ← -----------------on course web site File is available ...

Data mining assignment -in this assignment you are asked to

Data Mining Assignment - In this assignment you are asked to explore the use of neural networks for classification and numeric prediction. You are also asked to carry out a data mining investigation on a real-world data ...

Question team project submission - submit to the unit 4

Question: Team Project Submission - Submit to the Unit 4 Group Project Area This version of the capstone project assignment is FOR GRADING this week. Submit to the group area the document containing completed Sections On ...

  • 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