Pacific Green Company (PGC) provides landscaping services to individual and corporate customers in southern California. Heather F originally founded PGC as a lawn mowing service while she attended graduate school. It became so successful that she delayed her teaching career to concentrate on building the company. This case describes PGC's landscaping jobs (sales) process.PGC maintains an inventory of trucks of all sizes to support various landscaping requirements (the acquisition of trucks are outside the scope of this case). PGC tracks the trucks by vehicle identification numbers (VINs) and keeps meticulous records on each truck. For example, they track the mileage for each truck on each landscaping job. Additionally, they track the maintenance history for each truck (maintenance is

Question

outside the scope of this case). Each type of truck has a different use and hauling capacity, but each truck in a particular truck type has the same use and hauling capacity.PGC also maintains an extensive inventory of plants, ranging from colorful flowers to large trees, so they can address almost any landscaping requirement on short notice. Since the common name of many plants vary by region, PGC tracks its plant inventory by scientific names (i.e., the Latin names). To manage the plant inventory and ensure that the best plants are applied to job conditions, Heather categorizes all plants by plant type.PGC performs landscaping jobs for individual and corporate customers. Before performing any job, Heather prepares a quote describing the scope of service and the price. Sometimes, the customer will elect to perform pieces of the overall quote, so one quote may result in several landscaping jobs. The landscaping quote presents a price estimate based on the types of plants and types of trucks required and the overall time involved. Each quote involves at least one truck type and one plant type.PGC tracks landscaping jobs by job #. Each landscaping job may require many plants and several trucks. PGC typically assigns multiple employees to each job as follows: (1) one supervisor, (2) one truck drivers per truck, and (3) landscaping laborers. PGC specifically tracks which employees perform which of these functions on specific jobs, since they pay employees according to the work performed on each landscaping job. Customers must pay a 10% deposit when the job begins, another 50% when the job is half completed, and the final 40% within two weeks after the job is fully completed. Customer payments are made by check or credit card and processed for daily deposit by a PGC cashier.PGC keeps all employee information together (your model should show one Employee class). They track each employee's qualifications according to category: purchasing agent, cashier, landscaping laborer, truck driver, 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 purchasing agent could also be qualified as a truck driver.You may assume that all agent, resource, and type image information is entered into the database before any process activity takes place. You may also assume that PGC only receives cash from landscaping jobs.REQUIRED: 1) use the information above and the list of attributes below to draw a UML class diagram showing the classes, associations, and multiplicities, 2) prepare a listing of the tables necessary to support PGC's process using all the attributes. Name each table and clearly identify primary keys with PK and foreign keys with FK. List your tables in the following order: RESOURCES, EVENTS, AGENTS, TYPE IMAGES, and LINKING. Use only the following list of attributes (remember that there should be no entities without attributes).Attributes:Average price of plants in this categoryAverage daily charge to use a truck of this typeCash account #Cash account balanceCash receipt #Cash receipt amountCash receipt dateCount of this plant used on this jobCustomer #Customer nameDate this employee qualified for this employee typeEmployee #Employee hire dateEmployee nameEmployee TypeExpected maximum size of plants in this categoryHours this truck driver worked on this jobHours this truck used on this jobLandscaping job priceLandscaping job #Landscaping job start dateLandscaping quote #Landscaping quote amountLandscaping quote dateLandscaping quote expiration dateLight required for plants in this categoryNumber of employees qualified for this employee typeNumber of plants in this category bid on this quoteNumber of training hours to qualify for this employee typeNumber of trucks of this type bid on this quotePlant category namePlant common namePlant scientific nameQuantity on hand of this plantTruck acquired yearTruck makeTruck mileage to dateTruck typeTruck type hauling capacityTruck type useTruck VINYTD sales $ to this customer

What will be an ideal response?

Answer 1

Diagram:


Table listing:

Resources
Cash = cash account # (PK), cash account balance
Plants = plant scientific name (PK), plant common name, quantity on hand, plant category name (FK)
Trucks = truck VIN (PK), truck make, truck acquired year, truck mileage to date, truck type (FK)

Events
Cash receipt = cash receipt # (PK), cash receipt date, cash receipt amount, cash account # (FK) employee # (FK), customer # (FK), landscaping job # (FK)
Landscaping job = landscaping job # (PK), start date, price, customer#, landscaping quote # (FK), employee # (supervisor) (FK)
Landscaping quote = landscaping quote # (PK), quote date, quote expiration date, quote amount, customer # (FK), employee # (FK)

Agents
Employee = employee # (PK), employee name, employee hire date
Customer = customer # (PK), customer name, YTD sales $ to this customer

Type Images
Employee type = employee type (PK), number of training hours to qualify, number of employees qualified
Plant category = plant category name (PK), expected maximum size of plants in this category, light required for plants in this category, average cost of plants in this category
Truck type = truck type (PK), truck type use, truck type hauling capacity, average daily cost to use trucks of this type

Linking Tables
Quote - Plant Categories = landscaping quote # + plant category name (PK), number of plants in this category bid on this quote
Quote - Truck Types = landscaping quote # + truck type (PK), number of trucks of this type bid on this quote
Job - Trucks = landscaping job # + truck VIN (PK), hours this truck used on this job
Job - Plants = landscaping job # + plant scientific name (PK), count of this plant used on this job
Truck Drivers = landscaping job # + employee # (PK), hours this truck driver worked on this job
Landscape Laborers = landscaping job # + employee # (PK), hours this landscape laborer worked on this job
Employee - Employee Types = employee # + employee type (PK), date this employee qualified for this type