Chapter No.2 Make Tables in Oracle Database for Development

We will develop “Inventory Control System” step by step by using Oracle Forms Builder.

1. We will Create necessary table for this system.
2. Supplier Table, Transaction Master Table and Transaction Detail Table.
3. Just run copy these tables script and draw tables in Oracle Database.

–Supplier Table

CREATE TABLE SUPPLIER_INFO
(
SUPP_ID    NUMBER,
SUPP_NAME  VARCHAR2(100),
SUPP_ADD   VARCHAR2(500),
SUPP_PHON  VARCHAR2(20),
SUPP_FAX   VARCHAR2(20),
SUPP_EMAIL VARCHAR2(20),
CONSTRAINT PK_SUPP_ID PRIMARY KEY (SUPP_ID)
);

–Transaction Master Table

CREATE TABLE TRANSACTION_MASTER
(
TRANS_NUM     NUMBER,
TRANS_TYPE    NUMBER,
TRANS_DATE    DATE,
SUPP_ID       NUMBER,
REMARKS       VARCHAR2(500),
CREATED_ID    VARCHAR2(25),
CREATED_DATE  DATE,
UPDATED_ID    VARCHAR2(25),
UPDATED_DATE  DATE,
CONSTRAINT    PK_TRANS_NUM PRIMARY KEY (TRANS_NUM),
CONSTRAINT    FK_SUPP_ID   FOREIGN KEY (SUPP_ID) REFERENCES SUPPLIER_INFO(SUPP_ID)
);

–Transaction Detail Table

CREATE TABLE TRANSACTION_DETAIL
(
TRANS_NUM         NUMBER,
TRANS_DET_NUM     NUMBER,
ITEM_NAME         VARCHAR2(50),
ITEM_QTY          NUMBER(16,4),
ITEM_RATE         NUMBER(16,4),
ITEM_VALUE        NUMBER(16,4),
CONSTRAINT        PK_TRANS_DET_NUM PRIMARY KEY (TRANS_DET_NUM),
CONSTRAINT        FK_TRANS_NUM     FOREIGN KEY (TRANS_DET_NUM) REFERENCES TRANSACTION_MASTER(TRANS_NUM)
);

In next chapter we will design Oracle Forms by using these tables that will use in further system.