Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

ss Oracle Tips by Burleson

The PUBS Database

For the remainder of the book we will create examples that use the PUBS database schema.  This database was designed as a teaching tool and is available in the code depot.

The file is a list of Oracle commands that creates and populates the database schema.  The commands are discussed in detail in the companion SQL  book, Easy Oracle SQL.

To install the PUBS database you need a running oracle database and we assume that you have already successfully installed the Oracle database.  If not, you need to go to otn.oracle.com and download the latest version of the Oracle database.  We recommend downloading the new Oracle XE database, which is free and contains the latest version of the PL/SQL engine.  All the examples in this book were tested on Oracle9i, Oracle10g and Oracle XE Beta. 

1. When the database was created you assigned it a System ID (called a “SID”) and OracleXE uses the SID named “XE”. 

2. Create a practice directory and copy the pubs_db.sql file to this practice directory. 

3. Start the database.

4. On Windows go to services, right click and start the service OracleService<SID>.  Then start the service OracleListener.

5. On Linux/Unix you must set the environmental variables Oracle Home and Oracle SID before starting the database.  Substitute the correct information in the example below.

$ export ORACLE_HOME=/opt/oracle
$ export ORACLE_SID=mydb
$ sqlplus “/ as sysdba”
SQL> startup
SQL> exit
$ lsnrctl start

In both cases we started two programs, the database and the listener.  The listener listens on a port (normally port number 1521 but you can change the port) for a connection.  When you connect to the database, the listener takes your connection request and creates a server process to perform the actual database work. 

6. Open a terminal window and change into the practice directory.

7. On Windows select Start – Run – type cmd and press enter.

8. On Linux right click on the desktop and select terminal.

9. Change to the practice directory

C:> cd c:\practice
$ cd practice

All the examples in this book will use SQL*Plus.  This is Oracle’s command line tool for interacting with the database.  We will go into SQL*Plus  in much more detail later.  Start SQL*Plus as the super user.

$ sqlplus “/ as sysdba”
SQL>

After starting, SQL*Plus will leave you at the SQL> prompt.  To load the PUBS database, you will need to run the pubs_db.sql script.

SQL> @pubs_db.sql

This will create the pubs user with a password of pubs, create the tables and load the data.  When you are through you will be left as the user pubs.  You can verify this with the command below:

SQL> show user
PUBS
SQL> exit

Use exit to get back to the operating system prompt.

The code depot also contains a PowerPoint slide (PUBS_DB.ppt) with the diagram of the PUBS database.  The PUBS database details information about publishing books.  There is an author table  which list authors.  Notice that it contains a unique key called the auth_key that uniquely identifies each author.  The book table  lists the books.  The store table list stores that sell the books and the sales table list books sold, by order number, in each store.  A publisher also has and employee table for the employees who print the books. 

You can see the links between each table.  Notice the book_author table.  This is a weak entity needed to eliminate the many-to-many link between the author table and the book table, representing the fact that an author can write many books and a book can have more than one author.  The book_author table eliminates that many-to-many link between author and book.  The primary key in the book_author table is both the auth_key and the book_key (a multicolumn key).

The above book excerpt is from:

Easy Oracle PL/SQL Programming

Get Started Fast with Working PL/SQL Code Examples

ISBN 0-9759135-7-3   

John Garmany 

http://www.rampant-books.com/book_2005_1_easy_plsql.htm