Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
 

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:

  • Each city has many zip codes – A zip code belongs to only one city

  • People have many hair colors – Any person has only one hair color

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

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks