Catch$-22$ Inventories

Catch$-22$ maintains an inventory of crop$-$dusting aircraft. Catch$-22$ tracks each plane by their engine

number and for each plane in their inventory, Catch$-22$ also tracks the number of hours each plane is

used and their maintenance history. Each type of plane has a different capacity for pesticides and fuel,

but each plane within a particular type has the same capacity. $($Catch$-22$ leases all of their planes, but

the leasing process $($plane acquisition$)$ is outside the scope of this case. Just assume that the planes

are Catch$-22$ resources.$)$

Catch$-22$ also maintains an extensive inventory of pesticides, so they can effectively treat almost any

pest problem. To keep from running afoul of OSHA regulations, they categorize each pesticide

according to its EPA toxicity rating, which determines how all pesticides in that category should be

handled. EPA toxicity ratings run from $1$ to $10.($The acquisition of pesticides is also outside the scope

of this case; just assume that the pesticides are an available resource.$)$

Plane Maintenance

Milo$$s brother$-$in$-$law Joe owns a plane maintenance business, so Catch$-22$ usually buys fuel and

services from him $($but they can use other maintenance vendors$).$ Each day, the plane maintenance

vendor services all the planes that need maintenance and fuel. The vendor prepares a service slip

$($which serves as the invoice he presents to Catch$-22)$ for each plane serviced on a given day. A Catch

$22$ employee verifies the service slip and then at the end of the week, Catch$-22$ pays Joe, or other

maintenance vendor, for all services performed in the prior week by check. Crop Dusting Jobs

Catch$-22$ performs crop$-$dusting services for farmers. Catch$-22$ tracks each crop dusting job contract

by job#$.$ Catch$-22$ always assigns multiple employees to each job as follows: $(1)$ one supervisor, $(2)$ as

many pilots as are needed to fly the planes for the job, and $(3)1-10$ field hands. Since each crop may

require slightly different treatment, Milo$$s crews often use multiple kinds of pesticide on one job.

Farmers pay for all crop$-$dusting jobs performed during the month in one payment at the end of the

month.

Employees

Catch$-22$ keeps all employee information together $($your model should show one Employee Entity$).$

They track each employee$$s qualifications according to category: buyer, cashier, field hand, pilot,

supervisor, etc. An employee is considered qualified when he or she reaches the number of training

hours required for that category. However, each employee can hold qualifications for several

categories, for example an employee qualified as a buyer could also be qualified as a pilot. Although

Catch$-22$ does not differentiate among supervisors, pilots, and field hands in the database, they do

specifically track which employees perform which of these functions on specific crop dusting jobs, since

they pay employees according to the work they performed on each crop dusting job.

General

You may assume that all agent, resource, and type image information is entered into the database

before any process activity takes place.

REQUIRED: $1)$ use the information above and the list of attributes below $($no additions$)$ to draw an

REA data model showing the classes, associations, and multiplicities, $2)$ prepare a data dictionary

listing the tables necessary to support Catch$-22$s plane maintenance and crop dusting processes using

all the attributes $($name each table, underline the primary key, and place an asterisk by each foreign

key$),$ and $3)$ draw business process

maps showing the process steps. Accounts receivable from this farmer

Bank account #

Bank account balance

Bank name

Cash receipt#

Cash receipt amount

Cash receipt date

Check #

Check amount

Check date

Crop$-$dusting job #

Crop$-$dusting job date

Crop$-$dusting job $ cost

Employee #

Employee immunization status

Employee category

Employee last name

Employee nickname

EPA pesticide category number

Farmer customer #

Farmer name

Fuel capacity for planes in this category

Number of hours of training required for this

employee category

Number of hours of training for this employee

towards qualification for this employee

category

Pesticide #

Pesticide amount on hand

Pesticide amount used on this job

Pesticide capacity for this type of plane

Pesticide cost per gallon

Plane engine #

Plane name

Plane service slip #

Plane service slip $ amount

Plane type

Present plane color

Toxicity rating for pesticides in this category

Vendor #

Vendor name

Year$-$to$-$date $($YTD$)$ purchases $($$$)$ from this

vendor