Developing And Implementing Data Models Using MS Access: OrientalBank Database Essay.

1. The purpose of this assignment is to develop skills in managing data in databases and to gain understanding of data model development and implementation using a commercially available database management system development tool.
On completion of this assignment students will be able to:
a.Model organisational information requirements using conceptual data modelling techniques.
Convert the conceptual data models into relational data model

A.Write one paragraph describing the E-R diagram above in your own language
B.Implement above database on MS Access. You are required to create the above 4 tables with the given attributes. Name the database as ‘OrientalBank’.
C.Enter at least 10 data records for each table in the database you created in (a) above. Here you have to enter data into all 4 tables.
D.Write 4 examples of report cards that can be generated using the ‘OrientalBank’ database.
E.Write down the shorthand representation (database schema) of the structure of a relational database for the above E-R diagram
F.What is a primary key? What is the primary key for each table in the ‘OrientalBank’ database.

2.Write SQL queries for the following questions and execute the queries on the ‘OrientalBank’    database you created above using MS Access.                
(20 Marks)
A.Creating the four tables.
B.How many accounts have a balance greater than $500.
C.Display the Name, of all accounts with a balance greater than the average balance.
D.Find the ID, Name, Transaction number, date and type of the second account.
E.Find details of customers having their account in the second bank branch.
F.List the numbers and names of all customers.
G.List the complete account table.
H.List the account number and name of all customers that have opened their accounts in the second branch and that have a balance less than $1,000.
I.List the number and name of all customers that have opened their accounts in the second branch or that have a balance less than $1,000.
J.List the number and name of all customers who have opened an account in the “Melbourne city” branch.
K.Find the total of the balances for all accounts.

E-R diagram and Database Implementation

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:

C. Following are the screenshots of the 4 tables after entering 10 data records in each 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:

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

A. Please refer Task 1 (B) and (C) for the answer of this question.

B. SELECT Account.BALANCE

FROM Account

WHERE (((Account.BALANCE)>500));

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

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

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:

SELECT *

FROM Customer;

Result:

FROM Account;

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:

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

Select C.CID, C.CNAME

From Customer C, Branch B, CustomerBranchDetails D

WHERE C.CID=D.CID and D.BCODE=B.BCODE

and B.BNAME="MELBOURNE";

Select SUM(BALANCE)

FROM Account;

Result:

1. A) 1NF for the given data is:

PET ID	PET NAME	PET TYPE	PET AGE	OWNER
246	ROVER	DOG	12	SAM COOK
298	SPOT	DOG	2	TERRY KIM
341	MORRIS	CAT	4	SAM COOK
519	TWEEDY	BIRD	2	TERRY KIM

PET ID	VISIT DATE	PROCEDURE
246	JAN 13/2002	01 - RABIES VACCINATION
246	MAR 27/2002	10 - EXAMINE and TREAT WOUND
246	APR 02/2002	05 - HEART WORM TEST
298	JAN 21/2002	08 - TETANUS VACCINATION
298	MAR 10/2002	05 - HEART WORM TEST
341	JAN 23/2001	01 - RABIES VACCINATION
341	JAN 13/2002	01 - RABIES VACCINATION
519	APR 30/2002	20 - ANNUAL CHECK UP
519	APR 30/2002	12 - EYE WASH

1. B) 2NF for the given Data:

PET ID	PET NAME	PET TYPE	PET AGE	OWNER
246	ROVER	DOG	12	SAM COOK
298	SPOT	DOG	2	TERRY KIM
341	MORRIS	CAT	4	SAM COOK
519	TWEEDY	BIRD	2	TERRY KIM

Procedure_Id

PID	Treatment
01	Rabies Vaccination
05	Heart Worm Test
08	TETANUS VACCINATION
10	EXAMINE and TREAT WOUND
12	EYE WASH
20	ANNUAL CHECK UP

Patient_Treatment

PET_ID	PID
246	01
246	10
246	05
298	05
298	08
341	01
519	12
519	20

Patient_visitdate

PET_ID	VISIT DATE
246	JAN 13/2002
246	MAR 27/2002
246	APR 02/2002
298	JAN 21/2002
298	MAR 10/2002
341	JAN 23/2001
341	JAN 13/2002
519	APR 30/2002

1. C) 3NF for the given data:

The above relational schema is already in 3NF and there is no redundancy left which can further be divided.

Brownstein, Mark, Johnston, Stuart J. (November 14, 1988). "Microsoft Works on SAA Database". InfoWorld.  p .5.

Kevin Collins. (July 19, 2005). Microsoft Jet Program Management, "Microsoft Jet 3.5 Performance Overview and Optimization Techniques", MSDN.