Prerequisites

Before starting this milestone, the following topics should be covered:

Data modeling Chapters 8 and 14.

Milestone 1-3 Solutions (provided by your instructor)

Assignment

Now that we have studied the current system and analyzed some of its problems and opportunities, plus gained approval to proceed, we can now start to identify the business data requirements and graphically model them. In this assignment, we will use our results of the previous milestones, samples of forms we have collected and the preliminary data analysis provided below. The results of this activity will identify the business data requirements for the proposed system.

Activities

1. Complete an Entity Definition Matrix. Analyze each of the forms from the previous milestone and the preliminary data analysis provided below and make assumptions where necessary. (Note: While it is appropriate to make assumptions, you should document those assumptions and include them in your submission to your instructor.) If you create the matrix in Excel, paste it into a Word document prior to submission.
2. Complete the Visio tutorial which walks you through how to create an Entity

Relationship Diagram (ERD), located in Doc Sharing.

When you have completed the tutorial, paste the diagram you have created to the end

of the Word document that already holds your Entity Definition Matrix.

3. Prepare a Context Data Model.

Deliverable format and software to be used are according to your instructors specifications. Deliverables should be neatly packaged in a binder, separated with a tab divider labeled Milestone 4-Part I.

References

Milestone 1-3 Solutions - provided by your instructor

Case Study Introduction

Sample forms from the previous milestone.

Preliminary data analysis (see below)

Preliminary Data Analysis

Preliminary Data Analysis Requirements:

Additional data attributes have been bolded and italicized. This is not a complete list of all data requirements.

Attribute Name	Attribute Description
Employee ID	Five-digit, alphanumeric, unique code assigned to every employee for identification purposes.
Employee Name	Field consisting of an employees last, first, and middle names.
Employee Home Address	Field consisting of an employees street address, city, state, and zip code.
Employee Home Phone	10-digit numeric field consisting of an employees home telephone number.
Employee Social Security Number	Nine-digit numeric field unique to each employee.
Employee Birth Date	Eight-digit numeric field in MM/DD/YYYY format consisting of an employees date of birth.
Employee Marital Status	One-digit field indicating an employees marital status. Possible values are M for married, and S for single
Employee Job Code	10-digit alphanumeric field that relates to the employees current job title. Staffing department maintains a standard table of Job Codes, related Job Titles, and permissible salary range for each job title.
Employee Job Title	Alphanumeric field that consists of the employees current job title.
Employee Dept. Number	Four-digit numeric field indicating to what department the employee is assigned. Staffing department maintains a standard table of Department Numbers, Department Names, and Employee Number of the individual who is manager of the department.
Employee Dept. Name	Alphanumeric field indicating the department name of where the employee is assigned.
Employee Dept. Manager Number	Employee number of the individual who is the manager of the department.
Employee Current Annual Salary	Nine-digit field that includes two decimal points, consisting of a full-time employees annual salary.
Employee Hourly Rate	Six-digit field that includes two decimal points, consisting of a part-time employees hourly rate.
Number of Hours Scheduled	Two-digit numeric field consisting of the number of hours a part-time employee is scheduled to work per week.
Employee Work Phone	10-digit numeric field consisting of an employees work telephone number.
Employee Work Site	Three-digit alphanumeric field consisting of an employees work site. Valid values are ORL, MAR, VF, SUN, DEN.
Employee Work Building	Four-digit alphanumeric field consisting of the code for the building an employee works in.
Employee Work Room	Four-digit alphanumeric field consisting of an employees office or cubicle number.
Employee Work Mail Stop	Four-digit alphanumeric field consisting of the location where an employees mail is to be delivered.
Employee Status	One-digit alphanumeric field consisting of the employees current employment status. Valid codes are A for active, R for retired, L for on leave of absence, and F for former employee.
Employee Seniority Date	Eight-digit numeric field in MM/DD/YYYY format consisting of an employees first date of hire.
Employee Employment Activity Code	One-digit alphanumeric field indicating the type of employee employment activity. Valid codes are H for hire, T for termination, L for on leave of absence, R for return from leave of absence, and P for promotion.
Employee Employment Activity Date	Eight-digit numeric field in MM/DD/YYYY format consisting of the date reflecting the employment activity.
Employee Employment Activity Reason	Alphanumeric free-form field consisting of the reason for the employment activity.
Employee United Way Contribution Date	Eight-digit numeric field in MM/DD/YYYY format consisting of the date of the United Way campaign.
Employee United Way Contribution Code	One-digit alphanumeric field indicating the type of deduction. Valid codes are F for fair share, O for one time gift, T for specific payroll amount, and N for nonparticipating.
Employee United Way Contribution Amount	Seven-digit field that includes two decimal points, consisting of the amount to be contributed. For those who selected fair share, multiply annual salary by 5 percent.
Employee Savings Bonds Contribution Date	Eight-digit numeric field in MM/DD/YYYY format consisting of the date of the Savings Bonds campaign.
Employee Savings Bonds Deduction Type Code	One-digit alphanumeric field indicating the type of deduction. Valid codes are O for one deduction and R for repeating payroll deduction.
Employee Savings Bonds Deduction Amount	Seven-digit field that includes two decimal points, consisting of the amount to be deducted from payroll period.
Savings Bond Denomination Code	One-digit alphanumeric field indicating the amount of bond. Valid codes are 1 for $100, 2 for $200, 3 for $500, and 4 for $1,000.
Bond Owner Name	Field consisting of the bond owners last, first, and middle names.
Bond Owner Home Address	Field consisting of the bond owners street address, city, state, and zip code.
Bond Owner Social Security Number	Nine-digit numeric field unique to bond owner.
Employee Relationship to Owner	One-digit alphanumeric field indicating the relationship to bond owner. Valid codes are S for self, C for co-owner, and B for beneficiary.
Primary Contact Name	Field consisting of the primary contacts last, first, and middle names.
Primary Contact Home Address	Field consisting of the primary contacts street address, city, state, and zip code.
Primary Contact Home Phone	10-digit numeric field consisting of the primary contacts home telephone number.
Primary Contact Work Phone	10-digit numeric field consisting of the primary contacts work telephone number.
Primary Contact Relationship to Employee	Alphanumeric field consisting of the relationship to the employee.
Secondary Contact Name	Field consisting of the secondary contacts last, first, and middle names.
Secondary Contact Home Address	Field consisting of the secondary contacts street address, city, state, and zip code.
Secondary Contact Home Phone	10-digit numeric field consisting of the secondary contacts home telephone number.
Secondary Contact Work Phone	10-digit numeric field consisting of the secondary contacts work telephone number.
Secondary Contact Relationship to Employee	Alphanumeric field consisting of the relationship to the employee.