 Dear students, today we are in the lecture 9 of the database management system course. In the lecture number 8, we discussed the concept of keys, the importance and need of keys in the databases. We discussed different types of keys as well. And first thing that we discussed was the super keys. Super key is basically an attribute or set of attributes that are required to uniquely identify an instance of an entity type. We discussed that if any attribute included with the super key will also be a super key. Then we discussed the candidate keys which is basically a minimal super key. That is a candidate key cannot have some additional attributes whereas we can have some additional attributes in the super key as well. Then we also discussed the primary keys. Primary key is basically a successful candidate key. That is if we have got different candidate keys in an entity type and due to different reasons on the basis of certain criteria, if we select one of them that would be declared as the primary key whereas rest of them will be called the alternate key. We also studied the need and the purpose of the secondary key. Primary key is something on which we perform the access in the database but that is not necessarily unique like name, like father name, like address, etc. Today we are going to discuss another very important topic of the ER data model and that is relationship. It is important to kindly pay your full concentration on the topic. So let us start today's lecture. Relationships. We define relationship as the connections or interaction between entity instances or we can say this is the link between instances of an entity type. In the ER data model or in the ER diagram, we represent a relationship by a link between two entity types. Next thing is how to identify relationships. Dear students, this is somewhat technical that how to identify the relationships and in different situations it may get a bit confusing, because you will see that if you look at the design of two entities, they represent one thing or one situation as an entity type and another person represents different entity types and the relationship between them. So what is the idea behind? What is the rule? The first thing is how did you identify your entity types? First question starts from here. If you look at its mechanism or its flow, then one possibility can be that you may start from your context diagrams, different types of DFTs. From these two tools, you were able to identify different entity types. And the major characteristic of an entity type is that it combines together the properties of a particular thing that exists in an environment and that is distinguishable from the other things in that environment. So this means that generally, your entity type stores the properties of a particular thing or the attributes of a particular entity and it combines them. When you have identified different entity types in an environment, the next thing is that now you analyze different activities in that environment. For example, you say that if we look at an academic institute, then if we say that we have got a student entity type and a course entity type. Now if you analyze the activities there, then one of the activities is that the student is enrolled in a course. If you look at the analysis of this activity, then you will automatically see that it is an activity, a process in which two different entity types are mentioned. So there is a chance that there is some relationship involved in this situation, in this activity. Similarly, for example, we identified a student and the book in our library system. Then there is an activity in the library system that student borrows or returns the book. Now again there is some activity in which both the entity types that you have identified they are involved. Again a candidate situation that involves a relationship, a link, an association between these two entity types. There is another environment where you said that we have got different employees in this organization and the organization also runs different sort of projects. Now then there is an activity, there is a process when you say that the employees work in the project. Again you see an activity, a process, something that involves the mention of, that involves the names of two entity types that you have already defined. So in such a situation in which you feel that during this activity, there are some different entity types involved. So you will say that yes, there is a link, there is an association, there is an interaction between these two entity types to perform this specific activity, this specific process. So from here you will realize that there is a relationship between these two entity types. And you improve your design. Firstly you identify it on a rough basis, then you again think about that, you analyze that situation, maybe the one that you have identified as a relationship is later also not proven. Because it is not necessary that you make the right design, first time you just take a rough, draft-side design, then you further analyze it and improve it. So the relationship to identify a root is that when you were using different tools, which you said like that. But secondly, another way you can use it is that in which you have an explanation about the system, which is working, that is not given these precise tools like DFT or these cross-reference metrics, or if you do not have any other tool, rather you have got a description of the system. And once again I will say that the second approach I am mentioning, this is not much useful and you should avoid using it, especially in a large system or in real life systems. Especially as a practice or an academic point of view, when we give a test or assignment to an exam, then usually there is a very big project or a very big example, it is such an example that you can do in a little time. So such a situation when you are able to express the situation, a scenario, a system in the form of a paragraph, let us say 8, 10, 15, 20 lines in which you are explaining the system. So from there you can identify the anti-types and the relationships. Its proper routine, the proper route is that first you draw the DFT and from there you are able to do it. But there can be a way that you directly identify the anti-types and the relationships from the description of the system. And obviously the thing is that as a single thing, as a distinguishable thing, as an independent thing, it is mentioned and its properties are also mentioned. So you will identify it as an anti-type. And where such a description is given in the same paragraph where two or three anti-types are being mentioned. So from there you can guess, you can guess, you can guess that this relationship or link association exists between them. And look, when I said that the same situation can be represented as anti-type or some other person might be representing that thing as that situation as the anti-types with the link between them or the relationship between them. As I have told you that the same situation can be represented by different methods, by different extraction or different design. Everything is not wrong. So does this mean that we design anything wrong? No. You should always focus on the requirements. What are the requirements from the system, the system that you are going to develop, the system that you are designing? What are the requirements of the user from that system? So any design you come up with, there are some anti-types, there are attributes, there are relationships. Look at the output required by the users of that system. Any kind of output, whether it is in the form of reports, documents or screens, whatever. How can you extract data from your design to fulfill those requirements? If all the requirements that you have identified if they can be fulfilled from that design, there is always the link between. If you start from here, for example, for example, we want an examination system or a registration system, you want to know which students and roles are in the class. You can represent it however you want. But the validity of your design requires that when we have this requirement that which students and roles are in the class, one anti-type is involved, and the other anti-type is the student. Since we have both involved, there is also a relationship between them. If we place the data in them, then how will we fulfill this requirement? If you start from here, we will take data of a course, let's say the course has data structures or a database management system. We have students, for example, we have 10,000 students who are enrolled. One of them is 600 of them who are enrolled and registered in the database management system. So, your design must be able to establish the link between the database management system course and the students who are enrolled in that course. So, the link that would be represented in the form of the relationship. Now, look at the design. Is there a provision in the IIS design that you take a student course, hold a course, and from there you go and go to the student type and pick from all the instances which are registered in this particular course. In this way, you can have this requirement fulfilled that in one student, in one course, which student is enrolled. What will happen is that any course title is given to you. Whether it is data structures, whether it is a compiler, whether it is an automata theory, the course name will be given to you. You will start from any type of course. From there, you will get the link and you will go to the same link with your entry type of students. And from there, all the students who are enrolled in that course you will take their instances. So, how did you link them with the help of a relationship? In this way, we will do the opposite. We want to see that one student is enrolled in each and every course. So, you can take this from here because the student is linked with certain courses. Let's say the student is enrolled in five or six courses. So, the link between the student and the course can be taken out by the help of this report that one student is enrolled in each and every course. So, the relationship provides you the facility that you can link some entry types. Let's go ahead. We have got a relationship between two entry types. The question is, what will we name it? Because the relationship should have a name. But before that, you should know that the relationship name is not as important as the entry types we have. Because the relationship and its nature can be taken from both the links. So, let's discuss what are the rules if we want to name it. The first thing is that there is no specific role. Simple. How do you want to name it? Sometimes, you pick a non-pick from the description of the system to be used as a name of the relationship. For example, students are enrolled and they can use an entry type. If there is no such thing if you cannot use such a name from the scenario from the system, there is a simple approach that the entry types that you are involved in can be named or used as a name of the relationship or use their abbreviation. It's up to you. The purpose is that the link between the entry types will be shown graphically. For example, the entry types and attributes have been represented graphically. So, the relationship will be represented. But just for the identification of that relationship when you discuss it, you can name it. That is why you name it otherwise it is not necessary. For example, you can name the enrol whether it is a student or a class. The name of the entry types is student as a whole and class as a whole. But if you do not have an enrol or a relationship then you can name it student underscore class or as an abbreviation std underscore as you can see here. But keep in mind the abbreviation be consistent with that. To refer once you are using the abbreviation or short name and the second time you are using the full name they may mean two different things. Whereas actually you want to represent the same thing. So, you have to be careful about the naming that there is no such rule but once you have selected something then be consistent with that. Let's go ahead. To represent the relationship in ER design that is diamond as you can see here. Diamond is a symbol for relationship. All the entry types involved in this this diamond is through line. It would be linked with all the entry types. For example, there is only one diagram department or employee. That means the department entity type it is linked with the employee entity type. The name of this is DE the name of this is department amp or the name of this is works. So, you will say that employee works in the department. Similarly, we have a diagram here. Students and books. There is relationship that is being represented with the help of the diamond and that diamond is linked with the entity type. In this, you will say that if you want to name this then you can say student underscore book or s underscore b or you can name it student issues the book. These are the examples in which you represent a relationship. Let's go ahead. Entity types can be identified like the entity type. The way you use the abstraction process the way you identify the entity type the same way you identify the relationship between them. Same thing. The process called abstraction in which you distinguish the properties of the entity type the way you identify the entity types the way you identify the relationships. The entity types involved in a relationship are called participants. As you can see on the screen the different types of relationships are defined on the basis of the number of entity types which are involved in that relationship. The examples and the clarifications will be on the slide. The first type of relationship we are going to read is binary relationship. For example, binary relationship is a relationship in which two entity types are involved. From here you can see that when we say the entity types are involved. This means that we have some other types of relationships in which more than two entity types are involved. But the binary is such a relationship in which two entity types are involved. And then the same thing is how you identify by knowing by analyzing the description of the situation. As you said students issue books. and book entity type. If you look at the point of this relationship then in this relationship two entity types are involved. So this relationship is called a binary relationship. The examples of binary relationship are on the screen. In this you can see a student and a course. These are two entity types. This is a relationship show. It is a binary relationship because there are two entity types involved. Similarly, another binary relationship is a student and a book. A student issues a book or returns a book. So this is a binary relationship. Similarly, project and employee. One project employees work on different projects. So in this way, a relationship or a link is formed by the entity types involved. So you will say that these are the examples of the binary relationships. Let's move on. The way entity types have instances, which we call entity instances. Similarly, you have a relationship that also works as a relationship type in your ER Diagram. And it can also have instances. And one way to represent these instances is that you can represent them in the form of an ordered pair. I hope that you know what ordered pairs are. Otherwise, if you do not know, it is a notation in which we write things in the form of a set. And the members of that set are in the form of pairs. And in the form of pairs, there is a difference between them. So that is why we call them our Enrolled Relationship. Students are enrolled in some courses. So if you look here, we have S1001 which is an ID of a student. And OS is the course code. So we said that this student is enrolled in this course. S10020 and DS. So all these ordered pairs are the instances of the Relationship Enrolled. Let us go ahead. The attributes of the Relationship i.e. the attributes of your Relationship can also be attributed. The complete scenario or the different situations will be clear to you in the next lecture. But what is necessary for you is that the attributes of antitypes and relationships are also attributed. The first example is Ki. Your relationship because of its instances then how would we identify the instances? Because when there are instances, you will need to know how to identify them and how to identify them uniquely. So the first thing is that how will the relationship be made? When there are antitypes when there are participants the primary keys are generally made in the primary key of the relationship. If you look back we said that about the student and the course. When you see that a student can enroll in many courses and many students can enroll in the primary key of the student and the subject or the primary key of the course will be the key of the relationship i.e. the student ID and the course ID. But mind it in every case you do not have the instances of the relationship and not every case but there is one more thing that you should understand that apart from having the key the relationship also has some other attributes rather it may have some other attributes as well. We call these attributes descriptive attributes of the relationship i.e. key attributes and how they are made in the primary key for example enrollment key but apart from this there are some other attributes that can be associated with the relationship for example as we said that the students will enroll the next thing is you will definitely store the grades of the students as well in any examination system what is the grade of a student the question is what you will store with which thing with the entry type with the student with the entry type with the course or with the relationship this becomes sometimes a tricky but if you are a bit tricky but if you pay attention then you will be able to understand the solution of this how is that if you want to know the value of an attribute you need to know only one thing and this attribute will go to the entry type in which you can identify it for example if we say that the overall grade of a student i.e. the CGP what is that so the CGP is associated with the student if you know the student's ID then you can tell what is the CGP but if you want that what is the grade of this student in this course if you just know the ID of the student the problem is that you cannot tell the grade of that student in a particular subject that is why the student is enrolled in let us say 5, 6 or 7 different subjects so by knowing just the key of the student you cannot determine the grade of the student in a particular subject then you move to the other entry type involved if we say what is the grade of that student then you cannot tell the grade of a student in a particular course by knowing just the key of the course the data structures you cannot tell because that course is enrolled now the grade of a student in a particular subject obviously to know this you need to know the keys of both the antitripes that are involved in that particular relationship the participants need to know if you say that student whose ID is S1039 and a course whose code is DS data structures what is its grade then you can tell now you can tell what grade of this student this means to know both the needs of the ID this means neither it can come in the entry type student nor it can come in the entry type in your course in fact this will come as a relationship attribute which means that the relationship can come if it needs then as you can see on the next screen you can see on this screen the same situation is shown in this two entry types are shown student and the course is shown and the relationship between them has a descriptive attribute which is called grade now grade is the attribute of the relationship of any participant antity type you must have understood that on what basis we associate the attribute with a relationship and on what basis with a participant antity type now what you are going to see is the ternary relationship that means the relationship is the other type ternary relationship as mentioned in such a relationship in which three antity types are involved we will say a ternary relationship as we have an example student, class, faculty that means then it is the same thing that how you will identify it from the situation if it is said that in this class in which these teachers are teaching which students are enrolled in the first situation we were only showing that these students are enrolled but if we want to say that in this class in which these teachers are teaching which students are enrolled so in this way this becomes a relationship in which three antity types are involved so we will say this is a ternary relationship here on the screen the example shown are the examples of ternary relationships the first example I just mentioned that student, course faculty these three antity types are involved and these three are linked with different corners which represent a ternary relationship as mentioned the key to this the antity types will be the primary key and like in the previous example this relationship can also have the descriptive attributes the second example it is another example of a ternary relationship in this you can see project, employee and skill on a project a company is using a skill this means a company can have multiple skills a skill has an application description it is also an antity type this is how we have an independent antity type what is the link between these if you want to know which skill a company has used on a project or which skill a company has used on a project you can see that when you have this requirement in this situation three antity types are involved it reflects a situation of the ternary relationship let's go ahead as we have represented the instances of a binary relationship in the form of ordered pairs similarly the ternary relationship can also have instances as we have represented in the shape of an ordered pair we will represent here using ordered triples as mentioned here the instances we are showing are the ternary relationship between the student the course and the faculty in this our first S1013 this is the ID of the student antity type it has an instance ID similarly your DS data structures this is the course ID of the course antity type of the instance and the fact 105 represents the ID of an instance of the faculty antity type similarly different participant antity types their IDs you have represented an instance of the ternary relationship and obviously if someone had a descriptive attribute let's say if you want to place the grade here the grade as a force attribute but mind it the grade would have been a descriptive attribute so this is the representation this is only for the ternary relationship itself let's go ahead unary relationship this is the third type of the relationship in this one an antity type is linked by itself this means that in order to have a relationship you need to have two different antity types that means only one antity type can relate to itself this is also called a recursive relationship this is an example of a roommate relationship if we are storing students and we want to store who is the roommate of a student and the condition is at one time the maximum of one student is that two people can share the maximum of one room so in that case this relationship becomes a unary relationship because the antity type is only one and it is linked by itself the example of this is on the next slide on this slide you have two examples of unary relationship in the first slide it is related to the roommate relationship so you can see that the diamond is shown and the line is linked by the diamond and the second line is coming back to the same antity type so this is the representation of a unary relationship if you look at the other example it is shown that amp and chair person this means that your employee is chair person so in this if you want to show who is the chair person it again becomes a unary relationship in which if you look at the link you will go to a diamond and the link from the diamond will come back to the same antity type this is also a representation of a unary relationship if you look at another example if we have an employee this antity type if we have this possibility that some employees are married they are married to each other so what will happen in this you said that there is a relationship of spouse and that also employees are married so it again becomes a unary relationship because only single antity type also we have represented the same way as we had represented dear students generally we have environments that we represent in databases there are more examples of binary relationships or they say that the type of maximum relationship that we identify in real life are binary that is unary and the ternary relationship is generally not that frequently so if we look at the frequency then the most binary then unary and finally ternary and the type of this is not limit you can expand this up to 4 antity types 5 or 6 which is very very rare it is not that frequently just keep in mind ternary relationship is not the final type of relationship in this you can exist in any number of types but generally these three exist as I said earlier about relationships they might be confusing for you but by the time as you get practice you will get more clear about things similarly one more confusing thing which confuses students the cardinality of the relationships here I have briefly shown R-shifts basically this is the relationship so cardinality is an important thing about the relationships which you have to represent identify and mind it you do not define you do not create the cardinalities you identify them as they exist in the environment that you are working on the first thing is here is the definition number of instances of one antity type that can possibly be related to instances of other antity type you know you will study that situation and the first thing is that you identify the antity types then you identify the relationship then you identify the type of the relationship this is a binary relationship the second thing is the cardinality of this relationship this is very important because the cardinality that you identify ultimately you will have to implement that and if you do not identify the cardinality then the database that you implement then the situations that exist in the environment they cannot be identified therefore it is very important that you see that what is the cardinality of any relationship that exists between two antity types that is to see how we identify it first we will see on the basis of the cardinality what are the types of our relationships so the cardinalities that are between the relationship it could be many to many many to many now let us see how we identify them in different situations and mind it you have to be careful and you need to practice for that the first thing is one to one one to one means that one of your antity types one instance of that can relate to another antity type that can relate to it and see to determine the cardinality to determine you have to study this thing in both the directions initially we suppose that we are always talking of a binary relationship we have a binary relationship first we have department and chair person we have represented chair person as you can see on the screen on the screen we have department and chair person you are both in a relationship if you be careful you can see that one department has an instance that can relate to another antity type obviously one chair person can be and how did you define that you defined it by studying your environment the system that you are developing and you know that one chair person of one department now mind it this is one direction just by looking at this one chair person of one department you will not say this is one to one you have to see it you will see that one chair person's instance can link with one department or with department antity type if your organization has a rule that one chair person can belong to one department or it can be linked this means in this direction your chair person antity type has an instance only one instance of the department can be linked so when you see it in both directions then you will realize then you will declare that this is a one to one relationship and mind it students who make a mistake they make a mistake after analyzing in one direction when they see that only one chair person of one department can declare this is one to one I am emphasizing again that in order to determine the cardinality of a relationship you have to study it from both sides come let's go to the next type of the relationship the second type of the cardinality is one to many this means that one antity type has an instance that can be linked with another type but the second type has an instance that can be linked with another don't make a mistake that after studying from one side you declare the cardinality of the relationship no study it, analyze it in both the directions for example on this screen there is a relationship between the type of the employee that can be linked with another type of the department that can be linked now you study from the perspective of the department we will say that in one department many employees can work or do well this is one direction mind it from here we will see that one instance of the department has accounts registration, library now we have different departments we have a lot of employees one department has accounts how many employees can relate to accounts this is the description in the language of the ER data model if you describe it in the language of the organization then you will say that many employees can work so you realized that because one department can work when you link it to the instance then one instance of the department any type will be linked with many instances of many means that when it is more than one whether it is 2, 3, 4 it becomes in the range of many of the departments one instance of it can be linked with many instances but this is just one direction look at the employees and departments if the employee has an instance how many instances can relate to that department this is the description in the ER data model language and if you describe it in the language of the organization then one employee can work in many departments obviously you cannot determine it this is something this is a rule that exists that prevails in the environment in the systems it is the rule of the organization if it says that one of our employees can belong to many departments this means an instance of an employee entity type will be linked with many instances of the department but first of all we have an environment where the organization says that one employee works in one department in the ER data model we will say that one instance of the employee entity type will be linked with only one instance of the department entity type from this don't confuse that one person can work in one department but changing is something else but when we work in the accounts department we have an employee E509 his name is Muhammad Bashir he works in the sales department he works in the accounts that relationship will be changed the link will be with another instance but the thing is at one time at any particular moment one instance of the employee entity type is linked only with one instance of the department entity type this means when we have studied this relationship between department and employee it becomes one that one employee can work in one department this example is reflecting many to one relationship but again please do not fix the cardinality by studying only in one direction especially this confusion arises in one-to-many situation when you have seen that there can be many employees where one instance of the department can relate to the employee you have to declare on this basis that this is one-to-many if you represent it in one situation it can be fixed by chance that it is one-to-many but technically this is wrong again you have to mention the cardinality of the relationship by studying this possibility from both the directions as I have given you an example it covers one-to-many if you study from one side it becomes one-to-many if you discuss from the other side practically one-to-many or one-to-many they represent the same thing dear students after studying cardinality remember that you determine you fix the cardinality on the basis of possibility of the association what is the possibility of the instances of one entity type being linked with the instances of the other entity type in an environment that means if a company tells you that one of our employees can work in multiple departments but at that time all the employees are linked with one department all the employees so you don't have to make a decision on the basis of those instances but you will fix the cardinality by knowing the rules cardinality is always you will not fix those instances because it is a possibility that means if your employee is not with multiple departments but there may be a situation when it is linked because the organization allows it so you have to see the rule not to see particular instances while fixing the cardinality the last type of cardinality is man-to-many relationship when one entity type can relate to other entity type and its converse is that another entity type can relate to many instances you will see this example on the next screen the example you are seeing on this screen is a man-to-many relationship there are two entity types employee and project this shows that if you study it from the internet then one employee can work on multiple projects which means one employee that would be linked with multiple instances of the project entity type if you study it in the reverse direction then what will happen that your project entity type has many employees can work on that or one instance will be linked with many instances of the project entity type then the first thing this rule will be defined by the organization that one employee can work on multiple projects if this is the case then in the database terminology one instance of the employee entity type it will be linked with many instances of the project same is the case in the reverse order so you have identified these two entity types the nature of this is man-to-many another example students and courses are linked now to determine what we will do we will see how many courses can be enrol obviously we will describe this in the ER terminology that one instance of the student entity type will be linked with many instances of course entity type for example student S1005 is linked with data structures with database management system with networks with operating system this means that one instance it is linked with many instances of the course entity type if we look at the inverse obviously in one course many students enrol and in the terminology of ER data model we will say that one instance a course entity type will be linked with many instances of the student entity type so in this way when you studied it in both directions then you established that the corneality between them is man-to-many dear students today we conclude the lecture here and its summary is that we studied a very important topic of the ER data model that was relationship what happens how do we identify this apart from this there are different types which include unary, binary, ternary and apart from this we discussed the other types then about the relationship there is a very important point what is its corneality so how do we identify the corneality and how do we determine it after this the important point is how do we represent it on a diagram till now the examples we have seen we have only identified the corneality but how do we represent it there are different notations about these notations we will read in the next lecture till then I would like your permission Allah Hafiz