Datasheet

SQL and Relational Database Management Systems 1
FIGURE 1-1
Hierarchical structure
ROOT
CHILD 3/LEVEL 1
CHILD 2/LEVEL 1CHILD 1/LEVEL 1
CHILD 1/LEVEL 2CHILD 1/LEVEL 2CHILD 2/LEVEL 2CHILD 1/LEVEL 2
It is based on ‘‘parent/child’’ paradigm in which each parent could have many children but each
child has one and only one parent. You can visualize this structure as an upside down tree, start-
ing at the root (trunk) and branching out at many levels (see Figure 1-1).
Because the records in a child table are accessed through a hierarchy of levels, there could not
be a record in it without a corresponding pointer record in the parent table — all the way up to
the root. You could compare it to a file management system (like a tree view seen in the
Microsoft Windows Explorer) — to get access to a file within a directory, you must first open
the folder that contains this file.
The term ‘‘table’’ is not a part of hierarchical database jargon. It was first introduced
in the relational database model, discussed later in this chapter. Nevertheless, this
book also uses ‘‘tables’’ to refer to hierarchical and network databases’ ‘‘storage areas’’ (struc-
tured files). After all, a table can be simply defined as a set of elements organized in rows and
columns.
Let’s improve upon the previously discussed flat file model. Instead of dumping all of the
information into a single file, you are going to split it among three tables, each containing
pertinent information: business name and address for the
CUSTOMER table; product description,
brand name, and price for the
PRODUCT table; and an ORDER HEADER table to store the details
of the order.
In the hierarchical database model, redundancy is greatly reduced (compared with the flat file
database model). You store information about customer, product, and so on once only. The table
ORDER HEADER (see Figure 1-2) would contain pointers to the customer and to the products
this customer had ordered. Whenever you need to see what products any particular customer
purchased, you start with the
ORDER HEADER table, and find the list of IDs for all the customers
who placed orders and the list of product IDs for each customer; then, using the
CUSTOMER
table, you find the customer name you are after. Using the products IDs list, you get the descrip-
tion of the products from the
PRODUCT table.
13