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