/Double

(Fixed)
DURATION- Number [0..1], duration-distributions. MEAN-TIME-
DISTRIBUTIONS TO-REPAIR
Table new-setup
Fields Data Type Description
INDEX Number [1], system use
SETUP- Number [1]. system use
CHANGEOVER
SETUP- Number [1], setup-definition-key. NEW-SETUP
DEFINITION-KEY
ESTIMATED- Number [0..1], estimated-durations. NE W-SETUP
DURATIONS
Table object-boundary
Fields Data Type Description
INDEX Number [1], system use
RESOURCE-OBJECT | Number [1], system use
PART-OBJECT Number [1], system use
BOUNDARY- Number [1..*], boundary-element.OBJECT-
ELEMENT BOUNDARY
Table operation-definition
Fields Data Type Description
INDEX Number [1], system use
OPERATION- Number [1], system use
DEFINITIONS.
type Enumeration _| [0..1]
identifier Number 01)
number Text 01]
name Text [0..1]
operation-type Enumeration | [1]
operation-subtype Enumeration | [0..1]
description Text [0..1]
STATION-KEYS Text [0..1], station-keys.OPERATION-

DEFINITION

Table operation-definition-key

Fields Data Type Description
INDEX Number [1], system use
PLAN-STEP Number [1], system use
operation-definition- | Number 0)

identifier

operation-definition-__| Text {)

60


Appendix B: Enumerations

Table Field Enumeration items
enumeration-behavior in-layout TBD"
enumeration-controller-type | in-machine-program TBD"
in-machines TBD"
enumeration-day-name in-calendars SUN, MON, TUE, WED,
THR, FRI, SAT
enumeration-day-number day-number 0, 1, 2,3, 4, 5,6
day-name SUN, MON, TUE, WED,
THR, FRI, SAT
enumeration-efficiency- in-machines TBD*

rating

enumeration-employment-
status

in-employees

FULL-TIME, PART-TIME,
CONTRACTOR, LAID-

OFF, FORMER-

EMPLOYEE
enumeration-file-format in-references WORD, TEXT, «..
enumeration-logical- in-child-work-items, AND, OR, NOT

relationship

in-plan-definition,
in-precedent-constraints

enumeration-maintenance-
state

in-cranes,
in-machines,
in-stations

COMPLETED, UNDER-
REPAIR, TO-BE-REPAIR,
UNKNOWN

enumeration-material-type

in-materials-inventory

TBD*

enumeration-operational-
state

in-cranes,
in-machines,
in-stations

BUSY, IDLE, RESERVED,
OUT-OF-ORDER

enumeration-operation- in-operation-definitions TBD*
subtype
enumeration-operation-type | in-operation-definitions TBD*
enumeration-overtime- in-employees YES, NO
eligibility
in-time-sheets YES, NO
enumeration-priority-rating | in-departments 1, 2,3,4,5
in-organizations 1, 2, 3,4, 5
in-procurements REGULAR, URGENT
in-work HOT, HOT-HOT,
REGULAR
enumeration-salary-type in-employees TBD*

enumeration-status-code

in-inventory,
in-work-actual-progress,

EXISTING, NOT-
EXISTING, IN-ORDER,

* Enumeration items for this element have not been fully defined.

124



CRANE-ASSIGNED _| Number

[1], system use

CRANE-USED Number [1], system use
MAINTAINED- Number [1], system use
RESOURCE-KEYS

SETUP- Number [1]. system use

RESOURCES-KEYS

CRANE-SCHEDULE | Number

[1], system use

crane-identifier Number (1)
crane-number Text (1)
Table crane-keys
Fields Data Type Description
INDEX Number [1], system use
CRANE-REQUIRED _| Number [1], system use
CRANE-KEY Number [1..*], crane-key.CRANE-KEYS
Table crane-required
Fields Data Type Description
INDEX Number [1], system use
CRANES- Number [1], system use
REQUIRED
minimum-cranes Number {1
/Integer
maximum-cranes Number ag}
/Integer
CRANE-KEYS Number [1], crane-keys.CRANE-REQUIRED
SETUP- Number [0..1], setup-configurations.CRANE-
CONFIGURATIONS REQUIRED

Table crane-schedule

Fields Data Type Description

INDEX Number [1], system use

CRANES-SECTION __| Number [1], system use

CRANE-KEY Number [1], crane-key.CRANE-SCHEDULE
WORK- Number [1], work-assignemnts.CRANE-SCHEDULE
ASSIGNMENTS

Table crane-specifications

Fields Data Type Description

INDEX Number [1]. system use

CRANE Number [1], system use

manufacturer Text [0..1]

model-number Text 1]

serial-number Text [0..1]

horsepower Number [0..1]

26



NISTIR 7077

A Database Design for the Machine
Shop Information Model

Y. Tina Lee
Yan Luo

Manufacturing Systems Integration Division
Manufacturing Engineering Laboratory
National Institute of Standards and Technology
Gaithersburg, MD 20899-8260

August 2004

¢ gent OF O44,

Ep,

wn * Oy
o*

‘2,
&
*
x
€
ve
s

STarEs oF

U.S. DEPARTMENT OF COMMERCE

Donald L. Evans, Secretary

TECHNOLOGY ADMINISTRATION

Phillip J. Bond, Under Secretary of Commerce for Technology
NATIONAL INSTITUTE OF STANDARDS AND TECHNOLOGY
Arden L. Bement, Jr., Director


/Double

percent-completion Number [0..1]
/Double
COST-SUMMARY Number [0..1], cost-summary.WORK-ACTUAL-
PROGRESS
PARTS- Number [0..1], parts-completed. WORK-ACTUAL-
COMPLETED PROGRESS
Table work-assignment
Fields Data Type Description
INDEX Number [1], system use
WORK- Number [1]. system use
ASSIGNMENTS
WORK-KEYS Number 0..1], work-keys. WORK-ASSIGNEMNT
SETUP- Number [0..1], setup-definition-keys.WORK-
DEFICITION-KEYS ASSIGNEMNT
EVENTS Number [0..1], events. WORK-ASSIGNEMNT
Table work-assignments
Fields Data Type Description
INDEX Number [1], system use
MACHINE-STATUS _| Number [1], system use
MACHINE-GROUP __| Number [1], system use
STATION-STATUS __| Number [1], system use
STATION-GROUP Number [1], system use
CRANE-STATUS Number [1], system use
EMPLOYEE- Number [1], system use
STATUS
TOOL-INSTANCE Number [1], system use
TOOLSET- Number [1], system use
INSTANCE
FIXTURE- Number [1], system use
INSTANCE
FIXTURESET- Number [1], system use
INSTANCE
PART-INSTANCE Number {1], system use
MATERIAL- Number [1], system use
INSTANCE
STATION- Number [1], system use
SCHEDULE
MACHINE- Number [1], system use
SCHEDULE
CRANE-SCHEDULE | Number [1]. system use
EMPLOYEE- Number [1], system use
SCHEDULE

120


INDEX Number [1], system use
COMPONENT- Number [1], system use
PART-MULTI-KEY

part-inventory-item- Number (]

identifier

part-inventory-item- Text (y

number

Table part-key .
Fields Data Type Description
INDEX Number [1], system use
PART-QUANTITY Number [1], system use
MACHINE- Number [1], system use
SPECIFICATIONS

TOOL-DEFINITION _| Number [1], system use
FIXTURE- Number [1], system use
DEFINITION

PART-OBJECT Number [1], system use
BILL-OF- Number [1], system use
MATERIALS

PART-INVENTORY- | Number [1], system use
ITEM

COMPONENT- Number [1], system use
PART-MULTI-KEY

MATERIALS- Number [1], system use
INVENTORY-ITEM

PROCURED-PART Number [1], system use
BATCH-SIZE Number [1], system use
part-identifier Number f)
part-number Text U1]

Table part-object

Fields Data Type Description
INDEX Number [1], system use
PART-OBJECTS Number [1], system use
type Enumeration _| [0..1]

identifier Number 1]

number Text 0)

name Text [0..1]
PART-KEY Number [1], part-key.PART-OBJECT
FROM-REFERENCE- | Number [0..1], from-reference-frame.PART-OBJECT
FRAME

behavior Enumeration _| [0..1]
REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
OBJECT- Number [1..*], object-boundary.PART-OBJECT

69



ORDER- Number [1]. system use
DEFINITION
JOB-DEFINITION Number [1], system use
TASK-DEFINITION __| Number [1], system use
| MAINTENANCE- Number [1], system use

ORDER-
DEFINITION
PRECEDENT- Number [1..*], precedent-constraint. PRECEDENT-
CONSTRAINT CONSTRAINTS
Table precedent-plan-step-key
Fields Data Type Description
INDEX Number [1], system use
STEP-PRECEDENT- | Number [1], system use
CONSTRAINT
precedent-plan-step- Number {i
identifier
precedent-plan-step- Text 1]
number
Table precedent-work-item
Fields Data Type Description
INDEX Number [1], system use
PRECEDENT- Number [1], system use
CONSTRAINT
WORK-KEYS Number [1], work-keys. PRECEDENT-WORK-ITEM
Table probability-distribution
Fields Data Type Description
INDEX Number [1], system use
DURATION- Number [1]. system use
DISTRIBUTIONS
PROBABILTY- Number [1], system use
DISTRIBUTION-SET
type Enumeration | [0..1]
identifier Number eg}
number Text eal
name Text [0..1]
description Text [0..1]
valuel Number {1)

/Double

(Fixed)
value2 Number fy]

/Double

(Fixed)

80


eds. S. Chick, P.J. Sanchez, D. Ferrin, and D. J. Morrice, 1296-1304. Piscataway, NJ.
Institute of Electrical and Electronics Engineers.

[8] http://www.omg.org/uml/ [accessed December 11, 2003].
[9] http://www.oracle.com/ip/deploy/database/oracle9i/ [accessed December 11, 2003].

[10] http://searchdatabase.techtarget.com/sDefinition/0,,sid13_gci213877,00.html
[accessed December 11, 2003].

[11] http://searchvb.techtarget.com/sDefinition/0,,sid8_gci214133,00.html [accessed
December 11, 2003].

[12] http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/winui/WinUI/WindowsUserInterface/DataExchange/DynamicDataExchange/AboutDy
namicDataExchange.asp [accessed December 11, 2003].

[13] http:/Awww.microsoft.com/sql/ [accessed December 11, 2003].

16


stations
0.4

_ crane

foo.

0..*

4 lefinition
] toolset |

Se
i

“definition

INDEX
SHOP-DATA

type

identifier

number

name

description
REFERENCE-KEYS
REVISIONS

TOOL-CATALOG

FIXTURE-CATALOG
MACHINES

STATIONS

CRANES

rerrar

EMPLOYEES

41 ‘revision 4

reference. creators [- ~| "creator
egy

0..1

Figure C-4: resources and related tables

129



cl tomers

keys”

- type
INDEX identifier
SHOP-DATA number
type name
identifier description
number REFERENCE-KEYS
name REVISIONS
description CUSTOMERS
REFERENCE-KEYS [7 SUPPLIERS
REVISIONS 7] BILL-OF-MATERIALS-KEY
PART 1 PROCESS-PLAN-MULTI-KEYS
PART-SPECIFICATIONS
GROUP-TECHNOLOGY-CODE
[ [ ce
0.1 0.4 O41 0.1 ye
| reference — revisions revision [-

reference |

ey

creators | creator
7 18

0..1

Figure C-10: parts and related tables

135


WORK-ACTUAL- Number [0..1], work-actual-progress.PICK-ORDER-

PROGRESS ITEM-STATUS

Table pick-order-items

Fields Data Type Description

INDEX Number [1], system use

PICK-ORDER- Number [1], system use

DEFINITION

PICK-ORDER-ITEM _| Number [1..*], pick-order-item.PICK-ORDER-ITEMS

Table pick-order-key

Fields Data Type Description

INDEX Number [1], system use

WORK-KEYS Number [1], system use

PICK-ORDER- Number [1], system use

SCHEDULE

pick-order-identifier Number 0]

pick-order-number Text 1]

Table pick-order-schedule

Fields Data Type Description

INDEX Number [1], system use

PICK-ORDERS- Number [1], system use

SECTION

PICK-ORDER-KEY | Number [1], pick-order.key.PICK-ORDER-
SCHEDULE

RESOURCES- Number [1], resources-assigned.PICK-ORDER-

ASSIGNED SCHEDULE

Table pick-order-status

Fields Data Type Description

INDEX Number [1], system use

PICK-ORDER Number [1]. system use

WORK- Number [0..1], work-scheduled-progress.PICK-

SCHEDULED- ORDER-STATUS

PROGRESS

WORK-ACTUAL- Number [0..1], work-actual-progress.PICK-ORDER-

PROGRESS STATUS

Table pick-orders

Fields Data Type Description

INDEX Number [1], system use

WORK Number [1], system use

type Enumeration _| [0..1]

identifier Number ing}

75



Fields Data Type Description
INDEX Number [1], system use
ADDRESS Number [1], system use
type Enumeration _| [0..1]
identifier Number U1)
email-address Text ing}
Table employee
Fields Data Type Description
INDEX Number [1], system use
EMPLOYEES Number [1], system use
type Enumeration | [0..1]
identifier Number (
number Text i]
name Text [0..1]
employment-status Enumeration | [0..1]
ADDRESSES Number [0..1], addresses. EMPLOYEE
POSITION-MULTI- | Number [0..1], position-multi-keys. EMPLOYEE
KEYS
SKILL-MULTI-KEYS | Number (0..1], skill-multi-keys. EMPLOYEE
EMPLOYEE- Number [0..1], employee-status. EMPLOYEE
STATUS
Table employee-assigned
Fields Data Type Description
INDEX Number [1], system use
EMPLOYEES- Number [1], system use
ASSIGNED
number-of-employees | Number ing}
Integer
EMPLOYEE-KEY Number [1], employee-key.EMPLO YEE-ASSIGNED
EVENTS Number [0..1], events. EMPLO YEE-ASSIGNED

Table employee-availabil

ity

Fields Data Type Description
INDEX Number [1], system use
EMPLOYEE- Number [1]. system use
STATUS
EFFECTIVE-SHIFTS | Number [1], effective-shifts. EMPLOY EE-
AVAILABILITY
AVAILABILITY- Number [0..1], availability-exceptions. EMPLOYEE-
EXCEPTIONS AVAILABILITY
Table employee-key
| Fields | Data Type | Description

33



number Text (J
name Text [0..1]
description Text [0..1]
REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions. SOURCE
STATION Number [1..*], station. STATIONS
STATION-GROUP Number [0..*], station.group.STATIONS
Table stations-assigned
Fields Data Type Description
INDEX Number [1], system use
RESOURCES- Number [1], system use
ASSIGNED
number-of-stations Number {1]
‘Integer
STATION- Number [1..*], station-assigned. STATIONS-
ASSIGNED ASSIGNED
Table stations-required
Fields Data Type Description
INDEX Number [1], system use
RESOURCES- Number [1], system use
REQUIRED
STATION- Number [1..*], station-configuration.STATIONS-
CONFIGURATION REQUIRED
Table stations-section
Fields Data Type Description
INDEX Number [1], system use
RESOURCES- Number [1], system use
SECTION
STATION- Number [1..*], station-schedule.STATIONS-SECTION
SCHEDULE
Table stations-used
Fields Data Type Description
INDEX Number [1], system use
RESOURCES-USED _| Number [1], system use
number-of-stations Number fy
/Integer
STATION-USED Number [L..*], station-used.STATIONS-USED
Table status-code
Fields Data Type Description
INDEX Number [1], system use

104



ITEM-STATUS.

PROCUREMENT- Number [1], system use

STATUS

EVENT Number [1..*], event.EVENTS

Table fixture-catalog

Fields Data Type Description

INDEX Number [1], system use

RESOURCES Number [1], system use

type Enumeration _| [0..1]

identifier Number ion

number Text {1]

name Text [0..1]

description Text (0..1]

REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions SOURCE
FIXTURE- Number [1..*], fixture-definition. FIXTURE-
DEFINITION CATALOG

FIXTURESET- Number [0..*], fixtureset-definition.FIXTURE-
DEFINITION CATALOG

Table fixture-definition

Fields Data Type Description

INDEX Number [1], system use

FIXTURE- Number [1], system use

CATALOG

type Enumeration _| [0..1]

identifier Number 0)

number Text gl

name Text [0.1] |

description Text [0..1]

REFERENCE-KEYS _| Text [0..1], reference-keys. SOURCE
REVISIONS Text [0..1], revisions.SOURCE
PART-KEY Number [0..1], part-key.FIXTURE-DEFINITION
Table fixture-definition-key

Fields Data Type Description

INDEX Number [1], system use

SETUP- Number [1], system use
COMPONENTS

FIXTURESET- Number [1], system use
MEMBER-KEYS

FIXTURE- Number [1], system use
INVENTORY-ITEM

fixture-definition- Number (1)

40


INDEX

Number

[1], system use

WORK-SECTION Number [1], system use

ORDER-SCHEDULE | Number [1..*], order-schedule.ORDER S-SECTION
Table organization

Fields Data Type Description

INDEX Number [1], system use

ORGANIZATIONS Number [1], system use

type Enumeration _| [0..1]

identifier Number 1]

number Text 1]

name Text [0..1]

description Text [0..1]

REFERENCE-KEYS _| Text [0..1], reference-keys. SOURCE
REVISIONS Text [0..1], revisions. SOURCE

priority-rating Enumeration _| [0..1]

CONTACTS Number [0..1], contacts; ORGANIZATION
ADDRESSES Number [0..1], addresses. ORGANIZATION
DEPARTMENTS Number [1..*], departments. ORGANIZATION
POSITION- Number [0..1], position-descriptions. ORGANIZATION
DESCRIPTIONS

Table organization-key

Fields Data Type Description

INDEX Number [1], system use

CREATOR Number [1], system use

ORGANIZATIONAL- | Number [1]. system use

CONTACT

POSITION-MULTI- | Number [1], system use

KEY

CHILD- Number [1], system use

DEPARTMENT-

MULTI-KEY

organization-identifier_| Number [1]

organization-number__| Text ion

Table organizational-contact

Fields Data Type Description

INDEX Number [1], system use

CUSTOMERS Number [1], system use

SUPPLIERS Number [1], system use

ORGANIZATION- Number [1], organization-key.ORGANIZATIONAL-
KEY CONTACT

DEPARTMENT-KEY | Number [0..1], department-key. ORGANIZATIONAL-

CONTACT

65



REQUIRED

EMPLOYEE-
REQUIRED

Number

[1..*], employee-required EMPLOYEES-
REQUIRED

Table employees-section

Fields Data Type Description
INDEX Number 1], system use
RESOURCES- Number [1]. system use
SECTION
EMPLOYEE- Number [1..*], employee-schedule.EMPLOYEES-
SCHEDULE SECTION
Table employees-used
Fields Data Type Description
INDEX Number [1], system use
RESOURCES-USED _| Number 1], system use
number-of-employees | Number {]
/Integer
EMPLOYEE-USED Number [1..*], employee-used.EMPLOYEES-USED

Table enumeration-behavior

Fields Data Type Description
in-layout Text [L..*]

Table enumeration-controller-type

Fields Data Type Description
in-machine-program Text 1..*]
in-machines Text 1..*]

Table enumeration-day-name

Fields Data Type Description
in-calendars Text [1..*]

Table enumeration-day-number

Fields Data Type Description
in-day-number Number [1..*]
in-day-name Text [1..*]

Table enumeration-efficiency-rating

Fields Data Type Description
in-machines Text [1..*]

Table enumeration-employment-status

Fields Data Type Description
in-employees Text [L..*]

36



Fields Data Type Description

INDEX Number [1], system use

PARTS-ASSIGNED __| Number [1], system use

PARTS-USED Number [1], system use

COMPONENT- Number [1], system use

PART-MULTI-KEY

part-instance-identifier_| Number [1]

part-instance-number__| Text an]

Table part-instances

Fields Data Type Description

INDEX Number [1], system use
/Integer

PART-INVENTORY- | Number
ITEM

[1], system use

PART-INSTANCE Number

[1..*], part-instance.PART-INSTANCES

Table part-inventory

Fields Data Type Description
INDEX Number [1], system use
INVENTORY Number [1], system use

PART-INVENTORY- | Number
ITEM

[1..*], part-inventory-item.PART-
INVENTORY

Table part-inventory-item

Fields Data Type Description

INDEX Number [1], system use

PART-INVENTORY _| Number [1], system use

type Enumeration | [0..1]

identifier Number 01]

number Text i]

name Text [0..1]

description Text [0..1]

REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text {0..1], revisions SOURCE
PART-KEY Number [1], part-key.PART-INVENTORY-ITEM
STOCK-LEVEL- Number [1], stock-level-quantities. PART-
QUANTITIES INVENTORY-ITEM
PROCUREMENT- Number [0..1], procurement-multi-keys. PART-
MULTI-KEYS INVENTORY-ITEM

PART-INSTANCES _ | Number

{0..1], part-instances.PART-INVENTORY-
ITEM

Table part-inventory-item-key

| Fields | Data Type

| Description

68



WORK- Number [1], system use
SCHEDULED-
PROGRESS
WORK-ACTUAL- Number [1], system use
PROGRESS
TOOL-INSTANCE Number [1], system use
TOOLSET- Number [1], system use
INSTANCE
FIXTURE- Number [1], system use
INSTANCE
FIXTURESET- Number [1], system use
INSTANCE
PART-INSTANCE Number [1], system use
MATERIAL- Number [1], system use
INSTANCE
PROCUREMENT- Number [1], system use
ITEM-STATUS
PROCUREMENT- Number [1], system use
STATUS
type Enumeration | [0..1]
identifier Number (1)
number Text fi]
name Text [0..1]
value Enumeration _| [0..1]
default Enumeration | [0..1]
Table step-precedent-constraint
Fields Data Type Description
INDEX Number [1], system use
STEP-PRECEDENT- | Number [1], system use
CONSTRAINTS
type Enumeration | [0..1]
identifier Number ng}
number Text {1
name Text [0..1]
PLAN-STEP-KEY Number [1], plan-step-key.STEP-PRECEDENT-
CONSTRAINTS

PRECEDENT-PLAN- | Number [1], precedent-plan-step-key.STEP-
STEP-KEY PRECEDENT-CONSTRAINTS
logical-relationship Enumeration _| [0..1]
lag-percentage Number [0..1]

/Double
lag-duration Number [0..1]

/Double

(Fixed)

105



addresses
0:4

a
a

pg | eevartment

eo
4, | INDEX
ORGANIZATIONS
type
SHOP-DATA identifier
type number
identifier name
number description
name priority-rating 1
description CONTACTS =
REFERENCE-KEYS i] ADDRESSES
REVISIONS An DEPARTMENTS 4
ORGANIZATION POSITION-DESCRIPTIONS 7
1 REFERENCE-KEYS
REVISIONS 1
1
0.4} | 0.4 4| [0.1
| refere | evisions |
aig 7 ee 4 ir
ee i
as |__| position- |
7 ee 1.. _ description :

Figure C-2: organizations and related tables

127


type Enumeration _| [0..1] ‘|
identifier Number 1]
number Text (1)
name Text [0.1]
description Text [0..1]
REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions. SOURCE
JOB-DEFINITION Number [0..1], job-definition. JOB
JOB-STATUS Number [0..1], job-status JOB
Table job-definition
Fields Data Type Description
INDEX Number [1], system use
JOB Number [1], system use
CUSTOMERS Number [0..1], customers.JOB-DEFINITION
priority-rating Enumeration _| [0..1]
DUE-DATES Number [1], due-dates.JOB-DEFINITION
PART-QUANTITIES _| Number [1], part-quantities.JOB-DEFINITION
PROCESS-PLAN- Number [0..1], process-plan-multi-key.JOB-
MULTI-KEY DEFINITION
repetition-count Number [0..1]

/Integer
PARAMETERS Number [0..1], parameters.JOB-DEFINITION
RESOURCES- Number [0..1], resources-required. JOB-DEFINITION
REQUIRED
CHILD-WORK- Number [0..1], child-work-items.JOB-DEFINITION
ITEMS
PRECEDENT- Number [0..1], precedent-constraints. JOB-
CONSTRAINTS DEFINITION
COST-SUMMARY Number [0..1], cost-summary.JOB-DEFINITION
Table job-key
Fields Data Type Description
INDEX Number [1], system use
WORK-KEYS Number [1], system use
JOB-SCHEDULE Number [1], system use
job-identifier Number {4
job-number Text (
Table job-schedule
Fields Data Type Description
INDEX Number [1], system use
JOBS-SECTION Number [1], system use
JOB-KEY Number [1], jobi.key.JOB-SCHEDULE
RESOURCES- Number [1], resources-assigned.JOB-SCHEDULE

46


INDEX Number

[1], system use

SOURCE Text

1], system use

REFERENCE-KEY Number

[1..*], refenece-key.REFERENCE-KEYS

Table references

Fields Data Type Description

INDEX Number [1], system use

SHOP-DATA Number 1], system use

type Enumeration _| [0..1]

identifier Number U1]

number Text 1)

name Text [0..1]

description Text [0..1]

REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions SOURCE
REFERENCE Number [1..*], reference. REFERENCES
Table reliability-statistics

Fields Data Type Description

INDEX Number | [1], system use

MACHINE Number [1], system use

CRANE Number [1], system use

MEAN-TIME- Number [0..1], mean-time-between-
BETWEEN- failures. RELIABILITY-STATICTICS
FAILURES

MEAN-TIME-TO- Number
REPAIR

[0..1], mean-time-to-repair.RELIABILITY-
STATICTICS

Table repository

Fields Data Type Description
INDEX Number [1], system use
PAPER-DOCUMENT | Number [1], system use
reference-number Text esl
storage-location Text [0..1]

Table resource-key

Fields Data Type Description
INDEX Number [1], system use
RESOURCE-OBJECT | Number [1]. system use
resource-identifier Number inal
resource-number Text (1)

Table resource-object

Fields Data Type Description
INDEX Number [1], system use

88



implement and maintain than mainframe DBMSs. Access is extensible. It has a capability
to import and export data with various text file formats and database systems using the
data access interface, such as Data Access Objects (DAO) [10], Open Database
Connectivity (ODBC) [11], and Dynamic Data Exchange (DDE) [12].

5. DATABASE DEVELOPMENT

A database model is designed to map onto the machine shop information model. The
database contains a set of relational tables presented in a tree shape structure. Tables
comprise the fundamental blocks of a database. A table is a grouping of selected data
organized into fields (columns) and records (rows) on a datasheet. A field identifies a
data type for a set of value in a table while a record stores a set of values defined by
fields. This section describes the guidelines that are used to develop the database. It also
provides samples for a mapping from an XML element to a relational table, a design view
of a table, and the relationships between tables.

5.1 Guidelines

The following guidelines are used when developing the database structure:

e Elements in the information model are represented as “tables” in the database
model.

¢ Attributes and child-elements of an element are represented as “fields” in the
corresponding table.

e Parent-elements of an element are represented as “fields” in the corresponding
table. These fields are required for system maintenance.

e A cardinality relationship is specified with the definition of each field if a child-
element exists.
INDEX is a required field for each table and is used by the system.
A table name is presented in the lower case.
A field that serves as a referenced table, or a referencing table, or a system used
item (such as INDEX or parent-element) is presented in the upper case. All other
fields are presented in the lower case.

e Names used for elements or attributes in the information model are kept the same
in the database.

e Enumeration tables have names prefixed with “enumeration-” or “type-”.

5.2 Relational tables and XML elements

Elements in the information model are used to describe fundamental features or common
features of machine shop data. They are represented as tables in the database model.
Figure 3 is a sample presentation view about the stock-level-quantities element. Stock-
level-quantities specifies the quantities of tools, fixtures, materials, or parts for various
purposes and is represented by the following data elements: allocated-quantity, back-
order-quantity, on-hand-quantity, on-order-quantity, required-quantity, safety-stock-
quantity, and work-in-progress-quantity. In Figure 3, TOOL-INVENTORY-ITEM,
TOOLSET-INVENTORY-ITEM, FIXTURE-INVENTORY-ITEM, FIXTURESET-


1 INTRODUCTION...

2 STANDARD INTERFACES

3. OVERVIW OF THE INFORMATION MODEL:

4 OBJECTIVES OF THE DATABASE IMPLEMENTATION

5 DATABASE DEVELOPMEN’
5.1 Guidelines
5.2 Relational tables and XML elements
5.3 Data structure in a design view
54 Relationships between tables
5.5 Summary of the development

6 SAMPLE QUERIES.
6.1 Basic query ...
6.2 Complex query ....

7 CONCLUSIONS AND FUTURE WORK.....

REFERENCES........

APPENDIX A — Data structure:
APPENDIX B — Enumerations.
APPENDIX C — Relationships between tables
APPENDIX D — Sample data tables ....



Fields Data Type Description

INDEX Number [1], system use

SETUP- Number [1], system use

COMPONENTS

FIXTURESET- Number [1], system use

MEMBER-KEYS

FIXTURESET- Number [1], system use

INVENTORY-ITEM

fixtureset-definition- Number (1)

identifier

fixtureset-definition- | Text (y

number

Table fixtureset-instance

Fields Data Type Description

INDEX Number [1], system use

FIXTURESET- Number [1], system use

INSTANCES

type Enumeration _| [0..1]

identifier Number [1]

number Text fi)

name Text 0.1]

current-location Text [0..1]

STATUS-CODE Number [0..1], status-code. FIXTURESET-INSTANCE
FIXTURE- Number [1], fixture-instance-keys. FIXTURESET-
INSTANCE-KEYS INSTANCE

WORK- Number [0..1], work-assignments.F[X TURESET-
ASSIGNMENTS INSTANCE

Table fixtureset-instances

Fields Data Type Description
INDEX Number [1], system use
FIXTURESET- Number [1], system use
INVENTORY-ITEM

FIXTURESET- Number [1..*], fixtureset-instance.FIXTURESET-
INSTANCE INSTANCES
Table fixtureset-inventory-item

Fields Data Type Description
INDEX Number [1], system use
FIXTURE- Number [1]. system use
INVENTORY

type Enumeration | [0..1]
identifier Number U1]

43



WORK-KEYS Number [0..1], work-keys.PICK-ORDER-
DEFINITION

priority-rating Enumeration _| [0..1]

DUE-DATES Number [1], due-dates.PICK-ORDER-DEFINITION

RESOURCES- Number [0..1], resources-required.PICK-ORDER-

REQUIRED DEFINITION

COST-SUMMARY Number [0..1], cost-summary.PICK-ORDER-
DEFINITION

PICK-ORDER- Number [0..1], pick-order-items.PICK-ORDER-

ITEMS DEFINITION

Table pick-order-item

Fields Data Type Description

INDEX Number [1], system use

PICK-ORDER- Number [1], system use

ITEMS

type Enumeration _| [0..1]

identifier Number (1)

number Text (1)

name Text [0..1]

description Text [0..1]

PICK-ORDER-ITEM- | Number [0..1], pick-order-item-definition.PICK-

DEFINITION ORDER-ITEM

PICK-ORDER-ITEM- | Number [0..1], pick-order-item-status.PICK-ORDER-

STATUS

ITEM

Table pick-order-item-definition

Fields Data Type Description

INDEX Number [1], system use

PICK-ORDER-ITEM _| Number [1], system use

priority-rating Enumeration _| [0..1]

DUE-DATES Number [0..1], due-dates.PICK-ORDER-ITEM-
DEFINITION

PART-QUANTITIES | Number [1], part-quantities.PICK-ORDER-ITEM-
DEFINITION

COST-SUMMARY Number [0..1], cost-summary.PICK-ORDER-

DEFINITION

Table pick-order-item-status

Fields Data Type Description

INDEX Number [1], system use

PICK-ORDER-ITEM _| Number [1], system use

WORK- Number [0..1], work-scheduled-progress.PICK-
SCHEDULED- ORDER-ITEM-STATUS

PROGRESS

74


PROCESS-PLAN- Number [1..*], process-plan-multi-key. PROCESS-
MULTI-KEY PLAN-MULTI-.KEYS
Table process-plans
Fields Data Type Description
INDEX Number [1], system use
SHOP-DATA Number [1], system use
type Enumeration _| [0..1]
identifier Number 1]
number Text 1]
name Text [0..1]
description Text [0..1]
REFERENCE-KEYS _| Text 0..1], reference-keys. SOURCE
REVISIONS Text [0..1], revisions. SOURCE
PROCESS-PLAN Number [1..*], process-plan.PROCESS-PLANS
Table procured-part
Fields Data Type Description
INDEX Number [1], system use
PROCUREMENT- Number [1], system use
ITEM-DEFINITION
PART-KEY Number | [1], part-key.PROCURED-PART
quantity Number [1]

Integer
unit-cost Number [0.1]

/Double

(Fixed)
total-cost Number [0..1]

/Double

(Fixed)
Table procurement
Fields Data Type Description
INDEX Number 1], system use
PROCUREMENTS Number [1], system use
type Enumeration _| [0..1]
identifier Number esl
number Text 0]
name Text [0..1]
description Text, 0..1]
REFERENCE-KEYS _| Text [0..1], reference-keys. SOURCE
REVISIONS Text [0..1], revisions. SOURCE
PROCUREMENT- Number [0..1], procurement-
DEFINITION definition. PROCUREMENT
PROCUREMENT- Number [0..1], procurement-status. PROCUREMENT

83



NISTIR 7077

A Database Design for the Machine
Shop Information Model

Y. Tina Lee
Yan Luo

Manufacturing Systems Integration Division
Manufacturing Engineering Laboratory
National Institute of Standards and Technology
Gaithersburg, MD 20899-8260

NtsrT

National Institute of Standards and Technology
Technology Administration, U.S. Department of Commerce



[TASK-SCHEDULE | Number

il [1..*], task-schedule. TASKS-SECTION

Table time-sheet

Fields Data Type Description
INDEX Number [1], system use
TIME-SHEETS Number [1], system use
type Enumeration _| [0..1]
identifier Number 0]
number Text (1)
name Text [0..1]
EMPLOYEE-KEY Number [1], employee-key. TIME-SHEET
pay-period Number {i

/Double

(Fixed)
overtime-eligibility Enumeration _| [0..1]
hours-regular-time Number [0..1]

/Double

(Fixed)
hours-overtime Number [0..1]

/Double

(Fixed)
hours-vacation Number [0..1]

/Double

(Fixed)
hours-sick-leave Number (0..1]

/Double

(Fixed)
hours-holiday Number [0..1]

/Double

(Fixed)
hours-unpaid Number [0..1]

/Double

(Fixed)
total-hours Number [0..1]

/Double

(Fixed)
total-days-absent Number [0..1]

/Double

(Fixed)
Table time-sheets
Fields Data Type Description
INDEX Number [1], system use
SHOP-DATA Number [1], system use
type Enumeration | [0..1]

109


0,,4] INDEX
OPERATION-DEFINITION
STATION-KEY

STATION-GROUP-KEY

definitions

INDEX

SHOP-DATA

type

identifier

number

name

description
REFERENCE-KEYS
REVISIONS
OPERATION-DEFINITION

+ | INDEX

7_| OPERATION-DEFINITIONS
type

identifier

number

name
operation-type
operation-subtype a
description

STATION-KEYS

0..1

rence

reference-key_

0..1 1+
| revisions
; 15 c
creators. creator
0.1 1

Figure C-7: operation-definitions and related tables

132



A Database Design for the Machine Shop Information Model

Y. Tina Lee
Yan Luo
Manufacturing Systems Integration Division
National Institute of Standards and Technology
Gaithersburg, MD 20899-8260

Abstract: This report describes a database model currently under development at the
Manufacturing System Integration Division (MSID) of the National Institute of Standards
and Technology (NIST). The database model contains a set of tables that are mapped
onto the machine shop information model developed by MSID. The information model
provides neutral data interfaces for integrating machine shop software applications with
simulation. The interface data include organizations, calendars, work, resources,
schedules, parts, process plans, and layout within a machine shop environment. The
database model is implemented using Microsoft Access; it is used to support the
integration of manufacturing applications and simulations for machine shops. The
database’s structures, relationships, and usages are presented in this report.

Keywords: database, data structures, information model, machine shop operations,
simulation


{ identifier Number 1]
number Text eal
name Text | [0..1]
description Text [0..1]
REFERENCE-KEYS _| Text [0..1], reference-keys. SOURCE
REVISIONS Text [0..1], revisions. SOURCE
TIME-SHEET Number [1..*], time-sheet. TIME-SHEETS
Table tool-catalog
Fields Data Type Description
INDEX Number [1], system use
RESOURCES Number [1], system use
type Enumeration _| [0..1]
identifier Number eg}
number Text 1]
name Text [0..1]
description Text [0..1]
REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions.SOURCE
TOOL-DEFINITION _| Number [1..*], tool-definition. TOOL-CATALOG
TOOLSET- Number [0..*], toolset-definition. TOOL-CATALOG
DEFINITION
Table tool-definition
Fields Data Type Description
INDEX Number [1], system use
TOOL-CATALOG Number [1], system use
type Enumeration _| [0..1]
identifier Number 0]
number Text [1]
name Text [0..1]
description Text [0..1]
REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions. SOURCE
PART-KEY Number [0..1], part-key. TOOL-DEFINITION
Table tool-definition-ke
Fields Data Type Description
INDEX Number [1], system use
SETUP- Number [1], system use
COMPONENTS
TOOL-ORDER- Number [1], system use
DEFINITION
TOOLSET- Number [1], system use
MEMBER-KEYS

110


uniform-resource- Text [0..1]
indicator
Table digital-files
Fields Data Type Description
INDEX Number [1], system use
REFERENCE Number [1], system use
DIGITAL-FILE- Number [1..*], digital-file-properties. DIGITAL-FILES
PROPERTIES
Table dimension
Fields Data Type Description
INDEX Number [1], system use
DIMENSIONS Number [1], system use
type Enumeration _| [0..1]
value Number [1]

/Double
Table dimensions
Fields Data Type Description
INDEX Number [1], system use
MATERIAL- Number [1]. system use
INSTANCE
DIMENSION Number [1..*], dimension. DIMENSIONS
Table due-dates
Fields Data Type Description
INDEX Number [1], system use
ORDER- Number [1], system use
DEFINITION
ORDER-ITEM- Number [1], system use
DEFINITION
JOB-DEFINITION Number [1], system use
TASK-DEFINITION __| Number [1], system use
MAINTENANCE- Number [1], system use
ORDER-
DEFINITION
PICK-ORDER- Number [1], system use
DEFINITION
PICK-ORDER-ITEM- | Number [1], system use
DEFINITION
TOOL-ORDER- Number [1], system use
DEFINITION
PROCUREMENT- Number [1], system use

DEFINITION



JOB-SCHEDULE

Number

[1], system use

TASK-SCHEDULE Number [1], system use

MAINTENANCE- Number [1], system use

ORDER-SCHEDULE

PICK-ORDER- Number [1], system use

SCHEDULE

TOOL-ORDER- Number [1], system use

SCHEDULE

STATIONS- Number [0..1], stations-assigned. RESOURCES-

ASSIGNED ASSIGNED

MACHINES- Number [0..1], machines-assigned.RESOURCES-

ASSIGNED ASSIGNED

EMPLOYEES- Number [0..*], employees-assigned.RESOURCES-

ASSIGNED ASSIGNED

CRANES-ASSIGNED | Number [0..1], cranes-assigned.RESOURCES-
ASSIGNED

PARTS-ASSIGNED — | Number [0..*], parts-assigned. RESOURCES-

ASSIGNED

Table resources-required

Fields Data Type Description

INDEX Number [1], system use

ORDER-ITEM- Number [1], system use

DEFINITION

ORDER- Number [1], system use

DEFINITION

JOB-DEFINITION Number [1], system use

TASK-DEFINITION __| Number [1], system use

MAINTENANCE- Number [1], system use

ORDER-

DEFINITION

PICK-ORDER- Number [1], system use

DEFINITION

TOOL-ORDER- Number [1], system use

DEFINITION

PLAN-DEFINITION _ | Number [1], system use

PLAN-STEP Number [1], system use

STATIONS- Number [0..1], stations-required. RESOURCES-
REQUIRED REQUIRED

MACHINES- Number [0..1], machines-required.RESOURCES-
REQUIRED REQUIRED

EMPLOYEES- Number [0..1], employees-required.RESOURCES-
REQUIRED REQUIRED

CRANS-REQUIRED | Number [0..1], crans-required.RESOURCES-

REQUIRED

90


PROCUREMENT- Number [1], system use

MULTI-KEY

procurement-item- Number {i

identifier

procurement-item- text 1)

number

Table procurement-item-status

Fields Data Type Description

INDEX Number [1], system use

PROCUREMENT- Number [1], system use

ITEM

STATUS-CODE Number [1], status-code. PROCUREMENT-ITEM-
STATUS

EVENTS Number [0..1], events. PROCUREMENT-ITEM-
STATUS

Table procurement-items

Fields Data Type Description

INDEX Number [1], system use

PROCUREMENT- Number [1], system use

DEFINITION

PROCUREMENT- Number [1..*], procurement-item.PROCUREMENT-

ITEM ITEMS

Table procurement-key

Fields Data Type Description

INDEX Number [1], system use

PROCUREMENT- Number [1]. system use

MULTI-KEY

procurement-identifier_| Number 1]

procurement-number _| text 0)

Table procurement-multi-key

Fields Data Type Description

INDEX Number [1], system use

PROCUREMENT- Number [1], system use

MULTI-KEYS

WORK-KEYS Number [1], system use

PROCUREMENT- Number [0..1], procurement-key.PROCUREMENT-

KEY MULTI-KEY

PROCUREMENT- Number [0..1], procurement-item-

ITEM-KEY key.PROCUREMENT-MULTI-KEY

Table procurement-multi-keys

85



double or an integer), or an enumeration. A description presents additional information.
Appendix B lists enumeration elements that are used in the database model. Relationships
among major manufacturing shop data elements are presented in Appendix C. A set of
sample data tables is listed in Appendix D.

6. SAMPLE QUERIES
The Structured Query Language’s “SELECT”, “CREATE”, “INSERT”, “DELETE”,

“UPDATE,” etc. are useful to manipulate data from a database [13]. This section
demonstrates how to query a database for information.

6.1 Basic query

Basic query is used based on a single table. A sample basic query is introduced here. The
enumeration-units element has the following fields: angular-units, currency-units,
distance-units, length-units, mass-units, speed-units, time-duration-units, and volume-
units. Sample data for enumeration-units are presented in Table 2. Table 3 shows the
result from the sample query that uses the enumeration-units table to retrieve information
about unit items applicable to a distance.

The basic query statement is:

SELECT [enumeration-units].[distance-units] FROM [enumeration-units];

Table 2: Enumeration items for enumeration-units (Query Input)

itime-
langular- |currency |distance- iduration-
lunits units units llength-units |mass-units |speed-units |units lvolume-units
[DEGREE __|DOLLAR__|MILE [MILE [TON MILE/HOUR _|DAY_ [OUNCE
|KILOMETER/
IRADIAN _|EURO [FOOT [FOOT IPOUND [HOUR |HOUR (CUBICFOOT
[YEN INCH INCH [OUNCE MINUTE. LITER,
IKILOMETER_|KILOMETER |KILOGRAM SECOND __|GALLON
|METER. IMETER (GRAM. [MILLILITER
\CENTIMETER|CENTIMETER. {CUBICMETER_
|{CUBICINCH,
|CUBICCENTIMETER

Table 3: Enumeration items for distance-units (Query Output)
\distance-units

MILE

FOOT

INCH

KILOMETER

IMETER

CENTIMETER



MAINTENANCE- Number [1], system use

ORDER-

DEFINITION

MAINTENANCE- Number [1], system use

DEFINITION

MACHINE-KEY Number [0..*], machine-key. MAINTAINED-
RESOURCE-KEYS

CRANE-KEY Number [0..*], crane-key MAINTAINED-
RESOURCE-KEYS

STATION-KEY Number [0..*], station-key. MAINTAINED-

RESOURCE-KEYS

Table maintenance-definition

Fields Data Type Description
INDEX Number [1], system use
MAINTENANCE- Number [1], system use
DEFINITIONS
type Enumeration _| [0..1]
identifier Number {1
number Text (
name Text [0..1]
description Text (0..1]
periodicity Number [1], hh:mm:ss
/Double
(Fixed)
REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions. SOURCE
PLAN-DEFINITION — | Number [0..1], plan-definition MAINTENANCE-
DEFINITION
ESTIMATED- Number [0..1], estimated-durations MAINTENANCE-
DURATIONS DEFINITION
COST-SUMMARY Number [0..1], cost-summary.MAINTENANCE-
DEFINITION
MAINTAINED- Number [1], maintained-resource-

RESOURCE-KEYS

keys. MAINTENANCE-DEFINITION

Table maintenance-definition-key

Fields Data Type Description
INDEX Number [1], system use
MAINTENANCE- Number [1], system use
ORDER-

DEFINITION

maintenance- Number fi)
definition-identifier

maintenance- Text fy)

55



(Fixed)

DURATION- Number [0..1], duration-distributions.SETUP-
DISTRIBUTIONS DURATION
Table setup-resources-keys
Fields Data Type Description
INDEX Number [1], system use
SETUP-DEFINITION | Number [1], system use
MACHINE-KEY Number [0..*], machine-key. SETUP-R ESOURCES-
KEYS
STATION-KEY Number [0..*], station-key.SETUP-RESOURCES-
KEYS
CRANE-KEY Number [0..*], crane-key. SETUP-RES OURCES-KEYS
Table shift
Fields Data Type Description
INDEX Number [1], system use
SHIFTS Number [1], system use
type Enumeration _| [0..1]
identifier Number (1)
number Text 0)
name Text [0..1]
start-time Date/Time [1], hh:mm:ss
end-time Date/Time [0..1], hh:mm:ss
duration Number [0..1]
/Double
(Fixed)
DAYS-OF-WEEK Number [1], days-of-week.SHIFT
BREAKS Number [0..1], breaks.SHIFT
Table shift-key
Fields Data Type Description
INDEX Number [1], system use
CALENDAR-MULTI- | Number [1], system use
KEY
shift-identifier Number eg}
shift-number Text 0]
Table shift-schedule
Fields Data Type Description
INDEX Number [1], system use
CALENDAR Number [1], system use
type Enumeration | [0..1] _
identifier Number 1]
number -| Text [1]

97


number Text eal

name Text [0..1]

description Text [0..1]

REFERENCE-KEYS _| Text [0..1], reference-keys. SOURCE
REVISIONS Text [0..1], revisions. SOURCE
PICK-ORDER Number [1..*], pick-order.PICK-ORDERS

Table pick-orders-sectio

yn

Fields Data Type Description
INDEX Number [1], system use
WORK-SECTION Number [1], system use
PICK-ORDER- Number [1..*], pick-order-schedule.PICK-ORDERS-
SCHEDULE SECTION
Table plan-definition
Fields Data Type Description
INDEX Number [1], system use
MAINTENANCE- Number [1], system use
DEFINITION
ROUTING-SHEET Number [1], system use
OPERATION-SHEET | Number [1], system use
PARAMETERS Number [1], parameters. PLAN-DEFINITION
RESOURCES- Number [0..1], resources-required.PLAN-DEFINITION
REQUIRED
ESTIMATED- Number [0..1], estimated-durations.PLAN-
DURATIONS DEFINITION
PLAN-STEPS Number [0..1], plan-steps.PLAN-DEFINITION
STEP-PRECEDENT- | Number [0..1], step-precedent-constraints.PLAN-
CONSTRAINTS DEFINITION
Table plan-step
Fields Data Type Description
INDEX Number [1], system use
PLAN-STEPS Number [1], system use
type Enumeration _| [0..1]
identifier Number (1)
number Text (1)
name Text [0..1]
description Text [0..1]
repetition-count Number [0..1]
/Integer
OPERATION- Number [1], operation-definition-key.PLAN-STEP
DEFINITION-KEY
specific-instructions Text [0..1]
ESTIMATED- Number [0..1], estimated-durations.PLAN-STEP

76



Table enumeration-file-format

Fields Data Type Description
in-references Text [1..*]
Table enumeration-logical-relationship
Fields Data Type Description
in-child-work Text [L.*]
in-plan-definition Text [1..*]
in-precedent- Text [1..*]
constraints
Table enumeration-maintenance-state
Fields Data Type Description
in-cranes Text [1..*]
in-machines Text [1..*]
in-stations Text (1..*]
Table enumeration-material-type
Fields Data Type Description
in-materials-inventory | Text [1..*]
Table enumeration-operational-state
Fields Data Type Description
in-cranes Text [1..*]
in-machines Text [L..*]
in-stations Text [1..*]
Table enumeration-operation-subtype
Fields Data Type Description
in-operation- Text [1..*]
definitions
Table enumeration-operation-type
Fields Data Type Description
in-operation- Text [1..*]
definitions
Table enumeration-overtime-eligibility
Fields Data Type Description
in-employees Text [L..*]
in-time-sheets Text {1..*]
Table enumeration-priority-rating

| Fields | Data Type | Description



INDEX Number [1], system use
LOGICAL- Number [1], system use
STRUCTURE
a-key Text DJ
Table logical-structure
Fields Data Type Description
INDEX Number [1], system use
SETUP- Number [1], system use
COMPONENTS
logical-and Text [0..1]
a-key Text (0..1]
LOGICAL-OR Text [0..1], logical-or. LOGICAL-S TRUCTURE
SET-LOGICAL- Text [0..1], set-logical-clause. LOGICAL-
CLAUSE STRUCTURE
USE-LOGICAL- Text [0..*], use-logical-clause. LOGICAL-
CLAUSE STRUCTURE
A-WEIGHTED- Text [0..1], a-weighted-element. LOGICAL-
ELEMENT STRUCTURE
Table machine
Fields Data Type Description
INDEX Number [1], system use
MACHINES Number [1]. system use
type Enumeration | [0..1]
identifier Number (1)
number Text ti
name Text [0..1]
description Text [0..1]
hourly-rate Number [0..1]
/Double
(Fixed)
STATION-KEYS Number [0..1], station-keys.) MACHINE
RELIABILITY- Number [0..1], reliability-statistics MACHINE
STATISTICS
MACHINE- Number [0..1], machine-specifications. MACHINE
SPECIFICATIONS
MACHINE-STATUS _| Number [0..1], machine-status. MACHINE
Table machine-assigned
Fields Data Type Description
INDEX Number [1], system use
MACHINES- Number [1], system use
ASSIGNED .
MACHINE-KEY Number [0..1], machine-key.MACHINE-ASSIGNED

49


Table type-time-sheets

Table type-tool-definition
Table type-tool-inventory-item
Table type-tool-orders

Table type-toolset-instance
Table type-unit-of-measurement
Table type-work-item-group

Table unit-operation-duration

Table type-tool-catalog

Table type-tool-instance

Table type-tool-order

Table type-toolset-definition
Table type-toolset-inventory-item
Table type-work

Fields Data Type Description
INDEX Number [1], system use
ESTIMATED- Number [1], system use
DURATIONS
nominal-duration Number [0..1]
/Double
(Fixed)
DURATION- Number [0..1], duration-distributions. UNIT-
DISTRIBUTIONS OPERATION-DURATION
Table units-of-measurement
Fields Data Type Description
INDEX Number [1], system use
SHOP-DATA Number [1], system use
type Enumeration _| [0..1]
identifier Number (y
number Text (
name Text [0..1]
description Text [0..1]
REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
angular-units Enumeration | [0..1]
currency-units Enumeration | [0..1]
distance-units Enumeration | [0..1]
length-units Enumeration | [0..1]
mass-units Enumeration _| [0..1]
speed-units Enumeration | [0..1]
time-duration-units Enumeration _| [0..1]
volume-units Enumeration | [0..1]

Table use-logical-clause

Fields Data Type Description
INDEX Number [1], system use
LOGICAL- Number [1], system use
STRUCTURE

identifier Number (1)

118



/Integer

MACHINE- Number [1..*], machine-assigned. MAC HINES-
ASSIGNED ASSIGNED
Table machines-required
Fields Data Type Description
INDEX Number [1], system use
RESOURCES- Number [1], system use
REQUIRED
MACHINE- Number [1..*], machine-configuration. MACHINES-
CONFIGURATION REQUIRED
Table machines-section
Fields Data Type Description
INDEX Number [1], system use
RESOURCES- Number [1], system use
SECTION
MACHINE- Number [1..*], machine-schedule: MACHINES-
SCHEDULE SECTION
Table machines-used
Fields Data Type Description
INDEX Number [1], system use
RESOURCES-USED _| Number [1], system use
number-of-machines Number fy]
/Integer
MACHINE-USED Number [1..*], machine-used. MACHINES-USED
Table mail
Fields Data Type Description
INDEX Number [1], system use
ADDRESS Number [1], system use
type Enumeration _| [0..1]
identifier Number (1)
name Text [0..1]
street-address1 Text {1
street-address2 Text {0..1]
city Text (1)
state Text 01]
country Text [0..1]
postal-code Text [0..1]
Table maintained-resource-keys
Fields Data Type Description
INDEX Number [1], system use

54



“routing.
1." sheet _

= z
operation-

sheet

ee
| _process-plans * [INDEX
INDEX PROCESS-PLANS
SHOP-DATA type
type identifier
identifier number
number name
name description
description ROUTING-SHEETS
REFERENCE-KEYS i] OPERATION-SHEETS
REVISIONS An MACHINE-PROGRAMS
PROCESS-PLAN BATCH-SIZES
1 REFERENCE-KEYS
REVISIONS
0.4} lo1 4 4
revisions ision
Ir revision [

| ereators - — ‘ :

Figure C-14: process-plans and related tables

139


MACHINE-GROUP-_ | Number [0..1], machine-group-key. MACHINE-
KEY ASSIGNED
SETUP- Number [0.1], setup-configurations. MACHINE-
CONFIGURATIONS ASSIGNED
EVENTS Number [0..1], events. MACHINE-ASSIGNED
Table machine-configuration
Fields Data Type Description
INDEX Number [1], system use
MACHINES- Number [1], system use
REQUIRED
minimum-machines Number 0]
/Integer
maximum-machines Number {1]
/Integer
MACHINE-KEYS Number [1], machine-keys. MACHINE-
CONFIGURATION
SETUP- Number [0..1], setup-configurations. MACHINE-
CONFIGURATIONS CONFIGURATION
Table machine-group
Fields Data Type Description
INDEX Number [1], system use
MACHINES Number [1], system use
type Enumeration _| [0..1]
identifier Number (1
number Text ag}
name Text [0..1]
description Text [0..1]
hourly-rate Number [0..1]
/Double
(Fixed)
MACHINE-KEYS Number [0..1], machine-keys. MACHINE-GROUP
STATIION-KEYS Number [0.1], station-keys. MACHINE-GROUP
WORK- Number [0..1], work-assignments. MACHINE-GROUP
ASSIGNMENTS

Table machine-group-ke:

Fields Data Type Description
INDEX Number [1], system use
MACHINE-KEYS Number [1], system use
MACHINE- Number [1], system use
ASSIGNED

machine-group- Number (1)

identifier

50


POSITION-MULTI- | Number [1], system use

KEYS

ORGANIZATION- Number [0..1], organization-key.POSITION-MULTI-

KEY KEY

DEPARTMENT-KEY | Number [0..1], department-key.POSITION-MULTI-
KEY

POSITION-KEY Number [1], position-key.POSITION-MULTI-KEY

Table position-multi-keys

Fields Data Type Description

INDEX Number [1], system use

EMPLOYEE Number [1], system use

POISITION-MULTI- | Number [1..*], position-multi-key.POSITION-MULTI-
KEY KEYS

Table positions

Fields Data Type Description

INDEX Number [1], system use

DEPARTMENT Number [1], system use

POSITION Number [1..*], position. POSITIONS

Table precedent-constraint

Fields Data Type Description
INDEX Number [1], system use
PRECEDENT- Number [1], system use
CONSTRAINTS
type Enumeration _| [0..1]
identifier Number O)
number Text 0]
name Text [0..1]
logical-relationship Enumeration _| [0..1]
lag-percentage Number [0..1]

/Double
lag-duration Number [0..1]

/Double

(Fixed)
WORK-ITEM Number [1], work-item.PREDEDENT-CONSTRAINT
PRECEDENT- Number [1..*], precedent-work-item.PREDEDENT-
WORK-ITEM CONSTRAINT
Table precedent-constraints
Fields Data Type Description
INDEX Number [1], system use
ORDER-ITEM- Number [1], system use
DEFINITION

79



RESOURCE- Number [1]. system use

OBJECTS

type Enumeration _| [0..1]

identifier Number {1

number Text igi

name Text [0..1]

RESOURCE-KEY Number [1], resource-key.RESOURCE-OBJECT
FROM-REFERENCE- | Number [0.1], from-reference-frame.RESOURCE-
FRAME OBJECT

REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
OBJECT- Number [1..*], object-boundary.RESOURCE-OBJECT
BOUNDARY

Table resource-objects

Fields Data Type Description

INDEX Number [1], system use

LAYOUT Number [1], system use
RESOURCE-OBJECT | Number [1..*], resource-object.RESOURCE-OBJECTS
Table resources

Fields Data Type Description

INDEX Number [1], system use

SHOP-DATA Number [1], system use

type Enumeration _| [0..1]

identifier Number (1)

number Text eg

name Text [0..1]

description Text [0..1]

REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions. SOURCE
MACHINES Number [0..1], machines. RESOURCES
STATIONS Number [0..1], stations.RESOURCES
CRANES Number [0..1], cranes.RESOURCES
EMPLOYEES Number [0..1], employees.RESOURCES
TOOL-CATALOG Number [0..1], tool-catalog. RESOURCES
FIXTURE- Number [0.1], fixture-catalog. RESOURCES
CATALOG

Table resources-assigned

Fields Data Type Description

INDEX Number [1], system use

WORK- Number [1], system use

SCHEDULED-

PROGRESS

ORDER-SCHEDULE | Number [1], system use

89


number Text (1)

name Text [0..1]

description Text [0..1]

REFERENCE-KEYS _| Text [0..1], reference-keys. SOURCE
REVISIONS Text [0..1], revisions. SOURCE

FIXTURESET- Number [1], fixtureset-definition-key. FIXTURESET-
DEFINITION-KEY INVENTORY-ITEM

STOCK-LEVEL- Number [1], stock-level-quantities. FIXTURESET-
QUANTITIES INVENTORY-ITEM

PROCUREMENT- Number [0..1], procurement-multi-keys.FIXTURESET-
MULTI-KEYS INVENTORY-ITEM

FIXTURESET- Number [0..1], fixtureset-instances.FIXTURESET-
INSTANCES INVENTORY-ITEM

Table fixtureset-member-keys

Fields Data Type Description

INDEX Number 1], system use

FIXTURESET- Number [1], system use

DEFINITION

FIXTURE- Number [0..*], fixture-definition-key. FIXTURESET-
DEFINITION-KEY MEMBER-KEYS

FIXTURESET- Number [0..*], fixtureset-definition-key.FIXTURESET-

DEFINITION-KEY

MEMBER-KEYS

Table from-reference-frame

Fields Data Type | Description

INDEX Number _| [1], system use

REFERENCE- Number [1]. system use

FRAME

AREA Number [1], system use

PATH Number [1], system use

RESOURCE-OBJECT | Number [1], system use

PART-OBJECT Number [1], system use

REFERENCE- Number [1], reference-frmae-key. FROM-REFERENCE-
FRAME-KEY FRAME

TRANSLATION Number 0.1], translation. FROM-REFERENCE-FRAME
SCALING Number [0..1], scaling. FROM-REFERENCE-FRAME
ROTATION Number [0..1], rotation.FROM-REFERENCE-FRAME
Table group-technology-code

Fields Data Type Description

INDEX Number [1], system use

PART Number [1], system use

shape-code Text [0..1]

material-code Text [0..1]

44


INDEX
SKILL-DEFINITION
minimum-level-number
maximum-level-number

LEVEL-DEFINITION

identifie
number
name

description

r

skiiedefinfions

INDEX
SHOP-DATA
type
identifier
number
name
description

REVISIONS

REFERENCE-KEYS

~ skill-definition

SKILL-DEFINITION

=EEI

INDEX
SKILL-DEFINITIONS
type

identifier

number

name

description

LEVELS

|

reference-key

- 7 revision tp

1AP

“creator

Figure C-5: skill-definitions and related tables

130



Table tool-orders

Fields Data Type Description

INDEX Number [1], system use

WORK Number [1], system use

type Enumeration _| [0..1]

identifier Number (1)

number Text (1

name Text | {0..1]

description Text [0..1]

REFERENCE-KEYS _| Text [0..1], reference-keys. SOURCE
REVISIONS Text [0..1], revisions. SOURCE
TOOL-ORDER Number [1..*], tool-order. TOOL-ORDERS
Table tool-orders-section

Fields Data Type Description

INDEX Number [1], system use
WORK-SECTION Number [1], system use
TOOL-ORDER- Number [1..*], tool-order-schedule. TOOL-ORDERS-
SCHEDULE SECTION

Table toolset-definition

Fields Data Type Description

INDEX Number [1], system use
TOOL-CATALOG Number [1], system use

type Enumeration _| [0..1]

identifier Number (1)

number Text 0]

name Text [0..1]

description Text [0..1]

REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions. SOURCE
TOOLSET- Number [1], toolset-member-keys. TOOSET-
MEMBER-KEYS DEFINITION

Table toolset-definition-key_

Fields Data Type Description

INDEX Number | [1], system use

SETUP- Number [1], system use
COMPONENTS

TOOL-ORDER- Number [1], system use

DEFINITION

TOOLSET- Number [1]. system use
MEMBER-KEYS

TOOLSET- Number [1], system use

INVENTORY-ITEM

114


Table work-section

Fields Data Type Description

INDEX Number [1], system use

SCHEDULE Number [1], system use

ORDERS-SECTION __| Number [0..1], orders-section. WORK-SECTION
JOBS-SECTION Number [0..1], jobs-section. WORK-SECTION
TASKS-SECTION Number [0..1], tasks-section. WORK-SECTION
MAINTENANCE- Number [0..1], maintenance-orders-section.WORK-
ORDERS-SECTION SECTION

PICK-ORDERS- Number [0..1], pick-orders-section. WORK-SECTION
SECTION

TOOL-ORDERS- Number [0..1], pick-orders-section. WORK-SECTION
SECTION

123


1. days-of- holidays
week 0.4
Ce ae are:
shift ae breaks
1..*
—_ eee
calendars | __ calendar
ealendars Noe INDEX
INDEX 1." CALENDAR
SHOP-DATA CALENDARS 4. type
type type [- identifier
identifier identifier number
number number name
name dezcrsiion description
description REFERENCE-KEYS [7
REFERENCE-KEYS |) REFERENCE-KEYS 7) REVISIONS
REVISIONS F REVISIONS 1 effective-start-date
CALENDAR effective-start-date effective-end-date
1 effective-end-date SHIFTS {4}
SHIFT-SCHEDULE 1 HOLIDAYS
ot} 0.4 oj} 0.1 10.1 4
ce-keys Prana ‘as
I eT _-tevisions revision F

0..1

Figure C-3: calendars and related tables

128




al

I20ZZ00Z += f€0 2007 LE-€-€00Z = |L-b-€007 COOTIOIUIN COOTIPIUIMNGOZET E007AIUINZO0ZO0Z = 9
-saulyoeu} -seulyoeu -saulyoew
-oyloads} = -oyloeds) -oyloads|
-uewny = -uewny| -uewny|
(e0ZZ00Z VOL ZO07 LE-E-E00Z = |L-L-€007 COOZIOIUIN COOZIAIUISOZES COOTAIUIM|ZOOZOOZ S
-saulyoeu} -seulyoeu -Saulyoeu|
-payewoyne) -payewojne| -payewojne|
|cOZ007 [EOL Z007 LE-E-E007 = |L-L-€007 EOOTISIUINN COOZIOIUINPOZEZ EOOTIUIM|ZOOZOOZ id
-SauIYyoeW) -SaulyoeU! -saulyoeul
-payesado) §-payesado) -payesado|
-uewny = -uewny| -uewny|
LOzz00z cOL 2007 L€-Zb-ZO0? |L-Zb-007 ZOOZIIEAIEOTET ZO0ZIIE4}L00Z007 Ie
-saulyoeu -Saulyoeu
-o1yloeds| -oyloads|
-uewny| -uewny| -uewny|
IZOLZ00Z L€-L-Z007 |L-2L-200e zoociles| = zoozilesjzozez ZOOTIeA|LOOZOOZ =z
-saulyoew} -seulyoeu| -S@ulyoeUl|
-payewojne| -payewojne| -payewojne|
LOzz00z LOLz00z L€-Zb-ZO0? |L-Zb-Z007 zooziles| = zO0zilesjLOzez 4o00z00z b
-saulyoeu) -seulyoeul| !
-payesado} + -payesado| -payesedo}
-uewny) -uewny) -uewny|
SAVGMOH ~~ SLSIHS)—-8}eP-pue| @yep-pe}s) UoNduosap) aweul— Jaquinul adh] YVAN VO] XSGNI
-aanoayje| —_-annoaya

solquy, eq ojduies :q xipueddy

STMPIYOSAFIGS JO BTqeL



[CONTACT 7
Table target-machines
Fields Data Type Description
INDEX Number [1], system use
MACHINE- Number [1], system use
PROGRAM
MACHINE-KEYS Number [1..*], machine-keys. TARGET-MACHINES
controller-type Enumeration _| [0..1]
Table task
Fields Data Type Description
INDEX Number [1], system use
TASKS Number [1], system use
type Enumeration _| [0..1]
identifier Number ig}
number Text [iy
name Text [0..1]
description Text [0..1]
REFERENCE-KEYS | Text | [0..1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions. SOURCE
TASK-DEFINITION _| Number {0..1], task-definition. TASK
TASK-STATUS Number [0..1], task-status. TASK
Table task-definition
Fields Data Type Description
INDEX Number [1], system use
TASK Number [1], system use
CUSTOMERS Number [0..1], customers. TASK-DEFINITION
priority-rating Enumeration | [0..1]
DUE-DATES Number [1], due-dates. TASK-DEFINITION
PART-QUANTITIES _| Number [1], part-quantities. TASK-DEFINITION
PROCESS-PLAN- Number [0..1], process-plan-multi-key. TASK-
MULTI-KEY DEFINITION
repetition-count Number [0..1]

/Integer

PARAMETERS Number [0..1], parameters. TASK-DEFINITION
RESOURCES- Number [0..1], resources-required. TASK-DEFINITION
REQUIRED
CHILD-WORK- Number [0..1], child-work-items. TASK-DEFINITION
ITEMS
PRECEDENT- Number [0..1], precedent-constraints. TASK-
CONSTRAINTS DEFINITION
ESTIMATED- Number [0..1], estimated-durations. TASK-
DURATIONS DEFINITION

107


Fields Data Type Description

INDEX Number | [1], system use

SETUP-DEFINITION | Number [1], system use

SETUP- Number [1..*], setup-definition-key.PARENT-SETUP-
DEFINITION-KEY KEYS

Table part

Fields Data Type Description

INDEX Number [1], system use

PARTS Number [1], system use

type Enumeration _| [0..1]

identifier Number 1]

number Text 0]

name Text [0..1]

description Text (0..1]

REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions SOURCE
CUSTOMERS Number [0..1], customers.PART

SUPPLIERS Number [0..1], suppliers. PART

BILL-OF- Number [0..1], bill-of-materials-key. PART
MATERIALS-KEY

PROCESS-PLAN- Number [0..1], process-plan-multi-keys.PART
MULTI-KEYS

PART- Number [0..1], part-specifications. PART
SPECIFICATIONS

GROUP- Number [0..1], group-technology-code.PART
TECHNOLOGY-

CODE

Table part-instance

Fields Data Type Description

INDEX Number [1], system use

PART-INSTANCES __| Number [1], system use

type Enumeration | [0..1]

identifier Number f)

number Text {1

name Text [0..1]

current-location Text [0..1]

STATUS-CODE Number [0..1], status-code.PART-INSTANCE
WORK- Number [0..1], work-assignments.PART-INSTANCE
ASSIGNMENTS

COMPONENT- Number [0..1], component-part-multi-keys.PART-

PART-MULTI-KEYS

INSTANCE

Table part-instance-key

67


workpiece-capacity

Number

[0..1]

/Double
PART-KEY Number [0..1], part-key. MACHINE-
SPECIFICATIONS
Table machine-status
Fields Data Type Description
INDEX Number [1], system use
MACHINE Number [1], system use
operational-state Enumeration _| [0..1]
maintenance-state Enumeration _| [0..1]
SETUP- Number [0..1], setup-configurations. MACHINE-
CONFIGURATIONS STATUS
WORK- Number [0..1], work-assignments. MACHINE-STATUS
ASSIGNMENTS ’
Table machine-used
Fields Data Type Description
INDEX Number [1], system use
MACHINES-USED Number [1]. system use
MACHINE-KEY Number [1], machine-key. MACHINE-USED
SETUP- Number [0..1], setup-configurations. MACHINE-USED
CONFIGURATIONS
EVENTS Number [0..1], events. MACHINE-USED
Table machines
Fields Data Type Description
INDEX Number [1], system use
RESOURCES Number [1], system use
type Enumeration | [0..1]
identifier Number 0]
number Text 01]
name Text [0..1]
description. Text [0..1]
REFERENCE-KEYS _| Text [0..1], reference-keys. SOURCE
REVISIONS Text [0..1], revisions. SOURCE
MACHINE Number [1..*], machine. MACHINES
MACHINE-GROUP __| Number [0..*], machine.group.MACHINES
Table machines-assigned
Fields Data Type Description
INDEX Number [1], system use
RESOURCES- Number [1]. system use
ASSIGNED
number-of-machines _| Number [1]

53



employee-capacity Number [0..1]

/Integer
EMPLOYEE-KEYS Number [0..1], employee-keys.STATION-GROUP
WORK- Number [0..1], work-assignments.STATION-GROUP
ASSIGNMENTS
STATION-KEYS Number [1], station-keys.STATION-GROUP

Table station-group-key

Fields Data Type Description
INDEX Number [1], system use
STATION-KEYS Number [1], system use
STATION- Number [1]. system use
ASSIGNED

STATION-USED Number [1], system use
STATION- Number [1], system use
SCHEDULE

station-group- Number U1]

identifier

station-group-number_| Text (1

Table station-key

Fields Data Type Description
INDEX Number [1], system use
STATIONS-KEYS Number [1], system use
STATION- Number [1], system use
ASSIGNED

STATION-USED Number [1], system use
MAINTAINED- Number [1], system use
RESOURCE-KEYS

LOCATIONS- Number [1], system use
SERVED-KEYS

SETUP- Number [1], system use
RESOURCES-KEYS

STATION- Number [1], system use
SCHEDULE

station-identifier Number 0)
station-number Text a)

Table station-keys

Fields Data Type Description
INDEX Number [1], system use
STATION- Number [1], system use
CONFIGURATON

MACHINE Number [1], system use
MACHINE-GROUP __| Number [1], system use

102


/Double

maximum-load- Number [0..1]
capacity /Double
speed Number [0..1]
/Double
Table crane-status
Fields Data Type Description
INDEX Number [1], system use
CRANE Number [1], system use
operational-state Enumeration _| [0..1]
maintenance-state Enumeration _| [0..1]
SETUP- Number [0..1], setup-configurations. CRANE-STATUS
CONFIGURATIONS
WORK- Number [0..1], work-assignments.CRANE-STATUS
ASSIGNMENTS
Table crane-used
Fields Data Type Description
INDEX Number [1], system use
CRANES-USED Number [1], system use
CRANE-KEY Number [1], crane-key. CRANE-USED
SETUP- Number [0..1], setup-definition-keys.CRANE-USED
DEFINITION-KEYS
EVENTS Number [0..1], events. CRANE-USED
Table cranes
Fields Data Type Description
INDEX Number [1], system use
RESOURCES Number [1], system use
type Enumeration | [0..1]
identifier Number (y
number Text (
name Text [0.1]
description Text [0..1]
REFERENCE-KEYS _| Text [0..1], reference-keys. SOURCE
REVISIONS Text [0..1], revisions. SOURCE
CRANE Number [1..*], crane. CRANES
Table cranes-assigned
Fields Data Type Description
INDEX Number [1], system use
RESOURCES- Number [1], system use
ASSIGNED
number-of-cranes Number (J

27



SETUP-
DEFINITION-KEY

Number

[1..*], setup-definition-key.CHILD-SETUP-
KEYS

Table child-work-items

Fields Data Type | Description

INDEX Number [1], system use

ORDER-ITEM-DEFINITION | Number [1], system use

JOB-DEFINITION Number [1], system use

TASK-DEFINITION Number [1], system use

MAINTENANCE-ORDER- | Number [1], system use

DEFINITION

WORK-KEYS Number [1], work-keys.CHILD-WORK-ITEMS

WORK-ITEM-GROUP Number [1..*], work-item-group.CHILD-WORK-

ITEMS

Table component-part-multi-key

Fields Data Type Description

INDEX Number {1], system use

COMPONENT- Number [1], system use

PART-MULTI-KEYS

PART-KEY Number [0..1], part-key.COMPONENT-PART-
MULTI-KEY

PART-INVENTORY- | Number [0..1], part-inventory-item-

ITEM-KEY key. COMPONENT-PART-MULTI-KEY

PART-INSTANCE- Number [0.1], part-instance-key. COMPONENT-

KEY

PART-MULTI-KEY

Table component-part-multi-keys

Fields Data Type Description

INDEX Number [1], system use
PART-INSTANCE Number [1], system use
COMPONENT- Number [1..*], component-part-multi-
PART-MULTI-KEY key. COMPONENT-PART-MULTI-KEYS
Table contact

Fields Data Type Description

INDEX Number 1], system use

CONTACTS Number [1], system use

type Enumeration | [0..1]

identifier Number 1]

number Text 0]

name Text [0..1]

ADDRESSES Number [1], addresses. CONTACT

Table contact-key

23



Table step-precedent-constraints

Fields Data Type Description
INDEX Number [1], system use
PLAN-DEFINITION _| Number [1], system use
STEP-PRECEDENT- | Number [1..*], step-precedent-constraint.STEP-
CONSTRAINT PRECEDENT-CONSTRAINTS
Table stock-level-quantities
Fields Data Type Description
INDEX Number [1], system use
TOOL-INVENTORY- | Number [1], system use
ITEM
TOOLSET- Number [1], system use
INVENTORY-ITEM
FIXTURE- Number [1], system use
INVENTORY-ITEM
FIXTURESET- Number [1], system use
INVENTORY-ITEM
PART-INVENTORY- | Number [1], system use
ITEM
MATERIALS- Number [1], system use
INVENTORY-ITEM
on-hand-quantity Number (]

/Integer
allocated-quantity Number [0..1]

‘Integer
safety-stock-quantity | Number [0..1]

/Integer
required-quantity Number [0..1]

/Integer
on-order-quantity Number [0..1]

/Integer
back-order-quantity Number (0..1]

/Integer
work-in-process- Number [0..1]
quantity /Integer
Table suppliers
Fields Data Type Description
INDEX Number [1], system use
PART Number [1], system use
PROCUREMENT- Number [1], system use
DEFINITION
ORGANIZATIONAL- | Number [1..*], organizational-contact. SUPPLIERS

106


Table employee-status

Fields Data Type Description
INDEX Number [1], system use
EMPLOYEE Number [1], system use
WORK- Number [0..1], work-assignments-EMPLOYEE-
ASSIGNMENTS STATUS
EMPLOYEE- Number [0..1], employee-availability-EMPLOYEE-
AVAILABILITY STATUS
PAY-STATUS Number [0..1], pay-status-EMPLOYEE-STATUS
Table employee-used
Fields Data Type Description
INDEX Number [1], system use
RESOURCES-USED _| Number [1], system use
PART-QUANTITIES _| Number [1], part-quantities.PARTS-USED
PART-INSTANCE- Number [0..*], part-instance-key.PARTS-USED
KEY
Table employees
Fields Data Type Description
INDEX Number [1], system use
RESOURCES Number [1], system use
type Enumeration | [0..1]
identifier Number inal
number Text eel
name Text [0..1]
description Text [0..1]
REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text ‘[0..1], revisions. SOURCE
EMPLOYEE Number 1..*], employee.EMPLOYEES
Table employees-assigned
Fields Data Type Description
INDEX Number [1], system use
RESOURCES- Number [1], system use
ASSIGNED
number-of-employees | Number (]
/Integer
EMPLOYEE- Number [1..*], employee-assigned.EMPLOYEES-
ASSIGNED ASSIGNED
Table employees-required
Fields Data Type Description
INDEX Number [1], system use
RESOURCES- Number [1], system use

35



MULTI-KEYS INVENTORY-ITEM

TOOLSET- Number [0..1], toolset-instances. TOOLSET-
INSTANCES INVENTORY-ITEM
Table toolset-member-keys
Fields Data Type Description
INDEX Number [1], system use
TOOLSET- Number [1], system use
DEFINITION
TOOL-DEFINITION- | Number [0..*], tool-definition-key. TOOLSET-
KEY MEMBER-KEYS
TOOLSET- Number [0..*], toolset-definition-key. TOOLSET-
DEFINITION-KEY MEMBER-KEYS
Table total-operations-duration
Fields Data Type Description
INDEX Number [1], system use
ESTIMATED- Number [1], system use
DURATIONS
nominal-duration Number [0..1]
/Double
(Fixed)
DURATION- Number [0..1], duration-distributions. TOTAL-
DISTRIBUTIONS OPERATIONS-DURATION
Table translation
Fields Data Type Description
INDEX Number [1], system use
FROM-REFERENCE- | Number [1], system use
FRAME
x-coefficient Number {
/Double
y-coefficient Number {]
/Double
z-coefficient Number 1]
/Double
Table type-area *
Fields Data Type Description
classification-items Text (1..*]

* All table names with “type-“ prefix carry the same data structure as Table type-area.
These tables, listed below, are used to define type for each corresponding object. Type is
an enumeration of a set of classification items.

Table type-availability-exception Table type-bill-of-materials

Table type-bills-of-materials Table type-boundary-element

Table type-bom-element Table type-calendar

116



INDEX

SHOP-DATA

type

identifier

number

name

description
REFERENCE-KEYS
REVISIONS
TOOL-INVENTORY
FIXTURE-INVENTORY
PART-INVENTORY
MATERIALS-INVENTORY

Figure C-12: inventory and related tables

137



Fields Data Type Description

INDEX Number [1], system use

INVENTORY Number [1], system use

MATERIALS- Number [1..*], materials-inventory-item.MATERIALS-

INVENTORY-ITEM

INVENTORY

Table materials-inventory-item

Fields Data Type Description
INDEX Number [1], system use
MATERIALS- Number [1], system use
INVENTORY
type Enumeration | [0..1] _
identifier Number U1]
number Text (]
name Text [0..1]
description Text ([0..1]
REFERENCE-KEYS _| Text [0..1], reference-keys. SOURCE
REVISIONS Text [0..1], revisions. SOURCE
PART-KEY Number [0..1], part-key MATERIALS-INVENTORY-

ITEM
material-type Enumeration 1)
STOCK-LEVEL- Number [1], stock-level-quantities MATERIALS-
QUANTITIES INVENTORY-ITEM
PROCUREMENT- Number [0..1], procurement-multi-keys.: MATERIALS-
MULTI-KEYS INVENTORY-ITEM
MATERIALS- Number [0..1], materials-instances. MATERIALS-
INSTANCES INVENTORY-ITEM
Table mean-time-between-failures
Fields Data Type Description
INDEX Number [1], system use
RELIABILITY- Number [1], system use
STATISTICS
nominal-duration Number [0..1]

/Double
(Fixed)

DURATION- Number [0..1], duration-distributions. MEAN-TIME-
DISTRIBUTIONS ETWEEN-FAILURES
Table mean-time-to-repair
Fields Data Type Description
INDEX Number (1], system use
RELIABILITY- Number [1], system use
STATISTICS
nominal-duration Number [0..1]

59



TOOL-ORDER Number [1], system use

CUSTOMERS Number [0..1], customers. TOOL-ORDER-
DEFINITION

priority-rating Enumeration | [0..1]

DUE-DATES Number [1], due-dates. TOOL-ORDER-DEFINITION

TOOL-DEFINITION- | Number [0..*], tool-definition-key. TOOL-ORDER-

KEY DEFINITION

TOOLSET- Number [0..*], toolset-definitioni-key. TOOL-ORDER-

DEFINITION-KEY DEFINITION

RESOURCES- Number [0..1], resources-required. TOO L-ORDER-

REQUIRED DEFINITION

WORK-KEYS Number [0..1], work-keys. TOOL-ORDER-
DEFINITION

COST-SUMMARY Number [0..1], cost-summary. TOOL-ORDER-
DEFINITION

Table tool-order-key

Fields Data Type Description

INDEX Number [1], system use

WORK-KEYS Number [1], system use

TOOL-ORDER- Number [1], system use

SCHEDULE

tool-order-identifier Number 0]

tool-order-number Text (1)

Table tool-order-schedule

Fields Data Type Description

INDEX Number [1], system use

TOOL-ORDERS- Number [1], system use

SECTION

TOOL-ORDER-KEY | Number [1], tool-order.key. TOOL-OR DER-
SCHEDULE

RESOURCES- Number [1], resources-assigned. TOOL-ORDER-

ASSIGNED SCHEDULE

Table tool-order-status

Fields Data Type Description

INDEX Number [1], system use

TOOL-ORDER Number [1], system use

WORK- Number [0..1], work-scheduled-progress. TOOL-

SCHEDULED- ORDER -STATUS

PROGRESS

WORK-ACTUAL- Number [0..1], work-actual-progress. TOOL-ORDER -

PROGRESS STATUS

113


DURATIONS

PARAMETERS Number [0..1], parameters.PLAN-STEP
REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
RESOURCES- Number [0..1], resources-required.PLAN-STEP
REQUIRED
Table plan-step-key
Fields Data Type Description
INDEX Number [1], system use
STEP-PRECEDENT- | Number [1], system use
CONSTRAINT
plan-step-identifier Number {1]
plan-step-number Text {y
Table plan-steps
Fields Data Type Description.
INDEX Number [1], system use
PLAN-DEFINITION _| Number [1]. system use
PLAN-STEP Number [1..*], plan-step.PLAN-STEPS
Table planned-costs
Fields Data Type Description
INDEX Number [1], system use
COST-SUMMARY Number [1], system use
COST-CATEGORIES | Number [1], cost-categories.PLANNED-COSTS
Table position.
Fields Data Type Description
INDEX Number [1], system use
POSITIONS Number [1], system use
type Enumeration | [0..1]
identifier Number (J
number Text 01]
name Text [0..1]
hourly-rate Number [0..1]

/Double -

(Fixed)
ADDRESSES Number [0..1], addresses. POSITION
POSITION- Number [0..1], position-description-key.POSITION
DESCRIPTION-KEY
number-of-positions Number [0..1]

/Integer
EMPLOYEE-KEYS Number [0..1], employee-keys. POSITION

77


SETUP- Number [1], setup-definition-key. CURRENT-SETUP
DEFINITION-KEY

Table customers

Fields Data Type Description

INDEX Number [1], system use
JOB-DEFINITION Number [1], system use

ORDER- Number [1]. system use

DEFINITION

TASK-DEFINITION _| Number [1], system use
MAINTENANCE- Number [1], system use

ORDER-

DEFINITION

PICK-ORDER- Number [1]. system use

DEFINITION

TOOL-ORDER- Number [1]. system use

DEFINITION

PART Number [1], system use
ORGANIZATIONAL- | Number [1,*], organizational-contact. CUSTOMERS
CONTACT

Table day-of-week

Fields Data Type Description

INDEX Number [1], system use
DAYS-OF-WEEK Number [1], system use

day-number Enumeration | [0..1]

day-name Enumeration | [1]

Table days-of-week

Fields Data Type Description

INDEX Number [1], system use

SHIFT Number [1], system use
DAY-OF-WEEK Number [1..*], day-of-week.DA YS-OF-WEEK
Table department

Fields Data Type Description

INDEX Number [1], system use
DEPARTMENTS Number [1], system use.

type Enumeration | [0..1]

identifier Number 0)

number Text 0)

name Text [0..1]

level Text [0..1]

CONTACTS Number [0..1], contacts. DEPARTMENT
ADDRESSES Number [0..1], addresses. DEPARTMENT

29


[COMPONENTS l | CONFIGURATION
Table setup-configurations
Fields Data Type Description
INDEX Number [1], system use
STATION- Number [1]. system use
CONFIGURATION
MACHINE- Number [1], system use
CONFIGURATION
CRANE-REQUIRED _| Number [1], system use
MACHINE- Number [1], system use
ASSIGNED
MACHINE-USED Number [1], system use
MACHINE-STATUS _| Number [1], system use
CRANE-STATUS Number [1], system use
SETUP- Number [1..*], setup-configuration.SETUP-
CONFIGURATION CONFIGURATIONS
Table setup-definition
Fields Data Type Description
INDEX Number [1], system use
SETUP- Number [1], system use
DEFINITIONS
type Enumeration _| [0..1]
identifier Number 1]
number Text 1]
name Text [0..1]
description Text [0..1]
REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions SOURCE
SETUP- Number [1], setup-components.SETUP-DEFINITION
COMPONENTS :
SETUP- Number [1], setup-resources-keys.SETUP-
RESOURCES-KEYS DEFINITION
PARENT-SETUP- Number [0..1], parent-setup-keys.SETUP-
KEYS DEFINITION
CHILD-SETUP- Number [0..1], child-setup-keys.SETUP-DEFINITION
KEYS
Table setup-definition-key
Fields Data Type Description
INDEX Number [1], system use
SETUP- Number [1], system use
CONFIGURATION
SETUP- Number [1], system use

95


INVENTORY-ITEM, PART-INVENTORY-ITEM and MATERIALS-INVENTORY-ITEM
are parent-elements of stock-level-quantities.

INDEX
TOOL-INVENTORY-ITEM
TOOLSET-INVENTORY-ITEM
FIXTURE-INVENTORY-ITEM
FIXTURESET-INVENTORY-ITEM
PART-INVENTORY-ITEM
MATERIALS-INVENTORY-ITEM
on-hand-quantity
allocated-quantity
safety-stock-quantity
required-quantity
on-order-quantity
back-order-quantity
work-in-process-quantity

Figure 3: Stock-level-quantities

The XML definition of stock-level-quantities is presented as follows.

<stock-level-quantities>
<on-hand-quantity />
<allocated-quantity />
<safety-stock-quantity />
<required-quantity />
<on-order-quantity />
<back-order-quantity />
<work-in-process-quantity />

</ stock-level-quantities >

5.3 Data structure in a design view

In Access, Design View is used to create fields in each table. A design view contains
information presented in three columns: Field, Data Type, and Description. The “Field”
column identifies attributes of the element and special elements that are used by the
database system, or the referenced or referencing tables. The “Data Type” column defines
the data type format for the field. The “Description” column is reserved for information
about the attribute usage, domain, definition (that defines the internal relationship
between tables), reference, cardinality relationship, etc. The cardinality relationship
specifies how many specific instances of the child element could be related to the parent
element. The cardinality relationship may be one to zero or one, one to zero or more, one
to one or more, or exactly “n” occurrences, and is presented in the design view as [0..1],
(0..*], [1..*], [n], respectively.

10


Table position-description

Fields Data Type Description
INDEX Number [1], system use
POSITION- Number [1], system use
DESCRIPTIONS

type Enumeration _| [0..1]

identifier Number (1)

number Text [1]

name Text [0..1]

description Text [0..1]

Table position-description-key

Fields Data Type Description
INDEX Number [1], system use
POSITION Number [1], system use
position-description- Number (]

identifier

position-description- | Text 1)

number

Table position-descriptions

Fields Data Type Description
INDEX Number [1], system use
ORGANIZATION Number [1], system use
type Enumeration _| [0..1]

identifier Number {

number Text (1)

name Text [0..1]

description Text [0..1]
REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions.SOURCE
POSITION- Number [1..*], position-description. POSITION-
DESCRIPTION DESCRIPTIONS
Table position-key

Fields Data Type Description
INDEX Number [1], system use
POSITION-MULTI- | Number [1], system use
KEY

position-identifier Number (1)
position-number Text (1)

Table position-multi-ke:

Fields Data Type Description
INDEX Number [1], system use

78



[ STATUS

Table procurement-definition

Fields Data Type Description

INDEX Number [1], system use

PROCUREMENT Number [1], system use

priority-rating Enumeration _| [0..1]

DUE-DATES Number [1], due-dates.PROCUREMENT-
DEFINITION

SUPPLIERS Number [0..1], suppliers. PROCUREMENT-
DEFINITION

PROCUREMENT- Number [1], procurement-items.PROCUREMENT-

ITEMS DEFINITION

Table procurement-item

Fields Data Type Description

INDEX Number [1], system use

PROCUREMENT- Number [1], system use

ITEMS

type Enumeration | [0..1]

identifier Number f1)

number Text seg)

name Text [0..1]

description Text [0..1]

PROCUREMENT- Number [0..1], procurement-item-

ITEM-DEFINITION definition.PROCUREMENT-ITEM

PROCUREMENT- Number [0..1], procurement-item-

ITEM-STATUS status.PROCUREMENT-ITEM

Table procurement-item-definition

Fields Data Type Description

INDEX Number [1], system use

PROCUREMENT- Number [1], system use

ITEM

priority-rating Enumeration | [0..1]

DUE-DATES Number [1], due-dates. PROCUREMENT-ITEM-
DEFINITION

WORK-KEYS Number [0..1], work-keys.PROCUREMENT-ITEM-
DEFINITION

PROCURED-PART Number [1..*], procured-part.PROCUREMENT-ITEM-

DEFINITION

Table procurement-item-key

Fields

Data Type

Description

INDEX

Number

[1], system use

84


Shop Data

ek A XML Working
information Parsers Forms, e.g.,
Model/ C++ Structures
XML Schema
- Database
Machine Shop To/From DBMS .
Simulator le—>! Translators j<— Tables/Objects
1f@)
|
To/F: XML ae
- o/From Instance
Machine Sho} (J
aa Pp <> Translators Documents
vo

Figure 1: Standard data interfaces

3. OVERVIEW OF THE INFORMATION MODEL

NIST’s machine shop data model [7] is presented in both a graphical form in the Unified
Modeling Language (UML) [8], and a textual form in XML. The model contains twenty
major manufacturing data elements. The primary objective is to develop a structure for
exchanging shop data between various manufacturing software applications, including
simulation. The idea is to use the same data structures for managing actual production
operations and for simulating the machine shop. The rationale is that if one structure can
serve both purposes, the need for translation and abstraction of the real data would be
minimized when simulations are constructed. The mapping of real world data into
simulation abstractions is not, for the most part, addressed in the current data model.
Figure 2 illustrates the machine shop data model’s top-level data structure that includes
most of the major manufacturing data elements identified in the model. The name along
with a brief description of each major element is described as follows.


INVENTORY Number [1], system use

TOOL-INVENTORY- | Number [1..*], tool-inventory-item.TOOL-

ITEM INVENTORY

TOOLSET- Number [0..*], toolset-inventory-item.TOOL-

INVENTOR Y-ITEM INVENTORY,

Table tool-inventory-item

Fields Data Type Description

INDEX Number [1], system use

TOOL-INVENTORY _| Number [1], system use

type Enumeration _| [0..1]

identifier Number (1)

number Text fi)

name Text [0..1]

description Text [0..1]

REFERENCE-KEYS _| Text [0..1], reference-keys.SSOURCE

REVISIONS Text [0..1], revisions SOURCE

TOOL-DEFINITION- | Number [1], tool-definition-key. TOOL-INVENTORY-

KEY ITEM

STOCK-LEVEL- Number [1], stock-level-quantities. TOOL-

QUANTITIES INVENTORY-ITEM

PROCUREMENT- Number [0..1], procurement-multi-keys. TOOL-

MULTI-KEYS INVENTORY-ITEM

TOOL-INSTANCES _ | Number [0..1], tool-instances. TOOL-INVENTORY-
ITEM

Table tool-order

Fields Data Type Description

INDEX Number [1], system use

TOOL-ORDERS Number [1], system use

type Enumeration | [0..1]

identifier Number 1]

number Text (]

name Text [0..1]

description Text [0..1]

REFERENCE-KEYS _| Text [0..1],reference-keys. SOURCE

REVISIONS Text 0..1],revisions. SOURCE

TOOL-ORDER- Number [0..1], tool-order-definition. TOOL-ORDER

DEFINITION

TOOL-ORDER- Number [0..1], tool-order-status. TOOL-ORDER

STATUS

Table tool-order-definition

Fields Data Type Description

INDEX Number [1], system use

112


Fields Data Type Description

INDEX Number [1], system use

PROCESS-PLANS Number [1], system use

type Enumeration _| [0..1]

identifier Number 0]

number Text f]

name Text [0..1]

description Text [0..1]

REFERENCE-KEYS _| Text [0..1], reference-keys. SOURCE
REVISIONS Text [0..1], revisions. SOURCE

BATCH-SIZES Number [1], batch-sizes.PROCESS-PLAN
ROUTING-SHEETS __| Number [0..1], routing-sheets.PROCESS-PLAN
OPERATION- Number [0..1], operation-sheets.PROCESS-PLAN
SHEETS

MACHINE- Number [0..1], machine-programs.PROCESS-PLAN
PROGRAMS

Table process-plan-key

Fields Data Type Description

INDEX Number [1], system use

PROCESS-PLAN- Number [1], system use

MULTI-KEY

process-plan-identifier_| Number 1

process-plan-number__| Text U1]

Table process-plan-multi-key

Fields Data Type Description

INDEX Number [1], system use

JOB-DEFINITION Number [1], system use

TASK-DEFINITION _| Number [1], system use

PROCESS-PLAN- Number [1], system use

MULTI-KEYS

PROCESS-PLAN- Number [0..1], process-plan-key.PROCESS-PLAN-
KEY MULTI-KEY

ROUTING-SHEET- Number [0..1], routing-sheet-key. PROCESS-PLAN-
KEY MULTI-KEY

OPERATION- Number [0..1], operation-sheet-key.PROCESS-PLAN-
SHEET-KEY MULTI-KEY

MACHINE- Number [0..1], machine-program-key.PROCESS-

PROGRAM-KEY

PLAN-MULTI-KEY

Table process-plan-multi-keys

Fields Data Type Description
INDEX Number [1], system use
PART Number [1], system use

82


[COST-SUMMARY __[ Number [ (0..1], cost-summary.TASK-DEFINITION _|
Table task-key
Fields Data Type Description
INDEX Number [1], system use
WORK-KEYS Number [1], system use
TASK-SCHEDULE Number [1], system use
task-identifier Number [1]
task-number Text agi
Table task-schedule
Fields Data Type Description
INDEX Number [1], system use
TASKS-SECTION Number [1], system use
TASK-KEY Number [1], task.key. TASK-SCHEDULE
RESOURCES- Number [1], resources-assigned. TASK-SCHEDULE
ASSIGNED
Table task-status
Fields Data Type Description
INDEX Number [1], system use
TASK Number [1], system use
WORK- Number [0..1], work-scheduled-progress. TASK-
SCHEDULED- STATUS
PROGRESS
WORK-ACTUAL- Number [0..1], work-actual-progress. TASK-STATUS
PROGRESS
Table tasks
Fields Data Type Description
INDEX Number [1], system use
WORK Number [1], system use
type Enumeration _| [0..1]
identifier Number (1)
number Text (1)
name Text [0..1]
description Text (0..1]
REFERENCE-KEYS _| Text [0..1], reference-keys SOURCE
REVISIONS Text [0..1], revisions SOURCE
TASK Number [1..*], task. TASKS
Table tasks-section
Fields Data Type Description
INDEX Number [1], system use
WORK-SECTION Number [1], system use

108


x-coefficient

Number

/Double
y-coefficient Number (
/Double
z-coefficient Number {1]
/Double
Table schedule
Fields Data Type Description
INDEX Number [1], system use
SCHEDULES Number [1], system use
type Enumeration _| [0..1]
identifier Number (1]
number Text [1]
name Text [0..1]
description Text [0..1]
REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions. SOURCE
RESOURCES- Number [0..1], resources-section.SCHEDULE
SECTION
WORK-SECTION Number [0..1], work-section.SCHEDULE
Table schedules
Fields Data Type Description
INDEX Number [1], system use
SHOP-DATA Number [1], system use
type Enumeration | [0..1]
identifier Number (1)
number Text 1]
name Text [0..1]
description Text [0..1]
REFERENCE-KEYS _| Text [0..1], reference-keys. SOURCE
REVISIONS Text [0..1], revisions. SOURCE
SCHEDULE Number [1..*], schedule.SCHEDULES

Table set-logical-clause

Fields Data Type Description
INDEX Number [1], system use
LOGICAL- Number [1], system use
STRUCTURE

identifier Number (1)

Table setup-changeover

Fields Data Type Description
INDEX Number [1], system use

93



[number

Table operation-definitions

Fields Data Type Description

INDEX Number [1], system use

SHOP-DATA Number [1], system use

type Enumeration _| [0..1]

identifier Number 1]

number Text 0]

name Text ‘{0..1]

description Text [0..1]

REFERENCE-KEYS _| Text [0..1], reference-keys. SOURCE
REVISIONS Text [0..1], revisions. SOURCE
OPERATION- Number [1..*], operation-definition.OPERATION-
DEFINITION DEFINITIONS

Table operation-sheet

Fields Data Type Description

INDEX Number [1], system use

OPERATION- Number [1]. system use

SHEETS

type Enumeration | [0..1]

identifier Number {1]

number Text (

name Text [0..1]

description Text [0.1]

REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions. SOURCE
BATCH-SIZES Number [1], batch-sizes.OPERATION-SHEET
PLAN-DEFINITION _| Number [1], plan-definition.OPERATION-SHEET

Table operation-sheet-key

Fields Data Type Description
INDEX Number [1], system use
PROCESS-PLAN- Number [1], system use
MULTI-KEY

operation-sheet- Number fi]

identifier

operation-sheet- Text fi)

number

Table operation-sheets

Fields Data Type Description
INDEX Number [1], system use
PROCESS-PLAN Number [1], system use

61



MAINTENANCE- Number [1], maintenance-resource-

RESOURCE-KEYS keys. MAINTENANCE-ORDER-
DEFINITION

RESOURCES- Number [0..1], resources-required. MAINTENANCE-

REQUIRED ORDER-DEFINITION

CHILD-WORK- Number [0..1], child-work-items. MAINTENANCE-

ITEMS ORDER-DEFINITION

PRECEDENT- Number [0..1], precedent-

CONSTRAINTS constraints. MAINTENANCE-ORDER-
DEFINITION

COST-SUMMARY Number [0..1], cost-summary. MAINTENANCE-
ORDER-DEFINITION

Table maintenance-order-key

Fields Data Type Description

INDEX Number [1], system use

WORK-KEYS Number [1], system use

MAINTENANCE- Number [1], system use

ORDER-SCHEDULE

maintenance-order- Number (1)

identifier

maintenance-order- Text fy]

number

Table maintenance-order-schedule

Fields Data Type Description

INDEX Number [1], system use

MAINTENANCE- Number [1], system use

ORDERS-SECTION

MAINTENANCE- Number [1], maintenance-order.key. MAINTENANCE-
ORDER-KEY ORDER-SCHEDULE

RESOURCES- Number [1], resources-assigned. MAINTENANCE-
ASSIGNED ORDER-SCHEDULE

Table maintenance-order-status

Fields Data Type Description

INDEX Number [1], system use

MAINTENANCE- Number [1]. system use

ORDER

WORK- Number [0..1], work-scheduled-progress.
SCHEDULED- MAINTENANCE-ORDER-STATUS.
PROGRESS

WORK-ACTUAL- Number [0..1], work-actual-progress.

PROGRESS MAINTENANCE-ORDER-STATUS

Table maintenance-orders

S7



Table batch-size

Fields Data Type Description
INDEX Number [1], system use
BATCH-SIZES Number [1], system use
PART-KEY Number [1], part-key.BATCH-SIZE
quantity Number fl]
/Integer
Table batch-sizes
Fields Data Type Description
INDEX Number [1], system use
PROCESS-PLAN Number [1], system use
ROUTING-SHEET Number [1], system use
OPERATION-SHEET | Number 1], system use
MACHINE- Number [1], system use
PROGRAM
BATCH-SIZE Number [1..*], batch-size. BATCH-SIZES
Table bill-of-materials
Fields Data Type Description
INDEX Number 1], system use
BILLS-OF- Number [1], system use
MATERIALS
type Enumeration _| [0..1]
identifier Number 01]
number Text ng}
name Text [0..1]
description Text [0..1]
REFERENCE-KEYS _| Text [0..1], reference-keys. SOURCE
REVISIONS Text [0..1], revisions. SOURCE
PART-KEY Number [0..1], part-key.BILL-OF-MATERIALS
BOM-ELEMENTS Number [0..1], bom-elements.BILL-OF-MATERIALS
Table bill-of-materials-key
Fields Data Type Description
INDEX Number [1], system use
PART Number [1], system use
bill-of-materials- Number {]
identifier
bill-of-materials- Text 0]
number
Table bills-of-materials
| Fields | Data Type | Description

19



PATHS Number [0..1], paths. LAYOUT
RESOURCE- Number [0..1], resource-objects. LAYOUT
OBJECTS

PART-OBJECTS Number [0..1], part-objects. LAYOUT
Table level-definition

Fields Data Type Description

INDEX Number [1], system use

LEVELS Number [1], system use

type Enumeration _| [0..1]

identifier Number {1

number Text [tJ

name Text (0..1]

description Text [0..1]

Table level-definition-key

Fields Data Type Description

INDEX Number [1], system use
SKILL-MULTI-KEY _| Number [1], system use
level-definition- Number 0]

identifier

level-definition- Text 0)

number

Table levels

Fields Data Type Description

INDEX Number [1], system use
SKILL-DEFINITION _| Number [1], system use
minimum-level- Number (1)

number /Integer

maximum-level- Number fl]

number ‘Integer

LEVEL-DEFINITION | Number [1..*], level-definition. LEVELS

Table locations-served-keys

Fields Data Type Description

INDEX Number [1], system use

CRANE Number [1], system use

STATION-KEY Number [0..*], station-key.LOCATIONS-SERVED-
KEYS

MACHINE-KEY Number [0..*], machine-key. LOCATIONS-SERVED-
KEYS

Table logical-or

[Fields | Data Type | Description

48


REVISIONS Text [0..1], revisions. SOURCE
BATCH-SIZES Number [1], batch-sizes: MACHINE-PROGRAM
ESTIMATED- Number [0..1], estimated-durations. MACHINE-
DURATIONS PROGRAM

TARGET- Number [0..1], target-machines. MACHINE-
MACHINES PROGRAM

Table machine-program-key

Fields Data Type Description

INDEX Number [1], system use

PROCESS-PLAN- Number [1], system use

MULTI-KEY

machine-program- Number 0)

identifier

machine-program- Text 1]

number

Table machine-programs

Fields Data Type Description

INDEX Number [1], system use

PROCESS-PLAN Number [1], system use

MACHINE- Number {1..*], machine-program.MACHINE-
PROGRAM PROGRAMS

Table machine-schedule

Fields Data Type Description

INDEX Number [1], system use

MACHINES- Number [1], system use

SECTION

MACHINE-KEY Number [1], machine-key. MACHINE-SCHEDULE
WORK- Number [1], work-assignemnts.MACHINE-
ASSIGNMENTS SCHEDULE

Table machine-specificat

‘ions

Fields Data Type Description
INDEX Number [1], system use
MACHINE Number [1], system use
manufacturer Text [0..1]
model-number Text (J
serial-number Text [0..1]
machine-description Text [0..1]
horsepower Number [0..1]

/Double
controller-type Enumeration | [0..1]
efficiency-rating Text [0..1]

52



value3 Number ft]

/Double

(Fixed)
Table probability-distribution-key
Fields Data Type Description
INDEX Number [1], system use
DURATION- Number [1], system use
DISTRIBUTIONS
probability- Number 1)
distribution-identifier
probability- Text (1)
distribution-number
Table probability-distribution-set
Fields Data Type Description
INDEX Number [1], system use
PROBABILITY- Number [1], system use
DISTRIBUTIONS
type Enumeration _| [0..1]
identifier Number fy]
number Text (1)
name Text [0..1]
description Text [0..1]
REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions SOURCE
PROBABILITY- Number [1..*], probability-
DISTRIBUTION distribution. PROBABILITY-

DISTRIBUTION-SET

Table probability-distributions

Fields Data Type Description

INDEX Number [1], system use

SHOP-DATA Number [1], system use

type Enumeration | [0..1]

identifier Number 0)

number Text ng}

name Text {0..1]

description Text [0..1]

REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions. SOURCE
PROBABILITY- Number [1..*], probability-distribution-

DISTRIBUTION-SET

set.PROBABILITY-DISTRIBUTIONS

Table process-plan

81



4

Ly

igital-file-properties

/_ paper-document |

0.1

sference

INDEX

INDEX
SHOP-DATA

type

identifier

number

name

description
REFERENCE-KEYS

REVISIONS
REFERENCE

REFERENCES

type
identifier

number

name

description
REFERENCE-KEYS
REVISIONS,
DIGITAL-FILES
PAPER-DOCUMENT

AA

reference-key

0..1 0..1

Se 1.
revisions |

jen

creators

0..1

‘creator

Figure C-18: references and related tables

143


0..1

child-bom-
__ elements _

0.1

0..1

part-quantities

- bill-of-materials
bills-of-materials | INDEX
INDEX 1..*] BILLS-OF-MATERIALS
SHOP-DATA type —
type identifier
identifier number
number name
name description
description REFERENCE-KEYS [j
REFERENCE-KEYS LA REVISIONS 71
REVISIONS BOM-ELEMENTS
4 PART-KEY 1
BILL-OF-MATERIALS [; 1
1.4 -
7 | revision 0.1
1.
-Teference creators | _ creator
1." ae 0.1 I ct
be ee

Figure C-11: bills-of-materials and related tables

136


6.2 Complex query

Complex queries are used in programs or database operations. The database can be
manipulated using database interfaces such as Data Access DAO and ODBC. User can
directly manipulate the tables and records by database interfaces, for example, using a
make-table query, a cross-table query or a parameter query. The make-table query
creates a new table from all or part of the data in one or more tables. The cross-table
query is a summary query that gives the user control over how the summary data
appeared on the screen. The cross-table query generates a two-dimensional summary
matrix created from tables. The results are displayed in a row/column spreadsheet-like
format. The parameter query is a query that prompts the user for the criteria each time
the query is run.

A sample complex query is described here. The source data comes from Appendix D.
Table 4 shows the result from the following query.

The query conditions are:

(1 ) shift-schedule.name ="human-operated-machines-Fall2002"

(2) shift.number ="dms-one-of-two-shifts-five-days"

(3) Query for shift-schedulename, shiftnumber, shifi.start-time, shift.end-time and
shift.duration.

The query statement is:

SELECT  [shift-schedule].name, shift.number, — shift.[start-time], __ shift.[end-time],
shift.duration FROM ([shift-schedule] INNER JOIN shifts ON [shift-schedule].SHIFTS
= shifts.[SHIFT-SCHEDULE]) INNER JOIN shift ON shifts.SHIFT = shift.SHIFTS
WHERE ((({shift-schedule].name)="human-operated-machines-Fall2002") AND
((shift.number)="dms-one-of-two-shifts-five-days"));

Table 4: Output of the sample complex query

ishift-schedule.name lshift.number ishift.start-time |shift.end-time _|shift.duration
lhuman-operated- Idms-one-of-two-shifts-
machines-Fall2002 lfive-days {8:00:00 AM 3:59:00 PM 480

7. CONCLUSIONS AND FUTURE WORK

This report described the work being carried out at MSID in developing a database model
for the machine shop data. The objectives of the database development are to demonstrate
the feasibility of the information model, to develop a pilot database system and then to
migrate to a large database management system, and to support the integration of
manufacturing applications and simulations used in machine shops.

The information model will continue to evolve based on the experience and feedback
from others involved in this effort. The model is now being transformed into a schema


procurement-item -

INDEX
PROCUREMENT-ITEMS
type

identifier

number

name

description

PROCUREMENT-ITEM-DEFINITION

PROCUREMENT-ITEM-STATUS

m-definition

0..1

proourement-item-statu

INDEX

PROCUREMENT
priority-rating
DUE-DATES

SUPPLIERS

suppliers

PROCUREMENT-ITEMS ;

rocurement-items-

=

procurements 1. pro o
— INDEX
INDEX
SHOP-DATA heated
i i identifier
identifier number
number name
description description
REFERENCE-KEYS {4 REFERENCE-KEYS
REVISIONS 4 REVISIONS
PROCUREMENT 4 PROCUREMENT-DEFINITION
1 PROCUREMENT-STATUS

creator

0.1] |0.1 4»
revisions vision
1.
=| Teference creators
1. -key 0.4

Figure C-13: procurements and related tables

138


[definition-number —_|

Table maintenance-definitions

Fields Data Type Description

INDEX Number [1], system use

SHOP-DATA Number [1], system use

type Enumeration _| [0..1]

identifier Number (4)

number Text 1]

name Text [0..1]

description Text [0..1]

REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE

REVISIONS Text [0..1], revisions. SOURCE

MAINTENANCE- Number [1..*], maintenance-

DEFINITION definition. MAINTENANCE-DEFINITIONS

Table maintenance-order

Fields Data Type Description

INDEX Number [1], system use

TASKS Number [1], system use

type Enumeration | [0..1]

identifier Number 01]

number Text O1]

name Text [0..1]

description Text (0..1]

REFERENCE-KEYS _| Text [0..1], reference-keys.SSOURCE

REVISIONS Text [0..1], revisions. SOURCE

MAINTENANCE- Number [0..1], maintenance-order-

ORDER- definition. MAINTENANCE-ORDER

DEFINITION

MAINTENANCE- Number (0..1], maintenance-order-

ORDER-STATUS status. MAINTENANCE-ORDER

Table maintenance-order-definition

Fields Data Type Description

INDEX Number [1], system use

MAINTENANCE- Number [1], system use

ORDER.

CUSTOMERS Number [0..1], customers. MAINTENANCE-ORDER-
DEFINITION

priority-rating Enumeration | [0..1]_

DUE-DATES Number [1], due-dates MAINTENANCE-ORDER-
DEFINITION

MAINTAINED- Number [1], maintained-resource-keys.

RESOURCE-KEYS MAINTENANCE-ORDER-DEFINITION

56


1. INTRODUCTION

Standard interfaces could help reduce the costs associated with simulation model
construction and data exchange between simulation and other software applications --
and thus make simulation technology more affordable and accessible to a wide range of
potential industrial users. Currently, small machine shops do not typically use simulation
technology because of various difficulties and obstacles associated with model
development and data translation. Small shops typically do not have staff with the
appropriate technical qualifications required to develop custom simulations of their
operations or custom translators to import their data from other software applications.

NIST is working with a number of industrial partners and researchers to develop neutral
formats for machine shop data to facilitate simulation and modeling activities. A machine
shop data model, as a neutral interface format, has been under development to support
both NIST’s System Integration of Manufacturing Application (SIMA) program and the
Software Engineering Institute’s (SEI) Technology Insertion Demonstration and
Evaluation (TIDE) Program. SIMA supports NIST projects in applying information
technologies and standards-based approaches to manufacturing software integration
problems [1]. The TIDE Program is sponsored by the Department of Defense and SEI
and it is currently engaged in a number of other projects with various small
manufacturers in the Pittsburgh, Pennsylvania area. The technical work is being carried
out as a collaboration between NIST, SEI, Carnegie Mellon University, Duquesne
University, the iTAC Corporation, and the Kurt J. Lesker Company (KJLC).

KJLC is an international manufacturer and distributor of vacuum products and systems to
the research and industrial vacuum markets. KJLC manufactures complete, automatically
controlled vacuum systems with a special emphasis on custom-designed, thin-film
deposition systems for research in alloys, semiconductors, superconductors, optical and
opto-electronics. A small machine shop is contained within the KJLC manufacturing
facility. KJLC’s machine shop operation has been used to help define the requirements
for simulation modeling and data interface specification activities described in this paper.
Their facility will also be used as a pilot site for testing and evaluation of the simulation
models, neutral data interfaces, and other software developed under this TIDE project.
For more information on KJLC, see <www.lesker.com>.

The machine shop information model was developed with two goals in mind: a) support
for the integration of software applications at a pilot facility — the KJLC’s machine shop,
and b) promotion as a standard data interface for manufacturing simulators and possibly
for other software applications. The information model is continuing to evolve based on
experience and feedback from KJLC’s implementations and others involved in this effort.
The objective of the information modeling effort is to develop a standardized, computer-
interpretable representation that allows for exchange of information in a machine shop
environment. The information model, when completed, must satisfy the following needs:
to support data requirements for the entire manufacturing life cycle, to enable data
exchange between simulation and other manufacturing software for machine shops, to
provide for the construction of machine shop simulators, and to support testing and


(Fixed)

overtime-eligibility Enumeration _| [0..1]
overtime-hours- Number [0.1]
threshold /Double
(Fixed)
overtime-rate Number [0..1]
/Double
(Fixed)
annual-absentee-rate Number [0..1]
/Double
(Fixed)
ANNUAL-LEAVE- Number [0..1], annual-leave-accrual-rate.PAY-
ACCRUAL-RATE STATUS
SICK-LEAVE- Number [0..1], sick-leave-accrual-rate. PA Y-STATUS
ACCRUAL-RATE
Table phone
Fields Data Type Description
INDEX Number [1], system use
ADDRESS Number [1], system use
type Enumeration _| [0..1]
identifier Number {1]
number Text {1]
Table pick-order
Fields Data Type Description
INDEX Number [1], system use
PICK-ORDERS Number [1], system use
type Enumeration | [0..1]
identifier Number (]
number Text 1]
name Text [0..1]
description Text [0..1]
REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions SOURCE
PICK-ORDER- Number [0..1], pick-order-definition.PICK-ORDER
DEFINITION
PICK-ORDER- Number [0..1], pick-order-status. PICK-ORDER
STATUS
Table pick-order-definition
Fields Data Type Description
INDEX Number [1], system use
PICK-ORDER Number [1], system use
CUSTOMERS Number [0..1], customers.PICK-ORDER-DEFINITION

73



Integer

CRANE-ASSIGNED _ | Number [1..*], crane-assigned.CRANES-ASSIGNED
Table cranes-required
Fields Data Type Description
INDEX Number [1], system use
RESOURCES- Number [1], system use
REQUIRED
CRANE-REQUIRED _| Number [1..*], crane-required.CRANES-REQUIRED
Table cranes-section
Fields Data Type Description
INDEX Number [1], system use
RESOURCES- Number [1]. system use
SECTION
CRANE-SCHEDULE _| Number [1..*], crane-schedule.CRANES-SECTION
Table cranes-used
Fields Data Type Description
INDEX Number [1], system use
RESOURCES-USED _| Number [1], system use
number-of-cranes Number ]
/Integer
CRANE-USED. Number [1..*], crane-used.CRANES-USED
Table creator
Fields Data Type Description
INDEX Number [1], system use
CREATORS Number [1], system use
ORGANIZATION- Number [0..1], organization-key. CREATOR
KEY
DEPARTMENT-KEY | Number [0..1], department-key. CREATOR
EMPLOYEE-KEY Number [0..1], employee-key. CREATOR
Table creators
Fields Data Type Description
INDEX Number [1], system use
REVISION Number [1], system use
CREATOR Number [1..*], creator. CREATORS
Table current-setup
Fields Data Type Description
INDEX Number [1], system use
SETUP- Number [1], system use
CHANGEOVER

28



/Double

(Fixed)
unit-price Number [0..1]
/Double
(Fixed)
manufacturing-lead- Number [0..1], hh:mm:ss
time /Double
(Fixed)
special-handling- Text [0..1]
requirements
Table parts
Fields Data Type Description
INDEX Number [1], system use
SHOP-DATA Number [1], system use
type Enumeration _| [0..1]
identifier Number [1]
number Text [1]
name Text [0..1]
description Text [0..1]
REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions SOURCE
PART Number [1..*], part.PARTS
Table parts-assigned
Fields Data Type Description
INDEX Number [1], system use
RESOURCES- Number [1], system use
ASSIGNED
PART-QUANTITIES _| Number [1], part-quantities.PARTS-ASSIGNED
PART-INSTANCE- Number [0..*], plan-instance-key.PARTS-ASSIGNED
KEY
Table parts-completed
Fields Data Type Description
INDEX Number [1], system use
WORK-ACTUAL- Number [1], system use
PROGRESS :
PART-QUANTITIES _| Number [1], part-quantities.PART-COMPLETED
Table parts-required
Fields Data Type Description
INDEX Number [1], system use
RESOURCES- Number [1], system use
REQUIRED

71



Lyl

\POEZOOZ |vOLZ00Z

IS0EZOOZ |€OlZ00z

ZOEZOOZ |ZOLZ00c

LO€ZOOZ |LOLZ00z

TAT OLS

LAIHS|AINGSHOS| ~=X3NI

~LHIHS|
syrys
LOvz00z =~ ovel ~—“f065:€z J00:00:00 sKep-SAy-s skep-any[goeez skep-aalfp0ez00z|S
-921U}-SWP} -SYIYS-89JU}-SWp| -SYIYys-3914]]
Lovzo0z = (08 lo0:6¢:¢1 00:00:80 skep-ani skep-anilpoeez skep-aayleoezooz|y
-YIYyS-eu0-slup} —-y1ys-aUO-SWp| -\lys-auo!
2ovz00z =~ fOrbL———00:65:€Z [00:00:00 skep| skep-uanesleoeez, skep-uenas|Z0€ zoozle
-UBABS-SYIYS| -SYfI4S-8J4}-SUp| -SIYS-30J4)]
-80J4}-Slup|
bovzo0z = os ~———f00'6S€z 00:00:91 Skep| skep-any-sylus|zocec Replroezooz|e
~SAY-SYIYS-OM}| -OM}-JO-OM}-SLUp| -SAl-SYIUS|
-J0-ON)-SWUD| -OM}-JO-OM}}
bovz00z ~—osr l00:65:S1 00:00:80 skep| ~ skep-any-sylus|0f€z Kep|Loezooz|+
~SAY-SYIYS-OM}| -OM]-JO-2UO-SUUp| -OA-SyIUs|
-JO-2U0-StUp| -OM)-}0-9U0|
syvaug aS] voReINp| SuiR-pue| Su-LeIS aweu| Jequinu Uap ad] SL4IHS|XSQNI
-4JO-SAVG)

EUS JO B1GEL,


order-identifier Number (1)
order-number Text 0]
Table order-multi-key
Fields Data Type Description
INDEX Number [1], system use
WORK-KEYS Number [1], system use
ORDER-SCHEDULE | Number [1], system use
ORDER-KEY Number [0..1], order-key.ORDER-MULTI-KEY
ORDER-ITEM-KEY __| Number [0..1], order-item-key.ORDER-MULTI-KEY
Table order-schedule
Fields Data Type Description
INDEX Number [1], system use
ORDERS-SECTION _ | Number [1], system use
ORDER-MULTI-KEY | Number [1], order-multi.key.ORDER-SCHEDULE
RESOURCES- Number [1], resources-assigned.ORDER-SCHEDULE
ASSIGNED
Table order-status
Fields Data Type Description
INDEX Number [1], system use
ORDER Number [1], system use
WORK- Number [0..1], work-scheduled-progress.ORDER-
SCHEDULED- STATUS
PROGRESS
WORK-ACTUAL- Number [0..1], work-actual-progress..ORDER-
PROGRESS STATUS
Table orders
Fields Data Type Description
INDEX Number [1], system use
WORK Number 1], system use
type Enumeration _| [0..1]
identifier Number ng)
number Text ies
name Text [0..1]
description. Text [0..1]
REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text 0..1], revisions. SOURCE
ORDER Number [1..*], order.ORDERS
Table orders-section
| Fields | Data Type | Description

64



BOUNDARY

type Enumeration | [0..1]
identifier Number {1
number Text 1]
name Text [0..1]
Table break
Fields Data Type Description
INDEX Number [1], system use
BREAKS Number [1], system use
start-time Date/Time [1], hh:mm:ss
end-time Date/Time [0..1], hh:mm:ss
duration Number [0..1]
/Double
(Fixed)
Table breaks
Fields Data Type Description
INDEX Number [1], system use
SHIFT Number [1], system use
BREAK Number [1..*], break. BREAKS
Table calendar
Fields Data Type Description
INDEX Number [1]. system use
[Integer
CALENDARS Number [1], system use
type Enumeration | [0..1]
identifier Number {1
number Text eg]
name Text [0..1]
description Text [0..1]
effective-start-date Date/Time [U). yyyy-mm-dd
effective-end-date Date/Time (1), yyyy-mm-dd
REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions. SOURCE
SHIFT-SCHEDULE Number [1..*], shift-schedule CALENDAR
Table calendar-key
Fields Data Type Description
INDEX Number [1], system use
CALENDAR-MULTI- | Number [1], system use
KEY
calendar-identifier Number 0)
calendar-number Text f]

21



in-departments

Number

fl.*]

in-organizations Number [1..*]
in-procurements Text (1..*]

in-work Text [1..*]

Table enumeration-salary-type

Fields Data Type Description
in-employees Text [L..*]

Table enumeration-status-code

Fields Data Type Description
in-inventory Text [1..*]
in-work-actual- Text [1..*]

progress

in-work-actual- Text [1..*]

progress

in-default Text [1..*]

in-value Text [1..*]

Table enumeration-units

Fields Data Type Description
angular-units Text [1..*]
currency-units Text [1..*]
distance-units Text [1..*]
length-units Text [L..*]
mass-units Text [1..*]
speed-units Text [L..*]
time-duration-units Text [1..*]

volume Text [L..*]

Table estimated-costs

Fields Data Type Description
INDEX Number [1], system use
COST-SUMMARY Number [1], system use
COST-CATEGORIES | Number [1], cost-categories. ESTIMATED-COSTS

Table estimated-durations

Fields Data Type Description
INDEX Number [1], system use
TASK-DEFINITION _| Number [1], system use
NEW-SETUP Number [1], system use
MAINTENANCE- Number [1], system use
DEFINITION

PLAN-DEFINITION _| Number [1], system use
PLAN-STEP Number [1], system use

38



Fields Data Type Description

INDEX Number [1]. system use

WORK Number [1], system use

type Enumeration | [0..1]

identifier Number ag}

number Text (]

name Text [0..1]

description Text [0..1]

REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE

REVISIONS Text [0..1], revisions. SOURCE

MAINTENANCE- Number [1..*], maintenance-order. MAINTENANCE-

ORDER ORDERS

Table maintenance-orders-section

Fields Data Type Description

INDEX Number [1], system use

WORK-SECTION Number [1], system use

MAINTENANCE- Number [1..*], maintenance-order-

ORDER-SCHEDULE schedule. MAINTENANCE-ORDERS-
SECTION

Table material-instance

Fields Data Type Description

INDEX Number [1], system use

MATERIAL- Number [1], system use

INSTANCES

type Enumeration _| [0..1]

identifier Number 01]

number Text 0)

name Text [0..1]

current-location Text [0..1]

STATUS-CODE Number [0..1], status-code. MATERIAL-INSTANCE

WORK- Number [0..1], work-assignments.MATERIAL-

ASSIGNMENTS INSTANCE

DIMENSIONS Number [0..1], dimensions. MATERIAL-INSTANCE

Table material-instances

Fields Data Type Description

INDEX Number [1], system use

MATERIALS- Number [1], system use

INVENTORY-ITEM

MATERIAL- Number [1..*], material-instance. MATERIAL-

INSTANCE INSTANCES

Table materials-inventory

58



Fields Data Type

Description

INDEX Number

[1], system use

ORGANIZATIONAL- | Number
CONTACT

[1]. system use

contact-identifier Number

(1)

contact-number Text

U1]

Table contacts

Fields Data Type Description

INDEX Number [1], system use
ORGANIZATION Number [1], system use
DEPARTMENT Number [1], system use
CONTACT Number [1..*], contact. CONTACTS
Table cost-categories

Fields Data Type Description

INDEX Number [1], system use

ESTIMATED-COSTS | Number

[1], system use

PLANNED-COSTS Number

[1], system use

ACTUAL-COSTS Number

[1], system use

materials-cost Number [0..1]

/Double
labor-cost Number [0..1]

/Double
equipment-usage-cost | Number [0..1]

/Double
total-cost Number [0..1]

/Double
Table cost-summary
Fields Data Type Description
INDEX Number [1], system use
ORDER-ITEM-DEFINITION | Number [1], system use
ORDER-DEFINITION Number [1], system use
WORK-SCHEDULED- Number [1] system use
PROGRESS
WORK-ACTUAL- Number [1], system use
PROGRESS
JOB-DEFINITION Number [1], system use
TASK-DEFINITION Number [1], system use
MAINTENANCE-ORDER- | Number [1], system use
DEFINITION
PICK-ORDER-DEFINITION | Number [1], system use
PICK-ORDER-ITEM- Number [1], system use
DEFINITION

24



INDEX
type
identifier

number

name

description
REFERENCE-KEYS
REVISIONS
ORGANIZATIONS
CALENDARS

RESOURCES
SKILL-DEFINITIONS
SETUP-DEFINITIONS
OPERATION-DEFINITIONS
MAINTENANCE-DEFINITIONS
LAYOUT

PARTS
BILLS-OF-MATERIALS
INVENTORY
PROCUREMENTS
PROCESS-PLANS

WORK

SCHEDULES

TIME-SHEETS

REFERENCES
PROBABILITY-DISTRIBUTIONS
UNITS-OF-MEASUREMENT

0.1 | INDEX

SHOP-DATA

type

identifier

number

name

description
REFERENCE-KEYS
angular-units
currency-units
distance-units
length-units
mass-units
speed-units
time-duration-units
volume-units

0.1

ae
reference: Keys

Figure C-20: units-of-measurements and related tables

145


PARTS-REQUIRED | Number [0..1], parts-required. RESOURCES-
REQUIRED

Table resources-section

Fields Data Type Description

INDEX Number [1], system use

SCHEDULE Number [1], system use

STATIONS- Number [0..1], stations-section.RESOURCES-

SECTION SECTION

MACHINES- Number [0..1], machines-section.RESOURCES-

SECTION SECTION

CRANES-SECTION _| Number [0..1], cranes-section.RESOURCES-SECTION

EMPLOYEES- Number [0..1], employees-section.RESOURCES-

SECTION SECTION

Table resources-used

Fields Data Type Description

INDEX Number [1], system use

WORK-ACTUAL- Number [1], system use

PROGRESS

STATIONS-USED Number [0..1], stations-used. RESOURCES-USED

MACHINES-USED Number [0..1], machines-used.RESOURCES-USED

EMPLOYEES-USED _| Number [0..1], employees-used.RESOURCES-USED

CRANES-USED Number [0..1], cranes-used.RESOURCES-USED

PARTS-USED Number [0..*], parts-used.RESOURCES-USED

Table revision

Fields Data Type Description

INDEX Number [1], system use

REVISIONS Number [1], system use

type Enumeration _| [0..1]

identifier Number {1]

number Text 0]

name Text [0..1]

date Date/Time [1], yyyy-mm-dd

description Text (0..1]

comment Text [0..1]

CREATORS Number [0..1], creators REVISION

Table revisions

Fields Data Type Description

INDEX Number [1], system use

SOURCE Text [1]. system use

REVISION Number [1..*], revision REVISIONS

91



color-code Text [0..1]

surface-finish-code Text [0..1]

function-code Text [0..1]

weight-code Text [0..1]

process-code Text (0..1]

cost-code Text | [0..1]

Table holiday

Fields Data Type Description

INDEX Number [1], system use

HOLIDAYS Number [1], system use

identifier Number 1]

number Text ]

name Text [0..1]

description Text [0..1]

date Date/Time [1]. yyyy-mm-dd

Table holidays

Fields Data Type Description

INDEX Number [1], system use
SHIFT-SCHEDULE Number [1], system use

HOLIDAY Number [1..*], holiday. HOLIDAYS
Table inventory

Fields Data Type Description

INDEX Number [1], system use

SHOP-DATA Number (1], system use

type Enumeration _| [0..1]

identifier Number fy]

number Text (1)

name Text [0..1]

description Text ‘{0..1]

REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions.SOURCE
TOOL-INVENTORY _| Number ‘[0..1], tool-inventory. INVENTORY
FIXTURE- Number [0..1], fixture-inventory INVENTORY
INVENTORY

PART-INVENTORY _| Number [0..1], part-inventory. INVENTORY
MATERIALS- Number [0..1], materials-inventory. INVENTORY
INVENTORY

Table job

Fields Data Type Description

INDEX Number [1], system use

JOBS Number [1], system use

45



Fields Data Type Description

INDEX Number [1], system use
TOOL-INVENTORY- | Number [1], system use

ITEM

TOOLSET- Number [1], system use
INVENTORY-ITEM

FIXTURE- Number [1], system use
INVENTORY-ITEM

FIXTURESET- Number [1], system use
INVENTORY-ITEM

PART-INVENTORY- | Number [1], system use

ITEM

MATERIALS- Number [1], system use
INVENTORY-ITEM

PROCUREMENT- Number [1..*], procurement-multi-
MULTI-KEY key.PROCUREMENT-MULTI-KEYS
Table procurement-status

Fields Data Type Description

INDEX Number {1], system use
PROCUREMENT Number [1], system use
STATUS-CODE Number [1], status-code. PROCUREMENT-STATUS
EVENTS Number [0..1], events. PROCUREMENT-STATUS
Table procurements

Fields Data Type Description

INDEX Number [1], system use

SHOP-DATA Number [1]. system use

type Enumeration _| [0..1]

identifier Number [4]

number Text {1]

name Text {0..1]

description Text | [0..1]

REFERENCE-KEYS _| Text 0..1], reference-keys. SOURCE
REVISIONS Text [0..1], revisions.SOURCE
PROCUREMENT Number [1..*], procurement.PROCUREMENTS
Table reference

Fields Data Type Description

INDEX Number [1], system use

REFERENCES Number [1], system use

type Enumeration | [0..1]

identifier Number esl

number Text (1)

name Text [0..1]

86



Table order-item-definition

Fields Data Type Description

INDEX Number [1], system use

ORDER-ITEM Number [1], system use

priority-rating Enumeration | [0..1]

DUE-DATES Number [1], due-dates.ORDER-ITEM-DEFINITION

PART-QUANTITIES | Number [1], part-quantities.ORDER-ITEM-
DEFINITION

CHILD-WORK- Number [0..1], child-work-items.ORDER-ITEM-

ITEMS DEFINITION

PRECEDENT- Number [0..1], precedent-constraints.ORDER-ITEM-

CONSTRAINTS DEFINITION

RESOURCES- Number [0..1], resources-required.ORDER-ITEM-

REQUIRED DEFINITION

COST-SUMMARY Number [0..1], cost-summary.ORDER-ITEM-
DEFINITION

Table order-item-key

Fields Data Type Description

INDEX Number [1], system use

ORDER-MULTI-KEY | Number [1], system use

order-item-identifier Number ion

order-item-number Text eal

Table order-item-status

Fields Data Type Description

INDEX Number [1], system use

ORDER-ITEM Number [1], system use

WORK- Number [0..1], work-scheduled-progress.ORDER-

SCHEDULED- ITEM-STATUS

PROGRESS

WORK-ACTUAL- Number [0..1], work-actual-progress.ORDER-ITEM-

PROGRESS, STATUS

Table order-items

Fields Data Type Description

INDEX Number [1], system use

ORDER- Number [1]. system use

DEFINITION

ORDER-ITEM Number [L..*], order-item.ORDER-ITEMS

Table order-key

Fields Data Type Description

INDEX Number [1], system use

ORDER-MULTI-KEY | Number [1], system use

63



FRAME

description Text [0..1]

REFERENCE-KEYS _| Text [0..1], reference-keys. SOURCE
REVISIONS Text [0..1], revisions.SOURCE
DIGITAL-FILES Number [0..1], digital-files. REFERENCE
PAPAER- Number [0..1], paper-document.REFERENCE
DOCUMENT

Table reference-frame

Fields Data Type Description

INDEX Number [1], system use

REFERENCE- Number [1]. system use

FRAMES

type Enumeration _| [0..1]

identifier Number {1]

number Text ti

name Text [0..1]

FROM-REFERENCE- | Number [1], from-reference-frame.REFERENCE-

FRAME

Table reference-frame-key
Fields Data Type Description
INDEX Number [1], system use
FROM-REFERENCE- | Number [1], system use
FRAME
reference-frame- Number fi]
identifier
reference-frame- Text fy)
number
Table reference-frames
Fields Data Type Description
INDEX Number [1], system use
LAYOUT Number [1], system use
REFERENCE- Number [1..*], reference-frame.REFERENCE-
FRAME FRAMES
Table reference-key
Fields Data Type Description
INDEX Number [1], system use
REFERENCE-KEYS _| Number [1], system use
reference-identifier Number fi
reference-number Text OJ
Table reference-keys

[Fields [Data Type _| Description

87



DEFINITION

TOOL-ORDER- Number [1], system use

DEFINITION

WORK- Number [1], system use

ASSIGNMENT

PROCUREMENT- Number [1], system use

ITEM-DEFINITION

ORDER-MULTI-KEY | Number [0..*], order-multi-key. WORK-KEYS

JOB-KEY Number [0..*], job-key. WORK-KEYS

TASK-KEY Number [0..*], task-key. WORK-KEYS

PROCUREMENT- Number [0..*], procurement-multi-key. WORK-KEYS

MULTI-KEY

PICK-ORDER-KEY __| Number [0..*], pick-order-key. WORK-KEYS

TOOL-ORDER-KEY _| Number [0..*], tool-orderi-key.WORK-KEYS

MAINTENANCE- Number [0..*], maintenance-order-key. WORK-KEYS

ORDER-KEY

Table work-scheduled-progress

Fields Data Type Description

INDEX Number [1], system use

ORDER-STATUS Number [1], system use

ORDER-ITEM- Number [1], system use

STATUS

JOB-STATUS Number [1], system use

TASK-STATUS Number [1], system use

MAINTENANCE- Number [1], system use

ORDER-STATUS

PICK-ORDER- Number [1], system use

STATUS

PICK-ORDER-ITEM- | Number [1], system use

STATUS

TOOL-ORDER- Number [1], system use

STATUS

material-availability- | Date/Time [0..1], yyyy-mm-dd

date

STATUS-CODE Number [0..1], status-code. WORK-SCHEDULED-
PROGRESS

RESOURCES- Number [0..1], resources-assigned. WORK-

ASSIGNED SCHEDULED-PROGRESS

EVENTS Number [0..1], events. WORK-SCHEDULED-
PROGRESS

COST-SUMMARY Number [0..1], cost-summary. WORK-SCHEDULED-

PROGRESS

122



in-work-scheduled-progress

PENDING, ACCEPTED,
RELEASED, IN-
PROGRESS, COMPLETE,
CANCELLED

enumeration-units

angular-units

DEGREE, RADIAN

currency-units

DOLLAR, EURO, YEN

distance-units

MILE, FOOT, INCH,
KILOMETER, METER,
CENTIMETER

length-units

MILE, FOOT, INCH,
KILOMETER, METER,
CENTIMETER

mass-units

TON, POUND, OUNCE,
KILOGRAM, GRAM

speed-units

MILE/HOUR,
KILOMETER/HOUR

time-duration-units

DAY, HOUR, MINUTE,
SECOND

volume-units

OUNCE, MILLILITER,
LITER, GALLON,
CUBICFOOT,
CUBICMETER,
CUBICINCH,
CUBICCENTIMETER

type-shop-data,
type-calendars,
type-organizations,

enumeration

TBD*

125



PROCUREMENT-
ITEM-DEFINITION

Number

[1], system use

release-date Date/Time [0..1], yyyy-mm-dd
requested-earliest- Date/Time [0..1], yyyy-mm-dd
ship-date

requested-latest-ship- | Date/Time [0.1], yyyy-mm-dd

date

Table duration-distributions

Fields Data Type Description

INDEX Number [1], system use

SETUP-DURATION __| Number [1], system use
UNIT-OPERATION- | Number [1], system use

DURATION

TOTAL- Number [1], system use

OPERATIONS-

DURATION

MEAN-TIME- Number [1], system use

BETWEEN-

FAILURES

MEAN-TIME-TO- Number [1], system use

REPAIR

PROBABILITY- Number [0..*], probability-distribution-
DISTRIBUTION- key. DURATION-DISTRIBUTIONS
KEY

PROBABILITY- Number [0..*], probability-distribution. DURATION-
DISTRIBUTION DISTRIBUTIONS

Table effective-shift

Fields Data Type Description

INDEX Number [1], system use
EFFECTIVE-SHIFTS | Number [1], system use

effective-start-date Date/Time [1], yyyy-mm-dd

effective-end-date Date/Time [1]. yyyy-mm-dd
CALENDAR-MULTI- | Number [1], calendar-multi-key. EFFECTIVE-SHIFT
KEY

Table effective-shifts

Fields Data Type Description

INDEX Number [1], system use

EMPLOYEE- Number [1], system use

AVAILABILITY

EFFECTIVE-SHIFT __| Number [1..*], effective-shift.EFFECTIVE-SHIFTS

Table email

32


e Parts provides elements for part specifications, group technology codes, customers,
suppliers, as well as links to bills of materials, process plans, drawings, part models and
other references.

¢ Bills-of-materials cross-references the parts and quantities required in a hierarchical
bill-of-materials structure.

e Inventory identifies the instances and locations for parts, materials, tools, and fixtures
inventory.

e Procurements identifies the external purchases that have been created to satisfy the part
inventory and manufacturing requirements.

e Process-plans specifies a set of process plans that are associated with production and
support activities for a particular part or parts. A process plan has routing sheets and
operation sheets that correspond to the job and task level in the work hierarchy.

e Work is used to specify a collection of a hierarchy of production orders, jobs, and tasks.
It is also used to specify a collection of internal support orders for maintenance activities,
inventory picking and tool preparation.

e Schedules lists planned assignment or mapping of work to resources and resources to
work.

e Revisions specifies information about a set of revisions of the subjects. Information
included in the element are each revision’s identification, description, date, creators, etc.
 Time-sheets provides a list of individual time sheet elements. A time-sheet is used to
log the hours that an employee has worked, the time an employee has taken off from
work, and accrual of leave hours.

e Probability-distributions specifies distributions that are used to vary processing times,
breakdown and repair times, and availability of resources, etc.

e References describes the information about reference materials that support or further
define the data elements contained within the shop data structure.

© Units-of-measurement describes various measurement units used in the file, for
example, the measurement may be for distance, speed, mass, time duration, or currency.

4. OBJECTIVES OF THE DATABASE IMPLEMENTATION

The objectives of the database implementation effort include:
e to demonstrate the feasibility of the information model,
e to develop a pilot database system and then to migrate to a large database
management system, and
e to support the integration of manufacturing applications and simulations used in
machine shops.

A database is a collection of related information. It provides a structured means for
storing and querying data. Most existing databases are relational databases. A database
management system (DBMS), such as Microsoft Access, and Oracle [9], provides
software tools for users to organize data in a flexible manner. The database, described in
this report, has been developed using Access, a personal computer (PC) based database
product. Access comes with the Microsoft Office suite of products and contains many of
the features of a relational database management system. Access is more economic to


INDEX
SHOP-DATA

type

identifier

number

name

description
REFERENCE-KEYS
REVISIONS
ORDERS

JOBS

TASKS
MAINTENANCE-ORDERS
PICK-ORDERS
TOOL-ORDERS

EEIFERA-=

0..1
reference _

Ly

0..1 *

revisions - revisi
alll

1.

0..1

creators 1 creator

Figure C-15: works and related tables

140


| ASSIGNED

Table job-status

Data Type

Fields Description

INDEX Number [1], system use

JOB Number 1], system use

WORK- Number [0..1], work-scheduled-progress.JOB-STATUS
SCHEDULED-

PROGRESS

WORK-ACTUAL- Number [0..1], work-actual-progress.JOB-STATUS
PROGRESS

Table jobs

Fields Data Type Description

INDEX Number [1], system use

WORK Number [1], system use

type Enumeration _| [0..1]

identifier Number {J

number Text [1]

name Text [0..1]

description Text 0.1]

REFERENCE-KEYS _| Text [0.1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions. SOURCE

JOB Number [1..*], job. JOBS

Table jobs-section

Fields Data Type Description

INDEX Number [1], system use

WORK-SECTION Number [1], system use

JOB-SCHEDULE Number [1..*], job-schedule.JOBS-SECTION
Table layout

Fields Data Type Description

INDEX Number [1], system use

SHOP-DATA Number [1], system use

type Enumeration | [0..1]

identifier Number {1]

number Text (1)

name Text [0..1]

description Text ‘[0..1]

REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions. SOURCE
REFERENCE- Number [0..1], reference-frames.LA YOUT
FRAMES

AREAS Number [0..1], areas LAYOUT

AT


¢ Organizations is used to maintain the organizational structure, contacts and address
information for the manufacturing organization and its customers and suppliers.
© Calendars identifies the shift schedules that are in effect for a period of time, breaks
and holidays.
e Resources describes all the resources that may be assigned to tasks in the shop. The
resource types available in the machine shop environment include: stations and machines,
cranes, employees, and tool and fixture catalog items.
e Skill-definitions lists the skills that an employee may possess and the levels of
proficiency associated with those skills.
 Setup-definitions typically specifies tool or fixture setups on a machine. Tool setups are
typically the tools that are required in the tool magazine. Fixture setups are work holding
devices mounted on the machine. Setups may also apply to cranes or stations.
© Operation-definitions defines the operations that may be performed at a particular
station or group of stations in the shop.
[oa —Prganizations
at
0.4
=

shop-data
a ee
|-identifier{1] roa
ramet 57 pul oF materials]
SREEERENSE REYS\O..1]
|-REVISIONS[O..1]
|-UNITS-OF-MESUREMENT(O..1] 0.14
tort process-plans
a

0.7
0.7

probability-distributions

0.4

0.4

Figure 2: The top structure of the machine shop data model

© Maintenance-definitions defines preventive or corrective maintenance to be done on
machines or other maintained resources.

Layout defines the location of reference points within the shop, area boundaries, paths,
resource, and part objects.


identifier

fixture-definition- Text {1]

number

Table fixture-instance

Fields Data Type Description
INDEX Number [1], system use
FIXTURE- Number [1], system use
INSTANCES

type Enumeration _| [0..1]

identifier Number 1]

number Text {J

name Text [0..1]
current-location Text (0..1]
STATUS-CODE Number [0..1], status-code.FIXTURE-INSTANCE
WORK- Number [0..1], work-assignments. FIXTURE-
ASSIGNMENTS INSTANCE
Table fixture-instance-key

Fields Data Type Description
INDEX Number [1], system use
FIXTURE- Number [1]. system use
INSTANCE-KEYS

fixture-instance- Number [1]

identifier

fixture-instance- Text {1]

number

Table fixture-instance-keys

Fields Data Type Description

INDEX Number [1], system use

FIXTURESET- Number [1], system use

INSTANCE

FIXTURE- Number [1..*], fixture-instance-key.FIXTURE-
INSTANCE-KEY INSTANCE-KEYS

Table fixture-instances

Fields Data Type Description

INDEX Number [1], system use

FXITURE- Number [1], system use
INVENTORY-ITEM.

FXITURE- Number [1..*], fixture-instance.FXITURE-
INSTANCE INSTANCES

Table fixture-inventory

41



WORK- Number [1..*], work-assignment.WORK-
ASSIGNMENT ASSIGNMENTS
Table work-item

Fields Data Type Description
INDEX Number [1], system use
PRECEDENT- Number [1], system use
CONSTRAINT

WORK-KEYS Number [1], work-keys. WORK-ITEM
Table work-item-group

Fields Data Type Description
INDEX Number [1], system use
CHILD-WORK- Number [1], system use
ITEMS

type Enumeration _| [0..1]

identifier Number [y

number Text U1]

name Text [0..1]
logical-relationship Enumeration | [1]
WORK-KEYS Number [0..1], work-keys. WORK-ITEM-GROUP
WORK-ITEM- Number [0..1], work-item-group-key. WORK-ITEM-
GROUP-KEY GROUP

Table work-item-group-key

Fields Data Type Description
INDEX Number [1]. system use
WORK-ITEM- Number [1], system use
GROUP

work-item-group- Number {i

identifier

work-item-group- Text {i

number

Table work-keys

Fields Data Type Description
INDEX Number {1], system use
CHILD-WORK- Number [1], system use
ITEMS

WORK-ITEM- Number [1], system use
GROUP

WORK-ITEM. Number [1], system use
PRECEDENT- Number [1], system use
WORK-ITEM

PICK-ORDER- Number [1], system use

121



INDEX Number [1], system use

SHOP-DATA Number [1], system use

type Enumeration _| [0..1]

identifier Number (1)

number Text (1)

name Text [0..1]

description Text [0..1]

REFERENCE-KEYS _| Text [0..1], reference-keys. SOURCE
REVISIONS Text [0..1], revisions. SOURCE
BILL-OF- Number [1..*], bill-of-materials. BILLS-OF-
MATERIALS MATERIALS

Table bom-element

Fields Data Type Description

INDEX Number [1], system use
BOM-ELEMENTS Number [1], system use

type Enumeration | [0..1]

identifier Number 1).

number Text on

name Text [0..1]

description Text [0..1]

PART-QUANTITIES || Number [0..1], part-quantities. BOM-ELEMENT
CHILD-BOM- Number [0..1], child-bom-elements.BOM-ELEMENT
ELEMENTS

Table bom-element-key

Fields Data Type Description

INDEX Number [1], system use

CHILD-BOM- Number [1]. system use

ELEMENTS

bom-element-identifier | Number ty

bom-element-number_| Text U1]

Table bom-elements

Fields Data Type Description

INDEX Number [1], system use

BILL-OF-MATERIALS Number [1], system use

BOM-ELEMENT Number [1..*], bom-element.BOM-ELEMENTS
Table boundary-element

Fields Data Type Description

INDEX Number [1], system use

AREA-BOUNDARY _| Number [1], system use

PATH-ROUTE Number [1], system use

OBJECT- Number [1], system use

20


using an XML schema language. There are also plans to expand the model to include
assembly line, supply chain, and other domain areas. Thus the database model will be
revised accordingly.

The information model will be proposed as a candidate standard to be considered by a
formal standards body. There are also experimental development activities underway to
test the viability of the model with real world applications. A generic manufacturing
simulator is being developed at NIST for the TIDE Program [4]. The model is also being
used in the TIDE Program to integrate a manufacturing execution system with a real-time
adaptive scheduler, and the manufacturing simulator. Future work also includes the
development of the translators that exchange data among XML, the database, and certain
proprietary shop data based on the machine shop information model.

ACKNOWLEDGEMENTS AND DISCLAIMER

This project is funded by NIST's SIMA Program and the SEI TIDE Program. SIMA
supports NIST projects applying information technologies and standards-based
approaches to manufacturing software integration problems. No approval or endorsement
of any commercial product by the National Institute of Standards and Technology is
intended or implied. The work described was funded by the United States Government
and is not subject to copyright.

REFERENCES

[1] Carlisle, M., and J. Fowler. 2001. Systems Integration for Manufacturing
Applications Biennial Report. Fiscal Years, NISTIR 6721. Gaithersburg, MD. National
Institute of Standards and Technology.

[2] Lee, Y. T. 1999. Information Modeling: From Design To Implementation.

Proceedings of the Second World Manufacturing Congress, ed. S. Nahavandi and M.
Saadat, 315-321. Canada/Switzerland. International Computer Science Conventions.

[3] http://www.microsoft.com/office/access/default.asp

[4] McLean, C., A. Jones, Y. T. Lee, and F. Riddick. 2002. An Architecture for a Generic
Data-Driven Machine Shop Simulator. Proceedings of the Winter Simulation Conference.
eds. E. Yucesan, C. Chen, J. L. Snowdon, and J. M. Charnes, 1108-1116. Piscataway, NJ.
Institute of Electrical and Electronics Engineers.

[5] http://www.w3.org/XML/ [accessed December 11, 2003].

[6] van der Vlist, E., 2002. XML Schema. Sebastopol, CA. O’Reilly & Associates, Inc.

[7] Lee, Y. T., C. McLean, and G. Shao, 2003. A Neutral Information Model For
Simulating Machine Shop Operations. Proceedings of the Winter Simulation Conference.


evaluation of machine shops’ manufacturing software. Data structures contained within
the information model include organizations, calendars, resources, parts, process plans,
schedules, and work orders for machine shops.

An information model is a representation of concepts, relationships, constraints, rules,
and operations to specify data semantics for a chosen domain of discourse. The advantage
of using an information model is that it can provide shareable, stable, and organized
structure of information requirements for the domain context. An information model
serves as a medium for transferring data among computer systems that have some degree
of compliance with this information model. For proprietary data, implementation-specific
arrangements can be made when transferring those data [2].

In general, the contents of an information model include a scope, a set of information
requirements, and a specification. Information requirements serve as the foundation of
the specification of the information model. A thorough requirements analysis is a
necessity. The initial goal for the machine shop information model is to support data
transfer needed for KJLC’s machine shop operations. This information model, ultimately,
will be presented as a candidate for a standard data interface to be used by other machine
shops. Thus, the completeness and correctness of the information requirements and a
consensus on the data requirements from the industry are also important requisites.

The specification of the information model defines elements, attributes, constraints, and
relationships between elements in the domain context. The specification should be laid
out using some formal information modeling languages. An information modeling
language provides a formal syntax that allows users to unambiguously capture data
semantics and constraints. Three types of methods that implement information models
and currently used by the manufacturing community are:

e Data transfer via a working form, which is a structured, in-memory representation
of data. The method uses a mechanism that accesses and changes data sequentially
without actually moving the data around. All shared data are stored in memory.

e Data transfer via an exchange file, which is a file with a predefined structure or
format. This method requires a neutral file format for storing the data. The
application systems read from and write into files.

e Data transfer using a database management system. This method uses a database
management system where information is mapped onto and retrieved from
databases.

These implementation methods can be accomplished through translators that are
developed using programming languages and database management systems. The
selection of an implementation method is heavily dependent on the target environment
where the application system resides. While the relational database is generally desirable
for data transfer, the traditional file-oriented systems are being used still by many
manufacturing applications.

A machine shop database implementation, which generates relational database tables
from the information model using Microsoft Access [3], has been developed at the


INDEX
SHOP-DATA

type

identifier

number

name

description
REFERENCE-KEYS
REVISIONS
MAINTENANCE-DEFINITION

MAINTENANCE-DEFINITIONS
type

identifier

number

name

description
REFERENCE-KEYS
REVISIONS
PLAN-DEFINITION
ESTIMATED-DURATIONS
COST-SUMMARY
MAINTAINED-RESOURCE-KEYS

PEEEPP

Figure C-8: maintenance-definitions and related tables

133



TOOL-INVENTORY-
ITEM

Number

[1], system use

tool-definition- Number 0)

identifier

tool-definition-number | Text fy

Table tool-instance

Fields Data Type Description
INDEX Number [1], system use
TOOL-INSTANCES __| Number [1], system use
type Enumeration | [0..1]

identifier Number (1)

number Text 0]

name Text [0..1]
current-location Text [0..1]
STATUS-CODE Number [0..1], status-code. TOOL-INS TANCE
WORK- Number [0..1], work-assignments. TOOL-INSTANCE
ASSIGNMENTS

Table tool-instance-key

Fields Data Type Description
INDEX Number [1], system use
TOOL-INSTANCE- Number [1], system use
KEYS

tool-instance-identifier_| Number [1]
tool-instance-number__| Text [1]

Table tool-instance-keys

Fields Data Type Description

INDEX Number [1], system use

TOOLSET- Number [1], system use

INSTANCE

TOOL-INSTANCE- Number [1..*], tool-instance-key. TOOL-INSTANCE-
KEY KEYS

Table tool-instances

Fields Data Type Description

INDEX Number [1], system use

TOOL-INVENTORY- | Number [1], system use

ITEM

TOOL-INSTANCE Number [1..*], tool-instance. TOOL-INSTANCES
Table tool-inventory

Fields Data Type Description

INDEX Number [1], system use

111



[BOUNDARY |

Table part-objects

Fields Data Type Description

INDEX Number [1], system use

LAYOUT Number [1], system use

PART-OBJECT Number | [1..*], part-object. PART-OBJECTS
Table part-quantities

Fields Data Type Description

INDEX Number [1], system use

ORDER-ITEM- Number [1], system use

DEFINITION

PARTS-REQUIRED __| Number [1], system use

PARTS- Number [1], system use

COMPLETED

PARTS-ASSIGNED __| Number [1], system use

PARTS-USED Number [1], system use
JOB-DEFINITION Number [1], system use
TASK-DEFINITION _| Number [1], system use
PICK-ORDER-ITEM- | Number [1], system use

DEFINITION

BOM-ELEMENT Number [1], system use
PART-QUANTITY Number [1..*], part-quantity. PART-QUANTITIES
Table part-quantity

Fields Data Type Description

INDEX Number [1], system use
PART-QUANTITIES _| Number [1], system use

quantity Number 1]

PART-KEY Number [1], part-key.PART-QUANTITY

Table part-specifications

Fields Data Type Description
INDEX Number [1], system use
PART Number [1], system use
length Number [0..1]

/Double
width Number [0..1]

/Double
weight Number [0..1]

/Double
unit-of-issue Number [0.1]

/Integer
unit-cost Number [0.1]

70


TOOL-ORDER- Number [1]. system use
DEFINITION
MAINTENANCE- Number [1], system use
DEFINITION
ESTIMATED-COSTS Number [0..1], estimated-costs.COST-
SUMMARY
PLANNED-COSTS Number [0..1], planned-costs.COST-
SUMMARY

ACTUAL-COSTS Number [0..1], actual-costs.COST-SUMMARY
Table crane
Fields Data Type Description
INDEX Number [1], system use
CRANES Number [1], system use
type Enumeration _| [0..1]
identifier Number (1)
number Text (y
name Text [0..1]
description Text [0..1]
REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions. SOURCE
hourly-rate Number [0..1]

/Double

(Fixed)
LOCATIONS- Number [0..1], locations-served-keys. CRANE
SERVED-KEYS
RELIABILITY- Number [0.1], reliability-statistics CRANE
STATISTICS
CRANE- Number 0..1], crane-specification. CRANE
SPECIFICATION
CRANE-STATUS Number [0..1], crane-status. CRANE
Table crane-assigned
Fields Data Type Description
INDEX Number [1], system use
CRANES-ASSIGNED | Number [1], system use
CRANE-KEY Number [1], crane-key.CRANE-ASSIGNED
SETUP- Number [0..1], setup-definition-keys.CRANE-
DEFINITION-KEYS ASSIGNED
EVENTS Number [0..1], events. CRANE-ASSIGNED
Table crane-key
Fields Data Type Description
INDEX Number [1], system use
CRANE-KEYS Number [1], system use

25



0,.* | INDEX

type
identifier
number
name
description
value1
value2
value3

PROBABILITY-DISTRIBUTION-SET

INDEX

SHOP-DATA

type

identifier

number

name

description

REFERENCE-KEYS

REVISIONS .
PROBABILITY-DISTRIBUTION-SET

ESESES)

INDEX
PROBABILITY-DISTRIBUTIONS

type
identifier
number

name

description
REFERENCE-KEYS
REVISIONS

PROBABILITY-DISTRIBUTION

0..1

| reference

_reference-key

creators

0..1

creator

Figure C-19: probability-distributions and related tables

144



Shop-data is the top level of the data model. The design view of the shop-data table is
_ presented in Table 1.

Table 1: Data structure of shop-data

Field Data Type Description
INDEX ‘Number [I], system use

type Enumeration (0..1]

identifier ‘Number O

number Text iol

name Text (0.1)

description Text (0.1)

REFERENCE-KEYS Text (0.1), reference-keys SOURCE
REVISIONS Text (0..1], revisions SOURCE
ORGANIZATIONS Number [0.1], organizations. SHOP-DATA
‘CALENDARS Number [0.1], calendars. SHOP-DATA
RESOURCES ‘Number [0..1], resources. SHOP-DATA
‘SKILL-DEFINITIONS Number [0..1), skill-definitions SHOP-DATA
OPERATION-DEFINITIONS, ‘Number {0..1], setup-definitions. SHOP-DATA
‘SETUP-DEFINITIONS ‘Number [0..1], setup-definitions SHOP-DATA
MAINTENANCE-DEFINITIONS ‘Number [0..1], maintenance-definitions. SHOP-DATA
LAYOUT, Number [0..1], layout.SHOP-DATA

PARTS. Number [0..1],parts. SHOP-DATA
BILLS-OF-MATERIALS Number {0..1], bills-of-materials SHOP-DATA
INVENTORY Number (0..1], inventory. SHOP-DATA,
PROCUREMENTS Number (0..1], procurements. SHOP-DATA
PROCESS-PLANS Number {0..1), process-plans. SHOP-DATA

WORK Number {0..1], work. SHOP-DATA

SCHEDULES ‘Number {0..1], schedules.SHOP-DATA
TIME-SHEETS ‘Number [0..1), time-sheets. SHOP-DATA.
REFERENCES Number [0.1], references. SHOP-DATA.
PROBABILITY-DISTRIBUTIONS ‘Number (0..1], probability-distributions. SHOP-DATA
UNITS-OF-MEASUREMENT Number (0.1), units-of- measurement. SHOP-DATA

Shop-data is defined by an identifier, a number, and optionally a type, a name, a
description, references, revisions, units of measurement, organizations, calendars,
resources, skill definitions, setup definitions, operation definitions, maintenance
definitions, a layout, parts, bills of materials, an inventory, procurements, process plans,
work, schedules, time sheets, and probability distributions. The field INDEX in Table 1
is a special element used by the system, its instance should be an integer, and there will
be exactly one instance for a shop-data instance. Type is an attribute of shop-data and is
an enumeration to describe types about shop-data. Name is another attribute, and there
may be zero or one instance for a shop-data instance. ORGANIZATIONS is an attribute
served as a pointer pointing to the table of organizations. A shop-data instance may have
zero or one instance of ORGANIZATIONS. ORGANIZATIONS in the shop-data table
points to the field of shop-data in the organizations table. REFERENCE-KEYS points to
the field of source in the reference-keys table. All referenced tables that are identified in
Table 1 can be found in Appendix A.

5.4 Relationships between tables

The machine shop database model can be represented in a tree shape expression. There
are different levels of relationships among elements. The first level carries the
relationship between shop-data element and the major elements such as revisions,
organizations, calendars, resources, etc. The second level has the relationship among
major elements and their referencing elements.


INDEX
TIME-SHEET

employee-identifier
employee-number

Sura
1..* | INDEX

TIME-SHEETS

type

identifier

number

name 1

INDEX EMPLOYEE-KEY
SHOP-DATA pay-period
type overtime-status
identifier hours-regular-time
number hoursovertime
name hours-vacation
description hours-sick-leave
REFERENCE-KEYS }7] hours-holiday
REVISIONS 1 hours-unpaid
TIME-SHEET 1 total-hours

total-days-absent

0..1
revisions — F revision

+ Lisietenerkey L{ oestos F,f :

Figure C-17: time-sheets and related tables

142


CALENDAR-MULTI- | Number [0.1], calendar-multi-key. DEPARTMENT
KEY

CHILD- Number [0..1], child-department-multi-
DEPARTMENT- key. DEPARTMENT
MULTI-KEY

POSITIONS Number [1..*], positions. DEPARTMENT
Table department-key

Fields Data Type Description

INDEX Number [1], system use
CREATOR Number [1], system use
ORGANIZATIONAL- | Number [1], system use
CONTACT

POSITION-MULTI- | Number [1], system use

KEY

CHILD- Number [1], system use
DEPARTMENT-

MULTI-KEY

department-identifier _| Number 1]
department-number Text (J

Table departments

Fields Data Type Description

INDEX Number [1], system use
ORGANIZATION Number [1], system use

type Enumeration _| [0..1]

identifier Number 0]

number Text gl

name Text (0..1]

description Text [0..1]

priority-rating Enumeration | [0..1]
DEPARTMENT Number [1..*], department. DEPARTMENTS
Table digital-file-properties

Fields Data Type Description

INDEX Number [1], system use
DIGITAL-FILES Number [1], system use
server-system-name Text [0..1]

server-drive Text [0..1]
server-directory Text [0.1]

file-name Text [0..1]

file-extension Text [0..1]

file-format Text [0..1]
application-name Text [0..1]
application-version Text (0..1]

30


PARTS Number [0..1], parts. SHOP-DATA
BILLS-OF- Number [0..1], bills-of-materials SHOP-DATA
MATERIALS
INVENTORY Number [0..1], inventory.SHOP-DATA
PROCUREMENTS Number [0..1], procurements.SHOP-DATA.
PROCESS-PLANS Number [0..1], process-plans.SHOP-DATA
WORK Number [0..1], work.SHOP-DATA
SCHEDULES Number [0..1], schedules.SHOP-DATA
TIME-SHEETS Number [0..1], time-sheets.SHOP-DATA
REFERENCES Number [0..1], references.SHOP-DATA
PROBABILITY- Number [0..1], probability-distributions.SHOP-
DISTRIBUTIONS DATA
UNITS-OF- Number [0..1], units-of-measurement.SHOP-DATA
MEASUREMENT
Table sick-leave-accrual-rate
Fields Data Type Description
INDEX Number [1], system use
PAY-STATUS Number [1], system use
rate Number {1]

/Double

(Fixed)
time-units Enumeration | [0..1]
accrual-period Number [0..1]

/Double

(Fixed)
Table skill-definition
Fields Data Type Description
INDEX Number [1], system use
SKILL- Number [1], system use
DEFINITIONS
type Enumeration _| [0..1]
identifier Number 0)
number Text 1)
name Text [0.1]
description Text [0..1]
LEVELS Text [0..1], levels. SKILL-DEFINITION
Table skill-definition-ke
Fields Data Type Description
INDEX Number [1], system use
SKILL-MULTI-KEY _| Number [1], system use
skill-definition- Number [1]

identifier

99



“schedule :

INDEX

| schedules

noe SeneBUUES

SHOP-DATA identifier

He number

I Sate name

number description

ate REFERENCE-KEYS
REVISIONS

REFERENCE-KEYS Fp RESOURCES-SECTION

REVISIONS 1 WORK-SECTION

SCHEDULE 1

0..4

“ere

0.1

Figure C-16: schedules and related tables

141



Table rotation

Fields Data Type Description
INDEX Number [1], system use
FROM-REFERENCE- | Number [1], system use
FRAME
alpha-coefficient Number fi)
/Double
beta-coefficient Number [1]
/Double
gamma-coefficient Number (1
/Double
Table routing-sheet
Fields Data Type Description
INDEX Number [1], system use
ROUTING-SHEETS __| Number [1], system use
type Enumeration | (0..1]
identifier Number (1]
number Text [1]
name Text [0..1]
description Text (0..1]
REFERENCE-KEYS _| Text [0..1], reference-keys. SOURCE
REVISIONS Text [0..1], revisions. SOURCE
BATCH-SIZES Number [1], batch-sizes. ROUTING-SHEET
PLAN-DEFINITION _| Number [1], plan-definition.ROUTING-SHEET

Table routing-sheet-key

Fields Data Type Description
INDEX Number [1], system use
PROCESS-PLAN- Number [1], system use
MULTI-KEY

routing-sheet-identifier | Number (1)
routing-sheet-number_| Text (1)

Table routing-sheets

Fields Data Type Description
INDEX Number [1], system use
PROCESS-PLAN Number [1], system use
ROUTING-SHEET Number [1..*], routing-sheet-ROUTING-SHEETS
Table scaling

Fields Data Type Description
INDEX Number [1], system use
FROM-REFERENCE- | Number [1], system use

FRAME

92



This subsection describes the relationships between the machines element and its related
elements. Machines lists each machine that exists within the shop. The machine element
provides descriptive information, hourly rate, technical specifications, status, station
associations, and reliability data for a machine. Machines is a collection of individual
machine elements. It is referenced by resources and it references the elements of
machine, machine-group, reference-keys, and revisions. Type, identifier, number, name,
and description are attributes of machines. Figure 4 demonstrates how machines relates
to other elements.

machines -
INDEX 1.* INDEX
RESOURCES — MACHINES
type type
identifier identifier
number number
name name
description 1 description
MACHINE 1 hourly-rate
MACHINE-GROUP 1 STATION-KEYS
REFERENCE-KEYS REALIABILITY-STATISTICS
REVISIONS 1 MACHINE-SPECIFICATIONS
MACHINE-STATUS
revisions
0.1 0. | INDEX
—| MACHINES
type
identifier
number
name
description
hourly-rate
— MACHINE-KEYS
STATION-KEYS
ol reference-keys WORK-ASSIGNMENTS

Figure 4: The relationships of machines

5.5 Summary of the development

The machine shop database model includes about 500 relational tables. The database
model is presented in Appendices A and B. Appendix A presents the table structures for
the shop data model. Each table defines a name, its data type and a description for a field.
A field may be an attribute, a parent reference, or a child reference. A data type identifies
the data format for the field. The data type can be a text string, a number (such as a

12


name Text (0..1]

description Text [0..1]

effective-start-date Date/Time [1], yyyy-mm-dd

effective-end-date Date/Time [1], yyyy-mm-dd

SHIFTS Number [1], shifts. SHIFT-SCHEDULE
HOLIDAYS Number [0..1], holidays.SHIFT-SCHEDULE

Table shift-schedule-key

Fields Data Type Description
INDEX Number [1], system use
CALENDAR-MULTI- | Number [1], system use
KEY
shift-schedule- Number fi]
identifier
shift-schedule-number_| Text {1
Table shifts
Fields Data Type Description
INDEX Number [1], system use
[Integer
SHIFT-SCHEDULE __| Number | [1], system use
SHIFT Number [{1..*], shift.SHIFTS
Table shop-data
Fields Data Type Description
INDEX Number [1], system use
type Enumeration [0..1]
identifier Number (1)
number Text {J
name Text [0..1]
description Text [0..1]
REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions. SOURCE
ORGANIZATIONS Number [0..1], organizations.SHOP-DATA
CALENDARS Number [0..1], calendars.SHOP-DATA.
RESOURCES Number [0..1], resources.SHOP-DATA
SKILL- Number [0..1], skill-definitions SHOP-DATA
DEFINITIONS
OPERATION- Number [0..1], operation-definitions.SHOP-DATA
DEFINITIONS
SETUP- Number [0..1], setup-definitions.SHOP-DATA
DEFINITIONS
MAINTENANCE- Number [0..1], maintenance-definitions.SHOP-
DEFINITIONS DATA
LAYOUT Number [0..1], layout'SHOP-DATA

98



STATION-GROUP Number [1], system use

OPERATION- Number [1]. system use

DEFINITION

STATION-KEY Number [0..*], station-key.STATION-KEYS
STATION-GROUP- Number [0..*], station-group-key.STATION-KEYS
KEY

Table station-schedule

Fields Data Type Description

INDEX Number [1], system use

STATIONS- Number [1]. system use

SECTION

STATION-KEY Number [0..1], station-key. STATION-SCHEDULE
STATION-GROUP- Number [0..1], station-group-key. STATION-

KEY SCHEDULE

WORK- Number [1], work-assignemnts.STATION-SCHEDULE
ASSIGNMENTS

Table station-status

Fields Data Type Description

INDEX Number [1], system use

STATION Number [1], system use

operational-state Enumeration _| [0..1]

maintenance-state Enumeration | [0..1]

SETUP- Number [0..1], setup-definition-keys.STATION-
DEFINITION-KEYS STATUS

EMPLOYEE-KEYS Number [0..1], employee-keys.STATION-STATUS
WORK- Number [0..1], work-assignments.STA TION-STATUS
ASSIGNMENTS

Table station-used

Fields Data Type Description

INDEX Number [1], system use

STATIONS-USED Number [1], system use

STATION-GROUP- Number [0..1], station-group-key.STATION-USED
KEY

STATION-KEY Number [0..1], station-key. STATION-USED
EVENTS Number [0..1], events. STATION-USED

Table stations

Fields Data Type Description

INDEX Number [1], system use

RESOURCES Number [1], system use

type Enumeration _| [0..1]

identifier Number 1]

103



Table work

Fields Data Type Description
INDEX Number [1], system use
SHOP-DATA Number [1], system use
type Enumeration _| [0..1]
identifier Number [1]
number Text {1
name Text [0..1]
description Text [0..1]
REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions SOURCE
ORDERS Number [0..1], orders. WORK
JOBS Number [0..1], jobs. WORK
TASKS Number [0..1], tasks. WORK
MAINTENANCE- Number [0..1], maintenance-orders. WORK
ORDERS
PICK-ORDERS Number [0..1], pick-orders. WORK
TOOL-ORDERS Number [0..1], tool-orders. WORK.
Table work-actual-progress
Fields Data Type Description
INDEX Number [1], system use
ORDER-STATUS Number [1], system use
ORDER-ITEM- Number [1], system use
STATUS
JOB-STATUS Number [1], system use
TASK-STATUS Number [1], system use
MAINTENANCE- Number [1], system use
ORDER-STATUS
PICK-ORDER- Number [1], system use
STATUS
PICK-ORDER-ITEM- | Number [1], system use
STATUS
TOOL-ORDER- Number [1], system use
STATUS
material-availability- | Date/Time [0..1], yyyy-mm-dd
date
STATUS-CODE Number [0..1], status-code. WORK-ACTUAL-
PROGRESS
RESOURCES-USED | Number [0..1], resources-used. WORK-ACTUAL-
PROGRESS
EVENTS Number [0..1], events. WORK-ACTUAL-PROGRESS
setup-time-applied Number [0..1]
/Double
operation-time-applied_| Number [0..1]

119


CONTACT-KEY Number [1], contact.key.ORGANIZATIONAL-

CONTACT
Table organizations
Fields Data Type Description
INDEX Number [1], system use
SHOP-DATA Number [1], system use
type Enumeration _| [0..1]
identifier Number {1]
number Text fy
name Text [0..1]
description Text [0..1]
REFERENCE-KEYS _| Text 0..1], reference-keys. SOURCE
REVISIONS Text [0..1], revisions SOURCE
ORGANIZATION Number [1..*], organization, ORGANIZATIONS
Table paper-document
Fields Data Type Description
INDEX Number [1], system use
REFERENCE Number [1], system use
REPOSITORY Number [1..*], repository.PAPER-DOCUMENT
Table parameter
Fields Data Type Description
INDEX Number [1], system use
PARAMETERS Number (IJ, system use
type Enumeration | [0..1]
identifier Number fy)
number Text on
name Text [0..1]
value Number [0..1]

/Double
default Number [0..1]
/Double

Table parameters
Fields Data Type Description
INDEX Number [1], system use
JOB-DEFINITION Number [1]. system use
TASK-DEFINITION _| Number 1], system use
PLAN-STEP. Number [1], system use
PLAN-DEFINITION _| Number [1], system use
PARAMETER Number [1..*], parameter.PARAMETERS

Table parent-setup-keys



[PART-QUANTITIES [Number

LL], part-quantities PARTS-REQUIRED ]

Table parts-used

Fields Data Type Description
INDEX Number [1], system use
RESOURCES-USED _| Number [1], system use
PART-QUANTITIES _| Number [1], part-quantities.PARTS-USED
PART-INSTANCE- Number [0..*], part-instance-key. PARTS-USED
KEY
Table path
Fields Data Type Description
INDEX Number [1], system use
PATHS Number [1], system use
type Enumeration _| [0..1]
identifier Number (]
number Text (J
name Text [0..1]
FROM-REFERENCE- | Number [0..1], from-reference-frame.PATH
FRAME
REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
PATH-ROUTE Number [1..*], path-route. PATH
Table path-route
Fields Data Type Description
INDEX Number [1], system use
PATH Number [1], system use
BOUNDARY- Number [1..*], boundary-element.PATH-ROUTE
ELEMENT
Table paths
Fields Data Type Description
INDEX Number [1], system use
LAYOUT Number [1], system use
PATH Number [1..*], path. PATHS
Table pay-status
Fields Data Type Description
INDEX Number [1], system use
EMPLOYEE- Number [1], system use
STATUS
employment-status Enumeration | [1]
salary-type Enumeration _| [0..1]
hourly-rate Number [0.1]
/Double

72


Appendix A: Data Structures

Table a-weighted-element

Fields Data Type Description
INDEX Number [1], system use
LOGICAL- Number [1]. system use
STRUCTURE
a-key Text (1)
a-score Text ig}
Table actual-costs
Fields Data Type Description
INDEX Number [1], system use
COST-SUMMARY Number [1], system use
COST-CATEGORIES | Number. [1], cost-categories. ACTUAL-COSTS
Table address
Fields Data Type Description
INDEX Number [1], system use
ADDRESSES Number [1], system use
PHONE Number [0..*], phone. ADDRESS
EMAIL Number [0..*], email.ADDRESS
MAIL Number [0..*], mail.ADDRESS
Table addresses
Fields Data Type Description
INDEX Number [1], system use
EMPLOYEE Number [1], system use
CONTACT Number [1], system use
ORGANIZATION Number [1], system use
DEPARTMENT Number [1], system use
POSITION Number [1], system use
ADDRESS Number [1..*], address.ADDRESSES
Table annual-leave-accrual-rate
Fields Data Type Description
INDEX Number [1], system use
PAY-STATUS Number [1], system use
rate Number fy]

/Double

(Fixed)
time-units Enumeration _| [0..1]
accrual-period Number [0..1]

/Double

(Fixed)

17



skill-definition- Text 1]
number
Table skill-definitions
Fields Data Type Description
INDEX Number [1], system use
SHOP-DATA Number [1], system use
type Enumeration _| [0..1]
identifier Number D1
number Text eal
name Text [0..1]
description Text [0..1]
REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions. SOURCE
SKILL-DEFINITION _| Number [1..*], skill-definition.SKILL-DEFINITIONS
Table skill-multi-key
Fields Data Type Description
INDEX Number [1], system use
SKILL-MULTI-KEYS | Number [1], system use
SKILL-DEFINITION- | Number [1], skill-definition-key.SKILL-MULTI-KEY
KEY
LEVEL- Number [0..*], level-definition-key.SKILL-MULTI-
DEFINITION-KEY KEY
Table skill-multi-keys
Fields Data Type Description
INDEX Number [1], system use
EMPLOYEE- Number [1]. system use
REQUIRED
EMPLOYEE Number [1]. system use
SKILL-MULTI-KEY _| Number [1..*], skill-multi-key.SKILL-MULTI-KEYS
Table station
Fields Data Type Description
“| INDEX Number [1], system use
STATIONS Number [1], system use
type Enumeration | [0..1]
identifier Number ina
number Text [1]
name Text [0..1]
description Text [0..1]
REFERENCE-KEYS _| Text [0..1], reference-keys. SOURCE
REVISIONS Text [0..1], revisions. SOURCE
hourly-rate Number 0..1)

100



Table calendar-multi-ke

Fields Data Type Description

INDEX Number [1]. system use
EFFECTIVE-SHIFT Number [1], system use
DEPARTMENT Number [1], system use

CALENDAR-KEY Number

[0..1], calendar-key.CALENDAR-MULTI-
KEY

SHIFT-SCHEDULE- | Number
KEY

[0..1], shift-schedule-key. CALENDAR-
MULTI-KEY

SHIFT-KEY Number [0..1], shift-key.CALENDAR-MULTI-KEY
Table calendars

Fields Data Type Description

INDEX Number [1], system use

SHOP-DATA Number [1], system use

type Enumeration _| [0..1]

identifier Number (1)

number Text (y

name Text [0..1]

description Text [0.1]

REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions. SOURCE
CALENDAR Number [1..*], calendar. CALENDARS
Table child-bom-elements

Fields Data Type Description

INDEX Number [1], system use
BOM-ELEMENT Number [1], system use
BOM-ELEMENT- Number [1..*], bom-element-key.CHILD-BOM-
KEY ELEMENTS

Table child-department-multi-key

Fields Data Type Description

INDEX Number [1], system use
DEPARTMENT Number [1], system use

ORGANIZATION- Number
KEY

[0..1], organization-key.CHILD-
DEPARTMENT-MULTI-KEY

DEPARTMENT-KEY | Number

[1..*], department-key.CHILD-
DEPARTMENT-MULTI-KEY

Table child-setup-keys

Fields Data Type

Description

INDEX Number

[1], system use

SETUP-DEFINITION | Number

[1], system use

22



machine-group- Text 1]

number

Table machine-key

Fields Data Type Description
INDEX Number [1], system use
MACHINE-KEYS Number [1], system use
MACHINE- Number [1]. system use
ASSIGNED

MACHINE-USED Number [1], system use
MAINTAINED- Number [1], system use
RESOURCE-KEYS

LOCATIONS- Number [1], system use
SERVED-KEYS

SETUP- Number [1]. system use
RESOURCES-KEYS

MACHINE- Number [1], system use
SCHEDULE

machine-identifier Number (1)
machine-number Text (1)

Table machine-keys

Fields Data Type Description
INDEX Number [1], system use
MACHINE- Number [1], system use
CONFIGURATION

MACHINE-GROUP __| Number [1], system use
STATION Number [1], system use
TARGET- Number [1], system use
MACHINES

MACHINE-KEY Number [0..*], machine-key. MACHINE-KEYS
MACHINE-GROUP- | Number [0..*], machine-group-key.MACHINE-KEYS

KEY

Table machine-program

Fields Data Type Description

INDEX Number [1], system use

MACHINE- Number [1], system use

PROGRAMS

type Enumeration _| [0..1]

identifier Number {1]

number Text (1

name Text [0..1]

description Text (0..1]

REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE

51



DEFINITION-KEYS

PARENT-SETUP- Number [1]. system use
KEYS
CHILD-SETUP- Number [1], system use
KEYS
CURRENT-SETUP Number [1], system use
NEW-SETUP Number [1], system use
setup-definition- Number {]
identifier
setup-definition- Text 1]
number
Table setup-definition-keys
Fields Data Type Description
INDEX Number (1], system use
CRANE-ASSIGNED _| Number [1], system use
CRANE-USED Number [1], system use
WORK- Number [1], system use
ASSIGNMENT
STATION-STATUS __| Number [1], system use
SETUP- Number [1..*], setup-definition-key.SETUP-
DEFINITION-KEY DEFINITION-KEYS
Table setup-definitions
Fields Data Type Description
INDEX Number [1], system use
SHOP-DATA Number [1], system use
type Enumeration | [0..1]
identifier Number {1]
number Text ial
name Text [0..1]
description Text [0.1]
REFERENCE-KEYS _| Text [0..1], reference-keys. SOURCE
REVISIONS Text [0..1], revisions. SOURCE
SETUP-DEFINITION | Number [1..*], setup-definition.SETUP-DEFINITIONS
SETUP- Number [0..1], setup-changeovers.SETUP-
CHANGEOVERS DEFINITIONS
Table setup-duration
Fields Data Type Description
INDEX Number [1], system use
ESTIMATED- Number [1], system use
DURATIONS
nominal-duration Number [0..1]

/Double

96



INDEX Number [1], system use
CREATOR Number [1], system use
EMPLOYEE-KEYS Number 1], system use
EMPLOYEE- Number [1], system use
ASSIGNED

EMPLOYEE-USED Number | [1], system use
EMPLOYEE- Number [1], system use
SCHEDULE

TIME-SHEET Number [1], system use
employee-identifier Number 1]
employee-number Text {]

Table employee-keys

Fields Data Type Description
INDEX Number [1], system use
EMPLOYEE- Number [1], system use
REQUIRED

STATION-STATUS __| Number [1], system use
STATION-GROUP Number {1], system use
POSITION Number [1], system use
EMPLOYEE-KEY Number [1..*], employee-key.EMPLO YEE-KEYS

Table employee-required

Fields Data Type Description
INDEX Number [1], system use
EMPLOYEES- Number [1], system use
REQUIRED
minimum-employees | Number fy
/Integer
maximum-employees | Number 0]
/Integer
EMPLOYEE-KEYS Number [0..1], employee-keys.EMPLOYEE-
REQUIRED
SKILL-MULTI-KEYS | Number [0..1], skill-multi-keys.EMPLOYEE-

REQUIRED

Table employee-schedule

Fields Data Type Description

INDEX Number 1], system use

EMPLOYEES- Number [1]. system use

SECTION

EMPLOYEE-KEY Number [1], employee-key.EMPLOYEE-SCHEDULE
WORK- Number [1], work-assignemnts.EMPLOYEE-
ASSIGNMENTS SCHEDULE

34



resource-
1.* | object

a

1."

eg part-object

ee
INDEX
SHOP-DATA

type

identifier

number

name

description
REFERENCE-KEYS

REVISIONS

REFERENCE-FRAMES

AREAS

PATHS
RESOURCE-OBJECTS

== ]s]-/4 =

PART-OBJECTS

0.4
“reference-
“keys

0.4
“revisions

ay

creators

0.1

Figure C-9: /ayout and related tables

134


Table area

Fields Data Type Description

INDEX Number [1]. system use

AREAS Number [1], system use

type Enumeration _| [0..1]

identifier Number 0]

number Text (1)

name Text 0.1)

FROM-REFERENCE- | Number [0..1], from-reference-frame.AREA.
FRAME

REFERENCE-KEYS | Text [0..1], reference-keys.SOURCE
AREA-BOUNDARY _| Number [1..*], area-boundary. AREA

Table area-boundary

Fields Data Type Description

INDEX Number [1], system use

AREA Number [1], system use

BOUNDARY- Number [1..*], boundary-element.AREA-BOUNDARY
ELEMENT

Table areas

Fields Data Type Description.

INDEX Number [1], system use

LAYOUT Number [1], system use

AREA Number [1..*], area AREAS

Table availability-exception

Fields Data Type Description

INDEX Number [1], system use

AVAILABILITY- Number [1], system use

EXCEPTIONS

type Enumeration _| [0..1]

identifier Number ing}

number Text {1]

name Text [0..1]

EVENTS Number [1], events.A VAILABILITY-EXCEPTION
Table availability-exceptions

Fields Data Type Description

INDEX Number [1], system use

EMPLOYEE- Number [1], system use

AVAILABILITY

AVAILABILITY- Number [1..*], availability-

EXCEPTION exception.A VAILABILITY-EXCEPTIONS

18



[OPERATION-SHEET | Number

[[..*], operation-sheet-OPERATION-SHEETS |

Table order

Fields Data Type Description

INDEX Number [1], system use

ORDERS Number [1], system use

type Enumeration _| [0..1]

identifier Number (J

number Text (1)

name Text [0..1]

description Text [0..1]

REFERENCE-KEYS _| Text [0..1], reference-keys. SOURCE
REVISIONS Text [0..1], revisions. SOURCE

ORDER- Number [0..1], order-definition, ORDER
DEFINITION

ORDER-STATUS Number (0..1], order-status; ORDER.

Table order-definition

Fields Data Type Description

INDEX Number [1], system use

ORDER. Number [1], system use

priority-rating Enumeration _| [0..1]

CUSTOMERS Number [1], customers.ORDER-DEFINITION
DUE-DATES Number [1], due-dates.ORDER-DEFINITION
ORDER-ITEMS Number [0..1], order-items.ORDER-DEFINITION
PRECEDENT- Number [0..1], precedent-constraints.ORDER-
CONSTRAINTS DEFINITION

RESOURCES- Number [0..1], resources-required.ORDER-
REQUIRED DEFINITION

COST-SUMMARY Number [0..1], cost-summary.ORDER-DEFINITION
Table order-item

Fields Data Type Description

INDEX Number [1], system use

ORDER-ITEMS Number [1], system use

type Enumeration | [0..1]

identifier Number (1)

number Text (1)

name Text [0..1]

description Text [0..1]

ORDER-ITEM- Number [0..1], order-item-definition.ORDER-ITEM
DEFINITION

ORDER-ITEM- Number [0..1], order-item-status:ORDER-ITEM
STATUS

62



[| SETUP-DEFINITION

FIXTURE-DEFINITION-KEY

FIXTURESET-DEFINITION-KEY

TOOL-DEFINITION-KEY
TOOLSET-DEFINITION-KEY
LOGICAL-STRUCTURE

foolset-definition-key _

: _ Logical-structure

INDEX
SHOP-DATA
type

identifier
number
name
description

REVISIONS

REFERENCE-KEYS

SETUP-DEFINITION

=]-/7]

setu| p-definition

INDEX
SETUP-DEFINITIONS
type

identifier

number

name

description
REFERENCE-KEYS
REVISIONS
SETUP-COMPONENTS
SETUP-RESOURCE-KEYS.
PARENT-SETUP-KEYS
CHILD-SETUP-KEYS
SETUP-CHANGEOVERS

=FRPEEERFI

0..1

~| creator

Figure C-6: setup-definitions and related tables

131


toolset-definition- Number {]

identifier

toolset-definition- Text {]

number

Table toolset-instance

Fields Data Type Description

INDEX Number [1], system use

TOOLSET- Number [1], system use

INSTANCES

type Enumeration _| [0..1]

identifier Number 01]

number Text {1

name Text [0.1]

current-location Text [0..1]

STATUS-CODE Number [0..1], status-code. TOOLSET-INSTANCE
TOOL-INSTANCE- Number [1], tool-instance-keys. TOOLSET-INSTANCE
KEYS

WORK- Number [0..1], work-assignments. TOOLSET-
ASSIGNMENTS INSTANCE

Table toolset-instances

Fields Data Type Description

INDEX Number [1], system use

TOOLSET- Number [1], system use

INVENTORY-ITEM

TOOLSET- Number [1..*], toolset-instance. TOOLSET-
INSTANCE INSTANCES

Table toolset-inventory-item

Fields Data Type Description

INDEX Number [1], system use

TOOL-INVENTORY _| Number [1], system use

type Enumeration _| [0..1]

identifier Number 0]

number Text (1)

name Text [0..1]

description Text [0..1]

REFERENCE-KEYS _| Text [0..1], reference-keys. SOURCE
REVISIONS Text [0..1], revisions. SOURCE

TOOLSET- Number [1], toolset-definition-key. TOOLSET-
DEFINITION-KEY INVENTORY-ITEM
STOCK-LEVEL- Number [1], stock-level-quantities. TOOLSET-
QUANTITIES INVENTORY-ITEM
PROCUREMENT- Number 0..1], procurement-multi-keys. TOOLSET-

115



Table type-calendars

Table type-crane

Table type-department

Table type-department-structure
Table type-email

Table type-employees

Table type-fixture-catalog
Table type-fixture-instance
Table type-fixtureset-definition
Table type-fixtureset-inventory-item
Table type-job

Table type-layout

Table type-machine

Table type-machine-program
Table type-mail

Table type-maintenance-definitions
Table type-maintenance-orders
Table type-materials-inventory-item
Table type-operation-definitions
Table type-order

Table type-orders

Table type-organizations

Table type-part

Table type-part-instance

Table type-part-object

Table type-phone

Table type-pick-order-item
Table type-plan-parameter
Table type-position

Table type- precedent-constraint
Table type-probability-distributions
Table type-process-plan

Table type-procurement

Table type-reference

Table type-references

Table type-resource-object
Table type-revision

Table type-schedule

Table type-setup-configuration
Table type-setup-definitions
Table type-shift-schedule

Table type-skill-definition
Table type-station

Table type-stations

Table type-contact

Table type-cranes

Table type-departments

Table type-dimension

Table type-employee

Table type-event

Table type-fixture-definition
Table type-fixture-inventory-item
Table type-fixtureset-instance
Table type-inventory

Table type-jobs

Table type-level-definition
Table type-machine-group
Table type-machines

Table type-maintenance-definition
Table type-maintenance-order
Table type-material-instance
Table type-operation-definition
Table type-operation-sheet
Table type-order-item

Table type-organization

Table type-parameter

Table type-parts

Table type-part-inventory-item
Table type-path

Table type-pick-order

Table type-pick-orders

Table type-plan-step

Table type-position-description
Table type-probability-distribution
Table type-probability-distribution-set
Table type-process-plans

Table type-procurement-item
Table type-reference-frame
Table type-procurements

Table type-resources

Table type-routing-sheet

Table type-schedules

Table type-setup-definition
Table type-shift

Table type-shop-data

Table type-skill-definitions
Table type-station-group

Table type-status-code

Table type-step-precedent-constraint Table type-task

Table type-tasks

Table type-time-sheet

117


Manufacturing System Integration Division (MSID) of NIST. The database model will be
used to support the integration of manufacturing application and simulation in the shop
environment. This report provides a detailed description of the database model. Section 2
presents the concept of standard interfaces that support machine shop simulations.
Section 3 overviews the information model of machine shop data. Section 4 describes the
objectives of the database development. Section 5 introduces the database model. Section
6 presents sample queries. Section 7 provides conclusions and a discussion on future
work. The database model itself is presented in Appendices A-C, and a few sample data
tables are presented in Appendix D.

2. STANDARD INTERFACES

This section describes our approach to developing standard data interfaces that support
the machine shop manufacturing simulation. We have proposed an architecture for a
generic data-driven machine shop simulator [4], and have been constructing a prototype
simulator based on the architecture using commercial off-the-shelf software. The
architecture for the generic machine shop simulator is divided into the following
component elements: a neutral shop data file, an Extensible Markup Language (XML) [5]
data processor, a system supervisor and reporting module, a machine shop emulator, a
discrete event simulator, and a user interface system. The machine shop information
model is a key factor in effectively and efficiently integrating the generic machine shop
simulator.

The information model is now being formulated into a schema using the XML schema
language [6]. The information model/XML schema serves as a neutral data format for
representing and exchanging machine shop data. With the neutral data format, XML
parsers, DBMS translators, and/or XML translators, machine shop data can be
represented in working forms (structured, in-memory representations), in database tables,
or in XML instance documents. Figure 1 depicts the role of the standard interfaces. The
XML parsers, “to/from Database Management System (DBMS) translators,” and
“to/from XML translators” are custom-built software programs. XML parsers convert
XML schemas’ data elements to structural in-memory presentations, such as C++ data
structures. “To/from DBMS translators” and “to/from XML translators” allow data to be
converted among a user’s data formats, database structures, and XML document formats.

To facilitate an implementation of the machine shop information model, two translators
are being developed at NIST. One converts an XML instance document to an Access
database; the other converts a database back to XML. XML data structures, which are
parsed from the XML Schemas, are used as intermediate representation. A graphical
user interface (GUI) system will also be generated to execute various functions, such as
import, export, and translator execution.


SETUP- Number [1], system use

CHANGEOVERS

CURRENT-SETUP Number [1], current-setup.SETUP-CHANGEOVER
NEW-SETUP Number [1], new-setup.SETUP-CHAN GEOVER

Table setup-changeovers

Fields Data Type Description

INDEX Number [1], system use

SETUP- Number [1]. system use

DEFINITIONS

SETUP- Number [1..*], setup-changeover.SETUP-
CHANGEOVER CHANGEOVERS

Table setup-components

Fields Data Type Description

INDEX Number [1], system use

SETUP- Number [1], system use
CONFIGURATION

SETUP-DEFINITION _| Number (1], system use

FIXTURE- Number [0..*], fixture-definition-key. SETUP-
DEFINITION-KEY COMPONENTS

FIXTURESET- Number [0..*], fixtureset-definition-key.SETUP-
DEFINITION-KEY COMPONENTS
TOOL-DEFINITION- | Number [0..*], tool-definition-key.SETUP-
KEY COMPONENTS

TOOLSET- Number [0..*], toolset-definition-key. SETUP-
DEFINITION-KEY COMPONENTS

LOGICAL- Number [0..*], logical-structure. SETUP-
STRUCTURE COMPONENTS

Table setup-configuration

Fields Data Type Description

INDEX Number [1], system use

SETUP- Number [1]. system use
CONFIGURATIONS

type Enumeration _| [0..1]

identifier Number 1]

number Text OJ

name Text [0..1]

description Text [0..1]

REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions. SOURCE
SETUP- Number [1], setup-definition-key. SETUP-
DEFINITION-KEY CONFIGURATION

SETUP- Number [1], setup-components.SETUP-

94


MACHINE- Number [1], system use

PROGRAM

SETUP-DURATION _ | Number [0..1], setup-duration. ESTIMATED-
DURATION

UNIT-OPERATION- | Number [0..1], unit-operation-duration.ESTIMATED-

DURATION DURATION

TOTAL- Number [0..1], total-operations-duration.ESTIMATED-

OPERATIONS- DURATION

DURATION

Table event

Fields Data Type Description

INDEX Number [1], system use

EVENTS Number [1], system use

type Enumeration _| [0..1]

identifier Number i]

number Text ial

name Text [0..1]

date Date/Time (J), yyyy-mm-dd

time Date/Time [1], hh:mm:ss

Table events

Fields Data Type Description

INDEX Number [1], system use

WORK- Number [1], system use

SCHEDULED-

PROGRESS

WORK-ACTUAL- Number [1], system use

PROGRESS

STATION- Number [1], system use

ASSIGNED

MACHINE- Number [1], system use

ASSIGNED

EMPLOYEE- Number [1], system use

ASSIGNED

CRANE-ASSIGNED _| Number [1], system use

STATION-USED Number [1], system use

MACHINE-USED Number [1], system use

EMPLOYEE-USED Number [1], system use

CRANE-USED Number [1], system use

WORK- Number [1], system use

ASSIGNMENT

AVAILABILITY- Number [1], system use

EXCEPTION

PROCUREMENT- Number [1], system use

39



Appendix C: Relationships between tables

type
identifier

number

name

description
REFERENCE-KEYS
REVISIONS
ORGANIZATIONS
CALENDARS

RESOURCES
SKILL-DEFINITIONS
SETUP-DEFINITIONS
OPERATION-DEFINITIONS
MAINTENANCE-DEFINITIONS
LAYOUT

PARTS
BILLS-OF-MATERIALS
INVENTORY
PROCUREMENTS
PROCESS-PLANS

WORK

SCHEDULES
TIME-SHEETS
REFERENCES
PROBABILITY-DISTRIBUTIONS
UNITS-OF-MEASUREMENT

Figure C-1: shop-data and related tables

126


/Double

(Fixed)
employee-capacity Number [0..1]
/Integer
MACHINE-KEYS Number [1], station-keys.STATION
STATION-STATUS __| Number [0..1], station-status. STATION
Table station-assigned
Fields Data Type Description
INDEX Number [1], system use
STATIONS- Number [1], system use
ASSIGNED
STATION-GROUP- Number [0..1], station-group-key. STATION-
KEY ASSIGNED
STATION-KEY Number [0..1], station-key.STATION-ASSIGNED
EVENTS Number 0.1], events. STATION-ASSIGNED

Table station-configuration

Fields Data Type Description
INDEX Number [1], system use
STATIONS- Number [1], system use
REQUIRED
minimum-stations Number 1]
/Integer
maximum-stations Number [1]
Integer
STATION-KEYS Number [1], station-keys.STATIONS-
CONFIGURATION
SETUP- Number [0..1], setup-configurations. STATION-
CONFIGURATIONS CONFIGURATION
Table station-group
Fields Data Type Description
INDEX Number [1], system use
STATIONS Number [1], system use
type Enumeration | [0..1]
identifier Number 0)
number Text 0
name Text [0..1]
description. Text [0..1]
REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text ‘[0..1], revisions. SOURCE
hourly-rate Number [0..1]
/Double
(Fixed)

101



Fields Data Type Description

INDEX Number [1], system use

INVENTORY Number [1], system use

FIXTURE- Number [1..*], fixture-inventory-item.FIXTURE-
INVENTORY-ITEM INVENTORY

FIXTURESET- Number [0..*], fixtureset-inventory-item.FIXTURE-

INVENTORY-ITEM

INVENTORY

Table fixture-inventory-item

Fields Data Type Description

INDEX Number | [1], system use

FIXTURE- Number [1], system use

INVENTORY

type Enumeration _| [0..1]

identifier Number ion

number Text DJ

name Text [0..1]

description Text [0..1]

REFERENCE-KEYS _| Text [0..1], reference-keys.SOURCE
REVISIONS Text [0..1], revisions. SOURCE
FIXTURE- Number [1], fixture-definition-key.FIXTURE-
DEFINITION-KEY INVENTORY-ITEM
STOCK-LEVEL- Number [1], stock-level-quantities. FIXTURE-
QUANTITIES INVENTORY-ITEM
PROCUREMENT- Number [0..1], procurement-multi-keys.FIXTURE-
MULTI-KEYS INVENTORY-ITEM

FIXTURE- Number [0..1], fixture-instances.FIXTURE-
INSTANCES INVENTORY-ITEM

Table fixtureset-definition

Fields Data Type Description

INDEX Number [1], system use

FIXTURESET- Number [1], system use

CATALOG

type Enumeration _| [0..1]

identifier Number 0]

number Text 0)

name Text [0..1]

description Text [0..1]

REFERENCE-KEYS _| Text [0..1], reference-keys. SOURCE
REVISIONS Text [0..1], revisions. SOURCE
FIXTURESET- Number [1], fxitureset-member-keys.FIXTURE-
MEMBER-KEYS DEFINITION

Table fixtureset-definition-key

42