E-R Diagram

Introduction:- In 1976 ,Chen developed the Entity-Relationship Diagrams ,a high-level data model that is useful in developing a conceptual design for database . An ER diagram is a diagram containing entities or â€Å"items†, relationships among them, and attributes of the entities . The E-R model is one of the best known tools for logical database design. Within the database community, it is considered a natural and easy-to-understand way of conceptualizing the structure of database.Claims that have been made for it include the following: it is simple and easily understood by non-specialist ,it is easily conceptualized ,the basic constructs (entities and relationships) are highly intuitive and thus provide a natural way of representing a user’s information requirements , and it is a model that describes a world in terms of entities and attributes that is most suitable for computer naive end users. In E-R diagram the emphasis is on representing the schema a rather than t he instances. This is more useful in database design because a database schema changes rarely ,whereas the contents of the entity sets changes frequently.In addition ,the schema is usually easier to display that the extension of database ,because it is much smaller Purpose:- Entity-relationship diagrams were first proposed as a means of quickly obtaining, with minimum effort, a good sense of the structure of a database. They are used to plan and design a database and to model a systems data. Key Elements Entities:- * An entity represents the principle data objects about which information is to be collected. * Collective nouns, or nouns, are usually used to name (describe) entities * For example, each person in an enterprise is an entity. An entity has a set of properties, and the values for some set of properties may uniquely identify an entity. For instance, a person may have a person_id property whose value uniquely identifies that person. Entity Set:- * An entity set is a set of entities of the same type that share the same properties, or attributes. The set of all persons who are customers at a given bank, for example, can be defined as the entity set customer. Attribute:- * An attribute is one of the various properties that describe the entity’s characteristics. These properties usually present a single fact – they are atomic. The designation of an attribute for an entity set expresses that the database stores similar information concerning each entity in the entity set; however, each entity may have its own value for each attribute. * Possible attributes of the customer entity set are customer-id, customer-name, customer-street, and customer-city. Domain (value set):- * For each attribute, there is a set of permitted values, called the domain, or value set, of that attribute. * The domain of attribute customer-name might be the set of all text strings of a certain length.Types of attribute in the E-R model:- The attributes used in the ER mo del can be categorized as 1. Simple or Composite 2. Single Valued or Multi Valued 3. Stored or Derived. 1. Simple or Composite * The attribute which are not divided into subparts are called simple attributes. * For example, an attribute customer-id is a simple attribute. * Composite attributes, on the other hand, can be divided into subparts (that is, other attributes). * For example, an attribute name could be structured as a composite attribute consisting of first-name, middle-initial, and last- name. Using composite attributes in a design schema is a good choice if a user will wish to refer to an entire attribute on some occasions and to only a component of the attribute on other occasions. Suppose we were to substitute for the customer entity-set attributes customer-street and customer-city the composite attribute address with the attributes, street, city, state, and zip-code. * Note also that a composite attribute may appear as a hierarchy. In the composite attribute address, i ts component attribute street can be further divided into street-number, street-name, and apartment-number. 2. Single Valued or Multi Valued The attributes that have a single value for a particular entity is called a single valued attribute. * For example, an attribute customer-id is a single valued attribute because for a particular entity it holds a single value. * The attribute that have multiple valued for a particular entity is called a multi valued attribute. * For example, an attribute phone-number is a multi valued attribute because for a particular customer it holds zero, one or several phone numbers. 3. Stored or Derived * Normally attributes are stored attributes, that is, their values are stored and accessed as such from the database. For example, the attributes name, address and date-of-birth of customer entity set are stored attributes. * However, sometimes attributes’ values are not stored as such, rather they are computed or derived based on some other value. This other value may be stored in the database or obtained some other way. * For example, we may store the name, father-name, address of customers, but age can be computed from date-of-birth. * The advantage of declaring age as derived attribute is that whenever we will access the age, we will get the accurate, current age of employee since it will be computed right at the time when it is being accessedFig Symbol used for different types of attributes in E-R diagram An example diagram representing all types of attributes is given below: 1. The attributes empId, empName and dateHired are simple and single valued. 2. The attribute address is a composite attributes because it can be sub divided into street and houseNo. 3. The attribute emp_Qual is a multi valued attribute because an employee has zero, one or many qualifications. 4. The attribute Experience is a derived attribute because it can be derived from the attribute dateHired. 5.The attributes other than Experience are stored at tribute because it can be stored and accessed from the database. Null value:- * An attribute takes a null value when an entity does not have a value for it. * The null value may indicate â€Å"not applicable†Ã¢â‚¬â€that is, that the value does not exist for the entity. * For example, one may have no middle name. Relationship:- * Relationship is a representation of the fact that certain entities are related to each other. * Verbs are usually used to describe relationships. * For example: Students take Courses – Students and Courses are entities, and take is the relationship.Relationship Set:- * Set of relationships of a given type. * For example: students registered in courses ,passengers booked on flight ,parents and their children. Participation: * The association between entity sets is referred to as participation; that is, the entity sets E1, E2, . . . ,En participate in relationship set R. * The participation of an entity set E in a relationship set R is said to b e total if every entity in E participates in at least one relationship in R. * If only some entities in E participate in relationships in R, the participation of entity set E in relationship R is said to be partial.Degree:- * The number of entity sets that participate in a relationship set is called the degree of the relationship set. * A binary relationship set is of degree 2; a ternary relationship set is of degree 3. Mapping Cardinalities (cardinality ratio) :- * Mapping cardinalities, or cardinality ratios, express the number of entities to which another entity can be associated via a relationship set. * Mapping cardinalities are most useful in describing binary relationship sets, although they can contribute to the description of relationship sets that involve more than two entity sets. For a binary relationship set R between entity sets A and B, the mapping cardinality must be one of the following: a) One to one. An entity in A is associated with at most one entity in B, and a n entity in B is associated with at most one entity in A. b) One to many. An entity in A is associated with any number (zero or more) of entities in B. An entity in B, however, can be associated with at most one entity in A. c) Many to one. An entity in A is associated with at most one entity in B. An entity in B, however, can be associated with any number (zero or more) of entities in A. ) Many to many. An entity in A is associated with any number (zero or more) of entities in B, and an entity in B is associated with any number (zero or more) of entities in A. Fig. one to one Fig. one to many Fig. Many to one Fig. Many to many Key :- * A key allows us to identify a set of attributes that suffice to distinguish entities from each other. * A super key is a set of one or more attributes that, taken collectively, allow us to identify uniquely an entity in the entity set.For example, the customer-id attribute of the entity set customer is sufficient to distinguish one customer entity fr om another. Thus, customer-id is a super key. Similarly, the combination of customer-name and customer-id is a super key for the entity set customer. The customer-name attribute of customer is not a super key, because several people might have the same name. * The concept of a super key is not sufficient for our purposes, since, as we saw, a super key may contain extraneous attributes. * If K is a super key, then so is any superset of K. We are often interested in super keys for which no proper subset is a super key.Such minimal super keys are called candidate keys. * It is possible that several distinct sets of attributes could serve as a candidate key. Suppose that a combination of customer- name and customer-street is sufficient to distinguish among members of the customer entity set. Then, both {customer-id} and {customer-name, customer-street} are candidate keys. Although the attributes customerid and customer-name together can distinguish customer entities, their combination d oes not form a candidate key, since the attribute customer-id alone is a candidate key. The primary key is a candidate key that is chosen by the database designer as the principal means of identifying entities within an entity set. * A key (primary, candidate, and super) is a property of the entity set, rather than of the individual entities. Any two individual entities in the set are prohibited from having the same value on the key attributes at the same time. * The designation of a key represents a constraint in the real-world enterprise being modeled. * Sometimes we may have to work with an attribute that does not have a primary key of its own .To identify its rows ,we have to use the primary attribute of related table. this is known as foreign key. * So a foreign key is a field in a relational table that matches a candidate key of another table. The foreign key can be used to cross-reference tables . For example ,say we have two tables ,a CUSTOMER table that includes all custome r data ,and an ORDERS table that include all customer orders . The intention here is that all orders must be associated with a customer that is already in the CUSTOMER table . To do this,we will place a foreign key in the ORDERS table and have it related to the primary key of the CUSTOMER table.Strong and Weak Entities:- * An entity set may not have sufficient attributes to form a primary key. Such an entity set is termed a weak entity set. An entity set that has a primary key is termed a strong entity set. * As an illustration, consider the entity set payment, which has the three attributes: payment-number, payment-date, and payment-amount. Payment numbers are typically sequential numbers, starting from 1, generated separately for each loan. Thus, although each payment entity is distinct, payments for different loans may share the same payment number.Thus, this entity set does not have a primary key; it is a weak entity set. * For a weak entity set to be meaningful, it must be asso ciated with another entity set, called the identifying or owner entity set. * Although a weak entity set does not have a primary key, we nevertheless need a means of distinguishing among all those entities in the weak entity set that depend on one particular strong entity. The discriminator of a weak entity set is a set of attributes that allows this distinction to be made. The discriminator of a weak entity set is also called the partial key of the entity set.The primary key of a weak entity set is formed by the primary key of the identifying entity set, plus the weak entity set’s discriminator. Symbols used in E-R diagram:- Steps in E-R Modeling :- Usually the following five steps are followed to generate ER models 1. Identify the entity set. 2. Identify the relevant attributes. 3. Identify the prime attribute. 4. Find relationships between entity set. 5. Draw a complete ER model. How to Prepare an ERD:- Step 1 Let us take a very simple example and we try to reach a fully o rganized database from it. Let us look at the following simple statement:A boy eats an ice cream. This is a description of a real word activity, and we may consider the above statement as a written document (very short, of course). Step 2 Now we have to prepare the ERD. Before doing that we have to process the statement a little. We can see that the sentence contains a subject (boy), an object (ice cream) and a verb (eats) that defines the relationship between the subject and the object. Consider the nouns as entities (boy and ice cream) and the verb (eats) as a relationship. To plot them in the diagram, put the nouns within rectangles and the relationship within a diamond.Also, show the relationship with a directed arrow, starting from the subject entity (boy) towards the object entity (ice ICE CREAM EATS BOY Well, fine. Up to this point the ERD shows how boy and ice cream are related. Now, every boy must have a name, address, phone number etc. and every ice cream has a manufacture r, flavor, price etc. Without these the diagram is not complete. These items which we mentioned here are known as attributes, and they must be incorporated in the ERD as connected ovals. FLAVOUR MANUFACTURER ADRESS NAME EATS ICE CREAM CREAM BOY PRICE PHONEBut can only entities have attributes? Certainly not. If we want then the relationship must have their attributes too. These attribute do not inform anything more either about the boy or the ice cream, but they provide additional information about the relationships between the boy and the ice cream. FLAVOUR MANUFACTURER NAME ADRESS TIME DATE PRICE PHONE ICE CREAM EATS BOY Step 3 We are almost complete now. If you look carefully, we now have defined structures for at least three tables like the following: BOYPHONE ADRESS NAME ICE CREAM MANUFACTURER FLAVOUR PRICE EATS TIME DATE However, this is still not a working database, because by definition, database should be â€Å"collection of related tables. † To make them connected, the tables must have some common attributes. If we chose the attribute Name of the Boy table to play the role of the common attribute, then the revised structure of the above tables become something like the following BOY PHONE ADRESS NAME ICE CREAM NAME PRICE FLAVOUR MANUFACTURER NAME TIME DATE EATS This is as complete as it can be.We now have information about the boy, about the ice cream he has eaten and about the date and time when the eating was done. Extended Features of E-R Diagram 1. Specialization †¢An entity set may include sub-groupings of entities that are distinct in some way from other entities in the set. For instance, a subset of entities within an entity set may have attributes that are not shared by all the entities in the entity set. The E-R model provides a means for representing these distinctive entity groupings. †¢ Consider an entity set person, with attributes name, street, and city.A person may be further classified as one of the following: a) cust omer b) employee Each of these person types is described by a set of attributes that includes all the attributes of entity set person plus possibly additional attributes. For example, customer entities may be described further by the attribute customer-id, whereas employee entities may be described further by the attributes employee-id and salary. The process of designating sub-groupings within an entity set is called specialization. The specialization of person allows us to distinguish among persons according to whether they are employees or customers. As another example, suppose the bank wishes to divide accounts into two categories, checking account and savings account. Savings accounts need a minimum balance, but the bank may set interest rates differently for different customers, offering better rates to favored customers. Checking accounts have a fixed interest rate, but offer an overdraft facility; the overdraft-amount on a checking account must be recorded. * In terms of an E-R diagram, specialization is depicted by a triangle component labeled ISA, as Figure shows. The label ISA stands for â€Å"is a† and represents, for example, that a customer â€Å"is a† person.The ISA relationship may also be referred to as a superclass-subclass relationship. Higher- and lower-level entity sets are depicted as regular entity sets i. e. , as rectangles containing the name of the entity set. ISA 2. Generalization * The refinement from an initial entity set into successive levels of entity subgroupings represents a top-down design process in which distinctions are made explicit. The design process may also proceed in a bottom-up manner, in which multiple entity sets are synthesized into a higher-level entity set on the basis of common features.The database designer may have first identified a customer entity set with the attributes name, street, city, and customer-id, and an employee entity set with the attributes name, street, city, employee-id, and sa lary. * There are similarities between the customer entity set and the employee entity set in the sense that they have several attributes in common. This commonality can be expressed by generalization, which is a containment relationship that exists between a higher-level entity set and one or more lower-level entity sets.In our example, person is the higher-level entity set and customer and employee are lower-level entity sets. Higher- and lower-level entity sets also may be designated by the terms superclass and subclass, respectively. The person entity set is the superclass of the customer and employee subclasses. * Specialization stems from a single entity set; it emphasizes differences among entities within the set by creating distinct lower-level entity sets. These lower-level entity sets may have attributes, or may participate in relationships, that do not apply to all the entities in the higher-level entity set.Indeed, the reason a designer applies specialization is to repre sent such distinctive features. If customer and employee neither have attributes that person entities do not have nor participate in different relationships than those in which person entities participate, there would be no need to specialize the person entity set. * Generalization proceeds from the recognition that a number of entity sets share some common features (namely, they are described by the same attributes and participate in the same relationship sets).On the basis of their commonalities, generalization synthesizes these entity sets into a single, higher-level entity set. Generalization is used to emphasize the similarities among lower-level entity sets and to hide the differences; it also permits an economy of representation in that shared attributes are not repeated. Difference between Specialization and Generalization No. | Specialization| Generalization| 1| It is a Top Down approach. | It is a Bottom Up approach. | 2| Specialization stems from a single entityset; it em phasizes differences among entities within the set by creating distinct lower-level entity sets. Generalization proceeds from therecognition that a number of entity sets share some common features (namely, they are described by the same attributes and participate in the same relationship sets). | 3| The process of designating sub-groupingswithin an entity set is calledspecialization. | The process of designating groupingsfrom various entity sets is calledgeneralization. | 4| Specialization is a result of taking a subsetof higher level entity set to form a lower- level entity set. | Generalization is a result of taking theunion of two or more disjoint (lower- level) entity sets to produce a higher- level entity set. | .