You are designing a SQL Server 2008 database that the human resources department will use to store information about employees and training courses each employee has completed.
You must design a database schema that will allow information about each employee’s completed training to be recorded in the database, and will track completed training by department without introducing redundant data.
Your data model must also meet the following requirements:
• Many training courses may be offered to employees.
• Each employee may take one or more training courses.
• Each training course may be taken by multiple employees.
• Each employee may take a specific course only once.
• Each employee is assigned to a single department.
• Departments may have multiple employees.
Which actions should you take?
A. Create two tables, Employees and TrainingCourses, each containing a primary key.
Define a foreign key on TrainingCourses that references Employees
B. Create three tables, Employees, Departments, and TrainingCourses, each containing a primary key.
Define a foreign key on Employees that references TrainingCourses.
Define a foreign key on Employees that references Departments.
C. Create four tables, Employees, Departments, TrainingCourses, and TrainingHistory, each containing a primary key.
Define a foreign key on Employees that references TrainingHistory.
Define a foreign key on TrainingCourses that references TrainingHistory.
D. Create four tables, Employees, Departments, TrainingCourses, and TrainingHistory, each containing a primary key.
Define a foreign key on Employees that references Departments.
Define a foreign key on TrainingHistory that references Employees.
Define a foreign key on TrainingHistory that references TrainingCourses
Correct Answer: D
Explanation/Reference:
You can create the data model using the following Transact-SQL:
CREATE TABLE Departments(DeptID int PRIMARY KEY,DeptName varchar(25));
CREATE TABLE TrainingCourses(CourseID int PRIMARY KEY,CourseName varchar(30));
CREATE TABLE Employees (EmployeeID int PRIMARY KEY,FirstName varchar(25),LastName varchar(30),DepartmentID int FOREIGN
KEY REFERENCES Departments(DeptID));
CREATE TABLE TrainingHistory(CourseID int,EmployeeID int,TrainingDate datetime,CONSTRAINT PK_THistoryPRIMARY KEY
CLUSTERED (CourseID, EmployeeID),FOREIGN KEY (CourseID) REFERENCES TrainingCourses(CourseID),FOREIGN KEY (EmployeeID)
REFERENCES Employees(EmployeeID));
The data model in this scenario will resemble the following:
A PRIMARY KEY constraint is used to create a primary key for a table that uniquely identifies each row in the table. A FOREIGN KEY constraint is used to establish a relationship between a primary key or a unique key in one table and another column in the same table or a different table.
Each row in the Departments table represents a single department identified by the DeptID primary key. Each row in the Employees table represents a single employee identified by the EmployeeID primary key. The FOREIGN KEY constraint in the Employees table that references the Departments table ensures that each employee is assigned to a single department, but allows departments to have multiple employees. Each row in the TrainingCourses table represents a single course offered to employees, identified by the CourseID primary key.
To record the fact that an employee has completed a course, you must create an additional table, referred to as a junction table, to represent the many-to-many relationship between Employees and TrainingCourses. The junction table contains a composite primary key consisting of the primary keys of the joined tables, and has FOREIGN KEY constraints on each of the primary key columns to reference the original tables. The junction table may also include any other applicable columns. Each row in the TrainingHistory table represents the fact that a specific employee has taken a specific training course. The composite primary key on the CourseID and EmployeeID columns in the TrainingHistory table ensures that an employee can take a specific course only once, and allows each training course to be taken by multiple employees. The foreign key that references the EmployeeID column in the Employees table ensures that only employees can take the offered training courses. The foreign key that references the CourseID column in the TrainingCourses table ensures that employees may only take courses that are offered.
All of the other options are incorrect because they will introduce redundant data into the data model or do not support the business requirements.
You should not create only two tables, Employees and TrainingCourses. If you only created two tables, redundant data would be introduced. For example, you might store department data in the Employees table. If so, because departments have multiple employees, the DeptName would be redundant for employees within a given department. In a normalized data model, each table contains data for a single entity, such as an employee, department, or course.
You should not create only three tables, Employees, Departments, and TrainingCourses. You must have a TrainingHistory table to represent the fact that a specific employee has taken a specific training course.
