Deriving Foreign Keys

Depending on the validity of above population, as answered by the modeller the following outcomes are possible:

Yes,No

Within the population of fact type Project Employee, every project must be unique.

N 1 fom

When the conceptual model is transformed and shown as a logical diagram, the Project Employee fact type is grouped into the Project table. A foreign key is created in the Project table to the Employee table, and the cardinality specifies: An employee can occur in 0 or more projects, and every project must have exactly one employee.

N 1

 

No,Yes

Within the population of Project Employee, every employee must be unique.

1 N fom

When as the conceptual model is transformed and shown as logical diagram, the Project Employee is grouped into the Employee table. A foreign key is created from the Employee table to the Project table, and the cardinality specifies: An project must occur in at least one employee, but can occur in more employee records, and every employee may have a project reference.

1 N

No,No

Within the population of Project Employee, both projects and employees must be unique.

1 1 fom

When the conceptual model is transformed and shown as logical diagram, the Project Employee is grouped into the Project table. A foreign key is created from the Project table to the Employee table, and the cardinality specifies: An employee can occur in 0 or 1 project, and every project must have exactly one employee.

1 1

Yes,Yes

Within the population of Project Employee, every combination of project and employee must be unique. Therefor a project may occur more than once, as do employees.

N M fom

When the conceptual model is transformed and shown as logical diagram, the Project Employee fact type is created as a linking table between Employee and Project to realize the many to many relationship between the two. Two foreign keys are created in the Project Employee table.

Every Project Employee must have 1 employee. And the employee may be in multiple Project Employees.
Every Project Employee must have 1 project. And projects must occur at least once in Project Employee.

N M