Oracle8i Java Stored Procedures Developer's Guide Release 2 (8.1.6) Part Number A81358-01 |
Library | Service | Contents | Index

## Creating the Database Tables

Next, you create the database tables required by the schema plan. You begin by defining the table `Customers`, as follows:

    CREATE TABLE Customers (
      CustNo   NUMBER(3) NOT NULL,
      CustName VARCHAR2(30) NOT NULL,
      Street   VARCHAR2(20) NOT NULL,
      City     VARCHAR2(20) NOT NULL,
      State    CHAR(2) NOT NULL,
      Zip      VARCHAR2(10) NOT NULL,
      Phone    VARCHAR2(12),
      PRIMARY KEY (CustNo)
    );

The table `Customers` stores all the information about customers. Essential information is defined as `NOT` `NULL`. For example, every customer must have a shipping address. However, the table `Customers` does not manage the relationship between a customer and his or her purchase order. So, that relationship must be managed by the table `Orders`, which you define as:

    CREATE TABLE Orders (
      PONo      NUMBER(5),
      Custno    NUMBER(3) REFERENCES Customers,
      OrderDate DATE,
      ShipDate  DATE,
      ToStreet  VARCHAR2(20),
      ToCity    VARCHAR2(20),
      ToState   CHAR(2),
      ToZip     VARCHAR2(10),
      PRIMARY KEY (PONo)
    );

The E-R diagram in Figure 5-2 showed that line items have a relationship with purchase orders and stock items. The table `LineItems` manages these relationships using foreign keys. For example, the foreign key (FK) column `StockNo` in the table `LineItems` references the primary key (PK) column `StockNo` in the table `StockItems`, which you define as:

    CREATE TABLE StockItems (
      StockNo     NUMBER(4) PRIMARY KEY,
      Description VARCHAR2(20),
      Price       NUMBER(6,2))
    );

The table `Orders` manages the relationship between a customer and purchase order using the FK column `CustNo`, which references the PK column `CustNo` in the table `Customers`. However, the table `Orders` does not manage the relationship between a purchase order and its line items. So, that relationship must be managed by the table `LineItems`, which you define as:

    CREATE TABLE LineItems (
      LineNo    NUMBER(2),
      PONo      NUMBER(5) REFERENCES Orders,
      StockNo   NUMBER(4) REFERENCES StockItems,
      Quantity  NUMBER(2),
      Discount  NUMBER(4,2),
      PRIMARY KEY (LineNo, PONo)
    );

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index