< previous page page_10 next page >
Page 10
The Data Model
Figure 1-2 shows an Entity Relationship Diagram, or ERD, for the sample database.
Figure 12.
The sample database
As you can see from the ERD, there are only three entities: EMPLOYEE, PROJECT, and PROJECT_HOURS. Table 12 gives a brief description
of each entity.
Table 12. Entity Descriptions
Entity Name Description
EMPLOYEE Contains one record for each employee. This record contains the employee's name, hire date, termination date,
and billing rate. The primary key is an arbitrary employee ID number. The termination date for current
employees is set to NULL.
PROJECT Contains one record for each project that an employee may work on. Contains the project name and budget. The
primary key is an arbitrary project ID number.
PROJECT_HOURS Each time an employee logs time to a project, a record is generated in this table. The record contains the number
of hours charged against the project as well as the total dollar amount charged. The dollar amount charged is
calculated at the time the record is created because an employee's billing rate may fluctuate over time. The
primary key is a combination key made up of an employee ID, a project ID, and the date.

The number of employees and projects is fairly small. However, there is a fairly large amount of data in the PROJECT_HOURS table to allow
for the generation of

< previous page page_10 next page >
< previous page page_11 next page >
Page 11
multiple page reports, which are needed to demonstrate pagination, page headings, page footings, and summarization.
The Tables
This section shows the column descriptions, including column datatypes and lengths, for each of the three sample
tables. This is the same information you would get using SQL*Plus's DESCRIBE command.
The EMPLOYEE table

Name Null? Type

EMPLOYEE_ID NOT NULL NUMBER
EMPLOYEE_NAME VARCHAR2(40)
EMPLOYEE_HIRE_DATE DATE
EMPLOYEE_TERMINATION_DATE DATE
EMPLOYEE_BILLING_RATE NUMBER
The PROJECT_HOURS table
NAME NULL? TYPE

PROJECT_ID NOT NULL NUMBER
PROJECT_NAME VARCHAR2(40)
PROJECT_BUDGET NUMBER
The PROJECT_HOURS table
NAME NULL? TYPE

PROJECT_ID NOT NULL NUMBER
EMPLOYEE_ID NOT NULL NUMBER
TIME_LOG_DATE NOT NULL DATE
HOURS_LOGGED NUMBER
DOLLARS_CHARGED NUMBER
The Data
This section shows the data contained in the three sample tables.
The EMPLOYEE table
ID Name Hire Date Term Date Billing Rate

101 Jonathan Gennick 15-Nov-1961 Still Employed 169.00
102 Jenny Gennick 16-Sep-1964 05-May-1998 135.00
104 Jeff Gennick 29-Dec-1987 01-Apr-1998 99.00
105 Horace Walker 15-Jun-1998 Still Employed 121.00

107 Bohdan Khmelnytsky 02-Jan-1998 Still Employed 45.00
108 Pavlo Chubynsky 01-Mar-1994 15-Nov-1998 220.00
110 Ivan Mazepa 04-Apr-1998 30-Sep-1998 84.00

< previous page page_11 next page >
< previous page page_12 next page >
Page 12
111 Taras Shevchenko 23-Aug-1976 Still Employed 100.00
112 Hermon Goche 15-Nov-1961 04-Apr-1998 70.00
113 Jacob Marley 03-Mar-1998 31-Oct-1998 300.00
The PROJECT table
ID Project Name Budget

1001 Corporate Web Site 1,912,000.00
1002 Year 2000 Fixes 999,998,000.00
1003 Accounting System Implementation 897,000.00
1004 Data Warehouse Maintenance 294,000.00
1005 TCP/IP Implementation 415,000.00
The PROJECT_HOURS table
The PROJECT_HOURS table contains the following information, repeated for each employee.
Proj ID Emp ID Log Date Hours Charged Amt Charged

1001 101 01-Jan-1998 1.00 169.00
1003 101 01-Jan-1998 3.00 507.00
1005 101 01-Jan-1998 5.00 845.00
1002 101 01-Feb-1998 7.00 1,183.00
1004 101 01-Feb-1998 1.00 169.00
1001 101 01-Mar-1998 3.00 507.00
1003 101 01-Mar-1998 5.00 845.00
1005 101 01-Mar-1998 7.00 1,183.00

1002 101 01-Apr-1998 1.00 169.00
1004 101 01-Apr-1998 3.00 507.00
1001 101 01-May-1998 5.00 845.00
1003 101 01-May-1998 7.00 1,183.00
1005 101 01-May-1998 1.00 169.00
1002 101 01-Jun-1998 3.00 507.00
1004 101 01-Jun-1998 5.00 845.00
1001 101 01-Jul-1998 7.00 1,183.00
1003 101 01-Jul-1998 1.00 169.00
1005 101 01-Jul-1998 3.00 507.00
1002 101 01-Aug-1998 5.00 845.00
1004 101 01-Aug-1998 7.00 1,183.00
1001 101 01-Sep-1998 1.00 169.00
1003 101 01-Sep-1998 3.00 507.00
1005 101 01-Sep-1998 5.00 845.00
1002 101 01-Oct-1998 7.00 1,183.00
1004 101 01-Oct-1998 1.00 169.00
1001 101 01-Nov-1998 3.00 507.00
1003 101 01-Nov-1998 5.00 845.00
1005 101 01-Nov-1998 7.00 1,183.00
1002 101 01-Dec-1998 1.00 169.00
1004 101 01-Dec-1998 3.00 507.00
The detail is actually the same for each employee. They all work the same hours on all projects. There are enough
PROJECT_HOURS records to produce some rea-

< previous page page_12 next page >
< previous page page_13 next page >
Page 13
sonable summary reports, as you will see in Chapter 3, Generating Reports with SQL*Plus.
Loading the Sample Data

In order to load the sample data you will need an Oracle userid and password. If you are accessing a remote database,
often the case for people using Windows, you will also need a connect string. If you are using Oracle Personal Edition
(formerly Personal Oracle), then the connect string is not needed to connect to your local database. You must have the
necessary privileges and quotas to create tables in the database you are using. Specifically, you must have the following
system privileges:
CREATE SESSION
ALTER SESSION
CREATE TABLE
CREATE VIEW
CREATE TRIGGER
CREATE PROCEDURE
CREATE SYNONYM
CREATE SEQUENCE
CREATE TYPE (Oracle8 only)
Your database administrator can help you with any of these items. Once you have a username and password and have
been granted the necessary privileges, you can create the sample tables and data by following these four steps:
1. Download and unzip the script files.
2. Start SQL*Plus.
3. Log into your Oracle database.
4. Run the BLD_DB.SQL script file.
The screenshots in the remainder of this section show you how things would look if you were creating the sample tables
and data using SQL*Plus in a Windows 95 or NT environment. Under other operating systems, such as Unix,
everything would look much the same, except that SQL*Plus may not have its own window and the userid/password
prompts may not be in a dialog box.
If you are new to SQL*Plus and are completely uncertain how to start it in your particular environment, you should first
read the section titled Starting SQL*Plus in Chapter 2, Interacting with SQL*Plus. Once you know how to start
SQL*Plus, you can come back here and run the script to create the sample tables and fill them with data.

< previous page page_13 next page >
< previous page page_14 next page >

Page 14
Step 1: Download and unzip the script files
The SQL scripts to create the tables and data used for the examples in this book can be downloaded from O'Reilly &
Associates' web site. See the Preface for more information.
The scripts are stored in a ZIP file named BLD_DB.ZIP. Download this file, and extract the contents into a directory on
your hard disk. If you do not have an unzip utility, you may download BLD_DB.EXE instead. BLD_DB.EXE is a
selfextracting ZIP file. Just run it, the scripts will be extracted, and you won't need a separate unzip utility.
Step 2: Start SQL*Plus
When you first start SQL*Plus in a Windows environment, you will see a SQL*Plus window open. You will also
immediately be prompted for a userid, a password, and a host string. Your screen should look like the one shown in
Figure 13.
Figure 13.
SQL*Plus immediately after startup
On a Unix system, you won't see a dialog box like that shown in Figure 13. Instead you will simply see a userid prompt.
Step 3: Log into your Oracle database
Type your userid, password, and host string into the three dialog box fields. If you are using Oracle Personal Edition,
you typically leave the host string field blank. Once the correct information is entered, click the OK button. If you are
using a Unix system, answer the username prompt by typing in your username and press-

< previous page page_14 next page >
< previous page page_15 next page >
Page 15
ing ENTER. Answer the password prompt in the same way. Once logged in, you should see a screen similar to that
shown in Figure 14.
Figure 14.
SQL*Plus after a successful login
The opening screen shows the SQL*Plus version, some copyright information, and some Oracle database version
information. The exact version numbers and options you see may vary from those shown in Figure 14.
Step 4: Run the BLD_DB.SQL script file
The next and final step is to run the BLD_DB.SQL script file, which is one of the files in the ZIP archive you

downloaded in step 1. To do that, simply use the @ command as shown below:
SQL> @c:\ \bld_db
You need to specify the full directory path to this file.
After you type in the above command and press ENTER, your SQL*Plus screen should look like the one shown in
Figure 15.
The first thing the script does is confirm that you really do want to load the sample data. Go ahead and answer with a Y,
or with an N if you've changed your mind about loading the data.
You must answer the script questions with either a Y or an N. Your
reply is not case-sensitive, so a lowercase response is fine. Input validation is
minimal, but it is there. If you give an invalid response, an error message will be
displayed and the script will stop. You will then need to restart the script, using
the START command as shown earlier, and answer correctly.

< previous page page_15 next page >
< previous page page_16 next page >
Page 16
Figure 15.
After the BLD_DB script is started
If you've answered Y to the question about continuing, the next thing you will see is another prompt asking if you want
to first drop the sample tables. This is shown in Figure 16.
Figure 16.
The BLD_DB script asking to drop the sample tables
This option to first drop the sample tables is convenient if you have loaded them before and wish to quickly reload
them. If this is your first time running this script,

< previous page page_16 next page >
< previous page page_17 next page >
Page 17
you should answer this question with an N. If you have loaded the tables previously, and you know that they exist now,
then you should answer with a Y.

Now you can just sit back and watch. The next thing the script does is create the sample tables. Following that it inserts
data into each table. Figure 17 shows the progress messages being displayed on the screen while all this is occurring.
Figure 17.
Progress messages from the BLD_DB script
The entire load process should take less than a minute. When the load is complete, you will be asked to press ENTER
one final time. After doing that, you can use the EXIT command to exit SQL*Plus.
Now that you have loaded the sample data, you can proceed with the book and try out the examples as you go.

< previous page page_17 next page >