Using the narrative and the attributes inside the case, prepare:

1) a UML class diagram (with classes, associations, and multiplicities) and

2) a list of corresponding relational tables (list the tables with all their attributes, identify the primary keys [with PK or underline], and indicate the foreign keys [with an asterisk or FK]).

• The table definitions should only use the attributes listed at the end of this case (remember that there should be no entities without attributes).
• You may abbreviate attribute names if the abbreviation is clear
• List the tables in the following order: Resources, Events, Agents, Types, and Linking tables. Apply the standard REA patterns wherever the narrative does not clearly specify otherwise but avoid adding extra relationships that are not discussed.
• If you believe the narrative is not clear, you may make assumptions as necessary to clarify. However, you must write down any assumptions that you make on your diagram/tables for them to be considered.

Northwest Green Company (NGC) provides landscaping services to individual and corporate customers in Oregon and parts of southern Washington. Ashley originally founded NGC 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 NGC's landscaping jobs (sales) and vehicle maintenance (purchases) processes. Landscaping Jobs NGC performs landscaping jobs for individual and corporate customers, although at this point NGC does not differentiate among types of customers. NGC tracks landscaping jobs by job #. Each landscaping job may require many plants and at least one vehicle, and NGC assigns a minimum of two employees to each job. NGC designates one of those employees to supervise the 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 deposited daily in NGC's bank account by an NGC cashier. Each payment is made for only one job. NGC Inventories Vehicles. NGC maintains an inventory of vehicles of all sizes to support various landscaping requirements (the acquisition of vehicles is outside the scope of this case). NGC tracks the vehicles by vehicle identification numbers (VINS) and keeps meticulous records on each vehicle. For example, they track the mileage that each vehicle incurred on each landscaping job. Additionally, they track the maintenance history for each vehicle. Each type of vehicle has a different use and hauling capacity, but each vehicle in a particular vehicle type has the same use and hauling capacity. Furthermore, employees must be trained and have a license to drive a particular type of vehicle. For instance, they must have a Class A commercial driver's license in order to drive a big rig or a flatbed truck. Because of this, only certain employees are assigned particular vehicle types to drive, and each employee could have a license to drive many vehicle types or have no license to drive vehicles of any type. Plants. NGC 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 (the acquisition of plants is outside the scope of this case). Vehicle Maintenance. NGC purchases routine vehicle maintenance from local vehicle repair shops. When maintenance is required NGC takes its vehicles to the repair shop. After completing the maintenance, the repair shop will invoice NGC. An NGC purchasing agent approves each maintenance invoice and assigns a purchase number to track the purchase of maintenance. An NGC cashier pays the vehicle maintenance vendors monthly for all the maintenance invoices received from each vendor during the month. Miscellaneous. NGC keeps all employee information together your model should show one Employee class). NGC classifies the employees by type, but each employee could be multiple types. NGC keeps all vendor information together your model should show one Vendor class). However, they classify the vendors by type. 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 to draw a UML class diagram showing the classes, associations, and multiplicities, 2) prepare a listing of the tables necessary to support NGC'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). You should apply the standard REA patterns wherever the narrative does not clearly specify otherwise, but avoid adding extra relationships that are not discussed. If the narrative is not clear, you should clearly note any assumptions that you make. Attributes: Cash account # Cash account balance Cash receipt # Cash receipt amount Cash receipt date Check # Check amount Check date Customer # Customer name Date employee qualified to drive this type of vehicle Employee # Employee hire date Employee name Employee type Landscaping job # Landscaping job price Landscaping job start date Number of employees qualified Number of hours this employee worked on this job Number of vehicles of this type Number of training hours to qualify for this employee type Number of hours this vehicle used on this job Number of vendors of this type Plant common name Plant scientific name Price of this plant used on this job Quantity of this plant used on this job Quantity on hand of this plant Total vehicle maintenance $ cost for this purchase Vehicle maintenance date Vehicle maintenance purchase # Vehicle make Vehicle mileage to date Vehicle type Vehicle VIN Vendor # Vendor account payable amount Vendor name Vendor type Year that this vehicle was acquired YTD sales $ to this customer Northwest Green Company (NGC) provides landscaping services to individual and corporate customers in Oregon and parts of southern Washington. Ashley originally founded NGC 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 NGC's landscaping jobs (sales) and vehicle maintenance (purchases) processes. Landscaping Jobs NGC performs landscaping jobs for individual and corporate customers, although at this point NGC does not differentiate among types of customers. NGC tracks landscaping jobs by job #. Each landscaping job may require many plants and at least one vehicle, and NGC assigns a minimum of two employees to each job. NGC designates one of those employees to supervise the 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 deposited daily in NGC's bank account by an NGC cashier. Each payment is made for only one job. NGC Inventories Vehicles. NGC maintains an inventory of vehicles of all sizes to support various landscaping requirements (the acquisition of vehicles is outside the scope of this case). NGC tracks the vehicles by vehicle identification numbers (VINS) and keeps meticulous records on each vehicle. For example, they track the mileage that each vehicle incurred on each landscaping job. Additionally, they track the maintenance history for each vehicle. Each type of vehicle has a different use and hauling capacity, but each vehicle in a particular vehicle type has the same use and hauling capacity. Furthermore, employees must be trained and have a license to drive a particular type of vehicle. For instance, they must have a Class A commercial driver's license in order to drive a big rig or a flatbed truck. Because of this, only certain employees are assigned particular vehicle types to drive, and each employee could have a license to drive many vehicle types or have no license to drive vehicles of any type. Plants. NGC 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 (the acquisition of plants is outside the scope of this case). Vehicle Maintenance. NGC purchases routine vehicle maintenance from local vehicle repair shops. When maintenance is required NGC takes its vehicles to the repair shop. After completing the maintenance, the repair shop will invoice NGC. An NGC purchasing agent approves each maintenance invoice and assigns a purchase number to track the purchase of maintenance. An NGC cashier pays the vehicle maintenance vendors monthly for all the maintenance invoices received from each vendor during the month. Miscellaneous. NGC keeps all employee information together your model should show one Employee class). NGC classifies the employees by type, but each employee could be multiple types. NGC keeps all vendor information together your model should show one Vendor class). However, they classify the vendors by type. 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 to draw a UML class diagram showing the classes, associations, and multiplicities, 2) prepare a listing of the tables necessary to support NGC'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). You should apply the standard REA patterns wherever the narrative does not clearly specify otherwise, but avoid adding extra relationships that are not discussed. If the narrative is not clear, you should clearly note any assumptions that you make. Attributes: Cash account # Cash account balance Cash receipt # Cash receipt amount Cash receipt date Check # Check amount Check date Customer # Customer name Date employee qualified to drive this type of vehicle Employee # Employee hire date Employee name Employee type Landscaping job # Landscaping job price Landscaping job start date Number of employees qualified Number of hours this employee worked on this job Number of vehicles of this type Number of training hours to qualify for this employee type Number of hours this vehicle used on this job Number of vendors of this type Plant common name Plant scientific name Price of this plant used on this job Quantity of this plant used on this job Quantity on hand of this plant Total vehicle maintenance $ cost for this purchase Vehicle maintenance date Vehicle maintenance purchase # Vehicle make Vehicle mileage to date Vehicle type Vehicle VIN Vendor # Vendor account payable amount Vendor name Vendor type Year that this vehicle was acquired YTD sales $ to this customer