Online Project- System Analysis/ Design

Data Models

Data modeling was established because DBMS (Database Management Systems) and data needed to be specifically structured in file systems that enabled better control and handling of information and data sets. A data model therefore can be defined as concept sets that describe operations carried on and structure of information systems and databases (Navathe, 1992). One of the practical data modeling purposes is to serve as a blueprint for the physical intended database. It also helps in implementation of the system’s information access level impacting strongly on modifiability and overall performance.

The activity of data modeling has become common in the process of developing information systems which implement database management technology to store information and sensitive data. A data model is the result of graphically represented data structures and interactions as well as their relationships (Merson, 2009). Information systems’ data models hold vital architectural information and they provide the following useful functions:

· Providing a database structure creation blueprints

· Facilitating the inclusion of stakeholders in analysis and collecting specific requirements

· Guide the actual coding of the database including setting parameters for queries and database access.

· Providing the objects’ relationships and conceptual descriptions

· Serving as input for the automatic production of records access codes and schemas for the database

Distinguishing between the actual database and the database description is very important in any application’s development. This description of databases is designed by analyzing requirements for users and applications and it is referred to as Schema. Schemas sometimes undergo model to model transformation during the database design process. The whole activity involving producing definitions from requirements to the implementable final DBMS database schema is called schema design (Navathe, 1992).

Fig. 1 A conceptual database schema design diagram

Fig. 2 An example of network database schema

Purpose for Data Modeling

Data models are documentations and formalizations of events and processes that exist during the design and development of applications. Analysis and planning should precede the process of data modeling. Determining database requirements is done through analysis while planning defines the intended objectives of the database, their importance and setting a path to achieve the set goals (Mamčenko, 2004).

End user data requirements are accurately and completely represented by effective data models which should be simple and detailed enough for database designers to implement and end users to understand. The data modeling process is bottom up and basically independent of both software and hardware.

Data Modeling Scope

The scope of data models has been limited to schema design of databases in the conventional sense and they have been for modeling data static properties. Data modeling simply illustrates data entity structures and the relationships involved between them. In an information system, the different entities dictate the architectural design. Relationships may determine that a single employee can have more than one connection to identifying attributes. In our case with the payroll system, it will look like this;

Fig. 3 Payroll System Context Diagram

Database Design and Data Modeling

Designing databases requires the combined use of function models and data models in the abstract design procedure. Data models emphasize on database storage while function models will deal with ways to process the data (Hall, 2011). Relational tables in databases are developed from data models while on the other side; queries to control operations and access of data in the relational tables are designed using function models.

Analyzing Requirements

The main purpose for carrying an analysis is to;

· Determine executable transactions and the interaction between transactions and data in the database

· Determine primitive objects’ data requirements

· Identify data integrity governing rules

· Describe and classify information on primitive objects

· Classify and categorize the objects’ relationships.

Data Modeling Abstraction Levels

The system development cycle involves a number of abstraction levels ranging from defining classes, modeling formal requirements to use case modeling. A three step approach to database design and modeling that has been established well throughout the years includes three levels. These core stages of database design are;

a) Conceptual Level – the basic data entities and their relationships are documented here.

b) Logic Level – here details of the implementation of the entities and the specified relationships are omitted while clearly defining both.

c) Physical Level – the database structure is defined here.

PAYROLL SYSTEM

A company’s payroll system can be equated to a special purchase system where the item on sale is labor from employees. The following reasons however explain the need for payroll systems to have specialized measures. They include;

a) Procedures used in payroll systems differ from one class of employee to the other. This simply means employee classes could range from permanent and pensionable, hourly or part-time, commissioned to piece work. The other processing requirement is taxation.

b) Unlike activities usually involved in general expenditure, businesses keep payroll activities and procedures as discrete as possible.

c) Special control for writing employee checks is required and payroll fraud is easy when both normal trading transactions are combined with managing payroll systems.

The figure below simplifies the payroll system to two logical steps. The key processes and relationships are shown.

The employees can have several attributes defining and distinguishing each employee to separate and accurately identify each employee as a single entity. An example is illustrated below.

Fig. 5 Employee Context diagram

The payroll process can also on its own be expanded to;

Fig. 6 Payroll System level 0 DFD

Where;

· Process 1 (p1) – this is where different employee time cards are collected and stored in the TCF (Time Card File). This includes the total hours each employee works.

· Process 2 (p2) – the employee records are accessed to retrieve EHWRs (Employee Hourly Wage Rates) corresponding to each ETC (Employee Time Card) data and Gross Pay is calculated. The formula used in the Gross Pay calculating process is usually

· GP (Gross Pay) = Hours Worked * Hourly Wage Rate

· Process 3 (p3) – the input to this process includes the gross pay passed from p2 and tax rates from the TRT (Tax Rate Tables) file. These inputs are used to produce among other things, the final net pay.

· Process 4 (p4) – the resulting payroll data from p3 is updated into the employee’s file by p4 which it does using SPD (Summary Payroll Data) to finally produce employee pay checks and the related pay stubs.

Batch Systems and Data Processing

Due to the bulk involved in the simultaneous processes taking place in the payroll system, it has become mandatory to use batch processing in almost all sized business organizations. Many businesses rely heavily on data processing to run daily operations like account maintenance, invoicing and paying salaries. Batch processing systems input data and output information in batches in between databases and storage files. The results are then either prepared for storage in the database or computed for printing as shown below.

Fig. 7 Batch data processing system

For our proposed payroll system, the batch processing of data is illustrated as;

With varying procedures in various payroll systems, the proposed payroll system’s general errands will include the following described major components (Hall, 2011).

i) Department of Personnel – this is where processed personnel action forms are submitted after preparation onto the function called “prepare payroll”. These PAFs (Personnel Action Forms) are used to identify and determine valid employees, deductions in payroll, changes in rates per hour as well as job group or classification.

ii) Department of Production - here two time records are prepared: time cards which capture the employee’s time at work used to calculate their paychecks and job tickets which capture time spent on production jobs by each employee. These documents are implemented by cost accounting for direct labor allocation charges to accounts called WIP (Work In Process).

iii) WIP Account Update – the LDS (Labor Distribution Summary) which is basically a summary of labor costs generated by cost accounting and allocated to the WIP accounts, is passed to the GL (General Ledger) function.

iv) Payroll Preparation – the department of production sends data about hours worked for each employee while the department of personnel sends taxation and pay rate data to the payroll department. A clerk working in the payroll department therefore performs the following;

a. Payroll register preparation which shows deductions, gross pay, overtime pay and lastly the net pay.

b. The clerk then submits the above information into the EPR (Employee Payroll Records).

c. Preparation of pay checks for employees

d. Sending pay checks to the function distributing paychecks.

e. Filing a payroll register copy, personal action forms and time cards.

v) Paycheck Distribution – many companies employ a pay master who is independent of both preparation and authorization of paychecks and is used to distribute paychecks. This prevents fraud in payroll by submitting nonexistent employees’ time cards. All unclaimed paychecks by valid employees are taken back to payroll for further investigations.

vi) Accounts Payable preparation – the clerk in charge reviews payroll registers and prepares vouchers to pay out funds for the payroll’s amount once confirmed for correctness. The voucher is then recorded into the voucher register after which the combined packet (payroll register and voucher) is submitted to funds disbursement. The general ledger also receives a duplicate of the payout voucher.

vii) Cash Disbursement Preparation – when the cash disbursement function receives the voucher packet, it combines the whole payroll pay outs into one amount which it deposits into the PIA (Payroll Imprest Account). A journal voucher is finally prepared and passed on to the GL (General Ledger) function.

The General ledger is then updated using information from Accounts Payable’s payment voucher, the journal voucher and summaries of labor distribution from cost accounting. The above information can be modeled into the following design when implemented (Hall, 2011).

Fig. 9 Payroll Procedures Data Flow Diagram

PHYSICAL PAYROLL SYSTEM

Manual Payroll System

The above procedures can be represented in manual system context thereby providing the following clarifications;

· Two different sources send information on hours worked by employees and authorization of payrolls to the payroll department. They are production and personnel.

· Distribution of paychecks by the payroll department to the employees after reconciling the information received above and calculates payrolls.

· Information about time spent from production on each job is sent to cost accounting and posted to WIP (Work In Process) accounts.

Fig. 10 Manual payroll system

Fig. 11 a continuation of the Manual payroll system

Computer Based Payroll Systems

A payroll system is usually automated using batch data processing because it is run periodically on a monthly or a weekly basis. A hard copy of the job tickets, personnel action forms and job cards is received and digitized for batch computers applications to perform checks on thorough record storage, GL functions and check writing. Figure 8 shows a detailed batch processing computer based payroll systems (Hall, 2011).

Fig. 12 Payroll System Procedures showing real-time elements

UNDERSTANDING PAYLINES AND PAYSHEETS

Paylines

A payline consists of:

· Employee name.

· Benefit record number.

· Employee ID.

· Line number.

· Manual check indicator.

· Employment record number (used for multiple jobs).

Paylines are associated with one or sometimes more PE (Pay Earnings) records. Each PE record associated with a payline includes the information below:

· Regular pay amount.

· Number of ordinary hours.

· Supplementary pay.

· Additional income.

· Information on Taxation.

· Job data such as job code and department.

Note: every employee listed for pay during a pay phase must have at least one Pay Earnings record and one payline. Otherwise the system will have no data with which to compute salary, taxes, and subtractions.

Paysheets

A paysheet brings together different information from the system and when accessed it displays a payline for every employee are listed. Paysheets are arranged by lines and pages and every member of staff appears on a different line that encloses regular compensation information which include regular working hours, job data and regular salary.

Summary

In conclusion we can revisit the data modeling concept as the first step in database design and creation. Information collected during the analysis of the requirements is used to develop the initial models with the objective of classifying and identifying data objects together with their relationships. Database schemas are then developed from the successful definition of all entities and how they interact in numerous processes. Data modeling is vital for any development of information systems especially when they are database driven which constitutes many of the common implemented systems in modern data processing organizations (Mamčenko, 2004).

References

Hall, J. A. (2011). Accounting informat ion systems. (4 ed.). Mason, OH: South-West Cengage Learning.

Mamčenko, J. (2004). Introduction to data modeling and msaccess. Understandig of models and system of information resourses,.

Merson, P. (2009). Data model as an architectural view. Research, Technology, and System Solutions, doi: CMU/SEI-2009-TN-024

Navathe, S. B. (1992). Evolution of data modeling for databases. COMMUNICATIONS OF THE ACM, 35(9), 112. Retrieved from http://www.cc.gatech.edu/~sham/classpapers/p112-navathe.pdf

Project

Requirements Collection and Analysis

Database Requirements

Conceptual Design

Conceptual Schema

(In a high-level data model)

DBMS - Independent

Logical Design

Conceptual Schema

(In the data model of a specific DBMS)

DBMS - Specific

Physical Design

Internal Schema

(For the same DBMS)

Fig. 4 Payroll System and Employee Context diagram

Fig. 8 Payroll Batch processing diagram