I began professional programming using Oracle PLSQL, in 1998, as part of, an centralized budget project. The budget project was part of an Y2K project. Its purpose was to control budget appropriations, through out the world, for a particular organization. The Contractors office space included: a table, two computers, and our chairs. From our work-area, the view was great, for we sat infront of two windows, overlooking the city. The machines contained software, such as, visual basic, SQL Plus, and windows NT. All the necessary software, to build a world wide enterprise software managing the organizations budget. With over a million records, Oracle was the preferred database storage software. It provided the best volume managment by performance and could be designed nicely, to meet centralization issues. So, time was ticking and the software needed to be built before the turn of the century.

There is nothing better than the challenge to building a new software system. The adrenal was plumping and designs were beginning to materialized. The big picture needed to be built. So starting on white boards, the team talked about and sketched diagrams showing how the data was to be stored.

The buzz word, "relational databases" emmerged in the conversation. Jargon like rows, columns, primary keys, foreign keys, one to many, many to many, joins, and constraints were exchanged between developers and database administrator. In a relational database, the data is stored as tables. Tables contain fields describing the type of date, container name of the data, size, decimal precision, and required or not required constraints. Technically, a table is an object residing in a schema. A schema can contain other objects like indexes, views, snapshots, functions, procedures, packages, sequences, and clusters.

CREATE TABLE my_table_name( 
{field1} VARCHAR2(10) NOT NULL, 
{field2} NUMBER(10) ) 
PCTFREE 30 PCTUSED 60 TABLESPACE a_tablespace_name 
STORAGE ( INITIAL integer NEXT integer ); 

Wew! I had to describe schema tables definitions. Ok, so what type of information does the database administrator need? Well here's a few to steps to consider when adding tables to the schema.

1) Define each field name, field type, size, required for the tables 
2) Define and create the primary key constraints 
3) Define and create the foreign key constraints 
4) Define and create the indexes 

There is alot of planning. The data needed to analyzed, to determine, what data kept the field information kept the data records unique. This field was called the primary key. The primary key is always unique and provides a field or fields associated creating the primary key index.

Likewise, the foreign key is a field reference, into another table. The foreign key points back to the primary key of another table. Foreign keys prevent dependant tables from added orphan records. Orphan records are bad because they jeapordize the integrity of your data. Bad data makes the system unreliable. An example of an primary key and foreign key relationship would exist between an employee table and an address table.

The primary key in the employee table could be the employee identification number and the foreign key in the address book is the employee identification number linking back to the employee table.

Primary Key Constraint 
ALTER TABLE employee ADD 
( CONSTRAINT EMPLOYEE_EMPLOYEEID_PK PRIMARY KEY (EMPLOYEEID) 
USING INDEX 
TABLESPACE a_tablespace_name 
PCTFREE 10 STORAGE 
(INITIAL 20K NEXT 20K PCTINCREASE 0) 
) 
Foreign Key Constraint 
ALTER TABLE address 
ADD ( CONSTRAINT (employeeid_pk) 
FOREIGN KEY (employeeid ) REFERENCES employee ( employeeid ) 

So my constraints are created and data integrity were looking good. The next problem was performance improvements, since some of the database tables contained millions of records. During query extraction of data full table scans over a million records were very time consuming. The user didn't want to wait for over a few seconds to receive their data. It was important for the user to have real time response when they pressed a button. The database adminstrator analyzed what fields had criteria applied to them and created an index. An index is a object containing only fields that are being search. A index contains a pointer to the physical table records. So searching an index for a data match is much faster.

CREATE {UNIQUE} 
name_idx 
ON employee ( name ) 
PCTFREE 10 TABLESPACE (table_space_name) 
STORAGE (INITIAL 20K NEXT 20K PCTINCREASE 0); 

If UNIQUE is specified the key in the index is force to be unique otherwise the index is assumed to be non-unique.

Wow! Queries ran alot faster. SQL Plus has commands that measure query execution time in milliseconds.

In summary, planout and sketch, what your data will look like. Decide how the data will be stored. Apply rules of normalization (PL/SQL Haven) to move data around so rules of a relational database can be applied to your data. Once your table schema design has been completed start writing oracle scripts to create your schema. Try loading your data. If constraint problem occur you can enable and disable your constraints and indexes. Its more efficient to load your data into the table with index and constraints disabled. After the data has been loaded then enable your indexes and constraints.

Next week, I'll be describing Select, Insert, Update, and Delete; all the CRUID about data manipulation. David Nishimoto LSS