Neoview Guide to Stored Procedures in Java (R2.3, R2.4)

B Sample Database
This appendix presents the Sample Database schemas and tables on which the SPJs in this manual
operate:
• “PERSNL Schema” (page 109)
— “JOB Table” (page 109)
— “EMPLOYEE Table” (page 109)
— “DEPT Table” (page 111)
— “PROJECT Table” (page 113)
• “SALES Schema” (page 116)
— “CUSTOMER Table” (page 116)
— “ORDERS Table” (page 117)
— “ODETAIL Table” (page 118)
— “PARTS Table” (page 120)
• “INVENT Schema” (page 121)
— “SUPPLIER Table” (page 121)
— “PARTSUPP Table” (page 122)
— “PARTLOC Table” (page 124)
PERSNL Schema
The PERSNL schema stores employee data.
JOB Table
CREATE TABLE persnl.job (
jobcode NUMERIC (4) UNSIGNED
NO DEFAULT
NOT NULL
,jobdesc VARCHAR (18)
DEFAULT ' '
NOT NULL
,PRIMARY KEY (jobcode)
);
INSERT INTO persnl.job
VALUES ( 100, 'MANAGER' ),
( 200, 'PRODUCTION SUPV' ),
( 250, 'ASSEMBLER' ),
( 300, 'SALESREP' ),
( 400, 'SYSTEM ANALYST' ),
( 420, 'ENGINEER' ),
( 450, 'PROGRAMMER' ),
( 500, 'ACCOUNTANT' ),
( 600, 'ADMINISTRATOR' ),
( 900, 'SECRETARY' );
UPDATE STATISTICS FOR TABLE persnl.job ON EVERY COLUMN;
EMPLOYEE Table
CREATE TABLE persnl.employee (
empnum NUMERIC (4) UNSIGNED
NO DEFAULT
NOT NULL
PERSNL Schema 109