Entity-Relationship Model: Part-1

Entity-Relationship Model

The entity-relationship (E-R) data model describes the real word basic objects, called entity and relationships among those objects. It was developed to design database by allowing an enterprise schema which represents the overall logical structure of a database. The E-R data model is one of several semantic data model which represent the meaning of data. Many database designs use the E-R model because the E-R model is very useful in mapping the meanings and interactions of real word enterprises onto a conceptual schema.

 1.1    Basic Concepts:

   The E-R data model describes three basic concepts: entity sets, relationship sets, and attributes.

 1.1.1 Entity Sets:

       An entity is a ‘thing’ or ‘object’ in the real word that is distinguishable from all other objects. For example, each person in an enterprise is an entity. Entity has a set of properties and the values for some set of properties may uniquely identify an entity.  A person may have a personal-id property whose value uniquely identifies that person. The value of a person-id 880-00000001 would uniquely identify the particular person. Also loans can be an entities and loan No L-01 at Dhaka branch uniquely identifies a loan entity. An entity may concrete like a person or pen, or may abstract like a loan or a holiday or a concept.

    An entity set is a set of entities of the same type that share the some properties, or attributes. The set of all customers at a given bank can be identified as the entity set customer. Also, the entity set loan might represent the set of all loans approved by a particular bank.

     Entity sets don’t need to be disjoint. The employee may an entity set of all employee of the bank and the customer is another entity set of all customers of the bank. A person entity may be an employee, a customer, both or neither.

    An entity is represented by a set of attributes. Attributes are the descriptive property of each member of an entity set. The designation of an attribute for an entity set expresses that the database stores similar information of each entity in the entity set. Each entity may have its own value for each attributes.

   The customer entity set may contain the attributes customer-id, customer-name, customer-street and customer-city etc. Also loan-number, amount may be the attributes of loan entity set.

      Each entity has a value for each of its attributes. A particular customer entity may have the value 880-00000001 for customer-id, the value of Jones for the customer-name, the value Main for customer-street and the value Harrison for customer-city. The customer-id attributes used to uniquely identify customers, since there may be more than one customer with the same name, street, and city.

     For each attribute, there is a set of permitted values called the domain or value set of the attribute. The domain of attribute customer-name might be the set of all text strings of a certain length. Similarly, the domain of attribute loan-number might be the set of all strings of the form “L-n”, where n is the positive integer.

     A database contains a collection of entity set with any number of similar type entities. Fig 2.1 a shows a part of a bank database that consists of two entity set: customer, loan.

      An attribute of an entity set is a function that maps from the entity set into a domain. Since an entity set may have several attributes, each entity can be described by a set of (attribute, data value) pairs, one pair for each attribute of the entity set. For example, a particular customer entity may be described by the set {(cust_id, 880-00000001), (cust-name, Jones), (customer-street, Main), (customer-city, Haqqison)} meaning that the entity describe a person named Jones whose customer-id is 880-00000001 and who resides at Main Street in Harrison. At this point an integration of the abstract schema with the actual enterprise being modeled. The attributes values describe an entity will contribute a significant portion of the data stored in the database.

     An attribute, as used in the E-R model can be categorized by the following attribute types:

 Simple and Composite Attribute: The attributes that are not divided into subparts is known as Simple attributes. On the other hand, those attributes that can be divided into subparts. For example, customer-name is composite attribute because mane may be subdivided as first-name, mid-name and last-name as well as the customer-address is a composite attributes which can be subdivides as street, city, state, zip-code. But cust-id is a simple attribute. Figure 2.2 shows the example of composite attribute.

Single-Valued and Multi-valued attributes: Those attributes which have only one value called single-valued attributes, for example, loan-number attribute hold one value. Consider an employee entity set with the attribute phone-number. An employee may have zero, one or several phone numbers and different employee may have different phone numbers. This type of attribute is called multi-valued attributes.

Derived attributes: The value for this type of attribute can be derived from the values of other related attributes or entities. Suppose the customer entity set also has an attribute age, which indicate the customer’s age. If the customers entity set has an attribute date-of-birth, from which we can calculate the age. Thus age is derived attribute. The derived attribute is not stored, but is compute when required.

 2.1.2 Relationship Sets

       A relationship is an association among several entities. For example, we can define a relationship that associates customer Hayes with loan-no L-15. This relationship specifies that Hayes is a customer with loan-no 15.

            A relationship set is a set of relationships of the same type. Formally, it is a mathematical relation on n ≥ 2 (possible nondistinct ) entity sets. If E1, E2 … En are entity sets, then the relationship set R is a subset of

                        {(e1, e2… en) |e1 ε E1, e2 ε E2… en ε En}

                                                Where (e1, e2… en) is a relationship.

        Consider the two entity sets customer and loan in figure 2.1. We define the relationship set borrower to denote the association between customers and the bank loans that customers have. Consider another example, two entity sets loan and branch. We can define the relationship set loan-branch to denote the association between a bank loan and the branch in which that loan maintained.

     The association between entity sets is referred to as participation; that the entity sets E1, E2 … En   participate in relationship set R. A relationship instance is an E-R schema represents an association between the named entities in the real world enterprise that is being modeled. The individual customer entity Hayes who has customer identifier 677-89-9011 and the loan entity L15 participate in a relationship instance of borrower. This relationship instance represents that in the real world enterprise, the person called Hayes who holds customer-id 677-89-9011 has taken the loan is numbered L15.

      The function that an entity plays in a relationship is called that entity’s role. Since entity sets participating in a relationship set are generally distinct, roles are implicit and are not usually specified. Sometimes the entity sets of a relationship set are not distinct, that is, the same entity set participates in a relationship set more than one different roles, and then it is called recursive relationship set. For example, consider an entity set employee those records information about all employees of the bank. We may have a relationship set works-for that is modeled by ordered pairs of employee entities.  The first employee of a pair takes the role of worker, whereas the second takes the role of manager. In this way, all the relationships of works-for are characterized by (worker, manager) pairs; (manager, worker) pairs are excluded. A relationship may also have attributes called descriptive attributes. Consider a relationships set depositor with entity sets customer and account. We could associate the attribute access-date to that relationship to specify the most recent data on which a customer accessed in an account. The depositor relationship among the entity corresponding to customer Jones and account A-217 has the value “24 March 2013” for attribute access-date, which means the most recent date that Jones accessed account A-217 was “24 March 2013”.

        However, more than one relationship set involving the same entity set. For example, the customer and loan entity sets participate in the relationship set borrower. Additionally, suppose each loan must have another customer who serves as a guarantor for the loan. Then the customer and loan entity sets participate in another relationship set guarantor.

     The number of entity sets that participate in a relationship set is also the degree of the relationship set. A binary relationship set is of degree 2; a ternary relationship set is of degree 3.