Many of the examples in this book, particularly the reporting examples, have been developed against a sample time-tracking database. It’s a fairly simplistic database, containing only three tables, but it’s enough to illustrate everything I talk about in this book. You may or may not wish to create this database for yourself. Creating the database will allow you to try all the examples in this book exactly as they are shown. If you choose not to create and load the sample database, at least familiarize yourself with the data model. Also glance at the sample data itself, which is reproduced later in this section. If you have looked at the model and at the data, you shouldn’t have any trouble following and understanding the examples in this book.
Figure 1.2 shows an Entity Relationship Diagram, or ERD, for the sample database.
As you can see from the ERD, there are only three entities: EMPLOYEE, PROJECT, and PROJECT_HOURS. Table 1.2 gives a brief description of each entity.
Table 1-2. Entity Descriptions
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.
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.
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 multiple page reports, which are needed to demonstrate pagination, page headings, page footings, and summarization.
Name Null? Type ------------------------------- -------- ---- EMPLOYEE_ID NOT NULL NUMBER EMPLOYEE_NAME VARCHAR2(40) EMPLOYEE_HIRE_DATE DATE EMPLOYEE_TERMINATION_DATE DATE EMPLOYEE_BILLING_RATE NUMBER
Name Null? Type ------------------------------- -------- ---- PROJECT_ID NOT NULL NUMBER PROJECT_NAME VARCHAR2(40) PROJECT_BUDGET NUMBER
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 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
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
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 reasonable summary reports, as you will see in Chapter 3.
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 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:
Download and unzip the script files.
Log into your Oracle database.
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. 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.
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 is a self-extracting ZIP
file. Just run it, the scripts will be extracted, and you won’t
need a separate unzip utility.
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 1.3.
On a Unix system, you won’t see a dialog box like that shown in Figure 1.3. Instead you will simply see a userid prompt.
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 pressing ENTER. Answer the password prompt in the same way. Once logged in, you should see a screen similar to that shown in Figure 1.4.
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 1.4.
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 1.5.
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.
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 1.6.
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, 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 1.7 shows the progress messages being displayed on the screen while all this is occurring.
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.