You are provided with a Microsoft Access database with information about the people in several departments of Spacely Sprockets. You have been asked to analyze the information to see if there is any pattern of discrimination against women. It seems that one of our long-term employees, Becca Pearl, has threatened to file a salary discrimination suit with the department of labor. This is surprising since she is well paid and appreciated greatly by J.D. Clampett, the CEO of Spacely Sprockets. Ms. Pearl claims that women's salaries are almost always below those of comparable men's salaries; although how she would say who is comparable is hard to figure out because there are several departments and several job classifications within departments. Here is what Mr. Clampett wants you to do with the provided database:

1) In one key aspect, Mr. Clampett also believes our HR database leaves much to be desired. For example, while it doesnt appear to be a problem currently (i.e. you could do the present assignment with the database as currently designed), it is conceivable that deleting an employee (say upon retirement) could result in their job class or department being lost, due to the database tables not being properly normalized. Hence, he would like you to separate out themes into different tables and create proper relationships between the tables using appropriate foreign keys. Don't worry about deleting redundant data fields yet - just get the linkages done. You will need to look at the relationship view in Access to do this.

2) Next, use the query functions in Microsoft Access to sort and select out patterns of information which would help prove that the position youve been assigned in class, e.g., sort by sex and salary at the same time or just by salary; whatever you believe is appropriate. The combinations of fields you sort are up to you. Prepare a 1-2 PAGE summary of your conclusions using Word (1-inch margins, 12-point font, single space). Save your document as a .PDF. Include tables in your document (which will mean it should be longer by the size of the tables). Give the queries you use in your document descriptive titles so I can figure out what they are.

The 1-2 PAGE summary explaining what you found should appear in the Word document along with copies of the queries you use for your analysis in the same Word document. Put a descriptive title on each query (once you copy them over to the Word document). Save the whole thing as a .PDF.

3) Mr. Clampett suggests that you start by figuring out the basic reasons why salaries are different. Find some hypothesis that explains the salary differentials. The job titles, for example, might be the key; maybe the number of years employed.

4) Salaries should be about the same in each Job Classification. Also, the lower the Job Class is, the less important the job is.

5) Employees get a raise every year, so the number of years should be a good indicator of salary. But then, because of inflation, we have had to pay the new people we hired more and more each year.

6) Mr. Clampett has told you that he is not certain why we pay people what we do, but he is confident that there is a good reason.

7) Discrimination on the basis of sex is defined for this assignment as a woman getting a lower salary for doing the same job as a man.

8) In several instances you may well find yourself wanting information about Mr. Clampett's employees that simply doesn't exist in the database. As part of your report, please make some recommendations about what additional information Mr. Clampett should store in the database that would help defend against claims like those made by Ms. Pearl in the future.

To complete this assignment, turn in a copy of your completed database and the .PDF document you create with your analysis and queries. This will include all the queries (give them descriptive names) that support your conclusions (discussion of why the queries you provide make your case should be in your memo to Mr. Clampett). Also, sort the employee table in alphabetical order by employee last name. Include also queries listing how many people we have working for us in the information systems department (names, departments and job class will be fine), and all employees at Job Class 4 (again with names, departments and job classes).

The number of queries for the discrimination question you turn in is up to you; however many you need to make your case. But rememberyou are trying to come to a conclusion. Consequently, don't give Mr. Clampett any irrelevant queries containing unnecessary data.