Oriental Bank Database: ER Diagram, Tables, and SQL Queries

This assignment details the design and implementation of a database for a banking system, named Oriental Bank. It begins with an Entity-Relationship (ER) diagram illustrating the relationships between customers, branches, transactions, and accounts. The database was created using MS Access, with screenshots showcasing the four primary tables (Customer, Branch, Account, and Transaction) and their respective fields. The assignment includes screenshots of the tables populated with sample data. Furthermore, the assignment describes the creation of three additional tables to facilitate data joining. The document presents several report cards that can be generated from the database, such as customer lists based on account type and branch, and lists of transactions on a particular date. The assignment also defines the primary keys for each table. Task 2 focuses on SQL queries, demonstrating how to retrieve data from the database. Several queries are provided, including those to retrieve account balances, customer names based on account balances, and transactions based on specific criteria. The document includes the SQL view and results for each query. Finally, Task 3 covers database normalization, presenting the data in 1NF, 2NF, and 3NF forms, with an example of a health history report for pets. The assignment concludes with a bibliography referencing relevant database resources.

qwertyuiopasdfghjklzxcvbnmqwe
rtyuiopasdfghjklzxcvbnmqwertyu
iopasdfghjklzxcvbnmqwertyuiopa
sdfghjklzxcvbnmqwertyuiopasdfg
hjklzxcvbnmqwertyuiopasdfghjkl
zxcvbnmqwertyuiopasdfghjklzxcv
bnmqwertyuiopasdfghjklzxcvbnm
qwertyuiopasdfghjklzxcvbnmqwe
rtyuiopasdfghjklzxcvbnmqwertyu
iopasdfghjklzxcvbnmqwertyuiopa
sdfghjklzxcvbnmqwertyuiopasdfg
hjklzxcvbnmqwertyuiopasdfghjkl
zxcvbnmqwertyuiopasdfghjklzxcv
Data and Information Management
Managing Data in Databases

Task 1
A)
In the given Entity-Relationship diagram, there are four main entities Customer, Branch, Transaction and
Account. The purpose depicted from this ER diagram is: It shows the operations of a banking database
application where various customers has their accounts. The customer has been given an id which is the
unique identification of the customer. The customer can have their account in various branches of the
same bank. Every branch has its own unique code and a name. A distinct account number is provided to
every customer that holds their private transactions such as Account Type and Balance. All the
transactions made by the bank customers are recorded in the bank database and every single
transaction made by any of the customer possesses a unique identification number. The date and type
of transaction is recorded and if it is monetary transaction then amount used is also recorded in the
database. In brief it depicts that, a customer has an account in some branch of the given bank and
performs transaction on his account which all gets recorded in database.
B.
The database has been created using MS ACCESS and the database has been named as OrientalBank. All
the four tables were created with the desired fields and same has been shown thorugh screenshots
using snippin tool:
Figure 1: Customer Data Table

Figure 2: Branch Data Table
Figure 3: Account Data Table
Figure 4: Transaction Data Table

C.
Following are the screenshots of the 4 tables after entering 10 data records in each table:
Figure 5: Customer Table
Figure 6: Branch Table

Figure 7: Account Table
Figure 8: Transaction Table
Other than these four tables it was necessary to create three other tables that will allow joining the data
and those are as follows:

Figure 9: Customer Transaction Table
Figure 10: Customer Branch Details

Figure 11: Customer Account Details
D.
The report cards that can be generated using Oriental Bank Database are:
 List the name of customers those who have only savings account in branch Sydney
 List all the branches of Oriental Bank
 List all the customers of Oriental Bank who have an account in Adelaide branch
 List all the transactions occurred on a particular date.
E.
Customer (CID, CNAME)
Branch (BCODE, BNAME)
Account (ANO, ATYPE, BALANCE)
Transaction (TID, TTYPE, TDATE, TAMOUNT)
F.
A primary key column (either a single column or combination of 2 or more columns) is a distinctive
column in a relational database which is designated to contain uniquely identifiable records. The main
feature of a primary key is to consist of a unique value for every row of data present. And there cannot
be any null values for the primary key column.

The primary keys for OrientalBank database are:
 cid for Customer Table
 bcode for Branch Table
 ano for Account Table
 tid for Transaction Table
Task 2
A.
Please refer Task 1 (B) and (C) for the answer of this question.
B.
Figure 12: Query 1 and Results

SQL View for the query:
SELECT Account.BALANCE
FROM Account
WHERE (((Account.BALANCE)>500));
C.
Figure 13: Query 2 and Results

SQL View for the query:
SELECT Customer.CNAME
FROM Customer, CustomerBranchAccountDetails AS D, Account AS A
WHERE (Customer.CID = D.CID and D.ANO=A.ANO and A.balance>(SELECT AVG(BALANCE) FROM
Account));
D.
SQL View for the query:
SELECT C.CID, C.CNAME, T.TID, T.TDATE, T.TTYPE
FROM Customer C, Transaction T, CustomerTransactionRecord R WHERE (C.CID=R.CID and R.TID=T.TID
and C.CID=(SELECT CID FROM CustomerBranchAccountDetails D, Account A where D.ANO=A.ANO
and A.ANO=2002) );
Result:
E.
SQL View for the query:
SELECT C.CID,C.CNAME
FROM Customer C,CustomerBranchDetails D
WHERE C.CID=D.CID and D.BCODE= (SELECT BCODE FROM BRANCH WHERE BCODE=102);
Result:

F.
SELECT *
FROM Customer;
Result:
G.
SELECT *
FROM Account;

H.
Select A.ANO, C.CNAME
From Account A, Customer C, CustomerBranchAccountDetails D
WHERE C.CID=D.CID and D.ANO=A.ANO and A.BALANCE<1000 and D.BCODE=(SELECT BCODE FROM
BRANCH WHERE BCODE=102);
Result:
I.
Select C.CID, C.CNAME
From Account A, Customer C, CustomerBranchAccountDetails D
WHERE C.CID=D.CID and D.ANO=A.ANO and
A.BALANCE<1000 or (D.BCODE=(SELECT BCODE FROM BRANCH WHERE BCODE=102));
J.
Select C.CID, C.CNAME