A.4 Structure Rules

There are a lot of primary key, primary key/foreign key pair, and duplicate data examples in this set of tables. It shows how quickly these can get complex and interrelated.

Primary Keys

Table:	INVENTORY
Key:	PART_NUMBER
Rules:	Unique, not null, and not blank

Table:	INVENTORY
Natural Key:	DESCRIPTION, TYPE
Rules:	Unique, not null, and not blank

Table:	INVENTORY_HISTORY
Key:	PART_NUMBER, YEAR, QUARTER
Rules:	Unique, not null, and not blank

Table:	SUPPLIER
Key:	SUPPLIER_ID
Rules:	Unique, not null, and not blank

Table:	SUPPLIER
Natural Key:	COMPANY_NAME, COMPANY_ADDRESS, CITY, STATE
Rules:	Unique, not null, and not blank

Table:	SOURCE
Key:	PART_NUMBER, SUPPLIER_ID
Rules:	Unique, not null, and not blank

Table:	PURCHASE_ORDER
Key:	PO_NUMBER
Rules:	Unique, not null, and not blank

Primary Key/Foreign Key Pairs

Primary Table:	INVENTORY
Column:	PART_NUMBER
Secondary Table:	INVENTORY_HISTORY
Column:	PART_NUMBER
Characteristic:	ONE-TO-MANY NOT INCLUSIVE
Note:	There may be no history records if the part is new in the current quarter.

Primary Table:	ENGINEERING_DRAWINGS
Column:	ENG_DRAWING_NUMBER
Secondary Table:	INVENTORY
Column:	ENG_DRAWING_NUMBER
Characteristic:	ONE-TO-ONE NOT INCLUSIVE
Note:	This value may be blank in the INVENTORY table. It is used only if the part has a drawing number. All inventory rows of type I for internal must have a drawing number.

Primary Table:	INVENTORY
Column:	PART_NUMBER
Secondary Table:	SOURCE
Column:	PART_NUMBER
Characteristic:	ONE-TO-MANY INCLUSIVE
Note:	All inventory parts must have at least one source. They may have many supplier sources.

Primary Table:	INVENTORY
Column:	PART_NUMBER
Secondary Table:	PURCHASE_ORDER
Column:	PART_NUMBER
Characteristic:	ONE-TO-MANY NOT INCLUSIVE
Note:	A part may not have any orders for it. This is not unusual for a part that is new in inventory.

Primary Table:	SUPPLIER
Column:	SUPPLIER_ID
Secondary Table:	SOURCE
Column:	SUPPLIER_ID
Characteristic:	ONE-TO-MANY INCLUSIVE
Note:	All suppliers must supply at least one part. They may supply many parts.

Primary Table:	SUPPLIER
Column:	SUPPLIER_ID
Secondary Table:	PURCHASE_ORDER
Column:	SUPPLIER_ID
Characteristic:	ONE-TO-MANY NOT INCLUSIVE
Note:	A supplier may have no orders. This would be true for a newly established supplier or one who is a low-priority source for a part.

Primary Table:	PURCHASE_ORDER
Column:	PO_NUMBER
Secondary Table:	SUPPLIER
Column:	LAST_ORDER_NUMBER
Characteristic:	ONE-TO-MANY NOT INCLUSIVE
Note:	A supplier may have no orders.

Duplicate Data

There are three duplicate columns in the set of tables. All of them occur in the PURCHASE_ORDER table. Duplication is done for the purpose of making access to the purchase order information of higher performance without requiring joining information from the other tables.

Primary Table:	INVENTORY
Column:	DESCRIPTION
Secondary Table:	PURCHASE_ORDER
Column:	DESCRIPTION

Primary Table:	INVENTORY
Column:	UNIT_OF_MEASURE
Secondary Table:	PURCHASE_ORDER
Column:	UNIT_OF_MEASURE

Primary Table:	SOURCE
Column:	SUPPLIER_PART_NUMBER
Secondary Table:	PURCHASE_ORDER
Column:	SUPPLIER_PART_NUMBER