Database Design and Use

This article provides an overview of database design and use. It covers topics such as the ER diagram, database implementation, table data, different types of reports, and the relational schema of the database.

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:

12
DATABASE DESIGN AND USE
The data dictionary reports are used for the determination of the external objects in
the system. Some examples of the Data Dictionary reports are Retrieval, Report, Summary,
or Maintenance Details.
Data Modeller Reports:
The data modeller reports can be generated very easily which can be used for fetching
the business need and technical needs of the organization. The logical data model and the
physical data models are the examples of data modelling reports.
User Defined:
The user defined reports are the customizable reports that are used for fetching the
data that is used for the defining the reports that are according to need of the users. Any
business process data element can be used as an example of the user defined report.
Relational Schema of the databasex
The relational schema of the database in provided below:

13
DATABASE DESIGN AND USE
Customer (cid (pk), cname)
Branch (bcode (pk), bname)
Accounts (ano (pk), atype, balance, cid(fk), bcode (fk))
Transactions (tid (pk), ano (fk), ttype, tdate)
What is a primary key? What is the primary key for each table in the
‘OrientalBank’ database?
Primary key is a minimal set of attributes that helps to identify a tuple uniquely. This are used
for identifying a record. The main function of primary key is to implement a relationship
within a table of relational database. Primary key does not allow duplicate value or null value
within a tuple. Primary key constraints are used for identifying each rows and columns
separately.
The primary keys identified in the ER diagram of the Oriental bank database are:
Table Primary Key
Customer cid
Branch Bcode
Accounts ano
Transaction tid
SQL Queries
Query 1
CREATE TABLE "SYS"."BRANCH"
( "BCODE" NUMBER NOT NULL ENABLE,

14
DATABASE DESIGN AND USE
"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" ;
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

15
DATABASE DESIGN AND USE
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" ;
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

16
DATABASE DESIGN AND USE
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,
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" ;
CREATE TABLE "SYS"."TRANSACTION"
( "TID" NUMBER NOT NULL ENABLE,
"ANO" NUMBER NOT NULL ENABLE,
"TDATE" DATE,
"TTYPE" VARCHAR2(20 BYTE),

17
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" ;
Query 2
SELECT COUNT(Ano) FROM accounts WHERE balance > 500;

18
DATABASE DESIGN AND USE
Query 3
SELECT customer.cname, accounts.balance FROM accounts
INNER JOIN customer ON customer.cid = accounts.cid
WHERE accounts.balance > (SELECT AVG(accounts.balance) FROM accounts);

19
DATABASE DESIGN AND USE
Query 4
SELECT accounts.ano, customer.cname, transaction.*
FROM transaction
INNER JOIN accounts ON transaction.ano = accounts.ano
INNER JOIN customer ON customer.cid = accounts.cid
WHERE accounts.ano = 2;

20
DATABASE DESIGN AND USE
Query 5
SELECT customer.*
FROM customer
INNER JOIN accounts ON accounts.cid = customer.cid
INNER JOIN branch ON branch.bcode = accounts.bcode
WHERE branch.bcode = 102;

21
DATABASE DESIGN AND USE
Query 6
SELECT * From customer;

22
DATABASE DESIGN AND USE
Query 7
SELECT * From accounts;

23
DATABASE DESIGN AND USE
Query 8
SELECT customer.*
FROM customer
INNER JOIN accounts ON accounts.cid = customer.cid
INNER JOIN branch ON branch.bcode = accounts.bcode
WHERE branch.bcode = 102 AND accounts.balance < 1000;

24
DATABASE DESIGN AND USE
Query 9
SELECT customer.*
FROM customer
INNER JOIN accounts ON accounts.cid = customer.cid
INNER JOIN branch ON branch.bcode = accounts.bcode
WHERE branch.bcode = 102 AND accounts.balance < 1000;

25
DATABASE DESIGN AND USE
Query 10
SELECT customer.*
FROM customer
INNER JOIN accounts ON accounts.cid = customer.cid
INNER JOIN branch ON branch.bcode = accounts.bcode
WHERE branch.bname = 'Melbourne City';

26
DATABASE DESIGN AND USE
Query 11
SELECT SUM(balance)
from accounts;

27
DATABASE DESIGN AND USE
Normalization
Provided data
1st Normal Form
Health History (PetID (pk), PetName, PetType, PetAge, Owner)
Visiting (PetID (fk), VisitDate, procedure)
2nd Normal Form
Pet (PetID (pk), PetName, PetType, PetAge, Owner)
Visiting (PetID (fk), VisitDate, procedure(fk))
Procedure (procedure (pk))
3rd Normal Form
Pet (PetID (pk), PetName, PetType, PetAge, OwnerID (fk))
Owner (OwnerID (pk),OwnerName)
Visiting (VisitID (pk), PetID (fk), VisitDate, procedureID(fk))
Procedure (procedureID (pk), procedureName)

28
DATABASE DESIGN AND USE
Bibliography
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Eastman, C.M., 2018. Building product models: computer environments, supporting design
and construction. CRC press.
Mitrovic, A. and Suraweera, P., 2016. Teaching database design with constraint-based tutors.
International Journal of Artificial Intelligence in Education, 26(1), pp.448-456.
Mottla, G.L., Richter, K.S., Kaplan, B., Hayward, B. and Mahony, M.C., 2016. Evaluating
change in gonadotropin-releasing hormone analog (GnRH) use in a US real-world database
study of 96,446 in vitro fertilization cycles over 6.5 years. Fertility and Sterility, 106(3),
pp.e343-e344.