An Example Of A Distributed Database Design

The key to success with distributed servers is very simple. Start small and phase in new servers only after communications with the existing servers have been debugged. Companies choosing a very large or mission-critical system often get mired in technical problems, unable to deliver a finished system.

The mastery of distributed databases is achieved by doing, not by reading. To understand the issues fully and first hand, do the following exercise. This exercise requires:

•  A small, well-tested Oracle system that resides on a midrange computer or mainframe

•  A PC with Oracle’s SQL*Net connectivity software

•  Another database on the same platform as the source system

Begin with a small existing system which is not mission critical and remove part of the centralized data onto another platform. Assume that we are dealing with a customer-order system running on an Oracle database on a Unix platform. This system is very old and needs replacement, but all components have been fully tested and operational for several years. See from the sample E/R model in Figure 1.5 that the database has five tables:

Figure 1.5  A sample customer-order database.

1.  A CUSTOMER table to store information about the customer

2.  An ORDER table with order information

3.  An ITEM table for product information

4.  A COMPONENT table

5.  An ORDERLINE table to store the quantity for each item that participates in an order

A huge difference exists in the amount of effort that will be required to migrate the data depending on the architecture of the target system (see Figure 1.6). With a relational target system, extracting the data and reformatting the data for import into other relational tables is relatively simple. Other architectures, such as object or network databases, are far more complicated and require sophisticated load programs.

Figure 1.6  The relative difficulty of moving to new database architectures.

For this example, we simply take the existing CUSTOMER table and export the table from Oracle. Next, we add the table to the new database on another platform using Oracle’s Import utility. In sum, we have extracted data from a centralized source and created a distributed relational environment. While this exercise may seem trivial and artificial, the industry is moving toward architectures where the data will reside on a multitude of hardware platforms.

Using Referential Integrity

Oracle databases allow for the control of business rules with “constraints.” These Referential Integrity (RI) rules ensure that one-to-many and many-to-many relationships are enforced within the relational schema.

Several types of “constraints” can be applied to Oracle tables to enforce referential integrity, which include:

•  CHECK CONSTRAINT—This constraint validates incoming columns at row insert time. For example, rather than having an application verify that all occurrences of REGION are North, South, East, or West, a check constraint can be added to the table definition to ensure the validity of the region column.

•  NOT NULL CONSTRAINT—This constraint is used to specify that a column may never contain a NULL value. This is enforced at SQL INSERT and UPDATE time.

•  PRIMARY KEY CONSTRAINT—This constraint is used to identify the primary key for a table. This operation requires that the primary column(s) are unique, and Oracle will create a unique index on the target primary key.

•  REFERENCES CONSTRAINT—This is the foreign-key constraint as implemented by Oracle. A references constraint is only applied at SQL INSERT and DELETE times. For example, assume a one-to-many relationship between the CUSTOMER and ORDER tables such that each CUSTOMER may place many ORDERs, yet each ORDER belongs to only one CUSTOMER. The references constraint tells Oracle at INSERT time that the value in ORDER.cust_num must match the CUSTOMER.cust_num in the customer row, thereby ensuring that a valid customer exists before the order row is added. At SQL DELETE time, the references constraint can be used to ensure that a CUSTOMER is not deleted if rows still exist in the ORDER table.

•  UNIQUE CONSTRAINT—This constraint is used to ensure that all column values within a table never contain a duplicate entry.

Note the distinction between UNIQUE and PRIMARY KEY. While both of these constraints create a unique index, a table may only contain one PRIMARY KEY constraint column—but it may have many UNIQUE constraints on other columns.

---

NOTE:  Referential integrity usually needs to be double coded, once for the database and again within the application. For example, in a multi-part SQL*Form, you may not become aware of an RI violation until you are many pages into the form and your form attempts to commit.

---

As previously explained, referential integrity maintains business rules. Relational systems allow control of business rules with constraints, and RI rules form the backbone of relational tables. For example, RI ensures that a row in the CUSTOMER table is not deleted if the ORDER table contains orders for that customer (Figure 1.7).

Figure 1.7  An example of referential integrity.