 Dear students, today we are in the lecture number 10 of the database management system course. In the previous lecture, we studied an important structure of the ER data model and that was a relationship. We studied what is a relationship and then we discussed different types of the relationship which are unary, binary, ternary and it could go up to any level. Then we also discussed how to represent a relationship in the ER diagram. At the end of the lecture, we were discussing different types of coordinators of the relationships and we discussed that there are four different types of coordinators that are one to one, one to many, many to one and many to many and we also discussed that one to many and many to one. They are the same thing but basically they are viewed from two different angles otherwise the main types are one to one, one to many and many to many. Today we are going to discuss further about the cardinality and some other properties of the relationship. So let's start today's lecture. Dear students, so far whatever we have discussed about the cardinality, precisely speaking that can be called the maximum cardinality. That means that when we say that the cardinality means that how many instances of an entity can possibly relate to other types of entities. So at that time our objective is that how many instances of an entity can relate to. So what you are seeing on the screen is that the one type of the cardinality is the minimum cardinality. The minimum cardinality is obviously the maximum cardinality and it determines what is the lower level, what is the minimum side of the number of instances of one entity type that can possibly be related with the instances of the other entity type. So it means the important thing about the minimum cardinality is that it determines whether the link between two entity types is it compulsory or optional. Dear students, as I mentioned before that it is very important to determine the cardinality properly. Same is the case with the minimum cardinality. So the minimum cardinality of any relationship and the maximum cardinality, it is necessary to know precisely or in the right way, because these are the things that our ER database design represents in the form of semantics of the environment, semantics of the system. So we represent the constraints and rules of the system in our design. So ultimately you are going to implement your database based on this design. So your decision, although apparently you are a minor, but from the implementation point of view, they are very important. And for example, as we have mentioned, the minimum cardinality determines whether the relationship is compulsory or optional. This is a very important decision because you have to implement the ultimate database based on this. And the other thing is that if you do not identify it correctly, then the result will be the same as I have already determined that when you implement your system in that organization, then certain situations that you have developed system will not be able to properly handle or you say that in certain situations your system will fail and that obviously you are not like and that definitely the customer won't like as well. Let's move on. On this screen, the different diagrams that you are looking at, in this you have the way of showing the cardinality of the relationship. For example, if you look at the first diagram, in this you are looking at two types of the same type, that is, here you are looking at a binary relationship. In this, you have the anti-types, the rectangles with which you are looking at some symbols. For example, the first diagram in which the cardinality is shown is one to many. And the second thing is that both the cardinality are shown in this, maximum and minimum. In this, you can see that the symbol of the rectangle that is close to it is showing the maximum cardinality and the symbol that is present is showing the minimum cardinality. Apart from this, if we talk about the maximum cardinality, then the maximum cardinality has two possibilities, either one or many. One is shown with a single dash or single line with a small line, which you are looking at with the rectangle of the student anti-type. And if you look at the side of the book, then you can say that it is a cro-foot figure. That is, you can identify the lines of three of them. One is the straight line, which is the link of the relationship and the other is an arrowhead type. This can be called cro-foot. This symbol is used for many, maximum cardinality type. This figure is showing that you have one on the maximum side or the student side. And the many side, which you can see on the other side, which is with the book, is many. Apart from this, look at the other figure with the two sides. You can see that it is zero on both sides. Zero means that the minimum cardinality of this relationship is zero or you can call it optional. This is a coincidence that the first figure you see here, both are optional, but it is not necessary that both are optional. I have said it again and again that the meanings of these things do not create them. You do not fix them. You rather identify them because they exist there. And how do you identify them? You identify them by discussing with the users, by discussing with the user of the system, for which you are developing better. Here, as I told you yesterday, how do we determine the cardinality? Whereas, we look at both the types of cardinality that are linked to the possibility or the compulsion of them. And we look at both the sides. For example, the cardinality shown in this figure, let us see how we have determined it. Now, from the student's point of view, first of all, you see how much distance a student can relate to a book. In the language of the library system, you can say that how much distance a student can get from a book. One or more than one. Whatever the possibility is more than one, that means it will come in many categories. Whether it is two or a thousand. This means that if you look at the side of the student, the maximum cardinality of this relationship is many. Now, another thing to note here is that in the relationship that the student's cardinality is showing, the symbol of that relationship is attached to the book entry type. Again, the maximum or minimum cardinality of the student entry type is attached to the book entry type rectangle. Similarly, if we look at the inverse, the cardinality of the book entry type, the minimum and maximum of both, in this relationship, you have shown them with the student entry type rectangle. So, these things have also been clarified. Now, here you see that this relationship or this diagram tells you what it means. This means that if we describe it from the student's side, it is a one-to-many relationship. If we describe it from the book side, this is a many-to-one relationship. Now, as far as the minimum cardinality is concerned, note that the minimum cardinality here is either zero or optional. This means that if we say this in the computer language or in the ER language, we will say that it is optional. But if we describe it in the language or in the terminology of the system, how would we describe it? We will say that it is not necessary for a student to issue a book. Some such students can also exist. Or a student entry type can also exist. And when it can be one, it is possible that any book issue or any instance of the book is not related to any instance of the book. Similarly, if you look at the other side, the minimum cardinality of the book which we have represented as zero with the student entry type is also optional. What does this mean? It is not necessary for every book instance that it should be linked to a student's instance. That is, it is not necessary for every book that it should be given any student issue or that it should be given every time. That is why the minimum cardinality of both the pages is also optional. If you look at the second example, the diagram in front of you, you see an employee entry type and a project entry type. And there is a relationship between them. The nature of the relationship is one to many. One to many if we read it from the project to employee. And obviously, if we read it from employee to project, that is many to one. If you look at this, this means that the diagram tells you that the one instance of your employee can relate to the maximum of one instance of the project. Or you describe it as that one employee will work on one project at a time. On the other hand, the instance of one project will relate to the instance of many employees. Or you describe it as that many employees will work on one project. This is about the maximum cardinality. If we look at the minimum, the minimum is that the employee has a minimum cardinality and the minimum cardinality is one show or compulsory. You see, there are minor symbols but there is a difference. The difference is that if you want to one it, this means that the instance of an employee cannot exist in the employee entry type without being linked to an instance of the project entry type. Or if you describe it as an organization's language, then one employee does not work on any project or is not involved in any project until then, he is not an employee. Obviously, the employee will be there when his data will be counted. If you look at the minimum cardinality of the project, then the minimum cardinality of the project has been zeroed. This means that we may have a project that does not relate to any employee's instance. If you think about how we can serve a project where no employee is working, then I will say that this you will understand from the organization, from the system. If they say that we have a phase or a time and we have identified the project we are in the defining phase of the project but because we have not started the project practically, so we will not show that some employees are working on the project. When some employees are not working on the project, then how is this situation in the database being represented that with the instance of that project no employee is linked. In this, the minimum cardinality of the project has been zeroed or optional. If you look at the project cardinality maximum and minimum it has been represented with the rectangle of the employee entry type and the cardinality of the employee entry type maximum and minimum in this relationship it has been shown with the rectangle of the project. Let us look at this example. In this, you can see two types of entry of the student and the course. If you look here you will immediately understand that the cardinality of the two types in this relationship is many to many because on both the sides the cro-foot symbol is made and the minimum cardinality of the two types is zero. Then I will say that it is not necessary that both are zero Now how will we describe this? I will explain it again. This means that one student has many instances of the course it can be linked. If we look at it in a conversely the instance of the course can be linked with the student entry type with many instances Now if you look at the minimum cardinality then what is that? This means that any course can be like this when we talk about any. It means that we are leaving a possibility. Whether there is one or a thousand even if this is a situation where you have an entry type cardinality optionally then it is possible that if there are a thousand or a million instances and none of them are linked then it is reflecting that situation that there are a thousand instances and none of them are linked even that situation is representing this option because the real question is whether you are allowing it or not See by placing just a small zero and a small one how much big decision you are making regarding your database design whether you are allowing it or not it can be one or all if you look at the ends but if you have made it compulsory then it is certain that every instance has to be related The third example is a many to many relationship obviously the maximum cardinality is me and the minimum cardinality is optionally zero If you look at the fourth example here you can see student and hobby the type of student that we have all the instances can be the hobby of every student The first thing is this diagram shows that we can relate one of our students to one hobby obviously you will remember that some students can have multiple hobbies 2, 3, 4, 5, 6 although the student may have more than one hobby but our system it stores only one hobby Why is the system storing one hobby is because this is the requirement of the organization There are many hobbies especially boys so maximum one is enough so again this has to be the decision of the organization it says that one hobby is enough if you look at the other side one student one hobby can relate to many students or one hobby can be of many students for example we say reading hobby or gardening hobby so it can be of many students but if a student has a hobby of music listening music, outing it has 3-4 hobbies but when the data will be entered when it will be asked then it will tell you one hobby so obviously the most favorite hobby it will tell you how this situation will be handled now if you look again at its minimum cardinality what is in it? what is the meaning of this it means that we can have a student who has no hobby the student's instance that will not be linked with any instance of the hobby and similarly if you look at the other side then the minimum cardinality of your hobby is also optional it means that we can have some hobbies which are of no student now the question is what is the significance of this that if we have a hobby which is not of any student then it means that you have got sort of liberty you have freedom if you have some hobbies which are popular hobbies then you will put into the hobby entity type and as soon as a student starts entering then when you want to mention the hobby of that student then as soon as you have all your hobbies that becomes his hobby but first if there is no one there is still a possibility to add a hobby so this helps you to be optional but once again these things have to be finalized by the system by the people running the system Dear students what I have just described this was a particular notation in the ER Diagram to show the relationalities of the relationship but if you look at the books then you will see different notations so I will briefly mention those notations so that at least when you see them then you can relate them to your own notations which we are going to apply in this course this is what I have discussed now the first diagram in front of you this is the first notation which is applied to the notations if you look at this then the one to many relationship that is shown with the arrowhead on which you have one it is shown with a single arrow and with which you have to show many what is shown here between the student and the book one to many one arrowhead if you see between the student and the hobby students one arrow and multiple arrows if you see between the project and the employee then on both the sides there are double arrows from where you can guess that in this notation in this notation there is no option of minimum cardinality so there can be different ways sometimes to show the option we simply place 0 and we place 1 for the compulsory or sometimes for the optional we place a star there for this you can use any notation this is our second notation now a call notation in front of you you will see that for this notation you use one digit and for many you are using M M for many so in this notation when 1 to M is there 1 or M it will be 1 to 1 1 or 1 and it will be many to many so M or N now in this one do not get confused because only the different letters used are used to establish this concept if we place M to M it can show a many to many but where a confusion can be created is that they should be equal because to show that there are many but there is no link between their number if 1 can be 10 so here in this notation for many to many you have used M and N this was notation number 2 the third notation in front of you in this notation you see play line and dot where a simple line you have shown a connectivity with the rectangle of the anti type where you are representing the 1 you have shown when it is being linked with the anti type where you have placed the dot that means this is representing a many so in 1 to 1 1 or dot and in many to many Dear students what we have studied is how you show the cornellities in a diagram different notations which at this time in different books what symbols are used I hope you are clear and in the final we will do an exercise and I will give you assignment when you do that assignment you will clear roles in relationship roles are when an anti type is involved in a relationship in a link in that every anti type what is the function play what is the role and how is it involved in this relationship how is it important what is the significance when you know the role of anti types in a relationship then it is easy to understand that relationship and the cornellities you become the nature and explanation see these small things they are basically adding more and more semantics to your design you should understand what do you mean by the semantic data model and what role is there in database design as far as roles are most of the time it is clear from the nature of the relationship if you simply name the relationship itself then it is enough to understand what role is there in anti types and especially if there is such an environment in which you are used to in which you know then in that case it is so clear by seeing that this role is playing but there are some situations in which this is recommended that you explicitly mention roles in that here for example this is the example in which you only the relationship you will be able to see what role is playing in every anti type for example if we link a student anti type and a course anti type then we have got a relationship and first of all we have named that relationship so as soon as you understand that the student enrolls in this course or it is said that in the course the student enrolls then in this situation the role of both anti types in this relationship it will be clear as soon as you see it but there are two situations which are recommended in which you should explicitly mention the role let's see what are the situations one is when you have a recursive or junior relationship what does this mean it means that when an anti type is related to itself in that case you will say it is better that you either mention the name of the relationship and the role and if you do not mention it you make this name that you write the same type twice or whatever name you give but the role in that from both the sides because now there will be a link that the one anti type will be coming back so you have to mention the role on both the sides and the second situation is that when there are more than one relationship between two anti types more than one there can be two, three there can be more so the situation of this is that there are examples from them let's see the examples on the next skin the first diagram you are seeing if you see there is a faculty anti type and it is involved in a junior relationship the idea is that when you have all faculty members one of them who will be faculty member that will be head of the department so in this you have both the work that faculty member is already head of the department so this is a relationship that is with this anti type now here if you look at its cornellity then from one side there is many and from one side there is one what does it mean it means that your head a faculty member will head a lot of people and from that faculty member one person will head that means if we have a department that there are 20 faculty members one of them will be head of the department so now what is the significance in what sense from which side you have to show that there is one and which side there is many if we do not portray then it can be the opposite instead of saying that one head can be of many faculty members and one head of many faculty members if we do this it means that many heads will be of one faculty member head of the department so obviously this is a confusing or the opposite so in this situation we have to show that from which side there is one and from which side there is many now in this diagram what you have done that you have named the relationship head of department now here the one side the same type of role one side there you have written so this is obvious one faculty member will head of many and many faculty members will be of one head so this is a situation of recursive relationship where you have to what role you are playing you have to mention and again you have to be careful because it is important what you place on which side the second situation you have to mention its cornerality whereas between two anti-types there is more relationship like in the second diagram there are two anti-types one is student one is faculty member now between these two there are two types of relationship one is when we say one teacher teaches many students one student teaches many teachers for example if a student is enrolled in 3, 4, 5, 6 subjects so obviously every subject is taught by a teacher so in that case the other link between student and faculty is when students project each student has a supervisor but basically students accomplish the project so this is the second relationship between student and faculty where you want to show the teacher who teaches many students that is basically a many to many relationship and where the supervisor and student has one to many in what sense but one supervisor who is a faculty member will supervise many students that is why you see this relationship as one to many or many to one the significance of this is that which one is called many to many and which one is called one to many because if you reverse it then it will be totally different meaning so here the many to many the teaching relationship in which the teacher is written the student's role the student entity type is taught by ok and note one more thing that the cardinality the entity type you want to show the cardinality you write it with the other entity type but where the role the situation of the role you write it with the same entity as you can see that the relationship of your teaching the cardinality of the student will show that your faculty's rectangle the entity type the cardinality of the student is showing there but the role along with it the line of the link will be written on it taught by faculty's role along with it the line is written on it what is the benefit of it how to understand the relationship you will say that one faculty member teaches many students and you will also have to read it that one student is taught by many faculty members and the other one's relationship which is the supervisor so you will study it that one student is supervised by one faculty member and one faculty member supervises many students so you also write the language that when you read it then it becomes a sort of description as you saw the teachers written are written with the faculty entity type so you will read that one faculty member teaches many students similarly we will say one student is taught by many teachers and like that I hope you have cleared dependencies this is also a type of constraint you must have realized that this is sort of a condition a check and to make sure you impose different sort of constraint on that this is the very start that when you define the domain domain is also sort of constraint or check and even when you determine the cornerities they are sort of constraint that when you declare that your cornerities are one to many sort of this is a check or constraint the one you have said cannot be many the first type the main dependency that we are going to discuss is existence dependency and in this such a condition that we have already discussed the weak entity types and the regular entity types so in that this is the same condition but this is a little explanation existence dependency as the name suggests that when one entity type instance cannot exist without being linked with the instance of another entity type so there you will say that existence dependency exists or this entity type is existence dependent on another entity type and the two types of this are further one of them is identifier dependency identifier dependency means that your dependent entity type does not have its own identifier does not have its own key and for that key or to define its key the regular entity type which is dependent its key as a part of or as a whole is being used as key of the dependent entity type if you have entity A that is existence dependent on B and B is a regular entity type so it has its own key and its own identifier but when you are talking about A which is existence dependent then B's key or as a whole is being used as the key of the A and if it is not then A has its own composite key and in that key there is a part when your dependent entity type its identifier its key is based on your regular entity type on its key this is identifier dependence this example you will see on the next screen now we will see what is the second type of dependency and that is referential dependency referential dependency will represent that situation when your dependent entity type has its own key but its dependence on strong entity type or on regular entity type is represented with the help of another attribute and that attribute is used basically to link these two that means your dependent entity type its instance without a link will not exist although its identifier is its own and when we read this thing the weak entity type this attribute which is linking is called foreign we will read this in the next lecture but now I will show you an example where we say existence dependence it is existing let's see on the next screen what you are seeing you can see a book and a book copy of two entity types the logic of this is if we have a book and it has multiple copies then the data of the book will be repeated if we have 10 copies then we store those 10 copies here it is shown book and book copy in this case the book copy entity type its instances are not its own identifier but the book identifier which we said book ID which is basically the primary of your book entity type and also copy ID both of them together they will be declared as the primary key and the identifier of the book copy entity type this situation will be when your strong entity type its primary key as part of or as a whole is being used as the key of the dependent entity type we will say this is identifier dependence if we look at another example faculty and department this shows its its own identifier and dependent entity type which is your employee faculty faculty is shown here as dependent entity type faculty's instance it cannot exist without being hit with the instance of the department or you will say that until a faculty member is not related to any department then it cannot be entered into the faculty entity type faculty has its own identifier which we said faculty ID but its dependence on the department entity type is represented with the help of another attribute that attribute is a linking attribute which we will call as foreign key a link will be which will be with the department and until there is a proper link then you cannot enter faculty entity type or you will say that until a faculty member does not link with any department then you cannot enter it and mind it as always these are the rules you will identify from the system you will find them yourself i.e. you have to decide whether a faculty member is dependent on the department or not this is not your job this is the job of the organization this means if they option it what will happen that such faculty members whose data is present in the faculty entity type that is fine with you in either case that is fine with you you have to model it let us move on dear students what we have read on the ER data model you can say that this is a basic ER data model basically the scientist Chen who proposed the ER data model what we have read the maximum of that was included in the presentation or in the definition of the Chen i.e. this was basically the original ER data model and this was back in 70s somewhere from that time the utility of ER data model that has been proved as a tool for the conceptual database design the limitations or the strength to enhance it or to remove its limitations different people have enhanced it if you look at the literature you will see different search papers in which different people have suggested ER data model somewhere you will see extended ER data model somewhere you will see enhanced ER data model enhanced extended ER data model there are so many proposals the objective is you make it semantically richer you make it more powerful that is why the next title we have enhancements in ER data model the most common enhancements in ER data model we are discussing and this is a crux whenever you use ER data model you should remember you can do it and in this way you feel more handy let's go ahead different proposals and we have presented the most common and one of the most common features which we are using is the concept of super type and sub type and this concept is basically the paradigm of object oriented programming or it has a very powerful feature and the relationship of sub type or this feature the right strength of this is really in object oriented programming or in the paradigm it is clear to us but it is also used in a limited meaning we will see how it is used super type sub type is also a kind of relationship between different anti-types some anti-types are declared as the super type and some are declared as sub types and we call this generalization and specialization relationship that is your super type is called a general anti-type and your sub types are called specialized anti-types Dear students let me tell you one thing if you have super type sub type or generalization specialization relationship this is also a relationship as you have read different relationships this is also a link an association between different anti-types this is given a specific name and a special treatment which we give just because this is a relationship which has its own meanings and its own role so if you do not treat it like this then it is possible that you use the same symbols that we use but when we give it a specific treatment in ER data model through an enhancement what is the advantage of that that is clarity so in relationship generally you will see that these two anti-types are related but you will have to see on the nature of the relationship how it is linked but when you talk about super type sub type relationship then what is the benefit of meeting it differently that you will understand that this relationship is of super type sub type and the meaning will come in your mind so the objective was that you make the tool richer but also there is a constraint that you do not involve so many symbols that your tool has 50 different figures to see the meaning of this figure you need a different table you need a different computer and you will see that there are two, three, four symbols that represent different situations and for super type sub type you have different symbols that you will see in the future at this time the diagram on the screen you will see that what is written on the top is super type and below what you see is sub type and what is written sub type 1, sub type 2, sub type 3 what you have on the top is super type as general anti type and what you have below is specialized anti type note what are the symbols for this note that on the top side we have only one anti type there are two, three, four and whatever you identify in that environment there will be sub types if you look at the symbols you use a circle you have linked them and after that all the sub types are linked and you can see an arc where the edges are on the top side so you take a representation of the symbols that you use for example if you look at the other side there is a person and what you have shown is super type and all the sub types are student and faculty member what you are saying is that the person is super type and the student and faculty members are the sub types the person is a general anti type and the student and faculty are your specialized anti types dear students as I have just told you that you have a super type sub type relationship that is also a type relationship that is why we named it this is the situation of super type and sub types if you look at them individually each one of them is an anti type as in as itself that is a normal anti type like an anti type which we have said sub type or super type why we said only due to involvement in this particular relationship super type is super type because that is involved in this relationship if we look at it from this relationship then that is an anti type similarly sub type why sub type because it is involved in this particular relationship so we will call it only the type of relationship otherwise it is just like a normal anti type that is why you are confused that this is something different which is anti types apart from this how many sub types of a super type again you are no one to decide it you are no one to dictate it rather you are the person who is going to identify this that in this environment a special anti type how many sub types or how many sub types we will declare as super type how we will do this in today's lecture for you it is only important that we have super type and sub types are different types between which the relationship is of generalization and specialization dear students this is now time to wind up today's lecture in today's lecture we have a very important and technical point of ER data model which we want to say that one of its basics is cardinality of the relationships we discussed it what is cardinality how do we represent it a special notation we discussed it in detail because in the next lectures during the rest of this course we will use that notation in the books we discussed the different notations and related them that the notation we have adopted and what is the link what is the link then we discussed what is the functionality what is the importance of role of the antity types in a relationship and after this we discussed the enhancements in ER data model and what we have discussed is super type sub type relationship till now we have finally discussed that we declare it as super type and it can have different sub types and to represent it in ER data model which symbols are used in super type sub type relationship we need to discuss more and that we will do in our next lecture InshaAllah Allah Hafiz