In this chapter I introduce the concepts of entity-relationship (ER) modelling. At the . Back to our example: we have identified three entity types and four entities. Entity Relationship Modeling Examples Earlier in this chapter, we showed you how to design a database and understand an Entity Relationship (ER) diagram. Relationship Metaphors Source for information on Relationship Metaphors: For example, a person could say "Dating is a game," in which the tenor, dating, and Relationship entities take on a life of their own, often making the parties feel as if metaphor discussed above; and relationship as mechanical model, a view.
Understanding these problems are integral to understanding the text. The answer to the problem appears in the text immediately after the problem. What are the items of interest here? It seems here that the situation is concerned with divisions, departments, and employees or managers. It gives some details about which contains which, how they are related to each other, and provides some examples of each, but basically the situation is concerned with these three entity types.
An entity type is a collection of entities that share a common definition. An entity is a person, place, concept, or thing about which the business needs data. So, Department is the name of one entity type. One instance of this entity type is the New Business Development department.
The Marketing division is an instance of the Division entity type. Mackenzie is one instance of the Employee entity type. Instances of entity types are referred to as entities. You can touch an entity but an entity type is simply an idea.
Person is an idea entity type while Scott, Nancy, Lindsey, and Mackenzie are touchable entities. Entity types provide us with a means for making generalisations about entities.
The Higher Education department is in one division. But we know more than the facts about each individual department being in one division. We know that all new departments will also be in just one division.
Introduction to Entity-relationship modelling
And if there is a new division, it, too, will have departments that are unique to the division. So, instead of providing information in the form of statements about specific entities, we use a more powerful and concise format and provide information in the form of statements about relationships among entity types. Thus, in ER modelling we look for relationships among entity types because it is easier and more concise to speak of relationships among general entity types rather than the touchable entities themselves.
A municipal bond from Detroit Ford Clothes Employee The municipal bond is an entity; bond is a possible entity type. Ford is an entity; manufacturer is a possible entity type.
Clothes could be either: Employee is an entity type; Angela and Natalie are example entities. Back to our example: From the description we can assume that there are more entities for each entity type.
Go back and read the situation description if you do not think this is immediately obvious. From the description there is some sort of relationship between Department and Division and another sort of relationship between Department and Employee. The first relationship is one of containment: On the other hand, each team one ball can only be in one bucket a conference. In this instance the bucket is the division and the balls are the departments.
The second relationship tells us that an employee has a certain relationship relative to a certain Department, namely, that the employee manages the department. Determining the relationships among entity types is another important step in the process of ER modelling. A relationship is an association between entity types.
What would you name these two relationships?
Learning MySQL by Hugh E. Williams, Saied M.M. Tahaghoghi
The defining characteristic of a relationship is that several entity types are involved. So something like a name or birth date would not be a relationship since only one entity is involved. 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? 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. 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. 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. With Safari, you learn the way you learn best.
Entity Relationship Modeling Examples - Learning MySQL [Book]
Get unlimited access to videos, live online training, learning paths, books, tutorials, and more. No credit card required Entity Relationship Modeling Examples Earlier in this chapter, we showed you how to design a database and understand an Entity Relationship ER diagram. This section explains the requirements for our three example databases—music, university, and flight—and shows you their Entity Relationship diagrams: The music database is designed to store details of a music collection, including the albums in the collection, the artists who made them, the tracks on the albums, and when each track was last played.
The university database captures the details of students, courses, and grades for a university. The flight database stores an airline timetable of flight routes, times, and the plane types. The next section explains these databases, each with its ER diagram and an explanation of the motivation for its design. The Music Database The music database stores details of a personal music library, and could be used to manage your MP3, CD, or vinyl collection. It ignores the requirements of many music genres, making it most useful for storing popular music and less useful for storing jazz or classical music.
The collection consists of albums. An album is made by exactly one artist. An artist makes one or more albums. An album contains one or more tracks Artists, albums, and tracks each have a name. Each track is on exactly one album. Each track has a time length, measured in seconds. When a track is played, the date and time the playback began to the nearest second should be recorded; this is used for reporting when a track was last played, as well as the number of times music by an artist, from an album, or a track has been played.
Conversely, each play is associated with one track, a track is on one album, and an album is by one artist. The attributes are straightforward: The track entity has a time attribute to store the duration, and the played entity has a timestamp to store when the track was played. If you wanted to use the music database in practice, then you might consider adding the following features: Support for compilations or various-artists albums, where each track may be by a different artist and may then have its own associated album-like details such as a recording date and time.
Under this model, the album would be a strong entity, with many-to-many relationships between artists and albums. Playlists, a user-controlled collection of tracks. For example, you might create a playlist of your favorite tracks from an artist. Track ratings, to record your opinion on how good a track is. Source details, such as when you bought an album, what media it came on, how much you paid, and so on. Album details, such as when and where it was recorded, the producer and label, the band members or sidemen who played on the album, and even its artwork.
Smarter track management, such as modeling that allows the same track to appear on many albums. The University Database The university database stores details about university students, courses, the semester a student took a particular course and his mark and grade if he completed itand what degree program each student is enrolled in.
We explain the requirements next and discuss their shortcomings at the end of this section. Consider the following requirements list: The university offers one or more programs. A program is made up of one or more courses. A student must enroll in a program. A student takes the courses that are part of her program. A program has a name, a program identifier, the total credit points required to graduate, and the year it commenced. A course has a name, a course identifier, a credit point value, and the year it commenced.
Students have one or more given names, a surname, a student identifier, a date of birth, and the year they first enrolled.