EnterpriseDB SQL: Data Architecture
Oracle Tips by Burleson Consulting
read many SQL books and played with online tutorials. Almost
without exception, they start with the SQL SELECT statement and
build up to queries that are more complicated. I'm going to
approach this a bit differently. I believe that understanding
how the data is structured and why it is structured that way is a
better starting point.
honest, I am going to do quite a bit of generalization here.
This topic can be as detailed and complex as we would like it to be.
I am going to try to keep this as simple as possible while still
explaining the salient points. I know the heading of this
section, Data Architecture, sounds scary but it is really very
simple. If your mind can grasp the complexities of a filing
cabinet, you can understand databases.
database is a place to store data. A hard drive in your
computer is a type of database. The files and the directories
on the hard drive create what is known as a hierarchical database.
EnterpriseDB is what's known as a relational database. I will
explain below what makes it a relational database.
filing cabinet can be thought of as a database, and though it would
not be a relational database, it is still a type of database.
You use a filing cabinet to store and retrieve records. You
use a database to store and retrieve records. Filing cabinet =
Continuing with this analogy, a folder in the filing cabinet is the
equivalent of a database table. You can put many different
files in a cabinet. You can put the employee folder, the jobs
folder, the invoices folder, the accounts receivable folder, etc. in
a filing cabinet. In the same manner, your database can have
many tables: Employee, Jobs, Departments, Invoices, etc. File
Folder = Table!
geeks speak, they like to use words that other people won't
understand. Unless you're getting into the practical details
of database design, it's ok to make some assumptions. You can
assume that when a geek says ENTITY, what he means is TABLE.
It gets a little more complicated if the geek says RELATION. A
relation can be a table or it can be the data in two tables that
relates them. In general (very general), you can consider a
table = relation = entity = file folder. There are other words
that can be used and if you're around geeks for very long, they will
probably use them. If a geek near you starts speaking
gibberish, slap them and ask them to speak normally! But don't
tell them I sent you.
now we know that a filing cabinet can store many folders. What
do we put in folders? We put records in folders. An
employee record would go in the employee folder. That means
that a record would be something like a sheet of paper(s) containing
information about the subject of the folder. On that sheet of
paper would be one or more pieces of information. We can call
those items fields. An employee record would have a field for
name, address, phone number, etc.
table we would call these pieces of information columns. Data
in those columns would constitute a record. So, a table
contains records of information and that information is stored in
columns. When you add an employee to a table, you are adding
pieces of information (columns) about that employee (record).
many records are in an empty table? None. It's like an
empty folder. You only have records when you have data.
However, an empty table looks like an unfilled form. The
columns are still defined in the table but they contain no data.
to make a confusing topic a bit more complicated, some geeks call
records, tuples, and they call columns, attributes. Can't we
all just get along!
review what we have so far:
database is like a filing cabinet
table is like a folder
Tables and folders contain subject information
Information about a subject is called a record
folder record is composed of fields
table record is composed of columns
Geeks speak a foreign language
filing cabinet contains folders that contain records that contain
fields of information.
database contains tables that contain records that contain columns
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.