Class #06

Contents
Class #06: 관계형 데이타베이스를 이용한 객체지향적 제품정보 모델링
in place September 25, 2000, lasted modified April 5, 2002

We will review main entities of Product Information Management, the part master, the product structure and the configuration management, and present a relational data model that represents the entities through object-orineted views.
Main Entities of PIM

Part Master
A list of parts with their attribute values and associated objects

consists of key attributes: part number, version, name, status, type, create date and owner.
The part number could have meanings but, difficult to manage them.
The part version differenciates two parts but maintain certain relationship such as inter-operable parts.
The part name or description could be standardized with pre difined words for the language translation in the multi-lagnueage catalogue
The status indicates the position of part in its life cycle. Security, release and applicability management could use the attribute.
The type shows the production methods of the part. They include purchasing, fablication and semi-assemby. This will be used to interface to the ERP and Customer support part managment systems.
The owner and date can be used for the security control.
Create, delete, update, version up operations
Associated Objects: Service object, Purchase object (Duplicated), Drawing objects (meta data for drawings)

An Example

PART NO 16500150
PART VERSION --A
PART NAME CAP
TYPE FAB
STATUS RELEASED
USAGE E/C/M
CREATE_DATE 2000-10-5
OWNER DNC
WEIGHT 10
MATERIAL PLASTIC
UNIT EA
PRICE 5
CS_TYPE M

Product Structure Managment
Tree like set of constituent relationship between parts which consists of end-product

End-product and assembly
Constituent relationship between an assembly and component parts
Quantity per data on the relationship (consider DMU)
Introduction of options as a virtual level in the product structure (optional)
Variant and effectivity for muti-product structure representation (optional)
Add, cut and replace operations of product structure.
Get parent and Where used operations
Nevigation with effectivity (optional)
Consider MBOM and CSBOM.
Change Managment of the Product Structure

An Example

PARENT_PART_NO 16500150
PARENT_PART_VERSION --A
CHILD_PART_NO 16500176
CHILD_PART_VERSION ---
QTY 3

Document Management

Engineering drawings (CAD Models, Images), Engineering Documents
Need proper acess control
Support long duration transaction - Check in/check out
Storage methods:
file outside database - simple and no translation, no concurrnt contol
BLOB in database - need translation, advantage in sequrity control, can use the database client

An Example

DRAWING_NO 16500150
REVISION --A
SIZE A4
SHEET 1/1
PRIME 3
TYPE MS WORD 7.0

Product with options

Current market pressure force manufactures to open large number of choices for their product.
The "product with options" can meet the requirments.
Option needs the module design that can be used as a component of the product without any modification. Modification brings following procedures and processes: re-test, re-cost evaluation, or re-certification ...
Sales, Production, and Customer support take the advange of the module.

Interface Eng./Mfg./CS Information Systems with Option Code

'Option Code' is the unique identifier of the options.
It can be used as a medium for interfacing Marketing/Eng./Mfg./CS Information Systems (or any data exchange such as docmuents); It is a unique code or grouping system that can be recognized by all the people in the company and even suppliers and customers.
Designers are grouped for the sepecific module that corresponds to an option or a set of options.; Generally desingers needs more detail classification than the Mfg. or CS departments, so they use their own categories such as functional system group.
Manufacturing plan and order the production with the unit of the option.
Selecting options provided by sales person, customers complete a configuration for his/her order.

How to Design Options
- A detailed discussion of the design issues of options(= configuration modeling) is beyond the scope of this class, but in the future necessary.
Configuration

제품사양 (Configuration) 이란? -
다양한 사용자 요구에 부응하기 위하여 하나의 기본 모델에 여러가지 Option과 Variant를 생성하여 제품을 생산하게 된다. 이와 같이 기본 사양에 여러가지 변화된 Option 사항을 적용한것을 Configuration 이라 한다.

Definition of Configuration -
"Product configuration is concerned with the specification of manaufacturing of assembly plans for specific products. The planning includes specification of the actual constituents of a product which are to be included in a planned unit of production."
- ISO STEP PART 44: Product structure configuration, 1994.-
Type of Product Configuration

Technical configuration:
It is a configuration caused by technical dependancies or restrictions. For example if a customer select CD player, he can not select Radio at the same time, because there is only space for the audio device.

Marketing configuration:
The configuration is designed to a group of customers in a certain regional area or market segment. It is controlled by the climate or geographical properties of the region. It is also restricted by environment or regal regulations. The position of the product in the market, price, quality and etc., should be considered.
example of the configuration is tropical configuration, or low noise configuration for the noise regulation

Sales configuration:

This configuration is a real configuration for a customer. This will determine the specific product along with its price and performance. An example is 1500 cc, 2 door sedan

Variant and Effectivity

Variant is an element of the product structure representation that can change its sub structure according to a specific condition.
The 'configurable part' is the part that is a variant and in working condition.
Effectivity has two kinds of meanning: One is the condition of the variant.
The other is when the structure effective for production with unit of the option.
The types of effectivity is as the following:

Type Description Remark
Option Effectivity Model Name or Option Code Option+range is possible.
Range Effectivity Range of Serial Number Option+range is possible.
Date Effectivity Production Date -
Lot Effectivity Lot Number -

The engineering changes could cause the new product structure within a variant with new effectivity.

A Product Structure with Options

It consists of the model, the option, and the part entities, which are related to each other with specific consistent relationship.
The engineering changes employ it as their product structure and support its functionalities for the EC management.
The model entity in the product structure represents end-products which contain modules for the possible configurations.
The part entity and its constituent relationship with other parts represent the product structure of conventional mechanical assemblies. The constituent relationship could have quantity per data between the assemblies and their components.
Between the model and the part entities, the option entity represents a set of assemblies that provide a module for product configurations.
The option entity is designed as a variant that can specify different product structure according to the given effectivity.
option can change its sub assemblies according to the specific serial number of products (see type of effectivity - the range effectivity).
option can change its sub assemblies according to the specific serial number of products.
The following figure dipicts the entity relationship diagram for the product structure.

Fig.6-1 ERD of The Product Structure

Relational Model

Functions

Part Information Browsing
Expand Product Structure

Expand Prouct Structure with Option data

Expand Prouct Structure(Configurations) with Serial Numbers (Engineering Changes)

ER Model

Tables of Part, Option, Model and Its Edges(Relations)

PART
/* PART_ASSY table specification */ /* by Namchul Do, January 21, 2002 */ /* last modified January 21, 2002 */ /* The sytem object identifier, OID type is VARCHAR(16) */ drop table part_assy; CREATE TABLE PART_ASSY ( OID uniqueidentifier /* SYSTEM OBJECT IDENTIFIER */ CONSTRAINT PART_ASSY_OID_Default DEFAULT NEWID(), OBJ_TYPE VARCHAR(20) /* CLASS (TABLE NAME) */ DEFAULT 'PART_ASSY', /* OID + OBJ_TYPE determins a globly unique key */ PART_NO VARCHAR(16) NOT NULL, PART_VERSION VARCHAR(3), PART_REVISION VARCHAR(3), PART_NAME VARCHAR(25), PART_DESCRIPTION VARCHAR(50), STATUS INT, /* IF STATUS value=10 it is temporal */ OWNER VARCHAR(16), CREATOR VARCHAR(16), LAST_UPDATE_USER VARCHAR(16), CREATE_DATE DATETIME, UPDATE_DATE DATETIME, /* end of SYSTEM ATTRIBUTES */ E_USE CHAR(3), /* Engineering Usage of parts */ C_USE CHAR(3), /* CS Usage of parts */ /* Global CONSTRAINTS */ CONSTRAINT PART_ASSY_PK PRIMARY KEY (OID) );

/* REL_ASSY table specification */ /* by Namchul Do, Dec. 21, 2001 */ /* last modified January. 21, 2002 */ /* The sytem object identifier, OID type is VARCHAR(16) */ drop table REL_ASSY; CREATE TABLE REL_ASSY ( OID uniqueidentifier /* SYSTEM OBJECT IDENTIFIER */ CONSTRAINT REL_ASSY_OID_Default DEFAULT NEWID(), OBJ_TYPE VARCHAR(20) /* CLASS (TABLE NAME) */ DEFAULT 'REL_ASSY', /* OID + OBJ_TYPE determins a globly unique key */ STATUS INT, /* IF STATUS value = -1 it is replaced, if -10 it is temporal */ OWNER VARCHAR(16), CREATOR VARCHAR(16), LAST_UPDATE_USER VARCHAR(16), CREATE_DATE DATETIME, UPDATE_DATE DATETIME, /* SYSTEM ATTRIBUTES */ PARENT_OID uniqueidentifier, CHILD_OID uniqueidentifier, CHILD_TYPE VARCHAR(20), QTY VARCHAR(20), UNIT VARCHAR(20), COMMENT VARCHAR(20) DEFAULT('-'), REPLACED uniqueidentifier, /* Global CONSTRAINTS */ CONSTRAINT REL_ASSY_PK PRIMARY KEY (OID) );

MODEL
/* REL_MODEL table specification */ /* by Namchul Do, Dec. 20, 2001 */ /* last modified January. 21, 2002 */ /* The sytem object identifier, OID type is VARCHAR(16) */ drop table REL_MODEL; CREATE TABLE REL_MODEL ( OID uniqueidentifier /* SYSTEM OBJECT IDENTIFIER */ CONSTRAINT REL_MODEL_OID_Default DEFAULT NEWID(), OBJ_TYPE VARCHAR(20) /* CLASS (TABLE NAME) */ DEFAULT 'REL_MODEL', /* OID + OBJ_TYPE determins a globly unique key */ STATUS INT, OWNER VARCHAR(16), CREATOR VARCHAR(16), LAST_UPDATE_USER VARCHAR(16), CREATE_DATE DATETIME, UPDATE_DATE DATETIME, /* SYSTEM ATTRIBUTES */ PARENT_OID uniqueidentifier, CHILD_OID uniqueidentifier, CHILD_TYPE VARCHAR(20), QTY VARCHAR(20), UNIT VARCHAR(20), COMMENT VARCHAR(20) DEFAULT('-'), /* Global CONSTRAINTS */ CONSTRAINT REL_MODEL_PK PRIMARY KEY (OID) );

/* REL_MODEL table specification */ /* by Namchul Do, Dec. 20, 2001 */ /* last modified January. 21, 2002 */ /* The sytem object identifier, OID type is VARCHAR(16) */ drop table REL_MODEL; CREATE TABLE REL_MODEL ( OID uniqueidentifier /* SYSTEM OBJECT IDENTIFIER */ CONSTRAINT REL_MODEL_OID_Default DEFAULT NEWID(), OBJ_TYPE VARCHAR(20) /* CLASS (TABLE NAME) */ DEFAULT 'REL_MODEL', /* OID + OBJ_TYPE determins a globly unique key */ STATUS INT, OWNER VARCHAR(16), CREATOR VARCHAR(16), LAST_UPDATE_USER VARCHAR(16), CREATE_DATE DATETIME, UPDATE_DATE DATETIME, /* SYSTEM ATTRIBUTES */ PARENT_OID uniqueidentifier, CHILD_OID uniqueidentifier, CHILD_TYPE VARCHAR(20), QTY VARCHAR(20), UNIT VARCHAR(20), COMMENT VARCHAR(20) DEFAULT('-'), /* Global CONSTRAINTS */ CONSTRAINT REL_MODEL_PK PRIMARY KEY (OID) );

CONFIGURATIONS
/* PART_CONFIG table specification */ /* by Namchul Do, Feb. 15, 2002 */ /* last modified Feb. 15, 2002 */ /* The sytem object identifier, OID type is VARCHAR(16) */ drop table PART_CONFIG; CREATE TABLE PART_CONFIG ( OID uniqueidentifier /* SYSTEM OBJECT IDENTIFIER */ CONSTRAINT PART_CONFIG_OID_Default DEFAULT NEWID(), OBJ_TYPE VARCHAR(20) /* CLASS (TABLE NAME) */ DEFAULT 'PART_CONFIG', /* OID + OBJ_TYPE determins a globly unique key */ PART_NO VARCHAR(16) NOT NULL, PART_VERSION VARCHAR(3), PART_REVISION VARCHAR(3), PART_NAME VARCHAR(25), PART_DESCRIPTION VARCHAR(50), STATUS INT, OWNER VARCHAR(16), CREATOR VARCHAR(16), LAST_UPDATE_USER VARCHAR(16), CREATE_DATE DATETIME, UPDATE_DATE DATETIME, /* SYSTEM ATTRIBUTES */ MODEL_OID uniqueidentifier, /* Global CONSTRAINTS */ CONSTRAINT PART_CONFIG_PK PRIMARY KEY (OID) );

EFFECTIVITY
/* EFFECTIVITY table specification */ /* by Namchul Do, Dec. 22, 2001 */ /* last modified Dec. 22, 2001 */ /* The sytem object identifier, OID type is VARCHAR(16) */ drop table EFFECTIVITY; CREATE TABLE EFFECTIVITY ( ENV VARCHAR(16) NOT NULL, REL_OID uniqueidentifier NOT NULL, EFF_TYPE VARCHAR(16), OPTION_ID VARCHAR(16) DEFAULT('-'), START_SNO INT DEFAULT(0), END_SNO INT DEFAULT(0), EFF_DATE DATETIME, PLANNED_START_SNO INT DEFAULT(0), PLANNED_END_SNO INT DEFAULT(0), PLANNED_DATE DATETIME, STATUS INT, OWNER VARCHAR(16), CREATOR VARCHAR(16), LAST_UPDATE_USER VARCHAR(16), CREATE_DATE DATETIME, UPDATE_DATE DATETIME );

Contents
Knowledge & Engineering Databases (c) copyright Namchul Do, 2002

PART NO	16500150
PART VERSION	--A
PART NAME	CAP
TYPE	FAB
STATUS	RELEASED
USAGE	E/C/M
CREATE_DATE	2000-10-5
OWNER	DNC
WEIGHT	10
MATERIAL	PLASTIC
UNIT	EA
PRICE	5
CS_TYPE	M

PARENT_PART_NO	16500150
PARENT_PART_VERSION	--A
CHILD_PART_NO	16500176
CHILD_PART_VERSION	---
QTY	3

DRAWING_NO	16500150
REVISION	--A
SIZE	A4
SHEET	1/1
PRIME	3
TYPE	MS WORD 7.0

Type	Description	Remark
Option Effectivity	Model Name or Option Code	Option+range is possible.
Range Effectivity	Range of Serial Number	Option+range is possible.
Date Effectivity	Production Date	-
Lot Effectivity	Lot Number	-