create_ora.sql

来自「《j2ee经典实例详解》的源代码。原书无附带光盘。介绍的是一个在线银行系统的例子」· SQL 代码 · 共 77 行

drop table LINEITEM;
drop table ORDERS;
drop table VENDOR_PART;
drop table VENDOR;
drop table PART_DETAIL;
drop table PART;

/* Self-ref to identify Bill of Material (BOM)
 * Compound PK 
 */
create table PART (
	PART_NUMBER		VARCHAR(15)	NOT NULL,
	REVISION		NUMERIC(2)	NOT NULL,
	DESCRIPTION		VARCHAR(255)	NULL,
	REVISION_DATE		DATE		NOT NULL,
	BOM_PART_NUMBER		VARCHAR(15)	NULL,
	BOM_REVISION		NUMERIC(2)	NULL,
	PRIMARY KEY (PART_NUMBER, REVISION),
	FOREIGN KEY (BOM_PART_NUMBER, BOM_REVISION) REFERENCES PART (PART_NUMBER, REVISION)
);

/* Bean will be mapped to 2 tables (PART and PART_DETAIL)
 * BLOB column type
 * CLOB column type */
create table PART_DETAIL (
	PART_NUMBER	VARCHAR(15)	NOT NULL,
	REVISION	NUMERIC(2)	NOT NULL,
	SPECIFICATION	CLOB		NULL,
	DRAWING		BLOB		NULL,
	PRIMARY KEY (PART_NUMBER, REVISION)
);

/* PK can be mapped to a primitive PK field type */
create table VENDOR (
	VENDOR_ID	INTEGER		PRIMARY KEY,
	NAME		VARCHAR(30)	NOT NULL,
	ADDRESS		VARCHAR(255)	NOT NULL,
	CONTACT		VARCHAR(255)	NOT NULL,
	PHONE		VARCHAR(30)	NOT NULL
);

/* Can be used for unknown PK 
 * 1-1 to PART
 * Compound FK
 */
create table VENDOR_PART (
	VENDOR_PART_NUMBER	NUMERIC(19)		PRIMARY KEY,
	DESCRIPTION		VARCHAR(255)		NULL,
	PRICE			DOUBLE PRECISION	NOT NULL,
	VENDOR_ID		INTEGER			NOT NULL,
	PART_NUMBER		VARCHAR(15)		NOT NULL,
	PART_REVISION		NUMERIC(2)		NOT NULL,
	FOREIGN KEY (VENDOR_ID) REFERENCES VENDOR (VENDOR_ID),
	FOREIGN KEY (PART_NUMBER, PART_REVISION) REFERENCES PART (PART_NUMBER, REVISION),
	UNIQUE (PART_NUMBER, PART_REVISION)
);

create table ORDERS (
	ORDER_ID	INTEGER		PRIMARY KEY,
	STATUS		CHAR(1)		NOT NULL,
	LAST_UPDATE	DATE		NOT NULL,
	DISCOUNT	NUMERIC(2)	NOT NULL,
	SHIPMENT_INFO	VARCHAR(255)	NULL
);

/* Overlapping PK-FK 
 * Uni-directional to VENDOR_PART */
create table LINEITEM (
	ORDER_ID		INTEGER		NOT NULL,
	ITEM_ID			NUMERIC(3)	NOT NULL,
	QUANTITY		NUMERIC(3)	NOT NULL,
	VENDOR_PART_NUMBER	NUMERIC(19)	NOT NULL,
	FOREIGN KEY (ORDER_ID) REFERENCES ORDERS (ORDER_ID),
	FOREIGN KEY (VENDOR_PART_NUMBER) REFERENCES VENDOR_PART (VENDOR_PART_NUMBER),
	PRIMARY KEY (ORDER_ID, ITEM_ID)
);