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.

Figure 1: Orange Blossom Client Order Form

Orange Blossom

Client Order Form

ProjectID

Order Date

Date Modified

Employee

Client

Contact Person First Name, Last Name

Phone

Address

City, State ZIP

Project Location

Address

Project Start Date

Phase

Estimated Installation Start

Project Type

Design Segment

Segment Description:

ItemID	Description	Size	Color	Stock or External	Plant Location	Quantity	Price	Date Installed

Subtotal

Tax

Total

• Appendix A: Create a preliminary list of Normalized tables (at least 3^rd normal form) you think you will need for the project. Explain why you need to normalize the tables for each normalization. Show all the steps you followed as shown in class. List the structure of each table.
• Appendix B: Apply the conceptual modeling principles you learned in the class and draw the class diagram. Hand-draw is not acceptable; it must be done with a computer. I suggest using PowerPoint to do the diagram, unless you are familiar with a package designed to draw Class Diagrams such as MS Visio for UML.

Answer rating: 100% (QA)

Appendix A Table 1 Client Order Form This table will store the details of the order placed by the clients It can be normalized into the third form by breaking it into three different tables 1 Client T... View the full answer