To
"7
Supervises
Employee
Time
Sheet
TimeSheetiD
EmplD
1
Eee”
:
Grakecitiame
Ht
SupApprovedDa
H+
-
-
pee
EmpLastWame
ab
OfficeLoc
EmpEmail
H+
Logs
|
HoursWorked
et
EndDate
StanDate
Day
Level
ee
Month
Supervisor
E+
SEES
AssignedTo
Vins
+
TotalHours
i
Project
LH
Records
Man
ages
REEeS.
---
TH
ProjectiD
B+
RISE
In
cludes
ProjectName
of
HourCap
x
ee
ug
Contract
ContractiD
ContractDescripl
Client
ClientID
St
---Supports
ClientLegalName
di
ClientCommonNa
‘
ClientCity
ClientState
'
Contacts
pee
ContactiD
een
ContactFirstNam
oe
ContactLastNam
i
ContactAddress.
:
ContactCity
pees
ContactState
-—
Contact#ip
ContactPhone
Ptt--HasType
ContactEmail
ContaciType
ContactTypelD
ContactTypeNan
CMS PROJECT PART A – PHASE I
ACCOMPANYING NOTES
Part A:
There are 1500 employees. Each employee has a supervisor, who is also an employee. This is where the
“supervises” self-referencing relationship comes into play. One or several employees will end up being the
project manager of the project and will assign another or other employees to work on the project. All projects
are tied back to a contract that signed by the client.
The hours that an employee works on a particular project are tracked. Project hours are not directly tied to the
timesheet, but are logged under the amount of hours that an employee works. Each employee will have a time-
sheet whether hours are worked or not that a supervisor need approve.
Each client has a billing contact, a system engineering contact, and a sales contact. This is shown under the
Contacts entity. Rather than creating multiple tables for “Systems Engineering”, “Sales”, & “Billing” for each
and every client, there will be one table and it will be tied to a table that specifies the type of contact that that
person is.
A project will be completed under a single contract, but a contract may include multiple projects.
A client may have multiple contracts, but a contract can only belong to one client.
The contracts may be written different for different clients. For instance, one client may pay based on a
particular rate of hours worked with a max number of hours that they want worked. On the other hand, other
clients may want a fixed contract which will include a total contract price for all work to be done. The contract
ID and description would likely include this in it.
A single contract may need multiple projects to be done.
All listed attributes for each entity are necessary.
Since there are no non-key columns that determine other non-key columns, this ERD design is in 3NF.
Powered by TCPDF (www.tcpdf.org)
