Conceptual to relational mappings
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 开发者_JAVA百科 -------------------
|
-------------
| 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
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.
精彩评论