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. Oneexample 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 LevelII.
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. US 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 employees’ timesheets by placing his initials beside his name.
Part ADeliverable:
For this phase, you are
required to create an ER Diagram that will facilitate the development of CMS’s
company-wide database.It should be created using ER Assistant and include
entities and attributes, relationships, and accompanying notes.You should take
a screen shot of your ERD while it is displayed in ER Assistant and paste the
screen shot into a Word document.Make sure that the ERD fits on a single sheet
and is legible.Accompanying notes should 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
should 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:execsp_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, one 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 projectby 11:59 p.m.
(ET) on Monday of Module/Week 5.
No comments:
Post a Comment