E-R Diagram

Introduction- In 1976 ,Chen developed the Entity-Relationship Diagrams ,a high-level data mock up that is ingestionful in developing a conceptual construct for database . An ER diagram is a diagram containing entities or items, births among them, and belongingss of the entities . The E-R model is unity of the best kn accept tools for logical database design. Within the database community, it is considered a natural and easy-to-understand way of conceptualizing the body structure of database.Claims that take over been made for it overwhelm the future(a) it is unsubdivided and easily understood by non-specialist ,it is easily conceptualized ,the basic constructs (entities and kindreds) be highly intuitive and thus translate a natural way of representing a users development requirements , and it is a model that describes a world in wrong of entities and associates that is most capable for computer naive end users. In E-R diagram the emphasis is on representing the lin eation a rather than the instances. This is more useful in database design because a database schema changes rarely ,whereas the contents of the entity shapes changes frequently.In addition ,the schema is usually easier to display that the extension of database ,because it is much smaller Purpose- Entity- affinity diagrams were first proposed as a content 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. severalize Elements Entities- * An entity represents the principle data objects about which in somaation is to be collected. * Collective nouns, or nouns, are usually used to name (describe) entities * For example, each person in an initiative is an entity. An entity has a represent of properties, and the apprises for whatever delimitate of properties may uniquely identify an entity. For instance, a person may have a person_id property whose value uniquely identifie s that person. Entity Set- * An entity dress up is a come down of entities of the same type that share the same properties, or propertys. The bent grass of all persons who are customers at a given bank, for example, understructure be defined as the entity set customer. Attri savee- * An belongings is virtuoso of the various properties that describe the entitys characteristics. These properties usually present a angiotensin-converting enzyme incident 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 o f all text strings of a certain length.Types of attribute in the E-R model- The attributes used in the ER model can be categorized as 1. Simple or building complex 2. oneness Valued or Multi Valued 3. Stored or Derived. 1. Simple or Composite * The attribute which are non divide 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 excessively that a composite attribute may appear as a hierarchy. In the composite attribute address, its 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. * Howev er, sometimes attributes values are not stored as such, rather they are computed or derived found 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 diametrical 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 mevery qualifications. 4. The attribute Experience is a de rived attribute because it can be derived from the attribute dateHired. 5.The attributes other than Experience are stored attribute because it can be stored and accessed from the database. Null value- * An attribute concurs a null value when an entity does not have a value for it. * The null value may testify not applicablethat 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 relat ionship set R. * The participation of an entity set E in a relationship set R is said to be 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(prenominal) 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 o ne of the following a) One to one. An entity in A is associated with at most one entity in B, and an 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 draw allows us to identify a set of attributes that suffice to distinguish entities from each other. * A A-one distinguish is a set of one or more attributes that, taken collectively, allow us to identify uniquely an entity in the entity set.For exa mple, the customer-id attribute of the entity set customer is adapted to distinguish one customer entity from 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 clear-cut 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 t he attributes customerid and customer-name together can distinguish customer entities, their combination does not form a candidate key, since the attribute customer-id alone is a candidate key. The capital key is a candidate key that is chosen by the database condition as the principal means of identifying entities within an entity set. * A key ( first, 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 first step 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 sidestep. this is known as foreign key. * So a foreign key is a field in a relative 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 node table that includes all customer 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 woebegone 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 in series(p) 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 associated 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 sets discriminator. Symbols used in E-R diagram- Steps in E-R Modeling - Usually the following five steps are followed to generate ER models 1. secernate the entity set. 2. Identify the relevant attributes. 3. Identify the prime attribute. 4. Find relationships between entity set. 5. Draw a comple te ER model. How to Prepare an ERD- Step 1 Let us take a very simple example and we try to reach a fully organized database from it. Let us look at the following simple statementA 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 keep in line 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 skitter eat BOY Well, fine. Up to this point the ERD shows how boy and ic e cream are related. Now, every boy must have a name, address, phone number etcetera and every ice cream has a manufacturer, flavor, price etc. Without these the diagram is not complete. These items which we mentioned here are known as attributes, and they must be incarnate in the ERD as connected ovals. FLAVOUR MANUFACTURER ADRESS NAME EATS ICE CREAM CREAM BOY expense 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 supernumerary 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 wo rking 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 share 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 at tributes name, street, and city.A person may be further classified as one of the following a) customer 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 grade 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. evocation * 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 enti ty set with the attributes name, street, city, employee-id, and salary. * There are similarities between the customer entity set and the employee entity set in the sense that they have several attributes in common. This common land 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 accents 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 represent 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 emphasizes 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. .