bookstore1.sql

java web services how to program

connect 'jdbc:cloudscape:rmi:BookStore1;create=true'
;

drop table Books
;
drop table Orders
;
drop table OrderItems
;
drop table Customers
;

create table Books (
   ISBN varchar (10),
   price real NOT NULL,
   constraint pk_Books primary key (ISBN)
) 
;
create table Customers (
   customerID int DEFAULT AUTOINCREMENT,
   firstName varchar (255) NOT NULL,
   lastName varchar (255) NOT NULL,
   emailAddress varchar (255) NOT NULL,
   streetAddress varchar (255) NOT NULL,
   city varchar (255) NOT NULL,
   state varchar (255) NOT NULL,
   zipCode varchar (255) NOT NULL,
   country varchar (255) NOT NULL,
   creditCardNumber varchar (255) NOT NULL,
   constraint pk_Customers primary key (customerID)
)
;
create table Orders (
   orderID int DEFAULT AUTOINCREMENT,
   customerID int NOT NULL,
   constraint fk_Orders primary key (orderID),
   constraint fk_Customers foreign key (customerID)
      references Customers (customerID)
) 
;
create table OrderItems (
   ISBN varchar (10) NOT NULL,
   orderID int NOT NULL,
   quantity int NOT NULL,
   constraint fk_Books foreign key (ISBN)
      references Books (ISBN), 
   constraint fk_Order foreign key (orderID)
      references Orders (orderID) 
) 
;

insert into Books (ISBN, price) values ('0130895725', 64.95)
;
insert into Books (ISBN, price) values ('0130895717', 64.95)
;
insert into Books (ISBN, price) values ('0130308978', 69.99)
;
insert into Books (ISBN, price) values ('0130341517', 69.99)
;
insert into Books (ISBN, price) values ('0134569555', 69.99)
;
insert into Books (ISBN, price) values ('0130293636', 69.99)
;
insert into Books (ISBN, price) values ('0130284181', 69.99)
;
insert into Books (ISBN, price) values ('0130895601', 69.99)
;