Pine Valley Furniture.  The Pine Valley Furniture case is more complex than the examples I have been working so far.

There are always a number of different design approaches.  HPM focuses on two of them used most often.

• top down
  • start with the basic descriptions of the business
    • policies
    • processes
    • environment
  • best focuses on developing a high level, overall understanding of the organization

 

• bottom up
  • start with
    • users
    • documents
    • screens
    • other data sources
  • usually worthwhile to rapid prototype to help determine what end users really want while also figuring out how to best lay it all out

As usual, it is important make use of both of these approaches.  Each will have its importance in the overall scheme of things.

HPM starts with a high level for Pine Valley Furniture Company, which certainly assumes the high level people can surface most of their own business rules and needs.  This is important, but probably never works as well as it should.

Using this top down approach, the developers are able to determine certain entities.

• Product_Line - the company sells a variety of products that are grouped into different product lines. 
  • Each product line must contain at least one product.

  • Product_Line_ID
  • Product_Line_Name

• Product - this is the table for the products themselves. 
  • Each product must belong to exactly one product line.

  • Product_ID
  • Product_Description
  • Product_Finish
  • Standard_Price

• Order - customers submit orders for products. 
  • A customer may submit any number of orders, but may not submit any orders.
  • Each order is submitted by exactly one customer.
  • A customer must request at least one product and only one product per line item.
  • Any product sold by Pine Valley may not appear in any given order.
  • Any product sold by may appear on one or more line items.
    • Order_ID
    • Order_Date
    • Order_Quantity

• Customer - keeping track of some information about customers.
  • A customer is anyone Pine Valley decides is a customer
    • Customer_ID
    • Customer_Name
    • Customer_Address
    • Postal_Code

• Territory - Pine Valley has established territories.
  • Each customer may do business in any of the territories.
  • Each customer may choose to not do any business in any territory.
  • A sales territory has many customers in each one territory.
    • Territory_ID
    • Territory_Name
• Salesperson -
  • A salesperson serves exactly one sales territory.
  • Each sales territory has one or more salespersons.
    • Salesperson_ID
    • Salesperson_Name
    • Salesperson_Telephone
    • Salesperson_Fax

• Raw_Material - Products are developed from pre-specified amounts of raw materials.
  • Each raw_material is assembled into one or more products using a pre-specified amount for each product.
    • Material_ID
    • Unit_of_Measure
    • Material_Name
    • Standard_Cost

• Vendor - Each raw material is supplied by vendors.
  • A vendor may supply any number of raw materials to Pine Valley Furniture.
  • Each raw material can be supplied by one or more vendors.
  • An attribute of the relationship between raw material and vendor is Supply_Unit_Price (so it depends on both the vendor and the raw material)
    • Vendor_ID
    • Vendor_Name
    • Vendor_Address

• Work_Center - Pine Valley Furniture has a number of work centers.
  • Each product is produced in one or more work center.
  • A work center may be used to produce any number of products and it may not be used to produce any products.
    • Work_Center_ID
    • Work_Center_Location

• Employee - Pine Valley Furniture has over 100 employees.
  • An employee may have more than one skill.
  • Each employee may work in one or more work centers.
  • A work center must have at least one employee working in that center, but may have any number of employees.
    • Employee_ID
    • Employee_Name
    • Employee_Address
    • Skill

• Supervisor - Each employee, that is not a manager, has a supervisor.
  • An employee who is a supervisor may supervise any number of employees.
  • Not all employees are supervisors.

I am not going to recreate the data model from HPM.  Remember, you are not responsible for being able to develop E - R models as they are done in HPM.  We will focus on the data dictionaries and relationships that can be developed in Access.

Displaying Information.  In this semester, you are also not responsible for the SQL that HPM uses to create the different sorts of reports/information displays that Pine Valley Furniture wants.  We will do these sorts of things later in the semester using Access.  We will also do them in the next semester using SQL.

But we will discuss what sorts of information displays/reports Pine Valley Furniture is looking to have.

• Showing Product Information
• The following people need to see Pine Valley Furniture's product information
  • salespersons
    • responding to requests from customers
  • inventory managers
  • product managers

There are many ways this information can be made accessible.  You should expect these displays will depend on who requests or uses them.  Salespeople will usually need to see something quite different from inventory managers, for example.

• Showing Customer Information
• The following people need to see Pine Valley Furniture's product information
  • territory sales managers
  • salespeople

 

• Showing Customer Order Status
• The following people need to see Pine Valley Furniture's customer order information
  • customers need to be able to find out about this, likely through a company rep
  • delivery people

 

• Showing Product Sales
• The following people need to see Pine Valley Furniture's product sales information
  • managers and decision makers
  • sales managers
  • marketing

There is a lot of other types of information Pine Valley Furniture, its customers, vendors and others want to access.  Obviously, different information should be available to only certain people.  Hopefully, this section motivates future studies.