SQL Server: Data Modeling

DBA/Developer 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_THistory
PRIMARY 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, DBA/Developer 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. DBA/Developer should not create only two tables, Employees and TrainingCourses. If DBA/Developer only created two tables, redundant data would be introduced. For example, DBA/Developer 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. DBA/Developer should not create only three tables, Employees, Departments, and TrainingCourses. DBA/Developer must have a TrainingHistory table to represent the fact that a specific employee has taken a specific training course.