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 

Inside the Relational Model

Relational databases allow a user to both store and retrieve data, manipulating the data into many different forms.  Also, relational databases store more than just the data itself and all sorts of other information is stored inside Oracle. 

During the development of all computer systems, an important task is designing the database, and placing the data into its proper form:

Remember, storing data is not enough and you must capture information about the relationships between the data.  That’s information, too.  Oracle stores all of the following inside the database:

Data Relationships (using “constraints”)

Application programs (Java and PL/SQL are stored inside “procedures”)

Metadata (data about data) is stored in the data dictionary

Business logic (the object-oriented tables with methods)

Internally, Oracle stores the data in the lowest form possible to allow for flexible retrieval, and you can expand on the data form inside your SQL query.  Let’s take a look.

Data is stored inside relational tables.  Each table is an entity like an address or a person record.  Tables are made up of rows and columns very much like a spreadsheet. 

All the columns in a row pertain to the data in only that row.  Each row in a table must have a key that uniquely identifies that row.  This key is called the table’s Primary Key.  For example, an ADDRESS table might look like this: 

Street

City

ST

Zip

234 Starry Lane

Hereville

FL

34509

793 Davis Street

Thereville

CO

07843

482 MyStreet

Whereville

WO

58372

This table contains three rows.  But who lives where?  How do I retrieve one of these addresses?  First, I need to add a primary key  for my ADDRESS table, so I add a key: 

Add_key

Street

City

ST

Zip

A100

234 Starry Lane

Hereville

FL

34509

A101

793 Davis Street

Thereville

CO

07843

A102

482 MyStreet

Whereville

WO

58372

Now, when I retrieve the address that corresponds to key A101, I get the Thereville address.  My table of friends can now use this key to link names with addresses. 

F_Key

FirstName

LastName

Add_key

F100

John

Smith

A100

F101

Sammy

Spade

A101

F102

Thomas

Jones

A102

Now, I can join my FRIENDS table to my ADDRESS table and see that John Smith lives in Hereville.  But wait! Why not place both those items in one table?  The answer is: I can.  But I may not want to, and in fact I probably don’t want to.  In this little example, I could easily combine the two tables, but if my example grew large I would run into problems mostly with performance. 

If I happen to have two million friends and I decided to throw a party and invite everyone from Hereville, I need to know how many friends I have in Hereville. If I only have one table with all my data in it, I must search through all that data just to count the number of Herevilles. 

It is much more efficient to count the number of Herevilles in the smaller address table.  Since I don’t need my friend’s names, why sort through that unnecessary data to get what I need?  By separating data into subtables, I can also remove redundancy.  If two or more friends have the same address, they will share the same address key.  This is called normalizing the data. 


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