EnterpriseDB SQL: Data Architecture Oracle Tips by Burleson Consulting

I have 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.

To be 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.

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

A 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 = database!

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!

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

So, 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.

In a 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).

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

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

Let’s review what we have so far:

* A database is like a filing cabinet

* A table is like a folder

* Tables and folders contain subject information

* Information about a subject is called a record

* A folder record is composed of fields

* A table record is composed of columns

* Geeks speak a foreign language

A filing cabinet contains folders that contain records that contain fields of information.

A database contains tables that contain records that contain columns of information.

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.