You are the database developer on an instance of SQL Server 2008. You are designing a new database to track projects and project managers within your organization.
You must create a data model representing the relationship between the Project entity and the ProjectManager entity that will support the following requirements:
• Each project may have one or more project managers, depending on the size of the project.
• Each project manager may be assigned to manage a single project or multiple projects.
• Each project has a specific date on which the project is scheduled to begin.
Which action should you take?
A. Create a one-to-one relationship between the Project entity and the ProjectManager entity.
B. Create a many-to-many relationship between the Project entity and the ProjectManager entity.
C. Create a one-to-many relationship from the ProjectManager entity to the Project entity
D. Create a one-to-many relationship from the Project entity to the ProjectManager entity.
Correct Answer: B
Explanation/Reference:
In this scenario, each project may have one or more project managers, and each project manager may be assigned to multiple projects. It means that relationship between projects and project managers is many-to-many. Many-to-many relationships can be implemented by creating another table, referred to as a junction table, to join the entities. The junction table contains a composite primary key consisting of the primary keys of the joined tables, and FOREIGN KEY constraints on each of the primary key columns to reference the original tables. The junction table may also include other applicable columns. For example, in this scenario, you could use the following Transact-SQL statements to create the Project and ProjectManager tables and a junction table, ProjectXPM, to implement the many-tomany relationship:
CREATE TABLE Project (ProjectID int PRIMARY KEY,Description varchar(25),StartDate datetime);
CREATE TABLE ProjectManager (PMID int PRIMARY KEY,LastName varchar(30),FirstName varchar(30));
CREATE TABLE ProjectXPM (ProjectID int,PMID int,CONSTRAINT PK_Project_PMPRIMARY KEY CLUSTERED (ProjectID, PMID),FOREIGN
KEY (ProjectID) REFERENCES Project (ProjectID),FOREIGN KEY (PMID) REFERENCES ProjectManager (PMID));
Each row in the Project table represents a single project within the organization and includes the project’s start date. Each row in the ProjectManager table represents a project manager who may be assigned to projects within the organization. Each row in the ProjectXPM table represents a project manager assigned to a specific project. The ProjectXPM table contains a composite primary key consisting of the combination of ProjectID and PMID. This ensures that the table may include multiple project managers for each project and multiple projects for each project manager. The data model in this scenario would resemble the following:
You should not create a one-to-one relationship between the Project entity and the ProjectManager entity. This data model would allow each project manager to be assigned to only one project and each project to be assigned only one project manager, but would not allow projects to have multiple project managers or allow project managers to manage multiple projects.
You should not create a one-to-many relationship from the ProjectManager entity to the Project entity. This data model would allow each project manager to be assigned to multiple projects, but would only allow each project to be assigned a single project manager.
You should not create a one-to-many relationship from the Project entity to the ProjectManager entity. This data model would allow each project to have one or more project managers, but would allow a project manager to be assigned to only one project.
