Entity Relationship Modeling Examples - Learning MySQL [Book]
Here is an example of how these two concepts might be combined in an ER data model: Prof. Ba (entity) teaches (relationship) the Database Systems course. ER diagram for hospital management system to model your system. Edit the ER diagram online to make necessary changes and adapt it to your hospital. universities and hospitals) have a number of branches, divisions or sections in order to deal . example, we would include the primary key of Customer in the Order entity to The entity-relationship diagram above shows the one-to-one link .
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? 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. 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.
ABC Diagram | Editable Entity Relationship Diagram Template on Creately
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. 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.
Introduction to Entity-relationship modelling
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. Which doctors work in which wards? How much will be spent in a ward in a given week? How much will a patient cost to treat?
How much does a doctor cost per week? Which assistants can a patient expect to see? Which drugs are being used? Add cardinality to the relations Many-to-Many must be resolved to two one-to-manys with an additional entity Usually automatically happens Sometimes involves introduction of a link entity which will be all foreign key Examples: This flexibility allows us to consider a variety of questions such as: Which beds are free?
Which assistants work for Dr. What is the least expensive prescription? How many doctors are there in the hospital? When a student attempts a course, there are attributes to capture the Year and Semester, and the Mark and Grade.
For a real university, many more aspects would need to be captured by the database. The airline has one or more airplanes. An airplane has a model number, a unique registration number, and the capacity to take one or more passengers. An airplane flight has a unique flight number, a departure airport, a destination airport, a departure date and time, and an arrival date and time. Each flight is carried out by a single airplane.
A passenger has given names, a surname, and a unique email address. A passenger can book a seat on a flight. The ER diagram of the flight database An Airplane is uniquely identified by its RegistrationNumber, so we use this as the primary key. A Flight is uniquely identified by its FlightNumber, so we use the flight number as the primary key. The departure and destination airports are captured in the From and To attributes, and we have separate attributes for the departure and arrival date and time.
Because no two passengers will share an email address, we can use the EmailAddress as the primary key for the Passenger entity.
An airplane can be involved in any number of flights, while each flight uses exactly one airplane, so the Flies relationship between the Airplane and Flight relationships has cardinality 1: N; because a flight cannot exist without an airplane, the Flight entity participates totally in this relationship.
A passenger can book any number of flights, while a flight can be booked by any number of passengers.ER- Diagram Explanation Google student club activity 3 subject DBMS CSE Btech 5th SEM
N Books relationship between the Passenger and Flight relationship, but considering the issue more carefully shows that there is a hidden entity here: We capture this by creating the intermediate entity Booking and 1: N relationships between it and the Passenger and Flight entities.
Identifying such entities allows us to get a better picture of the requirements. There are no requirements to capture passenger details such as age, gender, or frequent-flier number. If, instead, we assumed that the capacity is determined by the model number, we would have created a new AirplaneModel entity with the attributes ModelNumber and Capacity.
The Airplane entity would then not have a Capacity attribute. Airlines typically use a flight number to identify a given flight path and schedule, and they specify the date of the flight independently of the flight number. For example, there is one IR flight on April 1, another on April 2, and so on. Different airplanes can operate on the same flight number over time; our model would need to be extended to support this.
The system also assumes that each leg of a multihop flight has a different FlightNumber.