Home   Downloads > Read Me

Readme.txt

Instructions for creating the Global Widgets database

Version 4 - June 26, 2004

This file contains full instructions for creating the Global Widgets database that is used in the Oracle Press book "Oracle Discoverer Handbook", and will be used in the "Become an expert in Oracle Discoverer Administration" series.

These instructions are broken into the following eight sections:

Section 1: ERD Diagram
Section 2: Explanation of Directory structure
Section 3: Schema creation script
Section 4: Edit the LOAD.BAT
Section 5: Creating the tables
Section 6: Loading the data
Section 7: Creating the Foreign Keys
Section 8: Contacting us


Section 1: ERD Diagram

An Entity-Relationship Diagram for this database can be found at:
http://www.learndiscoverer.com/books/erd_model.htm

The 12 tables that will be created are:

Table	Description
gs_channel	Sales Channel data
gs_city	City data
gs_customer	Customer data
gs_day	Fiscal Day data
gs_district	District data
gs_month	Fiscal Month  data
gs_prodline	Product Line data
gs_products	Product data
gs_quarter	Fiscal Quarter data
gs_region	Region data
gs_sales	Sales Fact data
gs_year	Fiscal Year data

[Return to Top of Page]

Section 2: Explanation of Directory structure

Having downloaded and extracted the file DataDefs.zip from our website at:
http://www.learndiscoverer.com/downloads/schema you should see a folder called datadefs.

When you open this folder you will see the following 3 sub-folders:

Sub-Folder	Description
CreateTables	Contains a SQL script that creates the 12 tables
ForeignKeys	Contains a SQL script that creates all foreign keys
LoadData	Contains a DOS Batch file that loads the data into the tables

In the CreateTables folder you will see 13 files.

File	File Name	Description
1	create.sql	The master script that calls the other scripts in turn
2	gs_channel.sql	Script creates GS_CHANNEL table
3	gs_city.sql	Script creates GS_CITY table
4	gs_customer.sql	Script creates GS_CUSTOMER table
5	gs_day.sql	Script creates GS_DAY table
6	gs_district.sql	Script creates GS_DISTRICT table
7	gs_month.sql	Script creates GS_MONTH table
8	gs_prodline.sql	Script creates GS_PRODLINE table
9	gs_products.sql	Script creates GS_PRODUCTS table
10	gs_quarter.sql	Script creates GS_QUARTER table
11	gs_region.sql	Script creates GS_REGION table
12	gs_sales.sql	Script creates GS_SALES table
13	gs_year.sql	Script creates GS_YEAR table

In the ForeignKeys folder you will see one file:

File	File Name	Description
1	fkeys.sql	This script creates all of the foreign keys

In the LoadData folder you will see 25 files:

File	File Name	Description
1	load.sql	A DOS Batch file that loads the data
2-13	load1.ctl to load12.ctl	Control files for loading the data
14-25	load1.dat to load12.dat	The data that is to be loaded

[Return to Top of Page]

Section 3: Schema creation script

If you are new to databases and wish to create the same user OOT_SCH on your database that we used on ours, and we recommend that you do so if you can, you must log on to your database as the SYSTEM user (default password MANAGER) and then run the following SQL script:

create user OOT_SCH identified by OOT_SCH
default tablespace TOOLS
temporary tablespace TEMP
quota unlimited on TOOLS;

grant connect, resource, execute any procedure, select any table to OOT_SCH;
grant execute on DBMS_JOB to OOT_SCH;
grant create procedure, create table, create view to OOT_SCH;

In addition, if you want to assign DBA privileges you can do this using this grant:

grant DBA to OOT_SCH;

Note: if you do not wish to use the OOT_SCH, create or ask your DBA to create a user for you. Please make sure that the user has the following required privileges:

connect
resource
execute any procedure
select any table
execute on DBMS_JOB
create procedure
create table
create view

[Return to Top of Page]

Section 4: Edit the LOAD.BAT

The database used in the book was called IASDB and the schema is owned by the user account OOT_SCH using a password of OOT_SCH. The batch file LOAD.BAT makes reference to this database and schema. If you are not using the OOT_SCH schema you must edit the LOAD.BAT file contained in the LoadData folder and replace the schema name with the name of a user that you have created on your database. In the batch file you should also check the password and your database SID. At the very least, you will probably want to change the database name to match the name (SID) of the database you want to load the data in to.

Each line of the batch file looks like this:

    sqlldr userid="oot_sch/oot_sch@iasdb", control=load.ctl, data=load.dat, log=load.log

You only need to edit the userid clause. This clause logs on to the database by passing a username and password, separated by a forward slash. For example, to change the above login to a user called WAREHOUSE with a password of SMITH on the database called GLOBAL, use the following:

    sqlldr userid="warehouse/smith@global", control=load.ctl, data=load.dat, log=load.log

Note: do not change any of the control, data or log clauses.

Having made the necessary changes to LOAD.BAT you are ready to start.
 

[Return to Top of Page]

Section 5: Creating the tables

All of the table creation scripts refer to a tablespace called USERS. This is the default tablespace as defined in the schema creation script in section 3.

If you do not want to use the USERS tablespace you must edit the 12 SQL files that begin with GS_ in the CreateTables folder and replace the tablespace name USERS with the name of a tablespace that you have created on your database. A simple search and replace on the word USERS will do the trick. You will need to change it two or three times in each script.

As an example, here is the script that creates the channel table:

CREATE TABLE GS_CHANNEL (
CHANNELID NUMBER(2),
NAME VARCHAR2(8 BYTE))
TABLESPACE USERS
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
LOGGING
NOCACHE
NOPARALLEL;


ALTER TABLE GS_CHANNEL ADD (
CONSTRAINT GS_CHANNE_PK PRIMARY KEY (CHANNELID)
USING INDEX
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1));

ALTER TABLE GS_CHANNEL ADD (
CONSTRAINT GS_CHANNEL_UK UNIQUE (NAME)
USING INDEX
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1));

To create the tables, follow this workflow:

Step	Task
1	Make sure you have created a user and know the password
2	From the Windows Start button select START | RUN
3	If using Win2000 or Win NT type CMD and click OK If using Win98 type COMMAND and click OK
4	Change to the folder containing the create.sql script E.g.  cd\datadefs\createtables
5	At the command prompt, type sqlplus username/password@database and then press ENTER, where username is the user you have created, password is the password you assigned, and database is the database SID you are using
6	Having logged in, at the SQL prompt, type @CREATE and press ENTER
7	The 12 tables will now be created
8	When done, type EXIT and press ENTER to leave SQL
9	Type EXIT and press ENTER to close the DOS window

Note: the above script refers to a schema called oot_sch. All of the tables use the same schema. If you do not want to use this schema you must edit the 12 SQL files and replace the schema name with the name of a user that you have created on your database.

[Return to Top of Page]

Section 6: Loading the data

Having created the 12 tables, you are now ready to load the data. This is done by running the batch file LOAD.BAT that we have provided for you and you edited in section 4.

To load the data, follow this workflow:

Step	Task
1	From the Windows Start button select START | RUN
2	If using Win2000 or Win NT type CMD and click OK If using Win98 type COMMAND and click OK
3	Change to the folder containing the load.bat batch file that we want to run E.g.  cd\datadefs\loaddata
4	At the command prompt, type LOAD and then press ENTER The file will now run and upload all of the data into the database
5	When done, type EXIT and press ENTER to close the DOS window

To help you verify that the correct amount of data has been loaded into each table, here is a count of how many rows should have been loaded:

Table	Count
gs_channel	2
gs_city	23
gs_customer	35
gs_day	1463
gs_district	7
gs_month	48
gs_prodline	3
gs_products	18
gs_quarter	16
gs_region	4
gs_sales	2484
gs_year	4

[Return to Top of Page]

Section 7: Creating the Foreign Keys

Having loaded the data into the 12 tables, the last step is to create the foreign keys.
 

To create the foreign keys, follow this workflow:

Step	Task
1	From the Windows Start button select START | RUN
2	If using Win2000 or Win NT type CMD and click OK If using Win98 type COMMAND and click OK
3	Change to the folder containing the fkeys.sql script E.g.  cd\datadefs\foreignkeys
4	At the command prompt, type sqlplus username/password@database and press ENTER, where username is the user you have created, password is the password you assigned, and database is the database SID you are using
5	Having logged in, at the SQL prompt, type @FKEYS and press ENTER
6	The required foreign keys will now be created
7	When done, type EXIT and press ENTER to leave SQL
8	Type EXIT and press ENTER to close the DOS window

To help you verify that the correct number of foreign keys have been created, here is a list:

Table	Foreign Key	Joins to
gs_sales	gs_channel_fk	gs_channel
gs_sales	gs_customer_fk	gs_customer
gs_sales	gs_products_fk	gs_products
gs_products	gs_prodline_fk	gs_prodline
gs_day	gs_month_fk	gs_month
gs_month	gs_quarter_fk	gs_quarter
gs_quarter	gs_year_fk	gs_year
gs_customer	gs_city_fk	gs_city
gs_city	gs_district_fk	gs_district
gs_district	gs_region_fk	gs_region

[Return to Top of Page]

Section 8: Contacting us

We hope you have fun working with our database. If you have any problems with these instructions or need to contact us, please do so through our website at the following address:

http://www.learndiscoverer.com/contact/

or send an email to info@learndiscoverer.com

Best wishes, Michael
June 2004

[Return to Top of Page]