DEVELOPING A DATABASE APPUCATION FOR PINE VALLEY FURNITURE COMPANY

Pine Valley Furniture Company was introduced earlier in this chapter. By the late 1990s,
competition in furniture manufacturing had intensified, and competitors seemed to respond
more rapidly than Pine Valley Furniture to new business opportunities. While there were
many reasons for this trend, managers believed that the computer information systems they
had been using (based on traditional file processing) had become outdated. After attending
an executive development session led by Fred McFadden and Jeff Hoffer (we wish!), the
company started a development effort that eventually led to adopting a database approach
for the company. Data previously stored in separate files have been integrated into a single
database structure. Also, the metadata that describe these data reside in the same structure.
The DBMS provides the interface between the various database applications for
organizational users and the database (or databases). The DBMS allows users to share the
data and to query, access, and update the stored data.
To facilitate the sharing of data and information, Pine Valley Furniture Company uses a local
area network (LAN) that links employee workstations in the various departments to a
database server, as shown in Figure 1-13. During the early 2000s, the company mounted a
two-phase effort to introduce Internet technology. First, to improve intracompany
communication and decision making, an intranet was installed that allows employees fast
Web-based access to company information, including phone directories, furniture design
specifications, e-mail, and so forth. In addition, Pine Valley Furniture Company also added a
Web interface to some of its business applications, such as order entry, so that more internal
business activities that require access to data in the database server could also be conducted
by employees through its intranet. However, most applications that use the database server
still do not have a Web interface and require that the application itself be stored on
employees' workstations.

Database Evolution at Pine Valley Furniture Company

A trait of a good database is that it does and can evolve! Helen Jarvis, product manager for
home office furniture at Pine Valley Furniture Company, knows that competition has become
fierce in this growing product line. Thus, it is increasingly important to Pine Valley Furniture
that Helen be able to analyze sales of her products more thoroughly. Often these analyses are
ad hoc, driven by rapidly changing and unanticipated business conditions, comments from
furniture store managers, trade industry gossip, or personal experience. Helen has requested
that she be given direct access to sales data with an easy-to-use interface so that she can
search for answers to the various marketing questions she will generate.
Chris Martin is a systems analyst in Pine Valley Furniture's information systems development
area. Chris has worked at Pine Valley Furniture for five years and has experience with
information systems from several business areas within Pine Valley. With this experience, his
information systems education at Western Florida University, and the extensive training Pine
Valley has given him, he has become one of Pine Valley's best systems developers. Chris is
skilled in data modeling and is familiar with several relational database management systems
used within the firm. Because of his experience, expertise, and availability, the head of
information systems has assigned Chris to work with Helen on her request for a marketing
support system.

1
Because Pine Valley Furniture has been careful in the development of its systems, especially
since adopting the database approach, the company already has databases that support its
operational business functions. Thus, it is likely that Chris will be able to extract the data
Helen needs from existing databases. Pine Valley's information systems architecture calls for
systems such as the one Helen is requesting to be built as stand-alone databases so that the
unstructured and unpredictable use of data will not interfere with the access to the
operational databases needed to support efficient transaction processing systems.
Further, because Helen's needs are for data analysis, not creation and maintenance, and are
personal, not institutional, Chris decides to follow a combination of proto typing and life-cycle
approaches in developing the system Helen has requested. This means that Chris will follow
all the life-cycle steps but focus his energy on the steps that are integral to prototyping. Thus,
he will quickly address project planning and then use an iterative cycle of analysis, design, and
implementation to work closely with Helen to develop a working prototype of the system she
needs. Because the system will be personal and likely will require a database with limited
scope, Chris hopes the prototype will end up being the actual system Helen will use. Chris has
chosen to develop the system using Microsoft Access, Pine Valley's preferred technology for
personal databases.

Project Planning
Chris begins the project by interviewing Helen. Chris asks Helen about her business area,
taking notes about business area objectives, business functions, data entity types, and other
business objects with which she deals. At this point, Chris listens more than he talks so that
he can concentrate on understanding Helen's business area; he interjects questions and
makes sure that Helen does not try to jump ahead to talk about what she thinks she needs
with regards to computer screens and reports from the information system. Chris asks general
questions, using business and marketing terminology as much as possible. For example, Chris
asks Helen what issues she faces managing the home office products; what people, places,
and things arc of interest to her in her job; how far back in time she needs data to go to do
her analyses; and what events occur in the business that are of interest to her. Chris pays
particular attention to Helen's objectives as well as the data entities that she is interested in.
Chris does two quick analyses before talking with Helen again. First, he identifies all of the
databases that contain data associated with the data entities Helen mentioned. From these
databases, Chris makes a list of all of the data attributes from these data entities that he thinks
might be of interest to Helen in her analyses of the home office furniture market. Chris's
previous involvement in projects that developed Pine Valley's standard sales tracking and
forecasting system and cost accounting system helps him speculate on the kinds of data Helen
might want. For example, the objective to exceed sales goals for each product finish category
of office furniture suggests that Helen wants product annual sales goals in her system; also,
the objective of achieving at least an 8 percent annual sales growth means that the prior
year's orders for each product need to be included. He also concludes that Helen's database
must include all products, not just those in the office furniture line, because she wants to
compare her line to others. However, he is able to eliminate many of the data attributes kept
on each data entity. For example, Helen does not appear to need various customer data such
as address, phone number, contact person, store size, and salesperson. Chris does, though,
include a few additional attributes, customer type and zip code, which he believes might be
important in a sales forecasting system.
2
Second, from this list, Chris draws a conceptual data model (Figure 1-14) that represents the
data entities with the associated data attributes, as well as the major relationships among
these data entities. The data model is represented using a notation called the Entity-
Relationship (E-R) model. You will learn more about this notation in Chapters 2 and 3. The
data attributes of each entity Chris thinks Helen wants for the system are listed in Table 1-6.
Chris lists in Table 1-6 only basic data attributes from existing databases, because Helen will
likely want to combine these data in various ways for the analyses she will want to do.

Analyzing Database Requirements

Prior to their next meeting, Chris sends Helen a rough project schedule outlining the steps he
plans to follow and the estimated length of time each step will take. Because prototyping is a
user-driven process, in which the user says when to stop iterating on the new prototype
versions, Chris can provide only rough estimates of the duration of certain project steps.
Chris does more of the talking at this second meeting, but he pays close attention to Helen's
reactions to his initial ideas for the database application. He methodically walks through each
data entity in Figure 1-14, explaining what it means and what business policies and
procedures are represented by each line between entities. A few of the rules he summarizes
are listed here:
1. Each CUSTOMER Places any number of ORDERs. Conversely, each ORDER Is Placed By
exactly one CUSTOMER.
2. Each ORDER Contains any number of ORDER LINEs. Conversely, each ORDER LINE Is
Contained In exactly one ORDER.
3. Each PRODUCT Has any number of ORDER UNEs. Conversely, each ORDER LINE Is For
exactly one PRODUCT.
4. Each ORDER Is Billed On one INVOICE and each INVOICE Is a Bill for exactly one ORDER.
Places, Contains, and Has are called one-to-many relationships because, for example, one
customer places potentially many orders and one order is placed by exactly one customer.
In addition to the relationships, Chris also presents Helen with some detail on the data
attributes captured in Table 1-6. For example, Order Number uniquely identifies each order.
Other data about an order Chris thinks Helen might want to know include the date when the
order was placed and the date when the order was filled. (This would be the latest shipment
date for the products on the order.) Chris also explains that the Payment Date attribute
represents the most recent date when the customer made any payments, in full or partial,
for the order.
Maybe because Chris was so well prepared or so enthusiastic, Helen is excited about the
possibilities, and this excitement leads her to tell Chris about some additional data she wants
(the number of years a customer has purchased products from Pine Valley Furniture Company
and the number of shipments necessary to fill each order). Helen also notes that Chris has
only one year of sales goals indicated for a product line. She reminds him that she wants these
data for both the past and current years. As she reacts to the data model, Chris asks her how
she intends to use the data she wants. Chris does not try to be thorough at this point because
he knows that HeJen has not worked with an information set like the one being developed;

3
thus, she may not yet be positive about what data she wants or what she wants to do with it.
Rather, Chris's objective is to understand a few ways in which Helen intends to use the data
so he can develop an initial prototype, including the database and several computer displays
or reports. The final list of attributes that Helen agrees she needs appears in Table 1-7.

Designing the Database

Because Chris is following a prototyping methodology and the first two sessions with Helen
quickly identified the data Helen might need, Chris is now ready to build a prototype. His first
step is to create a project data model like the one shown in Figure 1-15. Notice the following
characteristics of the project data model:
1. It is a model of the organization that provides valuable information about how the
organization functions, as well as important constraints.
2. The project data model focuses on entities, relationships, and business rules. It also
includes attribute labels for each piece of data that will be stored in each entity.
Second, Chris translates the data model into a set of tables for which the columns are data
attributes and the rows are different sets of values for those attributes. Tables are the basic
building blocks of a relational database (we will learn about this in Chapter 4), which is the
database style for Microsoft Access. Figure 1-16 shows four tables with sample data:
Customer, Product, Order, and OrderLine. Notice that these tables represent the four entities
shown in the project data model (Figure 1-15). Each column of a table represents an attribute
(or characteristic) of an entity. For example, the attributes shown for Customer are
CustomeriD and CustomerName. Each row of a table represents an instance (or occurrence)
of the entity. The design of the database also required Chris to specify the format, or
properties, for each attribute (MS Access calls attributes fields). These design decisions were
easy in this case because most of the attributes were already specified in the corporate data
dictionary.
The tables shown in Figure 1-14 were created using SQL(you will learn about this in Chapters
6 and 7). Figures 1-17 and 1-18 show the SQL statements that Chris would have likely used to
create the structure of the ProductLine and Product tables. It is customary to add the suffix
_T to a table name. Also note that because Access does not allow for spaces between names,
the individual words in the attributes from the data model have now been concatenated.
Hence, Product Description in the data model has become ProductDescription in the table.
Chris did this translation so that each table had an attribute, called the table's "primary key,"
which will be distinct for each row in the table. The other major properties of each table are
that there is only one value. for each attribute. in each row; if we know the value of the.
identifier, there can be only one value for each of the. other attributes. For example, for any
product line, there can be only one value for the current year's sales goal.
A final key characteristic of the relational model is that it represents relationships between
entities by values stored in the columns of the corresponding tables.
The other major decision Chris has to make about database design is how to physically
organize the database to respond as fast as possible to the queries Helen will write. Because
the database will be used for decision support, neither Chris nor Helen can anticipate all of
the queries that will arise; thus, Chris must make the physical design choices from experience

4
rather than p recise knowledge of the way the database will be used. The key physical
database design decision that SQL allows a database designer to make is on which attributes
to create indexes. All primary key attributes (such as OrderNumbcr for the Order_T table)-
those with unique values across the rows of the table-are indexed. In addition to this, Chris
uses a general rule of thumb: Create an index for any attribute that has more than 10 different
values and that Helen might use to segment the database. For example, Helen indicated that
one of the ways she wants to use the database is to look at sales by product finish. Thus, it
might make sense to create an index on the Product_T table using the Product Finish
attribute. However, Pine Valley uses only six product finishes, or types of wood, so this is not
a useful index candidate. On the other hand, OrderPlacementDate (called a secondary key
because there may be more than one row in the Order_T table with the same value of this
attribute), which Helen also wants to use to analyze sales in different time periods, is a good
index candidate.

Using the Database

Helen will use the database Chris has built mainly for ad hoc questions, so Chris will train her
so that she can access the database and build queries to answer her ad hoc questions. Helen
has indicated a few standard questions she expects to ask periodically. Chris will develop
several types of prewritten routines (forms, reports, and queries) that can make it easier for
Helen to answer these standard questions (so she does not have to program these questions
from scratch).
During the prototyping development process, Chris may develop many examples of each of
these routines as Helen communicates more clearly what she wants the system to be able to
do. At this early stage of development, however, Chris wants to develop one routine to create
the first prototype. One of the standard sets of information Helen says she wants is a list of
each of the products in the Home Office product line showing each product's total sales to
date compared with its current year sales goal. Helen may want the results of this query to
be displayed in a more stylized fashion- an opportunity to use a report- but for now Chris will
present this feature to Helen only as a query.
The query to produce this list of products appears in Figure 1-19, with sample output in Figure
1-20. The query in Figure 1-19 uses SQL. You can see three of the six standard SQL clauses ill
this query: SELECT, FROM, and WHERE. SELECT indicates which attributes will be shown in the
result. One calculation is also included and given the label "Sales to Date." FROM indicates
which tables must be accessed to retrieve data. WHERE defines the links between the tables
and indicates that results from only the Horne Office product line are to be included. Only
limited data are included for this example, so the Total Sales results in Figure 1-20 are fairly
small, but the format is the result of the query in Figure 1-19.
Chris is now ready to meet with Helen again to see if the prototype is beginning to meet her
needs. Chris shows Helen the system. As Helen makes suggestions, Chris is able to make a
few changes online, but many of Helen's observations will have to wait for more careful work
at his desk.
Space does not permit us to review the whole project to develop the Horne Office marketing
support system. Chris and Helen ended up meeting about a dozen times before Helen was
satisfied that all the attributes she needed were in the database; that the standard queries,

5
forms, and reports Chris wrote were of use to her; and that she knew how to write queries
for unanticipated questions. Chris will be available to Helen at any time to provide consulting
support when she has trouble with the system, including writing more complex queries,
forms, or reports. One final decision that Chris and Helen made was that the performance of
the final prototype was efficient enough that the prototype did not have to be rewritten or
redesigned. Helen was now ready to use the system.

Administering the Database

The administration of the Home Office marketing support system is fairly simple. Helen
decided that she could live with weekly downloads of new data from Pine Valley's operational
databases into her MS Access database. Chris wrote a C# program with SQL commands
embedded in it to perform the necessary extracts from the corporate databases and wrote
an MS Access program in Visual Basic to rebuild the Access tables from these extracts; he
scheduled these jobs to run every Sunday evening. Chris also updated the corporate
information systems architecture model to include the Home Office marketing support
system. This step was important so that when changes occurred to formats for data included
in Helen's system, the corporate CASE tool could alert Chris that changes might also have to
be made in her system.

Future of Databases at Pine Valley

Although the databases currently in existence at Pine Valley adequately support the daily
operations of the company, requests such as the one made by Helen have highlighted that
the current databases are often inadequate for decision support applications. For example,
following are some types of questions that cannot be easily answered:
1. What is the pattern of furniture sales this year, compared with the same period last year?
2. Who are our 10 largest customers, and what are their buying patterns?
3. Why can't we easily obtain a consolidated view of any customer who orders through
different sales channels, rather than viewing each contact as representing a separate
customer?
To answer these and other questions, an organization often needs to build a separate
database that contains historical and summarized information. Such a database is usually
called a data warehouse or, in some cases, a data mart. Also, analysts need specialized
decision support tools to query and analyze the database. One class of tools used for this
purpose is called online analytical processing (OLAP) tools. We describe data warehouses,
data marts, and related decision support tools in Chapter 9. There you will learn of the interest
in building a data warehouse that is now growing within Pine Valley Furniture Company.

E-R MODELING EXAMPLE: PINE VALLEY FURNITURE COMPANY

Developing an E-R diagram can proceed from one (or both) of two perspectives. With a top-
down perspective, the designer proceeds from basic descriptions of the business, including
its policies, processes, and environment. This approach is most appropriate for developing a

6
high-levelE-R diagram with only the major entities and relationships and with a limited set of
attributes (such as just the entity identifiers). With a bottom-up approach, the designer
proceeds from detailed discussions with users, and from a detailed study of documents,
screens, and other data sources. This approach is necessary for developing a detailed, "fully
attributed" E-R diagram.
In this section, we develop a high-level ERD for Pine Valley Furniture Company, based largely
on the first of these approaches (see Figure 2-22 for a Microsoft Visio version). For simplicity,
we do not show any composite or multi valued attributes (e.g., skill is shown as a separate
entity type associated with EMPLOYEE via an associative entity, which allows an employee to
have many skills and a skill to be held by many employees).
Figure 2-22 provides many examples of common E-R modeling notations, and hence, it can
be used as an excellent review of what you have learned in this chapter. In a moment, we will
explain the business rules that are represented in this figure. However, before you read that
explanation, one way to use Figure 2-22 is to search for typical E-R model constructs in it, such
as one-to-many, binary, or unary relationships. Then, ask yourself why the business data was
modeled this way.
For example, ask yourself
• Where is a unary relationship, what does it mean, and for what reasons might the
cardinalities on it be different in other organizations?
• Why is Includes a one-to many relationship, and why might this ever be different in some
other organization?
• Does Includes allow for a product to be represented in the database before it is assigned to
a product line (e.g., while the product is in research and development)?
• If there were a different customer contact person for each sales territory in which a
customer did business, where in the data model would we place this person's name?
• What is the meaning of the Does Business In associative entity, and why does each Does
Business In instance have to be associated with exactly one SALES TERRITORY and one
CUSTOMER?
• In what way might Pine Valley change the way it does business that would cause the Supplies
associative entity to be eliminated and the relationships around it to change?
Each of these questions is included in Problem and Exercise 3 at the end of the chapter, but
we suggest you use these now as a way to review your understanding of E-R diagramming.
From a study of the business processes at Pine Valley Furniture Company, we have identified
the following entity types.
An identifier is also suggested for each entity, together with selected important attributes:
• The company sells a number of different furniture products. These products are grouped
into several product lines. The identifier for a product is Product ID, whereas the identifier for
a product line is Product Line ID. We identify the following additional attributes for product:
Product Description, Product Finish, and Product Standard Price. Another attribute for
product line is Product Line Name. A product line may group any number of products bt.lt
must group at least one product. Each product must belong to exactly one product line.

7
• Customers submit orders for products. The identifier for an order is Order 10, and another
attribute is Order Date. A customer may submit any number of orders but need not submit
any orders. Each order is submitted by exactly one customer. The identifier for a customer is
Customer 10. Other attributes include Customer Name, Customer Address, and Customer
Postal Code.
• A given customer order must request at least one product and only one product per order
Line item. Any product sold by Pine Valley Furniture may not appear on any order line item
or may appear on one or more order line items. An attribute associated with each order line
item is Ordered Quantity.
• Pine Valley Furniture has established sales territories for its customers. Each customer may
do business in any number of these sales territories or may not do business in any territory.
A sales territory has one to many customers. The identifier for a sales territory is Territory ID
and an attribute is Territory Name.
• Pine Valley Furniture Company has several salespersons. The identifier for a salesperson is
Salesperson ID. Other attributes include Salesperson Name, Salesperson Telephone, and
Salesperson Fax. A salesperson serves exactly one sales territory. Each sales territory is served
by one or more salespersons.
• Each product is assembled from a specified quantity of one or more raw materials. The
identifier for the raw material entity is Material ID. Other attributes include Unit Of Measure,
Material Name, and Material Standard Cost. Each raw material is assembled into one or more
products, using a specified quantity of the raw material for each product.
• Raw materials are supplied by vendors. The identifier for a vendor is Vendor 10. Other
attributes include Vendor Name and Vendor Address. Each raw material can be supplied by
one or more vendors. A vendor may supply any number of raw materials or may not supply
any raw materials to Pine Valley Furniture. Supply Unit Price is the unit price at which a
particular vendor supplies a particular raw material.
• Pine Valley Furniture has established a number of work centers. The identifier for a work
center is Work Center 10. Another attribute is Work Center Location. Each product is
produced in one or more work centers. A work center may be used to produce any number
of products or may not be used to produce any products.
• The company has more than 100 employees. The identifier for employee is Employee ID.
Other attributes include Employee Name, Employee Address, and Skill. 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. A skill may be possessed by more than one employee or possibly no employees.
• Each employee has exactly one supervisor; however, a manager has no supervisor. An
employee who is a supervisor may supervise any number of employees, but not all employees
are supervisors.