DEVELOPING A DATABASE APPLICATION 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 felt that the computer information systems they had been using (based on traditional file processing)
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 felt 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. Figure 1-13 displays a general schematic of the computer network within Pine Valley Furniture
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. Before addressing a request that has been received for direct access to sales data from Helen Jarvis, product manager for home office furniture, let’s review the process that Pine Valley Furniture Company followed as they originally moved into a database environment. Pine Valley Furniture Company’s first step in converting to a database approach was to develop a list of the high-level entities that support the business activities of the organization. You will recall that an entity is an object or concept that is important to the business. Some of the high-level entities identified at Pine Valley Furniture were the following:
CUSTOMER, PRODUCT, EMPLOYEE, CUSTOMER ORDER, and DEPARTMENT
After these entities were identified and defined, the company proceeded to develop an enterprise data model. Remember that an enterprise data model is a graphical model that shows the high-level entities for the organization and associations among those entities. The results of preliminary studies convinced management of the potential advantages of the database approach. After additional data modeling steps had been completed, the company decided to implement a modern relational database management system that views all data in the form of tables. (We cover relational databases in more detail in Chapter 4.) A simplified segment of the project data model used is discussed next.
Simplified Project Data Model Example
A segment of the project data model containing four entities and three pertinent associations is shown in Figure 1-3b. The entities shown in this model segment are the
following:
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. Before addressing a request that has been received for direct access to sales data from Helen Jarvis, product manager for home office furniture, let’s review the process that Pine Valley Furniture Company followed as they originally moved into a database environment. Pine Valley Furniture Company’s first step in converting to a database approach was to develop a list of the high-level entities that support the business activities of the organization. You will recall that an entity is an object or concept that is important to the business. Some of the high-level entities identified at Pine Valley Furniture were the following:
CUSTOMER, PRODUCT, EMPLOYEE, CUSTOMER ORDER, and DEPARTMENT
After these entities were identified and defined, the company proceeded to develop an enterprise data model. Remember that an enterprise data model is a graphical model that shows the high-level entities for the organization and associations among those entities. The results of preliminary studies convinced management of the potential advantages of the database approach. After additional data modeling steps had been completed, the company decided to implement a modern relational database management system that views all data in the form of tables. (We cover relational databases in more detail in Chapter 4.) A simplified segment of the project data model used is discussed next.
Simplified Project Data Model Example
A segment of the project data model containing four entities and three pertinent associations is shown in Figure 1-3b. The entities shown in this model segment are the
following:
The three associations (called relationships in database terminology) shown in the Figure 1-3b (represented by the three lines connecting entities) capture three fundamental business rules, as follows:
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 LINEs. Conversely, each ORDER LINE Is For exactly one PRODUCT. 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.
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. Many entities would include more attributes than we list in Figure 1-3b, but we have included a sufficient number to help you begin to understand how the data will be stored in a database. Figure 1-14 shows the following 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-3b). 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. An important property of the relational model is that it represents relationships between entities by values stored in the columns of the corresponding tables. For example, notice that CustomerID is an attribute of both the Customer table and the Order table. As a result, we can easily link an order to its associated customer. For example, we can determine that OrderID 1003 is associated with CustomerID 1. Can you determine which ProductIDs are associated with OrderID 1004? In subsequent chapters, you will learn how to retrieve data from these tables by using a powerful query language, SQL, which exploits these linkages . 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
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 LINEs. Conversely, each ORDER LINE Is For exactly one PRODUCT. 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.
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. Many entities would include more attributes than we list in Figure 1-3b, but we have included a sufficient number to help you begin to understand how the data will be stored in a database. Figure 1-14 shows the following 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-3b). 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. An important property of the relational model is that it represents relationships between entities by values stored in the columns of the corresponding tables. For example, notice that CustomerID is an attribute of both the Customer table and the Order table. As a result, we can easily link an order to its associated customer. For example, we can determine that OrderID 1003 is associated with CustomerID 1. Can you determine which ProductIDs are associated with OrderID 1004? In subsequent chapters, you will learn how to retrieve data from these tables by using a powerful query language, SQL, which exploits these linkages . 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 application,such as order entry, so that more internal business activities that require access to data in the database server can 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. Although the database quite adequately supports daily operations at Pine Valley Furniture Company, managers soon learned that the same database is 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.
A Current Pine Valley Furniture Company Project Request
Atrait 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. 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 such systems as Helen is requesting to be built on 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 prototyping 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 very quickly address project planning, 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.
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.
A Current Pine Valley Furniture Company Project Request
Atrait 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. 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 such systems as Helen is requesting to be built on 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 prototyping 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 very quickly address project planning, 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.