CMS Project: Phase II Instructions

BMIS 325

CMS Project: Phase II Instructions

In this phase, you will create tables based upon the ERD and SQL code below.You will thenpopulate 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.

Background:

The following ERD will be used as the basis for this Phase.

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.

Additional instructions for materials to turn in for this phase of your project are included at the end of this specification document.

CREATE TABLE Regions

(RegionID int not null,

RegionAbbreviation varchar(4),

RegionName varchar(100),

CONSTRAINT PK_Regions PRIMARY KEY (RegionID))

CREATE TABLE Countries

(CountryID int not null,

CountryName varchar(50),

WeeklyHours int,

Holidays int,

VacationDays int,

RegionID int,

CONSTRAINT PK_Countries PRIMARY KEY (CountryID),

CONSTRAINT FK_CountriesRegions FOREIGN KEY (RegionID) References Regions)

CREATE TABLE EmployeeTitles

(TitleID int not null,

Title varchar(15),

CONSTRAINT PK_EmpTitles PRIMARY KEY (TitleID))

CREATE TABLE BillingRates

(TitleID int not null,

Level int not null,

Rate float,

CurrencyName varchar(5),

CONSTRAINT PK_BillingRates PRIMARY KEY (TitleID, Level),

CONSTRAINT FK_BillingRatesTitles FOREIGN KEY (TitleID) References EmployeeTitles)

CREATE TABLE Employees

(EmpID int not null,

FirstName varchar(30),

LastName varchar(30),

Email varchar(50),

Salary decimal(10,2),

TitleIDint,

Level int,

SupervisorID int,

CountryID int,

CONSTRAINT PK_Employees PRIMARY KEY (EmpID),

CONSTRAINT FK_EmployeesCountries FOREIGN KEY (CountryID) References Countries,

CONSTRAINT FK_EmployeesEmpTitles FOREIGN KEY (TitleID) References EmployeeTitles,

CONSTRAINT FK_EmployeeSupervisors FOREIGN KEY (SupervisorID) References Employees)

CREATE TABLE ContactTypes

(ContactTypeID int not null,

ContactType varchar(30)

CONSTRAINT PK_ContactTypes PRIMARY KEY (ContactTypeID))

CREATE TABLE ContractTypes

(ContractTypeID int not null,

ContractType varchar(30)

CONSTRAINT PK_ContractTypes PRIMARY KEY (ContractTypeID))

CREATE TABLE BenefitTypes

(BenefitTypeID int not null,

BenefitType varchar(30)

CONSTRAINT PK_BenefitTypes PRIMARY KEY (BenefitTypeID))

CREATE TABLE Clients

(ClientID int not null,

LegalName varchar(50),

CommonName varchar(50),

AddrLine1 varchar(50),

AddrLine2 varchar(50),

City varchar(25),

State_Province varchar(25),

Zip varchar(9),

CountryID int,

CONSTRAINT PK_Clients PRIMARY KEY (ClientID),

CONSTRAINT FK_ClientsCountries FOREIGN KEY (CountryID) REFERENCES Countries)

CREATE TABLE Contacts

(ContactID int not null,

FirstName varchar(50),

LastName varchar(50),

AddrLine1 varchar(50),

AddrLine2 varchar(50),

City varchar(25),

State_Province varchar(25),

Zip varchar(9),

CountryID int,

ContactTypeID int,

CONSTRAINT PK_Contacts PRIMARY KEY (ContactID),

CONSTRAINT FK_ContactsCountries FOREIGN KEY (CountryID) REFERENCES Countries)

CREATE TABLE ContractTypes

(ContractTypeID int not null,

ContractTypeDesc varchar(50),

CONSTRAINT PK_ContractTypes PRIMARY KEY (ContractTypeID))

CREATE TABLE Contracts

(ContractID int not null,

ContractDesc varchar(100),

ClientID int,

ContractTypeID int,

CONSTRAINT PK_Contracts PRIMARY KEY (ContractID),

CONSTRAINT FK_ContractsClients FOREIGN KEY (ClientID) REFERENCES Clients,

CONSTRAINT FK_ContractsContractTypes FOREIGN KEY (ContractTypeID) REFERENCES ContractTypes)

CREATE TABLE ContractsContacts

(ContractID int not null,

ContactID int not null,

CONSTRAINT PK_ContractsContacts PRIMARY KEY (ContractID, ContactID),

CONSTRAINT FK_CC_Contracts FOREIGN KEY (ContractID) REFERENCES Contracts,

CONSTRAINT FK_CC_Contacts FOREIGN KEY (ContactID) REFERENCES Contacts)

CREATE TABLE Projects

(ProjectID int not null,

ProjectName varchar(50),

HourCapAmount decimal(10,2),

ProjectManagerID int,

ContractID int,

CONSTRAINT PK_Projects PRIMARY KEY (ProjectID),

CONSTRAINT FK_ProjectsEmployees FOREIGN KEY (ProjectManagerID) REFERENCES Employees,

CONSTRAINT FK_ProjectsContracts FOREIGN KEY (ContractID) REFERENCES Contracts)

CREATE TABLE EmployeesProjects

(EmpID int not null,

ProjectID int not null,

StartDate smalldatetime,

EndDate smalldatetime,

CONSTRAINT PK_EmployeesProjects PRIMARY KEY (EmpID, ProjectID),

CONSTRAINT FK_EP_Employees FOREIGN KEY (EmpID) REFERENCES Employees,

CONSTRAINT FK_EP_Projects FOREIGN KEY (ProjectID) REFERENCES Projects)

CREATE TABLE Timesheets

(TimesheetID int not null,

SupervisorApproveDate smalldatetime,

CONSTRAINT PK_Timesheets PRIMARY KEY (TimesheetID))

CREATE TABLE WorkHours

(EmpID int not null,

ProjectID int not null,

WH_Day int not null,

WH_Month int not null,

WH_Year int not null,

HoursWorked float,

TimesheetID int,

CONSTRAINT PK_WorkHours PRIMARY KEY (EmpID, ProjectID, WH_Day, WH_Month, WH_Year),

CONSTRAINT FK_WorkHoursEmployees FOREIGN KEY (EmpID) REFERENCES Employees,

CONSTRAINT FK_WorkHoursProjects FOREIGN KEY (ProjectID) REFERENCES Projects,

CONSTRAINT FK_WorkHoursTimesheets FOREIGN KEY (TimesheetID) REFERENCES Timesheets)

CREATE TABLE BenefitsTaken

(EmpID int not null,

BenefitTypeID int not null,

BT_Day int not null,

BT_Month int not null,

BT_Year int not null,

HoursTaken float,

TimesheetID int,

CONSTRAINT PK_BenefitsTaken PRIMARY KEY (EmpID, BenefitTypeID, BT_Day, BT_Month, BT_Year),

CONSTRAINT FK_BenefitsTakenEmployees FOREIGN KEY (EmpID) REFERENCES Employees,

CONSTRAINT FK_BenefitsTakenBenefitTypes FOREIGN KEY (BenefitTypeID) REFERENCES BenefitTypes,

CONSTRAINT FK_BenefitsTakenTimesheets FOREIGN KEY (TimesheetID) REFERENCES Timesheets)

Data Section

The following information is currently maintained in various spreadsheets throughout CMS. Data from these spreadsheets must be uploaded into your newly created tables before the database can be considered operational.

REGIONS

IDAbbr.Region Name

1NARNorth America

2CALACentral and Latin America

3APACAsia and Pacific

4EMEAEurope, Middle East, and Africa

COUNTRIES

IDCountry NameWeekly HoursHolidaysVacation Days Region

1United States401110 NAR

2Canada401215 NAR

3United Kingdom381010 EMEA

4France381410 EMEA

5Ireland381015 EMEA

6Italy35920 EMEA

7Thailand401720 APAC

8Singapore401721 APAC

9Panama401215 CALA

BENEFIT TYPES

IDBenefit Type Name

1Vacation

2Holiday

3Jury Duty

4Maternity Leave

5Paternity Leave

6Military Duty

CONTACT TYPES

IDContact Type Name

1Systems Engineer

2Sales

3Billing

CONTRACT TYPES

IDContract Type Name

1Maintenance

2Fixed Price

3License

4Time and Materials

CLIENTS

ID Legal Name Common Address1 Address2 CityState Zip Country

1 BMA British Mobile130 Wake Dr. WakeNC 24539US

2 FT France Mobile123 East St. Suite #2Paris 45678 France

3 IBC IBC456 MainJohor 78945 Singapore

4 MTM MTM6789 First St. MeadGA 45678US

5 BT Britain Tele98769 Park St. Level 3 London 48695UK

CONTRACTS

IDContractDescContract TypeClient

1Work Order 1MaintFT

2Work Order 1T&MBT

3Work Order 1Fixed PriceIBC

4Work Order 2MaintIBC

5Work Order 1Fixed PriceMTM

6Work Order 2T&MFT

CONTACTS

ID First LastAddr1 Addr2 City StateZip Country Type

1 Bugg Bunny123 Looney NoWhereAK 45678 US SysEng

2 Elmer Fudd789 Park Pl. Apt 3 SkyvilleNM 45678 US Billing

3 Daffy Duck45678 One St. Norwood 45678 UK Sales

4 Darth Vader456 Two St. Towns 47896 UK Sales

5 Luke Sky#4 Tatooine Paris45678 France Billing

6 Princess Lea723 Coruscant Rome45678 Italy SysEng

7 John Doe987 Main St. Paris78945 France SysEng

8 Jane Doe7658 Oak Ln. CrueVA45678 US SysEng

CONTRACTS’ CONTACTS

ContractClientContact Name

Work Order 1BTDaffy Duck

Work Order 1FTJohn Doe, Jane Doe, Princess Lea

Work Order 2FTElmer Fudd

Work Order 1IBCBuggs Bunny

Work Order2IBCLuke Sky

Work Order 2IBCDarth Vader

Work Order 1MTMDaffy Duck

EMPLOYEETITLES

IDTitle

1Consultant

2Analyst

3Director

BILLINGRATES

TitleIDLevelRateCurrency

11150.00USD

12200.00USD

13300.00USD

2150.00USD

22100.00USD

23150.00USD

31250.00USD

32350.00USD

33450.00USD

EMPLOYEES

IDFirstLastCountryIDEmailSalaryTitleLevelID

1MatthewSmith 45000Consultant 1

2MarkJones 94000Director 1

3LukeRice 65000Consultant 2

4JohnRich 74000Consultant3

5JamesDoe 40000Analyst1

6PeterPride 60000Analyst2

7EricPotter 81000Consultant 3

8PaulDavis 103000 Director 2

PROJECTS

IDProject NameHourCapAmountProjectManagerContractsClient

1IBC – India 120Davis Work Order 2IBC

2FT-Maint 100Doe Work Order 2FT

3BT – WO 1 Time 270Rich Work Order1BT

4BT – WO1 MaterialsRich Work Order1BT

5IBC - WO1Davis Work Order 1IBC

6IBC – WO2Davis Work Order2IBC

7MTM – WO1Pride Work Order1MTM

8FT – WO2 Time 500Doe Work Order2FT

9FT –WO2 MaterialsDoe Work Order2FT

PROJECT-EMPOYEE ASSIGNMENTS

EmployeeProjectStartDateEndDate

DoeIBC-India1/1/2013

DoeIBC - WO15/7/2013

DoeBT – WO1 Materials2/1/20134/30/2013

SmithFT-Maint2/1/2013

JonesFT-Maint3/1/2013

RiceMTM – WO11/1/2013

WORKHOURS

EmployeeProjectDayMonthYearHoursWorkedTimeSheet

DoeIBC-India24201381

DoeIBC-India34201381

DoeIBC-India44201381

DoeIBC-India54201381

DoeIBC-India64201381

DoeIBC-India94201381

DoeIBC-India104201381

DoeIBC-India114201381

DoeIBC-India124201381

DoeIBC-India134201341

DoeIBC - WO1134201341

DoeIBC - WO1164201341

DoeIBC-India174201381

DoeIBC-India184201381

DoeIBC-India194201351

DoeIBC-WO1 194201331

DoeIBC-India204201381

DoeIBC-India234201381

DoeIBC-India244201381

DoeIBC-India264201381

DoeIBC-India274201381

DoeIBC-India304201381

DoeIBC-WO115201382

DoeIBC-WO125201382

DoeIBC-WO135201382

DoeIBC-WO145201382

DoeIBC-India75201382

DoeIBC-WO185201382

DoeIBC-WO195201382

DoeIBC-WO1105201382

DoeIBC-WO1115201382

DoeIBC-India145201382

DoeIBC-WO1155201382

DoeIBC-WO1165201382

DoeIBC-WO1175201382

DoeIBC-WO1185201382

DoeIBC-India215201382

DoeIBC-WO1225201382

DoeIBC-WO1235201382

DoeIBC-WO1245201382

DoeIBC-India285201382

DoeIBC-WO1295201382

DoeIBC-WO1305201382

DoeIBC-WO1315201382

JonesFT-Maint24201383

JonesFT-Maint34201383

JonesFT-Maint44201383

JonesFT-Maint54201383

JonesFT-Maint64201383

JonesFT-Maint94201383

JonesFT-Maint104201383

JonesFT-Maint114201383

JonesFT-Maint 124201383

JonesFT-Maint1342013153

JonesFT-Maint1642013143

JonesFT-Maint174201383

JonesFT-Maint184201383

JonesFT-Maint1942013103

JonesFT-Maint204201383

JonesFT-Maint234201383

JonesFT-Maint244201383

JonesFT-Maint264201383

JonesFT-Maint274201383

JonesFT-Maint304201383

JonesFT-Maint15201384

JonesFT-Maint25201384

JonesFT-Maint35201384

JonesFT-Maint45201384

JonesFT-Maint75201384

JonesFT-Maint85201384

JonesFT-Maint95201384

JonesFT-Maint105201384

JonesFT-Maint115201384

JonesFT-Maint145201384

JonesFT-Maint155201384

JonesFT-Maint165201384

JonesFT-Maint175201384

JonesFT-Maint185201384

JonesFT-Maint215201384

JonesFT-Maint225201384

JonesFT-Maint285201384

JonesFT-Maint295201384

JonesFT-Maint305201384

JonesFT-Maint315201384

SmithFT-Maint24201385

SmithFT-Maint34201385

SmithFT-Maint44201385

SmithFT-Maint54201385

SmithFT-Maint94201385

SmithFT-Maint104201385

SmithFT-Maint114201385

SmithFT-Maint 124201385

SmithFT-Maint1642013145

SmithFT-Maint174201385

SmithFT-Maint184201385

SmithFT-Maint1942013105

SmithFT-Maint204201385

SmithFT-Maint234201385

SmithFT-Maint244201385

SmithFT-Maint264201385

SmithFT-Maint274201385

SmithFT-Maint304201385

SmithFT-Maint15201386

SmithFT-Maint25201386

SmithFT-Maint35201386

SmithFT-Maint45201386

SmithFT-Maint75201386

SmithFT-Maint85201386

SmithFT-Maint95201386

SmithFT-Maint105201386

SmithFT-Maint115201386

SmithFT-Maint145201386

SmithFT-Maint155201386

RiceMTM – WO124201387

RiceMTM – WO134201387

RiceMTM – WO144201387

RiceMTM – WO154201387

RiceMTM – WO164201387

RiceMTM – WO194201387

RiceMTM – WO1104201387

RiceMTM – WO1114201387

RiceMTM – WO1124201387

RiceMTM – WO11642013147

RiceMTM – WO1174201387

RiceMTM – WO1184201387

RiceMTM – WO11942013107

RiceMTM – WO1204201387

RiceMTM – WO1234201387

RiceMTM – WO1244201387

RiceMTM – WO1264201387

RiceMTM – WO1274201387

RiceMTM – WO1304201387

RiceMTM – WO115201388

RiceMTM – WO125201388

RiceMTM – WO135201388

RiceMTM – WO145201388

RiceMTM – WO175201388

RiceMTM – WO185201388

RiceMTM – WO195201388

RiceMTM – WO1105201388

RiceMTM – WO1115201388

RiceMTM – WO1145201388

RiceMTM – WO1155201388

BENEFITS TAKEN

EmployeeDayMonthYearBenefitTypeTimeSheet

Doe2542013Holiday1

Doe2552013Holiday2

Jones2542013Holiday3

Jones2352013Vacation3

Jones2452013Vacation4

Jones2552013Holiday4

Smith642013Vacation5

Smith2542013Holiday5

Smith2552013Holiday6

Rice2542013Holiday7

Rice2552013Holiday8

TIMESHEETS

IDSupervisorApproveDate

14/30/2013

25/31/2013

34/30/2013

45/31/2013

54/30/2013

65/31/2013

74/30/2013

85/31/2013

Part B: Reports

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, then by Salary

Instructions:

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

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
Totalnumber 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, 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 real-world, 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.

Benefit Tracking

The Human Resources 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 should be 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 real-world, 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.

Management Exception reporting

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

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, 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 should reflect the requirements described above.

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:

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

For example, SELECT * from Clients

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.

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.

Page 1 of 16