I'm a student doing an assignment and am having a few problems. The case study is:
The firm is engaged in a variety of project types at any one time, but mainly in the field of civil and mechanical engineering. It has about 35 permanent engineering consultants. When necessary, it either recruits or sub-contracts additional engineers to cover specialist areas for certain projects, e.g. electrical engineers, architects, etc.
Because it takes on work of a variety of types, the firm is organised into divisions for each specialist area. The divisions are:
· Building automation
· Airport facilities
· Marine structures
· General engineering
During requirements analysis for the proposed project management system, the following information has been established:
The system is to store details of all consultants working on EC projects, i.e. consultants permanently employed by Structural Agents and those who are temporary or subcontracted. A consultant may work on several projects at the same time. Each consultant belongs to one division.
Each project has a start and a finish date, total budget cost, actual cost-to-date and %complete. Projects are identified by a unique project code. Additional information stored about projects is the project type (e.g. marine, general, building, etc) and the number of sub-contracted consultants working on the project. From the set of full-time consultants assigned to a project, one consultant is assigned as project manager. At any one time, Structural Agents is engaged in 10-15 projects.
All projects are carried out for a single customer. At any one time a customer may have several projects under way. The firm has about 80 customers on its books, each identified by a customer number.
There is a requirement to record the rates at which project assignments are to be costed. Various rates types are used within the firm, e.g., ‘consultant normal billing’, ‘junior consultant rework billing’, etc. Data stored about rate type includes rate type code, description, rate amount and comments. A rate type code is allocated to each assignment to indicate its costing category. This information can be used to estimate the cost of a project.
On a weekly basis, each consultant returns a timesheet. The timesheet contains a number of so called ‘timesheet entries’. Each timesheet entry details the hours spent by the consultant on a particular project, plus any additional comment the consultant may wish to make about that week’s work.
While coming up with the ER model, I am having problems linking the Timesheet entity into the model. If someone could help me out with how the model should look I would be really greatful.
Sometimes there are other codes, e.g. a "Activity" to distinguish between regular work, overtime, sickness, etc., or various project breakdown structures.
The Timesheet Entry would therefore have M:1 relationhips with Employee and Project (or a child of Project).
Whether you need a Timesheet entity to "own" all the Timesheet Entries for an employee for a week is debatable. In my experience such an entity is not created, as it is really just a "view" of all the Timesheet Entries for an employee and a date range. This allows for flexibility in querying e.g. an employee's hours by day, week, month.
A simple "ERD" would be:
Project --< Project Work Breakdown --< Timesheet Entry >-- Employee