Computer Overview
If you are like most of today’s modern business, you are utilizing a blend of computer automation to conduct your daily business. The computer automation is a mixture of hardware (computer,
printer, modem) and software applications (Word, Excel, Order Entry, A/R). Software applications have been developed in response to a customer need to automate a process. Software has either been
purchased (i.e. 3rd party vendor software), or created in-house (i.e. custom software).
Relational Database Overview
Regardless of the software inventory, software has a common thread of similarity, software stores and manages it’s data within a database. Most of the databases used in the marketplace today are
known as an RDBMS (relational database management system). Now just because a database is RDBMS, does not in anyway ensure that the developer that used that RDBMS to create the software solution
utilized the features that make the database truly relational. And what are the ramifications of a non-relational database you might ask? Consider a non-relational database to be that dreaded
drawer in your kitchen. Yes, you know the one. It has a little bit of everything, and no one can ever find anything when it’s time to dig. This is the same issue with a ‘dirty’ database.
Looking for data costs money, not finding the data costs even more money. Finding data you think is correct, but is actually not correct costs yet even more money. Now, let’s consider having that
dreaded drawer fully compartmentalized and when you go searching for the AAA batteries, there they are! No more buying batteries unnecessarily. The same theory holds for a relational database. The
customer you are searching for exists, the customer information is consistent, their credit history is complete. The reasons go on…. Basically, a relational database ensures accurate
information (what do you think corporate Auditors?). The database relationships can be enforced at different levels:
The Divine Database
The ultimate enforcement is at the database level. An example of this would be if a customer table and a customer address table were related and the relationship were enforced by indicating that
the customer must have at least one address on file at all times. If any application or SQL statement were executed to delete a customer address (where the customer only had one address to begin
with), then the RDBMS would send an error and refuse the command from executing. Thereby the business rules of the corporation are enforced all the way down to the data, regardless of the
application that is written over that data (very powerful & flexible).
In Data We Trust
The next level is at the application level, meaning that the relationships are enforced by code (hope wild Bob documented well). Even if you purchase 3rd party vendor software, what is the
guarantee that the modules are integrated and adhere to the same business rules? Chances get slimmer with the marriage of custom development and 3rd party vendor applications.
Selling Out
The last level of RDBMS relationship enforcement is considered ‘none’ (I don’t need to explain this one do I?). Refer to my dreaded drawer scenario above……
The RDBMS Solution
When a database is truly relational, it is like a clean-running machine. Everyone benefits, the Customer Service Representative can locate the customer, the Credit Manager can identify repeat
offenders accurately, inventory levels in the warehouse are true, the CEO can see the real daily sales figures. These benefits are not a dream to the technology industry. However, they require due
discipline in the following area, the RDBMS. There is a valid reason why the Database Administrator (DBA) of any bona fide technology team is making the big bucks. It’s because software
applications begin and end with data, period. The better the RDBMS design, the better the software application, the greater longevity of the software application, the greater opportunity of
mobility away from the software application (yes, think into the future).
Graphical Representation of an RDBMS
A database(s) can be graphically represented (uncovered) and rectified within a company. There are several graphical tools on the marketplace today that will graphically represent a database (a
database of any kind from mainframe to MS Access). These tools range in features, from a picture to a database generator. A picture drawing tool such as Visio, will allow a developer to graphically
represent a database and it’s relational integrity. A database generator tool such as Logicworks ERwin tool or Select/SE tool has a backward as well as a forward engineer tool that can extract a
RDBMS and graphically depict it, allowing a developer to redesign it and then physically generate a new database. It can even be a totally new RDBMS. These types of tools are known as CASE tools
(Computer Aided Structured Engineering).
Example
Let’s pretend that you have outgrown the Order Entry application written with a MS Access database. The system is slow and dogged. The developer has long since left Order Entry application in the
dust and you are more than ready to take the plunge to a new system with Oracle as the new RDBMS. One catch, you don’t want to loose the information that has gone into the existing Order Entry
System, rather you want to migrate it to the new system. Here’s the beauty of a CASE tool, where you reverse engineer the MS Access database, remodel it and generate a new Oracle database.
The key benefits for graphically modeling a single or multiple databases are as follows:
- The level of abstraction has been raised to merge single to multiple disparate data files together.
- Disparate data elements can now be standardized (i.e. alias names, formats, business definitions).
- The identification of unused and antiquated data items is now uncovered.
- A graphical representation can be designed, indicating the relationships between disparate databases.
Note – This is a perfect opportunity to consolidate the date fields for the Year 2000 initiative (i.e. change in millennium impact).
The Pitch
Once the new database has been created you now have a better database to build a stronger, more scaleable application over and the best part is that you have a blueprint for a successful data
migration path also. This migration will physically get the Order Entry data from MS Access and into Oracle (note physical data migration has yet it’s own suite of tools to physically move the
data out of the old database and into the new RDBMS).
Not only does the tool offer a graphical representation of the database (similar to an architectural blueprint for building a house), it has database intelligence to assist the developer to build a
better database with rules developed at the database level. Remember, that’s your ticket to ride.
Reverse and forward engineering are a hot topic in the marketplace today because data integrity (and the lack thereof), is costly. Getting it right is expensive, but having it wrong, is not even
affordable. All good DBA’s use a graphical design tool to do their work. Some companies have formed Data Management Teams to operate as the central artery of software development. These teams
foster a culture awareness of the importance of data development and management.
