Datasheet

Part I SQL Basic Concepts and Principles
FIGURE 1-2
Hierarchical database example
ORDER_HEADER
PRODUCTCUSTOMER
Everything works great as long as you are willing to put up with a somewhat nonintuitive way
of retrieving information. (No matter what information is requested, you always have to start
with the root, which in this example is the
ORDER HEADER table.) Should you need only cus-
tomers’ names, the hierarchical database would be blazingly fast — going straight from a par-
ent table to the child one. To get any information from the hierarchical database, a user has to
have an intimate knowledge of the database structure; and the structure itself is extremely inflex-
ible. For example, if you decided that the customers must place an order through a third party,
you’d need to rewire all relationships because the
CUSTOMER table would not be related to the
ORDER HEADER table anymore, and all your queries would have to be rewritten to include one
more step — finding the sales agent who sold this product, and then finding customers who
bought it. It also makes obvious the fact that you did not escape the redundancy problem — if
you have a customer who places an order through more than one sales agent, you’ll have to
replicate all the information for each agent in a number of customer tables.
What happens if you need to add a customer that does not have a placed order, or a product
that no one yet ordered? You cannot — your hierarchical database is incapable of storing infor-
mation in child tables without a parent table having a pointer to it. By the very definition of
hierarchy, there should be neither a product without an order, nor a customer without an
order — which obviously cannot be the case in the real world.
Hierarchical databases handle one-to-many relationships (described in Chapter 2) very well;
however, in many cases, you will want the child to be related to more than one parent. Not only
could one product be present in many orders, but one order could contain many products.
There is no answer (at least not an easy one) within the domain of hierarchical databases.
Network databases
Attempts to solve the problems associated with hierarchical databases produced the network database
model. This model has its origins in the Conference on Data Systems Languages (CODASYL), an
organization founded in 1957 by the U.S. Department of Defense. CODASYL was responsible for
developing COBOL — one of the first widely popular programming languages — and publishing the
Network Database standard in 1971. The most popular commercial implementation of the network
model was Adabas (long since converted to the relational model).
The network model is very similar to the hierarchical one — it is also based on the concept of
parent/child relationships, but it doesn’t have the restriction of one child having only one par-
ent. In the network database model, a parent can have multiple children, and a child can have
multiple parents. This structure could be visualized as several trees that share some branches. In
network database jargon, these relationships came to be known as sets.
14