Data Mining Project

siberiaskeinData Management

Nov 20, 2013 (3 years and 10 months ago)

275 views

0


MINNESOTA STATE UNIV
ERSITY MANKATO

Data Mining Project

FAOES Project


Jacob Ball, Ryan Colakovic, Slade Baumann, Zainab Hamza

12/5/2012






HONR401
-

Data Modeling and Mining

1


Abstract:

The main aim of this project is to analyze different set of FAOES data base, there are
four sets and each set vary in some aspects from the other. FAOES database contains
several tables regarding employee information, their sales records of each employee,
their performance, and other tables related to each brunch. Also it contains fuzzy tables
for sales, and most important part fuzzy decision tables of whether to fire an employee
or reward him/her. There are other decisions as well, but the main purpose of
this
database is to have a decision regarding the employees work performance. The project
consists of three main phases, first phase is studying and analyzing the 4 different
databases through sql queries, the second phase is similar with the use of pivot
tables,
and last phase is recording the presentation and final decision regarding the databases
and employee decisions.









2


Table of Contents

Abstract:

................................
................................
................................
................................
..........

1

Introduction:

................................
................................
................................
................................
....

3

Contents:

................................
................................
................................
................................
.........

4

List of Figures

................................
................................
................................
...............................

15

Figure 1. OES Data model. All FAOES Data models are based off of the basic framework of
this database with added Fuzzy Logic Tables.

................................
................................
..........

15

Figure 2. No of employees are fired in each database

................................
..............................

16

Figure 3. The number of Employees who have overlapping values in the sales table from each
database (FAOES_V2A, FAOES_V2B, FAOES_V2C).

................................
.........................

16

Figure 4. Number of employees in each performance category for orders made in two different
data sets.

................................
................................
................................
................................
....

17

Figure 5. Number of employee in each performance category for orders made out of 70 total
employees.

................................
................................
................................
................................

17

Figure 6. Number of employees in each performance category based on products sold
compared to total number of employees.

................................
................................
..................

18

Figure 7. Number of employees in each sales category for each database.

..............................

18

Figure 8. Number of employees in each decision category for each database.

........................

19

Figure 9. Number of employees who are
fired in each state.

................................
...................

19

Figure 10.The accuracy of data used to analyze employee 1002 performance, comparing
decisions in A and B.

................................
................................
................................
................

20

List of Tables

................................
................................
................................
................................

20

Table 1. Number of Employees who have overlapping values in the sales table from each
Database.

................................
................................
................................
................................
...

20

Table 2. Em
ployee Decisions for each branch in FAOES A database.

................................
...

20

Table 3. Decisions for employees in each state and the number of
employees that decision
affects.

................................
................................
................................
................................
.......

22

Table 4. Number of Employees in each position of the company.

................................
...........

23

Table 5. Number of employees being fired sorted by age in the FAOES_V2A schema.

.........

23

Table 6. Total number of employees and positions for each decision

................................
......

24

Table 7.

Years worked by employees and number of employees for each amount of the
employees who would be fired in the FAOES_V2C schema.

................................
..................

25

Table 8. Total amount to pay for employees who worked more than 15 years

........................

25

Conclusion:

................................
................................
................................
................................
...

27

Appendix:

................................
................................
................................
................................
......

28

Appendix: Queries Used for Analysis with Sample Results

................................
....................

28

3


I
ntroduction:

The main goal for FAOES project is to reach a reasonable and pract
ical decision for the
employees, which they are working in different branches. It is highly important for us to
understand four different databases titled with
FAOES_V2A, FAOES_V2B,
FAOES_V2C, and FAOES_V2E in order to analyze different situations. The pre
vious
database shows that they have the same employee number, however they differ in the
way they evaluate each
employee’s

performance. By that they have different ways of
making a decision or multiple decisions for each employee.

The project topic was chosen by Dr.Cyrus Azarbod in order to differentiate between the
databases. And recommend to him the best database with fuzzy decision that will give
the employees a fair decision. This project is important because it will allow us ap
ply the
data mining concepts and try to relate different pieces of information, in order to reach
to the right database. To make the decision we will apply different sets of queries on
each database to find some kind of

statistics and groups of facts. Also

we will
use pivot
table analysis to find the main changes from one database to another.










4


Contents:

Part A

1. Which Employees should be fired?

We wrote a query that shows which employees should be fired using the fuzzy decision
table. When the
decision was ‘fire employee’ or ‘fire’, we added that employee to our list
of fired employees. We did not take any of the employee information into account.

2. Which employees have overlapping values in fuzzy sales table?

We wrote a query that shows the em
ployees that could be assigned to two different
performance categories. For example, an employee could be in the poor AND below
-
average category for sales. Figure 1 is a pivot table that shows how many employees
had overlapping values in each of the three
instances of the fuzzy OES database.

3. How many employees are in each of the orders categories?

This query is to find out how many employees are in each of the performance
categories. The orders table shows that the employees’ performance is based on how
many orders they get. Figure 2 (not official) shows how many employees are in each
category, for example, there are 40 employees in the below
-
average in both data sets
for Version A, which is more than 50 percent of the employees in that database.

4. How m
any employees are in each of the products categories?

This query is to find out how many employees are in each of the performance
categories. The products table shows that the employees’ performance is based on how
many products they sell. Figure 3 (not of
ficial) shows how many employees are in each
category, for example, a majority of employees are in the poor category, and there is a
very small margin in the excellent category. This means that using Version A, only a few
5


of the employees had excellent per
formance according to how many product they sold.

5. How many employees are in each of the sales categories?

This query is to find out how many are in each of the performance categories. The
products table shows that the employees’ performance is based on
the number of sales
they have. Figure 4 (not official), which is a query of version A, shows how many
employees are in each category, for example, many of the employees are in the poor
category and there are only a few employees in the excellent category.
Only a few of
the employees had excellent performance according to how many product they sold.
Also, memory set 101 has different values for below average and average than memory
set 201.

6. In the decision table, how many employees are in each category?

W
hen we did this query, we grouped by the memory set. This showed us a spread of
decisions for each memory set and how many employees were affected by each
decision. Figure 5(not done) shows an example from FAOES version A, where you can
see that memory set

101 and 201 do not differ much, but have a different decision for a
few of the employees. Some of the versions of FAOES have decisions for employees
that vary more from memory set to memory set.

Part B

1. How are the rules going to affect our branches?


We found the decision and number of employees for the decision in each branch, and
used these to analyze how each branch was affected.


FAOES_V2A

In our analysis of FAOES_V2A, we found that for branches 100,101,102 there are more
6


than 10 employees being fi
red, which is a majority of their working employees. There
are too many people being fired and it would negatively affect these branches. For
branches 105,106,107,108 no one is being fired. Some of these branches have average
to above average performance,
but others are warning employees, meaning they could
be fired in the future for similar performance. For branches 109,110,111,112,113 the
people being fired are a minority of the working employees on that particular branch.
This could be a reasonable actio
n to take for these branches.

In Branch 114, everyone
is being fired. By these rules, no employees would be left in this branch and it could
eliminate the branch itself.

FAOES_V2B

In our analysis of FAOES_V2B, we found that in branches 100 and 101 there a
re more
than 70 employees being fired, which is a majority. Firing that many employees would
cause the branch to need to hire a lot of new employees quickly or most likely the
branch would close. In branches 102,103,104,105,106,107 employees that are given

more raises and gifts outnumber the employees being fired. This would suggest that
many of the employees are selling a lot of product. There is a problem in this table,
where the categories of decisions are being repeated because of a type error. We did
t
ake that into account during our analysis however.

FAOES_V2C

In our analysis of FAOES_V2C, we found that there are far less people being fired than
the previous two versions of FAOES. Many of the firing decisions have been changed to
warnings from version
B to version C. Most of the branches fire less than half of their
employees, which is a good way to keep the branch in working shape, while getting rid
7


of the people who aren’t doing a good job. In branch 114, there are twelve employees
being fired. This w
ould seem like a lot, but in version A and B, branch 114 pretty much
fires all of their employees. This version seems to be a lot more lenient on the
employees compared to the previous two versions, and in turn, rewards many of the
employees.

2. What is th
e impact of employee performance rules and membership values
affecting us in each state?



V2A

In version V2A, there are too many people being fired from many of the states. Of 26
total employees in Minnesota for example, both data sets decided that 20 emp
loyees
should be fired. This could be due to the poor quality of the employee’s sales values
that did not properly factor in the potential customers in that area, or because the values
that give above average and excellent are too high, and both belong in
excellent. Firing
20 people would not be good for the branch, because it would be tough to quickly
replace that many people. Other States affected in similar ways include Idaho (Fire all
employees) and Nevada (fire 3 of 5). The remaining states have more r
easonable
decisions, such as Colorado (Fire 1 of 5), but overall these decisions would fire too
many employees in many areas.


V2B

The V2B database has similar values to the first, but has added mem_set 102, 103,
202, 203 that have more reasonable values,
but still seem to fire too many employees.
Only half of the Minnesota employees are slated to be fired, but some employees have
overlapping values in the decision table. For example, out of 47, only 19 should be fired.
8


This is still a large number, but a m
ore reasonable one. This database lowers the
amount of employees being fired from version A, but would still cause problems for
some of the branches.


V2C

In version C of FAOES, the total number of people is lower, most likely because their
position is put

into account.

For the first mem_sets (101, 201), the amount of people
fired is much more reasonable. Minnesota, for example, has 4 people being fired out of
25, with others receiving warnings.

The difference in these values could be due to the
people wh
o are evaluated and it could also have more accurate values for each fuzzy
category. With this model, the amount of people being fired is much lower. However, it
appears that in this model, many people are receiving raises and some raises and gifts.
This i
s not a bad thing, but giving too many people these things could be ineffective in
trying to make money if not everyone deserves it. Especially in the newest mem_sets
(102, 103, 202, 203), far too many people are receiving raises. In Minnesota, half of the

employees receive a raise following the fuzzy mem_set 203. Version C keeps many
employees, and rewards many more for doing well in sales.


V2E

FAOES version E database compiles the values for other databases and can compare
the data by which schema the in
formation comes from. The main difference/advantage
of the newer data sets is that they offer a greater variety of options. They specify how
the warning will work, what percentage of raise and possible gifts. These different
schema show similar results as
past mem_sets, many fire too many people and many
others give raises to too many people.

Some of the newer ones have less decisions
9


that say “No Decision” as well that can be positive or negative. It is positive since it
gives an actual decision for each
data type. It is negative because not every employee
needs negative or positive reinforcement, average employees should have little to no
change in their wages. Some are better that the first mem_sets, such as faoes_v3b
which for Minnesota has 3 fired, 5 g
iven a warning and 7 receiving a raise (two different
kinds). This seems to be a very reasonable course of action, and many of the other
states have more reasonable decisions for their employees in this schema.

3. How accurate is our original data that we
have used to analyze employee's
performance?

In order to compare TOTAL_SALES of different databases, we chose an employee and
find the difference of his total sales in each FAOES database. The employee 1002 in
FAOES_v2a has total sales of 1,158,350 with a
weight .53666, which makes him in the
poor category. However, the same employee total sale in FAOES_v2c is 4,160,045 and
is in below_average or average category depending on different sets.

By that, we believe that calculating the total sales in each datab
ase has a major
influence on the final decision for each employee. Because a salesperson should be
evaluated based on the gross sales which is the total amount he/she has sell without
the deduction of customer discounts, returns, and allowances. So the dec
ision should
be based on the amount of effort of what each employee does, which is his/her gross
sales.

Also to compare the total sales of different sets, the two main attributes that each
employee is evaluated based are total products and total orders. wh
ich is the base to
have the total sales, it is calculated through knowing the total products for each
10


employee and the total orders to that employee.

Another point which we must consider in this part, we found that there is no specific
criteria to analyze
the employees performance, each membership differ in their fuzzy
attribute, for example in TRAPEZOID poor is 0 for membership a, b, and d however, it is
5 for membership c. also excellent is 1 million for membership c. on the other hand in
the triangle fuz
zy table they only focus on a, b, and c. and membership c is always differ
from a and b with a very high range.

4. Are we considering all employees or only people that are actually in sales?

We (should be) are considering only those who are in sales, becau
se the amount of
sales they had only applies to people who are actively involved in selling products and
dealing with customer in a direct way. In each version the consideration differs, for
example, in version A they are considering nearly everyone in the

decision table which
is wrong. It shows that the CEO is being fired as well as many other employees who are
not in sales. The CEO cannot be in the sales team and cannot have sales records
unless he used to work in sales, however the focus should be only o
n people with sales.
Database B follows a similar format, as the same people are in the Decision
table.

Database C and E do not have the CEO in the decision table, and only the
employees involved in sales are included. However, Database E gives a
dispropo
rtionate amount of employees raises which we believe would not be beneficial
to the company. That’s why we consider database C as the best because only the
people in sales are accounted for and less people are given raises, which might
negatively affect fu
ture growth of the company. Database C is focusing on the gross
sale of the sales people only, not the employees who are in non
-
sales positions in the
11


company.

5. Are we considering the age of employees in our decision? How does our
decision affect People
working more than 10 years service or close to retirement?

The image of the company has an important effect when it comes to the decision, and
we noticed that most people who are fired are very experienced people who have
worked for a long time in the comp
any. Most of the employees being fired in database A
have been working for more than 10 years, for example the most experienced person
who is being fired was born in 1931 and has worked at the company for 55 years, so he
one of the oldest and most experien
ced members of the company and could retire soon.
Of the employees who would be fired, many of the other employees are over 50 years
old and have been working for more than 25 years. Database B has the same people
being fired as database A. Database C does

not have as many people being fired, and
as a result, less people who are old and have worked more than 10 years are being
fired. Database E has many of the same people being fired as other databases since it
pulls data from several schemas. It appears th
at none of these databases factor in age
and years worked at the company. However, if we would like to follow a model that does
not fire many people who are close to retirement and have worked in the company for a
long period of time, database C would be o
ur best choice. Although the decision to fire
these employees may be justified on their sales performance, it could make the image
of the company worse and generate fewer sales. The loss of these employees means
we would also lose considerable experience t
hat newly hired employees would not
provide.

6. What is the impact for those employees fired if we have to pay $2,000 for each
12


year worked over 15 years of service?

By maximizing number of people that are fired in some databases, that will affect the
compa
ny. And the company will lose money instead of gain because they have to pay
the compensation to those employees. However, this can be avoided by not firing the
majority of the people in some databases. For some people, it is better to allow them to
retire

instead of firing them as they have worked a great number of years, accumulating
a very big compensation, and are close to retirement. Even though they are
recommended to be fire, they should not be as it would cost the company too much and
they are very
close to retirement anyways. On the other side, there are people who are
recommended be fired that have only worked slightly over fifteen years and are not
close to retirement. These people should be fired as doing so would save the company
money. Other pe
ople we could consider firing are people who have worked for a
substantial time in the company, but are not close to retirement. Even though we may
have to pay them a large compensation, the long run benefit in firing these employees
would pay off. Since m
ore people in this category are being fired in databases A and B,
the cost of firing these employees is very high, in database A, it would cost our
company more than 600,000 dollars. In comparison, database C would still cost the
company a considerable amo
unt, but it would be much less, around 160,000 dollars.
Database C would give a more reasonable decision on whom to fire, but it would be
good to consider the individuals case by case and may be more beneficial to fire
employees who are not close to retire
ment and would have a lower compensation.

7. Do the rules affect all employees or are there some employees that our rules do
not affect?

13


There are exceptions for the rules that governed the way that decisions were made for
the employees. Since many of our
rules were based on sales, only those who are in
sales should be affected by them. Someone who does not actively sell product as part
of their job should not be penalized for lack of sales. One example is the CEO should
not be included in our evaluation. N
o single person in the company can fire the CEO as
the CEO runs the company. There are some individuals that should have been
included. This may be out of an error, but some customer service employees were not
included in the decision table. Also, there ar
e some individuals who should not be
included in our decision tables, this includes positions that are not related to sales, such
as Branch Manager, CEO, Technician, Programmer, and Accountant. Since many of
our rules were based on sales, only those who ar
e in sales should be affected by them.
Someone who does not actively sell product as part of their job should not be penalized
for lack of sales. Although there are sales associated with every employee, even those
that have positions that are not directly
involved with sales, this could be faulty data.
Another possibility is that all employees included in the database have held a position
that involved sales at one point and the amount of sales they have even made at the
company will stay with them no matte
r which position they hold.

In version A and B, there are customer service and sales representative employees that
are not within the fuzzy decisions table. These employees are directly involved in sales,
therefore they should be in our decision table. Da
tabase C has more people who are
not included in the sales decision table, and many of these individuals are not in sales.
However, there are some employees in this table that should be included in the sales
decision table such as a person in Customer Serv
ice. Database C is better than A and
14


B because it does not include the CEO, Branch Manager, and other employee positions
that it should not include. Although some people who should be graded on their sales
performance are not, the database C shows the best

representation of which individuals
should not be included in the decision making based on sales.




















15


List of Figures

Figure 1. OES Data model.

All FAOES Data models are based off of the
basic framework of this database with added Fuzzy Logic Tables.










16



Figure 2. No of employees are fired in each database








Figu
re 3
. The number of Employees who have overlapping values in the
sales

table from each database (FAOES_V2A, FAOES_V2B, FAOES_V2C).






36

39

11

41

0
5
10
15
20
25
30
35
40
45
Total
a
B
C
E
14

36

10

Total

a
b
c
17





Figure 4
. Number of employees in each performance category for orders
made in two different data sets.





Figure
5
. Number of employee in each performance category for orders
made out
of 70 total employees.




0
10
20
30
40
50
60
70
80
Mem_Set 101
Mem_Set 201
2

40

16

8

7

POOR
BELOW AVG
AVG
ABOVE AVG
EXCELLENT
18




Figure 6
. Number of employees in each performance category based on
products sold compared to total number of employees.


Figure 7. Number of employees in each sales category for each
database.


0
10
20
30
40
50
60
70
80
0
20
40
60
80
100
120
140
160
180
A
B
C
Sum of POOR
Sum of
BELOW_AVERAGE
Sum of AVERAGE
Sum of
ABOVE_AVERAGE
Sum of EXCELLENT
19





Figure 8. Number of emplo
yees in each decision category for each
database.




Figure
9
. Number of employees who are fired in each state.








73

346

60

6

48

66

21

165

199

2

74

32

10

38

197

171

0
200
400
A
B
C
Fire Employee
Give Raise and Gift to Employee
Give Raise to Employee
Give Warning to Employee
Give warning toEmployee
NO DECISION
AZ, 4

CA, 3

CO, 2

DC, 4

ID, 8

IL, 4

MN, 40

NV, 6

OR, 2

20



Figure 10
.The accuracy of data used to analyze employee 1002
performance, comparing decisions in A and B.






List of Tables

Table
1. Number of Employees who have overlapping values in the sales
table from each Database.


Database

Employees

a

14

b

36

c

10

Grand Total

60

Table 2. Employee Decisions for each branch in FAOES A database.


Row Labels

Sum of
COUNT(E.EMPLOYEE_NO)

100

168

Fire Employee

98

Give Raise and Gift to Employee

6

Give Raise to Employee

24

Give Warning to Employee

4

Give warning toEmployee

4

0
5
10
15
20
25
1002
4160045
Different Decisions for employee
1002 in different DBs

Sum of POOR
Sum of
BELOW_AVERAGE
Sum of POOR2
Sum of
BELOW_AVERAGE2
Sum of AVERAGE
21


NO DECISION

32

101

112

Fire Employee

86

Give Warning to Employee

10

NO DECISION

16

102

84

Fire Employee

46

Give Raise to Employee

10

Give Warning to Employee

4

NO DECISION

24

103

56

Fire Employee

20

Give Raise to Employee

20

Give Warning to Employee

4

Give warning toEmployee

4

NO DECISION

8

104

56

Fire Employee

16

Give Raise to Employee

24

Give
Warning to Employee

6

Give warning toEmployee

2

NO DECISION

8

105

28

Give Raise and Gift to Employee

28

106

42

Give Raise to Employee

18

NO DECISION

24

107

28

Give Raise to Employee

11

NO DECISION

17

108

28

Give Raise and Gift to Employee

14

Give Warning to Employee

14

109

42

Fire Employee

21

Give Raise to Employee

4

Give Warning to Employee

5

NO DECISION

12

110

70

Fire Employee

38

Give Raise to Employee

18

22


Give Warning to Employee

2

NO DECISION

12

111

56

Fire Employee

12

Give
Raise to Employee

4

Give Warning to Employee

14

NO DECISION

26

112

84

Fire Employee

20

Give Raise and Gift to Employee

6

Give Raise to Employee

33

Give Warning to Employee

7

NO DECISION

18

113

70

Fire Employee

18

Give Raise to Employee

20

Give
Warning to Employee

6

NO DECISION

26

114

56

Fire Employee

44

NO DECISION

12

Grand Total

980

Table 3. Decisions for employees in each state and the number of
employees that decision affects.


Sum of NO_PEOPLE

State















Decision

AK

AZ

C
A

CO

DC

DE

FL

HI

ID

IL

MN

NV

OR

VA

Gra
nd
Tot
al

4% raise











1




1

5% raise


4

3

5

3


4

1

1

4

16

5

3

2

51

7% raise wt
h $100 gift
certificate







1







1

2

7% raise wt
h $300 gift
certificate


1


1







1




3

7% raise wth $500 gift
certificate

2

2

1

1


2

3




2




13

7% raise wt
h $700 gift
certificate

1


1

1


2

1




1




7

Fire


3

3

1

3




4

4

26

4

3


51

23


Fire Employ
ee


3

3

1

3




4

4

26

4

3


51

Give Raise
and Gift to Employee

3

3

2

3


4

5




4



1

25

Give Raise to Employee


4

3

5

3


4

1

1

4

17

5

3

2

52

Give warnin
g to Employee

1

1

1

2

1


1


5

1

9

2

4


28

NO DECISION



2







2





4

Warning with 3 month review













1


1

Warning wit
h 6 month review

1

1

1

2

1


1


5

1

9

2

3


27

Grand Total

8

22

20

22

14

8

20

2

20

20

11
2

22

20

6

316


Table 4. Number of Employees in each position of the company.


Employee Position

Count of EMPLOYEE_NO

Accountant

5

Branch Manager

17

CEO

1

Customer Service

14

Customer Service Manager

2

Programmer

6

Sales Manager

6

Sales Representative

30

Technician

9

Grand Total

90

Table 5
. Number of employees being fired sorted by age in the
FAOES_V2A schema.


Age

Employees

31

1

32

1

33

3

35

1

36

3

37

2

38

1

24


39

2

42

1

43

1

44

1

45

1

46

2

47

2

48

1

49

2

51

2

52

1

53

2

54

1

56

1

57

1

58

2

77

1

81

1

Grand Total

37

Table 6. Total number of employees and positions for each decision


Row Labels

Count of
EMPLOYEE_NO

Fire Employee

73

Accountant

2

Branch Manager

20

CEO

2

Customer Service

12

Programmer

8

Sales Manager

8

Sales Representative

17

Technician

4

Give Raise and Gift to Employee

6

Branch Manager

2

Sales Representative

4

Give Raise to Employee

21

Accountant

2

Branch Manager

2

Customer Service

2

Sales Manager

1

Sales Representative

14

Give Warning to Employee

2

25


Branch Manager

2

NO DECISION

38

Accountant

2

Branch Manager

6

Customer Service

4

Customer Service Manager

2

Sales Manager

1

Sales Representative

23

Grand Total

140

Table 7.

Years worked by employees and number of employees for each
amount of the employees who would be fired in the FAOES_V2C schema.


Years Worked

Employees

6

1

8

1

10

2

14

1

15

1

22

1

25

1

26

1

27

1

54

1

Grand Total

11

Table 8.

Total amount to pay for employees who worked more than 15
years


Row Labels

Sum of Firing
Bonus

1000

43142.74

36

43142.74

1001

40153.5

35

40153.5

1002

33148.12

31

33148.12

1004

33148.12

31

33148.12

1006

33148.12

31

33148.12

1008

21153.5

26


25

21153.5

1010

29148.12

29

29148.12

1012

30158.87

30

30158.87

1013

21153.5

25

21153.5

1015

25153.5

27

25153.5

1016

22164.25

26

22164.25

1019

25153.5

27

25153.5

1020

22164.25

26

22164.25

1021

19158.87

24

19158.87

1023

19158.87

24

19158.87

1024

12166.66

21

12166.66

1025

5164.25

17

5164.25

1026

2180.38

16

2180.38

1027

11164.25

20

11164.25

1030

14166.66

22

14166.66

1031

1166.66

15

1166.66

1034

180.38

15

180.38

1075

1166.66

15

1166.66

1086

79218.01

54

79218.01

1088

81218.01

55

81218.01

Grand Total

625099.75


27


C
onclusion:


Analyzing different databases in order to know the best database is an interesting thing
to do, at the same time keeping in mind that those decisions (if it was a real database)
will affect real employees, so it was very important for us to analyze it care
fully. It is also
important to find out the right decision of firing employees.

Based on the comparison we think that database A has lots of bad decisions in it and it
is the worst database to follow in this case, especially the decision on their total sa
les,
also number of employees that been fired. So it is important to consider other factors as
well. However, database C has decision made based on reasonable factors such as the
gross sale, which we guess the sales employees deserve to be rated on their t
otal sales
(gross sales) and that’s the main element that made us chose this database. Database
E, is fairly good but giving raise to everyone will make the company lose lots of money
as well, it gives raises to many employees, and have no decision about t
he majority.

We faced few issues during this project because of the time issue, we think that if we
had more time we would have had better understanding of different sets of databases,
also we would know have more factors and reasons behind each decision
in each
database.













28


A
ppendix
:


Appendix:
Queries Used for Analysis

with Sample Results


1.select unique lname, fname from FAOES_V2A.EMP_FUZZY_DECISION


where decision='Fire Employee';


LNAME


FNAME

Blazek
-
White


David

Bullit


Harrison

Doering


Thomas

Dupont


George

Halle


Donald

Hanzdo


Lee

Holman


Judy

Jenkins


Dusky

Katz


Sheela

Keting


Charles

McKissick


Doris

McMillan


Don



select unique lname, fname from FAOES_V2B.EMP_FUZZY_DECISION


where decision='Fire Employee';


select

unique lname, fname from FAOES_V2C.EMP_FUZZY_DECISION


where DECISION_GROSS ='Fire Employee';



select unique lname, fname from FAOES_V2E.EMP_MINING_MV where DECISION_NET
='Fire Employee';



2.select employee_no, fname, lname from FAOES_V2A.emp_fuzzy_sale
s

where (poor > 0 and below_average > 0)

or (below_average > 0 and average > 0)

or (average > 0 and above_average > 0)

or (above_average > 0 and excellent > 0);



EMPLOYEE_NO

FNAME

LNAME

1003

Bruce

Wooton

1059

Catherine

Beckley

1071

Edward

Claggett

1003

Bruce

Wooton

1021

Steward

Nelson

29


1029

Edward

Martin

1045

Willard

Doering

select employee_no, fname, lname from FAOES_V2B.emp_fuzzy_sales

where (poor > 0 and below_average > 0)

or (below_average > 0 and average > 0)

or (average > 0 and
above_average > 0)

or (above_average > 0 and excellent > 0);



select employee_no, fname, lname from FAOES_V2C.emp_fuzzy_sales

where (poor > 0 and below_average > 0)

or (below_average > 0 and average > 0)

or (average > 0 and above_average > 0)

or (above_av
erage > 0 and excellent > 0);


f
aoes_v2e has no values.



3. select mem_set, count(employee_no), sum(case when poor > 0 then 1 else 0 end) as
Num_Emp_Poor,

sum(case when below_average > 0 then 1 else 0 end) as Num_Emp_Below_Avg,

sum(case when average >

0 then 1 else 0 end) as Num_Emp_Avg,

sum(case when above_average > 0 then 1 else 0 end) as Num_Emp_Above_Avg,

sum(case when excellent > 0 then 1 else 0 end) as Num_Emp_Excellent

from faoes_v2a.emp_fuzzy_orders group by mem_set;




MEM_SET

ALL
EMPLOYEES

POOR

BELOW
AVG

AVG

ABOVE
AVG

EXCELLENT

101

70

2

40

16

8

7

201

70

2

40

16

8

7



select mem_set, count(employee_no),


sum(case when poor > 0 then 1 else 0 end) as Num_Emp_Poor,

sum(case when below_average > 0 then 1 else 0 end) as Num_Emp_Below_Avg,

sum(case when average > 0 then 1 else 0 end) as Num_Emp_Avg,

sum(case when above_average > 0 then 1 else 0 end) as Num_Emp_Above_Avg,

sum(case when excellent > 0 then 1 else 0 end) as Num_Emp_Excellent from
faoes_v2b.emp_fuzzy_orders group by mem_set;


s
elect mem_set, count(employee_no),

sum(case when poor > 0 then 1 else 0 end) as Num_Emp_Poor, sum(case when below_average >
0 then 1 else 0 end) as Num_Emp_Below_Avg,

sum(case when average > 0 then 1 else 0 end) as Num_Emp_Avg,

sum(case when
above_average > 0 then 1 else 0 end) as Num_Emp_Above_Avg,

sum(case when excellent > 0 then 1 else 0 end) as Num_Emp_Excellent

30


from faoes_v2c.emp_fuzzy_orders group by mem_set;




faoes_v2e has no values



4. select mem_set, count(employee_no), sum(case
when poor > 0 then 1 else 0 end) as
Num_Emp_Poor,

sum(case when below_average > 0 then 1 else 0 end) as Num_Emp_Below_Avg,

sum(case when average > 0 then 1 else 0 end) as Num_Emp_Avg,

sum(case when above_average > 0 then 1 else 0 end) as Num_Emp_Above_A
vg,

sum(case when excellent > 0 then 1 else 0 end) as Num_Emp_Excellent

from faoes_v2a.emp_fuzzy_products group by mem_set;


MEM_SET

ALL
EMPLOYEES

POOR

BELOW
AVG

AVG

ABOVE
AVG

EXCELLENT

101

70

45

15

7

4

3

201

70

45

16

7

4

3



select mem_set, count(
employee_no),

sum(case when poor > 0 then 1 else 0 end) as Num_Emp_Poor, sum(case when below_average >
0 then 1 else 0 end) as Num_Emp_Below_Avg,

sum(case when average > 0 then 1 else 0 end) as Num_Emp_Avg,

sum(case when above_average >

0 then 1 else 0 end) as Num_Emp_Above_Avg,

sum(case when excellent > 0 then 1 else 0 end) as Num_Emp_Excellent

from faoes_v2b.emp_fuzzy_products group by mem_set;


select mem_set, count(employee_no),

sum(case when poor > 0 then 1 else 0 end) as Num_Em
p_Poor, sum(case when below_average >
0 then 1 else 0 end) as Num_Emp_Below_Avg,

sum(case when average > 0 then 1 else 0 end) as Num_Emp_Avg,

sum(case when above_average > 0 then 1 else 0 end) as Num_Emp_Above_Avg,

sum(case when excellent > 0 then 1 els
e 0 end) as Num_Emp_Excellent

from faoes_v2c.emp_fuzzy_products group by mem_set;


faoes_v2e has no values




5
)
select mem_set, count(employee_no), sum(case when poor > 0 then 1 else 0 end) as
Num_Emp_Poor, sum(case when below_average > 0 then 1 else 0 e
nd) as
Num_Emp_Below_Avg, sum(case when average > 0 then 1 else 0 end) as Num_Emp_Avg,
sum(case when above_average > 0 then 1 else 0 end) as Num_Emp_Above_Avg, sum(case
when excellent > 0 then 1 else 0 end) as Num_Emp_Excellent from
faoes_v2a.emp_fuzzy_sal
es group by mem_set;



31





MEM_SET

ALL
EMPLOYEES

POOR

BELOW
AVG

AVG

ABOVE
AVG

EXCELLENT

101

70

31

20

11

5

6

201

70

31

23

16

5

6




select mem_set, count(employee_no), sum(case when poor > 0 then 1 else 0 end) as
Num_Emp_Poor, sum(case when below_average
> 0 then 1 else 0 end) as
Num_Emp_Below_Avg, sum(case when average > 0 then 1 else 0 end) as Num_Emp_Avg,
sum(case when above_average > 0 then 1 else 0 end) as Num_Emp_Above_Avg, sum(case
when excellent > 0 then 1 else 0 end) as Num_Emp_Excellent from
faoe
s_v2b.emp_fuzzy_sales group by mem_set;


select mem_set, count(employee_no), sum(case when poor > 0 then 1 else 0 end) as
Num_Emp_Poor, sum(case when below_average > 0 then 1 else 0 end) as
Num_Emp_Below_Avg, sum(case when average > 0 then 1 else 0 end) as

Num_Emp_Avg,
sum(case when above_average > 0 then 1 else 0 end) as Num_Emp_Above_Avg, sum(case
when excellent > 0 then 1 else 0 end) as Num_Emp_Excellent from
faoes_v2c.emp_fuzzy_sales group by mem_set;


faoes_v2e has no values



6)
select fuzzy_mem_set,
decision,

count(employee_no) Employees

from faoes_v2a.EMP_FUZZY_DECISION

group by fuzzy_mem_set, decision;


FUZZY_MEM_SET

DECISION

EMPLOYEES

101

NO DECISION

19

101

Fire Employee

37

101

Give Raise to Employee

10

101

Give Warning to Employee

1

101

Give Raise and Gift to Employee

3

201

NO DECISION

19

201

Fire Employee

36

201

Give Raise to Employee

11

201

Give Warning to Employee

1

201

Give Raise and Gift to Employee

3



select fuzzy_mem_set, decision, count(employee_no) Employees

from

faoes_v2b.EMP_FUZZY_DECISION

32


group by fuzzy_mem_set, decision;


select fuzzy_mem_set, decision_gross, count(employee_no) Employees

from faoes_v2c.EMP_FUZZY_DECISION

group by fuzzy_mem_set, decision_gross;


select schema, decision_net, count(employee_no)

from FAOES_V2E.EMP_MINING_MV

group by schema, decision_net;


Part b:

1

select Branch_no, decision, count(e.employee_no)

from faoes_v2a.EMP_FUZZY_DECISION
ef ,faoes_v2a.employee e

where ef.employee_no= e.employee_no

group by branch_no, Decision ;


BRANCH_N
O

DECISION

COUNT(E.EMPLOYEE_NO)

100

NO DECISION

4

100

Fire Employee

16

100

Give Raise to Employee

4

101

NO DECISION

2

101

Fire Employee

14

102

Fire Employee

10

102

Give Raise to Employee

2

103

NO DECISION

2

103

Fire Employee

4

103

Give Raise to
Employee

2



select Branch_no, decision, count(e.employee_no)

from faoes_v2b.EMP_FUZZY_DECISION
ef ,faoes_v2b.employee e

where ef.employee_no= e.employee_no

group by branch_no, Decision;


select Branch_no, decision_gross, count(e.employee_no
)

from
faoes_v2c.EMP_FUZZY_DECISION ef ,faoes_v2a.employee e

where ef.employee_no= e.employee_no

group by branch_no, Decision_Gross ;


select Branch_no, decision_net, count(e.employee_no)

from faoes_v2e.emp_mining_mv ef
,faoes_v2a.employee e

where ef.emp
loyee_no= e.employee_no

group by branch_no, Decision_Net ;




2 select fuzzy_mem_set, e.state, decision,

33


count(distinct e.employee_no)as No_People

from faoes_v2a.emp_fuzzy_decision f, faoes_v2a.employee e


where f.employee_no=e.employee_no


group by
fuzzy_mem_set, state, decision;


FUZZY_MEM_SET

ST

DECISION

101

AK

Give Warning to Employee

101

AK

Give Raise and Gift to Employee

101

AZ

NO DECISION

101

AZ

Fire Employee

101

AZ

Give Raise to Employee

101

CA

NO DECISION

101

CA

Fire Employee

101

CO

NO DECISION

101

CO

Fire Employee

101

CO

Give Raise to Employee



select fuzzy_mem_set, e.state, decision, count(distinct e.employee_no)as No_People from
faoes_v2b.emp_fuzzy_decision f, faoes_v2b.employee e

where f.employee_no=e.employee_no


group by
fuzzy_mem_set, state, decision;


select fuzzy_mem_set, e.state, decision_gross,

count(distinct e.employee_no)as No_People


from faoes_v2c.emp_fuzzy_decision f, faoes_v2b.employee e

where f.employee_no=e.employee_no


group by fuzzy_mem_set, state,
decision_gross;


select schema, e.state, decision_net, count(distinct e.employee_no)as No_People from
faoes_v2e.emp_mining_mv f, faoes_v2a.employee e

where f.employee_no=e.employee_no

group by schema, state, decision_net;



3
.

select

* from FAOES_V2A.FUZZY_PARAM_TRAPEZOID;


MEM SET

PK

STATUS

ATTRIBUTE

NAME

FUZZY

NAME

RANGE

LOW

RANGE HIGH

MEMBERSHIP A

MEMBERSHIP B

MEMBERSHIP C

MEMBERSHIP
D

201

1

1

ORDERS

POOR

0

9

0

0

5

9

201

2

1

ORDERS

BELOW_AVG

8

20

8

12

16

20

201

3

1

ORDERS

AVG

18

30

18

22

26

30

201

4

1

ORDERS

ABOVE_AVG

28

39

28

31

36

40

201

5

1

ORDERS

EXCELLENT

39

200

39

45

50

1000000



select * from FAOES_V2B.FUZZY_PARAM_TRAPEZOID;

34



select * from FAOES_V2C.FUZZY_PARAM_TRAPEZOID;


FAOES_V2E has no values.



4.
select
fuzzy_mem_set,
e.employee_no,

e.fname,

e.lname,

p.position,

p.description,

decision

from FAOES_V2A.employee e,FAOES_V2A.employee_position p,
faoes_v2a.emp_fuzzy_decision f

where p.position = e.position and e.employee_no = f.employee_no

order by
fuzzy_mem_
set,
position, decision


FUZZY
MEM
SET

EMPLOYEE

NO

FNAME

LNAME

POSITION

DESCRIPTION

DECISION

101

1000

Stefan

Wyatt

1

CEO

Fire
Employee

101

1001

Donald

Wright

2

Branch
Manager

Fire
Employee

101

1012

Bill

Somers

2

Branch
Manager

Fire
Employee

101

1079

James

Wooton

2

Branch
Manager

Fire
Employee

101

1075

George

Reed

2

Branch
Manager

Fire
Employee

101

1052

Stefan

Worral

2

Branch
Manager

Fire
Employee

101

1021

Steward

Nelson

2

Branch
Manager

Fire
Employee

101

1035

James

Holman

2

Branch
Manager

Give
Raise
and Gift to
Employee

101

1087

Jullian

Wyatt

2

Branch
Manager

Give Raise to
Employee

101

1041

Lee

Forman

2

Branch
Manager

Give
Warning to
Employee



select fuzzy_mem_set, e.employee_no, e.fname, e.lname, p.position, p.description, decision

from

FAOES_V2b.employee e,FAOES_V2b.employee_position p,
faoes_v2b.emp_fuzzy_decision f

where p.position = e.position and e.employee_no = f.employee_no

order by fuzzy_mem_set, position, decision;


select fuzzy_mem_set, e.employee_no, e.fname, e.lname, p.po
sition, p.description,
decision_gross

from FAOES_V2a.employee e,FAOES_V2a.employee_position p,
faoes_v2c
.emp_fuzzy_decision f

35


where p.position = e.position and e.employee_no = f.employee_no

order by fuzzy_mem_set, position, decision
_gross
;


select schema
,
e.employee_no, e.fname, e.lname, p.po
sition, p.description, decision_net

from FAOES_V2a.employee e,FAOES_V2a.employee_position p, faoes_v2e.emp_mining_mv

f

where p.position = e.position and e.employee_no = f.employee_no

order by schema
, position, decision
_
net
;



5.
select e.employee_no,e.fname,e.lname,e.dob, floor((sysdate
-

e.dob)/365.25) age,
floor((sysdate
-

e.start_date)/365.25) years_worked

from FAOES_V2A.employee e, FAOES_V2A.emp_fuzzy_decision d

where e.employee_no = d.employee_no

and d.decision = '
Fire Employee'

group by e.employee_no,e.fname,e.lname,e.dob, e.start_date

order by e.dob


EMPLOYEE_NO

FNAME

LNAME

DOB

AGE

YEARS_WORKED

1088

Lee

Hanzdo

30
-
APR
-
31

81

55

1086

Judy

Holman

29
-
APR
-
35

77

54

1000

Stefan

Wyatt

12
-
MAY
-
54

58

36

1001

Donald

Wright

10
-
NOV
-
54

58

35

1002

Al

Worral

12
-
MAY
-
55

57

31

1004

Albert

Widdes

11
-
MAY
-
56

56

31

1008

James F.

Stone

11
-
MAY
-
58

54

25

1010

Thomas

Stansbury

11
-
MAY
-
59

53

29



select e.employee_no,e.fname,e.lname,e.dob, floor((sysdate
-

e.dob)/365.25) age, floor((
sysdate
-

e.start_date)/365.25) years_worked

from FAOES_V2B.employee e, FAOES_V2B
.emp_fuzzy_decision d

where e.employee_no = d.employee_no

and d.decision = 'Fire Employee'

group by e.employee_no,e.fname,e.lname,e.dob, e.start_date

order by e.dob


select e
.employee_no,e.fname,e.lname,e.dob, floor((sysdate
-

e.dob)/365.25) age, floor((sysdate
-

e.start_date)/365.25) years_worked

from

FAOES_V2A.employee e, FAOES_V2C
.emp_fuzzy_decision d

where e.employee_no = d.employee_no

and d.decision
_gross

= 'Fire Employe
e'

group by e.employee_no,e.fname,e.lname,e.dob, e.start_date

order by e.dob


select e.employee_no,e.fname,e.lname,e.dob, floor((sysdate
-

e.dob)/365.25) age, floor((sysdate
-

e.start_date)/365.25) years_worked

from

FAOES_V2A.employee e, FAOES_V2e
.
emp_mining_mv

d

36


where e.employee_no = d.employee_no

and d.decision
_net

= 'Fire Employee'

group by e.employee_no,e.fname,e.lname,e.dob, e.start_date

order by e.dob



6.
SELECT employee_no, lname, fname, trunc((current_date
-
DOB)/365.25, 0) AGE,
trunc((MONTH
S_BETWEEN(sysdate, start_date))/12, 0) "YEARS EMPLOYEED",
trunc(2000/12*(MONTHS_BETWEEN(current_date, start_date)
-
15*12), 2) "Firing Cost" from
faoes_v2a.employee where (MONTHS_BETWEEN(current_date, start_date))>180 and
employee_no IN (select employee_no
from faoes_v2a.emp_fuzzy_decision where DECISION
='Fire Employee')

order by dob ;


EMPLOYEE_NO

LNAME

FNAME

AGE

YEARS
EMPLOYEED

Firing Cost

1088

Hanzdo

Lee

81

55

81208.43

1086

Holman

Judy

77

54

79208.43

1000

Wyatt

Stefan

58

36

43133.16

1001

Wright

Donald

58

35

40143.91

1002

Worral

Al

57

31

33138.54

1004

Widdes

Albert

56

31

33138.54

1008

Stone

James F.

54

25

21143.91

1010

Stansbury

Thomas

53

29

29138.54

1012

Somers

Bill

53

30

30149.29

1013

Simmins

Steven

52

25

21143.91



SELECT

employee_no,
lname, fname, trunc((current_date
-
DOB)/365.25, 0) AGE,
trunc((MONTHS_BETWEEN(sysdate, start_date))/12, 0) "YEARS EMPLOYEED",
trunc(2000/12*(MONTHS_BETWEEN(current_date, start_date)
-
15*12),
2) "Firing Cost" from
faoes_v2b
.employee where (MONTHS_BETWEEN(curr
ent_date, start_date))>180 and
employee_no IN

(select employee
_no from faoes_v2abemp_fuzzy_decision

where DECISION

='Fire Employee'
)

order by dob
;


SELECT

employee_no, lname, fname, trunc((current_date
-
DOB)/365.25, 0) AGE,
trunc((MONTHS_BETWEEN(sysdate,

start_date))/12, 0) "YEARS EMPLOYEED",
trunc(2000/12*(MONTHS_BETWEEN(current_date, star
t_date)
-
15*12), 2) "Firing Cost
" from
faoes_v2a.employee where (MONTHS_BETWEEN(current_date, start_date))>180 and
employee_no IN

(select employee
_no from faoes_v2c.emp
_fuzzy_decision

where
DECISION_GROSS

='Fire Employee'
)

order by dob
;


SELECT employee_no, lname, fname, trunc((current_date
-
DOB)/365.25, 0) AGE,
trunc((MONTHS_BETWEEN(sysdate, start_date))/12, 0) "YEARS EMPLOYEED",
trunc(2000/12*(MONTHS_BETWEEN(current_da
te, star
t_date)
-
15*12), 2) "Firing Cost
" from
faoes_v2a.employee where (MONTHS_BETWEEN(current_date, start_date))>180 and
employee_no IN (select employee_no from faoes_v2e.emp_mining_mv where
DECISION_NET ='Fire Employee' or DECISION_NET = 'Fire')

order
by dob
;

37



Total Sum:

SELECT sum(2000/12*(MONTHS_BETWEEN(current_date, start
_date)
-
15*12)) "Sum Firing
Cost
" from faoes_v2b.employee where (MONTHS_BETWEEN(current_date, start_date))>180
and employee_no IN

(select employee_no

from FAOES_V2B.EMP_FUZZY_DECISION
where decision='Fire Employee');


SUM FIRING COST

624864.308


SELECT sum(2000/12*(MONTHS_BETWEEN(current_date, start
_date)
-
15*12)) "Sum Firing
Cost
" from faoes_v2b.employee where (MONTHS_BETWEEN(current_date, start_date
))>180
and employee_no IN

(select employee_no from FAOES_V2B.EMP_FUZZY_DECISION
where decision='Fire Employee');


SELECT sum(2000/12*(MONTHS_BETWEEN(current_date, start
_date)
-
15*12)) "Sum Firing
Cost
" from faoes_v2a.employee where (MONTHS_BETWEEN(current_
date, start_date))>180
and employee_no IN

(select employee_no from FAOES_V2C.EMP_FUZZY_DECISION
where DECISION_GROSS ='Fire Employee');


SELECT sum(2000/12*(MONTHS_BETWEEN(current_date, start
_date)
-
15*12)) "Sum Firing
Cost
" from faoes_v2a.employee where (
MONTHS_BETWEEN(current_date, start_date))>180
and employee_no IN

(select distinct employee_no from
FAOES_V2E.EMP_MINING_MV

where DECISION_NET ='Fire Employee' or
DECISION_NET ='Fire');



7.
select distinct employee_no, lname, fname, p.position, descripti
on from faoes_v2a.employee
e, faoes_v2a.employee_position p where p.position=e.position and employee_no not in (select
distinct employee_no from faoes_v2a.EMP_FUZZY_DECISION)

order by position;

EMPLOYEE_NO

LNAME

FNAME

POSITION

DESCRIPTION

1068

Allen

Doug

2

Branch Manager

1044

Dunn

Michael

3

Sales Manager

1089

Stewart

Macy

4

Sales Representative

1083

Forman

John

5

Customer Service
Manager

1048

Colson

Eugenia

6

Customer Service

1055

Heisler

Albert

6

Customer Service

1081

Keting

James F.

6

Customer
Service

1017

Pregmon

Andrew

6

Customer Service

1007

Thomas

Patricia A.

6

Customer Service

1049

Claggett

Edward

7

Technician




38


select distinct employee_no, lname, fname, p.position
, description from faoes_v2b.employee e,
faoes_v2b.employee_position p where p.position=e.position and employee_no not in (select
distinct employee_no from faoes_v2b.EMP_FUZZY_DECISION)

order by position;


select distinct employee_no, lname, fname, p.posit
ion, description from faoes_v2a.employee e,
faoes_v2a.employee_position p where p.position=e.position and employee_no not in (select
distinct employee_no from faoes_v2c.EMP_FUZZY_DECISION)

order by position;


select distinct employee_no, lname, fname, p.po
sition, description from faoes_v2a.employee e,
faoes_v2a.employee_position p where p.position=e.position and employee_no not in (select
distinct employee_no from faoes_v2e.EMP_MINING_MV)

order by position;