schema.sql

java实现浏览器等本地桌面的功能

-- File containing schema for demo database

create table category (
    catid char(20) not null,
    locale varchar(10) not null,
    name varchar(80) null,
    description varchar(255) null,
    imageuri varchar(80) null,
    constraint pk_category primary key (catid, locale)
)
;

create table lodging (
    lodgingid char(20) not null,
    locale varchar(10) not null,
    location varchar(30) not null,
    name varchar(80) not null,
    description varchar(255) not null,    
    price decimal(10,2) null,
    imageuri varchar(80) not null,   
    constraint pk_lodging primary key (lodgingid , locale )
)
;

create table package (
    packageid char(20) not null,
    catid char(20) not null,
    locale varchar(10) not null,
    location varchar(30) not null,
    price decimal(10,2) not null,
    name varchar(80) null,
    description varchar(255) null,
    imageuri varchar(80) not null,
    lodgingid char(20) not null,
    constraint pk_package primary key (packageid, locale) ,
    constraint fk_package_1 foreign key (catid, locale)
        references category (catid, locale),
    constraint fk_package_2 foreign key (lodgingid, locale)
        references lodging (lodgingid, locale)
)
;

create table activity (
    activityid char(20) not null,
    locale varchar(10) not null,
    location varchar(30) not null,
    name varchar(80) not null,
    description varchar(255) not null,    
    price decimal(10,2) null,
    imageuri varchar(80) not null,   
    constraint pk_activity primary key (activityid , locale )
)
;

create table activitylist (
    packageid char(20) not null,
    activityid char(20) not null,
    locale varchar(10) not null,
    constraint pk_activitylist primary key (packageid , activityid ,locale),
    constraint fk_activitylist_1 foreign key (packageid , locale)
        references package (packageid, locale),
    constraint fk_activitylist_2 foreign key (activityid , locale)
        references activity(activityid , locale)
)
;

create table transportation (
    transportationid char(20) not null,
    locale varchar(10) not null,
    origin varchar(30) not null,
    destination varchar(30) not null,
    carrier varchar(80) not null,
    name varchar(80) not null,
    departuretime varchar(80) not null, 
    arrivaltime varchar(80) not null,
    description varchar(255) not null,  
    class varchar(20) not null,
    price decimal(10,2) null,
    imageuri varchar(80) not null,   
    constraint pk_transportation primary key (transportationid , locale )
)
;

create table signon (
    username varchar(25) not null,
    password varchar(25)  not null,
    constraint pk_signon primary key (username)
)
;

create table account (
    userid varchar(80) not null,
    email varchar(80) not null,
    firstname varchar(80) not null,
    lastname varchar(80) not null,
    addr1 varchar(80) not null,
    addr2 varchar(40) null,
    city varchar(80) not  null,
    state varchar(80) not null,
    zip varchar(20) not null,
    country varchar(20) not null,
    phone varchar(80) not null,
    constraint pk_account primary key (userid)
)
;

INSERT INTO category VALUES ('ISLAND', 'en_US', 'Island Adventures', 'Experience an island paradise in a way fit for your needs.','http://azonic.sfbay.sun.com/adventure/images/Island_Adventures.gif')
;
INSERT INTO category VALUES ('JUNGLE', 'en_US', 'Jungle Adventures', 'Experience a jungle paradise in a way fit for your needs.','http://azonic.sfbay.sun.com/adventure/images/Jungle_Adventures.gif')
;
INSERT INTO category VALUES ('MOUNTAIN', 'en_US', 'Mountain Adventures', 'Experience an elevated paradise with a view.','http://azonic.sfbay.sun.com/adventure/images/Mountain_Adventures.gif')
;
INSERT INTO category VALUES ('ORBITAL', 'en_US', 'Orbital Adventures', 'Experience a vacuum paradise with a beautiful view and where no one can hear you scream.','http://azonic.sfbay.sun.com/adventure/images/Space_Adventures.gif')
;
INSERT INTO category VALUES ('WESTERN', 'en_US', 'Western Adventures', 'Enjoy the Wild West.','http://azonic.sfbay.sun.com/adventure/images/Western_Adventures.gif')
;
INSERT INTO category VALUES ('SOUTH_POLE', 'en_US', 'South Pole Adventures', 'Experience a frozen paradise in a way fit for your needs.','http://azonic.sfbay.sun.com/adventure/images/SouthPole_Adventures.gif')
;


INSERT INTO lodging VALUES ('LODG-1', 'en_US', 'The Island of Maui', 'Budget Hotel','per night single ocuppancy' ,265.00,'http://azonic.sfbay.sun.com/adventure/images/Budget_Hotel_Icon.gif')
;
INSERT INTO lodging VALUES ('LODG-2', 'en_US', 'The Islands of the Bahamas', 'Downtown Luxury Hotel', 'per night single ocuppancy' ,260.00,'http://azonic.sfbay.sun.com/adventure/images/Luxury_Hotel_Icon.gif')
;
INSERT INTO lodging VALUES ('LODG-3', 'en_US', 'The Islands of Tahiti', 'Budget Hotel','per night single ocuppancy' ,187.00,'http://azonic.sfbay.sun.com/adventure/images/Budget_Hotel_Icon.gif')
;
INSERT INTO lodging VALUES ('LODG-4', 'en_US', 'Amazon Jungle', 'Jungle Cave Hotel','per night single ocuppancy' ,255.00,'http://azonic.sfbay.sun.com/adventure/images/Cave_Hotel_Icon.gif')
;
INSERT INTO lodging VALUES ('LODG-5', 'en_US', 'Amazon Jungle', 'Jungle Tent Hotel','per night single ocuppancy' ,156.00,'http://azonic.sfbay.sun.com/adventure/images/Tent_Hotel_Icon.gif')
;
INSERT INTO lodging VALUES ('LODG-6', 'en_US', 'Mt.Kilimanjaro', 'Mountain Cabin Hotel','per night single ocuppancy' ,195.00,'http://azonic.sfbay.sun.com/adventure/images/Log_Cabin_Hotel_Icon.gif')
;
INSERT INTO lodging VALUES ('LODG-7', 'en_US', 'Mt.Kilimanjaro', 'Mountain Cave Hotel','per night single ocuppancy' ,187.00,'http://azonic.sfbay.sun.com/adventure/images/Cave_Hotel_Icon.gif')
;
INSERT INTO lodging VALUES ('LODG-8', 'en_US', 'Space', 'Space Hotel','per night single ocuppancy' ,49887.00,'http://azonic.sfbay.sun.com/adventure/images/Space_Hotel_Icon.gif')
;
INSERT INTO lodging VALUES ('LODG-9', 'en_US', 'Antarctica', 'Snow Cave Hotel','per night single ocuppancy' ,289.00,'http://azonic.sfbay.sun.com/adventure/images/Snow_Cave_Hotel_Icon.gif')
;
INSERT INTO lodging VALUES ('LODG-10', 'en_US', 'Antarctica', 'Icy Igloo' ,'per night single ocuppancy' ,267.00,'http://azonic.sfbay.sun.com/adventure/images/Igloo_Hotel_Icon.gif')
;
INSERT INTO lodging VALUES ('LODG-11', 'en_US', 'Texas', 'Old Western Hotel' ,'per night single ocuppancy' ,227.00,'http://azonic.sfbay.sun.com/adventure/images/Budget_Hotel_Icon.gif')
;
INSERT INTO lodging VALUES ('LODG-12', 'en_US', 'Texas', 'Budget Hotel' ,'per night single ocuppancy' ,189.00,'http://azonic.sfbay.sun.com/adventure/images/Budget_Hotel_Icon.gif')
;

INSERT INTO  package VALUES ('PACK-1','ISLAND', 'en_US', 'The Island of Maui', 1115.00,'Maui Survival Adventure', 'Practice your survival skills in an island paradise.', 'http://azonic.sfbay.sun.com/adventure/images/Island_Survival.gif','LODG-1')
;
INSERT INTO  package VALUES ('PACK-2','ISLAND', 'en_US', 'The Islands of the Bahamas', 1135.00,'Bahamas Relaxation trip', 'Relax, unwind and enjoy in an island paradise.', 'http://azonic.sfbay.sun.com/adventure/images/Island_Relax.gif','LODG-2')
;
INSERT INTO  package VALUES ('PACK-3','ISLAND', 'en_US', 'The Islands of Tahiti', 1269.00,'Tahiti Snorkeling Adventure', 'Practice your snorkeling skills in an island paradise.', 'http://azonic.sfbay.sun.com/adventure/images/Island_Snorkeling.gif','LODG-3')
;
INSERT INTO  package VALUES ('PACK-4','JUNGLE','en_US', 'Amazon Jungle', 1756.00,'Amazon Appreciation Adventure','Practice your appreciation skills in a jungle paradise.','http://azonic.sfbay.sun.com/adventure/images/Jungle_Appreciation.gif','LODG-4')
;
INSERT INTO  package VALUES ('PACK-5','JUNGLE','en_US', 'Amazon Jungle', 1213.00,'Amazon Survival Adventure','Explore the amazon rainforest.','http://azonic.sfbay.sun.com/adventure/images/Jungle_Survival.gif','LODG-5')
;
INSERT INTO  package VALUES ('PACK-6','MOUNTAIN','en_US', 'Mt.Kilimanjaro', 1495.00,'Mountain Climbing Adventure','Practice your climing skills in an elevated paradise.','http://azonic.sfbay.sun.com/adventure/images/Mountain_Climbing.gif','LODG-6')
;
INSERT INTO  package VALUES ('PACK-7','MOUNTAIN','en_US', 'Mt.Kilimanjaro', 1676.00,'Mountain Relaxation Adventure','Practice your relaxation skills in a elevated paradise.','http://azonic.sfbay.sun.com/adventure/images/Mountain_Relax.gif','LODG-7')
;
INSERT INTO  package VALUES ('PACK-8','ORBITAL','en_US', 'Space', 1999999.00,'Orbital Appreciation Adventure', 'See the earth from a new perspective.', 'http://azonic.sfbay.sun.com/adventure/images/Space_Appreciation.gif','LODG-8')
;
INSERT INTO  package VALUES ('PACK-9','SOUTH_POLE','en_US', 'Antarctica', 19957.00,'South Pole Survival Adventure', 'Practice your survival skills in a frozen paradise.', 'http://azonic.sfbay.sun.com/adventure/images/SouthPole_Survival.gif','LODG-9')
;
INSERT INTO  package VALUES ('PACK-10','SOUTH_POLE','en_US', 'Antarctica', 18764.00,'South Pole Relaxation Adventure', 'Practice your relaxation skills in a frozen paradise.', 'http://azonic.sfbay.sun.com/adventure/images/SouthPole_Relax.gif','LODG-10')
;
INSERT INTO  package VALUES ('PACK-11','WESTERN' ,'en_US', 'Texas', 764.00,'Urban Cowboy Adventure', 'Practice your cowboy skills in the wild wild west.', 'http://azonic.sfbay.sun.com/adventure/images/Western_Urban_Cowboy.gif','LODG-11')
;
INSERT INTO  package VALUES ('PACK-12','WESTERN', 'en_US', 'Texas', 1862.00,'Dude Ranch Adventure', 'Practice your cowboy skills in a western paradise.', 'http://azonic.sfbay.sun.com/adventure/images/Western_Dude_Ranch.gif','LODG-12')
;

INSERT INTO activity VALUES ('ACTY-1', 'en_US', 'The Island of Maui', 'Snorkeling','Learn about life under the sea',100.00,'http://azonic.sfbay.sun.com/adventure/images/Activity_Boat_Ride_Icon.gif')
;
INSERT INTO activity VALUES ('ACTY-2', 'en_US', 'The Island of Maui','Helicopter Ride' , 'Get a spectacular view of the island',125.00,'http://azonic.sfbay.sun.com/adventure/images/Activity_Helicopter_Icon.gif')
;
INSERT INTO activity VALUES ('ACTY-3', 'en_US', 'The Island of Maui','Surfing', 'Experience the thrill - ride the waves',139.00,'http://azonic.sfbay.sun.com/adventure/images/Activity_Surfing_Icon.gif')