email article printer friendly

Relational Database Theory in a Nutshell
How to think of your data as a set of components, not a monolithic table.
by Buck Woody

Posted July 12, 2004

On April 25, 1986, reactor No. 4 at the Chernobyl nuclear plant was to be shut down for routine maintenance. Sensing a good opportunity, junior-level personnel decided to run a test of the diesel engines that would provide power to the critical cooling system in the event of a power failure.

The test proceeded, but when the system reached about half power, an electric load dispatcher decided that he would not allow the test to continue. After a brief discussion, he was overridden and the power was shut down to the cooling system.

Unfortunately, the power stabilization voltage was set far too low, and coolant rushed into the system, causing a drop in steam pressure. The technicians had to withdraw some, and then all, control rods, causing the system to become unstable. When the temperature skyrocketed, the fuel exploded, mixing with steam and destroying the reactor core. Some two or three minutes later another explosion added to the destruction. The safety cap designed to contain a fire was blown into an odd angle, allowing the radioactive material to burn through the flooring, contaminating two adjoining rooms.

The results of the devastation include the deaths of almost all onsite personnel, as well as most of the firefighters and pilots who were brought in to battle the ensuing blaze. The town was evacuated, and it will remain uninhabitable for more than 300 years. The resulting cement casing is unstable, and a huge effort is being mounted by the Russian government to create a more permanent burial for the site.

The World Nuclear Association made the following statements regarding the accident:

"The Chernobyl accident in 1986 was the result of a flawed reactor design that was operated with inadequately trained personnel and without proper regard for safety."

The overall problems cited involved a disregard for safety procedures and a poor design.

Technical professionals can learn from these errors. Although your database designs might not be used in a nuclear plant, you might still cause significant harm if your systems suffer from the same problems as the Chernobyl reactors: flawed design, inadequate training, and a lack of professionalism.

The best way to defend against these problems is to understand how SQL Server works and be familiar with its parts and how they relate to each other. In my last column, I discussed SQL Server in length, as well as how programs move data through the system. In this article, you'll learn more about the parts of the individual databases it houses.

Physical Components
SQL Server databases are made up of two parts: the binary code that runs the database system and the files that store the data.

There are two types of files that store data. The first stores data and the second stores a logging file. Data is written first to the log file and then on to the database during a process called a checkpoint. This process is known as a two-phase commit.

To better understand this process, let's assume that three separate data sets (called transactions) occur. The first records a sale, the second another sale, and the third deletes the first sale. Those transactions look like what we see in Table 1.

Let's assume further that we're writing this process on a piece of paper. We might be tempted to record it as shown in Table 2.