Databases Overview

Databases come in all different shapes and sizes. They can be flat files of ASCII data (like Q&A) or complex binary tree structures (Oracle or Sybase). In any form, a database is a data store, or a place that holds data.

If a database is simply a collection of data, then what keeps track of changes to this data?

That is the job of the database management system, or DBMS. Some DBMSs are relational. Those are RDBMS. The relational part refers to the fact that separate collections of data within the reaches of the RDBMS can be looked at together in unison. The RDBMS is responsible for ensuring the integrity of the database. Sometimes, things will get out of whack and the RDBMS will keep all that data in line.

Database Terminology

In the days of yours, programming database applications are pretty simple. There were mainframe databases and there were very few microcomputer databases available. The ones that were available cost an arm and a leg. The cheap ones were, well, you got what you paid for. But you always had database files, records, and fields. The database terms of yesteryear, however, have been replaced by new ones. Some of the bigger database companies like Oracle and Sybase have redefined database terminology. The main thrust of this redefining is most likely in response to the larger customer base that is not "programming-literate."

A programmer can deal with files, records and fields. But more and more non-technical people are creating database applications and queries these days. Their formal training is through the use of applications. As you'll see, some of the new terms are commonly found in spreadsheet and word processing programs.

The following is a list of current database terms that will be used in the rest of this chapter and throughout the book:

• Client\Server. Client\server is more of an architecture than a tangible entity. The client is a computer system that requests the services provided by an entirely different computer system. On a smaller scale, the client and server may be separate processes running on the same computer system. The distinction is that there is a service provider (the server), and a consumer of that service (the client). For your purposes here, the server would be the RDBMS, and the client is your application that is requesting data from the server.
• Database or Instance. The database or instance is the entity, or collection of data, that is created and stored for retrieval and modification. Depending on the RDBMS, several of these entities can exist on a single machine. For instance, multiple Oracle instances can exist on a UNIX server. Each has a distinct area for data, and unless properly configured, they have no knowledge of each other. A database or instance is comprised of schemas.
• Schema. A schema is a collection of database objects that belongs to a single user of the database. Databases have many users.
• Table. A table is a database object that contains a single set of data. Like things are stored in tables. For instance, at a company a normal table to have is an employee table. This would store all kinds of information about an employee. A table contains rows of data.
• Row. A row of data is a single record in a table. A row is divided into columns.
• Column. A column is the smallest unit of data in a table. It is a part of the row. When data is displayed in a spreadsheet-like fashion, a column would be the up/down slice of data.

In order to "talk" to your database you need to use some sort of software. Whether it comes with your server or you have to write the code yourself, this software is essential for database communications.

Although there are innumerable methods of retrieving and storing data, the following are the most common:

• SQL (Structured Query Language) the most common data access method,
• ODBC (Open Database Connectivity)
• Native methods are rarely used.

Database Client/Server Methodology

The evolution of relational data storage began in 1970 with the work of Dr. E. F. Codd, who proposed a set of 12 rules for identifying relationships between pieces of data. Codd's rules formed the basis for the development of systems to manage data. Today, Relational Database Management Systems (RDBMS) are the result of Codd's vision.

Data in an RDBMS are stored as rows of distinct information in tables. A structured language is used to query (retrieve), store and change the data. The Structured Query Language (SQL) is an ANSI standard, and all major commercial RDBMS vendors provide mechanisms for issuing SQL commands.

Single-Tier Database Design

The early development of RDBMS applications utilized an integrated model of user interface code, application code and database libraries. This single binary model ran only on a local machine, typically a mainframe. The applications were simple but inefficient and did not work over LANs. The model did not scale the application and user interface code was tightly coupled to the database libraries.

Application

Database Libraries

Two-Tier Database Design

Two-tier model appeared with the advent of server technology. Communication-protocol development and extensive use of local and wide area networks allowed the database developer to create an application front end that accessed data through a connection (socket) to the back-end server. A two-tier database design, where the client software is connected to the database through a socket connection.

Client programs (applying a user interface) send SQL requests to the database server. The server returns the appropriate results, and the client is responsible for the formatting and display of the data. Clients still use a vendor-provided library of functions that manage the communication between client and server. Most of these libraries are written in either the C language or Perl.

Commercial database vendors realized the potential for adding intelligence to the database server. They created proprietary techniques that allowed the database designer to develop macro programs for simple data manipulation. These macros, called stored procedures, can cause problems relating to version control and maintenance. Because a stored procedure is an executable program living on the database, it is possible for the stored procedure to attempt to access named columns of a database table after the table has been changed.

For example, if a column with the name id is changed to cust_id, the meaning of the original stored procedure is lost. The advent of triggers, which are stored procedures executed automatically when some action (such as insert) happens with a particular table or tables, can compound these difficulties when the data returned from a query are not expected. Again, this can be the result of the trigger reading a table column that has been altered.

Limitations of Two-Tier Database Design

• The vendor-provided library limits them. Switching from one database vendor to another requires a rewrite of a significant amount of code to the client application.
• Version control is an issue. When the vendor updates the client-side libraries, the applications that utilize the database must be recompiled and redistributed.
• Vendor libraries deal with low-level data manipulation. Typically, the base library only deals with queries and updates of single rows or columns of data. This can be enhanced on the server side by creating a stored procedure, but the complexity of the system then increases.
• All of the intelligence associated with using and manipulating the data is implemented in the client application, creating large client-side runtimes. This drives up the cost of each client set.

Three-Tier Database Design

In a multi-tier design, the client communicates with an intermediate server that provides a layer of abstraction from the RDBMS. The intermediate layer is designed to handle multiple client requests and manage the connection to one or more database servers. There does not have to be just three tiers, but conceptually this is the next step.

Advantages of Three-Tier Design

• It is multithreaded to manage multiple client connections simultaneously.
• It can accept connections from clients over a variety of vendor-neutral protocols (from HTTP to TCP/IP), then hand off the requests to the appropriate vendor-specific database servers, returning the replies to the appropriate clients.
• It can be programmed with a set of "business rules" that manage the manipulation of the data. Business rules could include anything from restricting access to certain portions of data to making sure that data is properly formatted before being inserted or updated.
• It prevents the client from becoming too heavy by centralizing process-intensive tasks and abstracting data representation to a higher level.
• It isolates the client application from the database system and frees a company to switch database systems without having to rework the business rules.
• It can asynchronously provide the client with the status of a current data table or row.