Databases

With the rise of business data-processing systems on a very large scale during the 1960s and 1970s came the development of databases and database management systems. Databases are large collections of interrelated data stored on computer disk systems from which they can be immediately accessed and revised. Database management systems are large computer programs that "manage" or control the databases.

Data is normally defined as "facts" from which information can be derived. For example, "Janene Clouse lives at 1411 Sycamore Avenue" is a fact. A database may contain millions of such facts. From these facts the database management system can derive information in the form of answers to questions such as "How many people live on Sycamore Avenue?" The popularity of databases in business is a direct result of the power of database management systems in deriving valuable business information from large collections of data.

Relational Databases

Most modern databases are relational, meaning that data are stored in tables, consisting of rows and columns, and that data in different tables are related by the meanings of certain common columns. (The tables in a database are sometimes called "files," the rows are called "records," and the columns are called "fields." However, this is an older terminology, left over from the early days of business computer systems.) The following is an example of a simple relational database consisting of three tables—one for customers, one for products, and one for sales:

Suppose we want to know the customer's name for sale number 845. We look in the customer number column of the Sales table, and we see that it was customer 1003. Next, we refer to the Customers table and find customer 1003. Here we see the customer's name is Brown. So, Brown was the customer for sale number 845.

Structured Query Language

The going is a simple example of a database query. In a modern database, queries are expressed in a query language, which requires a particular format that can be recognized and interpreted by the database management system (DBMS). The standard query language for relational databases, as adopted by the American National Standards Institute (ANSI), is SQL, which is generally understood to be an abbreviation for "Structured Query Language." Let us look at a few examples of queries expressed in SQL.

This query's SQL solution illustrates the SQL format. In general, SQL "statements" have a Select "clause," a From "clause," and a Where "clause." The Select clause lists the columns that are to be shown in the result, the From clause lists the database tables from which data is to be taken, and the Where clause gives the condition to be applied to each row in the table. If a row satisfies the condition, then it is selected, and the values in that row for the columns listed in the Select clause are included in the result.

In this example you can see that the condition in the Where clause includes the connector "and," which indicates that both conditions (product–no 45 and customer–no 1002) must be fulfilled. In our sample database there are two rows that satisfy this condition, and the query's result yields the dates from those two rows.

Our next query gives the SQL solution to the original query we discussed above.

This query illustrates how we can query more than one table at once in SQL. First, we list all tables needed to answer the query. In this case then, we list the Customers and the Sales tables. Then in the Where clause we give two conditions: sale_no = 845 and

Sales.customer_no =
Customers.customer_no

The first condition indicates that the sale_no column must have a value of 845. Since there is only one row in the Sales table having that value, we have limited our query to that single row. The second condition indicates that we want only that row in the Customers table which has the same value for its customer_no column as the Sales row has for its customer _no column. This condition then limits our result to the joining together of one row from the Sales table and one row from the Customers table. Finally, the Select clause,

Select name tells us that we should give the value from the name column as our result. As we showed before, the resulting customer name is "Brown."

Queries can also be used to perform calculations:

SQL also provides statements that can be used to make changes to data in the database. For example, let's suppose we want to increase the price of our products by 3 percent. Then we can use the following statement:

Update Products

Set price = 1.03 * price

This statement will cause the price of every product in our Products table to be increased by 3 percent. Note that it doesn't matter whether we have 3 products, as shown in our sample database, or 300,000 products. A single statement will update the prices of all products. Of course, if we only want to change the prices of selected products we can do that, too:

Update Products

Set price = 1.03 * price

Where product_no = 33

This statement will only change the price of product number 33. SQL also provides statements to Insert new rows into tables and to Delete rows from tables.

These queries show only a very small number of the capabilities of SQL. The Where clause can be used to select rows based on where names are in the alphabet, whether dates are before or after certain other dates, based on averages, and based on many other conditions.

Small and Large Databases

Databases can be single-user or multi-user. A single-user database exists on a single computer and is accessible only from that computer. Many single-user databases exist, and there are a number of commercial DBMSs that address this market. A multi-user database may exist on a single machine, such as a mainframe or other powerful computer, or it may be distributed and exist on multiple computers. Multi-user databases are accessible from multiple computers simultaneously. With the rise of the Internet, many databases are publicly accessible. For example, the holdings of university libraries are maintained on databases that can be browsed from remote locations. A person interested in locating a book in a library can enter the book's title, author, or subject, and a database query will be automatically performed. Information on the desired book or list of books will be returned to the person's computer.

Selecting a Database System

A person or business seeking to purchase a database management system for use in managing a database should consider the following factors:

Relational: Virtually all major commercial database management systems are relational, since the desirability of relational databases is well-accepted in the database community.

SQL: In addition, since the American National Standards Institute has adopted SQL as it standard for relational databases, the desired DBMS should support SQL.

Capacity: As noted above, database management systems are designed for a variety of environments. Some are designed to be single-user systems, others are designed for medium-sized businesses, while still others are designed for large businesses. The system chosen should naturally be one that has been shown to be successful in and appropriate for the environment it is chosen for.

Disaster recovery capability: More sophisticated systems are more capable of recovering from power outages, computer hardware failure, and the like than are the single-user systems. They use sophisticated logging and database locking facilities that make such recovery possible. Often, these facilities are unnecessary for single-user systems.

Summary

Databases and database management systems are central to modern business information systems. Relational databases using the SQL language provide substantial logical power to help businesses make informed decisions based on their own data. Database systems can be small and handled by a single user, or they can be large and available to multiple users. They are even publicly available through the Internet. Database management systems can be sophisticated and expensive, and consequently their purchase requires careful, informed consideration.

Bibliography

Dunham, Jeff. (1998). Database Performance Tuning Handbook. NewYork: McGraw-Hill.

Groff, James R. and Weinberg, Paul N. (1999). SQL: The Complete Reference. Berkeley: Osborne/McGraw-Hill.

Hansen, Gary W. and Hansen, James V. (1996). Database Management and Design, 2d ed. Upper Saddle River, NJ: Prentice Hall.

Kroenke, David M. (2000). Database Processing, 7th ed. Upper Saddle River, NJ: Prentice Hall.

Rob, Peter and Coronel, Carlos. (2000). Database Systems: Design Implementation, and Management, 4th ed. Cambridge, MA: Course Technology.

[Article by: GARY HANSEN]