CMS Phase 1 Part A and Part B + Phase 2 CSIS 325

CMS Project – Phase I Instructions

Part A:

Background Information:

CMS Systems, Inc. is a company that provides information systems consulting services to companies in the telecom industry in the United States and the United Kingdom. Due to its success, CMS is hoping to expand its operations into other parts of Europe. Despite its large size, CMS currently uses a manual/spreadsheet-based process for maintaining employee and client data. Management has now decided to implement a company-wide database that will serve all of its operations.

CMS currently employs 1,500 individuals (900 in the US and 600 in the UK) who serve as systems analysts, developers, managers, testers, maintenance engineers, accountants, lawyers, and sales representatives. Each employee has a first name, last name, unique CMS ID, office location, email address, salary, title, level, and supervisor.

CMS has more than 200 clients in the US and UK. Clients are identified by various names by CMS associates. As such, they represent a source of confusion for the company. The legal department refers to clients by their legal names, while the sales and consulting departments refer to them by a more common name. One example is British Telecom. CMS’s legal team uses its full legal name “British Telecom, Ltd.,” while the sales force and consultants refer to it as “BT.” The accounting department uses a mixture of legal and common names to identify clients. Thus, to avoid confusion, both legal and common names must be available to all users. Data that must be kept about clients include client names, an address (city, state, zip, country), and contact information (discussed below).

Clients have contacts within their companies that CMS employees must utilize. For example, the accounting department must know a client’s billing contact in order to know where to send the bill. Maintenance engineers must know a client’s systems engineering contact to know with whom to speak when a problem arises. CMS’s sales representatives must know a client’s sales contact to determine who is responsible for the buying decisions at a client site. Although only these three contact “types” are currently used by CMS, it is foreseeable that additional contact types might be useful as the company expands into other parts of the world. Currently, a client has only one billing contact, one systems engineering contact, and one sales contact at any given time. This structure is not expected to change (that is, more than one billing contact per client as of a particular point in time is not anticipated); however, it is important to retain all contact information over time. That is, when one contact is replaced by another contact, it is essential to retain information about the original contact. For example, assume an invoice is sent to Contact A, who is later replaced by Contact B. If the invoice is lost, CMS must have an audit trail to show that it was sent to Contact A (who was the known contact for the client at that time).

Clients can have one or more contracts with CMS to provide a variety of consulting services. For example, a single client might have one contract for maintenance of an existing system and also have another contract (sometimes called a work order by the sales force) for the development of a new system.

Some clients are billed based upon negotiated contracts, which stipulate a pre-determined amount for charges regardless of the number of hours that employees actually work on the contracts. Such contracts are called “fixed price” contracts. Other clients are billed based on the total number of hours provided by CMS employees multiplied by a rate per employee type per employee hour. These arrangements are called “T&M – Time and Materials” contracts. T&M contracts often specify a maximum number of hours for which the client is willing to pay. CMS managers must ensure that when these maximum (cap) amounts are exceeded, the clients are not billed for such additional hours.

For T&M contracts, the rate per hour for each consultant is determined by the employee’s level of expertise. For example, a client might pay $100/hour for an employee who is at the level of Systems Analyst I. That same client would pay $250/hour for an employee designated as a Manager Level II.  

Although T&M and Fixed Price contracts are the only two types of contracts currently used by CMS, it is likely that other types of contracts will be used in the near future.

All CMS employees must keep a record of the time they spend working for each client. Because employees can work for more than one client and perform different functions for each client, CMS utilizes “project management” to keep track of employee assignments to client contracts. Employees can be assigned to many different projects throughout their tenure. They also can be assigned to more than one project at a given time. In fact, it is not unusual for an employee to spend time on two or more different projects within the same day. Likewise, projects can consist of many different employees. Project assignments change over time. For example, Employee “A” may work on Project “X” during January, and, in February, that same employee may be reassigned to Project “Y.” It is important to maintain records of the dates for which each employee is assigned to each project. Additionally, each project has a manager who oversees its progress and ensures that contracts are fulfilled and profitable (e.g., U.S. project managers try to prevent hours worked in excess of the maximum allowed by a fixed price contract).  

Just as a client can have more than one contract with CMS, a contract can consist of more than one project. For example, a contract for the development of a new system could be fulfilled in multiple phases. Phase I could include implementation at one client site using a group of consultants in close proximity to that site. Phase II could include implementation at a different site with a potentially different set of consultants. Both of these phases are considered separate projects, even though they are governed by the same contract. To further complicate matters, if such a contract specifies a maximum number of hours for overall implementation, the project managers of each project will have to agree how to split up the cap amounts between the two projects and maintain appropriate data about the split.

The number of hours worked for each employee on each project must be recorded on a daily basis. Employees currently log their time using an Excel worksheet. An example of this worksheet is presented below. Notice that the employee’s supervisor is listed on the worksheet. This supervisor may or may not be the same person as the project manager. Each employee is assigned to one supervisor, and each supervisor manages one or more employees. The concepts of supervisor and project manager have completely different meanings at CMS. A supervisor manages an employee with respect to evaluations, vacation requests, raises, etc. A project manager manages a project, allocating the time of employees assigned to the project. A project manager is not required to sign off on an employee’s timesheet. A supervisor, however, is required to approve his/her employees’ timesheets by placing his/her initials beside his/her name.

Part A: Deliverable

For this phase, you are required to create an ER Diagram that will facilitate the development of CMS’s company-wide database. It must be created using ER Assistant and include entities and attributes, relationships, and accompanying notes. You must take a screenshot of your ERD while it is displayed in ER Assistant and paste the screenshot into a Word document. Make sure that the ERD fits on a single sheet and is legible. Accompanying notes must be included on a separate sheet of the document. Name this Word document “CMS Project Part A – your last name followed by your first initial.”

Example: CMS Project Part A – SmithJ.doc

The scope of the database includes all entities referenced above. The first goal of this phase of development is to support a timekeeping system that will replace the spreadsheet process illustrated above. Note that you will not actually create the timekeeping system. You are responsible only for designing the database that will be used by the system.

Part B: Background

After much deliberation, CMS has decided to broaden its operations by expanding into various parts of Europe and also Canada. It plans to recruit employees from Canada, Italy, France, and Ireland. Each of these countries offers different benefit packages. Presently, under the manual spreadsheet system, human resource personnel have to maintain only two types of benefit allotments. In the newly expanded company, this manual process will be unmanageable.

The following table lists the different benefit packages in each country. Some characteristics are unique to each country while others are unique to a region.

Region

Country

Holidays

WeeklyHours

VacationDaysAllowed

North America

US

11

40

10

North America

Canada

12

40

15

Europe

UK

10

38

10

Europe

France

14

38

10

Europe

Ireland

10

38

15

Europe

Italy

9

38

20

In the same manner that employees must track the time they spend working on projects, they must also log the days they use as holidays and vacation. Below is a sample timesheet for recording benefit time taken. Note that this benefit section exists on the same timesheet that is used to log hours to projects, but benefits are not related to projects.

In addition to the need to accommodate benefit tracking in the new database, a change has occurred since the last iteration. In Part A, CMS stated, “a client has only one billing contact, one systems engineering contact, and one sales contact at any given time. This structure is not expected to change (that is, more than one billing contact per client as of a particular point in time is not anticipated).” Recent developments from a newly acquired client have caused this assertion to no longer be true. France Telemobile, Inc. is a new client in France and has three different systems engineering contacts to support its one contract with CMS. Thus, a change in initial design is required.

Finally, CMS is implementing two new types of contracts, maintenance and license, to be added to its list of possible contracts.

Currently, maintenance is included as part of an initial work order. In keeping with the rules of accounting and revenue recognition for software providers, maintenance is an important feature of a contract and has special rules that affect how much revenue can be recognized for licenses. To simplify its procedures, the company will be issuing separate contracts for maintenance to clearly distinguish between license revenue, which can be recognized at the time of system delivery, and maintenance revenue, which in most cases must be recognized over the duration of the maintenance agreement.

Many of the features of CMS’s custom work orders have evolved into a standard set of templates that will be sold as a package to other companies to expand CMS’s client base. Therefore, the company has created various products that incorporate the most widely used features of its custom development initiatives. The products are called TeleTrak-BP, TeleTrak-FM, and TeleSource. Each product will be sold as an out-of-the-box solution to tracking usage and subscriptions in the Telecom industry. Each product will be sold under a license contract. Presently, a license contract will be issued for exactly one product, but it is possible that a license agreement will be written in the future that will cover multiple products as new products are developed.

Part B: Deliverable:

Using ER Assistant, modify your original ERD to accommodate the requirement to allow more than one instance of a given contact type per contract. Also, include new entities and relationships to support benefit tracking in all countries. Depending on your initial design, you may or may not have to modify your ERD to accommodate the new contract types (License and Maintenance). You will also have to account for the introduction of products in the business model.

In a separate Word document, take a screenshot of your revised ERD and paste it in. Include accompanying notes.

After completing your new ER Diagram, convert the diagram into tables with primary keys and foreign keys as appropriate. Use SQL Server to create your tables. Be sure to enforce referential integrity in your CREATE TABLE statements using “on delete,” “on update,” etc., where appropriate.

As in Part A, your ERD screenshot must be able to legibly fit on a single sheet in the Word document. To receive credit for your table conversions, include the SQL queries used to generate the tables and paste them into your Word document. Also execute the following command for each table and include results in your document: exec sp_help [table name]

Name this document “CMS Project Part B – your last name followed by your first initial.” Screenshots are required each SQL, DML, DDL, and DCL statement for a grade to be given. Note: 1 screenshot is not the idea; however, multiple screenshots along the way is the goal.

Using the link provided in Blackboard, upload your 2 documents for phase I of this project by 11:59 p.m. (ET) on Monday of Module/Week 5.

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.

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),

 TitleID int,

 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

ID        Abbr.               Region Name

1          NAR               North America

2          CALA             Central and Latin America

3          APAC             Asia and Pacific

4          EMEA             Europe, Middle East, and Africa

Countries

ID        Country Name             Weekly Hours             Holidays          Vacation Days      Region

1          United States              40                                11                    10                         NAR

2          Canada                                    40                                12                    15                         NAR

3          United Kingdom         38                                10                    10                         EMEA

4          France                         38                                14                    10                         EMEA

5          Ireland                         38                                10                    15                         EMEA

6          Italy                             35                                9                      20                         EMEA       

7          Thailand                      40                                17                    20                         APAC

8          Singapore                    40                                17                    21                         APAC

9          Panama                        40                                12                    15                         CALA

Benefit Types

ID        Benefit Type Name

1          Vacation

2          Holiday

3          Jury Duty

4          Maternity Leave

5          Paternity Leave

6          Military Duty

Contact Types

ID        Contact Type Name

1          Systems Engineer

2          Sales

3          Billing

Contract Types

ID        Contract Type Name

1          Maintenance

2          Fixed Price

3          License

4          Time and Materials

Clients

ID   Legal Name     Common             Address1          Address2    City State      Zip          Country

1     BMA                British Mobile     130 Wake Dr.                       Wake NC    24539         US

2     FT                     France Mobile     123 East St.        Suite #2        Paris         45678       France

3     IBC                  IBC                     456 Main                                 Johor        78945    Singapore

4     MTM                MTM                   6789 First St.                       Mead GA    45678         US

5     BT                    Britain Tele         98769 Park St.      Level 3     London      48695         UK

Contracts

ID        ContractDesc  Contract Type Client

1          Work Order 1  Maint               FT

2          Work Order 1  T&M               BT

3          Work Order 1  Fixed Price      IBC

4          Work Order 2  Maint               IBC

5          Work Order 1  Fixed Price      MTM

6          Work Order 2  T&M               FT

Contacts

ID   First      Last         Addr1               Addr2     City           State     Zip        Country      Type

1     Bugg     Bunny     123 Looney                  NoWhere       AK      45678       US         SysEng

2     Elmer    Fudd       789 Park Pl.       Apt 3   Skyville        NM     45678        US          Billing

3     Daffy    Duck       45678 One St.               Norwood                   45678       UK          Sales

4     Darth    Vader      456 Two St.                    Towns                     47896       UK          Sales

5     Luke       Sky        #4 Tatooine                       Paris                      45678     France     Billing

6     Princess  Lea        723 Coruscant                  Rome                      45678        Italy      SysEng

7     John       Doe        987 Main St.                     Paris                      78945      France     SysEng

8     Jane       Doe        7658 Oak Ln.                    Crue          VA      45678         US        SysEng

Contracts’ Contacts

Contract                      Client              Contact Name

Work Order 1             BT                   Daffy Duck

Work Order 1              FT                    John Doe, Jane Doe, Princess Lea

Work Order 2              FT                    Elmer Fudd

Work Order 1              IBC                 Buggs Bunny

Work Order2               IBC                 Luke Sky

Work Order 2              IBC                 Darth Vader

Work Order 1              MTM               Daffy Duck

Employee Titles

ID        Title

1          Consultant

2          Analyst

3          Director

Billing Rates

TitleID            Level               Rate                 Currency

1                      1                      150.00             USD

1                      2                      200.00             USD

1                      3                      300.00             USD

2                      1                      50.00               USD

2                      2                      100.00             USD

2                      3                      150.00             USD

3                      1                      250.00             USD

3                      2                      350.00             USD

3                      3                      450.00             USD

Employees

ID        First                 Last     CountryID      Email                           Salary              Title     LevelID          

1          Matthew          Smith        1                 [email protected]       45000              Consultant 1

2          Mark                Jones        1                 [email protected]       94000              Director 1       

3          Luke                Rice          4                 [email protected]            65000              Consultant 2

4          John                 Rich          5                 [email protected]           74000              Consultant 3

5          James               Doe          6                 [email protected]           40000              Analyst 1

6          Peter                Pride         3                 [email protected]        60000              Analyst 2

7          Eric                  Potter       3                 [email protected]       81000              Consultant 3

8          Paul            &nb