Question Details

[solution] » For this phase, you are required to create an ER Diagram that

Brief item decscription

Step-by-step solution file


Item details:

For this phase, you are required to create an ER Diagram that
More:

 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.


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

 


 

Page 1 of 6

 


 

CSIS 325

 


 

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

 


 

Page 2 of 6

 


 

CSIS 325

 


 

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.

 


 

Page 3 of 6

 


 

CSIS 325

 


 

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

 

North

 

America

 

North

 

America

 

Europe

 

Europe

 

Europe

 

Europe

 


 

Country

 

US

 


 

Holidays

 

11

 


 

WeeklyHours

 

40

 


 

VacationDaysAllowed

 

10

 


 

Canada

 


 

12

 


 

40

 


 

15

 


 

UK

 

France

 

Ireland

 

Italy

 


 

10

 

14

 

10

 

9

 


 

38

 

38

 

38

 

38

 


 

10

 

10

 

15

 

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

 


 

Page 4 of 6

 


 

CSIS 325

 


 

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

 


 

Page 5 of 6

 


 

CSIS 325

 


 

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.

 


 

Page 6 of 6

 


 

 







About this question:
STATUS
Answered
QUALITY
Approved
ANSWER RATING

This question was answered on: Feb 21, 2020

PRICE: $24

Solution~0001018794.zip (18.37 KB)

Buy this answer for only: $24

This attachment is locked

We have a ready expert answer for this paper which you can use for in-depth understanding, research editing or paraphrasing. You can buy it or order for a fresh, original and plagiarism-free copy (Deadline assured. Flexible pricing. TurnItIn Report provided)

Pay using PayPal (No PayPal account Required) or your credit card. All your purchases are securely protected by PayPal.
SiteLock

Need a similar solution fast, written anew from scratch? Place your own custom order

We have top-notch tutors who can help you with your essay at a reasonable cost and then you can simply use that essay as a template to build your own arguments. This we believe is a better way of understanding a problem and makes use of the efficiency of time of the student. New solution orders are original solutions and precise to your writing instruction requirements. Place a New Order using the button below.

Order Now