Conceptual to relational mappings

Question

this is a homework question. I already have my own answer, but I am not sure if its correct, need your guys advise. Here is the conceptual diagram:

-------------
| Employer  |
-------------------
| EmployerID, Name|
-------------------
    |
    |- supervise
    |
------------------           ---------------  ---------
| Running Project | ------- |Year, duration| | Project |
-------------------          --------------- -----------
    |                                        | ProjectID, ProjectName,|
    |- works                                 -------------------
    |
-------------
| Employees |
-------------
| EmpID, Name|
--------------

Here is my relational schema:

Employer(EmployerID, name) -> Primary key is EmployerID

Employees(EmpID, name) -> Primary key is EmpID

I think the employer and employees entity should be correct, as its normal class. BUt I am not sure the Running project and project entity, which is the association class and the dependent class. Here is my answer:

RUnning Project (EmpID, Year, Duration, ProjectID, ProjectName) -> all primary keys

Project(ProjectID, ProjectName) -> all primary keys

Answer 1

Here is what I understood from the relationship,

• You have 3 major entities: Employer, Employee, Project
• Employer attributes: EmployerId, Name
• Employee attributes: EmployeeId, Name
• Project attributes: ProjectId, Name, Duration, Year
• One employer supervises more than one projects.
• One employee can work for more than one projects.

In that case, one can identify 3 root tables: Employer, Employee and Project. The tables that are used to relate the root tables are:

• EmployerProject (EmployerId, ProjectId)
• EmployeeProject (EmployeeId, ProjectId)

Again, I have a suggestion for the Project table, the year attribute seems to the year (or date) when the project was started. If so, then the Duration attribute is a derived attribute (CurrentDate - StartDate). This derived attribute need not be included in the table.