Before proceeding any further it might behoove us to look at the structures that are available in a database
We already looked at tables structures. Some of the latest databases have the following kinds of tables:
Temporary tables or Session Tables
These table contain data only till the session is active. After the user disconnect or logs out from the database the data in the tables is flushed out. This is useful for doing temporary calculations.
This is where the DBMS goes to where it all got started – back to file systems. You can have a file and still use it as a table! Write select statements to join it with other tables and access data. This feature is available in Oracle DBMS.
All columns that you create in a table occupy space on the hard disk. But virtual columns are where the data in that column doesn’t take space on the hard disk. For example you keep the total price would be quantity * price. So you can keep the quantity and the price in the table as normal columns. And keep the Total Price as a virtual column. You might think why, why can’t we do the calculation every time we need than keeping it in the table. But you might type total price as quantity * price but someone else might type as quantity + price by mistake. So it is good to standardize.
This is a structure created to manage large volumes of data sitting in one table. For example if you storing the call records in a table then you will have one large table which will literally take hours to return the results for a simple query. So one way to overcome this is to partition the table by months or by customer segments. So the customer will select from the main table but the real data is coming from a partition.
Well this might sound strange. Why do all this you might think. But welcome to the world of the DBA! We live in a world of BIG data (pardon the pun). A typical Boeing Jet engine produces 10 terabytes of data per 30 minutes of flight. Imagine putting all that in a table and trying to query a single record from that mess! It truly is searching for a needle in haystack!
You see what you want to see. While that is true of life you can make it true for tables as well. Let’s say you want to give a read only access to only certain columns of a table to your staff. Views are the way to do it. Let’s say you want to join two tables and want to let your Call Centre agents to query this information. So you create a view on top of these two tables.
Think of books. Indexes tell you where to find a word inside the book. A table index will tell you where you can find a particular column. Have you seen books with index of subject and another index of names etc.? So they have a basis to index either names or subjects. Same with tables. You choose a column upon which the index is built. For example if the index column is a customer id, the DBMS, and let’s say you query for customer id 5004 then the DBMS will first consult the index find out where the row with customer id 5004 is and then read it from there.
The idea was to keep the business logic – the rules on how data should be processed also inside the database. Simple idea. Amazing results. If you keep it inside the application – reusability was limited. Because in today’s complex applications a single application is written in several languages. Part of it in .NET part of it in virgin C (I mean pure C, using the term virgin after Richard Branson). So if you put the business rules inside an application created in one language it is difficult for the next application to use it. Put it in the database then everybody can use it.
So if you want 100% data purity – you create stored procedures to select, update, insert, delete and use only the procedures. So those who create data will tell you how to manipulate data also.
DBMS typically use a separate language to write the stored procedures. Oracle uses PL/SQL, Microsoft uses T-SQL.
Typically a column like customer id is auto generated. You go first you get number 1 I go next I go number 2. So it is a hassle to check what was the number given to the last customer and then add one to that every time. So you use a structure called the sequence. You can select from it. It will always give you the next number. And it will auto increase.
Every time you insert to a table you want to send an alert to start another process. So you can put a trigger on a table which will be run every time you insert or delete or update a row from that table.
For example let’s say whenever there is a sale that exceed USD1million a regional manager must be notified. So you put a ‘on insert’ trigger on the table and in the trigger code you start a process which will notify the manager.