Let us continue with the data modeling topic. So far we have seen the basic features of ER model, where to use it, what advantages it offers. We studied the concept of entity attribute and the key attributes. We also looked at the relationship concept. And then we looked at a simple diagramming notation through which you can show some entities, some relationships among them. This is what we call an instance diagram. And it is always good to draw an instance diagram to illustrate the understanding, or to illustrate the concepts we are modeling in our diagram. So let us proceed further.
We were talking about relationship cardinality as an additional constraint on a relationship. It tries to characterize the relationship more precisely by specifying how many entities of one set may relate with another entity in another set as a part of the relationship. So primarily, by cardinality, we are indicating how many entities in terms of one or more are related to entities from another set in forming a relationship. This is a very useful concept for binary relationships where two entity types or two entity sets are related.
If you consider a relationship named R between two entity sets A and B, then what are the possible cardinalities that we can indicate? The first one is of type one-to-one. It means that one entity in A is associated with at most one entity in B. So here it’s a one-to-one relationship. One-to-many relationship is where one entity in A may be associated with zero or more entities in B. So when we go from A to B, we may meet many entities from B which are associated or connected through the relationship with the given entity in A. So one-to-many is also an indication that if we start traversing or navigating in the database, when we start from A we may reach multiple instances of entities in B. This is characterized as one-to-many relationship. Obviously in a given relationship like this, if you now go from B to A, then one entity in B you would reach at most one entity in A. So one-to-many and many-to-one are really the inverse of each other.
Then finally, you have the many-to-many relationship. In the many-to-many relationship, one entity in A may be associated with more than one entities in B and also the reverse. So here one entity in A might be reachable from many entities in B. And similarly, an entity in B may be reachable from many entities in A. So this is a many-to-many example of relationship cardinality.
Let us look at some examples. Take the example called teachers. The teachers relationship exists from entity teacher and course. So we have a teacher entity. We have a course entity. Between them we have the teachers relationship. Now what is the cardinality of the teachers relationship? Here we are saying that it is one-to-many. That is, one teacher may teach many courses. But if you go from course to teacher, then it would be for a given course, there is one teacher. So one-to-many indicates that a teacher may teach many courses whereas the course is always taught by one teacher. So obviously as you can see here the relationship is giving you more information. It tells you about how many entities are relating to each other in a given environment.
Now the teachers relationship is from teacher to course we say, that is how that we understand it from the readability point of view. Now the inverse of teachers relationship, that means when you want to go from course to teacher, I mean you may prefer to name it as taught by. But in ER diagrams that we will draw, a relationship carries only one name, whether you go from one entity to another or from the other entity to the first one. So the relationships will have the same name irrespective of the direction you are traversing the relationship.
The other example is the study relationship. The study relationship exists between student and course. So what is the cardinality of this binary relationship? A little thought will tell us that it is a many-to-many relationship. What it means is that one student may be studying many courses and one course may be studied by many students. So again if you draw an instance diagram and you show a few courses and a few students, then if you start from one student, you would reach on the other end multiple courses. Similarly, if you start from one course, you may find many students who are studying that course. So this is a many-to-many relationship. So we can characterize relationships further by indicating the cardinality and it is a very useful concept and you must specify this at least for the binary relationships that you define in your data model.
The next concept is that of existence dependence. Again this is a constraint. We specify this in order to indicate what is permissible in our data model. So it’s an important constraint. What it specifies is that existence of an entity A may depend on the existence of another entity B. That means that although they are entities in their own right, they are distinguishable from each other. So the entity still has the same definition that we gave earlier that they are unique and they are distinguishable. But in our database, in the real world, we are saying that entity A must exist in association of another entity B. This is called existence dependence.
In this case, B is called dominant entity and A is called subordinate entity. An entity which depend on another entity for its existence is naturally called a subordinate entity. Now in this case, both the entities are proper entities. They will have their own attributes. They will have their own keys. But still when they are created, when they exist in the real world, they exist in association with another entity. So a simple example would be the dependency between teacher and department. Now teacher and department are two entities and they have a relationship between them which we may call as employee. So we say teacher is a employee in some department, or teacher is employed in some department.
Now whenever a new teacher is appointed, the teacher has to be appointed in a particular department. What it means is that teacher cannot exist without being associated with some department. So teacher depends on department. Teacher cannot exist without being associated with some department. So there is existence dependence between these two entities where the teacher is a subordinate entity and department is a main entity. Again I’m emphasizing this that the subordinate entities do have their own key and they can participate in additional relationships. So they are entities in their own right. We are only expressing the real world requirement that entity like this must exist in association with another entity.
Let us now look at the diagramming notation for ER model and look at some more examples. Here are the diagramming notation symbols we will be using. An entity is represented by a rectangle and we will write the name of the entity within this rectangle. A relationship is represented by a diamond. These are basically notations for entity set and the relationship set. An attribute is written down in a small circle which is generally connected either to an entity or to a relationship. Because attributes don’t exist independently, they are associated with entity or relationship. And the key attribute may be underlined.
Here are some more notations. The cardinality of the relationship is indicated by writing a number. Usually we write one or many, or N, also for many. So we may say the cardinality is many-to-many in which case we may write a letter N here and a letter M here or we may write the letter M at both the places. So this is how the cardinality for binary relationships are shown. Now these two symbols are used when we encounter weak entities. So we will see their usage in more details subsequently. We have not yet seen those concepts.
A multivalued attribute is shown by a double circle. So recall that we had taken example of such an attribute like author of a book. So author will be written in a double circle and would be connected to the book entity to show that this is a multivalued attribute of the book entity. So these are the diagramming notations. There is no universal standard for these notations, but these are the symbols which are very commonly used.
Let’s look at this simple example. We have shown here an entity called student. This entity has two attributes: roll number and name. So we indicate entities and we associate attributes in this fashion. And we may also say that roll number is the key for the student entity. Another entity in this example is the course entity which has again two attributes: the course number and the title. And the course number may be the key for this entity. These two entities are related through the enroll relationship. So student enrolls in a course, right. And this enroll relationship has the cardinality many-to-many.