Relationship Types, Relationship Sets, Roles, and Structural Constraints




There are several implicit relationships among the various entity types. In fact, whenever an attribute of one entity type refers to another entity type, some relationship exists. 
For example,

  1. the attribute Manager of DEPARTMENT refers to an employee who manages the department;
  2. the attribute Controlling_department of PROJECT refers to the department that controls the project;
  3. the attribute Supervisor of EMPLOYEE refers to another employee (the one who supervises this employee);
  4. the attribute Department of EMPLOYEE refers to the department for which the employee works; and so on.

In the ER model, these references should not be represented as attributes but as relationships, which are discussed in this section. The COMPANY database schema will be refined to represent relationships explicitly. In the initial design of entity types, relationships are typically captured in the form of attributes. As the design is refined, these attributes get converted into relationships between entity types. 

Relationship Types, Sets, and Instances

A relationship type R among n entity types E1, E2, ..., En defines a set of associations—or a relationship set—among entities from these entity types. As for the case of entity types and entity sets, a relationship type and its corresponding relationship set are customarily referred to by the same name, R.

Mathematically, the relationship set R is a set of relationship instances ri , where each ri associates n individual entities (e1, e2, ..., en), and each entity ej in ri is a member of entity set Ej , 1 ? j ? n. Hence, a relationship set is a mathematical relation on E1, E2, ..., En; alternatively, it can be defined as a subset of the Cartesian product of the entity sets E1 × E2 × ... × En.

Topics You May Be Interested In
Subclasses, Superclasses, And Inheritance Relational Query Languages
Specialization And Generalization Overview Of The Sql Query Language
Using High-level Conceptual Data Models For Database Design Sql Data Definition
Relational Databases Additional Basic Operations-string Operations
Introduction To Database Security Issues Relationship Types, Relationship Sets, Roles, And Structural Constraints

Each of the entity types E1, E 2, ..., En is said to participate in the relationship type R; similarly, each of the individual entities e1, e2, ..., en is said to participate in the relationship instance ri = (e1, e2, ..., en). Informally, each relationship instance ri in R is an association of entities, where the association includes exactly one entity from each participating entity type. Each such relationship instance ri represents the fact that the entities participating in ri are related in some way in the corresponding miniworld situation.

For example, consider a relationship type WORKS_FOR between the two entity types EMPLOYEE and DEPARTMENT, which associates each employee with the department for which the employee works in the corresponding entity set. Each relationship instance in the relationship set WORKS_FOR associates one EMPLOYEE entity and one DEPARTMENT entity. Figure illustrates this example, where each relationship .

 

Relationship Types, Relationship Sets, Roles, and Structural Constraints

Topics You May Be Interested In
Using High-level Conceptual Data Models For Database Design Additional Basic Operations-the Rename Operation
Using High-level Conceptual Data Models For Database Design A Sample Database Application
History Of Database Systems Initial Conceptual Design Of The Company Database
Schema Diagrams Refining The Er Design For The Company Database
Overview Of Object Database Concepts Relationship Types Of Degree Higher Than Two

 

instance ri is shown connected to the EMPLOYEE and DEPARTMENT entities that participate in ri . In the miniworld represented by , employees e1,e3, and e6 work for department d1; employees e2 and e4 work for department d2; and employees e5 and e7 work for department d3. In ER diagrams, relationship types are displayed as diamond-shaped boxes, which are connected by straight lines to the rectangular boxes representing the participating entity types. The relationship name is displayed in the diamond-shaped box.

Relationship Degree, Role Names, and Recursive Relationships

Degree of a Relationship Type. The degree of a relationship type is the number of participating entity types. Hence, the WORKS_FOR relationship is of degree two. A relationship type of degree two is called binary, and one of degree three is called ternary. An example of a ternary relationship is SUPPLY, shown in Figure 7.10, where each relationship instance ri associates three entities—a supplier s, a part p, and a project j—whenever s supplies part p to project j. Relationships can generally be of any degree, but the ones most common are binary relationships. Higherdegree relationships are generally more complex than binary relationships; we characterize them further. 

 

Topics You May Be Interested In
View Of Data Xml: Extensible Markup Language
A Sample University Eer Schema, Design Choices, And Formal Definitions Sql Data Definition
Example Of Other Notation: Representing Specialization And Generalization In Uml Class Diagrams Basic Structure Of Sql Queries
Structure Of Relational Databases Attribute Specification In Select Clause
Discretionary Access Control Based On Granting And Revoking Privileges Example Of Notation- Uml Class Diagrams

Relationship Types, Relationship Sets, Roles, and Structural Constraints

 

Relationships as Attributes. It is sometimes convenient to think of a binary relationship type in terms of attributes, as we discussed in Section 7.3.3. Consider the WORKS_FOR relationship type in Figure 7.9. One can think of an attribute called Department of the EMPLOYEE entity type, where the value of Department for each EMPLOYEE entity is (a reference to) the DEPARTMENT entity for which that employee works. Hence, the value set for this Department attribute is the set of all DEPARTMENT entities, which is the DEPARTMENT entity set. This is what we did in Figure 7.8 when we specified the initial design of the entity type EMPLOYEE for the COMPANY database. However, when we think of a binary relationship as an attribute, we always have two options. In this example, the alternative is to think of a multivalued attribute Employee of the entity type DEPARTMENT whose values for each DEPARTMENT entity is the set of EMPLOYEE entities who work for that department. The value set of this Employee attribute is the power set of the EMPLOYEE entity set. Either of these two attributes—Department of EMPLOYEE or Employee of DEPARTMENT—can represent the WORKS_FOR relationship type. If both are represented, they are constrained to be inverses of each other.

Role Names and Recursive Relationships. Each entity type that participates in a relationship type plays a particular role in the relationship. The role name signifies the role that a participating entity from the entity type plays in each relationship instance, and helps to explain what the relationship means. For example, in the WORKS_FOR relationship type, EMPLOYEE plays the role of employee or worker and DEPARTMENT plays the role of department or employer. Role names are not technically necessary in relationship types where all the participating entity types are distinct, since each participating entity type name can be used as the role name. However, in some cases the same entity type participates more than once in a relationship type in different roles. In such cases the role name becomes essential for distinguishing the meaning of the role that each participating entity plays. Such relationship types are called recursive relationships. Figure 7.11 shows an example. The SUPERVISION relationship type relates an employee to a supervisor, where both employee and supervisor entities are members of the same EMPLOYEE entity set. Hence, the EMPLOYEE entity type participates twice in SUPERVISION: once in the role of supervisor (or boss), and once in the role of supervisee (or subordinate). Each relationship instance ri in SUPERVISION associates two employee entities ej and ek, one of which plays the role of supervisor and the other the role of supervisee. In Figure 7.11, the lines marked ‘1’ represent the supervisor role, and those marked ‘2’ represent the supervisee role; hence, e1 supervises e2 and e3,e4 supervises e6 and e7, and e5 supervises e1 and e4. In this example, each relationship instance must be connected with two lines, one marked with ‘1’ (supervisor) and the other with ‘2’ (supervisee).

Topics You May Be Interested In
Characteristics Of The Database Approach Relational Databases
Specialization And Generalization Database Schema
Constraints And Characteristics Of Specialization And Generalization Hierarchies Entity Types, Entity Sets, Keys, And Value Sets-2
Database Security Relationship Types, Relationship Sets, Roles, And Structural Constraints
Database Languages Er Diagrams, Naming Conventions, And Design Issues

 

Relationship Types, Relationship Sets, Roles, and Structural Constraints

 

Constraints on Binary Relationship Types

Relationship types usually have certain constraints that limit the possible combinations of entities that may participate in the corresponding relationship set. These constraints are determined from the miniworld situation that the relationships represent. For example, in Figure 7.9, if the company has a rule that each employee must work for exactly one department, then we would like to describe this constraint in the schema. We can distinguish two main types of binary relationship constraints: cardinality ratio and participation.

Topics You May Be Interested In
Database-system Applications Introduction To Database Security Issues
Using High-level Conceptual Data Models For Database Design Relationship Types, Relationship Sets, Roles, And Structural Constraints
Introduction To Transaction Processing Attribute Specification In Select Clause
Database Architecture Er Diagrams, Naming Conventions, And Design Issues
Introduction To Database Security Issues Relationship Types Of Degree Higher Than Two

 

Cardinality Ratios for Binary Relationships. The cardinality ratio for a binary relationship specifies the maximum number of relationship instances that an entity can participate in. For example, in the WORKS_FOR binary relationship type, DEPARTMENT:EMPLOYEE is of cardinality ratio 1:N, meaning that each department can be related to (that is, employs) any number of employees,9 but an employee can be related to (work for) only one department. This means that for this particular relationship WORKS_FOR, a particular department entity can be related to any number of employees (N indicates there is no maximum number). On the other hand, an employee can be related to a maximum of one department. The possible cardinality ratios for binary relationship types are 1:1, 1:N, N:1, and M:N. An example of a 1:1 binary relationship is MANAGES (Figure 7.12), which relates a department entity to the employee who manages that department. This represents the miniworld constraints that—at any point in time—an employee can manage one department only and a department can have one manager only. The relationship type WORKS_ON (Figure 7.13) is of cardinality ratio M:N, because the mini world rule is that an employee can work on several projects and a project can have several employees. Cardinality ratios for binary relationships are represented on ER diagrams by displaying 1, M, and N on the diamonds as shown in Figure 7.2. Notice that in this notation, we can either specify no maximum (N) or a maximum of one (1) on participation. An alternative notation (see Section 7.7.4) allows the designer to specify a specific maximum number on participation, such as 4 or 5.

 

Relationship Types, Relationship Sets, Roles, and Structural Constraints

Topics You May Be Interested In
Characteristics Of The Database Approach The Natural Join
Modeling Of Union Types Using Categories Relationship Types, Relationship Sets, Roles, And Structural Constraints
A Sample University Eer Schema, Design Choices, And Formal Definitions Attribute Specification In Select Clause
Database Security Sensitive Data And Types Of Disclosures
Specialty Databases Set Operations-introduction

Relationship Types, Relationship Sets, Roles, and Structural Constraints

 

Participation Constraints and Existence Dependencies. The participation constraint specifies whether the existence of an entity depends on its being related to another entity via the relationship type. This constraint specifies the minimum number of relationship instances that each entity can participate in, and is sometimes called the minimum cardinality constraint. There are two types of participation constraints—total and partial—that we illustrate by example. If a company policy states that every employee must work for a department, then an employee entity can exist only if it participates in at least one WORKS_FOR relationship instance (Figure 7.9). Thus, the participation of EMPLOYEE in WORKS_FOR is called total participation, meaning that every entity in the total set of employee entities must be related to a department entity via WORKS_FOR. Total participation is also called existence dependency. In Figure 7.12 we do not expect every employee to manage a department, so the participation of EMPLOYEE in the MANAGES relationship type is partial, meaning that some or part of the set of employee entities are related to some department entity via MANAGES, but not necessarily all. We will refer to the cardinality ratio and participation constraints, taken together, as the structural constraints of a relationship type. In ER diagrams, total participation (or existence dependency) is displayed as a double line connecting the participating entity type to the relationship, whereas partial participation is represented by a single line (see Figure 7.2). Notice that in this notation, we can either specify no minimum (partial participation) or a minimum of one (total participation). The alternative notation (see Section 7.7.4) allows the designer to specify a specific minimum number on participation in the relationship, such as 4 or 5.

Attributes of Relationship Types

Relationship types can also have attributes, similar to those of entity types. For example, to record the number of hours per week that an employee works on a particular project, we can include an attribute Hours for the WORKS_ON relationship type in Figure 7.13. Another example is to include the date on which a manager started managing a department via an attribute Start_date for the MANAGES relationship type in Figure 7.12. Notice that attributes of 1:1 or 1:N relationship types can be migrated to one of the participating entity types. For example, the Start_date attribute for the MANAGES relationship can be an attribute of either EMPLOYEE or DEPARTMENT, although conceptually it belongs to MANAGES. This is because MANAGES is a 1:1 relationship, so every department or employee entity participates in at most one relationship instance. Hence, the value of the Start_date attribute can be determined separately, either by the participating department entity or by the participating employee (manager) entity. For a 1:N relationship type, a relationship attribute can be migrated only to the entity type on the N-side of the relationship. For example, in Figure 7.9, if the WORKS_FOR relationship also has an attribute Start_date that indicates when an employee started working for a department, this attribute can be included as an attribute of EMPLOYEE. This is because each employee works for only one department, and hence participates in at most one relationship instance in WORKS_FOR. In both 1:1 and 1:N relationship types, the decision where to place a relationship attribute—as a relationship type attribute or as an attribute of a participating entity type—is determined subjectively by the schema designer. For M:N relationship types, some attributes may be determined by the combination of participating entities in a relationship instance, not by any single entity. Such attributes must be specified as relationship attributes. An example is the Hours attribute of the M:N relationship WORKS_ON (Figure 7.13); the number of hours per week an employee currently works on a project is determined by an employeeproject combination and not separately by either entity. 

Topics You May Be Interested In
Database-system Applications Relational Databases
Using High-level Conceptual Data Models For Database Design Introduction To Database Security Issues
Introduction To Transaction Processing Overview Of Object Database Concepts
Database Languages A Sample Database Application
Database Design Control Measures


Frequently Asked Questions

+
Ans: An entity type DEPARTMENT with attributes Name, Number, Locations, Manager, and Manager_start_date. Locations is the only multivalued attribute. We can specify that both Name and Number are (separate) key attributes because each was specified to be unique view more..
+
Ans: A database usually contains groups of entities that are similar. For example, a company employing hundreds of employees may want to store similar information concerning each of the employees. These employee entities share the same attributes, but each entity has its own value(s) for each attribut view more..
+
Ans: Entities and Their Attributes. The basic object that the ER model represents is an entity, which is a thing in the real world with an independent existence. An entity may be an object with a physical existence (for example, a particular person, car, house, or employee) view more..
+
Ans: There are several implicit relationships among the various entity types. In fact, whenever an attribute of one entity type refers to another entity type, some relationship exists. For example, the attribute Manager of DEPARTMENT refers to an employee who manages the department; the attribute view more..
+
Ans: Select clause uses. view more..
+
Ans: Entity types that do not have key attributes of their own are called weak entity types. In contrast,regular entity types that do have a key attribute—which include all the examples discussed so far—are called strong entity types view more..
+
Ans: If some cardinality ratio or dependency cannot be determined from the requirements, the users must be questioned further to determine these structural constraints view more..
+
Ans: The individual entity instances in an entity set and the individual relationship instances in a relationship set. In ER diagrams the emphasis is on representing the schemas rather than the instances. view more..
+
Ans: The UML methodology is being used extensively in software design and has many types of diagrams for various software design purposes. We only briefly present the basics of UML class diagrams here, and compare them with ER diagrams view more..
+
Ans: we defined the degree of a relationship type as the number of participating entity types and called a relationship type of degree two binary and a relationship type of degree three ternary view more..
+
Ans: SQL offers the user some control over the order in which tuples in a relation are displayed. view more..
+
Ans: The rapid advancement of the use of information technology (IT) in industry, government, and academia raises challenging questions and problems regarding the protection and use of personal information. Questions of who has what rights to information about individuals for which purposes become more important as we move toward a world in which it is technically possible to know just about anything about anyone. view more..
+
Ans: Sensitivity of data is a measure of the importance assigned to the data by its owner, for the purpose of denoting its need for protection. Some databases contain only sensitive data while other databases may contain no sensitive data at all. view more..
+
Ans: Whenever a person or a group of persons needs to access a database system, the individual or group must first apply for a user account. The DBA will then create a new account number and password for the user if there is a legitimate need to access the database. view more..
+
Ans: The database administrator (DBA) is the central authority for managing a database system. The DBA’s responsibilities include granting privileges to users who need to use the system and classifying users and data in accordance with the policy of the organization view more..
+
Ans: SQL includes a between comparison operator to simplify where clauses that specify that a value be less than or equal to some value and greater than or equal to some other value. view more..
+
Ans: Four main control measures are used to provide security of data in databases: Access control, Inference control, Flow control, Data encryption view more..
+
Ans: The SQL operations union, intersect, and except operate on relations. view more..




Rating - 3/5
550 views

Advertisements