The Entity Relationship Model At a basic level, databases store information An attribute may be formed from smaller parts; for example, a postal address is. DATA MODELING USING THE ENTITY-RELATIONSHIP MODEL. Click here for Example 1. COMPANY has departments 2. Department has name, number, manager An entity type is the set of entities having the same attributes; We can . Learn about entity relationship diagram symbols. See ERD diagram examples. By defining the entities, their attributes, and showing the relationships.
For an entity an attribute, such as StudentGrade, usually holds exactly one value, such as 93, and thus is a single-valued attribute. However, two lab assistants might assist in a laboratory section.
The Entity Relationship Model - Learning MySQL [Book]
Consequently, the LabAssistant attribute for the entity LabSection is multi-valued. A multi-valued attribute has more than one value for a particular entity. We illustrate this situation with a double oval around the lab assistant type, LabAssistant see Figure 5.
Definition For a particular entity, an entity attribute that holds exactly one value is a single-valued attribute. ER diagram notation for multi-valued attribute domain, LabAssistant A derived attribute can be obtained from other attributes or related entities. For example, the radius of a sphere can be determined from the circumference.
We request the derived attribute with a dotted oval and line, such as in Figure 6. ER diagram notation for derived attribute, radius An attribute or set of attributes that uniquely identifies a particular entity is a key.
However, to determine the class we need a composite key that consists of several attributes, such as catalogue number, section, semester, and year.
In the ER diagram of Figure 7we underline the composite key, class. The figure shows another attribute DragExpWeek of LabSection that stores the week of the semester in which the drag experiment occurs. Definition An attribute or set of attributes that uniquely identifies a particular entity is a key. A composite key is a key that is a composite of several attributes.
For example, the student entity type is related to the team entity type because each student is a member of a team. In this case, a relationship or relationship instance is an ordered pair of a specific student and the student's particular physics team, such as Emanuel Vagas, PhysFA04where PhysFA04 is Emanuel's team number.
Figure 8 illustrates three relationships. Unfortunately, Itnatios Trekas had to drop the course and retake it another semester. Consequently, his name is associated with two team numbers. We arrange the diagram so that the relationship reads from left to right, "a student is a member of a team.
Definition A relationship type is a set of associations among entity types.
A relationship or relationship instance is an ordered pair consisting of particular related entities. The degree of a relationship type is the number of entity types that participate. Thus, the LabSecMemberOf relationship type of Figure 9 has degree 2, which we call a binary relationship type. To clarify the role that an entity plays in each relationship instance, we can label a connecting edge with a role name that indicates the purpose of an entity in a relationship.
For example, we can have two binary relationship types associating the student and team types, TeamMemberOf and LeaderOf. In the former case, a student entity is a member of a team entity; in the later case, a student can be a leader of a team. We illustrate the situation in Figure Definition The degree of a relationship type is the number of entity types that participate.
If two entity types participate, the relationship type is binary. A role name indicates the purpose of an entity in a relationship. ER diagram notation with roles member, leader, and lab team As Figure 11 illustrates, a relationship type can also have attributes.
The relationship type order connects entities chemical and supplier.
Learning MySQL by Hugh E. Williams, Saied M.M. Tahaghoghi
The relationship is many-to-many because each chemical can be from several suppliers and each supplier has a number of chemicals. An order has a purchase date, amount, and total cost as well as the chemical and supplier information. Attributes describe the entity they belong to.
An attribute may be formed from smaller parts; for example, a postal address is composed of a street number, city, ZIP code, and country. Some attributes can have multiple values for a given entity. For example, a customer could provide several telephone numbers, so the telephone number attribute is multivalued. Attributes help distinguish one entity from other entities of the same type.
We could use the name attribute to distinguish between customers, but this could be an inadequate solution because several customers could have identical names. To be able to tell them apart, we need an attribute or a minimal combination of attributes guaranteed to be unique to each individual customer.
The identifying attribute or attributes form a key. In our example, we can assume that no two customers have the same email address, so the email address can be the key.
However, we need to think carefully about the implications of our choices. For example, if we decide to identify customers by their email address, it would be hard to allow a customer to have multiple email addresses. Any applications we build to use this database might treat each email address as a separate person, and it might be hard to adapt everything to allow people to have multiple email addresses.
Clearly, there may be several possible keys that could be used to identify an entity; we choose one of the alternative, or candidate, keys to be our main, or primary, key. 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.Entity Relationship Diagram (ERD) Tutorial - Part 1
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. 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. 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.