A.2 Tables

A.2 Tables

The following table definitions exist for these business objects. Figure A.1 shows a diagram of the integration points between them.

click to expand
Figure A.I: Table diagram.

Each part in inventory has a master inventory row in the INVENTORY table and one row in the INVENTORY_HISTORY table for every calendar quarter the part has been carried in inventory.

INVENTORY

 

PART_NUMBER

CHARACTER(8)

DESCRIPTION

CHARACTER(20)

TYPE

CHARACTER(1)

UNIT_OF_MEASURE

CHARACTER(3)

QUANTITY_ONHAND

INTEGER

QUANTITY_ONORDER

INTEGER

ENG_DRAWING_NUM

NUMBER(9)

INVENTORY_PRICE

MONEY

LAST_PURCHASE_PRICE

MONEY

DATE_LAST_RECEIVED

DATE

LAST_PURCHASE_ORDER

NUMBER(8)

DO_NOT_ORDER_FLAG

CHARACTER(1)

  

INVENTORY_HISTORY

 

PART_NUMBER

CHARACTER(8)

YEAR

SMALL INTEGER

QUARTER

SMALL INTEGER

QUANTITY_SOLD

INTEGER

QUANTITY_ORDERED

INTEGER

QUANTITY_RECEIVED

INTEGER

NUM_ORDERS_PLACED

INTEGER

Each supplier of parts has one row in the SUPPLIER table and as many rows in the SOURCE table as parts that respective supplier is a source for. A part may be supplied by many suppliers.

SUPPLIER

 

SUPPLIER_ID

INTEGER(6)

COMPANY_NAME

CHARACTER(20)

COMPANY_ADDRESS

CHARACTER(20)

CITY

CHARACTER(20)

STATE

CHARACTER(2)

ZIP

CHARACTER(10)

PHONE

CHARACTER(10)

CONTACT_NAME

CHARACTER(20)

DATE_ESTABLISHED

DATE

DATE_LAST_ORDER

DATE

LAST_ORDER_NUMBER

NUMBER(9)

  

SOURCE

 

SUPPLIER_ID

CHARACTER(6)

PART_NUMBER

CHARACTER(8)

SUPPLIER_PART_NUM

CHARACTER(10)

PRIORITY

SMALLINTEGER

MINIMUM_QUANTITY

INTEGER

ORDER_MODE

CHARACTER(1)

ORDER_ADDRESS

CHARACTER(20)

ORDER_EMAIL

CHARACTER(20)

ORDER_EDI_ADDRESS

CHARACTER(20)

BILL_TO_ADDRESS

CHARACTER(20)

Each order for a part causes a single row to be created in the PURCHASE_ORDER table. An order can only order one part.

PURCHASE_ORDER

 

PO_NUMBER

NUMBER(9)

DATE_OF_ORDER

DATE

SUPPLIER_ID

INTEGER(6)

PART_NUMBER

CHARACTER(8)

DESCRIPTION

CHARACTER(20)

SUPPLIER_PART_NUM

CHARACTER(10)

QUANTITY

INTEGER

UNIT_OF_MEASURE

CHARACTER(3)

UNIT_PRICE

MONEY

DATE_SHIPPED

DATE

QUANTITY_SHIPPED

INTEGER

DATE RECEVIED

DATE

QUANTITY_RECEIVED

INTEGER

RECEIVED_BY

PERSON_ID

RECEIVED_BY_EXT

CHARACTER(4)

QUANTITY_RETURNED

INTEGER

DATE_RETURNED

DATE

STATUS

CHARACTER(1)

REASON

VARIABLE CHARACTER(200)



Data Quality(c) The Accuracy Dimension
Data Quality: The Accuracy Dimension (The Morgan Kaufmann Series in Data Management Systems)
ISBN: 1558608915
EAN: 2147483647
Year: 2003
Pages: 133
Authors: Jack E. Olson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net