You are here

Logical Database Design

19 January, 2016 - 12:35

In Database Management Systems, we compared data as it would be stored in a file(s) with that same data when stored in a database, with emphasis on the relational database model (see Figure 3.7  and Figure 3.8). In this section, we will depict the relational tables for the data we have just mentioned in the discussion of the:

  • Customer master data
  • Sales order master data
  • Completed picking ticket file
  • Shipping notice data

To do so, we are well advised to first redraw the E-R diagram appearing in Figure 3.9. Figure 11.18 is our new E-R diagram. It differs from Figure 3.9 in that the SALES event in Database Management Systems now has been divided into three events comprising the sale—namely, picking goods (STOCK PICKING event in Figure 11.18), shipping goods (SHIPMENT event), and billing the customer for the shipment (SALES INVOICE event). From Figure 11.18, we develop the relational tables appearing in Figure 11.19.

Compare the CUSTOMERS relation in part (a) of Figure 11.19 with the discussion of the customer master data and observe that the data elements (attributes) are essentially the same. Note that the relation allows for both a customer address and “ship to” address, each being subdivided into four attributes—street address, city, state, and ZIP code—to facilitate database inquiries using any of these attributes. Now compare part (b) of the figure—the SALES_ORDERS and SALES_ORDER line item INVENTORY relations, respectively—to the sales order screen in Figure 11.7  and the discussion of the sales order master data. Here we see some marked differences. The two sales order tables contain far fewer data elements than the sales order document itself because many of the elements needed to complete the document are available from other relations. Recall that a major advantage of a database approach to data management is the elimination of redundant data items. Therefore, using the Cust_No from SALES_ORDERS, we can obtain the customer’s name, address, ship to name, ship to address, and credit terms from the CUSTOMERS relation. Likewise, using Item_No from SALES_ORDER line itemINVENTORY, we can obtain from the INVENTORY_ITEM relation the description of the goods and unit selling price. Finally, using the primary key from SALES_ORDER line item INVENTORY (i.e., the combination of SO_No/Item_No), we can determine the quantity picked/shipped from the INVENTORY line item STOCK_PICK relation in Figure 11.12. Other items often found on the sales order form—quantity back ordered, extended price, and the components of the sales order trailer—can be computed from other data and therefore need not be stored in any table.

Parts (c) (d) and (e) of Figure 11.19 need no particular comment, except to note once again that the four relations contain relatively few attributes because most of the data needed to complete a picking ticket or shipping notice document reside in other relations. For example, an actual picking ticket often takes the physical form of a duplicate copy of the sales order document. The primary item that differentiates the two documents is the warehouse location, which must appear on the picking ticket to facilitate the actual picking of the goods. Even in sophisticated enterprise systems, picking tickets can be paper based since the packers must move around a warehouse to find the ordered goods. Once the goods are picked, the picking ticket document could be completed by adding the quantity picked, date picked, and identification of the person who picked the items, attributes which appear in the two relations in part (c).1

Figure 11.18 Entry-Relationship (E-R) Diagram for the M/S Process 
Figure 11.19 Selected Relational Tables (Partial) for the M/S Process