Get Instant Help From 5000+ Experts For
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing:Proofread your work by experts and improve grade at Lowest cost

And Improve Your Grades
myassignmenthelp.com
loader
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Guaranteed Higher Grade!
Free Quote
wave

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.

Cite This Work

To export a reference to this article please select a referencing stye below:

My Assignment Help. (2021). Developing And Implementing Data Models Using MS Access: OrientalBank Database Essay.. Retrieved from https://myassignmenthelp.com/free-samples/mn405-data-and-information-management/relational-schema.html.

"Developing And Implementing Data Models Using MS Access: OrientalBank Database Essay.." My Assignment Help, 2021, https://myassignmenthelp.com/free-samples/mn405-data-and-information-management/relational-schema.html.

My Assignment Help (2021) Developing And Implementing Data Models Using MS Access: OrientalBank Database Essay. [Online]. Available from: https://myassignmenthelp.com/free-samples/mn405-data-and-information-management/relational-schema.html
[Accessed 22 November 2024].

My Assignment Help. 'Developing And Implementing Data Models Using MS Access: OrientalBank Database Essay.' (My Assignment Help, 2021) <https://myassignmenthelp.com/free-samples/mn405-data-and-information-management/relational-schema.html> accessed 22 November 2024.

My Assignment Help. Developing And Implementing Data Models Using MS Access: OrientalBank Database Essay. [Internet]. My Assignment Help. 2021 [cited 22 November 2024]. Available from: https://myassignmenthelp.com/free-samples/mn405-data-and-information-management/relational-schema.html.

Get instant help from 5000+ experts for
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing: Proofread your work by experts and improve grade at Lowest cost

loader
250 words
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Plagiarism checker
Verify originality of an essay
essay
Generate unique essays in a jiffy
Plagiarism checker
Cite sources with ease
support
close