6 Example Operations

Database Design

The next figure shows a customer order form for a fictitious company. The company owner has decided to design an Eloquence database to be used to store information from the order form. Once the data has been stored in the database, the owner wishes to generate various sales reports using the order data.

Demonstration Bicycle Company

	Order Date		______ (MMYY)		 Order Number		______
	Ship Date	 	______ (MMYY)		 Region					______
					 Salesperson		______
	Name		___________________________________
       Address			___________________________________
	City		______________  State		__	__________
	Zip Code		______________	Country	____________

	Product		______________	 Price	 ____________

		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

Eloquence Database Manual - 19 DEC 2002