Zero-cost, non-commercial, registration-free resources for learning about relational databases

Russ Kratowicz, P.E., CMRP, executive editor, uncovers practical information about relational databases.

By Russ Kratowicz, P.E., CMRP, executive editor

Welcome to a dive into the Web morass in search of zero-cost, non-commercial, registration-free resources aimed at uncovering practical information about relational databases.

Organizing a collection of data elements in a logical manner is what converts them into a database. Although data stored in a single spreadsheet might constitute a rather primitive database, what makes a database relational is a particular way of organizing the data pieces into normalized tables. Doing so permits large quantities of data bits to be accessed and reassembled to produce useful output without having to reorganize the database tables themselves. Furthermore, a normalized database eliminates redundancy and the possibility of making errors each time the beast is updated.

Its origin

Dr. Edgar Frank "Ted" Codd, IBM Fellow of the San Jose Research Laboratory, invented the relational database in 1970. If you go to the Web page posted by Paul Maxim, a graduate student at the University of Wyoming http://www.cs.uwyo.edu/~paulmax/cosc5000/codd_files/frame.html you'll find a brief biography of Mr. Codd, a list of the awards he's received and more. Codd is quite a guy.

Glossary

The details that underlie databases and the construction thereof make use of many unique terms and concepts, but it's not exactly rocket science we're talking here. It's more logic and organization. Nevertheless, before you get too deep into this field, it would be worth your while to learn the jargon. To that end, you should visit http://www.dhdursoassociates.com/glossary.html, where you'll find a glossary posted by D. H. D'Urso & Associates.

But now that I think about it, even rocket scientists need databases. The Glossary of Database Terminology is a richly-linked document published by the Harvard-Smithsonian Center for Astrophysics, High Energy Astrophysics Division. Click on the technical words in a definition, and you move to a definition of the technical word, which contains additional technical terms. The glossary is reasonable comprehensive, but the risk is getting into a circular searchA means B, B means C, but C means A. And you never figure out what A really means using common words. Other than that, this site is worth your time. Visit http://hea-www.harvard.edu/MST/simul/software/docs/pkgs/pgsql/glossary/glossary.html .

SQL

Structured query language, SQL, is the interactive programming lingua franca for getting information from and into a relational database. Like so many software concepts and products, when a new version of SQL appears, its ultimate fate is to be supplanted by a later revision. Searching for relevant Web sites for this article uncovered references to SQL versions 2.0 through 4.66, which might not even be the most current. Make sure you're learning about the version you have available.

With that said, you probably should learn something about SQL if you're going to develop a customized database. To that end, I'd suggest paying a visit to http://www.cs.unibo.it/~ciaccia/COURSES/RESOURCES/SQLTutorial/sqlcont.htm, where you'll find a complete self-paced tutorial.

From the Ivory Tower

Christopher W. Clifton, Associate Professor in the Department of Computer Sciences at Purdue University, West Lafayette, Ind., offers 20 pages of class notes for a graduate-level computer science course. There's little explanatory text in this material that focuses on the underlying database logic and theory. It may be disconcerting to some to have to deal with Boolean and other symbology that's used to get across what you need to learn. This isn't a cookbook that shows how to do the deed using some particular software. It's not for everyone, but it's good. Look for CS 541 Review at http://www.cs.purdue.edu/homes/clifton/cs541/Review.pdf.

Getting serious

Database theory is a deep subject, but most of us use a database of one sort or another. If nothing else, we're all resident on some telemarketer's hard drive. Yes, anyone can use a database, and you can rightfully claim to be more than merely an appliance operator if you know something about the details concerning what's going on behind the keyboard of your CMMS and EAM systems. The next Web site could give you some info tidbits to spout off on the next trip to the water cooler. Read Characteristics of a Relational Database, a document posted at http://www.frick-cpa.com/ss7/Theory_RelationalDB.asp by David R. Frick & Company, Valencia, Calif. If you bother, you'll be rewarded with an explanation of Codd's 12 rules for a database management system, the engine that can make your very own data bits come alive.

Introduction to Relational Database Design by Fernando Lozano is an article that presents the basics of relational database design so that you can tackle ambitious database projects using mSQL or any other relational database operating under OS/2. Lozano even provides samples to run under mSQL and SQL Anywhere, but he doesn't attempt to teach the basics of SQL. Check out this document at http://www.edm2.com/0612/

Normalization

This process arranges the data elements into individual tables that are then linked to eliminate redundancy and update errors.

A properly normalized database is constructed so that the individual customer information appears in only one place in one table. When the area code changes, the updated information is entered into your database only once. Then, when anyone queries the database, the only area code available for that customer is the correct one.

About, Inc. offers a guide to the normalization process at http://visualbasic.about.com/cs/dbnormalization/. Explaining this important concept requires some unfamiliar jargon. Fortunately, the site also has a glossary.

Developer Shed, Inc. weighs in on the topic at http://www.devshed.com/Server_Side/MySQL/Normal/. Be sure to read An Introduction to Database Normalization first. You might need to do a lot of clicking because the tutorial consists of a series of relatively short pages that are served up in succession instead of as a single enormous document. When you get to Part 2, you'll be working on an example problem that's based on MySQL, but the principles you learn can be adapted to other database packages.

Our friends at Microsoft Corp. are making use of the Web in an impressive manner. They've posted Support WebCast: Database Normalization Basics, an online PowerPoint presentation that covers effective table structures and relationships and the normalization that makes them efficient. To access the audio and video on this streaming media presentation, you'll need an Internet connection of 28.8 Kbps or faster and a minimum screen resolution of 800 x 600. Use your digital prowess to get to http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc060600/wcblurb060600.asp, where you'll be able to listen to a lecture and slide show that runs for a bit more than 22 minutes.

Downloads

What bills itself as "the world's most popular open-source database" is available for free at http://www.mysql.com/. David Axmark, Allan Larsson and Michael "Monty" Widenius are the principals of MySQL AB, a Swedish software company. These lads are offering their MySQL 4.1 database server "as-is" and without warranty. It's free, after all. Visit the site and read about it.

Big Blue wants you to have a copy of its universal database product, DB2. In this context, universal refers to the fact the software operates on UNIX, Windows and Linux. The files are largemore than 500 Mbwhich suggest downloading it only if you have access to a fast connection or can pull in the files while you're out to a long lunch. The place to go is http://www-3.ibm.com/software/data/db2/udb/downloads.html.

Oracle is a database management system that powers many CMMS packages. The source code for the company's database and other software offerings is available for download, too. Access the Oracle Technology Network by clicking on over to http://otn.oracle.com/software/content.html.

If your operating system of choice is Linux, it should come as no surprise that Red Hat, the software company, also offers product downloads. Take for example Red Hat Database 2.1, which is found at http://www.redhat.com/apps/download/.

Applications

It seems the folks down under must comply with the Australian Hazardous Goods Legislation. I can't imagine it's any worse that what's dictated by our hired hands in Washington. In an attempt to ease the Aussie's compliance headaches, Thoroughly Useful Software, Glengowrie, South Australia, offers the Chemical Inventory Database. The package, based on Microsoft Access, tracks your chemical inventory and is most suitable for facilities that store only moderate amounts of the wet nasties. The software indicates where a particular chemical is stored and displays the MSDS information on the monitor. Use it to produce manifests and keep track of inventory. The best part is that a limited version is available for free at www.picknowl.com.au/homepages/tus/download.htm.

Access

If you're having trouble getting the Australian software to run, you might want to brush up on using Access. Microsoft's Web site offers tips and tricks, templates, tutorials and other information designed to turn you into a softwaremeister. Nudge your mouse in the general direction of http://www.microsoft.com/office/access/using/default.asp for the details.

Despite your best efforts at database construction, sometimes the blasted software won't get with the program and read your mind. Instead, all it gives you are a series of arcane error messages, each of which is disguised as plain English words. Now, it's Bruce Corkhill to the rescue. He posted ASP Microsoft Access Database Errors FAQs at http://www.webwizguide.info/asp/faq/access_database_faq.asp. Now when Access starts acting a bit more recalcitrant than a piece of common software should, you can get a cogent explanation before you apply a liberal dose of percussive maintenance to the side of your monitor.

Florida Gulf Coast University, Fort Meyers, Fla., also offers a tutorial for Access at http://www.fgcu.edu/support/office2000/access/. In fact, the school offers tutorials for the rest of the software in the Microsoft Office suite.

You may already have spreadsheets full of information you refer to as a database. Technically speaking, they aren't, but that can be our secret. The best fix is to convert your hard-won data into a genuine database. Thanks to our friends at About.com, you can learn to import spreadsheets into Access if you're inclined to mouse on over to http://databases.about.com/library/weekly/aa123100a.htm. Importing a Spreadsheet into Access is a three-page document that will walk you through the necessary steps.

CMMS

In this magazine, no database article would be complete without searching for a CMMS. Just for grins, we searched for sites that contained two termsCMMS and download. The next thing you know, we're looking at more than 8,000 Web citations. Lacking sufficient time to check each individually, we picked one at random using a complex methodology based on its own super-duper database. A result of that selection is a package that comes to you from Clueword DotCom Sdn. Bhd., Kuala Lumpur, Malaysia. The company's full-featured software offering, CWorks Computerized Maintenance Management System, is found at http://www.cworks.com.my and is available for free.

If you don't believe in a global economy and prefer a domestic source, fire up your browser and examine those 8,000 responses. I'd be interested in learning what you find.