An entity-relationship (ER) diagram is a graphical representation of entities and their relationships. Types include one-to-one, Boxes represent entities. Diamonds represent relationships; Circles (ovals) represent attributes. Related Terms. An attribute of an entity is a particular property that describes the entity. A relationship is the association that describes the interaction between entities. For our discussion of entity-relationship-attribute models we must use a different An attribute must be capable of being defined in terms of words or numbers.
Learning MySQL by Hugh E. Williams, Saied M.M. Tahaghoghi
You usually make this choice based on how confident you are that the attribute will be non-empty and unique for each individual entity, and on how small the key is shorter keys are faster to maintain and use.
Attributes comprising the primary key are shown underlined. The parts of any composite attributes are drawn connected to the oval of the composite attribute, and multivalued attributes are shown as double-lined ovals. Similarly, a product price could be a positive rational number. Attributes can be empty; for example, some customers may not provide their telephone numbers. You should think carefully when classifying an attribute as multivalued: The sales database requirements may specify that a product has a name and a price.
To distinguish between products, we can assign a unique product ID number to each item we stock; this would be the primary key. Each product entity would have name, price, and product ID attributes. The ER diagram representation of the product entity Representing Relationships Entities can participate in relationships with other entities. For example, a customer can buy a product, a student can take a course, an artist can record an album, and so on.
Like entities, relationships can have attributes: Our database could then record each sale and tell us, for example, that at 3: For example, each customer can buy any number of products, and each product can be bought by any number of customers.
This is known as a many-to-many relationship. We can also have one-to-many relationships. For example, one person can have several credit cards, but each credit card belongs to just one person. Looking at it the other way, a one-to-many relationship becomes a many-to-one relationship; for example, many credit cards belong to a single person. Finally, the serial number on a car engine is an example of a one-to-one relationship; each engine has just one serial number, and each serial number belongs to just one engine.
We often use the shorthand terms 1: N for one-to-one, one-to-many, and many-to-many relationships, respectively. The number of entities on either side of a relationship the cardinality of the relationship define the key constraints of the relationship.
- Stay ahead with the world's most comprehensive technology and business learning platform.
- Latest Articles
There are many relationships that may at first seem to be one-to-one, but turn out to be more complex. For example, people sometimes change their names; in some applications, such as police databases, this is of particular interest, and so it may be necessary to model a many-to-many relationship between a person entity and a name entity.
Redesigning a database can be time-consuming if you assume a relationship is simpler than it really is. In an ER diagram, we represent a relationship set with a named diamond. The cardinality of the relationship is often indicated alongside the relationship diamond; this is the style we use in this book.
The ER diagram representation of the customer and product entities, and the sale relationship between them. Partial and Total Participation Relationships between entities can be optional or compulsory.Introduction to Entity, Attribute, Types of Attributes in DBMS
In our example, we could decide that a person is considered to be a customer only if they have bought a product. On the other hand, we could say that a customer is a person whom we know about and whom we hope might buy something—that is, we can have people listed as customers in our database who never buy a product. These are referred to as the participation constraints of the relationship.
In an ER diagram, we indicate total participation with a double line between the entity box and the relationship diamond. From time to time, we encounter cases where we wonder whether an item should be an attribute or an entity on its own.
For example, an email address could be modeled as an entity in its own right. When in doubt, consider these rules of thumb: Is the item of direct interest to the database? Objects of direct interest should be entities, and information that describes them should be stored in attributes.
Our inventory and sales database is really interested in customers, and not their email addresses, so the email address would be best modeled as an attribute of the customer entity. Does the item have components of its own? If so, we must find a way of representing these components; a separate entity might be the best solution.
In the student grades example at the start of the chapter, we stored the course name, year, and semester for each course that a student takes.
Can the object have multiple instances? If so, we must find a way to store data on each instance. The cleanest way to do this is to represent the object as a separate entity.
In our sales example, we must ask whether customers are allowed to have more than one email address; if they are, we should model the email address as a separate entity. Is the object often nonexistent or unknown? In the real world model for purposes of clarity we include all entities regardless of their purpose, and use. The relationships are the real world relationships and describe how the entities relate and interact with each other.
The data model by contrast makes a distinction between entities we are interested in because we collect data about them and entities we are interested in because they are carriers of data or forms and documents we use to collect data about other entities.
The real world model describes the relationships between entities, by stating that one or more occurrences of this kind of entity must or may be related to one or more occurrences of that kind of entity. The data model describes the data we need to know about those relationships, and how we can determine which actual occurrences of one kind of entity are related to which actual occurrences of another kind of entity and under what conditions or subject to what qualifications.
Normally in the data model we ignore document entities which function purely as carriers of data about other real entities.
We instead concentrated on the data they carry and what entities that data describes. One of the primary techniques for data modeling both in framework development and in procedural development is the Entity-relationship model.
The first two of the four Entity-Relationship model levels are used to develop the data framework. These were the enterprise level model and the entity-relationship level model and were described in earlier chapters. These models can be used at the framework level since they can be developed deductively, and from empirical evidence. The remaining two models the entity-relationship-attribute level model and the data element level are inductively developed at the detail design level and are dependent on the full description of the entity family classification structure and a complete set of user views or data event maps for their completion.
A complete set of data event maps is necessary since it is only from the data event maps each of which depicts a specific user task view that we can develop the composite data model which is the entity family model and only by examining the complete set can we be sure we have identified every necessary characteristic, and its data content, and every relationship required by every task for data access.
ER Model - Basic Concepts
All data event maps are necessary because it is only be examining these maps that we can determine whether the data we identified deductively future data needs not currently present, and future relationship needs not currently captured can be captured and maintained on a practical basis. Since the data model development at the entity-relationship-attribute level is an inductive process and is dependent upon determining the exhaustive set of data event maps and the exhaustive classification structure of each entity family it must be the last step in the design process.
The development of the Entity-Relationship model is a multilevel process where each level produces a clearer and more well-defined view of the proposed environment. The complete results of this modeling effort results in a series of leveled environmental descriptions, along with a diagrammatic representation of each level. These diagrammatic representations, the ER models, are descriptions of the entities, or real components, of the business environment and how they relate to each other.
On a regular basis the firm deals with customers products, employees, places, orders, shipments, etc. The firm collects data about these things, these entities, and stores that data in files. With few exceptions, it is the common data resource files of the firms which hold the descriptions of those entities in the form of collections of data items. The ER models are not data structure models, but data descriptions of the entities of interest to the firm.
These data descriptions also correspond to the data contents of the files and thus serve well as a mechanism for developing our data framework.
ER Model Basic Concepts
And, although at their most detailed level they contain and identify data elements, and they are not data processing models. They are business models and as such, they model business environments and depict business components. Entity-Relationship models consist of representations of the various levels and parts of the organization, from the strategic to the operational levels. Each of these leveled models represent the entities and relationships from the perspective of that level, and within a level the Entity-Relationship models represent the perspective of a one or more particular users at that level.
Although there are numerous variations of the Entity-Relationship Approach model notation, the three basic notational components of the Entity-Relationship model consist of symbols representing an entity, a relationship between two entities, and the attributes, or descriptors of either entities or relationships.
Rectangles - Each unique entity type or entity subtype is represented by a rectangle which contains the name of that unique entity type or entity subtype. Diamonds - Each relationship which exist between any two different entities or between two occurrences of the same entity is represented by a diamond which contains the name of that relationship. Circles - Each unique attribute of either an entity or a relationship is represented by a circle which contains the name of that attribute.
The framework model depicts real world families of entities. At this level the entities have the widest possible definition and scope, while still maintaining the general physical and role characteristics of the individual entities which comprise them.
These entity sets are treated as if there were no variations in type and as if each of their component entities were defined in a similar manner and behaved in a similar manner. Just as we use the general term vendor to represent each any every kind of vendor and employee to represent each and every kind of employee, so to we use an even more general term to represent all the various kinds of people, places, thing, concepts and events of interest to the firm.
Depending upon the model since a different definition is used for each model - real world and data the term, entity, thus represents either any one of the real world things in the real world model, or any one of the generalized collections of data we gather, record, and maintain in the data model.
At the framework level: There is no differentiation between the various subtypes of a given entity, unless those differences have meaning at a firmwide, and a functional level.
In the classification model: The polyhierarchic structure of each entity family is defined Each relationship between the different kinds of kinds of entities or entity groups are identified and named Entity groups, kinds of groupings, aliases, parent-child, cousin to cousin and generation to generation relationships as identified in the structure may be firmwide or viewed from a variety of business unit, divisional or other organizational perspectives.
A characteristic or attribute is represented by a circle attached directly to the entity or the relationship which it describes. The circle contains the name of the attribute. The attribute names for an employee entity, might be very similar to the section or item headings on an employment application, or the section or item headings on the permanent employee record form.
For a customer, they might be very similar to the section headings on a new account opening form, or on the customer record form. For an entity, each attribute represents some grouping of data which is necessary, from a business perspective, to describe a physical or logical characteristic of the entity, or to describe some activity of the entity.
For a relationship, each attribute represents some grouping of data which is necessary from a business perspective, to describe, qualify or maintain the named relationship between two entities. Until this point the models have only identified the entities and relationships by name and context. For a given entity or relationship little is known about them other than their name, the obvious fact of their existence, and the fact that the firm is interested in them.
At the Entity-Relationship-Attribute level, entities and relationships are described in terms of their attributes, or characteristics. In other words, beyond knowing that the entity exists, we must also know what the entity looks like, how it is identified, and what it does. These descriptors or characteristics are called attributes. An attribute is thus any distinct aspect of the entity or relationship that is necessary to describe the entity or to qualify the relationship.
The full description of an entity or relationship consists of the full set of attributes which describe that entity or relationship. For an entity attribute to be significant it must relate directly to the entity, be completely dependent on the entity for its existence and meaning, and it must be definable in terms of one or more data elements.
It is immaterial as to whether there are one or more data elements in an attribute, as long as the attribute applies to all instances of the entity being represented. Seen another way, an attribute is some distinct category of mutually related data, the sum of which describes something of interest about the entity. The identifiers unique or otherwise of an entity are a special form of attribute. Entity attributes Figure represent: A physical characteristic of that entity - size, shape, weight or color An historical attribute - date of birth or date of hire A location attribute - place of residence, place of work, or place of birth - A non-physical characteristic - price An identifier - name, title An occupational characteristic - current position, skill possessed, training received, educational courses, etc.
The intermediate or final results of some processing activities related to the entity Data which relates to some current state or condition of the entity, or to some past or future state or condition of the entity Data which relates to some current action taken by or against the entity, or to some past or future action taken by or against the entity For an relationship attribute to be significant it must relate directly to the relationship, be completely dependent on the relationship for its existence and meaning, and it must be definable in terms of one or more data elements.
It is immaterial as to whether there are one or more data elements in an attribute, as long as the attribute applies to all instances of the entity or relationship being represented. Seen another way, an attribute is some distinct category of mutually related data, the sum of which describes something of interest, or some qualifier about the relationship between two entities. A relationship attribute must be dependent upon the connection between both entities and should be incapable of existence in the absence of that relationship.
The minimum attributes of a relationship are the necessary identifiers of each entity of the related pair. Relationship attributes Figure represent some descriptor or qualifier of the relationship such as: An historical attribute - date of marriage, or date of sale, date of storage An location attribute - place of storage, place of work, or place of birth A non-physical characteristic - price at time of sale, discount at time of sale, grade in course Some meaningful data which is not an attribute of either entity participating in the relationship, but pertains only to the relationship between them.
This data is sometimes called intersection data. It is possible for the same named attribute to be used to describe many different entities and relationships. Identifier attributes in particular describe both the entities and the relationships between them. This step produces the most detail model. Step one extracts each entity from the Entity-Relationship model and places it at the top of a separate page.
Each distinct relationship between each pair of related entities is extracted from the Entity-Relationship model, and placed at the top of a separate page. Step two, identifies, names and defines each attribute of each entity. Each attribute, represented by an attribute symbol, is drawn below the entity symbol and is connected to the entity by a single line Figure As each attribute symbol is drawn, the attribute name should be placed within it.