Oracle8i
Java Stored Procedures Developer's Guide Release 2 (8.1.6) Part Number A81358-01 |
|
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. |
|