Purpose of the assessment

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.

b. Convert the conceptual data models into relational data model

Assignment Description

1. Below is the entity-relationship diagram for the banking database application, there are four relations/tables in their database describing banking application details, details of projects, work assigned for eachbranch and account, namely, BRANCH, ACCOUNT, CUSTOMER and TRANSACTION. The relationship diagram for these relations are given below.

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.

A. Creating the four tables.
B. How many accounts have abalance 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 typeof the second account.
E. Find details of customershaving 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 haveopened 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 haveopened 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.

3. Write the 1NF, 2NF, 3NF for the below data.

HEALTH HISTORY REPORT

PET ID    PET NAME              PET TYPEPET AGEOWNER VISIT DATE            PROCEDURE

246          ROVER                   DOG           12            SAM COOK        JAN 13/2002           01 - RABIES VACCINATION

                                                                                                                MAR 27/2002          10 - EXAMINE and TREAT WOUND

                                                                                                                APR 02/2002           05 - HEART WORM TEST

298          SPOT                      DOG           2              TERRY KIM        JAN 21/2002           08 - TETANUS VACCINATION            

                                                                                                                MAR 10/2002          05 - HEART WORM TEST

341          MORRIS                 CAT             4              SAM COOK        JAN 23/2001           01 - RABIES VACCINATION

                                                                                                                JAN 13/2002           01 - RABIES VACCINATION

519          TWEEDY                                BIRD           2              TERRY KIM        APR 30/2002           20 - ANNUAL CHECK UP

                                                                                                                APR 30/2002           12 - EYE WASH