Entity relationship

Question

Hi,

I have started develping database for machineries performance mgt system Facts:
1.A machine(platNo,model,name) can work on several cane fields(fieldNo,fieldNo) - machine vs field 2.Many machineries can work on a cane field 3.A machine can do tasks for many userDept(deptId,deptName) 4.A userDept demands several machines for its activity{A task can be done on several cane fields; plowing,land shaping,etc can be done on field 1, 2, 3...- task vs field, Many tasks can be done on a field; on field 1 , plowing ,harrowing,... can be done - task vs field?/?} 5.A machine can do for many userDept; lpcd(using its machine) can do the same type of work (e.g.: plowing) for plantation, rehabilitation and expansion projects. - task vs userDept 6.Much type of tasks can be done for a userDept; plowing, harrowing,... can be done for plantation- task vs user 7.A machine works in three shifts(1 -to- 3)

Problem : please help me in designing the ER!! Thanks, Dejene

Answer 1

So let us see what you tried and what the exact problem is? Which entities do you propose? Which attributes and which relations?

Answer 2

I'll assume platNo can be used as a unique identifier for a machine. There are quite a few possibilities depending on rules that you have left ambiguous - e.g. some of the following relations may not be required or may need to be modified:

MACHINE (platNo, model, name) - represents each machine

FIELD (fieldNo) - represents each cane field

TASK (taskId, taskName) - represents the various tasks (e.g. plowing, harrowing) that can be done by any machine

USERDEPT (deptId, deptName) - represents each department

PROJECT (projId, projName, deptId) - represents each project for each department (e.g. plantation, rehabilitation, expansion)

SHIFT (shiftNo) - represents the shifts that any machine might be assigned to

MACHINE_FIELD (platNo, fieldNo) - represents the fact that a particular machine can work on a particular cane field

MACHINE_TASK (platNo, taskId) - represents the fact that a particular machine can perform a particular task

PROJECT_REQUIREMENT (projId, taskId) - represents the fact that a particular project (for a particular department) requires a particular task

MACHINE_ASSIGNMENT (projId, taskId, shiftNo, platNo) - represents the fact that a particular machine has been assigned to perform a particular task on a given shift