The Entity-Relationship (E-R) data model, a semantic data model, views the real world as entities and relationships, aiding database design by specifying an enterprise schema. It is useful in mapping real-world enterprise meanings onto conceptual schemas, influencing many database-design tools.
The E-R data model consists of three fundamental concepts: entity sets, relationship sets, and attributes.
Entities are distinguishable objects in the real world, such as people or loans. They have a set of properties that can uniquely identify them. An entity set is a set of entities of the same type that share the same properties or attributes. For example, the set of all persons who are customers at a given bank can be defined as the entity set customer. The individual entities that constitute a set are called the extension of the entity set.
Ex.: Employees Entity Set
Attributes are descriptive properties possessed by each member of an entity set. Each entity has a value for each attribute, and the domain of each attribute is the set of permitted values. In the above Figure, ssn, name and lot are the attributes of Employees entity set.
For each attribute associated with an entity set, we must identify a domain of possible
values. For example, the domain associated with the attribute name of Employees might be the set of 20-character strings. As another example, if the company rates employees on a scale of 1 to 10 and stores ratings in a field called rating, the associated domain consists of integers 1 through 10.
Further, for each entity set, we choose a key. A key is a minimal set of attributes whose values uniquely identify an entity in the set. There could be more than one candidate key; if so, we designate one of them as the primary key.
A database includes a collection of entity sets, each containing any number of entities of the same type. An attribute of an entity set is a function that maps from the entity set into a domain. Each entity can be described by a set of (attribute, data value) pairs, one pair for each attribute of the entity set..
A relationship is an association among two or more entities. Note that several relationship sets might involve the same entity sets. For example, we could also have a Manages relationship set involving Employees and Departments. A relationship can also have descriptive attributes. Descriptive attributes are used to record information about the relationship, rather than about any one of the participating entities; for example, we may wish to record that Mr. X works in the pharmacy department as of January 1991.
Ex. Works_In Relationship between Employees and Departments Entities.
An instance of a relationship set is a set of relationships. Intuitively, an instance can be thought of as a `snapshot' of the relationship set at some instant in time. Each Employees entity is denoted by its ssn, and each Departments entity is denoted by its did, for simplicity. The since value is shown beside each relationship.
As another example of an ER diagram, suppose that each department has offices in several locations and we want to record the locations at which each employee works. This relationship is ternary because we must record an association between an employee, a department, and a location. The ER diagram for this variant of Works_In, which we call Works_In2.
The entity sets that participate in a relationship set need not be distinct; sometimes a relationship might involve two entities in the same entity set. Since employees report to other employees, every relationship in Reports To is of the form (emp1; emp2), where both emp1 and emp2 are entities in Employees. However, they play different roles: emp1 reports to the managing employee emp2, which is reflected in the role indicators supervisor and subordinate. For example, the Reports_To relationship set has attributes corresponding to the ssn of the supervisor and the ssn of the subordinate, and the names of these attributes are supervisor_ssn and subordinate_ssn.
Participation Constraints
The key constraint on Manages tells us that a department has at most one manager. Let us say that every department is required to have a manager. This requirement is an example of a participation constraint; the participation of the entity set Departments in the relationship set Manages is said to be total. A participation that is not total is said to be partial. As an example, the participation of the entity set Employees in Manages is partial, since not every employee gets to manage a department.
Revisiting the Works_In relationship set, it is natural to expect that each employee works in at least one department and that each department has at least one employee. This means that the participation of both Employees and Departments in Works_In is total.
The ER diagram shows both the Manages and Works_In relationship sets and all the given constraints. If the participation of an entity set in a relationship set is total, the two are connected by a thick line; independently, the presence of an arrow indicates a key constraint. The instances of Works_In and Manages satisfy all the constraints.
Weak Entities
Thus far, we have assumed that the attributes associated with an entity set include a
key. This assumption does not always hold. For example, suppose that employees can
purchase insurance policies to cover their dependents. We wish to record information
about policies, including who is covered by each policy, but this information is really
our only interest in the dependents of an employee. If an employee quits, any policy
owned by the employee is terminated and we want to delete all the relevant policy and
dependent information from the database.
We might choose to identify a dependent by name alone in this situation, since it is reasonable to expect that the dependents of a given employee have different names. Thus
the attributes of the Dependents entity set might be pname and age. The attribute
pname does not identify a dependent uniquely. Recall that the key for Employees is ssn; thus we might have two employees called Mr. X, and each might have a son called Joe.
Dependents is an example of a weak entity set. A weak entity can be identified
uniquely only by considering some of its attributes in conjunction with the primary
key of another entity, which is called the identifying owner.
The following restrictions must hold:
- The owner entity set and the weak entity set must participate in a one-to-many relationship set (one owner entity is associated with one or more weak entities, but each weak entity has a single owner). This relationship set is called the identifying relationship set of the weak entity set.
- The weak entity set must have total participation in the identifying relationship set.
For example, a Dependents entity can be identified uniquely only if we take the key
of the owning Employees entity and the pname of the Dependents entity. The set of
attributes of a weak entity set that uniquely identify a weak entity for a given owner
entity is called a partial key of the weak entity set. In our example pname is a partial
key for Dependents.
The Dependents weak entity set and its relationship to Employees is shown in following Figure. The total participation of Dependents in Policy is indicated by linking them with a dark line. The arrow from Dependents to Policy indicates that each Dependents
entity appears in at most one (indeed, exactly one, because of the participation constraint)
Policy relationship.
To underscore the fact that Dependents is a weak entity
and Policy is its identifying relationship, we draw both with dark lines. To indicate
that pname is a partial key for Dependents, we underline it using a broken line. This
means that there may well be two dependents with the same pname value.
The E-R model integrates abstract schema with actual enterprise data, focusing on attribute values. Attributes can be simple, composite, single-valued, derived, or null.
- Simple attributes are not divided into sub-parts, while composite attributes can be divided into sub-parts. Composite attributes group related attributes together, making modeling cleaner.
- Single-valued attributes have a single value for a particular entity, while multi-valued attributes have a set of values for a specific entity. Derived attributes can be derived from other related attributes or entities, such as loans-held or age. Null values indicate that an attribute does not exist or are unknown.
A database for a banking enterprise may include multiple entity sets, such as accounts, branch entities, or customer entities. Each entity set may be described by attributes such as branch-name, branch-city, and assets.
An attribute can be null when an entity does not have a value, indicating "not applicable" or unknown values. For example, a customer's name may be null, while an apartment number may be missing or unknown.
In a banking enterprise, database management may include multiple entity sets, such as accounts, branches, and accounts. Attributes play a crucial role in ensuring accurate and efficient data management.
Posts
No comments:
Post a Comment