We have used databases for many years and it is an integral part of our daily lives. We deal with databases all the time and multiple times a day regardless if we know it or not and have done so long before the electronic age.
Where do we as ordinary people use databases? Point of Sale systems found in stores are databases, library electronic card catalogs, hospitals maintain patient records on them as well as billing, even our cell phones use a simple database for the phone book. So as you can see we are surrounded by databases.
To really simplify a definition of a database, or “DB” we can say that it is a collection or records organized in a searchable format.
A very good example is our old friend the telephone book. Here you can find a person’s name, their address and of course their phone number provided they
have been published in the book. As we know it is a relatively easy process to open the book and find the person’s number. In fact I am going to refer back to the telephone book in this article.
A DB is comprised of four main components. They are the tables, forms, queries and reports. Of course there are other components like macros which automate things and a few others as well but we are not going to address those here.
The first item and is the most important part of a DB are the tables for that is where the data is stored. The tables are divided into rows and columns and where they intersect is a cell. Each cell holds a specific item of data. Very much like a common spreadsheet.
Back to the phonebook, the first column will be for the person’s name. The next column is their address and finally the phone number in the third column. Of course you can break this down further for a column for the last name, one for the first name, one for the street address, one for the town, one for the state and at last, the phone number.
Let’s say we created a table mimicking the phone book only let’s add in a few extra columns as if we ran a HR department. Right after the phone number we have the person’s date of birth, next is their social security number, finally is their annual gross income.
In a physical phone book we cannot add those unless we have access to the printing presses. And then it would require a massive amount of work to repaginate the layout. In our electronic version you can add as many columns as the program will allow with just a click of the mouse.
Looking at our electronic phone book above with the new information we added like birthdates, social security numbers and salary it should become very apparent how critical the tables are and what needs to be done to protect the data they contain. Tables in a DB can and usually do contain very sensitive information which can not only cause irreversible damage to a business but destroy people’s lives if the wrong person gains access to that data.
Tables can contain sensitive data like that are found in DB’s from Human Resources, financial institutions like banks and credit card companies, hospitals, military, and a whole gambit of other industries. So it really is not unusual at all to have that kind of data and more.
For publicly traded companies that fall under the Sarbanes Oxley Act and other regulations, extra care must be taken to protect the integrity of the tables and the data therein. Any changes to the tables must be documented time and time again to ensure that the data has not manipulated by an outside source.
In many DB’s you will find that they are made up with numerous tables, sometimes in the thousands. Each table can hold say several million pieces of data each. A lot of times these tables are interconnected in some form of relationship which gives rise to the modern relational databases. As you can probably imagine DB’s can be quite large and take up a lot of hard drive space. In several database the tables can get to be so large that they often require their own servers alone. And in these too can be so large like hundreds or thousands of servers just for the tables we would be entering the world known as “Big Data.”
Now it should be obvious that the tables that are the actual heart of any DB.
To make it easier to find and sort the data each table will have one unique piece of data known as the primary key. This key is very important in keeping the data organized. In some tables may have a shared key from another table called the surrogate key especially when it comes to relational databases. This in the simplest terms helps synchronize data between tables.
The next part of the DB are the forms. This is what the user sees on the screen and is used to enter the data in the first place. In many cases forms are often used to display the data after a search or query has been performed. Forms can also be used as control panels with buttons the user can click on to do certain functions.
I mentioned the term query in the above paragraph. Queries are important to the DB as they retrieve data from the tables in a specified and logical manner for you to use.
In a phone book, you would look up someone based on their last name. This is a manual query and depending on the size of the phone book we are searching through it can take from a few short minutes to hours. In our DB you would enter the search parameters like the last name or address and the query would pull up all the records based upon that in a blink of an eye.
Queries can be very simple like the one above with one search parameter or very complex with several parameters depending on what the ultimate goal is.
As an example I wrote a trouble ticketing system in my last company that I used for help desk functions. I was able to enter a user’s name as a query parameter and very quickly was able to bring up the entire service history for that user and their computer. This was a lot faster and easier than searching through traditional hand written work orders. Plus it was very useful as a knowledge base.
Finally the last part of a DB are the reports. Reports are generated based upon data retrieved from running a query and then are printed out onto paper or in a paperless environment as a .pdf or similar document. This gives a written account of the data stored in the tables retrieved by the query.
The reports can be written with the DB software or from third party software like Crystal Reports.
The reports can be one page long or several thousand pages depending on what was in the query in the first place. In many instances a massive report is best being produced as a .pdf instead of burning through reams of paper. Yet I have seen people run voluminous reports on paper that I seriously doubt anyone takes the time to read everything they contained which was a true waste of paper. So the .pdf version of the reports would fit perfectly in a paperless office environment.
I tried to implement that at my last company for the accounting officers would go through cases of paper creating reports only to be tossed out. It was met with resistance until they were shown the cost savings alone.
That was a very simple overview on how a DB works. If you sit back and look at it with those four parts, you will see that they are not that bad at all and why they are so very important in our daily lives. Once you understand the principles of how a DB works, then all you need to do is pick up a book so to speak and read how a particular type of DB works like SQL Server, SAP, Oracle, FileMaker Pro, etc.
As you can also see, a DB is the heart and soul of a paperless office solution. You can read my entry on paperless offices in this blog.
No comments:
Post a Comment