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)
);

---