DDL

DDL

DROP  TABLE  BorroWBond;
DROP  TABLE  Detail;
DROP  TABLE  DetailType;
DROP  TABLE  PromiseBorrow;
DROP  TABLE  ACCOUNT;
DROP  TABLE  Employee;
DROP  TABLE  BANK;
DROP  TABLE  Customer;
DROP  TABLE  AccountType;
DROP  TABLE  Promotion;
DROP  TABLE  BondsMan;
--DROP  TABLE  PromiseBorrow;
--DROP  TABLE  BANK;
--DROP  TABLE  ACCOUNT;


--ลบตารางในถังขยะ
purge recyclebin;


--สร้างตาราง bank
CREATE  TABLE  BANK
(BankId NUMBER(3)
CONSTRAINT Nn1_Bank   NOT NULL,
BankBranch VARCHAR2(40),
BankTel VARCHAR2(10),
CONSTRAINT Pk_Bank PRIMARY KEY(BankId));


--สร้างตารางพนักงาน
CREATE  TABLE  Employee
(EmpId NUMBER(5)

CONSTRAINT Nn1_Emp NOT NULL,

EmpTitle VARCHAR2(10),
EmpFName VARCHAR2(20),
EmpLName VARCHAR2(20),
EmpAge NUMBER(3),
EmpTel VARCHAR2(10),
EmpAddr VARCHAR2(40),
EmpBankId NUMBER(3),
CONSTRAINT Pk_Emp PRIMARY KEY(EmpId),
CONSTRAINT Fk_EmpBank FOREIGN KEY(EmpBankId)
REFERENCES Bank(BankId));


--สร้างตารางลูกค้า
CREATE  TABLE  Customer
(CusId CHAR(13)
CONSTRAINT Nn1_Cus NOT NULL,
CusTitle VARCHAR2(10),
CusFName VARCHAR2(20),
CusLName VARCHAR2(20),
CusAge NUMBER(3),
CusTel VARCHAR2(10),
CusAddr VARCHAR2(40),
CONSTRAINT Pk_Cus PRIMARY KEY(CusId));


--สร้างตารางโปรโมชัน
CREATE  TABLE  Promotion

(ProId NUMBER(3)

CONSTRAINT Nn1_pro NOT NULL,
ProName VARCHAR2(30),
ProDetail VARCHAR2(50),
ProUsedFlg CHAR(1),
 CONSTRAINT Pk_Pro PRIMARY KEY(ProId));


--สร้างตารางประเภทบัญชี
CREATE  TABLE  AccountType

(AccTypeId CHAR(1)

CONSTRAINT Nn1_accT NOT NULL,
AccTypeName VARCHAR2(10),
AccTypeProId NUMBER(3),
    CONSTRAINT Pk_AccT PRIMARY KEY(AccTypeId),
    CONSTRAINT Fk_AccTPro FOREIGN KEY(AccTypeProId)
REFERENCES Promotion(ProId));


--สร้างตารางบัญชี
CREATE  TABLE  Account
(AccId NUMBER(10)
CONSTRAINT Nn1_acc NOT NULL,
AccName VARCHAR2(30),
AccAccTId CHAR(1),
AccCusId CHAR(13),
AccBankId NUMBER(3),
AccUsedFlg CHAR(1),
      CONSTRAINT Pk_Acc PRIMARY KEY(AccId),
      CONSTRAINT Fk_AccCus FOREIGN KEY(AccCusId)
REFERENCES Customer(CusId),
      CONSTRAINT Fk_AccBank FOREIGN KEY(AccBankId)
REFERENCES Bank(BankId),
      CONSTRAINT Fk_AccAccT FOREIGN KEY(AccAccTId)
REFERENCES AccountType(AccTypeId));


--สร้างตารางผู้ค้ำประกัน
CREATE  TABLE  BondsMan
(BondId CHAR(13)
CONSTRAINT Nn1_Bond NOT NULL,
BondTitle VARCHAR2(10),
BondFName VARCHAR2(20),
BondLName VARCHAR2(20),
BondAge NUMBER(3),
BondTel VARCHAR2(10),
BondAddr VARCHAR2(40),
CONSTRAINT Pk_Bond PRIMARY KEY(BondId));


--สร้างตารางสัญญาเงินกู้
CREATE  TABLE  PromiseBorrow
(BorrowId NUMBER(8)
CONSTRAINT Nn1_Bor NOT NULL,
BorrowTitle VARCHAR2(10),
BorrowFName VARCHAR2(20),
BorrowLName VARCHAR2(20),
BorrowAccId NUMBER(10),
BorrowDetail VARCHAR2(35),
BorrowDate DATE,
BorrowTotalMoney NUMBER(8,2),
Payment NUMBER(8,2),
TotalDueMoney NUMBER(8,2),
      CONSTRAINT Pk_Bor PRIMARY KEY(BorrowId),
      CONSTRAINT Fk_BorAcc FOREIGN KEY(BorrowAccId)
REFERENCES Account(AccId));


--สร้างตารางประเภทธุรกรรม
CREATE  TABLE  DetailType
(DeTId CHAR(1)
CONSTRAINT Nn1_DeT NOT NULL,
DeTName VARCHAR2(5),
      CONSTRAINT Pk_DeT PRIMARY KEY(DeTId));


--สร้างตารางรายละเอียดบัญชี
CREATE TABLE  Detail
(DeSeg NUMBER(7)
CONSTRAINT Nn1_De NOT NULL,
DeAccId NUMBER(10),
DeDate DATE,
DeDeTId CHAR(1),
DeMoney NUMBER(8,2),
TotalMoney NUMBER(8,2),
      CONSTRAINT Pk_De PRIMARY   KEY(DeSeg,DeAccId),
      CONSTRAINT Fk_DeAcc FOREIGN KEY(DeAccId)
REFERENCES Account(AccId),
      CONSTRAINT Fk_DeDeT FOREIGN KEY(DeDeTId)
REFERENCES DetailType(DeTId));


--สร้างตารางสัญญา+ผู้ค้ำ
CREATE  TABLE  BorroWBond
(BBBorId NUMBER(8)
CONSTRAINT Nn1_BBB  NOT NULL,
BBMId CHAR(13)
CONSTRAINT Nn1_BBM  NOT NULL,
BBMFName VARCHAR2(20),
      CONSTRAINT Fk_BBB FOREIGN KEY(BBBorId)
REFERENCES PromiseBorrow(BorrowId),
      CONSTRAINT Fk_BBM FOREIGN KEY(BBMId)
REFERENCES BondsMan(BondId));