CHAPTERS Databases in Flexible Manufacturing Systems William D. Engelke Research Institute University of Alabama in Huntsville Huntsville, Alabama 1. Inrodsetion 111 V. Compute-Aided Manutoctring A. The Meaning of a Database in Considerations When Planning for Fleible Mancfactaring Systems Fleible Manufacturing Systems 129 M VI. Tie Flesible Manuactring Sytem 1, Importance of a Database in the Database System 131 Fenible Manofacturing System A. Major SoftwarerDatabuse Subsystems Environment 118 iH . Database Technologies 114 B. Supporting Database Systems 134 1, Manufacturing Data Systems 122 Systems Design and Data Flow IML, Manutaetoring Data Flow 122 Planning 135 A. Overview 122 D. Operation 140 BB. Upsteam-Doweatream Isues 123. VIL. Questions « Be Considere’ When IV. Computer-Aided Design Consiertions Planning the Flexible Manufacturing ‘When Planning for Flexible System Datsbase 14 Manufacturing Systems. 124 IIL. Case Stay Example 142 A. Library of Pans 124 1X Concsions 148 B. Group Technology 126 Referens 144 C. Computer-Aided Design orter Readings 148 Requirements for Flexible Manufactring System Data Preparation 126 I. Introduction A. THE MEANING OF A DATABASE IN FLEXIBLE MANUFACTURING SYSTEMS: A database is a necessary and integral part of any flexible manufacturing system (FMS). The most basic function of a database in an FMS is providing storage and availability of information needed to control the manufacturing pro- tL2 Witiom D. Engelke cess. In performing this role, the database handles information needed for essentially all tasks of controlling the FMS. These include planning machine loading and management of the software used to control programmable manu- facturing equipment such as numerically controlled (NC) machines. Also, da- tabases function to interface the FMS with other computer-centered manufac turing systems such as material requirements planning (MRP II), computer-aided design (CAD), computer-aided manufacturing (CAM), and inventory control systems. See Figure | Databases operate in conjunction with software (computer programs) that carry out movement and organization of data, combined with necessary com- putations and decision-making processes. It has been estimated that 15 to 30% Of the cost of a flexible manufacturing system is incurred in control software (Greenwood, 1988a), with 50 to 80% of that being devoted to contingency management (Greenwood, 1988b) (handling exceptions, special cases, and error recovery), Databases and software ate designed together and are inseparable For a successful FMS implementation, good database design is a must. First, it is important to understand what a database is. It is much more than simply 2 large computer file file is collection of data Itmay be organized So that itis retrievable only in the order in which it was stored (sequential) or so that it can be retrieved in any order desired (random). In general, 2 file contains just one type of data (e.g., a list of items in inventory) and all records are laid out in the same way. A database differs in many respects, because a database +s likely to contain many files. It is much more than simply comput- erized filing cabinet. * Has a logical structure: The topology of the database is optimized to the applications which use it. The database system for an FMS is a tightly fitting overlay on the manufacturing business it serves. Clearly, this means that it is necessary to understand the business quite well to properly design a database to serve it + Works in conjunction with application software to create a manufacturing ‘data system supporting the factory operation. * Is frequently designed to be able to serve many applications concurrently, in a coordinated fashion; the FMS may share a database with other pans of the business + Must be considered a strategic business resource, much as plants and equipment. It requires maintenance, staffing, occasional replacement of obsolete facilites, and can be used to generate revenue. + Can be used for diverse purposes, including quality control, productivity ‘measurement, process planning. optimization of machine utilization, simulation, and many others,5. Dotabases in Fleible Manufacturing Sytems 13 Planning} } Marketing Cuan 8 Cn ea ; . NC Programming : : sont Wats oe : oe Bill of Process Setup : ccccarcs | tqmireowanens Producten Panang See EL er cone Segteroney Cfosay | aes eT Calendar an : 7 Sores FMSiProduction i oy Sec mo : a mans couse Figure 1, Typicl system design and comesponding database layout for fexible manufsctorng sper14 William D. Engebe ‘Actual functions of planning and NC part programming are performed by software which uses the database as a source and repository of information. Databases can be designed so as to greatly facilitate the operation of software (or retard it if improperly designed). Since each FMS database is tailored closely to the requirements of the individual business, and each business is different, it follows that each FMS database will be different. There is no one “best” database layout or architecture. ‘This chapter is devoted to exploring the issues a system developer must face when designing databases to serve an FMS. Several database layouts are ex- plained to illustrate the underlying principles behind FMS database design. In terfaces between FMS databases and other management information system (MIS) ‘and manufacturing databases are explained B. IMPORTANCE OF A DATABASE IN THE FLEXIBLE MANUFACTURING SYSTEM ENVIRONMENT ‘An FMS database is a key clement of a strategy aimed at ensuring in- tegrity and reliability of information in the FMS system. A database manage. ‘ment system (DBMS) is a collection of programs which provide coordinated access to information stored in the database. As part of implementing a DBMS, companies often establish a data dictionary to create a central reference which defines the content and format of each data element. A DBMS’s most impor- tant function is to integrate the data so that logical relationships necessary 10 ‘operation of the business are maintained. This can reduce the storing of re- bocier Saaeve tocar tes Sine Shasy8 carponent € Subany A Component There may + > boomer ‘Saas A ainsion Shay 6 Component K ‘Subassy F ‘Component J ‘component may Y Y betrcken coun mo fsmaerats mere may “nore maybe actihonal ives te recor pes beet ine BOM storueto an atarydeptn, tomarnlantbe arene 2st 1 preduce the component Figure 3, strcture of hierarchical datbsse. A bin of material may contin many levels, wide each assembly (mac toplevel part umber in this case) having single oot-evel record18 Witton D. Engelke A LE Px; i Second chig level | Feu Thi chic) evel Figure. sirecturesisgram (SD) showing a ypc hierarchical database layout. The various records Shown (A.B, C, et.) may be te same or diferent record tyes of the hierarchy of the assembly are stored in the same database. The uppermost level of the hierarchy, in this case assemblies, is known as the roor level of the database. It is possible to access the database at the root level to simply list all available assemblies, The database may also be traversed at any lower level. It is also possible ro navigate the database by moving up and down the parent child relationships between the levels. In this way, it is possible to retrieve all the components used in an assembly (starting from the top level), or determine ‘on what assembly a component is used (starting from the bottom). This type of structure is very widely used because it parallels the actual relationships between real objects quite well. Many such relationships are found in a manufacturing environment, including information oriented toward product, process, tools, machines, conveyor lines, orders, and so forth. The SD which describes a hierarchical database shows the parent-child relationship between information items. Note that the vertices of the SD do not represent individual occurrences of data items, but types of data items (Figure 4). Here, a database contains a single root (by definition) type A; subordinate to the root are item types B and C; subordinate to B are D and E; and so forth. An item type can have multiple occurrences. Underneath each occurrence of that item type, there may be multiple occurrences of the item subordinate tit. Hierarchical databases are well suited to representing the one-to-many relation- ship. 4, Relational Databases ‘There are some real-world relationships which are not easily represented by a hierarchical database. The hierarchical paradigm breaks down when it is necessary to create a structure where a “child” record has multiple “parents.” This is the case in the many-to-many relationship. It is possible to force this structure upon a hierarchical database, but daing so will requite much redundancy ‘of information and possibly also exact a severe performance penalty. Note here5. Dataates in Flexible Manufactaring Systems 19 that redundancy in and of itself is not necessarily a problem. In the BOM example just cited, consider the following requirement. Ifa particular component is used in multiple assemblies, it will appear in multiple places in the database. If we ‘must do a “where-used” search (in which we start out with a given component and search for all assemblies in which it is used), a lot of searching will be required in a strictly hierarchical schema. ‘The database architecture known as relational can address this situation, Ina sense, a relational database is series of flat databases (each on one topic) linked together by relationships, A composite record database, as described earlier, can be split into several files called tables to form the beginnings of a relational database Consider the example of a factory’s machines and parts (Figure 5). The ‘composite record file would be broken into two files, one containing a list ofall the parts, and one containing a list ofall the machines. This split has lost some intelligence; it must be restored by creating a relationship file that links machines and parts In the case of the BOM, the relationship file can serve to point from assemblies to subassemblies to components, down through the product hierarchy. In that case, the relationship file contains relationships between records in a single file. A where-used relationship can also be inferred from this same file, Macher Engincoring| par rolaton Change Level ship fle painter ec Pointer a pointer porter pointer pointer painter Figure 5. A relational database reduces data redundancy by string most feds of information only ‘nts. Relationships are maintained by flaonship hes which pout back and forth to related information, These are xmetimes called "intersection" records120 Wiliam D.Engeke but for performance reasons its likely that it would be implemented as a separate relationship file with a different sort sequence. Even this method may not prove entirely satisfactory in this many-to-many relationship. Refer to the following section, 5. Network Database ‘The network database architecture is well suited to handling the many-to- ‘many relationship, It can represent the many-to-many situation with a good trade- off between redundancy and performance. Network databases are also good for handling complex. tangled relationships that do not lend themselves well to hierarchical representation (e.g.. the transitive graph). This situation can occur in FMSs that handle a number of product lines of varying complexity. especially ‘when subassemblies are used at different product levels depending on the order, customer, and so forth. Groups of related data items are known as sets, and the data items are ‘members of the set (see Figure 6). (This does not follow all the same rules as ‘mathematical set theory.) A set is ereated by linking related items in a circular loop which starts with the set owner, travels through each member, and ends at the owner. In the example of the factory machines and parts, a set begins with ‘4 machine, traverses through the parts it can manufacture, and ends at the machine. An item can be a member of more than one set. This would be a frequent occurrence in this example, as a typical part requires more than one Machine 223 Machine 224 Pans 5421 Lgl sees Figure 6. A network database sors relat information inst5. Databases in Flesible Manufacturing Systems 2 Factory Plant Organization Machine Machine Parts Manufactures by Mach pat Lol pad Bi of Materia Sine [omer] cron] [Smo] ooo a Figure 7. A record in a network database may be a member of mull sts machine to build it. Also, the machines are part of another set, the factory set, which contains all machines in the factory. As the network set SD Figure 7 shows, the factory, machine, part, and BOM sets can all be related. The many- to-many part-machine relationship is implemented via a link which acts as a bidirectional intersection between the sets. All the database architectures just discussed are implemented on computers using a variety of technical methods, including random access files, indexed files, hash tables, binary search trees, pointers, secondary indices, and many fther techniques. It is important that the team which is going to be selecting database technologies be familiar with these technical methods to avoid selecting architectures which will later prove unworkable after much investment has al- ready been made. The primary risk factor in designing databases for FMS performance. The wrong database architecture and/or design can reduce perfor- ‘mance enough that manufacturing schedules will be impacted. This will then lead toa requirement for expensive computer upgrades that will hurt the economic Viability of the project.