ERD Notations in Data Modeling. Part 2 – Chen Notation
Entity - ERD Symbol A weak entity is an entity that must defined by a foreign key relationship with. An Entity Relationship Diagram (ERD) is a data model describing how entities There's the crow's foot notation, and there's the multiplicity syntax, which has a. You can edit this Entity Relationship Diagram using Creately diagramming tool .. A Entity Relationship Diagram showing ERD (Chen Notation) Online Student .
It occurs with a master table that links to multiple tables in a one-to-many relationship. The issue derives its name from the way the model looks when it's drawn in an entity—relationship diagram: This type of model looks similar to a star schemaa type of model used in data warehouses.
When trying to calculate sums over aggregates using standard SQL over the master table, unexpected and incorrect results. The solution is to either adjust the model or the SQL.
This issue occurs mostly in databases for decision support systems, and software that queries such systems sometimes includes specific methods for handling this issue. The second issue is a 'chasm trap'. A chasm trap occurs when a model suggests the existence of a relationship between entity types, but the pathway does not exist between certain entity occurrences.
For example, a Building has one-or-more Rooms, that hold zero-or-more Computers. One would expect to be able to query the model to see all the Computers in the Building. However, Computers not currently assigned to a Room because they are under repair or somewhere else are not shown on the list. Another relation between Building and Computers is needed to capture all the computers in the building. This last modelling issue is the result of a failure to capture all the relationships that exist in the real world in the model.
See Entity-Relationship Modelling 2 for details. Entity—relationships and semantic modeling[ edit ] Semantic model[ edit ] A semantic model is a model of concepts, it is sometimes called a "platform independent model". It is an intensional model. At the latest since Carnapit is well known that: The first part comprises the embedding of a concept in the world of concepts as a whole, i.
The second part establishes the referential meaning of the concept, i.
Entity-Relationship (E-R) diagrams
Extension model[ edit ] An extensional model is one that maps to the elements of a particular methodology or technology, and is thus a "platform specific model". The UML specification explicitly states that associations in class models are extensional and this is in fact self-evident by considering the extensive array of additional "adornments" provided by the specification over and above those provided by any of the prior candidate "semantic modelling languages".
It incorporates some of the important semantic information about the real world. Plato himself associates knowledge with the apprehension of unchanging Forms The forms, according to Socrates, are roughly speaking archetypes or abstract representations of the many types of things, and properties and their relationships to one another.
Now we have identified three entity types Employee, Department, Division and two relationships among these entity types manages, contains. Now we can begin to represent the problem in the language of ER modelling. ER models are usually represented graphically. The language we are going to use represents entity types as rectangles and relationships as diamonds.
Below is the representation of the situation we are working with. Notice that the contains relationship is drawn between the two entities that it is associated with. Similarly for the manages relationship. This simplified ER model tells us that: Division is related to department through a relationship called contains. Departments are related to employees through a relationship called manages. Employees are not directly related to divisions.
Certainly we know more about the problem than this. Consider the relationship between divisions and departments. We know that divisions have multiple departments and departments can only be contained within one division. Or, for every one division there can be many departments. In the language of ER modelling this is called a 1: What is the relationship between departments and managers?
Fill in the blanks with either a one or a many: The relationship between department and a managing employee is different. Certainly you can imagine an instance in which a department has co-managers. That possibility is just as viable as the possibility I have assumed. This is part of the attraction of this type of work. If you were actually creating a database in this example, you would have to ask someone what the situation actually is.
But since you are just given this description, you have to come up with some assumption. In other words, for every one department there can be, at most, one managing employee. This information can also be represented in the ER diagram: As you might have determined, the M part of a relationship is represented by putting an M next to the appropriate entity type in the relationship while the 1 part is represented by a 1.
The ER diagram now represents much more information than it did above: Any one division can contain many departments. Any one department can be contained in, at most, one division. Any department can have, at most, one managing employee or manager. Any manager can manage, at most, one department.
If you are a bit confused about all this 1: Several other questions remain about this situation that are not addressed in the description: What is the minimum number of departments in a division?
ER Diagram Symbols and Their Usage
Does a department have to be associated with a division? Does a department have to have a manager? These questions would have to be answered before we complete the ER model. And we will answer these questions later. For now we are going to stop this part of the analysis since the purpose of this example is to demonstrate what ER modelling is all about. The ER modelling process is not something for which a set of steps can be given and then performed.
The process contains almost as much art as science. Some steps are performed many times and many decisions are re-visited and revised.
- ER Diagram Symbols and Their Usage
- Entity-relationship modelling
Given these conditions, a broad outline can be given: Determine what entity types are involved. Determine which entity types are related. Refine the definition of the relationships. Understand now that there are several methods for representing ER models graphically. Notice what has happened with this situation.
Initially we had a text description of the problem.
After analysing it and making some necessary assumptions, we created an ER diagram that reflects the situation accurately and makes explicit the relationship among the entity types. This is why we perform ER modelling. It is quite a straight-forward step to go from this ER model to an implemented database.
Entity-Relationship (E-R) diagrams notes
Remember why we are doing all this: We are finding out all we need to know to create a database that will hold our data. And a well-defined database can be a very useful tool for solving business problemsand it is also in high demand by recruiters.
You will learn how to perform the steps necessary to create such a database in later chapters. In this section I present more detail on some of the basic concepts. In the example in an earlier section, we saw that divisions are directly associated with departments and departments are directly associated with employees.
No direct association between division and employee was given. This does not mean that there is no relationship between division and employee. In fact, the ER diagram tells us that there is a relationship between the two: Given any one division, there can be many employees managing departments within that division.
Certainly, this is not earth shattering news. But it is in the ER diagram. The above fact is not represented as a separate relationship between division and employee because it can be inferred from existing relationships. An ER diagram should contain the minimum number of relationships necessary to reflect the situation. For relationships between two entity types, there are three basic cardinalities. Each of the following descriptions are given in terms of a relationship between entity type X and entity type Y.
One entity of type Y can be associated with, at most, one entity of type X. A car has only one steering wheel and a steering wheel can only be installed in one car. M one-to-many One entity of type X can be associated with many entities of type Y. A building can have many rooms but a room can be in, at most, one building. M many-to-many One entity of type X can be associated with many entities of type Y. One entity of type Y can be associated with many entities of type X.
A car can have many options and an option can be installed on many cars. Determine the cardinality of the relationships between the following four pairs of entity types. For each relationship you have to answer two questions: Answering these two questions gives you the answer to the following questions. For example, if you answered M to the first question and 1 for the second question, then this relationship between entity types X and Y is of cardinality M: Patient under care of primary care physician Physician performs operation Doctors have speciality in disease Needle injected into patient It would seem that at any particular time a patient can only have one primary care physician and that any physician can have many patients M: One physician can perform many operations and one operation can be performed by many physicians M: One doctor can have specialities in many diseases and one disease can be the speciality of many doctors M: In this section we examine the minimum number of entities in a relationship.
Existence is given as optional, mandatory, or unknown. This is best clarified with an example. Consider again the example discussed in Section 2. Specifically, focus on the manage relationship between department and employee. We know the cardinality is 1: This tells us that at most one department is managed by an employee and an employee can manage, at most, one department.
Be sure you understand the distinction between these two phrases. The existence of this relationship tells us the fewest number of departments that can be managed by an employee and the fewest number of employees that can manage a department.
Only one of the following can be true: Similarly, only one of the following may be true: For each set of three above, which ones would you choose? It is not entirely clear from the situation description which of the above are true. I make the relatively standard assumptions that a department must have at least one manager and that an employee need not be the manager of any department.
Thus, the existence of this relationship is mandatory in one direction and optional in the other.
Going back to the definition of existence, we can also look at this situation in this way: Given any randomly chosen department, there must be an employee on the other side of the manage relationship. Thus, the relationship is mandatory in this direction. Given any randomly chosen employee, there need not be any department on the other side of the manage relationship.
Thus, the relationship is optional in this direction. I assume that the contains relationship is mandatory in both directions. Given this information, the ER diagram is modified in the following manner: This diagram is beginning to look a little complicated but remember the following pieces of information and it gets a little easier: The marks on the lines tell you the minimum number in a relationship.
A dash on the line looks like a 1; it tells you the minimum number in the relationship is one so the existence is mandatory. A circle on the line looks line a 0; it tells you the minimum number in the relationship is zero so the existence is optional.
If there are not any marks on the line, then the existence is unknown. The marks below the line tell you the maximum number in a relationship. An M tells you the cardinality is many while a 1 tells you it is one. Look at the manages relationship again. Cover up the line connecting the manages diamond with the department rectangle. Now all you see are the department entity type, the employee entity type, and the right side of the line with a dash on it and a one below it.
This tells us that a department is managed by at least the dash one employee and at most the 1 one employee. A department is managed by one and only one employee. Cover up the line connecting the manages diamond with the employee rectangle. Now all you see are the department entity type, the employee entity type, and the left side of the line with a circle on it and a 1 below it.
This tells us that an employee does not have to manage any departments the circle and may manage at most the 1 one department. An employee may manage no more than one department. For each of the relationships listed in Problem 5: Define the existence in both directions.Entity Relationship Modeling
Draw the ER diagram for the relationship. Write out two sentences that represent what the ER diagram says.