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.
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, and (c) the (relational) logical database design. For each of these, you should identify the major design decisions that you faced and the design decisions that you made with justications 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.

4. 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 dierential grades.

2. DATABASE DESIGN REQUIREMENTS

A requirements analysis that was conducted has identied a few requirements about the operations and goals of CS331-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 CS331-BANK.

• The bank is organized into branches. Each branch is in a particular city with an address and is identied 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 identied by their social security numbers. The bank stores each customers 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 identied by their social security numbers. The bank stores the name and telephone numbers of each employee, the names of the employees dependents. The bank also keeps track of the employees 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 oers 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 identied 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 identied 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.