Data Quality: The Accuracy Dimension (The Morgan Kaufmann Series in Data Management Systems)
|
|
A.2 Tables
The following table definitions exist for these business objects. Figure A.1 shows a diagram of the integration points between them.
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) |
|
|