CSC443 Database Management
Spring 2006
Major Project #2 Alternative

Due:May 8

To turn in the project: Email the project to me at pepper@adelphi.edu

Design a database for a bank. It should have customers, checking accounts, transactions, and branches. Customers can have many accounts. Accounts can have many transactions. The opening balance is, itself, a transaction. Every account is assigned to a branch. Customers can open accounts in many different branches. Transactions can only be for one account (so a transfer would actually be two different transactions – one out and another in). Every transaction has a date, amount, fee, type and reference number, as well as a transaction id number. Money into the account should be a positive amount and money withdrawn is a negative amount. Fees should all be negative amounts. Please record the customer’s name and address, including their zip code. Please record the branch code, name and address, including its zip code. Please record the date the checking account was opened.

Design an E-R diagram first and then convert it into tables. Create your own data for all tables and implement the database including the following queries in Oracle SQL.

For extra credit, include a java menu to execute the queries.

The files turned in include
1) E-R diagram and tables converted from the diagram.
2) A script to initialize the database including filling the tables with data.

3) A script file of the queries
4) A capture file containing a run of the script file of the queries with echo set on.

Queries to be implemented:
1) List the names of all branches
2) List all the withdrawal transactions in all the accounts showing the date, amount, fee and the total transaction amount (amount + fee). (A transaction is a withdrawal if the amount is negative.)

3) List all the customers who had a transaction type DEP in the month of April.

4) List all the total value of every account, showing account # and value

5) List the total value of every account, showing account #, and value, and customer name

6) List the total value of every account, showing account #, and value, and customer name. List only accounts whose total value is under 1000.

7) List the total account value for each zip code

8) List the average account value for each customer.

9) List all the customers who have no accounts.

10) List all the customers who did not make any transactions in 2006. (In testing be sure to have one customer that had transactions in both 2006 and 2005 to be sure that customer doesn’t make your results.)

11) List every transaction with its branch name, customer name, account number and transaction id and amount.

For example (results are omitted here):

Query 1:

SQL
---
SELECT cust_name
FROM CUSTOMERS

EXPLANATION
-----------
List the names of all customers
by selecting all customers in the CUSTOMERS table.

Query 6:

SQL
---
SELECT order_date
FROM ORDERS, ORDER_DETAILS
WHERE ORDERS.order_no = ORDER_DETAILS.order_no and
order_date > '30-may-91' and order_date < '01-july-91'
GROUP BY order_date
HAVING SUM(order_qty * order_price) > 1000

EXPLANATION
-----------
On what days in June 1991 did the sum of the value of all orders
taken on that day exceed $1000?
The where clause does a join and checks that the order date
was in June 1991.  The phrase "value of all orders" indicates
order_qty * order_price.  Checking the sum for "that day"
indicates that we need GROUP BY... HAVING...