For the situations in this chapter, and for the workshops and examples throughout the book, we will use the database from the company Horatio's Woodscrews, Inc. This is a fictitious company that sells…well…woodscrews. This company has a primary database that holds millions of records concerning woodscrews, woodscrew inventory, and woodscrew orders. The following is the code to create the application owner in the oracle database, along with the three primary tables: Woodscrew, woodscrew_inventory, and woodscrew_orders. These tables will be used throughout the book for all examples, so you might want to make note of this information. After building the tables, a few rows are added so we can manipulate them for labs, like this:
create tablespace ws_app_data datafile
'/u01/product/oracle/oradata/orcl/ws_app_data01.dbf' size 100m;
create tablespace ws_app_idx datafile
'/u01/product/oracle/oradata/orcl/ws_app_idx01.dbf' size 100m;
create user ws_app identified by ws_app
default tablespace ws_app_data
temporary tablespace temp;
grant connect, resource to ws_app;
connect ws_app/ws_app;
create table woodscrew (
scr_id number not null,
manufactr_id varchar2(20) not null,
scr_type varchar2(20),
thread_cnt number,
length number,
head_config varchar2(20),
constraint pk_woodscrew primary key (scr_id, manufactr_id)
using index tablespace ws_app_idx);
create index woodscrew_identity
on woodscrew(scr_type, thread_cnt, length, head_config)
tablespace ws_app_idx;
create table woodscrew_inventory (
scr_id number not null,
manufactr_id varchar2(20) not null,
warehouse_id number not null,
region varchar2(20),
count number,
lot_price number);
create table woodscrew_orders (
ord_id number not null,
ord_date date,
cust_id number not null,
scr_id number not null,
ord_cnt number,
warehouse_id number not null,
region varchar2(20),
constraint pk_wdscr_orders primary key (ord_id, ord_date)
using index tablespace ws_app_idx);
---- Now, add rows to the tables.
insert into woodscrew values (
1000, 'Tommy Hardware', 'Finish', 30, 1.5, 'Phillips');
insert into woodscrew values (
1000, 'Balaji Parts, Inc.', 'Finish', 30, 1.5, 'Phillips');
insert into woodscrew values (
1001, 'Tommy Hardware', 'Finish', 30, 1, 'Phillips');
insert into woodscrew values (
1001, 'Balaji Parts, Inc.', 'Finish', 30, 1, 'Phillips');
insert into woodscrew values (
1002, 'Tommy Hardware', 'Finish', 20, 1.5, 'Phillips');
insert into woodscrew values (
1002, 'Balaji Parts, Inc.', 'Finish', 20, 1.5, 'Phillips');
insert into woodscrew values (
1003, 'Tommy Hardware', 'Finish', 20, 1, 'Phillips');
insert into woodscrew values (
1003, 'Balaji Parts, Inc.', 'Finish', 20, 1, 'Phillips');
insert into woodscrew values (
1004, 'Tommy Hardware', 'Finish', 30, 2, 'Phillips');
insert into woodscrew values (
1004, 'Balaji Parts, Inc.', 'Finish', 30, 2, 'Phillips');
insert into woodscrew values (
1005, 'Tommy Hardware', 'Finish', 20, 2, 'Phillips');
insert into woodscrew values (
1005, 'Balaji Parts, Inc.', 'Finish', 20, 2, 'Phillips');
insert into woodscrew_inventory values (
1000, 'Tommy Hardware', 200, 'NORTHEAST', 3000000, .01);
insert into woodscrew_inventory values (
1000, 'Tommy Hardware', 350, 'SOUTHWEST', 1000000, .01);
insert into woodscrew_inventory values (
1000, 'Balaji Parts, Inc.', 450, 'NORTHWEST', 1500000, .015);
insert into woodscrew_inventory values (
1005, 'Balaji Parts, Inc.', 450, 'NORTHWEST', 1700000, .017);
insert into woodscrew_orders values (
20202, '2003-09-22 00:02:02', 2001, 1000, 20000, 64114, 'NORTHEAST');
insert into woodscrew_orders values (
20203, '2003-09-22 00:02:04', 2001, 1001, 10000, 64114, 'NORTHEAST');
insert into woodscrew_orders values (
20204, '2003-09-22 00:02:06', 2002, 1002, 10000, 64114, 'NORTHWEST');
insert into woodscrew_orders values (
20205, '2003-09-22 00:02:08', 2002, 1003, 30000, 64114, 'NORTHWEST');
insert into woodscrew_orders values (
20206, '2003-10-04 00:02:12', 2002, 1004, 10000, 80903, 'SOUTHWEST');
insert into woodscrew_orders values (
20207, '2003-10-04 00:02:14', 2001, 1003, 20000, 80903, 'SOUTHWEST');
insert into woodscrew_orders values (
20208, '2003-10-04 00:02:16', 2002, 1002, 30000, 64114, 'SOUTHWEST');
insert into woodscrew_orders values (
20209, '2003-10-04 00:02:18', 2003, 1001, 40000, 90210, 'NORTHWEST');
insert into woodscrew_orders values (
20210, '2003-11-04 00:02:20', 2005, 1000, 10000, 83401, 'SOUTHEAST');
insert into woodscrew_orders values (
20211, '2003-11-04 00:02:22', 2002, 1005, 10000, 83401, 'SOUTHEAST');
insert into woodscrew_orders values (
20212, '2003-11-04 00:02:24', 2001, 1004, 10000, 64114, 'NORTHEAST');
insert into woodscrew_orders values (
20213, '2003-11-04 00:02:26', 2003, 1003, 10000, 64114, 'NORTHEAST');
insert into woodscrew_orders values (
20214, '2003-12-04 00:02:28', 2002, 1001, 20000, 64114, 'SOUTHEAST');
insert into woodscrew_orders values (
20215, '2003-12-04 00:02:30', 2001, 1000, 10000, 80903, 'NORTHWEST');
insert into woodscrew_orders values (
20216, '2003-12-04 00:02:32', 2005, 1001, 50000, 80903, 'SOUTHWEST');
insert into woodscrew_orders values (
20217, '2003-12-04 00:02:34', 2003, 1003, 70000, 90210, 'SOUTHWEST');
commit;
