Create and Build the Retailer and Supplier Schemas

These schema scripts set up the Retailer and Supplier environment, users, tablespaces, quota, and so on. They also create and then populate the schemas.

• SQL Example 1: Set up the Retailer and Supplier Environment -- BuildAll.sql. This calls:

• SQL Example 2: Create and Populate the Retailer-Supplier Schema -- BuildSchema.sql

SQL Example 1: Set up the Retailer and Supplier Environment -- BuildAll.sql

BuildAll.sql sets up the environment for the Retailer and Supplier schema. It calls BuildSchema.sql which creates the Retailer and Supplier schemas and then populates them with data.

--
-- buildall.sql builds all the schemas
-- 
accept sysPswd prompt 'Enter the system password                                       
> ' hide
accept cStr    prompt 'Enter the connect string if any, including ''@'' sign (ie 
@atp-1) > '
connect system/&sysPswd&cStr
drop user retailer cascade
/
drop user supplier cascade
/
col tablespace_name head "Available Tablespaces"
select tablespace_name from dba_tablespaces
/
prompt
accept userTbsp prompt 'What is the DEFAULT Tablespace name ?   > '
accept tempTbsp prompt 'What is the TEMPORARY Tablespace name ? > '
prompt

create user retailer identified by retailer
default tablespace &userTbsp
temporary tablespace &tempTbsp
quota unlimited on &userTbsp
/
grant connect, resource, create any directory to retailer
/
create user supplier identified by supplier
default tablespace &userTbsp
temporary tablespace &tempTbsp
quota unlimited on &userTbsp
/
grant connect, resource, create any directory to supplier
/
prompt Now populating Supplier, hit [Return]
pause
connect supplier/supplier&cStr
@buildSchema
prompt Now populating Retailer, hit [Return]
pause
connect retailer/retailer&cStr
@buildSchema
prompt done !

SQL Example 2: Create and Populate the Retailer-Supplier Schema -- BuildSchema.sql

BuildSchema.sql is called from BuildAll.sql. It creates, populates, and builds the Retailer and Supplier schema.

This script creates and populates the following five tables:

• Customers

• Suppliers

• Inventory_item

• Ord

• Line_item

See Figure 13-3 for an illustration of this schema.

--
-- buildSchema.sql drops then creates all the tables for the B2B XML Application
--
drop trigger line_item_insert_trigger;
drop table line_item;
drop table ord;
drop table customer;
drop table inventory_item;
drop table supplier;
drop sequence ord_seq;
drop sequence customer_seq;
drop sequence line_item_seq;
drop sequence supplier_seq;
drop sequence inventory_item_seq;

prompt
prompt Creating sequences...
prompt
prompt
prompt Creating sequence ORD_SEQ
create sequence ord_seq start with 101;

prompt Creating sequence CUSTOMER_SEQ
create sequence customer_seq start with 201;

prompt Creating sequence LINE_ITEM_SEQ
create sequence line_item_seq start with 1001;

prompt Creating sequence SUPPLIER_SEQ
create sequence supplier_seq start with 301;

prompt Creating sequence INVENTORY_ITEM_SEQ
create sequence inventory_item_seq start with 401;

prompt
prompt
prompt Creating tables...
prompt
prompt
--
-- ***** Create table CUSTOMERS ******
--
prompt Creating table CUSTOMER
create table customer(
  id         number,
  name       varchar2(30),
  status     varchar2(8),
  web_site   varchar2(40),
  constraint 
     customer_pk 
     primary key (id)
);
--
-- ***** Create table SUPPLIERS ******
--
prompt Creating table SUPPLIER
create table supplier(
  id         number,
  name       varchar2(30),
  web_site   varchar2(40),
  constraint 
     supplier_pk 
     primary key (id)
);
--
-- ***** Create table INVENTORY_ITEM ******
--
prompt Creating table INVENTORY_ITEM
create table inventory_item(
  id             number,
  description    varchar2(30),
  price          number(8,2),
  onhand         number,
  supplier_id    number,
  constraint
     inventory_item_pk 
     primary key (id),
  constraint 
     supplied_by
     foreign key (supplier_id) references supplier
);
--
-- ***** Create table ORD ******
--
prompt Creating table ORD
create table ord (
  id             number,
  orderDate      date,
  contactName    varchar2(30),
  trackingNo     varchar2(20),
  status         varchar2(10),
  customer_id    number,
  constraint 
     ord_pk 
     primary key (id),
  constraint
     order_placed_by 
     foreign key (customer_id) references customer
);

prompt Creating table LINE_ITEM
create table line_item(
  id             number,
  quantity       number,
  item_id        number,
  ord_id         number,
  discount       number,
  constraint
     line_item_pk 
     primary key (id),
  constraint 
     item_ordered_on 
     foreign key (ord_id) references ord,
  constraint 
     order_for_item
     foreign key (item_id) references inventory_item
);

prompt
prompt
prompt Inserting data...
prompt
prompt
prompt Inserting values into SUPPLIER and INVENTORY_ITEM
prompt

insert into supplier values( supplier_seq.nextval,'DELL','http://dell.com');
insert into inventory_item values( inventory_item_seq.nextval,'Optiplex GXPro', 
1500, 27, supplier_seq.currval );
insert into inventory_item values( inventory_item_seq.nextval,'Inspiron 7000', 
2500, 49, supplier_seq.currval );
insert into inventory_item values( inventory_item_seq.nextval,'PowerEdge 6300', 
7500, 16, supplier_seq.currval );
insert into inventory_item values( inventory_item_seq.nextval,'Inspiron 3000', 
2500, 0, supplier_seq.currval );
insert into inventory_item values( inventory_item_seq.nextval,'Inspiron 2000', 
2500, 0, supplier_seq.currval );

insert into supplier values( supplier_seq.nextval, 'HP', 'http://hp.com');
insert into inventory_item values( inventory_item_seq.nextval, 'LaserJet 6MP', 
899, 123, supplier_seq.currval );
insert into inventory_item values( inventory_item_seq.nextval, 'Jornada 2000', 
450, 1198, supplier_seq.currval );
insert into inventory_item values( inventory_item_seq.nextval, 'HP 12C', 69, 
801, supplier_seq.currval );
insert into inventory_item values( inventory_item_seq.nextval, 'LaserJet 2', 69, 
3, supplier_seq.currval );


insert into inventory_item values( inventory_item_seq.nextval,'Jaz PCMCIA 
adapter', 125, 54, supplier_seq.currval );
insert into inventory_item values( inventory_item_seq.nextval,'8860 Digital 
phone', 499, 12, supplier_seq.currval );
insert into inventory_item values( inventory_item_seq.nextval,'Jaz carrying 
bag', 20, 66, supplier_seq.currval );



insert into inventory_item values(supplier_seq.nextval,'Intel', 
'http://www.intel.com');
prompt Inserting values into CUSTOMER
prompt


insert into ord values( ord_seq.nextval, sysdate, 'George', 'AX'||ord_
seq.currval, 'Pending', 201);
insert into line_item values (line_item_seq.nextval, 2, 410,ord_seq.currval, 0);
insert into line_item values (line_item_seq.nextval, 1, 402,ord_seq.currval, 0);
insert into line_item values (line_item_seq.nextval, 1, 406,ord_seq.currval, 0);

insert into ord values(ord_seq.nextval,sysdate,'Elaine','AX'||ord_seq.currval, 
create trigger line_item_insert_trigger
  before insert on line_item for each row
  begin
     select line_item_seq.nextval into :new.id from dual ;
  end;
/

commit;