COMM & BUSI 335 | Transaction Processing Assignment Case | Complete Solution

COMM & BUSI 335 – Summer 2015 TPS Assignment Case

This document provides you with details of the transaction processing system that you will implement in Microsoft Access 2010. Refer to the Access tutorial notes for more information on specific Microsoft Access skills.

Introduction
The purpose of this assignment is to enable you to gain some hands-on experience in developing a simple transaction processing system. The assignment itself is structured as a case study: you are provided with a simple scenario and the domain constraints of the system. Note that the scenario and constraints are constructed for the purpose of this course (e.g., to demonstrate several principles in one case study) and might be different from what you know in real life. It is, therefore, unacceptable to change the information given in the case study when you do the assignments. Your task then is to create a transaction processing system (spread across three distinct phases) that meets the daily operational needs of users and create the necessary summaries and reports required by the users.
Very Important Preamble
Remember that this assignment is spread over three distinct phases (3 weeks). Like a new home builder you must be very, very patient. In the first phase of construction, your new home is just a very large hole in the ground. Many features actually only work a few days before the keys are handed over. Some of you may, at the beginning, already be very frustrated by the fact that your system does almost nothing – this is not surprising.
Software application systems are actually very complex. Many of the systems that you and I use every day (and may take for granted) are actually very meticulously programmed – a fragment at a time. Each little piece is carefully and independently built and tested and then “assembled” and “tested” before the system is delivered. The developers work very hard and very long hours to make it not only work well but also easy to use as well – so easy, that we might think it is equally easy to build. Many students think computers should work “automatically” but there is nothing that is automatic in this world! Everything is your hard work.
We actually cannot even come close to developing an ‘industrial-strength’ system in the short time that we have. So we are going to take a very, very naïve and simplistic view of the credit card information system for this assignment. This is a lesson in itself meaning you will see the role of the DBMS and the role of the associated tools together as they strive to provide the company and her employees with the services they need.

The Case
Introduction
Maple Leaf Energy (MLE) is a large utility company serving customers in the Pacific North West region. As part of its employee benefits package, MLE in cooperation with a small number of investment firms had decided to offer its (MLE) employees a one-of-a-kind pension plan.
This pension plan is special in that employees can log in to their account at any time and make changes to their portfolio (e.g., they can shift from say, the Bond Fund to the Equity Fund if they think it will do better). We might therefore describe it as a “self-service” investment (pension) plan where MLE’s employees make their own decisions and exercise their own options about their pension plan investments.
Unions and Pension Plans
Employees of MLE (hereafter called employees) are individuals directly employed by the company – contract workers are exempt. Each employee is represented by a union. No employee may be represented by more than one union. You may assume that all employees of MLE are represented by one of several unions active at MLE. All employees earn a monthly salary and are expected to name a single beneficiary for their accounts in the event the employee passes away.
There are a number of different unions representing the different job classes at MLE. Each union negotiates a different contract (and therefore pension plan) for the employees they represent. For example, the TWU union representing technical workers may have secured a 12%, ‘50-50’ pension plan. This means that the employee contributes 6% of his/her salary into the plan and MLE will match the same amount of money. Employees belonging to the WCU union (representing white collar workers), on the other hand may have a 15%, ‘60-40’ contribution plan. This means that the employee contributes 9% (15% * 0.6) while MLE will contribute 6% (15% * 0.4) of the employee’s salary into his/her plan. For the purpose of this course, please only store the employer’s and employee’s percentage contributions in the database. For example, in the WCU union case, only store 9% and 6% for employee’s and employer’s contributions, respectively (i.e., do not worry about 15% and 60-40). Contributions may only be made through payroll deductions.
The total contribution (both the employee and MLE) for each employee, each year, however, cannot exceed the maximum amount specified by the Canadian Federal Government for that year. For the purpose of this course, please do not worry about any other types of upper limits or restrictions other than those mentioned in the case.

Investment and Fund Types
The pension plan allows employees to invest among a number of different Fund Types (e.g., Balance Fund, Equity Fund, and Bond Fund). For the purpose of this course, please assume that each Fund Type is managed by only one investment firm. But each investment firm may handle more than one Fund Type. For the purpose of this project, treat investment firm as an attribute (i.e., you do not need to create a table for investment firm as your database is not required to store information other than their name).
COMM & BUSI 335 – Summer 2015 TPS Assignment Case Page 3 of 4
© 2014 Y.M. Cheung, W. Tan, C. Woo, adapted by W.Dorfmann
Naturally investment firms charge a monthly fee for their service (taken directly from the plan contribution) but they are able to capitalize on economies and expertise not normally available in individual investors. For the purpose of this course, there is only one fee for each fund type (i.e., don’t worry about situations like a fee increase).

Employees can use their contributions (and MLE’s matching amount of course) to purchase any Fund Type for their pension plan. However, to make things easy, an employee must decide upfront, by percentage, what proportion of their monthly contribution they would like invested in a particular Fund Type. For example, an employee may decide to put ten percent of his/her savings in Bond Fund, forty percent in Equity Fund, and fifty percent in Balance Fund. All apportionment by the employee must add up to a hundred percent. These percentages are used ONLY for the purpose of calculating the monthly contribution. It does NOT imply that the entire investment amount an employee has with the pension plan must add up to these proportions at any time.
A special feature of MLE’s plan, as mentioned at the beginning of this case, is the ability of employees to move funds from one Fund Type to another on their own by simply logging into their account, specifying the amount of money, identifying the Fund Type to move this amount of money from and the Fund Type to move the money to. For the purpose of this course, please assume that there is no fee for moving funds, and money will be transferred on the same day as the request. Each month, all pension plan accounts will ALSO track:

1. Contribution amount by the employee.
2. Contribution amount by MLE.
3. Income from investments (may be negative).
4. Administrative expenses.
5. Date each activity takes place.
Information Requirements (Summary)

MLE pension plan accounts need to store and manage the following information:
1. All current contributing employees of MLE. For each employee, his/her beneficiary, date hired, date terminated (don’t worry about how this will be determined), and monthly salary.
2. The various unions active at MLE and the contribution plan details they had negotiated with MLE.
3. The various types of transactions it needs to process e.g. withdrawal, deposit (contributions), administrative fees, income, etc.
4. The various Fund Types participating in the pension plan investment program and their corresponding investment firm name.
5. MLE and her employees must also know the status of their accounts including balances with each Fund Type. They further need a list of transactions including a detailed description of the transaction including dates and amounts involved.
6. The maximum amount of pension contribution allowed for each year.

For the purpose of this project, please do not worry about recording the history of changes other than those transactions mentioned in point 3 above. For example, there is only one monthly salary for each employee. If an employee got a new salary, the old one will be deleted. The same is true for the union an employee belongs to, his/her beneficiary, and so on.
Assignment Package
A sample employee list, fund type list, and union list are currently stored in an excel workbook. That file will be available for download at a later date.
FOR THIS PHASE YOU NEED NOT ADD DATA BUT ARE FREE TO DO SO TO TEST OR VISUALIZE YOUR DESIGN.

Requirements
You are required to create a transaction processing system with the functionality described above. At this time you are NOT required to think about the interface – just focus on creating a set of tables and relationships that best serve the above purpose. For detailed requirements at each phase, please consult the Phase Requirement Handout available at the beginning of each phase including Phase 1.
Terminology Used In This Case
Employee: Any individual working for MLE that is eligible for their pension plan. This person is also required to be a member of a union active at MLE.
Union: An organized group of employees at MLE. There is more than one union at MLE. Each union “specializes” in serving the needs of a particular group or class of employees at MLE e.g. clerical workers.
Fund Type: A way to group investments according to risk and growth opportunity or according to categories such as industry (e.g., energy or financials, etc.). These groupings are identified in this document as Fund Types. Fund Types make it easy for clients (in other words employees) to invest according to their needs and beliefs.
End of document.