Sams Teach Yourself BEA WebLogic Server 7.0 in 21 Days

Listing C.51 is to be used to create the tables in PointBase. After creating the user, these commands can be run on the console. Alternatively, you can run this file in PointBase by running the command file listed in C.52.

Listing C.51 pointbase_setup.sql

CREATE TABLE "SYSTEM"."CREDIT_CARD_DETAILS" ( "PROFILE_ID" DECIMAL(10,0), "CARD_NUMBER" VARCHAR(20), "CARD_TYPE" VARCHAR(20), "EXPIRATION_MONTH" VARCHAR(20), "EXPIRATION_YEAR" VARCHAR(20) ); COMMIT; CREATE TABLE "SYSTEM"."FLIGHT" ( "AIRLINE_ID" DECIMAL(10,0), "AIRLINE_NAME" VARCHAR(20), "FROM_LOCATION" VARCHAR(20), "TO_LOCATION" VARCHAR(20), "DEPARTURE_TIME" TIME, "ARRIVAL_TIME" TIME, "DURATION" DECIMAL(5,2), "TOTAL_SEATS" DECIMAL(3,0), "FLIGHT_ID" DECIMAL(10,0) NOT NULL ); COMMIT; ALTER TABLE "SYSTEM"."FLIGHT" ADD CONSTRAINT "PK_FLIGHT_ID" PRIMARY KEY ("FLIGHT_ID"); COMMIT; CREATE TABLE "SYSTEM"."FLIGHT_DETAILS" ( "FLIGHT_ID" DECIMAL(10,0) NOT NULL, "FLIGHT_DEPARTURE_DATE" DATE NOT NULL, "PRICE" DECIMAL(6,2), "AVAILABLE_SEATS" DECIMAL(3,0) ); COMMIT; ALTER TABLE "SYSTEM"."FLIGHT_DETAILS" ADD CONSTRAINT "PK_FLIGHT_DETAILS_ COMPOSITE" PRIMARY KEY ( "FLIGHT_ID", "FLIGHT_DEPARTURE_DATE" ); COMMIT; CREATE TABLE "SYSTEM"."PASSENGER_PROFILE" ( "PROFILE_ID" DECIMAL(10,0) NOT NULL, "PASSWORD" VARCHAR(20), "FIRST_NAME" VARCHAR(20), "LAST_NAME" VARCHAR(20), "ADDRESS" VARCHAR(20), "TEL_NO" VARCHAR(20), "EMAIL_ID" VARCHAR(20) ); COMMIT; ALTER TABLE "SYSTEM"."PASSENGER_PROFILE" ADD CONSTRAINT "PK_PROFILE_ID" PRIMARY KEY ( "PROFILE_ID" ); COMMIT; CREATE TABLE "SYSTEM"."TICKET_INFO" ( "TICKET_ID" DECIMAL(30,0) NOT NULL, "FLIGHT_ID" DECIMAL(10,0), "PROFILE_ID" DECIMAL(10,0), "FLIGHT_DEPARTURE_DATE" DATE, "STATUS" VARCHAR(20) ); COMMIT; ALTER TABLE "SYSTEM"."TICKET_INFO" ADD CONSTRAINT "PK_TICKET_ID" PRIMARY KEY ( "TICKET_ID" ); COMMIT; ALTER TABLE "SYSTEM"."CREDIT_CARD_DETAILS" ADD CONSTRAINT "FK_CC_PROFILE_ID" FOREIGN KEY ( "PROFILE_ID" ) REFERENCES "SYSTEM"."PASSENGER_PROFILE" ( "PROFILE_ID" ) ON UPDATE no action ON DELETE no action; COMMIT; ALTER TABLE "SYSTEM"."FLIGHT_DETAILS" ADD CONSTRAINT "FK_FLIGHT_ID" FOREIGN KEY ( "FLIGHT_ID" ) REFERENCES "SYSTEM"."FLIGHT" ( "FLIGHT_ID" ) ON UPDATE no action ON DELETE no action; COMMIT; ALTER TABLE "SYSTEM"."TICKET_INFO" ADD CONSTRAINT "FK_FLIGHT_ID_DATE" FOREIGN KEY ( "FLIGHT_ID", "FLIGHT_DEPARTURE_DATE" ) REFERENCES "SYSTEM"."FLIGHT_ DETAILS" ( "FLIGHT_ID", "FLIGHT_DEPARTURE_DATE" ) ON UPDATE no action ON DELETE no action; COMMIT; ALTER TABLE "SYSTEM"."TICKET_INFO" ADD CONSTRAINT "FK_PROFILE_ID" FOREIGN KEY ( "PROFILE_ID" ) REFERENCES "SYSTEM"."PASSENGER_PROFILE" ( "PROFILE_ID" ) ON UPDATE no action ON DELETE no action; COMMIT;

Listing C.52 setupPointBaseTables.cmd

set POINTBASE_HOME=C:\bea\weblogic700\samples\server\eval\pointbase set CLASSPATH=%POINTBASE_HOME%\lib\pbclient42ECF172.jar;%POINTBASE_HOME%\lib\ pbtools42ECF172.jar;%POINTBASE_HOME%\lib\pbserver42ECF172.jar;%CLASSPATH% java com.pointbase.tools.toolsLoad -driver com.pointbase.jdbc.jdbcUniversalDriver -url jdbc:pointbase:learnweblogic7 -file c:\ibm\unloaddb.sql -user system -password password -log true