 |
|
ss
Oracle Tips by Burleson |
Misleading Data Relationships
If we follow the formal rules for entity
relational modeling, it is tempting to move all relations into
separate tables. For example, we might note:
It would be tempting to create tables for zip code
and hair color, but this is incorrect! It is wrong because there are
no additional attributes to zip code and hair color. Remember, the
smaller the number of tables, the faster your SQL will run!
The PUBS Database
For the remainder of the book, we will be using
the PUBS database. This database was designed as a teaching tool and
is available in the code depot.
The file is simply a list of commands that creates
and populates the database. We will learn each of the commands as we
continue through this text.
To install the PUBS database, you need a running
Oracle database. Here, we assume that you have already installed the
Oracle database. When the database was created, you assigned it a
sid. Copy the PUBS.sql file
to a practice directory.
Start the database.
On Windows, go to services, right click and start
the service OracleService<SID>. Then, start the service
OracleListener.
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
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. We will
learn more about the database later in this chapter.
Open a terminal window and change into the
practice directory.
On Windows select Start – Run – type cmd and
press enter.
On Linux, right click on the desktop and select
terminal.
Change to the working directory:
C:> cd c:\john
$ cd john
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*Pluswill leave
you at the SQL> prompt. To load the PUBS database, you will need to
run the PUBS.sql script.
SQL> @pubs.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.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 list 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 employees who,
of course, print the books. You can see the links between each
table. Notice the book_author
table. Remember, the weak entity needed to
eliminate the many-to-many link. 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. The primary key in the
book_author table is both the
auth_key and the
book_key (a multicolumn key).
Before we jump into using SQL, we need to know a
little about how the Oracle database handles our request.
The above book excerpt is from:
Easy Oracle
SQL
Get Started
Fast writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
John Garmany
http://www.rampant-books.com/book_2005_1_easy_sql.htm |
