SQL Statements › CREATE TABLE › Data Types › Example 5

Example 5

Create a table to keep track of customers who pay their bills on time. Then create another table to keep track of current orders. Design the tables to enforce the following business rules:

1. Never accept an order from a customer who does not pay his bills.
2. Do not finance orders of less than $1,000.00 worth of merchandise.
3. Always get a down payment of at least 25 percent on financed orders.
4. Never negotiate interest rates to below 11 percent.
5. Ensure maximum performance when retrieving customer orders based on the assigned "order ID."

Note: The following example is intended to show the specification of as many different data types and options as possible. For this reason, proper data base design and table normalization have been ignored. Proper design and normalization of your databases is imperative for maximum system performance.

EXEC SQL
      CREATE TABLE ACCOUNTING.PAYING_CUSTOMERS
       (TOTAL_NUM_ORDERS    INTEGER NOT NULL,
        FIRST_ORDER         DATE,
        LAST_ORDER          DATE,
        COMPANY_NAME        CHAR(40) NOT NULL,
        TOTAL_GROSS_ORDERS  DECIMAL(12,2) NOT NULL,
        AVG_GROSS_PER_ORDER DECIMAL(9,2) NOT NULL,
        PRIMARY KEY (COMPANY_NAME));
END-EXEC

(Example 5 continued on next page)

EXEC SQL
      CREATE TABLE ORDERS
       (ORDER_ID_NUMBER     INTEGER NOT NULL PRIMARY KEY,
        CUSTOMER_NAME       CHAR(40) DEFAULT 'INTERNAL ORDER',
        SHIPMENT_ID_NUMBER  DECIMAL(6,0) NOT NULL UNIQUE,
        GROSS_AMOUNT        NUMERIC(8,2) NOT NULL,
        PERCENT_DOWN_PMT    REAL DEFAULT 100.0
                            CHECK (PERCENT_DOWN_PMT >= 25.0),
        NUM_PAYMENTS        SMALLINT DEFAULT NULL,
        INTEREST_RATE       DOUBLE PRECISION CHECK (INTEREST_RATE >= 11.0)
                            CONSTRAINT ORDERS_MIN_INTEREST_RATE,
        DATE_PROMISED       DATE,
        TIME_PROMISED       TIME,
        DATE_AND_TIME_SHIPPED TIMESTAMP NOT NULL WITH DEFAULT,
        AVG_ITEM_QUANTITY   FLOAT,
        AVG_NUM_ITEMS       FLOAT(6),
        TOTAL_PIECES        INTEGER NOT NULL WITH DEFAULT,
        FOREIGN KEY (CUSTOMER_NAME) REFERENCES
                    ACCOUNTING.PAYING_CUSTOMERS(COMPANY_NAME),
        CHECK (GROSS_AMOUNT > 1000.0 OR PERCENT_DOWN_PMT = 100.0)
              CONSTRAINT ORDERS_MIN_AMT_FINANCED);
END-EXEC

How the Tables Enforce the Business Rules:

The following numbers correspond to the numbers of the business rules specified at the beginning of this example.

1. The foreign key on ORDERS.CUSTOMER-NAME prevents orders from being inserted into the table ORDERS unless the customer's name appears in the PAYING-CUSTOMERS table in column COMPANY-NAME. Any attempt to insert an order for a nonpaying customer is rejected.
2. Constraint ORDERS-MIN-AMT-FINANCED prevents an order from being inserted if an attempt is being made to finance an order of less than $1000.00.
3. CHECK (PERCENT-DOWN-PMT >= 25.0) stops orders that are being financed with a down payment of less than 25 percent. Since the user did not specify a constraint name, the system generates a unique name. It is recommended that you give meaningful names to all constraints so that error messages are more meaningful when a constraint name is in the message.
4. Constraint ORDERS-MIN-INTEREST-RATE stops orders with an interest rate below 11 percent.
5. Making ORDER-ID-NUMBER the primary key causes the system to generate an index based on this column for quick access to the data records.