Schema PARTS

The PARTS schema is used for managing parts.
It comprises the tables ITEMS, ITEM_CAT, PURPOSE, WAREHOUSE, COLOR_TAB and TABTAB.

Figure 6: Base tables of the PARTS schema; referencing foreign keys are displayed on a gray background

ITEMS table

The ITEMS table contains information about the stocked articles. It consists of a unique item number, the item name, its color and price, the current and minimum permitted stock levels for the item.

The ITEMS table is defined as follows:

CREATE TABLE	items
(item_num	INTEGER CONSTRAINT item_num_primkey PRIMARY KEY,
item_name	CHARACTER(20) CONSTRAINT item_name_notnull NOT NULL,
color	CHARACTER(15),
price	NUMERIC(8,2) CONSTRAINT price_notnull NOT NULL,
stock	INTEGER CONSTRAINT i_stock_notnull NOT NULL,
min_stock	INTEGER
)

ITEM_NUM is the primary key of the ITEMS table.

The ITEMS table contains the following data:

item_num	item_name	color	price	stock	min_stock
1	Bicycle	black	700.50	2	1
2	Bicycle	flame	230.00	1	1
10	Frame	black	150.00	10	5
11	Frame	edelweiss	150.00	10	5
120	Front wheel	metallic	40.00	3	5
130	Back wheel	metallic	40.00	12	5
200	Handlebars	metallic	60.00	1	5
210	Front hub	metallic	5.00	15	1
220	Rear hub	metallic	5.00	14	10
230	Rim	black	10.00	9	10
240	Spoke	black	1.00	211	240
500	Screw M5	black	1.10	300	240
501	Nut M5	black	0.75	295	240

Table 9: Data in the ITEMS table

ITEM_CAT table

The ITEM_CAT table contains two REF columns. The REF values in these columns reference BLOB objects in the BLOB tables IMAGES and DESCRIPTIONS in the ADDONS schema. The ITEM_CAT table is defined as follows:

CREATE TABLE	item_cat
(item_num	INTEGER CONSTRAINT c_item_num_notnull NOT NULL,
image	FOR REF(images),
desc	FOR REF(descriptions),
)

The ITEM_CAT table contains the following data:

item_ num	image	desc
2	ADDONS/IMAGES? UID=942acb5a471511db8700a9de6f050 52d &OID=1	ADDONS/DESCRIPTIONS? UID=9c3d3d64471511db8600bb19c953d 9f8 &OID=1
120	ADDONS/IMAGES? UID=a9f7656a471511db8f019d75c8d27c 82 &OID=2	ADDONS/DESCRIPTIONS? UID=b0e1d5cc471511db8300ae9379299 332 &OID=2
500	ADDONS/IMAGES? UID=bdfe5e10471511db8600e510dc40a 634 &OID=3	ADDONS/DESCRIPTIONS? UID=c279d1ea471511db8a01e1958d1b 9863 &OID=3
501	ADDONS/IMAGES? UID=cf7c2fbe471511db8e018591fbfe609 b &OID=4	ADDONS/DESCRIPTIONS? UID=d49b3a08471511db8c0096edddbf5 55c &OID=4

Table 10: Data in the ITEM_CAT table

PURPOSE table

The PURPOSE table specifies the individual components involved in the construction of an item and the number of each of these that is required. Some items are used separately and as a component of another item. The PURPOSE table contains the item number of an article in the ITEMS table, the item number of the part in the ITEMS table and the number of times that this part is used in the item (NUMBER).

The PURPOSE table is defined as follows:

CREATE TABLE	purpose
(item_num	INTEGER CONSTRAINT p_item_num_notnull NOT NULL,
part	INTEGER CONSTRAINT part_notnull NOT NULL,
number	INTEGER CONSTRAINT number_notnull NOT NULL,
CONSTRAINT	p_item_num_ref_items FOREIGN KEY (item_num) REFERENCES items,
CONSTRAINT	part_ref_items FOREIGN KEY (part) REFERENCES items
)

The foreign keys ITEM_NUM and PART reference the ITEMS.ITEM_NUM primary key of the ITEMS table.

The PURPOSE table contains the following data:

item_num	part	number
1	10	1
1	120	1
1	130	1
1	200	1
120	210	1
120	230	1
120	240	15
120	500	5
120	501	5
200	500	10
200	501	10

Table 11: Data in the PURPOSE table

WAREHOUSE table

The WAREHOUSE table contains information about the item stocks in the individual warehouses. It consists of an item number in the ITEMS table and the stock of the item at a given warehouse location.

The WAREHOUSE table is defined as follows:

CREATE TABLE	warehouse
(item_num	INTEGER CONSTRAINT w_item_num_notnull NOT NULL,
stock	INTEGER CONSTRAINT w_stock_notnull NOT NULL,
city	CHAR(25),
CONSTRAINT	w_item_num_ref_items FOREIGN KEY (item_num) REFERENCES items
)

The foreign key ITEM_NUM refers to the primary key ITEMS.ITEM_NUM in the ITEMS table. The WAREHOUSE table contains the following data:

item_num	stock	city
1	2	Main warehouse
2	1	Main warehouse
10	10	Main warehouse
11	10	Main warehouse
120	3	Main warehouse
130	3	Main warehouse
130	9	Parts warehouse
200	1	Main warehouse
210	15	Main warehouse
220	8	Main warehouse
220	6	Parts warehouse
230	6	Main warehouse
230	3	Parts warehouse
240	11	Main warehouse
240	200	Parts warehouse
500	120	Main warehouse
500	180	Parts warehouse
501	248	Main warehouse
501	47	Parts warehouse

Table 12: Data in the WAREHOUSE table

COLOR_TAB

The COLOR_TAB table contains information about how individual colors can be constructed from differing proportions of red, green and blue. It consists of the name of the color and the different proportions of red, green and blue

The COLOR_TAB table is defined as follows:

CREATE TABLE	color_tab
(color_name	CHARACTER(15),
rgb	(3)NUMERIC(2,2)
)

The COLOR_TAB table contains the following data:

color_name	rgb
flame	0.98	0	0
orange	0.9	0.3	0
skyblue	0	0	0.99
aquamarine	0	0.1	0.99
edelweiss	0.99	0.99	0.99
black	0	0	0
metallic	0	0.2	0.3

Table 13: Data in the COLOR_TAB table

TABTAB table

The TABTAB table contains information about the tables present in the PARTS schema. It consists of a unique table number, the table name and the function of the table.

The TABTAB table is defined as follows:

CREATE TABLE	tabtab
(table_num	INTEGER CONSTRAINT table_num_primkey PRIMARY KEY,
table_name	CHARACTER(20) CONSTRAINT table_name_notnull NOT NULL,
comment	CHARACTER(50)
)

TABLE_NUM is the primary key of the TABTAB table. The TABTAB table contains the following data:

table_num	table_name	comment
1	items	Parts data
2	purpose	Related bicycle data
3	warehouse	Warehouse locations
4	color_tab	Permissible colors
5	tabtab	Tables used
6	item_cat	Basic data for catalog

Table 14: Data in the TABTAB table