SQL Script

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