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 each branch and account,  namely,  BRANCH, ACCOUNT, CUSTOMER and TRANSACTION.  The relationship diagram for these relations are given below.(20 Marks)

1. Write one paragraph describing the E-R diagram above in your own language

2. Implement above database on MS Access. You are required to create the above 4 tables with the given attributes. Name the database as ‘OrientalBank’.

3. 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.

4. Write 4 examples of report cards that can be generated using the ‘OrientalBank’ database.

5. Write down the shorthand representation (database schema) of the structure of a relational database for the above E-R diagram

6. 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)

1. Creating the four tables.

2. How many accounts have a balance greater than $500.

3. Display the Name, of all accounts with a balance greater than the average balance.

4. Find the ID, Name, Transaction number, date and type of the second account.

5. Find details of customers having their account in the second bank branch.

6. List the numbers and names of all customers.

7. List the complete account table.

8. 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.

9. 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.

10. List the number and name of all customers who have opened an account in the “Melbourne city” branch.

11. Find the total of the balances for all accounts.

3) Write the 1NF, 2NF, 3NF for the below data. (20 Marks)


PET ID       PET NAME                  PET TYPE  PET AGE   OWNER  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

You can order this assignment from our service and receive a completely high-quality custom paper. Our service offers any of the academic papers written from scratch, according to all customers’ specifications, expectations and highest standards.”


Related posts: