E-R Diagram

Introduction- In 1976 ,Chen create the Entity- kin Diagrams ,a high- aim data model that is useful in developing a conceptual design for database . An ER diagram is a diagram containing entities or items, kins among them, and portions 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 gestate been made for it imply the next it is simple and easily understood by non-specialist ,it is easily conceptualized ,the basic constructs (entities and affinitys) argon highly intuitive and thus provide a natural way of representing a users information requirements , and it is a model that describes a world in call of entities and charges that is most sui prorogue for computer ingenuous end users. In E-R diagram the emphasis is on representing the dodge a rather than the instances. This is more useful in d atabase design because a database schema changes r bely ,whereas the contents of the entity adapts changes frequently.In addition ,the schema is usually easier to display that the offstage of database ,because it is much smaller Purpose- Entity- consanguinity diagrams were first proposed as a featureor of quickly obtaining, with minimum effort, a good sense of the structure of a database. They be used to plan and design a database and to model a systems data. Key Elements Entities- * An entity represents the principle data targets some which information is to be collected. * corporal nouns, or nouns, be usually used to name (describe) entities * For precedent, distributively(prenominal) psyche in an enterprise is an entity. An entity has a grouchyise of properties, and the cling tos for m any clip of properties whitethorn uniquely identify an entity. For instance, a person may take hold a person_id property whose mensurate uniquely identifies that person. Entity Set- * An entity garment is a luck of entities of the equivalent type that shargon the alike(p) properties, or holdings. The educate of all persons who argon clients at a given bank, for example, post be defined as the entity go down client. Attribute- * An designate is one of the respective(a) properties that describe the entitys characteristics. These properties usually present a one fact they are atomic. The designation of an attribute for an entity put in expresses that the database stores similar information concerning each(prenominal) entity in the entity pose however, each entity may turn out its own value for each attribute. * Possible attributes of the node entity desex are guest-id, customer-name, customer-street, and customer-city. Domain (value sight)- * For each attribute, there is a prepare 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 model basin be categorized as 1. wide-eyed or Composite 2. sensation Valued or Multi Valued 3. Stored or Derived. 1. Simple or Composite * The attribute which are non divided into subparts are called simple attributes. * For example, an attribute customer-id is a simple attribute. * Composite attributes, on the opposite(a) hand, substructure be divided into subparts (that is, other attributes). * For example, an attribute name could be structured as a compound attribute consisting of first-name, middle-initial, and last- name. Using composite attributes in a design schema is a good pickax if a user will wish to refer to an entire attribute on round occasions and to only a dowery 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 as well as that a composite attribute may appear as a hierarchy. In the composite attribute address, its component attribute street can be elevate divided into street- payoff, street-name, and apartment-number. 2. Single Valued or Multi Valued The attributes that pull in a single value for a particular entity is called a single cherished 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 * ordinarily attributes are stored attributes, that is, their values are stored and entryed as such from the database. For example, the attributes name, address and date-of-birth of customer entity set are stored attribu tes. * However, some metres 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 castigate 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 umteen qualifications. 4. The attribute Ex perience 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. Null value- * An attribute hit the bookss a trifling value when an entity does not have 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 affinitys. * For example Students take Courses Students and Courses are entities, and take is the alliance.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 amid entity sets is referred to as club 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 e genuinely entity in E records 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 both entity sets. For a binary relationship set R among entity sets A and B, the mapping cardinality essential be one of the adjacent 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 cite allows us to identify a set of attributes that suffice to distinguish entities from each other. * A super light upon 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 from another. Thus, customer-id is a super recognize. Similarly, the combination of customer-name and customer-id is a super get word for the entity set customer. The customer-name attribute of customer is not a super tonality, because several hoi polloi 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 have-to doe withed in super keys for which no proper subset is a super key.Such minimal super keys are called prognosis keys. * It is possible that several distinct sets of attributes could serve as a prospect 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 does not form a candidate key, since the attribute customer-id solely is a candidate key. The indigenous key is a candidate key that is chosen by the database designer as the principal heart and soul of identifying entities at heart an entity set. * A key ( uncreated, candidate, and super) is a property of the entity set, rather than of the individual entities. whatsoever ii 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 primordial 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 m atches 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 allows 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 irresolute 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 trine 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 d ifferent 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 engage 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, convinced(p) the weak entity sets discriminator. Symbols used in E-R diagram- step 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 skin rash attribute. 4. Find relationships amongst entity set. 5. Draw a complete ER model. How to get up an ERD- graduation 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 rumorA son 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 serve 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 lap 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 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. FLAVOUR MANUFACTURER ADRESS address EATS ICE CREAM CREAM BOY outlay 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 succession booking 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 habitual attributes. If we chose the attribute Name of the Boy table to play the role of the special K 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. metier 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 represent ing these distinctive entity groupings. Consider an entity set person, with attributes name, street, and city.A person may be only classified as one of the following a) customer b) employee Each of these person types is draw 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, forecast 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 pr efer customers. Checking accounts have a fixed interest rate, but offer an overdraft ease the overdraft-amount on a checking account must be recorded. * In terms of an E-R diagram, specialization is depicted by a triangle component label 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. inductive reasoning * The subtlety 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 se t 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 inductance, 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 relati onships, that do not apply to all the entities in the higher-level entity set.Indeed, the causal agent 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 scrimping 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 pickings 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. .