// Create tables for all Java EE Tutorial examples// Create tables for Web bookstore applicationsCREATE 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 typeCREATE 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 valuesCREATE 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 exampleINSERT INTO EJB_ORDER_SEQUENCE_GENERATOR    VALUES ('VENDOR_PART_ID',1);// Duke's bankINSERT 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', 'rhill@j2ee.com');INSERT INTO BANK_CUSTOMER VALUES('201', 'Jones', 'Mary', 'R', '88 Poplar Ave.', 'Cupertino', 'CA', '95014', '408-123-4567', 'mhill@j2ee.com');INSERT INTO BANK_CUSTOMER_ACCOUNT_XREF VALUES('200', '5005');