Databases

Databases

Databases are designed to manipulate large amounts of information by inputting, storing, retrieving, and managing that information. Databases use a table format, with Microsoft Access being one of the most widely used.

Databases consist of rows and columns. Each piece of information is entered into a row, which creates a "record." Databases are commonly used when saving addresses or other types of long lists of information. Once the records are created in the database, they can be sorted and manipulated in a variety of ways that are limited primarily by the software being used.
 
The word data is normally defined as facts from which information can be derived. For example, "Fred Crouse lives at 2209 Maple Avenue" is a fact. A database may contain millions of such facts. From these facts the database management system (DBMS) can derive information in the form of answers to questions such as "How many people live on Maple Avenue?" The popularity of databases in business is a direct result of the power of DBMSs in deriving valuable business information from large collections of data.
 
Databases are somewhat similar to spreadsheets, but databases are more powerful than spreadsheets because of their ability to manipulate the data. It is possible to do a number of functions with a database that would be more difficult to do with a spreadsheet. Consider these actions that are possible to do with a database:
 
Perform a variety of cross-referencing activities
Complete complicated calculations
Bring current records up to date
Retrieve large amounts of information that match certain criteria
 
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. Nevertheless, 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:
 
Customers
 
customer_no name address phone
1001 Jones 320 Main 555-8811
1002 Smith 401 Oak 555-8822
1003 Brown 211 Elm 555-8833
1004 Green 899 Maple 555-8844
Products
product_no description price
25 Ring 3.25
33 Gasket 1.23
45 Shaft 4.55
Sales
sale_no date customer_no product_no
841 3/11 1002 45
842 3/12 1001 25
843 3/12 1002 45
844 3/13 1004 33
845 3/14 1003 25
846 3/15 1002 33
 
Suppose one wants to know the customer's name for sale number 845. Looking in the customer number column of the Sales table, one will see that it was customer 1003. Next, one refers to the Customers table and finds customer 1003. Here one see the customer's name is Brown. So, Brown was the customer for sale number 845.
 
Structured Query Language
 
The foregoing 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 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." Here are a few examples of queries expressed in SQL:
 
Query: Which products have a price over $2?
SQL solution: Select product_no, description
From Products
Where price > 2.00
Result: product_no description
25 Ring
45 Shaft
 
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 are 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.
 
Query: When have we sold product number 45 to customer 1002?
SQL solution: Select date
From Sales
Where product_no = 45 and customer_no = 1002
Result: date
3/11
3/12
 
In this example one 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 the sample database there are two rows that satisfy this condition, and the query's result yields the dates from those two rows.
 
The next query gives the SQL solution to the original query discussed above.
 
Query: What is the customer's name for sale number 845?
SQL solution: 
Select name
From Customers, Sales
Where sale_no = 845 and Sales.customer_no =
Customers.customer_no
Result: Brown
 
This query illustrates how one can query more than one table at once in SQL. First, one lists all tables needed to answer the query. In this case then, one lists the Customers and the Sales tables. Then in the Where clause, one states 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. Because there is only one row in the Sales table having that value, one has limited one's query to that single row. The second condition indicates that one wants 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 one's 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 one that one should give the value from the name column as one's result. As shown before, the resulting customer name is "Brown."
 
Queries can also be used to perform calculations:
 
Query: What is the average price of our products?
SQL solution: Select Avg (price)
From Products
Result: 3.01
 
SQL also provides statements that can be used to make changes to data in the database. For example, suppose one wanted to increase the price of one's products by 3 percent. Then the following statement can be used:
 
Update Products
Set price = 1.03 * price
 
This statement will cause the price of every product in the Products table to be increased by 3 percent. Note that it does not matter whether one has 3 products, as shown in the sample database, or 300,000 products. A single statement will update the prices of all products. Of course, if one wants to change only the prices of selected products, one can do that, too:
 
Update Products
Set price = 1.03 * price
Where product_no = 33
 
This statement will change only 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 multiuser. 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 database manufacturers that address this market. A multiuser 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. Multiuser 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 DBMS for use in managing a database should consider the following factors:
 
Relational
 
Virtually all major commercial DBMSs are relational, because the desirability of relational databases is well-accepted in the database community.
 
SQL
 
In addition, because the ANSI has adopted SQL as it standard for relational databases, the desired DBMS should support SQL.
 
Capacity
 
As noted above, DBMSs are designed for a variety of environments. Some are designed to be single-user systems, while others are designed for medium-sized businesses, while still others are designed for large businesses. The system selected should naturally be one that has been shown to be successful in and appropriate for the environment for which it is chosen.
 
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 DBMSs are central to modern business information systems. Relational databases using SQL 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. DBMSs can be sophisticated and expensive, and consequently their purchase requires careful, informed consideration.
 
Bibliography
 
Dunham, Jeff (1998). Database performance tuning handbook. New York: McGraw-Hill.
 
Groff, James R., and Weinberg, Paul N. (2002). SQL: The complete reference (2nd ed.). Berkeley, CA: Osborne/McGraw-Hill.
 
Hansen, Gary W., and Hansen, James V. (1996). Database management and design (2nd ed.). Upper Saddle River, NJ: Prentice Hall.
 
Kroenke, David M. (2006). Database processing: Fundamentals, design, and implementation (10th ed.). Upper Saddle River, NJ: Pearson Prentice Hall.
 
Post, Gerald V. (2005). Database management systems: Designing and building business applications (3rd ed.). Boston: McGraw-Hill/Irwin.
 
Rob, Peter, and Semaan, Elie (2004). Databases: Design, development and deployment (2nd ed.). Boston: McGraw-Hill.
 
Dorothy Maxwell
 
Gary Hansen
  • Recommend Us