Parts and Inventory Control for ConAgra Grocery Products via Computerized Maintenance Management Software (CMMS)

HIGH-LEVEL MODELING and CONCEPTUAL SCHEMA DESIGN

Based on the requirements listed above, a high-level model or ER-diagram, can be created to establish the basic construct of the schema.

Entity Types, Attributes and Values

In order to construct an ER diagram for the EasyMainsaver computerized maintenance management software, the control factors for stockroom parts must be categorized into entity types and attributes. The illustrations below describe entity types (a set of entities that have the same attributes) and attributes (a particular property that describes an entity) with their independent existences.

The table above illustrates the STOCK_ITEM entity type which is described by the following attributes (all of which are atomic):

• Stk_No: The stock number of the stock item (part). A value consists of five numbers followed by a single letter. Ex.: 00039P, 10020X (see Requirements Analysis for further details).
• Description: A formal description of the part item – values can be up to 50 characters long.
• Part_No: The part number; this attributes may also be used to describe the part manufacturer (note: manufacturer may be different from the supplier or vendor). Values for this attribute are not specifically defined.
• Vendor_ID: The vendor or supplier of the part; identified here as an abbreviation of their name. Values for this attribute usually consist of abbreviated names. Ex: Bosch is BSCH, etc.
• Loc_ID: The physical location of a part; the values are coded as an ID that combines location attributes (see LOCATION attributes). This attribute is used for relationship purposes (see next section).
• OH_Qty: The quantity of parts on hand for that particular stock item. A value can consist of a single, double or triple-digit number.

The table above illustrates the LOCATION entity type; each stock item, with respect to location, is described using the following attributes (all of which are atomic):

• Room_ID: An abbreviated description of the physical room in which the stock item is found. For example, WH1 is a value for this attribute that denotes the main stockroom, MEZZ is the mezzanine storage area, etc.
• Cabinet: A numerical identification of the cabinet in which the stock item is found. Values are single or double-digit numbers.
• Drawer: All cabinets have “drawers”. These are the horizontal “dimensions” of the cabinet , the values of which are denoted by single alphabetical letters.
• Shelf: Some cabinets are shelf-style (although we call them all cabinets for the sake of simplicity). The Shelf attribute is an additional parameter that's used to describe a “cubby hole” location – it's for larger part items. The value is denoted by a single or double-digit number. This attribute is not always used.

Loc_ID: used in relationships (see next section).

The table above illustrates the EQUIPMENT entity type; this describes the particular equipment, machine or system that each particular stock item is associated with using the following attributes (all of which are atomic):

• Name_of_Equipment: The actual name of the machine, equipment, or system that stock items are used on. This description may or may not contain the manufacturer of the machine. The values for this attribute may look like the following: “Aseptic FFS, AS 78,” “Cup Coder, VideoJet, Excel.”
• Equip_No: A plant-designated identification code for machines, equipment and systems (see Requirement Analysis). A value for this attribute may look like “301-000”.
• Stk_No: The stock number of a particular stock item used on a machine (used for relationships).
• Part_No: The part number for an item used on a machine.

The table above illustrates the VENDOR entity type; this describes a particular vendor from whom stock items are purchased. It consists of the following attributes (all of which are atomic):

• Name: The name of a vendor. An example of a value is “Grainger.”
• Addr: The address of a vendor. This attribute simple or atomic in that its value consist of a complete address (i.e., street, city, zip are all described within a single value).
• Phone_No: The phone number of the vendor.
• FAX_No: The FAX number of the vendor.
• Vendor_ID: The vendor or supplier of the part; identified here as an abbreviation of their name. Values for this attribute usually consist of abbreviated names. Ex: Bosch is BSCH, etc.

The table above illustrates the ORDER_POINT entity type. It is used to control the requisition of parts and consists of the following attributes (all of which are atomic):

• Order_Pt: A quantity value at which a particular stock item needs to be ordered. In the real Mainsaver program, when Tot_OH = Order_Pt, a fax P/O is automatically generated.
• Tot_OH: The quantity of parts on hand for that particular stock item. A value can consist of a single-, double- or triple-digit number.
• Max_OH: A company constraint for the maximum number of items allowed to be kept in inventory. This value is also used to calculate the quantity of an order. Although the ordering of parts is not covered in this simple design, a derived value may be computed for an attribute as follows: Amount_to_Order = Max_OH minus Order_Pt.
• Stk_No: The stock number for a stock item.
• Cost_ea_$: The cost of each item (or package). The value is a decimal number.
• Lead_time: The lead time, in number of weeks. This is a derived value and is represented a single or double-digit integer. The value can e derived by the following arithmetic operation: Lead_time = Date_promised minus current date.
• Vndr_ID: The vendor or supplier of the part; identified here as an abbreviation of their name. Values for this attribute usually consist of abbreviated names. Ex: Bosch is BSCH, etc.
• Date_promised: The latest date the item will be delivered.
• Date_last_activity: The last time the values for any attributes for a particular entity were amended. This can include the receiving date or when a part is taken from the inventory to be used on a machine.

The diagram below illustrates the relationship of a sample entity type, EQUIPMENT, and a few of its entities. For example, the first item is called “Cup Coder” (the machine or equipment); this is the value of the attribute Name_of_Equipment. Listed next is the value for the Equipment_No attribute, and so on. The entire group of entities, from e₁ to e_n, represent the entity set of EQUIPMENT.

Relationships, Relationship Types, Roles, and Structural Constraints

Looking at the ER diagram (see Appendix) various relationships can be noted. A description of each is now presented.

Relationship Role Name: KEPT_IN:

[a] This relationship type between the entity types STOCK_ITEMS and LOCATIONS is binary – that's , there are two participating entity types. There are no recursive relationships used.

[b] Cardinality Ratio: N:1. The reason for this is as follows: the most specific description for a part location is the drawer of a cabinet. The inside of the drawer is divided into compartments (much like the compartments inside a fishing tackle-box). Each compartment is labeled with a specific stock number. Therefore, many individual stock-numbered items are found within a single drawer. This means many stock items (N) may be related to a single location (1).

The following diagram illustrates the binary relationship KEPT_IN between STOCK_ITEM and LOCATION. Lower case letters followed by the integer subscript denote individual entities within each entity type. The entity type names appear above each terminator.  Please note: the GIF image may take a few seconds to appear).

[c] Participation Constraints and Existence Dependencies: The thick black line that connects the relationship to the two entity types represents total participation whilst the thin line partial. It can be seen that LOCATIONS participates totally in the KEPT_IN relationship type whereas STOCK_ITEMS is only partial. This is due to the fact that as new items are being added to the inventory for the first time, they may “float” at the Receiving Dock for a period of time before a formal location is assigned to them. Therefore, STOCK_ITEMS may exist without having an associated LOCATION; but, each LOCATION must have at least one STOCK_ITEM.

Relationship Role Name: USED_ON

[a] This relationship type between the entity types STOCK_ITEMS and EQUIPMENT is binary. There are no recursive relationships used.

[b] Cardinality Ratio: N:M. A particular stock item may be used by many machines (pieces of equipment or systems), such as a ½ inch bolt; concurrently, a particular piece of equipment or machine may have many parts associated with it.

[c] Participation Constraints and Existence Dependencies: Participation is total on both sides of the relationship. That is, one can't exist without the other. This is a business constraint in that every stock item ordered, even new items, must belong to a particular equipment, machine or system. The definition of “equipment” can also include particular rooms or areas of the plant, all of which have a particular equipment number.

The following diagram illustrates the binary relationship USED_ON between STOCK_ITEMS and EQUIPMENT. Lower case letters followed by the integer subscript denote individual entities within each entity type. The entity type names appear above each terminator.  Please note: the GIF image may take a few seconds to appear).

Relationship Role Name: SUPPLIED_BY

[a] This relationship type between the entity types STOCK_ITEMS and VENDORS is binary. There are no recursive relationships used.

[b] Cardinality Ratio: N:1. A particular stock item may only be provided by one vendor (that is, a particular item can't have more than one vendor at any given time); however, a particular vendor may supply more than one stock item.

[c] Participation Constraints and Existence Dependencies: The participation on both sides is total. This is a business constraint in that all parts must to come from a supplier or vendor (they are not fabricated in-house). Needless to say, all vendors must have at least one stock item related to them. All vendor accounts at ConAgra are “open”. If an account is closed, the vendor entity is deleted; the stock item associated with that vendor is then either eliminated or related to another vendor.

Relationship Role Name: HAVE_AN

[a] This relationship type between the entity types STOCK_ITEMS and ORDER_POINT is binary. There are no recursive relationships used.

[b] Cardinality Ratio: 1:1. A particular stock item will have exactly one order point. And, a particular order point will have only one stock item associated with it. This is due to the fact that the primary key, Stk_No, identifies each tupple as unique (see Relationship Model Concepts section)

[c] Participation Constraints and Existence Dependencies: An order point entity must have a related stock item. However, a particular stock item can exist without an order point if was a one-time order (a rare situation but, by all means, possible).

Schema Diagram

The schema diagram of the EasyMainsaver relational database is displayed below (shown with primary keys underlined):

STOCK_ITEM

LOCATION

EQUIPMENT

VENDORS

ORDER_POINT

OUTLINE

1. INTRODUCTION
2. REQUIREMENTS ANALYSIS and MINIWORLD RULES
3. HIGH-LEVEL MODELING and CONCEPTUAL SCHEMA DESIGN
4. RELATIONSHIP MODEL CONCEPTS
5. SQL
6. APPENDIX
7. REFERENCES and RESOURCES

top of page

Industrial Maintenance pages:

Industrial Preventive Maintenance (PM) FAQ | Choosing Computerized Maintenance Management Software (CMMS) | Maintenance Management Books

Maintenance-Industrial home page