Schema ORDERPROC

The schema ORDERPROC for the database ORDERCUST implements an information system for processing orders in a small data center. Information is to be recorded on customers, on contacts, on jobs done for these customers and on the services provided for these orders. The schema ORDERPROC contains the following tables: CUSTOMERS, CONTACTS, ORDERS, SERVICE and ORDSTAT.

The figure below shows an overview of the base tables of the ORDERPROC schema and the dependencies between these tables.

Figure 5: Base table for the ORDERPROC schema; referencing foreign keys have a gray background

CUSTOMERS table

The CUSTOMERS table contains information on the customers. In addition to a unique customer number, the table contains the name, address, telephone number and information on the customer's field of activity. The following table definition is used to create the CUSTOMERS table:

CREATE TABLE	customers
(cust_num	INTEGER CONSTRAINT cust_num_primary PRIMARY KEY,
company	CHAR(40) CONSTRAINT company_notnull NOT NULL,
street	CHAR(40),
zip	NUMERIC(5),
city	CHAR(40),
country	CHAR(3),
cust_tel	CHAR(25),
cust_info	CHAR(50)
CONSTRAINT PlausZip	CHECK(country IS NULL OR zip IS NULL OR
	(country = 'D' AND zip >= 00000)
	OR (country <> 'D' ))
)

CUST_NUM is the primary key of the CUSTOMERS table. A CHECK constraint has been defined to check the plausibility of the zip codes. The CUSTOMERS table contains the following data:

cust_ num	company	street	zip	city	cou ntry	cust_tel	cust_info
100	Siemens AG	Otto-Hahn-Ring 6	81739	Munich	D	089/636-8	Electrical
101	Login GmbH	Rosenheimer Str. 34	81667	Munich	D	089/4488870	PC networks
102	JIKO GmbH	Posener Str. 12	30659	Hannover	D	0551/123874	Import/Export
103	Plenzer Trading	Paul-Heyse-Str. 12	80336	Munich	D	089/923764	Fruit trade
104	Freddy's Fishery	Hirschgartenstr. 12	12587	Berlin	D	016/5739921	Unit retail
105	The Poodle Parlor	Am Muehlentor 26	41179	Moenchen- gladbach	D	040/873562	Service
106	Foreign Ltd.	26 West York St.		New York, NY	USA	001703/2386 532	Commercial agency
107	Externa & Co KG	Berner Weg 78	3000	Berne 33	CH		Law firm

Table 4: Data in the CUSTOMERS table

CONTACTS table

The CONTACTS table contains information on contacts for a specific customer. It comprises a unique contact number, the number of the customer from the CUSTOMERS table, the first name, last name, title and telephone number of the contact and information on the person's position and department along with the reason for the contact. The following table definition is used to create the CONTACTS table:

CREATE TABLE	contacts
(contact_num	INTEGER CONSTRAINT contact_num_primary PRIMARY KEY,
cust_num	INTEGER CONSTRAINT contact_cust_num_notnull NOT NULL,
fname	CHAR(25),
lname	CHAR(25) CONSTRAINT name_notnull NOT NULL,
title	CHAR(20),
contact_tel	CHAR(25),
position	CHAR(50),
department	CHAR(30),
contact_info	CHAR(50),
CONSTRAINT	contact_cust_num_ref_customers FOREIGN KEY (cust_num) REFERENCES customers
)

CONTACT_NUM is the primary key for the CONTACTS table. A referential constraint has been defined for the table. The foreign key CUST_NUM refers to the primary key CUSTOMERS.CUST_NUM of the referenced table CUSTOMERS.

The CONTACTS table contains the following data:

cont act_ num	cust _num	fname	lname	title	contact_tel	position	department	contact _info
10	100	Walter	Kuehne	Dr.	089/6361896	CEO	Personnel
11	100	Stefan	Walkers	Mr.	089/63640182	Secretary	Sales
20	101	Roland	Loetzerich	Mr.	089/4488870	Managing director		Networks
25	102	Ewald	Schmidt	Mr.	0551/123873	Training
26	103	Beate	Kredler	Ms.	089/923764	Organization		SQL course
30	104	Xaver	Bauer	Mr.	016/6739921	Sales exec.
35	105	Anke	Buschmann	Ms.	02161/584097	Managing director
40	106	Mary	Davis	Ms.	001703/2386531	Management	Purchasing
41	106	Robert	Heinlein	Mr.	001703/2386532	Trainer	Purchasing

Table 5: Data in the CONTACTS table

ORDSTAT table

The ORDSTAT table allows the order status numbers from the ORDERS table (ORDER_STAT column) to be assigned to the corresponding texts. It contains a unique order status number and the relevant text.

The following table definition is used to create the ORDSTAT table:

CREATE TABLE	ordstat
(ord_stat_num	INTEGER CONSTRAINT ord_stat_num_primary PRIMARY KEY,
ord_stat_text	CHAR(15) CONSTRAINT ord_stat_text_not_null NOT NULL
)

The ORDSTAT table contains the following data:

order_status	ord_stat_text
1	planned
2	contract
3	completed
4	paid
5	archived

Table 6: Data in the ORDSTAT table

ORDERS table

The ORDERS table contains the basic data for an order. It contains a unique order number, references to the customer and the contact person, the order date, the name of the order, the planned and actual completion dates and an order status number. The following table definition is used to create the ORDERS table:

CREATE TABLE	orders
(order_num	INTEGER CONSTRAINT order_num_primary PRIMARY KEY,
cust_num	INTEGER CONSTRAINT o_cust_num_notnull NOT NULL,
contact_num	INTEGER,
order_date	DATE DEFAULT CURRENT_DATE,
order_text	CHAR(30),
actual	DATE,
target	DATE,
order_status	INTEGER DEFAULT 1 CONSTRAINT order_stat_notnull NOT NULL,
CONSTRAINT	o_cust_num_ref_customers FOREIGN KEY (cust_num) REFERENCES customers,
CONSTRAINT	contact_num_ref_contacts FOREIGN KEY (contact_num) REFERENCES contacts,
CONSTRAINT	order_stat_ref_ordstat FOREIGN KEY (order_stat) REFERENCES ordstat(ord_stat_num)
)

ORDER_NUM is the primary key of the ORDERS table. The foreign key CUST_NUM references the primary key CUSTOMERS.CUST_NUM in the CUSTOMERS table, the foreign key CONTACT_NUM references the primary key CONTACTS.CONTACT_NUM in the CONTACTS table, and the foreign key ORDER_STAT references the primary key ORD_STAT_NUM in the ORDSTAT table.
The DEFAULT clause in the column definition for ORDER_DATE sets the current date as the default value using CURRENT_DATE. The DEFAULT clause for ORDER_STAT sets the default value 1. The ORDERS table contains the following data:

ord er_ nu m	cust_ num	conta ct_nu m	order_date	order_text	actual	target	order _stat us
200	102	25	4/15/2009	Staff training	5/2/2009	5/2/2009	5
210	106	40	12/15/2009	Customer management	4/12/2010	4/1/2010	3
211	106	41	12/29/2009	Database draft customers	4/9/2010	4/1/2010	4
250	105	35	1/19/2010	Instruction concerning mail merge		3/3/2010	2
251	105	35	1/19/2010	Customer management		5/2/2010	2
300	101	20	2/16/2010	Network test/comparison			1
305	105	35	4/28/2010	Staff training		5/2/2010	2

Table 7: SQL table ORDERS

SERVICE table

The SERVICE table contains the individual services required by the order. It contains a unique service number, the relevant order number, the date the service was provided, the name of the service, the units in which the service is measured, the number of units, the price per unit, the relevant rate of VAT and the invoice number.

The following table definition is used to create the SERVICE table:

CREATE TABLE	service
(service_num	INTEGER CONSTRAINT service_num_primary PRIMARY KEY,
order_num	INTEGER CONSTRAINT s_order_num_notnull NOT NULL,
service_date	DATE,
service_text	CHAR(25),
service_unit	CHAR(10),
service_total	INTEGER CONSTRAINT service_total_pos CHECK (service_total > 0),
service_price	NUMERIC (5,0),
vat	NUMERIC(2,2),
inv_num	NUMERIC(4,0),
CONSTRAINT	order_num_ref_orders FOREIGN KEY(order_num) REFERENCES orders
)

SERVICE_NUM is the primary key of the SERVICE table. The foreign key ORDER_NUM refers to the primary key ORDERS.ORDER_NUM in the ORDERS table.

The SERVICE table contains the following data:

service_ num	order_ num	service_date	service_text	service_ unit	service _total	service_ price	vat	inv_num
1	200	4/20/2009	Training documentation	Pages	45	75	0.19	3
2	200	4/22/2009	Training	Day	1	1500	0.19	3
3	200	4/23/2009	Training	Day	1	1500	0.19	3
4	211	1/21/2010	Systems analysis	Day	8	1200	0.00	10
5	211	1/28/2010	Database design	Day	10	1200	0.00	10
6	211	2/16/2010	Copies/ transparencies	Pages	30	50	0.19	10
7	211	3/24/2010	Manual	Fixed price	1	200	0.07	10
10	250	2/23/2010	Travel expenses	Fixed price	2	125	0.00
11	250	2/23/2010	Training	Day	1	1200	0.19

Table 8: Data in the SERVICE table