tutorial.sql

java 的关于web serive教程

// Create tables for all Java EE Tutorial examples

// Create tables for Web bookstore applications

CREATE TABLE WEB_BOOKSTORE_BOOKS
(bookId VARCHAR(8),
surname VARCHAR(24),
firstName VARCHAR(24),
title VARCHAR(96),
price FLOAT,
onSale SMALLINT,
calendar_year INT,
description VARCHAR(30),
inventory INT);

INSERT INTO WEB_BOOKSTORE_BOOKS VALUES('201', 'Duke', '',
 'My Early Years: Growing up on *7',
 30.75, 0, 1995, 'What a cool book.', 20);

INSERT INTO WEB_BOOKSTORE_BOOKS VALUES('202', 'Jeeves', '',
 'Web Servers for Fun and Profit', 40.75, 1,
 2000, 'What a cool book.', 20);

INSERT INTO WEB_BOOKSTORE_BOOKS VALUES('203', 'Masterson', 'Webster',
 'Web Components for Web Developers',
 27.75, 0, 2000, 'What a cool book.', 20);

INSERT INTO WEB_BOOKSTORE_BOOKS VALUES('205', 'Novation', 'Kevin',
 'From Oak to Java: The Revolution of a Language',
 10.75, 1, 1998, 'What a cool book.', 20);

INSERT INTO WEB_BOOKSTORE_BOOKS VALUES('206', 'Gosling', 'James',
 'Java Intermediate Bytecodes', 30.95, 1,
 2000, 'What a cool book.', 20);

INSERT INTO WEB_BOOKSTORE_BOOKS VALUES('207', 'Thrilled', 'Ben',
 'The Green Project: Programming for Consumer Devices',
 30.00, 1, 1998, 'What a cool book', 20);

INSERT INTO WEB_BOOKSTORE_BOOKS VALUES('208', 'Tru', 'Itzal',
 'Duke: A Biography of the Java Evangelist',
 45.00, 0, 2001, 'What a cool book.', 20);


// Create tables for CMP roster application (examples/ejb/cmproster)
 
CREATE TABLE EJB_ROSTER_LEAGUE (
        id VARCHAR(8) CONSTRAINT pk_league PRIMARY KEY,
        name VARCHAR(24),
        sport VARCHAR(24));

CREATE TABLE EJB_ROSTER_PLAYER (
        id VARCHAR(8) CONSTRAINT pk_player PRIMARY KEY,
        name VARCHAR(24),
        position VARCHAR(24),
        salary VARCHAR(24));

CREATE TABLE EJB_ROSTER_TEAM (
        id VARCHAR(8) CONSTRAINT pk_team PRIMARY KEY,
        name VARCHAR(24),
        city VARCHAR(24),
        league_id VARCHAR(8),
        CONSTRAINT fk_league_id FOREIGN KEY (league_id) REFERENCES EJB_ROSTER_LEAGUE(id));

CREATE TABLE EJB_ROSTER_TEAM_PLAYER (
        player_id VARCHAR(8),
        team_id VARCHAR(8),
        CONSTRAINT fk_player_id FOREIGN KEY (player_id) REFERENCES EJB_ROSTER_PLAYER(id),
        CONSTRAINT fk_team_id FOREIGN KEY (team_id) REFERENCES EJB_ROSTER_TEAM(id));

// Create tables for CMP order application (examples/ejb/cmporder)
 
// Self-reference to identify Bill of Material (BOM)
// Compound PK 
 
CREATE TABLE EJB_ORDER_PART (
	PARTNUMBER		VARCHAR(15)	NOT NULL,
	REVISION		NUMERIC(2)      NOT NULL,
	DESCRIPTION		VARCHAR(255),
	REVISIONDATE		TIMESTAMP       NOT NULL,
        SPECIFICATION	CLOB(10K),
        DRAWING		BLOB(10K),
	BOMPARTNUMBER           VARCHAR(15),
	BOMREVISION		NUMERIC(2) ,
	PRIMARY KEY (PARTNUMBER, REVISION)
);

ALTER TABLE EJB_ORDER_PART
	add CONSTRAINT FK_1 FOREIGN KEY (BOMPARTNUMBER, BOMREVISION) 
                REFERENCES EJB_ORDER_PART (PARTNUMBER, REVISION);

// Bean will be mapped to 2 tables (PART and PART_DETAIL)
// BLOB column type
// CLOB column type
 
//CREATE TABLE EJB_ORDER_PART_DETAIL (
//	PARTNUMBER	VARCHAR(15)	NOT NULL,
//	REVISION	NUMERIC(2)      NOT NULL,
//	SPECIFICATION	CLOB(10K),
//	DRAWING		BLOB(10K),
//	PRIMARY KEY (PARTNUMBER, REVISION)
//);

// PK can be mapped to a primitive PK field type

CREATE TABLE EJB_ORDER_VENDOR (
	VENDORID        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 map to PART
// Compound FK
 
CREATE TABLE EJB_ORDER_VENDOR_PART (
	VENDORPARTNUMBER	BIGINT                  PRIMARY KEY,
	DESCRIPTION		VARCHAR(255),
	PRICE			DOUBLE PRECISION        NOT NULL,
	VENDORID		INTEGER                 NOT NULL,
	PARTNUMBER		VARCHAR(15)		NOT NULL,
	PARTREVISION		NUMERIC(2)              NOT NULL,
	CONSTRAINT FK_2 FOREIGN KEY (VENDORID) REFERENCES EJB_ORDER_VENDOR (VENDORID),
	CONSTRAINT FK_3 FOREIGN KEY (PARTNUMBER, PARTREVISION) REFERENCES EJB_ORDER_PART (PARTNUMBER, REVISION),
	UNIQUE (PARTNUMBER, PARTREVISION)
);

CREATE TABLE EJB_ORDER_SEQUENCE_GENERATOR (
        GEN_KEY         VARCHAR(255),
        GEN_VALUE       NUMERIC(20)
);

CREATE TABLE EJB_ORDER_ORDER (
	ORDERID         INTEGER         PRIMARY KEY,
	STATUS		CHAR(1)		NOT NULL,
	LASTUPDATE	TIMESTAMP       NOT NULL,
	DISCOUNT	NUMERIC(2)      NOT NULL,
	SHIPMENTINFO	VARCHAR(255)	
);

// Overlapping PK-FK 
// Uni-directional to EJB_ORDER_VENDOR_PART
 
CREATE TABLE EJB_ORDER_LINEITEM (
	ORDERID                 INTEGER         NOT NULL,
	ITEMID			NUMERIC(3)      NOT NULL,
	QUANTITY		NUMERIC(3)      NOT NULL,
	VENDORPARTNUMBER	BIGINT          NOT NULL,
	CONSTRAINT FK_4 FOREIGN KEY (ORDERID) REFERENCES EJB_ORDER_ORDER (ORDERID),
	CONSTRAINT FK_5 FOREIGN KEY (VENDORPARTNUMBER) REFERENCES EJB_ORDER_VENDOR_PART (VENDORPARTNUMBER),
	PRIMARY KEY (ORDERID, ITEMID)
);

// Create tables for online banking application (Duke's Bank)
// and seed the BANK_NEXT_ID table with initial values

CREATE TABLE BANK_ACCOUNT (
    account_id VARCHAR(8) CONSTRAINT pk_account PRIMARY KEY,
    type VARCHAR(24),
    description VARCHAR(30),
    balance NUMERIC(10,2),
    credit_line NUMERIC(10,2),
    begin_balance NUMERIC(10,2),
    begin_balance_time_stamp TIMESTAMP);

CREATE TABLE BANK_CUSTOMER (
    customer_id VARCHAR(8) CONSTRAINT pk_customer PRIMARY KEY,
    last_name VARCHAR(30),
    first_name VARCHAR(30),
    middle_initial VARCHAR(1),
    street VARCHAR(40),
    city VARCHAR(40),
    state VARCHAR(2),
    zip VARCHAR(5),
    phone VARCHAR(16),
    email VARCHAR(30));

CREATE TABLE BANK_TX (
    tx_id VARCHAR(8) CONSTRAINT pk_tx PRIMARY KEY,
    account_id VARCHAR(8),
    time_stamp TIMESTAMP,
    amount NUMERIC(10,2),
    balance NUMERIC(10,2),
    description VARCHAR(30),
    CONSTRAINT fk_tx_account_id FOREIGN KEY (account_id) REFERENCES BANK_ACCOUNT(account_id));

CREATE TABLE BANK_CUSTOMER_ACCOUNT_XREF (
    customer_id VARCHAR(8), 
    account_id VARCHAR(8),
    CONSTRAINT fk_bank_customer_id FOREIGN KEY (customer_id) REFERENCES BANK_CUSTOMER(customer_id),
    CONSTRAINT fk_bank_account_id FOREIGN KEY (account_id) REFERENCES BANK_ACCOUNT(account_id));

CREATE TABLE BANK_NEXT_ID (
    beanName VARCHAR(30) CONSTRAINT pk_next_id PRIMARY KEY,
    id NUMERIC);

// For Persistence Order example
INSERT INTO EJB_ORDER_SEQUENCE_GENERATOR
    VALUES ('VENDOR_PART_ID',1);

// Duke's bank

INSERT INTO BANK_NEXT_ID 
    VALUES ('customer', 202);

INSERT INTO BANK_NEXT_ID
    VALUES ('account', 5050);

INSERT INTO BANK_NEXT_ID
    VALUES ('tx', 100);

INSERT INTO BANK_ACCOUNT VALUES
('5005', 'Money Market', 'Hi Balance', 4000.00, 0.00, 3500.00, '2003-07-28 23:03:20');
 
INSERT INTO BANK_ACCOUNT VALUES
('5006', 'Checking', 'Checking', 85.00, 0.00, 66.54, '2003-07-21 03:12:00');

INSERT INTO BANK_ACCOUNT VALUES
('5007', 'Credit', 'Visa', 599.18, 5000.00, 166.08, '2003-07-23 10:13:54');

INSERT INTO BANK_ACCOUNT VALUES
('5008', 'Savings', 'Super Interest Account', 55601.35, 0.00, 5433.89, '2003-07-15 12:55:33');

INSERT INTO BANK_CUSTOMER VALUES
('200', 'Jones', 'Richard', 'K',
 '88 Poplar Ave.', 'Cupertino', 'CA', '95014',
 '408-123-4567', '[e-mail]');

INSERT INTO BANK_CUSTOMER VALUES
('201', 'Jones', 'Mary', 'R',
 '88 Poplar Ave.', 'Cupertino', 'CA', '95014',
 '408-123-4567', '[e-mail]');

INSERT INTO BANK_CUSTOMER_ACCOUNT_XREF VALUES
('200', '5005');