Setting Up a Database

   

For the rest of this chapter and Chapter 27, "Advanced JDBC 2.0," a database had to be selected to use for the examples. As you know, there are many to pick from. Some of the relational databases have been around for a long time and have a large share of the market. However, there are a few new vendors in the market and the price is just right for using one of these for the purposes of this book. The problem with some of these newer databases is that they don't fully support the JDBC 2.0 driver functionality.

So, the Oracle8i Lite database from Oracle will be used for these examples.Oracle also now has a version called Oracle8i Personal Edition. This version will also run on Windows98 as well as Windows NT, Linux, and other Unix platforms. You can choose one of these databases or select one of your favorites to use along with the examples in this book. Just be sure that there is a JDBC 2.0 compliant driver for the database before using it.

Note

The download size of Oracle Lite and Personal Edition is quite large. The Personal Edition is approximately 200 MB. Unless you have a fast Internet connection, you might want to get a CD from Oracle or use a different database.


You can download a developer copy from the following URL:

 http://technet.oracle.com/software/index.htm 

or you can start from the main Oracle site at

 http://www.oracle.com 

Oracle8i supports the Windows, Linux, and several of the UNIX platforms such as Solaris. If you already have another database such as Informix, Sybase, or Microsoft SQL Server, you are welcome to use that along with the examples in this and the next chapter. If you are going to be using a different database as you follow along, you must ensure that you download a JDBC 2.0 driver for your specific database. It doesn't matter which JDBC driver type it is, just ensure that the JDBC driver you are using supports the full JDBC 2.0 features or you will not be able to run all the upcoming examples.

The same schema will be used throughout the two database chapters. If you choose Oracle8i Lite, the schema has already been created for you and populated with some sample data. If you are using another database, you should create the schema on your particular database. Also, when you get to the part about setting up a JDBC driver, you will need to read the specific documentation for the driver that you will be using. The steps to set up a JDBC driver for the Oracle8i Lite database are discussed later.

Caution

You can use whichever database you are most comfortable with. Just be sure that it supports transactions and you can find a JDBC 2.0 driver for it. Many vendors say that they support the full 2.0, but when you attempt to execute a 2.0 feature, it prints a message saying, "That 2.0 feature not yet supported."


You get the JDBC 2.0 driver for Oracle8i Lite with the same download. When you run the installer for the database, it will create a datasource with the name "POLITE". This is the reference that you will use to connect to the database later.

Note

Even though the install will create a datasource during the installation process, this is not a TYPE I driver, it is a TYPE II.


For installation of the Oracle8i Lite database, refer to the documentation that is provided with the download. There are several tools that are provided with the Oracle install. You can use these to check your installation and to view the sample data that is provided. Again, refer to the database documentation for any problems with the database itself.

Setting Up the Example Database Schema

A database schema must be selected for the rest of this chapter and the next. If you have ever designed or used a database schema, you know that there are good schema designs and bad ones. There are complete college degrees given for creating good database designs. This book doesn't spend any time analyzing the example schema for being properly or improperly " normalized. " In short, normalization is database term for a database that has had all the redundant and duplicate meta-data definitions removed and relocated for optimal consistency. There are a series of tests that can be applied to any database to test to what degree the database has been normalized. Normalization by itself does not guarantee a good database design. It is just way to help get there in the beginning of the design.

The example schema used here is for purposes of this book and it's being used just as it is. There also won't be much explanation about SQL or database details such as VARCHAR. If you need a refresher on either of these topics, read the documentation for your particular database before going any further with this chapter.

Tables 26.1, 26.2, and 26.3 show the tables and columns that the examples will use. Because a schema and sample data is provided by the Oracle install, those tables are used for the examples. Some columns are not listed and will not be used throughout the examples, because they don't add any necessary information.

Note

If you choose to use another database, you will need to create these tables in that database and populate them with some sample data.


Note

Creating tables and schema is something normally done by a Database Administrator. If you don't know how to do this or are having trouble, check the documentation that comes with your database.


Table 26.1. Customer Table
Column Name Column Type
CUSTID NUMBER NOT NULL (PK)
NAME VARCHAR(45) NOT NULL
ADDRESS VARCHAR(40)
CITY VARCHAR(30)
STATE VARCHAR(2)
ZIP VARCHAR(9)
PHONE VARCHAR(9)
REPID NUMBER
The (PK) for CUST_NO indicates that it is the primary key of the table.
Table 26.2. Emp (Employee) Table
Column Name Column Type
EMPNO NUMBER NOT NULL (PK)
ENAME VARCHAR(10)
JOB VARCHAR(9)
MGR NUMBER
HIREDATE TIMESTAMP
SAL NUMBER
COMM NUMBER
DEPTNO NUMBER NOT NULL
Table 26.3. SalaryHistory Table
Column Name Column Type
EMPID NUMBER (PK)
DATE DATE (PK)
SALARY VARCHAR(10)

Caution

The column name DATE above in the Salary History table was chosen to make a point. Although Oracle Lite allowed me to name the column with this name, in other databases, this is a reserved word. Not to mention that the name is not very informative to begin with. Make sure to choose good names when naming your schema.


Note that there are two columns in the Salary History table that make up the primary key. This is called a composite primary key and is made up of two or more columns in the table. Because an employee hopefully will have many salary change records, the column EMP_NO will not be unique. This is why an additional primary key is used along with the EMP_NO column to help ensure uniqueness. With this schema design, an employee could only have one salary history record per Timestamp value, but the likelihood of that happening is extremely small.

   


Special Edition Using Java 2 Standard Edition
Special Edition Using Java 2, Standard Edition (Special Edition Using...)
ISBN: 0789724685
EAN: 2147483647
Year: 1999
Pages: 353

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net