MIS 3376: Term Project – Orange Blossom Nursery

At Programming Homework Tutors, we believe in providing our students with practical, real-world examples of how to apply the concepts they learn in class. That’s why we’ve developed a variety of sample projects to help you see how our courses can be used to create impactful solutions in your field of study.

Description

The Orange Blossom is a local plant nursery that specializes in providing hard to-find plants to contractors and homeowners. The company also carries most of the common plants and trees, but the owners take pride in their ability to grow more unusual varieties and colors. Contractors and landscape design firms within a couple hundred miles come to the Orange Blossom for unique specimens, often used as highlights in their designs. Prices reflect the uniqueness and high quality of items offered by the firm.

Despite the company’s reputation, Orange Blossom faces the same pressures of any nursery. Weather can be a problem—particularly dry spells or unexpected cold winter. Such weather can boost sales. Homeowners and businesses lose plants during difficult times and they come to Orange Blossom to replace them. The biggest challenge is trying to forecast the demand for new housing and landscaping. It takes time to grow many of the plants to the size requested by contractors and landscapers, so Orange Blossom managers have to plan ahead and estimate the number of plants that will be needed. If the local economy slumps, rates fall, and the company is left with unsold plants. Fortunately, most of them can be protected and sold in future years, but the costs of growing the plants cuts into current-year profits.

Plant Orders

As much as possible, Orange Blossom managers encourage contractors and landscapers to order plants in advance. Many large projects are designed a year or two in advance of construction, providing time for the nursery to grow or locate the larger specimens required. Of course, homeowners rarely plan ahead, so almost all of the advance orders are placed by established clients. To assist the clients, Orange Blossom wants to keep many of the plan details in the computer system. Clients will be able to select a project and find the list of plants, along with the planned locations of each plant. Figure 1 shows the basic information collected on the order form. Large projects are often completed in stages or phases, so the form has a place for clients to record that information. Plants are ultimately identified by their species and variety using specific Latin names. However, general descriptions are provided using common names. Plants and trees have different types of measurements (sizes). Therefore, the sizes of the plants will change depending on the requirements of each order. Similarly, the colors of the plants can vary depending on the requirements of each order. Each plant has a fixed price, but the employee can put a different price based on the difficulty of the job or any sales available at the time of the order placed. Orange Blossom wants its database to track both prices. Finally, the employees at Orange Blossom have multiple specialties, therefore the company wants to keep track on the database of all specialties each employee has.

Purchases

Orange Blossom Nursery purchases several items from manufacturers and large growers. The company goes through thousands of pots every year, along with tons of fertilizer and other chemicals. Most of the products are used to grow and sell the plants. A few are sold directly to clients. Most of the vendors have multiple locations, so the purchase order generally specifies which location was contacted to provide the products. Figure 3 shows the details of the purchase order form. Some of the key features are shown in the detail section for the items ordered. Each item purchased has a fixed price, but can be also offered at a sales price available at the time of the order placed. Orange Blossom wants its database to track both prices. Finally, the employees at Orange Blossom have multiple specialties, therefore the company wants to keep track on the database of all specialties each employee has.

Purchase Order Form 

AS IS

1. PurchaseOrderForm (PurchaseOrder#, OrderDate, VendorFirstName, VendorLastName, Location, Address, City, State, ZIP, EmployeeName, 

(ItemID, Description, Quantity, Price, QuantityDelivered, DeliveryDate))

1NF

1. PurchaseOrderForm (PurchaseOrder#, OrderDate, VendorFirstName, VendorLastName, Location, Address, City, State, ZIP, EmployeeFirstName, EmployeeLastName)
2. PurchaseItem (PurchaseOrder#, ItemID, Description, Quantity, Price, QuantityDelivered, DeliveryDate)

2NF

1. PurchaseOrderForm (PurchaseOrder#, OrderDate, VendorFirstName, VendorLastName, Location, Address, City, State, ZIP, EmployeeFirstName, EmployeeLastName)
2. PurchaseItem (PurchaseOrder#, ItemID, Quantity, SalePrice, QuantityDelivered, DeliveryDate)
3. Item (ItemID, Description, ListPrice)

3NF (ALL FINAL TABLES FOR THE FORM)

1. PurchaseOrderForm (PurchaseOrder#, OrderDate, EmployeeID, VendorID, LocationID)
2. PurchaseItem (PurchaseOrder#, ItemID, Quantity, SalePrice, QuantityDelivered, DeliveryDate)
3. Item (ItemID, Description, ListPrice)
4. Vendor(VendorID, VendorFirstName, VendorLastName)
5. VendorLocation(LocationID, Address, CityID)
6. Employee (EmployeeID, EmployeeFirstName, EmployeeLastName)
7. City(CityID, CityName, State, Zip)
8. EmployeeSpecialty (EmployeeID, SpecialtyID)
9. Specialty (SpecialtyID, Title, Description)

TP2: Queries, Forms, Reports and write-up (Deadline May 2^nd)

1. Correct any problem you had in TP1.
2. Transfer tables on Microsoft Access. – (Done In MS Access)
3. Create Class Diagram on MS Access  – (Done In MS Access)
4. Each student should generate 3 queries without overlapping. Include your last name in the query (i.e., customers-dimoka). The queries (for each student) must have the following criteria. 

• At least 2 must use more than one table
• Each query must have at least one constraint 
• At least 1 query must have a computed expression
• At least 1 query must have a subquery 

1. Each student should build one data input form and one output report which deal with the issues of the case. Each student should replicate the form provided by the company  and selected on TP1. When you save the form or the report, include your name in the form name (i.e., OrderForm-dimoka). Each group should create a switchboard form that displays the company’s logo and all forms and reports created.   – (Done In MS Access)

Final Report Write-up Guidelines

1.    TP2 report

The report is intended to compile all of the pieces that you have prepared to date (including your write up from TP1, with all corrections suggested).  Your final report should reflect the combined comments that you have received throughout the term and include the following components:

Background

Describe the problem and the motivation for it.  Indicate how a database addresses the problem. 

Assumptions

Identify your project assumptions in two parts

Part 1. Express the assumptions pertaining to business rules and constraints you made (all assumptions needed to normalize your table and design your class diagram)

Part 2. Write the queries you designed ( In bullet 4 up there) and the rational behind them (why you believe the company will need these queries). As part of this, provide two or more use scenarios including, in English, the types of queries that such a scenario requires.

2.    Appendices

In addition, include the following Appendices:

• Appendix A: Final Normalized tables (table notation – you can insert here the final normalized tables given at TP1 Key)
• Appendix B: Conceptual model (class diagram from Microsoft Access)
• Appendix C: Queries (write the questions/statements you are trying to answer and show SQL, QbE, datasheet)
• Appendix D: Forms and Reports (screenshots from Microsoft Access)

There are two deliverables: your database (MS ACCESS) and the final report (MS WORD).

Disclaimer

The sample projects provided on our website are intended to be used as a guide and reference for educational purposes only. While we have made every effort to ensure that the projects are accurate and up-to-date, we do not guarantee their accuracy or completeness. The projects should be used at your own discretion, and we are not responsible for any loss or damage that may result from their use.
At Programming Homework Tutors, we are dedicated to helping students and educators achieve their goals by providing them with the resources they need to succeed. Our website offers a variety of tools and resources that can help you with the project mentioned above.
Whether you need help with research, project management, or technical support, our team of experts is here to assist you every step of the way. We offer online courses, tutorials, and community forums where you can connect with other learners and get the support you need to succeed.
If you’re looking to take your skills to the next level and make an impact in your field, we invite you to explore our website and see how we can help you achieve your goals.