Essay title - Botswana Bank Record Tracking System
Introduction
This project was done with the aim of fulfilling the necessary requirements expected to obtain an Honors degree in Computer Studies and was directed at producing a system that can track records for Botswana Savings Bank who are the project sponsors.
The aim of the project was to help the project sponsors to keep track of records of their passbooks and to control their movement within the organization departments. There was need to have a system that could help in improving customer service delivery to their customers and to have the press of button system that can produce the passbook status and its whereabouts as quickly as possible.
The project was chosen as it could help the author gain experience in database design and gain knowledge on securing them and the subject topic was of importance as it brought about challenges that were beneficial and of best personal interests.
The report is structured as follows; Chapter two is a research topic on Database Security, and the next chapter (chapter 3) is a research on Database Tracking. The following chapter discusses the project management and approach and planning followed by chapter five that covers analysis. Chapter six is the design phase and chapter seven covers the testing phase. A consolidated report of testing follows with different testing methods conducted. Chapter nine is the project evaluation, the last chapter ten suggests recommendations for possible future work, and References for the two research topics are listed in the last chapter, and the reports conclude with the appendices.
Sponsor Background
Botswana Savings Bank is one of the commonly used Banks which caters mainly for the low class income earners and to help Remote dwellers to bring resources closer to them. The Bank offers its services through their agents being the Botswana Post through their branches and transaction can be made in any Post office around the country. The use of Passbooks also makes life harder as the Bank charges are deducted on monthly basis and in most cases the information is not updated on the passbooks. This calls for passbook verification every year which is done at the Head Office where the passbook is audited and interest is reflected on the passbook. A system is needed to keep track of incoming and outgoing passbooks.
Research Topics
Two research topic were chosen, the first was a research on Database Security which is the core of every database security and the second research topic was on Database tracking which is a good way of learning how a tracking database is implemented and distributed as well as to learn more on how they operate when manipulated by users.
2. Database Security
2.1 Introduction to Database security
Database security refers to the protection of information on a database from access to unauthorized persons or intruders of any kind for personal gain, and the building of such security involves the stages of design, development and implementation of security to cover and protect the database. (Hernandez, 2005) describe Operational database and Analytical database as the two types of database in database management. Operational database is used primarily in online transaction processing(OLTP) scenarios, in a situation where there is need to collect, modify and maintain data. In contrast the online analytical processing is used in a situation where there is need to store static data that is rarely modified. This data is used mainly used in marketing analysis and chemical labs. In comparison to Operation databases that stores volumes of data used on transaction processing like banks. (Ranakhrishnan & Gehrke) describe secrecy, integrity and Availability as the three main objectives to consider when designing a database. These are the main keys to database security building, and to have them in place a security plan that will guide the processes should be developed that will clearly describe measures that should be taken to enforce security. Also a note should be made that the number of users authorized to use the database contribute to this design as they will affect the type of security that will be implemented as some of the information access will be restricted from some users as it is deemed confidential. In a Relational Database system these include database security system and authorization sub-system that is responsible for ensuring that part of the restricted database is inaccessible to unauthorized users. (Ranakhrishnan & Gehrke) describe Discretionary Security Mechanism and Mandatory Security Mechanism which would be covered further in this chapter.
Though statistics shows that in many instances security is considered an afterthought, Donald K. Burleson sees it differently as he believes that some people confuse open system to security and this can seem impossibly opposite. This can bring about sub standard security as the judgment to the kind of security proposed could be underestimated. In that Ratan (2005) describe the three binding layers of security in the corporate security policy that defines the rules, procedures, and processes that aim to protect against and respond to security threats as Authentication, authorization and administration.
2.2. Aspect of Security
There is a distinction between Network security and data security but they differ because data security is meant to protect the data whereas the latter protect the whole system including data on the database. Therefore it is important that the security setting of the database security should not supersede the network security. For best practices database administrator should ensure that log files are up and running to record the database changes for auditing purposes. Also correct access right to specified tables should be granted per user and correct restrictions should be set. This would be determined by the size of the organization and the users logging onto that system. Ault 2003 describes the number of users as a way of determining processing power needed to run the database successfully. In some instances the users are grouped by department as different departments uses different tables for different purposes. Security checks should be done regularly to check any irregularities. And for this convenience it is important to research further on the correct methodologies in linking Database security and the Network security to see how they are going to affect and compliment each other. As said on the introduction at database level the 3A’s of security are the core of building an effective security at database level and thus should be studied for a comprehensive security implementation.
2.3. Securing Databases (Authentication, authorization and administration)
the base of the security model.
The correct process of identifying who is trying to access the database lies in the correctness of the undertaken process,
( Elmasri & Navathe 2004) describe the Database Administrator as the central authority for managing a database. The Database administrator has an account in the Database commonly known as the System or Supervisor account, which provides powerful capabilities that are not made available to regular database accounts and users. The administrator is the one in charge of assigning menus to users. Database administrator privileged commands include commands for granting, and revoking privileged to individual accounts, users, or user groups and for performing the following types of action:
- Account Creation- Creates new account and Password for a use or group of users to enable access to the database.
- Privilege granting- used to control discriminatory database authority
- Privilege Revocation- used to control discriminatory database authority
- Security level assignment – is used to control mandatory authorization.
To further supports standard access control practices the Database Administrator must ensure that individual or group must first apply for a user account. The Database Administrator will then create a new account number and Password for the user if there is legitimate need to access the database. The user must log in to the DBMS by entering the account number and Password whenever database access is needed. The DBMS checks that the account number and password are valid, if they are; the user is permitted to use the DBMS and to access the Database.
2.4 Password Security
Oracle allows you to create Password Profiles that govern the behavior of the database with respect to passwords and authentication. To do this, create a profile with values set for the attributes you wish to use. The profile then can be enforced on your users. Oracle supports the following attributes for password profiles:
- Password lifetime which allows a password to exist for a specific period of time
- Grace period Time at which Database begins to warn users to change their password
- Reuse time/max Supports password history and forces users to use new passwords
- Failed login attempts Locks the account if the incorrect password is given after specified number of times
- Account lockout Disables the account (combined with failed attempts to help prevent brute force attempts into user accounts)
- Password Verify Function Defines the password complexity function that will be called when the user changes the password
Normally, after creating a User profile, users should be prompt to change their passwords to ensure that they create password that meets the set standard. Their password should be set to expire the moment they log into the system. Upon their next login, the database prompts them to renew their password. The new password is checked against the complexity routine and the other password profile values will also be enforced. Although most Database allows administrators to configure authentication of their choice and it remains with them to avoid dangerous and weak authentications. Ratan (2005) describes Passwords as our first line of defense and sometimes our only line of defense, so we should make sure that we count on them.
Keep the Password Policies Practical
A password profile is a great way to ensure that good password management practices are being used. Once again, however, you have to balance security with usability. While using password profiles is generally a good idea, it can backfire. For example, forcing users to choose a new password each week (that is, expiring passwords too frequently) may in fact force the user to use easy passwords or worse, write down their passwords.
As another example, you may decide after three failed logins, you’ll lock the user account for a day. There are unintended consequences to this. The failed login and account locking can aid someone launching a denial of service (DoS) attack. The attack is made easy because a malicious person can intentionally lock all the database accounts by simply providing an incorrect password for each database user.
2.7 Conclusion
The first step to robust systems is bedded on security and to have peace of mind the first step would be to invest on security. And as such the protection of your systems from any form of intrusion is to start building your security at Server level. It is important to plan how your users are to access network resources and how databases would be distributed on the network. It is clear that database security is the protection of database from any form of unauthorized use, and as such it is very important to ensure that it is well implemented. As technology changes and new securities are implemented there is also possibilities of loopholes that could lead to unauthorized people accessing to your restricted areas. Also as technology grows so is the advancement and enhancement of hacking tools to crack down our system caution need to be taken to ensure constant checks for intruders. Achieving comprehensive DBMS security requires ensuring that database security policies are aligned with IT security policies and taking strong advanced security measures to harden the database environment. The future of DBMS security will focus on increased automation, tighter integration with technology stack components, and intelligent features. And the future of database security lies in our hands.
3. Research Topic 2- Tracking Information
3.1 Introduction
Every database is designed with something in mind. Its’ designed to fulfill a specified purpose. And what ever you program you use to design the database it should be to accomplish the expectation. Depending on what database would be used for it is important to choose a program that would be safe, secure and reliable. And in most DBMS and Relational database are used for easy distribution and re-distribution and to ensure non redundant data. This brings us to the control of the database and tracking. For every database there should be control both in the growth and management of the database. And to achieve this successfully information need to be tracked. The speed at which the query returns results will depend on the correctness of the database design and the information will be return information faster than programs that retrieve data from log files. Also the performance of the database determines the speed at which information can be retrieved. Most people use Structured Query Language to retrieve information from databases because its’ command structures are simple and well defined, so good programmers can create an SQL query much more quickly than they could code a program to do the same thing, and the query would be less prone to error and easier to understand. As Michael J. Hernandez puts it analytical databases are primarily used in on-line analytical processing (OLAP) scenarios, where there is a need to store and track historical and time-dependent data. This method is a valuable asset when there is a need to track trends, view statistical data over a long period of time, and make tactical or strategic business projections. This type of database stores static data, meaning that the data is never (or very rarely) modified. This type of database stores static data and is used when there is a need to track trends, view statistical data over a long period of time, or make tactical or strategic business projections; it is typically associated with OLAP. This is the main and core of this project which is to produce a tracking database. After a database is deployed and is in operation, actual use of the applications, transactions, and views reveals factors and problems that may not be accounted for during the initial physical design. And to address these bottlenecks, resource utilization and internal DBMS processing should be monitored to enable better estimation of the database size and the volumes of activities. It is therefore necessary to monitor and revise the physical database design. This calls for tuning. As marked the dividing line between physical design and tuning is very thin (Elmassi 2004), that the design decision we made are revisited during tuning phase adding that the inputs to the tuning process are done to make applications run faster, lower response time of queries, and to improve overall throughout the transaction.
To know and address the situation certain questions need to be addressed. This will help come with the right solution to the crisis which basically can be resolved by creating and using a tracking system. One might first of all want to know what is a tracking system and why tracking system. To answer this, another question needs to be clarified and that is a database. A database is organized collection of data used for the purpose of modeling some type of organization or organizational process. There are mainly two types of Databases in database management. These are operational database and analytical database. But for the better part of the project we will look at one method and that is analytical database. This is so as this method seem to be in line with what we want to achieve.
3.2 Tracking -Account number and Password
How easy or hard to track the system depends on the system setting in place. Some believe tuning the system is not and easy thing or not important. But It is easy to keep track of database users and their accounts and password by creating an encrypted table or file with two fields, Account number and passwords. This can easily be maintained by DBMS. When ever an account is created, a new record is inserted into the table. When an account is cancelled, the corresponding record must be deleted from the table. The database must also keep track of all operation on the database that are applied by a certain user throughout each log on session, which consists of the sequence of database interaction that a user performs from the time of logging to the time off. When a user logs in, the DBMS can record the user’s account number and associate it with the terminal from which the user logged in. all operations applied from that terminal are attributed to the user’s account until the user logs off. It is particularly important to keep track of update operations that are applied to the database so that if the database Administrator can find out which user did the tempering.
3.3 Tracking Logs
To keep a record of all updates applied to the database and of the particular user who applied each update, we can modify the System log. System log includes an entry for each operation applied to the database that maybe required to for recovery from a transaction failure or system crash. The log entries can be expanded so that they can also include the account number of the users and the online terminal ID that applied each operation recorded on the log. If any tempering with the database is suspected, a database Audit is performed which consists of reviewing the log to examine all access and operations applied to the database during a certain time period. When an illegal or unauthorized operation is found, the DBA can determine the account number used to perform this operation. Database audits are particularly important for sensitive databases that are updated by many transactions and users such as a banking database that is updated by many bank tellers. A database log that is used mainly for security purposes is sometimes called Audit Trial.
Audit Trials and log
To keep track of database transactions the DBMS maintains a special file called a Log (or Journal) that contains information about all updates. The log may contain the following data:
- Transaction records containing transaction identifier and type of log record (transaction start, insert, update, delete, abort, commit).
- Identifier of data item affected by the database action before- image of the data item, that is its value after change.
- Log management information such as pointer to previous and next log records for that transaction.
- Checkpoint records- the point of synchronization between the database and the transaction log file. All buffers are force written to secondary storage.
Checkpoint records are scheduled at predetermined intervals and involve the following options:
- Writing all log records in main memory to secondary storage
- Writing the modified blocks in the database buffers to secondary storage
- Writing a checkpoint record to the log file. This record contains the identifiers of all transactions that are active at the time of checkpoint.
3.4 Web tracking
To design a database in Web design you have to:
- minimize load times
- minimize query times
- minimize administration and Maintenance
- minimize database design
And to achieve these goals, certain decisions have to be made. The time it takes to load your data will depend on how much data you want to load, whether you use a ‘look up’ table, or whether your database is stored on a RAID system.
As Bill Winnet (2000) puts it sometimes the decision you take might conflict. For example, to minimize query time, you may have to create and maintain summary tables. But if you do this administration and maintenance time increases, and the size of your database grows. And as you make these database decisions, don’t forget that people who look at your data will, at some point, want to audit and compare it with the data in your Web server log files.
3.5 Database Record Matching
Another effective method that could be used is Database Record Matching. To ensure perfection in the database and that the database does not duplicate customer who already exist in the database another method has to be adopted. That method is used to track records that were previously captured on the system and they can then be updated and not recaptured on the system thus the elimination of database redundancy and duplication. It should be noted that this method can only be used combined with other methods of tracking to ensure that maximum tracking is reached. Database Record Matching, also referred to as “lifetime affiliate tracking”. Database Record Matching is the least used method of tracking because it is difficult to operate and maintain and requires the use of at least one other method of tracking. Database Record Matching cannot be used alone since it only tracks returning customers and not new customers. The tracking of the initial customer must be done by another tracking method, and then unique contact information about the customer is stored along with the referring affiliate’s ID. Anytime that customer makes another purchase the database can then be searched to find whether or not that customer belongs to an affiliate. As I stated earlier in addition to the various methods of tracking available there are also many different uses for the tracking. Some of the most popular uses for tracking are in advertising, email campaigns, and to operate an affiliate program.
Businesses who track their advertising are able to tell how well a particular ad was received which can help them to maximize their profits. By looking at the ad tracking results they can tell whether an ad campaign failed to bring the expected results in all mediums, in which case the entire ad needs to be changed or if the problem was only in a particular medium thus allowing them to put more money in those mediums where the ad performed well. In today’s market where advertising budgets are shrinking by the minute, knowing where to spend your advertising dollars is of key importance.
3.6 Tracking in Oracle
In his book MicrosoftSql Server 2005 New features Michael Otey says new Database Snapshot feature provides a read-only snapshot of database at a specified time in the database. In Oracle snapshots can be created and updated. They are updated by one or more tables and this process is known as REFRESHING. There are two types of common Refresh, Fast, Complete and Force. Fast method is used to update the changes made to the master database. Creating a Database Snapshot is an inexpensive operation server-wise, as the server basically uses metadata in conjunction with recovery to create the viewpoint. It’s important to understand that Database Snapshots are not a complete copy of a database. Instead, creating a Snapshot is a metadata-only operation. A Database Snapshot uses the same data pages as the original database, so it doesn’t require a great deal of additional disk space. Database Snapshots are built using copy-on-write technology where anytime a change is made to one of the source database’s data pages, a copy of that page is saved for the Database Snapshot and then the updated page is written the same way it normally would be. When the Database Snapshot is accessed, it uses the shared data pages until it gets to the changed page, and then it will look at the pages that have been copied rather than the data pages that contain the updated data. In this way, the Database Snapshot needs storage for only those pages that have been changed since the time the Database Snapshot was created. Database Snapshots can be combined with Database Mirroring to create a reporting server based on the data that’s on the mirrored server. Normally, the data on the mirrored server is always in recovery mode, so it can’t be accessed by an application. On another note, you can create a Database Snapshot that is based on the mirrored database and that Database Snapshot can be accessed in read-only mode for reporting. Although the database on the mirroring server can’t be directly accessed because it’s in an ongoing state of recovery, that doesn’t affect the Database Snapshot, which accesses a snapshot of the data pages in the mirroring server’s database. Creating a Database Snapshot on the mirroring server enables you to better utilize the processing power of the mirroring server by enabling you to shift your static reporting to that server. One thing to consider when using Database Snapshots on a Database Mirror is that in the event of a failover, the existing Database Snapshots that were created on the mirroring server will remain intact. It’s important to realize that Database Snapshots are an availability feature, not a failover feature. They provide more ways to access your data, increasing your data’s availability. By default, all three types of replication use a snapshot to initialize Subscribers. The SQL Server Snapshot Agent always generates the snapshot files, but the agent that delivers the files differs depending on the type of replication being used. Snapshot replication and transactional replication use the Distribution Agent to deliver the files, whereas merge replication uses the SQL Server Merge Agent. The Snapshot Agent runs at the Distributor. Snapshots can be generated and applied either immediately after the subscription is created or according to a schedule set at the time the publication is created. The Snapshot Agent prepares snapshot files containing the schema and data of published tables and database objects, stores the files in the snapshot folder for the Publisher, and records tracking information in the distribution database on the Distributor. You specify a default snapshot folder when you configure a Distributor, but you can specify an alternate location for a publication instead of or in addition to the default.
Oracle transactional publications are implemented using the transactional publishing architecture of SQL Server; however, changes are tracked using a combination of database triggers on the Oracle database and the Log Reader Agent. When creating a new transactional publication, there are options offered for configuration including the option of sync_method. This option should be used to specify how you would want your snapshot to work and the how you would control the locking of tables when replicating.
(Enterprise Servers and Deployment, SQL Server 2005)
3.7 Damaged Page tracking
A closely related new feature that ties in with SQL Server 2005’s ability to perform page-level restores is the ability to track damaged pages. Any bad pages that are encountered on a read operation are tracked in a table, and by using the fine-grained restore capability, you can restore on a page-by-page basis while the database remains online. Any transaction that uses data from a damaged page is rolled back. If the bad page happens to turn up during a transaction rollback, then the database will be forced to restart. Another special feature in Oracle is the track file which is used to track blogs that have been modified since the last incremental backup. This method of tracking is new but can be of much importance. (Kyle 2005) points the log file as responsible for reading the blocks that have been modified since the last incremental backup. Also there is a new feature in Oracle 10g called flashback log file which is tasked with speeding up the slow processor of a point in time of database recovery. Another tracking weapon in Oracle is the log miner. This feature as said by (Freeman & Blonberg) redo logs that wished to be mined. A note should be made that a log miner can be used for tasks like data recovery, security review and audit and problem solving. This is just part of Oracle tracking systems devices that can be used to help in tracking and makes Oracle excellent if not the best for this purpose. These features are said to be special because they are only part of the solutions Oracle has risen to address tracking issues.
3.8 Conclusion
In conclusion to the above research which was an eye opener, adoption was taken to ensure proper management of databases to eliminate redundancy and proper tracking deployment. This can only be achieved if the below findings are successfully implemented. From the research above a lot was done to ensure that correct system is implemented and certain critical adoptions were made. The research showed that the first important tracking on Account number and password were critical thus has to be undertaken. This is critical in tracking records of users who log onto the system and could also be used to ensure that tracking events done by individual is traced. This makes management of password and user accounts manageable and in reality user update operation that are applied to the database can be traced back and any tempering could be traced back to the terminal attributed to the user account. The system log can be used to track down all activities that happened on the system. Also it should be noted that the use of Database audits are very important and are mainly used in the databases that are updated by many users simultaneously. A database log that is used for security is commonly known as Audit Trial log. For database transactions a special file called log or journal can be viewed. This file contains information about all updates. Another aspect that was visited was the deployment of the system on to the web and the web tracking and interestingly a lot was learned that for a web designed database to run successfully it should be listed that the critical challenge lies in the minimizing load times, minimizing query time, minimizing maintenance and administration and minimizing database design. The time it takes to load data will depend on the data you want to load, and the method you are using. Another method of tracking studied was Data Record Matching which is actually part of what the desired project will be focused on. The database should not duplicate already captured customers and updating them effectively what is needed and this method uniquely what the project requires. Commenting on the latest software it was realized that Microsoft SQL Server 2005 also has a new feature that could also be useful. This special feature is called Database Snapshot. This feature is used to create copies of the database for reporting on for creating a backup copy of the database. It should be noted that a snapshot is not a complete copy of the database and does not use a lot of space and can be considered for this project. Snapshots are an availability feature. Not a failover feature. Also there is Damaged page tracking that could be used for tracking. This feature allows restoration on a page-by-page basis while the database remains online. These features are a good start and a way to successful tracking databases.
From the above elaboration, Oracle seems all necessary and has what takes to track down the database. to be the idle solution as it could be easily tracked and there are quick solution tools that can be used quickly to recover both from situations and for easy manipulation of Database without altering or damaging the databases. Besides that there is quality in the monitoring process which helps in keeping the processor working to optimal pressure without slowing down or affecting the system.
4 Project Management Approach and Planning
4.1 Project Management Approach
Project management must reconcile two conflicting aspects of projects - the increasingly important need for speed in project delivery and the equally important need for reliability in delivering the project as promised. One way of thinking about how to deal with this conflict is to develop strategies to avoid expansion of project lead-time (Parkinson's Law) while protecting against Murphy's Law.
From the start of the project the project author decided on documenting an integral part of managing the project. The author opted on using the structured documents agreement between the author and the project sponsor to follow the steps that would help him develop the project in a desired way. These would be referred to as and when there is need to and also they can be used as reference to help mediate should conflict or disagreement arise between the sponsor and the author. This was outlined and agreed with the sponsor. A document was created by the author and the outline was drafted and agreed to by the project sponsor.
And as part of project enhancement and team building to ensure smooth running of the project the project sponsor elected a team that would work with him and the project author to ensure that their fulfillments are reached fully and to advice and input as and when they need to. Also meetings were scheduled between parties to come and appreciate the process.
4.2 Terms of Reference and Project Plan
Meetings were scheduled between the author and the sponsor, and Terms of Reference were agreed, documented and approved by both the project sponsor and the project Supervisor (See Appendix A). All meetings between the author and the sponsor were documented and later signed as approval by the sponsor from the first meeting were agreements were signed. It was agreed that all that is agreed on the meeting would be adopted for developments and employed as agreed in the meeting.
To mark the start and end of the project a project plan was developed (see Appendix B) and broken down into stages and each stage further broken down into further into tasks that would be done as per planned and timed. The project plan was developed using Microsoft Project plan software package and would be updated and correct as and when need arise. Timings were made available for planning and risk management at each phase of the development to ensure that risks were identified and to properly manage the project. The Gnatt chart would provide task projection and the critical path to project completion. Also the Gnatt chart would provide visible inter relations between tasks.
5 Analysis
As is in terms of Reference the project would be based on tracking databases, that would be used to input of records onto the database captured as they come. Also the database would be used to track down customer records and movement. This would make it easier for record information retrieval over telephonic conversation with customers as the database would provide means of searching records status and it’s where about as much as the estimated duration before it’s completion.
5.1 System Requirements Proposed
Meeting were held between the project sponsor and the Author on the proposed system and the agreement was reached that the system would be developed within the secure Bank network and a new Pentium iv p.c. would be used as server. The location of the server was identified within the network as seen in (Appendix C).
5.2 Constraints and Limitations
As identified on Terms of Reference the Database would need more space in future and there would probably be limitation on the speed of the machine to be used. Another problem would be the space that would be needed to test the database as this was not catered for.
5.3 Software
5.3.1. Database Development
Software chosen for the development of the project was Oracle 9i, and to create tables and triggers MySql were adopted. Oracle forms 6i were used to create forms and as earlier stated mySql were to be used to write codes to link the database with the forms.
In choosing Oracle 9i as opposed to Microsoft rival were Microsoft Access has no robust security and cannot produce snapshots. It has limitation compared to Oracle 9i. The high level security of Oracle and it’s compatibility with Microsoft Products made it easier to take it as the first choice. Oracle allows the setting of user access to table that a user is allowed to use and the also users have to use authentication to log onto the system. In general the security is very high. Also Oracle allows deployment of Database onto the Web which allows the use of database by multiple users on the web and gives companies a chance to link their branches online and use the database online.
5.3.2 Windows XP Pro
Windows XP Pro was used as the platform as it is stable and robust. It can be updated online with update from Microsoft with fixes also available online. Though there is a bad side with Windows being one on the systems prune to virus attacks now and then it is also good and true to say Microsoft Corporation is doing it’s best to get quick fixes in these cases. Using Microsoft Xp pro as the base or platform provides a good base for Oracle to sit on.
5.3.3 Crystal Reports
Crystal Reports was used to create reports. It is linked to Oracle 9i and generates reports that are linked to Oracle 9i and extracts the required information to produce reports that are desired. Crystal Reports was chosen because it is flexible and allows the author to design the reports in a required manners or design.
6. Software Analysis
All the developments were made on the author’s personal laptop and the files were later transferred to Server. This was an agreement between the Project Sponsor and the Author to give the author more time to work on the project at home. It was agreed that the Database be loaded to the main domain when it is up and running and should be on time for testing phase
6.1 Server Architecture
The Project sponsor and the author agreed to deploy the Database on the Bank’s secure network on the existing infrastructure to safeguard it from any intrusion. The Oracle Databases were to be connected by ODBC (Open Database Connectivity) connection. Also the connections for the Crystal reports were done in the same manner. The ODBC will provide an application programming interface (API) which would allow clients side programs to call the Database.
The PBM computer which will act as a server was allocated a space on the rack where it joins the rest of the servers on the server room. This was done for security purposes and to protect the database from unauthorized access and use. The server is well protected and has limited access to only authorized personnel.
For this reason the author had to work on a personal computer connected on the network, accessing the network from the personnel machine and working on the computer that acts as a database server. This was to comply with the bank standard. The project sponsors agreed to this to make testing easy. The computers used for testing would access the database over the network and to help reduce computer shortage.
7. Development & Design
7.1 Project Documentation
The objective of System design is to fulfill the requirement of the proposed system and to map them onto the components of the system. This would be followed by components design which is a presentation of how the system would look. This was done by diagram design aid using the relevant tools. An interface design was done of the system was also done which included the design of various databases on the system.
7.2 Design Concepts and Architectural Design
The architectural plan represents the structure of the program and data components that are necessary to enable the building of any computer based system. It shows the architectural style the system will take as well as the structure and properties of the component that will make up the system and how they relate. And for that purpose a three tier architecture was adopted. This method has client, server and database and the clients requests are sent to the server and the server in turn sends the request to the database. The database would respond by sending back the information or data required to the server which would reply back to the client.
Client Tier- Here the client sends a request to the Application server which in receives the request and process it by forwarding it to the next tier which is the database tier. The database will look for the requested information or data and sends it back to the application server which in turn send it back to the client to complete a client request cycle.
Components Design
7.2 Design Concepts
The user opens the database and has the option of choosing a menu to access. The available menus are User menu which are under the menu name Menus, Reports which is available for users to print reports. And also there is Search Db menu which allows users to search the database for customers. All these menus would take the user to check the database for the relevant information and send back a feet back which a user can decide what to do next.
Example of a scenario
Scenario: Search Db
Actor: User
Flow of Events: 1. user logs onto the Database with the correct password
2. Operational Menu opens with the option Input menu, Reports, and Search Db.
3. User opts and goes into Search Db and clicks to continue.
4. User enters Customers details and search the Database.
Exit condition: System retrieves the information for the requested customer.
A proposal of Database table was proposed and agreed on by the project sponsor and the Author and a draft of what is to be input into each database were proposed as above. Primary keys were set on all tables. The reports were excluded for the latter development as they depended on the success of database design as above.
7.3 Database Design
An analysis was carried out with reference to the client requirements and the functionality of the system will be provided for in the system.
- Adding new user onto the database which would be done by the end user with authorized input forms to introduce new customers. Once introduced into the system, they will stay there until they close their passbooks.
- Reports- The reports generation depends entirely on the third generation application creator called Crystal reports which would be used to write all reports as per Sponsor requirements with provision of creating more in future as the business might require.
- Searching customers- This functionality was to enable the user to check if the customers exist in the database before introduction to the system. This is to eliminate duplicates. This is mandatory though there is a code to cross check and give back an error message in case a customer exists. The rule is one account per customer and this is the field used to check the customer existence.
Normalization
Un-Normalized form
User_Name, Login, Password,
Account_no, Name, Surname, Origin,received_by, Purpose, Department, Received, Captured_By, Dispatch_Date, Dispatch_to, Dispatch_by, Next Audit, Audit start_date, Audit finish_Date, Done_by, checked_by, Department, Registration, Captured_by
Second Normal form Definition of Primary keys and Removing of Repeating Groups
CustomerDetails(Capturing):(Account_No, Name, Surname, Registration, Origin, Department, Captured_by)
Audit (Audit_Id, Audit_start_date, Audit_finish_date, Next Audit, Done_by)
Dispatch (Dispatch_No, Account_No, Dispatch_Date, Dispatch_to, Dispatch_by)
CapturingRecieve: ( Account_no,Origin, Department, Captured_by )
Log_on (User_Name, Password)
Third Normal form Removing functional Dependencies
CustomerDetails(Capturing):(Account_No, Name, Surname, Registration, Origin,Department, Captured_by)
Audit (Audit_Id, Audit_start_date, Audit_finish_date, Next Audit, Done_by)
Dispatch (Dispatch_No, Account_No, Dispatch_Date, Dispatch_to, Dispatch_by)
CapturingRecieve: ( Account_no,Origin, Department, Captured_by )
Log_on (User_Name, Password)
The Main menu is the first screen that appears appear when you click on the icon and a user have to click on click next to continue button to continue to the next screen.
The Navigation Menu –used to navigate all areas of the form by category and by Department. Every menu will take you to the Menu you select. If you want to Exit the Menu user can go straight to the exit button and exit. To go straight to reports you can do that and capturing is done on other menus.
The first main screen is the main menu. This screen gives users the option to select the menu they wish to access. They have the option of choosing from Reports, Menus and Search which is used to search the database. In case the user wants to close they can exit using the EXIT button.
The search screen is used to navigate, generate and eventually print report. This menu is accessible to all users to enable them to print reports. In case the user does not want to go to reports they click on the Go_Next button to progress to the next screen. The user can exit the program by using the Exit button.
The next Screen is for searching if the customer already exists in the database. And the search can be done in two ways, either by searching by customer account number or by customer name. If the customer does not exist in the database then he can be added by using the Add_new button that would take him to the add customer screen. Also the user can exit if he so wish to. Also it should be noted that these screen are to be used by the person who receives the Passbooks from the Banking Hall or from the Administration.
All customer information has o be captured as stipulated in the form. All details have to be filled. After filling all the relevant information the passbooks have to be saved by pressing Enter button. This will save the passbook and the user can then lock off the system as he will only have access to these menus only. At this point he has to take all captured passbooks to the Audit section for processing.
Here all captured passbooks will be entered again here to ensure that additional information is entered into the database and this is done for control purposes to ensure that passbooks have been received and where they originate within the bank. From there they will be filed by date and any auditor who takes will check the last batch that was taken and run a report to say he will be doing books received from this date to that date. This should an agreement between him and the Supervisor. This is for the supervisor to ensure he is in sink with the processes running in the department.
Also it has to be noted that the Auditors have to register a passbook every time before they start working on it. This will be done to ensure that during auditing of passbooks it becomes easier for the tracking process of knowing who is doing the passbook and when he started working on it. After completing the passbook it has to be registered as well to reflect the finish date. Once the finish date is entered the next Audit date should automatically slot it’s self without human intervention. And this would be used to print report the next year to extract books that would be coming for audit that month and letters can be sent to customers a month in advance. Also it should be noted that the Supervisor also have the right to this menu. This is for signoff after the passbook has been audited.
When the Supervisor is sure the audit for that passbook is done, the supervisor will sign off and send them to the right resource who receive them in the department and will open another form and capture as below. Then all captured passbooks can be sent to the right places for dispatch.
It should be noted that once the book has been captured on the system it can now only be maintained and when it comes for next audit it will only be updated. Each customer should only have one record in the system. This is to control redundancy and ensure that the database grows at a steady pace and at a controllable and maintainable speed as is in line with the vision of designing this program.
8 Testing
The Three phase approach of testing was adopted to ensure that all levels of testing are reached. The testing was to involve functional testing as well s to test the System to ensure that optimal results are obtain and perfection and correction be made. This testing was done not to check the system perfection or functional level alone but were done mainly to test find errors committed and for correction purposes. The adopted strategy was documented ( Appendix ) and approved by the project sponsor. The three testing phases were:
- System Testing
- Unit testing
- User Acceptance Testing
All test results were recorded on the developed testing form and any defects found were recorded on the Defect Identification form. The defects would then be corrected and the tests would be repeated until the results are positive and satisfactory.
8.1 System Testing
System testing was conducted on the system by the author and was commenced immediately after the completion of the unit testing. The aim of the system testing was to ensure that there was communication between the database system and other integrated system like Crystal Reports. The test was to ensure that all database open perfectly and in line with the agreement between the author and Project Sponsor. The tests and results performed were detailed in Appendix
8.2 Unit Testing
The purpose of this test was to ensure that any defect on each database was fully fixed before the databases are linked. This was done to ensure that defects are found and corrected at unit level to avoid fixing multiple and complex errors after linking pages.
Test scripts were designed which detailed a description of the test, expected results, actual results and if the test passed or failed.
Any change request that was made by the project Sponsor was attended provided that change management were filled and agreed on.
As part of unit testing accessibility test was done to test if the system connection were done and possible both locally and remotely.
8.3 Usability Testing and User Acceptance Testing (UAT)
The Usability test was done by the project Sponsor and four of his clients. It was done to evaluate if the proposed system were meeting their requirements. The author was present to help them when help was needed. The feed back correspondence from users was positive from the sponsors and the clients. More enhancements to the system were suggested by users like the use of advanced dashboards to show the passbook status when the passbook is searched for. The suggestions were mainly on reporting and almost all were resolved by the author to make sure that reports come out in the sponsors and clients’ desired format.
The above table illustrates the statistics of usability testing conducted. About ten users were involved in the usability testing and each of the respondent were given the usability testing questionnaire (Appendix ) to evaluate the database. The respondents were required to subjectively rate the database on ease of use.
9 Evaluation and Conclusion
Overall, the project has been a success. The objectives were met and the project deliverables were met taking approximately two hundred hours to complete.
9.1 System Evaluation
It is no doubt that the system created meets the requirements as defined in the agreed functional specification between the author and the Sponsors. The same sentiments were shared as well by the project sponsors clients who were testing the system as observed by the testing scripts submitted after the testing. Also submitted were additional comments and suggestions for improvement. The sponsor has hailed the project as a success and is willing to go further using the system and to improve it in future.
Although the project was a success the author still feels that there is room for improvement and new features could still be added to enhance the system. There is need to add dashboards as part of improvement. Also the author believes that the content and the user manuals need to be enhanced and every other change in the system need to be enhanced. It was the author’s wish to add more feature but the time set for the project was insufficient to allow that hence the implementation of the core functionalities. Also the author wished to have run the application on the Web to enable online running of the system which were hindered by lack of certain.
9.2 Process Evaluation
The author expressed gratitude at the communication between him and the project sponsor which were in the form of regular meetings to discuss the progress as well as the telephonic conversation and the e-mail to communicate. This has made it easier the project progress easier and manageable with discussions and action taken at the appropriate time. This made the project sponsor and the author comfortable and for them to appreciate their part and ownership of the project. Changes that affected the changes in the system production were always agreed on by both the author and the Project Sponsor and would be attended to immediately looking at the critical part of the change.
There were time constraints on the part of the sponsors at times because of the major changes that were done on the part of the sponsor’s network. This hinders the plan as the author was forced at times to continue on to another phase without the closure of another phase by the project sponsor.
The author believes that there could have done more tests if there was sufficient tome to do more tests.
9.3 Evaluation of Research
The two research conducted were very useful to the author as they have given the author time to know the insights of those topics and to increase his knowledge and significantly applied to the project. This knowledge would be used again in future endeavors.
9.4 Conclusion
The author gained a lot of experience and exposure to problems and problem solving. This brought about appreciation and importance of securing database as the author got a chance to apply his learning and findings to the project. The author has learned to apply the findings of a research in a project as well as to appreciate the importance of the research in any system development. Also the use of Crystal Reports by the Author has brought light to the author on how to use and appreciate the application as well as to write reports n Crystal Reports. This has given the author vast experience and wish to continue in the same for future reporting.
10 Recommendations for Future Work
Recommendations for future work include the purchase of a new Server that would run SQL Server 2003. Also the Database should be deployed on Web for easy assess by users and to allow on market functionalities that are supported by the Web to be added to enhance the Database. In addition the Database should be improved by buying Dash board system that could show the status. Also the system should be upgraded to the current Oracle 10g. Training should be done continuously to sensitize administrators on Oracle support as Oracle is new. Backup system also has to be implanted to avoid losing system crush. There should be a backup system for this purpose as the current system could not backup Oracle. Default dates should also pop up automatically and should be changed by adding a calendar to the relevant fields. Status bar should be added at the bottom of every page and the system should be able to report delays and overdue work and a blip sound should be added to remind the user about the delay.
