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:
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhLM551RAW6XhnRmCPWF0izI0ewr0AvEGTTYkB9GA8UnqfHcPJOKH8Kqe9CMu5Pmq2xDbiOuKDt2cq2bvlZMt1n1HLycW1Ml1q4BIiGNhyQf5-rByHEu_VSomDerd9aYBo9Q63w22HT8O0/s1600/SQL+Data++Model.png)
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.
No comments:
Post a Comment