SQL Definition and History

Every business has data, which requires some organized method or mechanism for maintaining the data. This mechanism is referred to as a database management system (DBMS). Database management systems have been around for years, many of which started out as flat-file systems on a mainframe. With today's technologies, the accepted use of database management systems has begun to flow in other directions, driven by the demands of growing businesses, increased volumes of corporate data, and of course, Internet technologies.

The modern wave of information management is primarily carried out through the use of a relational database management (RDBMS), derived from the traditional DBMS. Relational databases and client/server technologies are typical combinations used by current businesses to successfully manage their data and stay competitive in their appropriate markets. The next few sections discuss the relational database and client/server technology to provide you with a stronger foundation of knowledge for the standard relational database language—SQL.

What Is SQL?

SQL, Structured Query Language, is the standard language used to communicate with a relational database. The prototype was originally developed by IBM using Dr. E.F. Codd's paper ("A Relational Model of Data for Large Shared Data Banks") as a model. In 1979, not long after IBM's prototype, the first SQL product, ORACLE, was released by Relational Software, Incorporated (it was later renamed Oracle Corporation). It is, today, one of the distinguished leaders in relational database technologies. SQL is pronounced either of two ways: as the letters S-Q-L, or as "sequel"; both pronunciations are acceptable.

If you travel to a foreign country, you may be required to know that country's language to get around. For example, you may have trouble ordering from a menu via your native tongue if the waiter speaks only his country's language. Look at a database as a foreign land in which you seek information. SQL is the language you use to express your needs to the database. Just as you would order a meal from a menu in another country, you can request specific information from within a database in the form of a query using SQL.

What Is ANSI SQL?

The American National Standards Institute (ANSI) is an organization that approves certain standards in many different industries. SQL has been deemed the standard language in relational database communication, originally approved in 1986 based on IBM's implementation. In 1987, the ANSI SQL standard was accepted as the international standard by the International Standards Organization (ISO). The standard was revised again in 1992 and was called SQL/92. The newest standard is now called SQL3 or is sometimes referred to as SQL/99.

The New Standard: SQL3

SQL3 has five interrelated documents and other documents may be added in the near future. The five interrelated parts are as follows:

• Part 1—SQL/Framework— Specifies the general requirements for conformance and defines the fundamental concepts of SQL.

• Part 2—SQL/Foundation— Defines the syntax and operations of SQL.

• Part 3—SQL/Call-Level Interface— Defines the interface for application programming to SQL.

• Part 4—SQL/Persistent Stored Modules— Defines the control structures that then define SQL routines. Part 4 also defines the modules that contain SQL routines.

• Part 5—SQL/Host Language Bindings— Defines how to embed SQL statements in application programs that are written in a standard programming language.

The new ANSI standard (SQL3) has two levels of minimal conference that a DBMS may claim: Core SQL Support and Enhanced SQL Support.

ANSI stands for American National Standards Institute, an organization that is responsible for devising standards for various products and concepts.

With any standard come numerous, obvious advantages, as well as some disadvantages. Foremost, a standard steers vendors in the appropriate industry direction for development; in the case of SQL, providing a basic skeleton of necessary fundamentals which, as an end result, allows consistency between various implementations and better serves increased portability (not only for database programs, but databases in general and individuals who manage databases).

Some may argue that a standard is not so good, limiting the flexibility and possible capabilities of a particular implementation. However, most vendors who comply with the standard have added product-specific enhancements to standard SQL to fill in these gaps.

A standard is good, considering the advantages and disadvantages. The expected standard demands features that should be available in any complete SQL implementation and outlines basic concepts that not only force consistency between all competitive SQL implementations, but increase the value of a SQL programmer or relational database user in today's database market.

A SQL implementation is a particular vendor's SQL product.

What Is a Database?

In very simple terms, a database is a collection of data. Some like to think of a database as an organized mechanism that has the capability of storing information, through which a user can retrieve stored information in an effective and efficient manner.

People use databases every day without realizing it. A phone book is a database. The data contained consists of individuals' names, addresses, and telephone numbers. The listings are alphabetized or indexed, which allows the user to reference a particular local resident with ease. Ultimately, this data is stored in a database somewhere on a computer. After all, each page of a phone book is not manually typed each year a new edition is released.

The database has to be maintained. As people move to different cities or states, entries may have to be added or removed from the phone book. Likewise, entries will have to be modified for people changing names, addresses, or telephone numbers, and so on. Figure 1.1 illustrates a simple database.

An Introduction to the Relational Database

A relational database is a database divided into logical units called tables, where tables are related to one another within the database. A relational database allows data to be broken down into logical, smaller, more manageable units, allowing for easier maintenance and providing more optimal database performance according to the level of organization. In Figure 1.2, you can see that tables are related to one another through a common key in a relational database.

Again, tables are related in a relational database, allowing adequate data to be retrieved in a single query (although the desired data may exist in more than one table). By having common keys, or fields, among relational database tables, data from multiple tables can be joined to form one large result set. As you venture deeper into this book, you see more of a relational database's advantages, including overall performance and easy data access.

A relational database is a database composed of related objects, primarily tables. A table is the most basic means of storage for data in a database.

An Introduction to Client/Server Technology

In the past, the computer industry was predominately ruled by mainframe computers; large, powerful systems capable of high storage capacity and high data processing capabilities. Users communicated with the mainframe through dumb terminals—terminals that did not think on their own, but relied solely on the mainframe's CPU, storage, and memory. Each terminal had a data line attached to the mainframe. The mainframe environment definitely served its purpose, and does today in many businesses, but a greater technology was soon to be introduced: the client/server model.

In the client/server system, the main computer, called the server, is accessible from a network—typically a local area network (LAN) or a wide area network (WAN). The server is normally accessed by personal computers (PCs) or by other servers, instead of dumb terminals. Each PC, called a client, is provided access to the network, allowing communication between the client and the server, thus explaining the name client/server. The main difference between client/server and mainframe environments is that the user's PC in a client/server environment is capable of thinking on its own, capable of running its own processes using its own CPU and memory, but readily accessible to a server computer through a network. In most cases, a client/server system is much more flexible for today's overall business needs and is much preferred.

Relational database systems reside on both mainframes and on client/server platforms, Although a client/server system is preferred, the continued use of mainframes can certainly be justified according to a company's needs. A high percentage of companies have recently been leaving their mainframe systems behind and moving their data to a client/ server system, motivated by the urge to stay current with new technologies, provide more flexibility to better suit their business needs, and make old systems Year 2000-compliant.

The switch to a client/server system has proven beneficial for some companies, while others have failed in the client/server implementation and have, as a result, wasted millions of dollars, causing some to return to their mainframes; others still hesitate to make a change. The lack of appropriate expertise—a result of new technology combined with a lack of training—is the main reason for failed implementations. Nevertheless, an understanding of the client/server model is imperative with the rising (and sometimes unreasonable) demands placed on today's businesses as well as the development of Internet technologies and network computing. Figure 1.3 illustrates the concept of client/server technology.

Some Popular Relational Database Vendors

Some of the most predominant database vendors include Oracle, Microsoft, Informix, Sybase, and IBM. Although there are many more, this list includes names that you may have recognized on the bookshelf, in the newspaper, magazines, the stock market, or on the World Wide Web.

Differences Between Implementations

As each individual in this world is unique in both features and nature, so is each vendor-specific implementation of SQL. A database server is a product, like any other product on the market, manufactured by a widespread number of vendors. It is to the benefit of the vendor to ensure that its implementation is compliant with the current ANSI standard for portability and user convenience. For instance, if a company is migrating from one database server to another, it would be rather discouraging for the database users to have to learn another language to maintain functionality with the new system.

With each vendor's SQL implementation, however, you find that there are enhancements that serve the purpose for each database server. These enhancements, or extensions, are additional commands and options that are simply a bonus to the standard SQL package and available with a specific implementation.