Database Design and Use

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),