Part A:
Preliminary Field List
Employee ID
Employee First Name
Employee Last Name
Home Phone
Work Phone
Type of employee
Reporting Station
Physical work location
Current Salary
Old Salary
Payroll date
Net Pay
Expenses Reimbursed
Bonus or extra pay
Preliminary table list
Employee
Payroll
Description of methodology for refining the lists:
The process of refining the lists above will mainly involve two methods, which are using the list of subjects, and using the mission objective. The first method (using the list if subjects) will involve merging the list of subjects with the preliminary table list. In this case, the list of subjects can be drawn from interviews with users and the management aimed at determining the core subjects of the required system. The merging process will involve completing three critical steps involved in the design process. The first step involves resolving duplicate items in the two lists. This step involves thoroughly scrutinizing the list of subjects and preliminary table and determining whether duplicate items on the list represent the same or different subjects. If duplicates appear and represent the same subject then the item requires removal from the list of subjects. Otherwise, if they represent different subjects the item in the list of subjects requires renaming and addition to the preliminary table list.
The second step in the refining process will involve resolving items that represent the same subject. This will involve comparing the contents of the list of subjects and the preliminary table list. For items in both lists that represent the same subject it is important to select one name that accurately represents the subject. The final step will involve combining the two lists by adding all the remaining subjects from the list of subjects to the preliminary table lists.
The preliminary table list can also be refined using the mission objective. In the case of this project the mission statement is “Replace the existing manual system so as to be able to easily manage all information about our employees. The information will be supplied to and work in conjunction with the current payroll system, which does not contain any information about the employees.” Therefore, the refining process will involve deconstructing the mission objective statement above in an effort to identify the subjects contained in the statement. Once all subjects from the statement are identified, it is important to crosscheck them with the preliminary tables list. The three stage refining process discussed above will find use in further improving the preliminary table list. These stages include resolving duplicated items from the mission statement and preliminary table list, resolving items that represent the same subject, and combining the two lists.
The results of the process would be as follows:
Preliminary table list:
Employee
Payroll
Salary
The final list of fields and tables:
Employee Details Table:
This table will store all information relating to identifying information for all employees working at the organization. The fields within this table are:
Employee ID (Primary Key)
Employee First Name
Employee Last Name
Home Phone
Work Phone
End date for a different job/title
Type of employee
Reporting Station
Physical work location
This table will store identifying information for the different department within the organization within which an employee can work. The fields within this table are:
Payroll Information Table:
This table will store all payroll information relating to all employees within the organization. It will also help coordinate employee data with payroll. This will include the employee’s salary, reimbursed expenses, bonuses, and extra pay. The fields within this table are:
Payroll ID (Primary Key)
Employee ID (Foreign Key)
Payroll date
Current Salary
Expenses Reimbursed
Bonus or extra pay
Net Pay
Salary Information Table:
This table will store all salary information relating to a particular employee. It will help with tracking of the employee’s current salary, when to stop paying an old salary, and when to start paying a new salary. The fields within this table are:
Salary ID (Primary Key)
Employee ID (Foreign Key)
Current Salary
Old Salary
Salary Effective Date
Salary End Date
Resolve Multipart and Multivalued fields:
The Street Address field in the employee table is a multipart field. This can be broken down into three consistent fields, which are Address, City, and Postal Code. There are no multivalued fields in the table structure.
Normalize all tables to 3NF. Create linking tables (associative) when needed for breaking down the M:M relationships.
A M:M relationship exists between department and employees since employees can be posted to many departments. In order to eliminate this relationship a linking table (associative) will be needed. The name of the table will be Employee Department Posting Table and it will keep track of information relating to departments that the employee has worked for at the organization. The table will also keep track of the employees physical location and who he or she reports to while working at a particular department.
The normalized database is as shown below:
Employee Details Table:
Employee ID (Primary Key)
Employee First Name
Employee Last Name
Home Phone
Work Phone
Postal Code
Type of employee
Payroll Information Table:
Payroll ID (Primary Key)
Employee ID (Foreign Key)
Payroll date
Current Salary
Expenses Reimbursed
Bonus or extra pay
Net Pay
Salary Information Table:
Salary ID (Primary Key)
Employee ID (Foreign Key)
Current Salary
Old Salary
Salary Effective Date
Salary End Date
Employee Department Posting Table:
Posting ID (Primary Key)
Employee ID (Foreign Key)
End Date
Reporting Station
Physical Work Location
The logical database schema (ERD) using MS Visio
Figure 1: Logical Database Schema (ERD) developed using Ms. Visio
Part B:
Working in teams is an enriching experience that led to a lot of learning. The main reason for this is that team members provide each other with knowledge and information relating to the problem under analysis. Working in a team also helped in building confidence and encouraged cooperation between colleagues. These are important skills in everyday life, as well as, in the workplace. However, despite these advantages, numerous issues arose while working in a team. The main issues that arose the lack of cooperation due to individuals having different ideas and solutions to the problems presented, which in turn led to conflicts within the team. This greatly delayed the group’s efforts in the beginning, as the group could not agree fully on the solution to adopt. Communication was also a major issue facing the team. Ineffective communication was also a major issue facing the group. This was resolved by examining the issues affecting communication and cooperation and developing solutions that ensured that every group member’s views were taken into consideration.
