HS2021 Database Design and Use: Oriental Bank Database Design

This report provides a comprehensive database design for Oriental Bank, including an Entity Relationship (ER) diagram illustrating the data structures for banking details, customer accounts, and transactions. The report outlines the database implementation with SQL CREATE TABLE statements for Branch, Customer, Accounts, and Transaction tables, complete with primary and foreign key constraints. It includes sample data insertion into the tables and discusses different report types that can be generated from the database. The relational schema of the database is presented, and the concept of a primary key is explained. The report also features several SQL queries for data retrieval and manipulation, demonstrating practical database usage. Desklib offers a wealth of similar solved assignments and past papers for students seeking assistance with their studies.

Running Head: DATABASE DESIGN AND USE
Database Design and Use
Name of the Student:
Name of the University:
Author Note

1
DATABASE DESIGN AND USE
Database relationship
ER Diagram description
The ER diagram or the Entity relationship diagram described above is used for the
representation of the data structures that are to be followed in the oriental bank database. The
database would be used as a banking application and which would be used for the description
of the banking details, the account details, the details of the customer and the transactions that
takes place in the bank. The Branch field contains the details of the branch code and the
branch names. The customer field contains the details of the customer ids and the names of
the customers. In addition to this each of the customer have an account in the bank which
belongs to certain branch. The transactions that takes place in system makes use of the
account details of the customers. In addition to this, the tables in the ER diagram have been
allocated with the required primary keys. However, there are absence of the foreign keys that
would be used for the references in the tables in the databases. Hence new foreign keys are to
be allocated in the system. Hence the accounts table is to allocated with the cid field and the
bcode which is used as references from the table customer and branch respectively. In
addition to this, the transaction table would be used would be allocated with the ano which
would be used as the reference to the table accounts where ano is the primary key.

2
DATABASE DESIGN AND USE
Database Implementation
Branch Table
CREATE TABLE "SYS"."BRANCH"
( "BCODE" NUMBER NOT NULL ENABLE,
"BNAME" VARCHAR2(20 BYTE),
CONSTRAINT "BRANCH_PK" PRIMARY KEY ("BCODE")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS
LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;

3
DATABASE DESIGN AND USE
Customer Table
CREATE TABLE "SYS"."CUSTOMER"
( "CID" NUMBER NOT NULL ENABLE,
"CNAME" VARCHAR2(20 BYTE),
CONSTRAINT "CUSTOMER_PK" PRIMARY KEY ("CID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS
LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

4
DATABASE DESIGN AND USE
TABLESPACE "SYSTEM" ;
Accounts table
CREATE TABLE "SYS"."ACCOUNTS"
( "ANO" NUMBER NOT NULL ENABLE,
"ATYPE" VARCHAR2(20 BYTE),
"BALANCE" NUMBER,
"BCODE" NUMBER NOT NULL ENABLE,
"CID" NUMBER NOT NULL ENABLE,
CONSTRAINT "ACCOUNTS_PK" PRIMARY KEY ("ANO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE,
CONSTRAINT "FK1" FOREIGN KEY ("BCODE")
REFERENCES "SYS"."BRANCH" ("BCODE") ENABLE,

5
DATABASE DESIGN AND USE
CONSTRAINT "FK2" FOREIGN KEY ("CID")
REFERENCES "SYS"."CUSTOMER" ("CID") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS
LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
Transactions table
CREATE TABLE "SYS"."TRANSACTION"
( "TID" NUMBER NOT NULL ENABLE,
"ANO" NUMBER NOT NULL ENABLE,
"TDATE" DATE,
"TTYPE" VARCHAR2(20 BYTE),

6
DATABASE DESIGN AND USE
CONSTRAINT "TRANSACTION_PK" PRIMARY KEY ("TID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE,
CONSTRAINT "FK3" FOREIGN KEY ("ANO")
REFERENCES "SYS"."ACCOUNTS" ("ANO") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS
LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;

7
DATABASE DESIGN AND USE
Table Data
Customer Table
INSERT INTO "SYS"."CUSTOMER" (CID, CNAME) VALUES ('11', 'John')
INSERT INTO "SYS"."CUSTOMER" (CID, CNAME) VALUES ('12', 'Steve')
INSERT INTO "SYS"."CUSTOMER" (CID, CNAME) VALUES ('13', 'Kevin')
INSERT INTO "SYS"."CUSTOMER" (CID, CNAME) VALUES ('14', 'Philip')
INSERT INTO "SYS"."CUSTOMER" (CID, CNAME) VALUES ('15', 'Willy')
INSERT INTO "SYS"."CUSTOMER" (CID, CNAME) VALUES ('16', 'James')
INSERT INTO "SYS"."CUSTOMER" (CID, CNAME) VALUES ('17', 'Anne')
INSERT INTO "SYS"."CUSTOMER" (CID, CNAME) VALUES ('18', 'Sammy')
INSERT INTO "SYS"."CUSTOMER" (CID, CNAME) VALUES ('19', 'David')
INSERT INTO "SYS"."CUSTOMER" (CID, CNAME) VALUES ('20', 'Kenny')
INSERT INTO "SYS"."CUSTOMER" (CID, CNAME) VALUES ('21', 'Henry')

8
DATABASE DESIGN AND USE
Branch Table
INSERT INTO "SYS"."BRANCH" (BCODE, BNAME) VALUES ('102', 'Perth')
INSERT INTO "SYS"."BRANCH" (BCODE, BNAME) VALUES ('103', 'Hobart')
INSERT INTO "SYS"."BRANCH" (BCODE, BNAME) VALUES ('104', 'Melbourne City')
INSERT INTO "SYS"."BRANCH" (BCODE, BNAME) VALUES ('107', 'Canberra City')
INSERT INTO "SYS"."BRANCH" (BCODE, BNAME) VALUES ('105', 'Perth City')
INSERT INTO "SYS"."BRANCH" (BCODE, BNAME) VALUES ('106', 'Victoria')
INSERT INTO "SYS"."BRANCH" (BCODE, BNAME) VALUES ('108', 'NSW')
INSERT INTO "SYS"."BRANCH" (BCODE, BNAME) VALUES ('109', 'Sydney South')
INSERT INTO "SYS"."BRANCH" (BCODE, BNAME) VALUES ('110', 'South East')
Accounts Table

9
DATABASE DESIGN AND USE
INSERT INTO "SYS"."ACCOUNTS" (ANO, ATYPE, BALANCE, BCODE, CID)
VALUES ('1', 'Savings', '800', '102', '11')
INSERT INTO "SYS"."ACCOUNTS" (ANO, ATYPE, BALANCE, BCODE, CID)
VALUES ('2', 'Savings', '700', '102', '12')
INSERT INTO "SYS"."ACCOUNTS" (ANO, ATYPE, BALANCE, BCODE, CID)
VALUES ('3', 'Current', '500', '103', '13')
INSERT INTO "SYS"."ACCOUNTS" (ANO, ATYPE, BALANCE, BCODE, CID)
VALUES ('4', 'Savings', '400', '102', '14')
INSERT INTO "SYS"."ACCOUNTS" (ANO, ATYPE, BALANCE, BCODE, CID)
VALUES ('5', 'Current', '1100', '102', '15')
INSERT INTO "SYS"."ACCOUNTS" (ANO, ATYPE, BALANCE, BCODE, CID)
VALUES ('6', 'Savings', '800', '104', '16')
INSERT INTO "SYS"."ACCOUNTS" (ANO, ATYPE, BALANCE, BCODE, CID)
VALUES ('7', 'Current', '300', '104', '17')
INSERT INTO "SYS"."ACCOUNTS" (ANO, ATYPE, BALANCE, BCODE, CID)
VALUES ('8', 'Savings', '400', '104', '18')
INSERT INTO "SYS"."ACCOUNTS" (ANO, ATYPE, BALANCE, BCODE, CID)
VALUES ('9', 'Current', '700', '104', '19')
INSERT INTO "SYS"."ACCOUNTS" (ANO, ATYPE, BALANCE, BCODE, CID)
VALUES ('10', 'Savings', '400', '104', '20')
INSERT INTO "SYS"."ACCOUNTS" (ANO, ATYPE, BALANCE, BCODE, CID)
VALUES ('11', 'Savings', '700', '102', '21')

10
DATABASE DESIGN AND USE
Transaction Table
INSERT INTO "SYS"."TRANSACTION" (TID, ANO, TDATE, TTYPE) VALUES ('1', '1',
TO_DATE('21-10-18', 'DD-MM-RR'), 'Debit')
INSERT INTO "SYS"."TRANSACTION" (TID, ANO, TDATE, TTYPE) VALUES ('2', '2',
TO_DATE('09-11-17', 'DD-MM-RR'), 'Credit')
INSERT INTO "SYS"."TRANSACTION" (TID, ANO, TDATE, TTYPE) VALUES ('3', '3',
TO_DATE('17-11-18', 'DD-MM-RR'), 'Debit')
INSERT INTO "SYS"."TRANSACTION" (TID, ANO, TDATE, TTYPE) VALUES ('4', '3',
TO_DATE('19-09-18', 'DD-MM-RR'), 'Credit')
INSERT INTO "SYS"."TRANSACTION" (TID, ANO, TDATE, TTYPE) VALUES ('5', '3',
TO_DATE('21-10-16', 'DD-MM-RR'), 'Credit')
INSERT INTO "SYS"."TRANSACTION" (TID, ANO, TDATE, TTYPE) VALUES ('6', '5',
TO_DATE('20-09-16', 'DD-MM-RR'), 'Debit')

11
DATABASE DESIGN AND USE
INSERT INTO "SYS"."TRANSACTION" (TID, ANO, TDATE, TTYPE) VALUES ('7', '3',
TO_DATE('10-09-18', 'DD-MM-RR'), 'Credit')
INSERT INTO "SYS"."TRANSACTION" (TID, ANO, TDATE, TTYPE) VALUES ('8', '4',
TO_DATE('11-12-17', 'DD-MM-RR'), 'Debit')
INSERT INTO "SYS"."TRANSACTION" (TID, ANO, TDATE, TTYPE) VALUES ('9', '5',
TO_DATE('12-09-18', 'DD-MM-RR'), 'Credit')
INSERT INTO "SYS"."TRANSACTION" (TID, ANO, TDATE, TTYPE) VALUES ('10', '6',
TO_DATE('11-09-17', 'DD-MM-RR'), 'Debit')
Report examples
There are three type of reports that can be fetched from the database. These are:
Data Dictionary Reports: