Data is Alpha and Omega

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).


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.


submit to reddit

About Sarah Wentworth

Ms. Sarah E. Wentworth started Knowledge Strategies Insights (KSI), a consulting and technical training company one and 1/2 years ago. KSI specializes in data centric tools and processes that streamline the development process and promote software solutions with greater longevity.

Since startup, Sarah has been invited to speak at several conferences both in the U.S. and in Europe in the promotion of best data management practices. By the third month of operation, KSI was turning down opportunities to keep up. Over this past year, Sarah led and advised clients on the creation and management of large-scale data warehousing projects and various data related projects. KSI is a certified channel partner with some of industries best data tools which has brought business and training opportunities. Recently, Sarah was accepted in the "Who’s Who of Entrepreneurs" for 1997.

Sarah created KSI on the belief that group based development where information sharing and reuse is fostered, leads to a higher quality product with greater longevity.

Ms. Sarah E. Wentworth, Principal
Knowledge Strategies Insights (KSI)
P.O. Box 226, 1050 State Rte 32
Round Pond, ME 04564 (USA)
Bus: (207) 529-4280
Fax: (207) 529-4279