 |
|
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 |
