E-R Diagram

Introduction- In 1976 ,Chen developed the Entity-Relationship Diagrams ,a high-level data stick that is utilizable in developing a imaginationual physique for database . An ER draw is a plat containing entities or items, consanguinitys among them, and evaluates of the entities . The E-R model is unmatched of the best k flat tools for logical database foundation. Within the database community, it is recollected a natural and easy-to-understand way of conceptualizing the structure of database.Claims that permit been made for it include the elicit it is mere(a) and easily understood by non-specialist ,it is easily conceptualized ,the basic constructs (entities and human alliances) atomic number 18 highly intuitive and thus put forward a natural way of re rendering a users nurture requirements , and it is a model that describes a world in terms of entities and connects that is close to sui circuit card for computer naive end users. In E-R diagram the fury is on est ablishing the synopsis a rather than the instances. This is more reclaimable in database design because a database strategy changes rargonly ,w presentas the contents of the entity decides changes frequently.In addition ,the schema is usu al togethery easier to display that the extension of database ,because it is much sm bother Purpose- Entity-relationship diagrams were prototypal proposed as a means of quickly obtaining, with minimum effort, a corking sense of the structure of a database. They are utilize to plan and design a database and to model a systems data. Key Elements Entities- * An entity represents the principle data objects close which information is to be collected. * Collective nouns, or nouns, are usually employ to get up (describe) entities * For example, each mortal in an enterprise is an entity. An entity has a exercise of properties, and the value for about pock of properties whitethorn uniquely get word an entity. For instance, a someone may buzz off a person_id shoes whose value uniquely identifies that person. Entity company- * An entity arrange is a fit(p) of entities of the selfsame(prenominal) type that apportion the same properties, or attributes. The laid of all persons who are guests at a given rim, for example, croupe be defined as the entity raise customer. place- * An attribute is wizard of the various properties that describe the entitys characteristics. These properties usually present a integrity concomitant they are atomic. The designation of an attribute for an entity stack expresses that the database stores similar information concerning each entity in the entity stigmatize however, each entity may catch its own value for each attribute. * Possible attributes of the customer entity exercise set are customer-id, customer-name, customer- channel, 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 use in the ER model canful be categorised 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 polar hand, can be divided into subparts (that is, other attributes). * For example, an attribute name could be structured as a confused attribute consisting of first-name, middle-initial, and last- name. Using composite attributes in a design schema is a good cho ice rink if a user will invite to refer to an entire attribute on some occasions and to unless a comp iodinnt of the attribute on other occasions. Suppose we were to relief for the customer entity-set attributes customer-street and customer-c ity the composite attribute channelize with the attributes, street, city, state, and zip-code. * Note also that a composite attribute may appear as a hierarchy. In the composite attribute brood, 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 nourish a ace value for a particular entity is called a single cherished attribute. * For example, an attribute customer-id is a single precious attribute because for a particular entity it holds a single value. * The attribute that shit 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 some(prenominal) 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 attrib utes 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 establish 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 duration 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 symbolism 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 attri bute because an employee has zero, one or some 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 attribute because it can be stored and accessed from the database. unavailing value- * An attribute takes a null value when an entity does not puzzle a value for it. * The null value may indicate 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 escape valve ,parents and their children. Participation * The association be tween entity sets is referred to as participation that is, the entity sets E1, E2, . . . ,En come in in relationship set R. * The participation of an entity set E in a relationship set R is said to be total if any 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 layer 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 essential be one of the next 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 more. 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 underlying allows us to identify a set of attributes that suffice to distinguish entities from each other. * A super pigment is a set of one or more at tributes 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 satisfactory to distinguish one customer entity from another. Thus, customer-id is a super bet. Similarly, the combination of customer-name and customer-id is a super draw 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 fitting 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 kindle in super keys for which no proper subset is a super key.Such tokenish super keys are called candidate keys. * It is possible that several distinct sets of attributes could action as a candidate key. Suppose that a combination of customer- name and customer-street is sufficient to distinguish among members of the cus tomer 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 does not form a candidate key, since the attribute customer-id alone is a candidate key. The primary winding 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 exotic key. * So a forei gn key is a sphere 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 customer data ,and an ORDERS table that include all customer orders . The intention here is that all orders essential(prenominal) 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 idle 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 terzetto 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 theatrical role the same payment number.Thus, this entity set does not have a primary key it is a worn down entity set. * For a exhausted entity set to be meaningful, it must be associated with another entity set, called the identifying or possessor 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 differentiator 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- tonicitys in E-R Modeling - Usually the following five steps are followed to generate ER models 1. lay the entity set. 2. Identify the relevant attributes. 3. Identify the prime attribute. 4. Find relationships between entity set. 5. Draw a pinpoint ER model. How to Prepare an ERD- Step 1 Let us take a very simple example and we try to arena 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 supra statement as a written document (very short, of course). Step 2 Now we have to prepare the ERD. Before doing that we have to cognitive process the statement a little. We can see that the sentence contains a sk etc. (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 t ell arrow, starting from the subject entity (boy) towards the object entity (ice crank emollient EATS boy Well, fine. Up to this point the ERD shows how boy and ice cream are related. Now, either boy must have a name, address, phone number etc. 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 incorporated in the ERD as connected ovals. tonus maker ADRESS bring up 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 surplus information about the relationships between the boy and the ice cream. FLAVOUR MANUFACTURER NAME ADRESS metre DATE PRICE PHONE ICE CREAM EATS BOY Step 3 We are almost complete now. If you look carefully, we now have defined stru ctures 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 parking lot 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. specialty 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 a ll 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) 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 s et interest rates differently for different customers, oblation 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. abstract * 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 primer of comm on 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 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 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. * strong suit stems from a single entity set it stresss differences among entities within the set by creating distinct lower-level entity sets. T hese lower-level entity sets may have attributes, or may participate in relationships, that do not confine 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. * inductance 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 no t repeated. Difference between Specialization and Generalization No. Specialization Generalization 1 It is a Top Down approach. It is a base 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 divorce (lower- level) entity sets to produce a higher- level entity set. .