C# Programming Platform II Midterm
Create an Employee Information System for the Great Corporation. This corporation produces software systems that regulate the production of baked goods. In
this corporation you will find the following jobs: programmer associate, programmer, sales associate, sales manager, and then president.
In developing your system, you will need a SQL Express database that stores the appropriate data for your employees. For sales associate also hold a bonus
percent and a dollar amount for the sales made by that associate during a sales time period. For Sales Manager hold a similar percent but his/her bonus is based
upon total sales for his/her division (i.e. his/her sales amount is the sum of the other sales people data). Sales Manager, Sales Associate and President also have
a car allowance field that would hold a monthly dollar amount for the use of a car.
To test your database, use the following data:
ID Last Name First Name Job Title XXXXXXXXXXDOB Hire Date Monthly Salary XXXXXXXXXXSales Amt Bonus Rate (%) Car Allowance
101 Gates William President XXXXXXXXXX/03/1984 02/27/1999 $ XXXXXXXXXX,300 XXXXXXXXXX5 $ 2,400
102 Garcia Estella Sales Manager XXXXXXXXXX/11/1997 04/07/ XXXXXXXXXX, XXXXXXXXXX XXXXXXXXXX600
XXXXXXXXXXGoode April Programmer XXXXXXXXXX/11/1989 10/12/ XXXXXXXXXX,900
203 Simpson Homer Sales Associate XXXXXXXXXX/05/1987 01/29/ XXXXXXXXXX,600 $4,500 XXXXXXXXXX15 XXXXXXXXXX400
202 Chan Henry Sales Associate XXXXXXXXXX/27/1994 01/29/ XXXXXXXXXX, XXXXXXXXXX,980 XXXXXXXXXX XXXXXXXXXX400
297 Jackson Shirley Sales Associate XXXXXXXXXX/15/1988 11/18/ XXXXXXXXXX, XXXXXXXXXX,670 XXXXXXXXXX15 XXXXXXXXXX400
211 Slack Elton Programmer Associate 04/12/1995 03/24/ XXXXXXXXXX,200
You may use any technique to create your Sql Express database. For example, the easiest way would be to just create an empty web application and use the
wizards to create and populate your database (you can hand input the data). For your Sql Express database use the following name:
Great_YourLastNameFirstInitial. For example, if your last name was Smith and your First Name was Joe your Sql Express database name should be
Great_SmithJ. For your table name use Employees. Note: at this stage the database is not normalized. If you have some knowledge of Sql and have the time,
you can normalize your database for bonus points by putting the appropriate data into separate tables (job information, sales data, etc.). This file will be put in my
c:\databases directory for scoring so make sure that all your c# code uses this location for your database unless you use the app.config bonus points technique.
You will then create a console application that will pull information from your Sql Express database and do at a minimum the following:
1. Print to the screen a data dump of all fields contained in your Employees table.
2. Output should be nicely formatted including column headers, aligned cu
ency and numeric data, etc.
Completion of minimum requirements will earn a C grade area.
For a B grade in addition to the above you will need to complete the following:
1. Make a new report column that contains the calculated bonus amount. You may use a
eviations in your column header.
2. Make a new report column that contains the total compensation (salary + bonus amount + car allowance). You may use a
eviations in your column
header.
Note:
All calculations should be based upon the data found in your Sql Server Express database. That is, do not hard code your data unless otherwise instructed: use
your defined Sql Express fields to retrieve your report data.
For the president monthly bonus amount is simply the bonus rate * monthly salary.
For Sales Associates the bonus amount is Sales Amount * Bonus Rate. Make sure that you realize that Bonus Rate is a %! Thus, a Bonus Rate of 5% is .05 in
your calculations.
For the Sales Manager the bonus calculation gets more interesting. If you have previous Sql knowledge it is relatively easy to retrieve the sales amount data for
the other sales associates, total this data, and then perform your bonus calculation based on the total sales amount for all the Sales Associates (total sales amount
* the Sales Manager bonus rate). However, if you have limited Sql experience you can just type this total in the Sales Amount field for the Sales Manager. Thus
the Sales Manager bonus is the bonus rate * the total sales for the other sales personnel.
For an A grade you will need in addition to the above the following:
1. Print out at the bottom of your report the appropriate grand totals (monthly salary, sales amount, total monthly compensation, car allowance, etc.).
2. Sort the data by Job Title, Monthly salary (ascending for each field).
3. Sort the data by Job Title, Total Compensation (ascending for each field).
Bonus points will be given for the following:
1. Creating your database in pure code. For those of you who have the time and have some knowledge of Sql creating the EIS database in code is fairly easy. For
example, see the following URL:
https:
support.microsoft.com/en-us/help/307283/how-to-create-a-sql-server-database-programmatically-by-using-ado-net
Bonus Points: 10.
2. Use an app.config file with a |DataDirectory| option. Note that for console applications this will result in the default search using the root directory and not the
App_Data folder, i.e. place your Sql Express database file in the root directory of your application.
Bonus points: 10
3. Normalize your database by adding additional tables such as job code, sales information, etc. and using the appropriate joins to process your data.
Bonus points: 10
You will have 2 weeks for this assignment.
https:
support.microsoft.com/en-us/help/307283/how-to-create-a-sql-server-database-programmatically-by-using-ado-net