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

RELATIONSHIP MODEL CONCEPTS

The ER high-level model, if designed carefully, can translate directly to the relationship model (logical design of schema) used to create the physical database. The mdb database created in Access for this project is a bit simpler than the conceptual schema modeled above. Specifically, certain attributes from the ORDER_POINT relation were omitted for simplicity. Otherwise, the Access file is fully functional.

Domains

Certain domain information was presented in REQUIREMENTS ANALYSIS and MINIWORLD RULES (see above). However, a detailed description of all domains, domain constraints (if any) and their atomic values is presented below:

• Stk_No: The set of 5-digit-plus-one-letter codes that define each stock item within the entire inventory.
• Tot_OH: A set of numbers (integers) with possible values between 0 and 1000 (inclusive).
• Order_Pt: A set of numbers each between 0 and 1000 (inclusive).
• Max_OH: A set of numbers with possible values between 1 and 1000 (inclusive).
• Lead_time: A set of numbers (integers) with possible values between 1 and 100 (inclusive).
• Cost_ea_$: A set of floating-point numbers with possible values between 0 and 1,000,000 (inclusive).
• Vndr_ID or Vendor_ID: A set of codes, usually alphabetical.
• Loc_ID: A set of alpha-numeric codes.
• Room_ID: Another set of alpha-numeric codes.
• Cabinet: A set of one- or two-digit numbers (integers).
• Shelf: A set of one-digit numbers (integers) between 1 and 10 (inclusive).
• Drawer: A set of single letters between A and M (inclusive).
• Name_of_Equipment: A set of names of machines or equipment (i.e. hardware).
• Eqiuip_No: A set of 6-character codes.
• Part_No: A set of alpha-numeric codes (usually with equipment maker in parenthesis).

Key Constraints and Referential Integrity

In order to form relationships, keys must be assigned to establish uniqueness constraints. For three of the relations developed, the stock number proved to be the most convenient attribute to designate as the primary key (see Schema Diagram or Relational Tables). When designing the individual relations, all attempts should be made to eliminate unnecessary data. However, for this project, it was deemed suitable to make compromises in order to simplify the design.

Referential integrity constraints are formally defined through the usage of foreign keys. For the EasyMainsaver schema, relationships, with respect to foreign keys, may be graphically shown. An arc directed from the foreign key to its referencing relation demonstrates the usage of the foreign key; the arrowhead points to the referencing relation (that is, the primary key).  A diagram appears below (GIF image may take a few seconds to load):

AMAZON multi-meters discounts AMAZON oscilloscope discounts

Relational Tables (also see the Access mdb file associated with this project):

STOCK_ITEMS

LOCATIONS

EQUIPMENT

ORDER_POINT

VENDORS

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