6 Example Operations
Demonstration Bicycle Company
ORDER FORM
Order Date ______ (MMYY) Order Number ______
Ship Date ______ (MMYY) Region ______
Salesperson ______
Name ___________________________________
Address ___________________________________
City ______________ State __ __________
Zip Code ______________ Country ____________
Product ______________ Price ____________
Options
Option Type Price
_________________________________________________
__________ __________ _________
__________ __________ _________
__________ __________ _________
__________ __________ _________
__________ __________ _________
__________ __________ _________
Total: _________
The database structure shown next could be used to store the required data. This structure offers little advantage over using a direct (random) access data file, but does provide a starting point for the database design. Notice that in order to generate a list of all orders for a particular product, all entries in the detail set must be scanned. Also no provision is made in this structure to handle an order which includes more than four options. In order to provide a common basis for diagramming database designs, pentagons are used to represent manual master data sets, triangles are used to represent automatic master data sets, and trapezoids are used to represent detail data sets. The item names and item types used to define the data entry within the data set are also shown.
Figure 9 Possible Database Structure
The next database structure shows a quick method of generating lists of orders for a particular product number. Using this structure, an application program can perform a DBFIND (see page 59 ) on the order detail data set to locate the chain head for a particular product number (PRODUCT-NO). The program can then perform successive chained DBGETs (see page 59 ) to retrieve the required orders. Only the desired orders are accessed, thus reducing the time required to generate the list when a large number of orders are stored in the database.
Figure 10 Possible Database Structure
Although an automatic master set could have been used for the PRODUCT master set, a manual master set was chosen for two reasons. First, since the PRODUCT set is a manual master, Eloquence DBMS automatically checks the validity of the product number (PRODUCT-NO) as an order is entered into the database. Second, additional data can be stored in the PRODUCT data set, such as a product description (PRODUCT-DESC). Automatic master entries cannot contain items other than the search item.
The database structure shown next utilizes a detail data set to store the option information and a master data set to store the rest of the order data. This organization of data corresponds to dividing an order into two forms, as shown in the next figure. Unlike the previous structures shown, the number of options that can be stored with any order is limited only by the number of free entries in the OPTION data set. In addition, this organization requires fewer disk space to store order information when fewer than four options are ordered. (In the previous examples, the space to store four options with each order is allocated whether the options are purchased or not.) Notice that all order numbers within the ORDER data set must be unique, since the order number is a search item.
Figure 11 Possible Database Structure
Figure 12 Order Form with Separate Option Forms
The advantages of the previous two structures are incorporated into the structure shown below. The CUSTOMER data set contains all order information except for option data, which is stored in the OPTION data set. Options are logically linked to the order through the ORDER master data set. Orders for a particular product are chained together using the PRODUCT master set.
Figure 13 Possible Database Structure
Although Eloquence DBMS prevents orders from being entered for products not in the PRODUCT master, it does not prevent duplicate order numbers (ORDER-NO) from being entered. An order entry program can easily prevent the entry of duplicate order numbers, however, by performing a calculated DBGET on the ORDER master set before entering the order into the CUSTOMER and OPTION data sets.
The final database design, which is used throughout the rest of this chapter, is shown below. A second manual master set, LOCATION, is used to chain orders from a particular sales region. A second automatic master, DATE, is used to locate orders for a particular order date or ship date. Dates are stored as an integer number (to reduce disk space requirements), and are easily converted to and from an ASCII character date within an application program.
Figure 14 Sales Analysis Database