CMS PROJECT - PHASE II INSTRUCTIONS

In this phase, you will create tables based upon the ERD and SQL code below. You will then populate each table with the data presented below. Finally, you will create queries that will be used to support reports for Accounting and Management. You will not actually create the reports in a GUI environment, only the queries that will serve as the basis for the reports. Screenshots are required for a grade to be given. One screenshot is not the idea; however, multiple screenshots along the way is the goal.

Part A: Table Creation and Data Loading

Instructions: Create a new database in SQL Server and run the following CREATE TABLE commands. Note that you must run the CREATE TABLE statements in the order presented (and load the data in the order presented) to avoid conflicts resulting from foreign key constraints.

Part B: Reports

1. Human Resources:

The HR department requires a list of all the employees who are employed by CMS. This information should be organized as follows:

Region
Country
Employee name (Last, First)
Title + Level (e.g. "Consultant - 1")
Salary (in USD)

*Sort data in ascending order first by region, then by country, then by employee last name, then by title, and then by salary.

Instructions:

For this assignment, write the query that produces the results as described above.

2. Invoicing

Accounting requires information to produce invoices. For each client, CMS's invoicing controller must know the following information as of the last day of each month:

Client name
Contract name(s)
Project(s)

Employees who logged hours to a project from the first day of the current month until the last day of the current month

Total number of hours logged for each employee during the month
Employee rate
Total charges per employee (i.e. employee rate x employee hours worked)
Billing contact(s)(name, address)for each contract
*Sort data in ascending order first by client, then by project, and then by employee.

Instructions:

All of this information should be produced using a single query that can serve as the basis for a report.Do not use views or stored procedures in conjunction with your query.

For this assignment, you will write your query for only the month of April 2013. You may hardcode the month number in your query. In the realworld, you would likely run this report for the current month, in which case you would want to use the getdate() function to retrieve the current date. Conversely, you might produce this query as a stored procedure that takes a given month as an argument and returns a resultset. For your assignment, however, just assume this report will be run for April 2013 and hardcode this date in your query to produce the results.

3. Benefit Tracking

The HR department requires a report that provides information on benefit information. Assume a calendar year whereby new benefit allotments are granted as of January 1 and must be used by December 31 of same year. No carryover benefits are allowed.

Number of benefits days allotted to each employee
Number of benefit days taken year-to-date
Number of benefit days remaining in the calendar year
Number of holidays allotted to each employee
Number of holidays taken year-to-date
Number of holidays remaining in the calendar year

*Data mustbe sorted in ascending order by employee last name.

Instructions:

For this assignment, write a query that produces the results described above. Assume that you are running the report for the 2013 calendar year. As in the previous report, in the realworld, you would likely use the getdate() function to determine the current date and run the report from the beginning of the current year until the present time. For this assignment, however, you may hardcode the year 2013 in your query and retrieve all of the data for that year.

4. Management Exception Reporting

a. Management must keep track of employees whose combined hours have exceeded the maximum allowed hours on projects. This report must be run before invoicing occurs in order to prevent billing in excess of contractual amounts. Show only projects whose cap amounts have been exceeded.

Project name
Maximum allowed hours per project
Total hours worked on project
Overage (the difference between the cap and actual hours)
*Sort data by project name.

b. In a separate query, show the details for the projects whose cap amounts have been exceeded:

Project name
Employees who worked on project
Total hours worked on project per employee

*Sort data by project name and then by employees who worked on the project

Instructions:

For this assignment, write a query for 4(a) and a separate query for 4(b). The results mustreflect the requirements described above.

5. Payroll

The payroll department requires a report of employees who are logging more hours per week than they are legally required to work per country stipulations. These employees are paid overtime wages for hours worked in excess of weekly stipulated hours.

Employee name
Employee country
Weekly Hours per employee per country
Hours logged by employee in current week

Instructions:

For this assignment, produce a query that determines employees who have incurred overtime during April 2013.

Phase II Deliverables:

1. In a Word document, take screen shots of the data in each of your tables using basic SELECT statements.

For example, SELECT * from Clients

2. Write queries for each of the reports above. In the same Word document, include screenshots of your queries from SQL Server Express (or SQL Server). Below EACH query, include (via screen shots) the results of each query.

3. Name your Word document as follows: "Phase II CMS Project - your last name followed by your first initial."

Using the link provided in Blackboard, upload the CMS Project - Phase II by 11:59 p.m. (ET) on Friday of Module/Week 8.

Attachment:- CMS_Project_Phase_II_Instructions.rar