ianroo

Database Project Final Report
Amy Hu and Andrew Van Sant
2007. 04. 24

Transaction Processing

We did not use any transaction processing beyond what MySQL and the MySQL 
Connector/J provide on their own.  We assumed that if a connection could be 
established with a server that all the queries would operate without flaw.  In the event 
of a transaction where multiple commands need to be issued, we assumed that there 
would not be an interruption between commands.  

Access Control

We use JSPs session construct for access control.  Basically, before any JSP page 
is executed, we first check to see if the session credentials authorize the user to be 
at the JSP page they are using.  We have three variables, "accounttype", "username", 
and "super".  

Application Architecture

We used a 3-tier architecture for our project.  The web-browser provides the first tier, 
rendering our pages using provided xhtml and cascading style sheets. Our tomcat server
 recieves the requests, and responds to them appropriately, making connections to the 
database as need be. Finally, a MySQL server stores all the data and enables quick 
retrieval.

List of Developed User Interfaces with their Purposes
All of our UI has a standard look and feel which was achieved by using cascading style 
sheets.  We will now list the specific "pages" and what each of them do. In many cases 
the title of the page will make it self explanatory. It should be noted that success and 
failure of an operation is always noted using visual feedback to the user.

Initial Login Page (index.jsp) - This page allows the user to login.  They must explicitly 
state whether they are a customer or staff, as well as providing a username and password.
  Depending on whether or not they check out they will be forwarded to the appropriate
 page. A drop-down box is provided for convenience. 

Main Customer Page (customer/index.jsp) - This page lists customer information as 
well as a summary of all accounts they are allowed to interact with.  Also, at the bottom
 of the page there is a UI for transferring funds between accounts.  We used drop-down
 boxes to eliminate the possibility of getting many improper inputs (for instance, people
 attempting to transfer money into a CD). It's also handy for users that they don't need 
to type in the actual account number themselves. Basically, you can only select a transfer
 fund combination that is valid.  You can click on the account numbers in the account 
summary for more detailed transaction history.

Transaction History (customer/transhist.jsp) - This page will show the transaction
 history for a particular account. The account it shows is passed along to it from the 
Main Customer Page. Note that all transactions where the account is either the primary 
or secondary account are displayed.

Main Staff Page (staff/index.jsp) - This page is strictly for navigation, it allows you to
 get to other pages which will let you do things.  These things include: Create Client, 
Edit Client, Add User to Group, Create Account, Deposit/Withdraw, Transfer Funds,
 and Inquiry. Additionally, if you are a manager / superteller you will be given these 
extra options: Update Account, Create Teller, Modify Teller, Delete Teller, SQL 
Input, and Database Log, which are not shown to non-superteller users. Upon the 
initial login page, when the affiliation “staff” is chosen, the system will automatically 
check whether the user is a superteller or not, and show the appropriate navigation
 options accordingly.

Create Client (staff/createclient.jsp) - This page allows you to create a new client. It 
accepts an id, password, name, and address. As long as there is a value for each, and 
the id is not already taken, the inputs are considered valid.  Creation date and the teller
 who created the account are recorded implicitly.

Edit Client (staff/editclient.jsp) - This page allows you to edit a client.  You can select
 the client you wish to edit from a drop-down menu, and then decide if you want to 
change the name, password, or address of the client by choosing, or not choosing to 
type a value into the available fields. Only the fields whose values you provide will be 
modified.

Add User to Group (staff/modifygroups.jsp) - This page allows you to add users to 
groups.  You can select the user from a drop down box, but you must type in the group
 name. Typing in the group name means that you are also creating new groups if you 
add a user to a group that does not exist. The purpose of using “groups” is to further
 support the co-ownership of accounts. Each account is associated with a group, rather
 than individual clients. A group might contain only one user, or multiple ones.

Create Account (staff/createaccount.jsp) - This page allows you to create an account.  
You select the group that the account will belong to from a drop-down box as well as
 the type of the account.  From there you need to fill out the required data for the type
 of account you have selected.  If you add values to fields that are not required, they 
will be thrown away automatically.  If you do not provide data for required fields, you
 will be prompted to do so upon submission.

Deposit/Withdraw (staff/depositwithdraw.jsp) - This page allows you to deposit and 
withdraw money from an account.  A drop-down box is provided here for choices as 
well.  You must type the amount and account number manually.  In the event of 
withdrawing form a mature CD, the entire amount is withdrawn implicitly.  Also, you 
cannot withdraw/deposit from an account that does not exist. You may not input a 
negative value in the amount field either.

Transfer Funds (staff/transfer.jsp) - This page allows a teller to transfer funds from one
 account to another.  It only lists valid accounts in the “to” using drop-down boxes 
(non-matured CDs for instance are not considered).  It does the same for the “from”
 field.  You should also input an amount, but for a matured CD the amount is implicitly
 the entire value.

Inquiry (staff/inquiry.jsp) - This page allows you to select an account from a drop down 
box, which will then retrieve all the account information as well as its full transaction 
history.

Update Account (staff/updateaccount.jsp) - This page allows you to update account 
information.  You select the account and the type from drop-down boxes, and then
 type in the fields you want to change. All fields with values will be changed to those
 values, all other fields will be left alone.

Create Teller (staff/createteller.jsp) - This page allows you to create a teller.  You put
 in an id, name, password, and whether or not the account is a super account. As long 
as the username is not taken the teller account will be created.

Modify Teller (staff/modifyteller.jsp) - This page allows you to modify a teller.  You
 select the teller from a drop- down box and then enter in new values for either 
“sname”, “sPW”, or “super”.  If a new value is entered the value is changed,
 otherwise, it stays the same.

Delete Teller (staff/deleteteller.jsp) - This page allows you to delete a teller. You 
select the teller from a drop down box.  Upon submission, the teller will be deleted.

SQL Input (staff/sqlin.jsp) - This page allows you to execute any arbitrary SQL 
command.  As long as the syntax is okay, the command will be executed, otherwise i
t will be noted that something was wrong with the SQL command.  It should be noted 
that queries are not allowed (as no visual feedback will be provided), just updates and
 inserts.

Database Log (staff/dblog.jsp) - This page displays all the rows in the log table of the
 database.  The rows are all automatically generated by triggers whenever anything is
 done.

Roles of Each Student in the Project

We split the work up equally, and met on many occasions in person to work on the
 project.  We did about half of the project in each others' company for discussion and 
debugging, and the rest of the project on our own time.  Roles weren't assigned in the 
“A does X and B does Y” sense. We more just kept working on the next logical 
step until everything was done.  We both have knowledge as far as all the technologies
 were concerned, so we were both able to contribute in every way.