Suppose a given user view contains information about employees and projects. Suppose further each employee has a unique EmployeeNum and each project has a unique ProjectNum. Explain how you would implement the relationship between employees and projects in each of the following scenarios:a. Many employees can work on a given project, but each employee can work on only a single project. b. An employee can work on many projects, but each project has a unique employee assigned to it. c. An employee can work on many projects, and a project can be worked on by many employees.

a. Many employees can work on a given project, but each employee can work on only a single project.      

The relationship between Employee and Project in this scenario is one-to-many. This is because a single project is related to many employees means many employees can work on a project.

Employee table has a primary key EmployeeNum and Project table has a primary key ProjectNum.

So to implement the relationship between employees and projects, foreign key is used. A one-to-many relationship allows to establish a relationship between a single record in one table and multiple records in another table. So this relationship is established by including the primary key of Project Table as a foreign key in the Employee Table. This means, Employee table would contain a foreign key that matches the primary key of the Project table, while the Project table would have no foreign key pointing to the Employee table.

Suppose Employee table has following attributes: EmployeeNum, Last Name, First Name, Street, City, Wage

and

Project table has following attributes: Project ID, Name, StartDate, EndDate

Then ProjectNum , the primary key of Project table is used as a foreign key in Employee Table as:

Project (ProjectNum, Name, StartDate, EndDate)

Employee (EmployeeNum,  First Name, Last Name, Street, City, Wage, ProjectNum)

b. An employee can work on many projects, but each project has a unique employee assigned to it.

The relationship between Employee and Project in this scenario is one-to-many.

This is because an employee is related to many projects. The concept of one-to-many relationship and foreign key is described in the previous scenario and the attributes of Employee and Project are also given above.

So this relationship is established by including the primary key of Employee Table as a foreign key in the Project Table. This means, Project table would contain a foreign key that matches the primary key of the Employee table, while the Employee table would have no foreign key pointing to the Project table.

Then EmployeeNum, the primary key of Employee table is used as a foreign key in Project Table as:

Employee (EmployeeNum,  First Name, Last Name, Street, City, Wage,)

Project (ProjectNum ,Name, StartDate, EndDate, EmployeeNum)

c. An employee can work on many projects, and a project can be worked on by many employees.

The relationship between Employee and Project in this scenario is many-to-many. This is because a project can be worked on by many employees. Similarly an employee can work on many projects. This means a project can have many employees to work on and every employee can be linked to a project.

Many-to-many relationships required more than two tables (at least 3). This is because the primary key of one table cannot be added into the other table, or both. If we do this then this will only make a single relationship but here we need "many" relationships.

So in order to establish a relationship between Employee and Project tables another table is created which is used to join and related both these tables. Let us name that table as WorksOn. This table stores two columns: one for each of the primary keys from Employee and Project tables and has separate records for each combination of employee and project. So this will looks as:

Project (ProjectNum, Name, StartDate, EndDate)

Employee (EmployeeNum,  First Name, Last Name, Street, City, Wage, ProjectNum)

WorksOn (EmployeeNum, ProjectNum)

we need a link or picture : )