Structure of tables related to Bank Application

The following are the tables that are used to store information regarding accounts, chequebooks, and transactions.

Table Name Description

ACCOUNTS Contains information about all accounts in the bank

CHEQUEBOOKS Contains information about cheque books issued to accounts.

AVA_CHEQUEBOOKS Contains information about all available cheque books.

TRANS Stores information about all transactions.

Table Name	Description
ACCOUNTS	Contains information about all accounts in the bank
CHEQUEBOOKS	Contains information about cheque books issued to accounts.
AVA_CHEQUEBOOKS	Contains information about all available cheque books.
TRANS	Stores information about all transactions.

The following is the script to create these four tables.

Use the following procedure to create tables using this script.

Select the script given below and copy into clipboard
Open Notepad on your system and paste the script into Notepad.
Save the script into a file using File->Save As option in Notepad.
Run the script that is saved into a file at SQL prompt using START command
SQL>START c:\bank\banktables.sql

Click on Insert Data to see the script used to insert data after tables are created.


REM SCRIPT TO CREATE TABLES OF BANK APP.

REM ********* DROP ALL TABLES *************

DROP TABLE TRANS  CASCADE CONSTRAINTS;
DROP TABLE  CHEQUEBOOKS CASCADE CONSTRAINT;
DROP TABLE  AVA_CHEQUEBOOKS CASCADE CONSTRAINT;
DROP TABLE  ACCOUNTS CASCADE CONSTRAINT;

REM  ******  CREATE TABLES. MOST INDEPENDENT TABLES FIRST  ****


REM  *********  ACCOUNTS  ****************

CREATE TABLE ACCOUNTS
( ACNO NUMBER(5)
        CONSTRAINT ACCOUNTS_PK PRIMARY KEY,
  NAME  VARCHAR2(20) 
       CONSTRAINT ACCOUNTS_NAME_NN NOT NULL,
  DO    DATE,
  ATYPE CHAR(1) 
        CONSTRAINT ACCOUNTS_ATYPE_CHK
        CHECK ( ATYPE IN ('S','C')),
 CURBAL NUMBER(10,2),
  CF    CHAR(1) DEFAULT  'N'
        CONSTRAINT ACCOUNTS_CF_CHK
        CHECK ( CF IN ('Y','N'))
);

REM  *********  CHEQUEBOOKS  **********

CREATE TABLE CHEQUEBOOKS 
( ACNO  NUMBER(5)  
        CONSTRAINT CHEQUEBOOKS_ACNO_FK
        REFERENCES ACCOUNTS(ACNO),
  CBNO  NUMBER(5)
        CONSTRAINT CHEQUEBOOKS_PK 
        PRIMARY KEY,
  DI    DATE,
  SCNO  NUMBER(5), 
  ECNO  NUMBER(5),
  CONSTRAINT CHEQUEBOOKS_SCNO_ECNO_CHK
      CHECK ( SCNO <= ECNO)
);

REM  *********  AVA_CHEQUEBOOKS  **********

CREATE TABLE AVA_CHEQUEBOOKS 
(CBNO  NUMBER(5)
        CONSTRAINT AVA_CHEQUEBOOKS_PK 
        PRIMARY KEY,
  SCNO  NUMBER(5), 
  ECNO  NUMBER(5),
  CONSTRAINT AVA_CHEQUEBOOKS_SCNO_ECNO_CHK
      CHECK ( SCNO <= ECNO)
);

REM  *********  TRANS  **********

CREATE TABLE TRANS
(TNO   NUMBER(10) 
       CONSTRAINT TRANS_PK PRIMARY KEY, 
 ACNO  NUMBER(5)
       CONSTRAINT TRANS_ACNO_FK
       REFERENCES ACCOUNTS(ACNO),
 DT    DATE,
 TTYPE CHAR(1) CONSTRAINT TRANS_TTYPE_CHK
        CHECK ( TTYPE IN ('D','W')),
 TAMT  NUMBER(10,2),
 CQNO  NUMBER(10),
 CQBANK VARCHAR2(20),
 CQACNO NUMBER(5),
 CQTO   VARCHAR2(20)
);

Inserting Data Into Sample Tables

The following script can be used to insert data into sample tables related to Bank application.


REM ************ SCRIPT TO INSERT DATA INTO BANK TABLES *******

REM ************ DELETE ALL ROWS FROM ALL TABLES  **************

DELETE FROM TRANS;
DELETE FROM CHEQUEBOOKS;
DELETE FROM AVA_CHEQUEBOOKS;
DELETE FROM ACCOUNTS;


REM  ************ INSERT INTO AVA_CHEQUEBOOKS  ************

INSERT INTO AVA_CHEQUEBOOKS  VALUES(1020,50001,50025);
INSERT INTO AVA_CHEQUEBOOKS  VALUES(1021,50026,50050);
INSERT INTO AVA_CHEQUEBOOKS  VALUES(1022,50051,50075);
INSERT INTO AVA_CHEQUEBOOKS  VALUES(1023,50076,50100);
INSERT INTO AVA_CHEQUEBOOKS  VALUES(1024,50101,50125);
INSERT INTO AVA_CHEQUEBOOKS  VALUES(1025,50126,50150);


REM  ***********  INSERT INTO ACCOUNTS **********

INSERT INTO ACCOUNTS VALUES(1,'RONALDO','14-MAR-2002','S',10000,'Y');
INSERT INTO ACCOUNTS VALUES(2,'DENILSON','16-MAR-2002',  'S',5000,'Y');
INSERT INTO ACCOUNTS VALUES(3,'RIVALDO','20-APR-2002','C',20000,'Y');
INSERT INTO ACCOUNTS VALUES(4,'ROBERTO CARLOS','12-MAY-2002', 'S',5000,'N');
INSERT INTO ACCOUNTS VALUES(5,'CAFU','14-MAR-2002', 'S',4000,'N');
INSERT INTO ACCOUNTS VALUES(6,'EDILSON','2-JUN-2002',  'C',15000,'Y');
INSERT INTO ACCOUNTS VALUES(7,'LENARDO','3-JUN-2002',  'S',4500,'N');
INSERT INTO ACCOUNTS VALUES(8,'RONALDINHO','3-JUN-2002',  'S',5000,'N');



REM   ****** CHEQUEBOOKS ********

INSERT INTO CHEQUEBOOKS VALUES(2,1011,'19-MAR-2002', 45026,45050);
INSERT INTO CHEQUEBOOKS VALUES(1,1012,'1-APR-2002',  45051,45075);
INSERT INTO CHEQUEBOOKS VALUES(3,1013,'5-MAY-2002',  45076,45100);
INSERT INTO CHEQUEBOOKS VALUES(6,1014,'2-JUN-2002',  45101,45125);
INSERT INTO CHEQUEBOOKS VALUES(1,1015,'2-JUN-2002',  45126,45150);


REM  ************ TRANS ************

INSERT INTO TRANS  VALUES(1,2,'2-MAY-2002','D',1000,NULL, NULL,NULL,NULL);
INSERT INTO TRANS  VALUES(2,1,'2-MAY-2002','D',5000,7474,'GTB',28282,NULL);
INSERT INTO TRANS  VALUES(3,3,'6-MAY-2002','W',5500,45077, NULL,NULL,'FIGO');
INSERT INTO TRANS  VALUES(4,5,'15-MAY-2002','W',3000,NULL, NULL,NULL,NULL);
INSERT INTO TRANS  VALUES(5,4,'20-MAY-2002','D',5000,NULL, NULL,NULL,NULL);
INSERT INTO TRANS  VALUES(6,1,'22-MAY-2002','W',1000,NULL, NULL,NULL,NULL);
INSERT INTO TRANS  VALUES(7,2,'20-MAY-2002','D',1000,32838,'ICICI',232,NULL);
INSERT INTO TRANS  VALUES(8,6,'22-MAY-2002','D',1500,12333,'SBI',1222,NULL);
INSERT INTO TRANS  VALUES(9,1,'23-MAY-2002','D',10000,NULL,NULL,NULL,NULL);
INSERT INTO TRANS  VALUES(10,7,'25-MAY-2002','D',2000,NULL,NULL,NULL,NULL);
INSERT INTO TRANS  VALUES(11,7,'25-MAY-2002','W',3000,NULL,NULL,NULL,NULL);
INSERT INTO TRANS  VALUES(12,4,'26-MAY-2002','W',3500,NULL,NULL,NULL,NULL);
INSERT INTO TRANS  VALUES(13,3,'26-MAY-2002','W',3000,45090,NULL,NULL,'ZIDANE');
INSERT INTO TRANS  VALUES(14,8,'1-JUN-2002','D',20000,37333,'AB',111,NULL);
INSERT INTO TRANS  VALUES(15,8,'3-JUN-2002','W',7000,NULL,NULL,NULL,NULL);



COMMIT;