Making data useful
For a collection of data to be useful, you must be able to easily and quickly retrieve the particular data you want, without having to wade through all the rest of the data. One way to make this happen is to store the data in a logical structure. Flat files don’t have much structure, but databases do. Historically, the hierarchical database model and the network database model were developed before the relational model. Each one organizes data in a different way, but all three produce a highly structured result. As a result, starting in the 1970s, any new development projects were most likely to be done using one of three database models: either the hierarchical model, the network model, or the relational model. I explore database models further in the “Competing Database Models” section, later in this chapter.
Of all the operations that people perform on a collection of data, the retrieval of specific elements out of the collection is the most important. This is because retrievals are performed more often than any other operation. Data entry is done only once. Changes to existing data are made infrequently, and data is deleted only once. Retrievals, on the other hand, are performed frequently, and the same data elements may be retrieved many times. Thus, if you could optimize only one operation performed on a collection of data, that one operation should be data retrieval. As a result, modern database management systems put a great deal of effort into making retrievals fast. Retrievals are performed by queries. A modern database management
system analyzes a query that is presented to it and decides how best to perform it. Generally there are multiple ways of performing a query, some much faster than others. A good DBMS consistently chooses a near-optimal execution plan. Of course, it helps if the query is formulated in an optimal manner to begin with. I discuss this subject in depth in Book VII, which covers database tuning.
A simple example of an E-R model
In this section, as an example, I apply the principles of E-R models to a hypothetical Web-based business named Gentoo Joyce that sells apparel items with a penguin motif, such as T-shirts, scarves, and dresses. The business displays its products and takes credit card orders on its Web site. There is no “brick and mortar” store. Fulfillment is outsourced to a fulfillment house, which receives and warehouses product from vendors, and then, upon receiving orders from Gentoo Joyce, ships the orders to customers. The Web site front end consists of pages that include descriptions and pictures of the products, a shopping cart, and a form for capturing customer and payment information. The Web site back end holds a database that stores customer, transaction, inventory, and order shipment status information. Figure 2-14 shows an E-R diagram of the Gentoo Joyce system. It is an example typical of a very small “boutique” business. Gentoo Joyce buys goods and services from three kinds of vendors: product suppliers, Web hosting services, and fulfillment houses. In the model, VENDOR is a super type of SUPPLIER, HOST, and FULFILLMENT_HOUSE. Some attributes are shared among all the vendors; these are assigned to the VENDOR entity. Other attributes are not shared and are instead attributes of the subtype entities. A many-to-many relationship exists between SUPPLIER and PRODUCT because a supplier may provide more than one product, and a given product may be supplied by more than one supplier. Similarly, any given product will (hopefully) appear on multiple orders, and an order may include multiple products. Such many-to-many relationships can be problematic. I discuss how to handle such problems in Book II. The other relationships in the model are one-to-many. A customer can place many orders, but each order comes from one and only one customer. A fulfillment house can stock multiple products, but each product is stocked by one and only one fulfillment house.
II. SQL Overview
in the early days of relational database management systems, as is true for the early days of just about anything, there was no standard language for performing relational operations on data. A number of companies came out with relational database management system products, and each had its own associated language. There were some general similarities among the languages would prevail. The logic was that once developers learned a language, they would want to stick with it on subsequent projects. This steaming cauldron of ideas set the stage for the emergence of SQL. There was one company that had more market power than all the others combined, and had the additional advantage of being the employer of the inventor of the relational database model.
Where SQL Came From
It is interesting to note that even though Dr. Codd was an IBM employee when he developed the relational database model, IBM’s initial support of that model was lukewarm at best. One reason might have been the fact that IBM already had a leading position in the database market with its IMS hierarchical DBMS. In 1978, IBM released System/38, a minicomputer that came with a RDBMS that was not promoted heavily.
As a result, in 1979, the world was introduced to a fully realized RDBMS by a small startup company named Relational Software, Inc. headed by Larry Ellison. Relational’s product, called Oracle, is still the leading relational database management system on the market today.