Entity Relationship Diagrams for IT Projects Essay

Exclusively available on Available only on

Table of Contents

Introduction
Background of the case
Proposed Solution
References

Introduction

Entity Relationship Diagrams (ERD) are used to store structured data in a database. This paper provides an ERD for an IT project by giving the case background, requirements, various relationships and the actual diagram for a real-life application.

Database: Oracle 8
ER Tool: SmartDraw (SmartDraw, 2007)

Background of the case

XYZ Ltd. employs 625 sales personnel who incur expenses for work-related travel and entertainment. The expenses may go up to a few hundred dollars a day and these expenses and salespeople were required to submit their expense statement along with supporting documents every now and then. Expenses were reimbursed on the 20th of each month after the data entry team verified the statements and cleared the payment (Chian-Son Yu, 2005).

This has placed a financial burden as expenses for a whole month had to be borne by the sales personnel. If the salesperson missed submitting the documents on the 20th for any reason, then payment would be delayed till the next 20th. Since the company reimburses only the principal amount, the interests for the pending amount had to be borne by the sales personnel.

The strength of the system is that salespeople displayed more sincerity and honesty and while a small amount of padding of the expenses may have been done, there were no large-scale frauds. The system had imposed self-honesty among the sales personnel.

The weakness is that the system caused a months delay in reimbursement of the expenses and this caused a financial loss for the sales personnel. In addition, the work of data entry teams seems to be substantial, as they have to enter the statement details of 625 sales personnel on almost a daily basis. This factor alone may be the cause of substantial delays besides large manpower requirements in the form of data entry operators, account people who would verify the entries and so on.

Proposed Solution

A new computerised system is proposed as a web-based system that allows sales personnel to enter details of the expenses incurred. The following sections provide more details of how the system would work. Since this is web-based, personnel can access it from any part of the country or the world.

In the new system, employees would be identified by their employee number and this will be the primary key. Along with the employee other relevant details such as name, department to which the person belongs, grade and designation, etc. can be viewed. The system can also be integrated into any attendance registration or human resources system that the company has. The ER diagram is illustrated in Figure 1.

Figure 1. ER Diagram of Web-Based Expenses Management Application (Naughton Patrick)

The following table provides information about the entity, relationship and attribute.

Table 1. The information about the entity, relationship and attribute.

Entity	Relationship	Attribute
Salesman	employee_ID: Primary key gives the employee number. Alphanumeric bank_acnt_numbr: Primary key gives the bank code and the account number in which the salary would be credited employee_name: Gives name of the employee. budget_center: Gives the department for which the employee works. Budget allocations, expenses, salary deductions will be done for this budget centre designation: Gives designation of the employee. Amount of expenses such as by taxi, by plane, grade of hotel, etc. depend on the designation	fill_statement: Employee fills the bank statement that gives details of expenses incurred.
Expense Statement	statement_ID: Primary key, alphanumeric and system generated. Used to identity and track the expense statement date: Date on which statement was filed amnt_currency: Amount of expense filled by the employee
Supervisor	employee_ID: Primary key gives the employee number. Alphanumeric employee_name: Gives the name of the employee. budget_center: Gives the department for which the employee works. Budget allocations, expenses, salary deductions will be done for this budget centre designation: Gives designation of the employee. Supervisors of different designations can authorize different amounts	verify_statement: Verifies the statement submitted by the employee. Accounts can take the statement only after the supervisor has verified it.
Account	budget_center emply_id: Primary Key designation employee_name statement_ID: Primary Key bank_acnt_numbr: Primary Key	verify_statement: Verifies the expense statement and authorizes payment. Payment is released to the bank_acnt_numbrand emply_idfor the specific statement _ID.

Each user would be assigned a user name and password and users need to log in with their details to access their accounts in the system. The system would allow users to enter details of their expense statements, view details of previous paid and pending bills and see the status of each. If a bill has been rejected for any reason, then they can see the reason for rejection.

For security reasons, once the details are entered and submitted, they cannot be altered by a user but only by the supervisor. Details of previous records can also be accessed as per the months in which they have been paid. These will be displayed as read-only and editing should not be allowed.

User can open a new record and enter details of a new statement. A common format is suggested for all entries. Details such as the date of the claim, expense details, amount of expense incurred, etc. can be entered by the user. Once the entry is done, then the user needs to click the submit button. With one click of the submit button, a record number is generated. This record number refers to the expense statement made by the salesperson and the date on which the statement was made. The accounts department, salesperson and supervisor can use this record number to track the statement.

After the submit button has been clicked by the user, then a record number is generated. The user needs to print the statement made online along with the record number, attach bills, receipts and other proofs and send them to the accounts department.

The sales supervisor and manager need to access the system using their own user name and password. Supervisors and managers will have certain rights such as the amount they can approve, changing the status, declining to pay and authorising payment. In addition to filling their own statement, they also need to approve the pending statements of their subordinates. When they log in, a prompt will tell them the number of pending statements that the supervisor needs to clear. If for any reason, they have to reject a statement, then they can choose the reason for rejection from a drop-down box.

An escalation feature is built into the system. As per this system, if a statement is pending for more than 48 hours with any supervisor or accounts person, then the matter is escalated to the next immediate supervisor and so on. This will ensure that no bill gets held up in the system just because of lack of time or any other reason.

Only after the supervisor or sales manager has authorised the payment can the accounts department process the statement. They can however read the statement online and convey any disparities or non-conformance as per the accounting procedures.

Hard copy printing of the cheque requires extra manpower, strict controls, delays

in the signature of authorising personnel, etc. It is proposed in the system that the approved amount be directly credited to the salary bank account of the sales personnel. As a contingency, it is also proposed that there be a facility to generate cheques also if needed. This will require controlled cheque stationary, secure computer and authorised personnel who track the cheques.

References

Chian-Son Yu, 2005, Causes influencing the effectiveness of the post-implementation ERP system, Journal of Industrial Management & Data Systems, Vol. 105, No. 1, pp. 115-132
Hoffer, Prescott, McFadden, Modern Database Management 8th Ed
SmartDraw, 2007, Create Entity Relationship Diagrams. Web.
Naughton Patrick, Schildt Herbert, 1999, Java 2, The Complete Reference. McGraw Hill Publications. ISBN 0-07-463769-X

More related papers Related Essay Examples