Feb 12
9
Explanation about Key Constraint in ER Model
Consider the Works_In relationship shown in the following figure. An employee can work in several departments, and a department can have several employees, as illustrated in the Works_In instance as shown in the following figure. Employee 231-31-5368 has worked in Department 51 since 3/3/93 and in Department 56 since 2/2/92. Department 51 has two employees.
Works_In Relationship set
Works_In Instance
Now consider another relationship set called Manages between the Employees and Departments entity sets such that each department has at most one manager, although a single employee is allowed to manage more than one department. The restriction that each department has at most one manager is an example of a key constraint, and it implies that each Departments entity appears in at most one Manages relationship in any allowable instance of Manages. This restriction is indicated in the ER diagram of the following figure by using an arrow from Departments to Manages. Intuitively, the arrow states that given a Departments entity, we can uniquely determine the Manages relationship in which it appears.
Key Constraint on Manages
An instance of the Manages relationship set is shown in the following figure. While this is also a potential instance for the Works_In relationship set, the instance of Works_In shown in the above figure violates the key constraint on Manages.
Instance of Manages Relationship Set
A relationship set like Manages is sometimes said to be one-to-many, to indicate that one employee can be associated with many departments (in the capacity of a manager), whereas each department can be associated with at most one employee as its manager. In contrast, the Works_In relationship set, in which an employee is allowed to work in several departments and a department is allowed to have several employees, is said to be many-to-many. If we add the restriction that each employee can manage at most one department to the Manages relationship set, which would be indicated by adding an arrow from Employees to Manages in the above figure, we have a one-to-one relationship set.
