DATA BASE DESIGN REQUIREMENTS

Computer Science 631

Database Management Systems Design Term Project

In this term project, you are asked to design a small database system, create, and populate this database using ORACLE, and write a few application programs to access the database. The topic of the project is to design the database and corresponding applications. In the following, you are given the requirements for the database design (Section 1) and the requirements for application design (Section 2).

1 GENERAL GUIDELINES

The following guidelines apply for the project:

1. The projects will be done in groups of two. You should form your own groups and post a message in the designated forum if you have not done it yet.

2. You are required to demonstrate your programs. You should treat these demonstrations as if you were giving them to your customer. So, prepare them professionally. The demonstrations will take place at the end of the term, after classes are over. We will put up a sign-up sheet soon.

3. You are required to submit a typed project report at the end of the process. This report should minimally cover (a) a summary of the system requirements and any additions you may have made, (b) the entity-relationship design, (c) the (relational) logical database design, and (d) the application program design. For each of these, you should identify the major design decisions that you faced and the design decisions that you made with justiﬁcations for those decisions. Also include, as an appendix, a list of the relational instances you have used to populate your database and the code written for the application.

( 1 )

4. Grading will be done as follows: (a) design report: 40%; (b) application programs: 60%. The report must be typed and should be written clearly. The presentation (language and communication of ideas) of the report is very important. Please have it read by someone else before you submit it.

5. In general, each member of a group will be assigned the same grade. However, if I notice that one member of the group is doing all (or most of) the work, I reserve the right to assign diﬀerential grades.

2 DATABASE DESIGN REQUIREMENTS

A requirements analysis that was conducted has identiﬁed a few requirements about the operations and goals of CS631-BANK. You, as the systems analyst/designer, should feel free to add to these requirements to achieve a richer design. The following list itemizes the major requirements for the CS631-BANK.

· The bank is organized into branches. Each branch is in a particular city with an address and is identiﬁed by a unique branch-ID and a name. The bank monitors the assets of each branch. Each branch of the bank has a manager and an assistant manager who are employees of the bank.

· Bank customers are identiﬁed by their social security numbers. The bank stores each customer’s name and address (apartment number, street number, state, city and zip code). Customers may have several accounts and can take out loans that are managed as accounts as well. A customer may be associated with a particular branch and has a personal banker who works with the customer their loan and bank transactions.

· Bank employees are also identiﬁed by their social security numbers. The bank stores the name and telephone numbers of each employee, the names of the employee’s dependents. The bank also keeps track of the employee’s start date and, thus, length of employment. An employee at the bank works for one of the branches and reports to the manager of that branch. may have a manager and the manager is in charge of a certain number of employees.

· The bank oﬀers several types of accounts, savings, checking, money market and loan accounts. An account can be held by more than one customer, and a customer can have more than one account. Each account is assigned a unique account number. The bank maintains a record of each account balance and the most recent date on which the account was accessed by each customer holding the account. In addition, saving and loan accounts have fixed interest rates, money market accounts have variables interest rates regularly updated based on the stock market. and overdrafts are recorded for each checking account.

· A loan originates at a particular branch and can be held by one or more customers. A loan is identiﬁed by a unique loan number (similar to an account number). For each loan, the bank keeps track of the loan amount and the loan monthly re-payment amount.

· The bank keeps track of all the transactions. A transaction is identiﬁed by a unique code and has a type of name. For example, “WD” is the code for withdrawal, “CD” for customer deposit. When a customer makes a transaction, the transaction record should identify the transaction code, the date, the hour, an amount, and an account. Some transactions are free but the bank charges for most of them. If a customer makes a chargeable transaction, the charge is also registered as a chargeless transaction.

This is the end of the requirements report. You are now expected to work on this report to come up with your database design. This requires the development of (a) an entity relationship model for the conceptual design, and (b) a relational schema design for the logical design. You are also expected to populate this database with some sample data of your own so that you can demonstrate the functionality to your customer.

3 APPLICATION DEVELOPMENT REQUIREMENTS

You are expected to write three application programs (using SQL, JBDC, …) that will allow the employees of CS631-BANKto manage customers, keep track of transactions, and print passbooks for customers. You must make decisions as to the appearance of your forms. There is no standard output format.

3.1 Transaction Application

In fact, every transaction in the bank has a counterpart. When an account is debited, another must be credited. When a customer makes a cheque deposit for example, this credits their account but the account from which the money is taken from must be debited. To keep things simple, we will allow only cheques within the bank on accounts that are listed in the CS631-BANKdatabase. When a customer withdraws money, his account is debited, and the counterpart is a manual operation (a form to ﬁll out and sign) that is not registered in the database. Same for cash deposit, the customer account is credited, and the counterpart transaction is a record of deposit given to the user. The bank has special accounts for management purpose. One of these accounts is CHARGE that collects all the charges on customer’s accounts. After a transaction, the balance of the account is updated. For debit transactions, be sure that the balance of the account allows the transaction. Otherwise, the transaction is not allowed, and the customer is charged. Each month, a service charge ($10.00) is applied on all the accounts. You will have to design the interface for inserting this data into the database. The database should be updated accordingly after each transaction to reflect the balance of the accounts involved.

3.2 Passbook Application

This program is used to print passbooks for customers. The passbook starts by a balance forward, the balance of the account the day of the latest passbook update. Then, it lists all the entries in order since. The program, invoked by a one-word command or a click on the main form, should ﬁrst issue prompts to obtain the account number and/or the customer’s name. Then the inquiry program displays the transaction information resulting from retrieving the data. The program should display the account number and owners and a list of transactions as follow:

Date	Transaction Code	Transaction Name	Debits	Credits	Balance
31 SEP		Balance Forward			1700.00
1 OCT	WD	Withdrawal	800.00		900.00
2 OCT	SC	Service Charge	2.00		898.00
3 OCT	CD	Customer Deposit		200.00	1098.00

3.3 Customer Application

This program helps employees of CS631-BANKto create, delete, and modify a customer. To be considered as a customer of the bank, you must open an account. The customer must make a deposit of at least $500 to open an account.