Schema:
#=PK
Employee (#NIN, fName, lName, address, DOB, sex, salary, taxCode, deptNo)
Department (#deptNo, deptName, managerNIN, businessAreaNo, regionNo)
Project (#projNo, projName, contractPrice, projectManagerNIN, deptNo)
WorksOn (#NIN, #projNo, hoursWorked)
Business (#businessAreaNo, businessAreaName)
Region (#regionNo, regionName)
Where:
- Department contains department details and deptNo is the key. managerNIN identifies the employee who is the manager of the department. There is only one manager for each department.
- Project contains details of the projects in the company and the key is projNo. The project manager is identified by the projectManagerNIN, and the department responsible for the project by deptNo.
- WorksOn contains details of the hours worked by employees on each project and (NIN, projNo) forms the key.
- Business contains names of the business areas and the key is businessAreaNo
- Region contains names of the regions and the key is regionNo
This is the UML diagram that I’ve come up with:
I’m not sure how wrong/correct this is, but I’m particularly concerned with the multiplicities and the relationship that Employee has with WorksOn with Project.
For the record this is just a practice exercise.
0
Several things in your diagram seem to be missing or wrong.
- Do you really want to constrain employees to work on just one project?
- Do you want to allow that employees work on a project of another department? If not, you have to add a constraint/invariant prohibiting this.
- With your current multiplicities for the
Employee–manages–Departmentassociation you require every employee to manage a department. That’s probably not correct.