EnterpriseDB SQL: Data Architecture
Oracle Tips by Burleson Consulting
index in a database is like the little card on the front of a filing
cabinet that says A-C in this drawer, D-F in this drawer, etc. An
index helps programs looking for information find the information
faster. Instead of looking through every drawer possible, it can go
straight to the drawer with the right folder.
Actually, the analogy falls apart somewhat at this point. An index
is really more like a list of names written on a folder. When you
look for a specific name, you know which folder it's in (and
approximately where it is in the folder) but you have to look
through the entire folder to find the specific name. The list will
tell you which folder it's in and will make your search quicker.
downside to an index is that every time you add a name to the
folder, you have to update the list. Therefore, adding new records,
or updating or deleting existing records, takes longer. The trade
off to an index is that you can choose to find it as quickly as
possible or you can choose to add, update and delete as quickly as
possible. The more indexes you have, for example: by name, date of
birth, job function, etc, the longer it takes to add or update an
wasn't painful was it? We've already covered tables, columns and
let's pretend we have a table called EMP. The columns in EMP look
Column Data Type
worry about the data types yet. I'll cover those in the next
section. Right now, we're still working on architecture.
EMPNO column can hold a 4-digit number that uniquely identifies an
employee. When I say it "uniquely identifies", I mean that the
table can only have one record with a column that has that value.
This is called a primary key.
could possibly use ENAME, which is the employee name, as a primary
key but we might have two employees with the same name. That
wouldn't be unique! We could use something like social security
number but not every employee has one. So the column, or columns,
that uniquely identifies a row in a table is called the primary
key. In this case, our primary key is EMPNO.
make sure we have a unique value in EMPNO, we can use an object
called a sequence. I will show you how to create and use a sequence
below. A sequence is a number generator. It is user defined. It
can go forward or backwards and it skips one or more numbers between
calls. You can have many sequences defined in a database.
we populate EMPNO, we would call this sequence and get a new
number. The database automatically increments and keeps track of
the current value so that we don't have to. Compare this to the old
way of generating unique numbers (by hand on paper) and you can see
how this makes life easier.
primary key is always NOT NULL. Keeping this general, a NULL column
means it is an empty column (technically, it means the value is not
known). Before you put data in a column, it will be NULL. When an
empty field on a form, is blank, you could say that its value is
saying a field must be NOT NULL, that means that you cannot add a
record unless that column has data. This is called a NOT NULL
constraint. Now we know that a primary key has, at least, two
constraints: Unique and NOT NULL.
have another table in our database called DEPT. DEPT has the
Column Data Type
this table, DEPTNO is a sequence that uniquely identifies the
department. DEPTNO is the DEPT table's primary key.
has a DEPTNO column. Just because they have the same name does not
mean they are related. You have to know the business use of the
data to know if they are related. In this case, I know they are.
the primary key of one table is referenced in another table, it
means there is a relationship between the two tables. In this case,
has a foreign key relationship with the DEPT table. That just means
that a foreign key (in this case the DEPT table's DEPTNO primary
key) exists in EMP.
is also called a referential constraint.
that means is that EMP
child table to DEPT and DEPT is the parent of EMP.
DEPTNO column in EMP
defined as NOT NULL, then an employee cannot exist in a DEPTNO that
doesn't exist in the DEPT table.
DEPTNO column in EMP
defined as to allow NULLs, then an employee can exist in a NULL
case, DEPTNO in EMP
allow NULL values. We can create an employee record that is not
assigned to a department. We might want to do that if we had a new
hire who was not yet assigned to a particular position.
diagram below, Figure 3.1, shows the relationship between EMP and
DEPT. This is called an Entity Relationship Diagram (ERD).
3.1: Entity Relationship Diagram
see what we've covered since our last summary:
Primary Key uniquely identifies a record.
NULL value is an unknown value.
most purposes, an empty column is also a NULL column.
Primary Key CANNOT be NULL.
Sequence is a database object that generates sequential numbers.
constraint is a restriction defined in the database.
NOT NULL constraint on a column forces that column to have data when
a record is added.
Referential constraint, also called a foreign key constraint,
restricts a row in a table to valid rows in a corresponding table.
we've covered the major concepts of Data Architecture, albeit at a
very general level. I don't intend this small section to make you a
database designer or guru. However, you should now be somewhat
comfortable with the rest of this chapter.
Summary of Data Architecture: Tables have columns and tables can be
related to other tables on foreign keys. A foreign key is usually
the primary key of another table. The relationships between tables
are why some databases are called Relational Databases. Indexes can
make queries go faster but they might slow down updates.
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.