 Sir, could you please tell us what is a clustered primary key? What is a clustered primary key? Clustering is actually a physical property of how the relation is stored, whereas the primary key is a logical notion. So, a primary key uniquely identifies a tuple and it is chosen by the person who designs the schema. Now, when you store the relation on disk, the order in which the tuples are stored can have a significant impact on performance. So, supposing you want to find out all students in a particular class, if the relation which takes is sorted by the course ID and the section ID and so forth, then all the records for a particular section or a particular course offering will be together on disk. So, with just one disk I owe you can fetch all of them. On the other hand, if the takes relation is sorted by the student's ID, it is easy to find out what all courses the student has taken, but if you want to know who all have registered for a particular course, those tuples may be scattered all over this. So, the idea of clustering is that you sort it on certain attributes. Now, if a relation is stored sorted on the primary key, then that would be called a clustered relation is clustered on primary key. But you can have primary key being something, but the relation may be stored, sorted or clustered on some other key for efficiency. So, that is what clustering is about. Does that answer your question? I have another question. Is there any difference between composite primary key and clustered primary key? They are separate notions. The composite primary key is a primary key which has more than one attribute. So, in our case, for instructor and student, the primary key is ID, so that is not composite, but if you see the takes relation, there are many attributes which together form the primary key. So, there the primary key is a composite key. Composite simply means there are multiple attributes and so you can have a clustered composite. You can have any combination. You can take a couple more questions before we start on today's topics. Professor Meghe, Professor Meghe, is there a question for you? Database system stores each relation in a separate operating system file. Then operating system uses it as authentication schema. Which kind of authentication is there, sir? So, first of all, a database system may or may not store a relation, each relation as a file. So, we will come to this when we look at database internals, but many databases keep multiple files per relation, but each file corresponds to one relation. That is a common situation. Now, you are talking of authorization. So, there is database level authorization and then there is operating system level authorization. So, typically all the files used by a database system have the same user at the level of the operating system. And then the authorization, the control which we discussed yesterday, is imposed by the database. Now, of course, if you are a super user or administrator on the operating system, you can look at any file you want. So, authorization can be defeated by bypassing the whole thing. But of course, you have to control passwords and so on. So, you cannot bypass and become administrator. One last question before we start on today's topic. Vishwa Jyoti, Kerala, please go ahead. My question is, is it possible to do a join between two tables from two different databases? Okay. So, the question is, can you perform a join between two tables which are on two different databases? So, many database systems allow you to create a view of a relation which is on a remote database. So, now when you create that remote view and then you do a join on the view, the database system will actually fetch the records from the remote database and then do the join locally. So, many databases support this and it is widely used to integrate data from multiple databases. Let's start on today's session which is entity relationship modeling. Many of you will be familiar with ER modeling. For the few of you who are not familiar with it, let me say a little bit in introduction. ER modeling is part of design process for a database. So, we have seen a university schema with many relations. How did we come up with all of these? How did we decide what was the relation? How did we decide what are the attributes? A good way of doing this is by starting at a high level and understanding what is it that the database is modeling and the entity relationship modeling approach provides a nice basis for doing this. In this approach, we think of a database as modeling a real world system which has entities and then it has relationships between entities. So, what is an entity? It's an object that exists and is distinguishable from other objects. It's a very loose definition. It could be a physical object such as a person, a company, and so on. It could be a logical object, for example, a course or a course offering. It's a logical object, it's not a physical thing. Course is defined by a number of attributes starting with a course ID, a syllabus, and so forth. But it doesn't physically exist anywhere, but conceptually it is a meaningful entity. So, a course would be an entity. Entities have to have attributes which describe them. For example, for people, there may be IDs, names, addresses, and so forth. An entity set is a set of entities of the same type which have the same property. For example, the set of all people in the thing which you're modeling. I don't mean necessarily all people in the world or all things in the universe. But your ER model is for some particular thing. It could be a university, it could be a company, it could be a website like Piazza, which lets people sign up. And each of these would have its own notion of what is an entity set. And what are the different entity sets? So, here is a small sample of entity sets for instructor and students. This is showing a few attributes. Again, in our relations, we have a lot more attributes, but we just shown a few here for simplicity. So, moving on, we have a notion of relationships and relationship sets. First, the relationship is singular. It's an association among several entities. As an example, students have advisors. So, an advisor is a relationship between a student and an instructor. So, here we have student identified by the ID, whose advisor is this person identified by this ID. So, an advisor relationship exists between this student and this instructor. Now, a relationship set is a set of relationships of the same type. So, the advisor in our example is treated as a relationship set, because there are many instances of student-instructor pair, where the instructor is an advisor of the student. Note that there may be other relationships between a student and a instructor. Advisor is just one possible thing. Maybe there is another thing, which is something completely different. Maybe it's a project guide, which is different from the advisor. Advisor may be a faculty advisor overall. And then for your project, you may have a guide, which is another relationship between students and instructors, which has a different meaning. So, formally, a relationship set is a mathematical relationship amongst n, sorry, single relationship is a particular instance. A relationship set is a relation in the sense that, in the mathematical terminology, a relation is a set of these instances. So, it is not a relationship, it could be a single instance. The relation in formal terminology is basically a set of couples. We already know that from the relational model, but this is a formal mathematical notion of a relation. So, we would say that, in this case, the ID is used to identify the student as well as the instructor. So, we would say that, this ID and this ID is an element of the advisor relationship. We'll drop this distinction between an individual relation and a relationship set, because it's fairly clear from the context what we mean. So, we will just say a relationship and not bother about the fact that it's a set or a single relationship, because it's common, it's easy to understand from the context. So, again, the same advisor relationship set shown here using lines. So, there are lines connecting Crick with Tanaka, Cads with Shankar and Zang and so forth. So, this is a relationship set, advisor. Note that, because it's a binary relation between just two things, a single line property, if it were a ternary relationship, then you would have to use a slightly different terminology. But, we're not going to use this loose terminology, we're actually going to use a more formal terminology or diagraming representation rather for denoting entities and relationships. Now, why is the diagraming important? If you look at a text page, it takes a lot of while to read through it and understand what is going on. The same information, which is shown as a picture, usually makes it a lot easier to understand what is going on. So, for the first cut understanding of what a particular database design is about, it turns out that a pictorial representation is actually very nice. That's why we showed you a pictorial representation of the relation schema. Now, the entity relationship modeling looks kind of similar, but it's not the same. You please don't confuse the two. Later on, I'll tell you the differences between those. But, an ER diagram is a diagrammatic representation, which is at a higher level than the lower level relational schema, even if that is shown as a diagram. So, here we have the same two entities and the same relationship we saw with a few extra attributes. Instructor has attributes ID, name, salary. Student has attributes ID, name, and total credits. And, advisor is a relationship between the two. So, how have we shown it? We have shown rectangles for entities or entity sets and diamonds for relationships. Again, a relationship set technically, but I'll just say relationship, it should be clear what it is. So now, within the entity set, we show the name of the entity set at the top and the attributes below. Again, there are many notations. In fact, up to the fifth edition, our book used an earlier notation called the Chen notation. But, we found that in the industry, people use UML notation, which is different from ER notation, to often model the same things. And, we found that the old Chen notation for those of you who are familiar with it, was a little cumbersome. There were a lot of ovals and took up a lot of space, which meant you could put fewer things in a page. It was harder to understand the big picture. So, we changed to this notation, where the attributes are simply listed one below the other. And, an attribute, which is underlined is a primary key. So, we have to choose one or more attributes, which will uniquely identify an entity in this set. So, that's that attribute or that set of attributes is underlined. So, that's the first entity diagram. Now, here is a small extension, which shows that relationship sets can have attributes too. This is a toy example. Supposing, along with the advisor relationship, we wanted to track a date. Maybe that's the date when the student started being associated with that advisor. So, maybe initially the student was with some other advisor. Now, that relationship changed. We want to know how long this advisor has known the student. So, then what are the first date? In reality, you may have more complex stuff and the schema may actually change as a result. We'll come back to some of these tricky issues later. For the moment, there's just a single attribute. And here is how we show it diagrammatically. This, again, is based on the UML notation, where from the relationship diamond, we have a dashed line to a box, which contains the attributes. Now, for those of you familiar with UML, UML also uses diamonds to indicate relationship. Its relationships are usually between classes, whereas we think of these entities. They're very similar notions, classes and entities. Now, coming to the attributes of an entity, we can have several as we have seen. The domain of an attribute is a set of permitted values for each attribute. So, the domain of name is a set of all possible names. Now, in India, people pretty much give any name they choose. But in some countries, the domain of names is actually restricted. As I'm told in Germany and in Iceland and a few other countries, they have a finite set of names, and you can only pick from those names. So, you can't choose any other name. And this is a funny incident where somebody protested against the system. And when her daughter had to be named, she said some name, and they refused to accept it and registered the name as no name. And on all official records, that person's name is now no name. So, it's a funny situation, which incidentally also happens apparently in Maharashtra. When South Indians moved to Maharashtra, they were asked to give a surname. And apparently, this person didn't have a surname. So, he said none, and of course, his surname soon became no name. So, yeah, there's this issue of null values here. Should have been a null value, but since maybe the database didn't allow it, or the human operator didn't allow it, they got a weird surname. I shouldn't say weird, it's a perfectly nice surname, but it was weird when applied to a person without a surname. Okay, so moving back, domain is a set of permitted value. Sometimes there are constraints on this. For example, if you have salary, it should be generally a positive zero to a positive thing, although occasionally people pay to get a job, as we know from recent incidents involving the railway ministry. And for that matter, at five-star hotels, there are these people who open car doors. I'm told they get so much money from tips that they actually pay for the job. They don't get a salary, they pay to get that particular job. But leaving such weird things aside, you may have a constraint that a salary should be positive. So the domain would be positive, maybe numerics with up to two decimal places, up to the PISA, not beyond that. Okay, then you have other types of attributes. You can have simple attributes versus composite attributes. You can have single-valued versus multi-valued. You can have derived attributes. So what are these coming up in the next slide? So here are composite attributes. Name may be broken up into first name, middle initial last name. Address may be broken up into street, city, state, and postal code. And further, the street part of the address may be broken up into, this is street number, but in the Indian context, it's the house number, the name of the street, and the apartment number, which is very common. So these are composite attributes with parts, and the component attributes are the parts of the composite attribute. And attributes which don't have parts are simple attributes. Now multi-valued attributes are also common. For example, you may have phone numbers, there's a set of phone numbers for a person that's pretty common. And address books, mostly you're allowed to add more phone numbers. If you go to your address book on your mobile phone, you're allowed to add many phone numbers for a particular entry. Finally, derived attributes can be inferred from others. In India, for some reason, all our official forms ask for age. Which is rather silly if you think about it, because age keeps changing, and your ration card or whatever other document gives you a raise as of when the card was issued, and people are supposed to add the number of years since then and figure it out. That's the wrong way of doing things. Age should be a derived attribute, which should be computed from date on birth. Maybe they did this because many people didn't really have a date of birth. And many people, of course, don't age. They prefer to be whatever age for the rest of their life. Either on paper, and of course some people, mental or emotional age, remain fixed for many years, or maybe reduces. But leaving such things aside, it should be a derived attribute which is computed from the date of birth. So here is how we represent these situations in our ER diagram notation. Again, unfortunately, there are many standards for this, and we created our own standard. So this is not something which you may find other people using. But our goal here was to get something which is like UML as far as possible. But add some features which UML doesn't actually support in order to be able to do full ER modeling. So the specific extensions to UML here are things which are not in core UML. But UML itself is in some sense extensible. You can always add a notation to UML, and in some sense, that's what you're doing. You can think of it as extended UML a few weeks. So what we have here is instructor has an ID underlined, so it's primary. Name has a subpart, so it's composite, whose components are first name, middle, initial, last name. Address is again composite, one of whose components is street, which itself is composite, and so forth. So the indentation shows the level of next thing. Coming down here, phone number is shown enclosed in curly brackets to indicate that it is a multi-valued attribute. And lastly, age is shown in parenthesis to indicate it's a derived attribute. Now this last part, derived attribute, is actually part of UML, because UML class diagram include methods, and this is just a method. Now we come to degree of a relationship set. So binary is the most common situation, which involves two entities. And in real life, most relationships, but not all, are binary. Now, there are many relationships which at first cut may appear non-binary, but in fact, they are binary. So we will come to a concrete example of a ternary relationship in a little bit. But before that, let's take some instances of a relationship, which may appear non-binary, but maybe are not. So let's say that a person has a father and a mother. Maybe we could think of a ternary relationship between person, father, and mother, but if you think about it, there are some issues with having a ternary relationship between person, mother, and father. What if we don't know the father of a person? This does happen, or maybe we know it, but it's not represented in our database. So we don't have an entity in the database corresponding to the father. But we do have something corresponding to the mother. How do we represent this? Now in the year modeling notation, or year model in fact, not just the notation, in the year model, a relationship must identify a set of entities. You cannot have null as one of the participating entities in a relationship. So if you don't know the father, and we only have a ternary relationship, we cannot represent the fact that we know who's the mother of this person. And in fact, it makes more sense to have two relationships, mother and father, which relate a person to their mother and to their father. In fact, we can even have one relationship saying parent which relates a person to their parent, and if you have the gender of the parent, you can figure out whether it's the mother or the father. Or if you want to make it explicit, you can have an extra attribute in the relationship which says, what is the role in this relationship? So whether it's a mother or the father. May not be meaningful for parent, but for other things, this is meaningful. So ternary relationships, what look ternary, may be better represented really by binary, but not always. So let's look at this situation. Supposing students work on research projects under the guidance of an instructor. This happens during summer, many times, and sometimes during summer as well also. So we have a project guide which relates a student and project guide and the project itself. The student, instructor and project are related. Now, should this be ternary or should it be a combination of binary? So what we want to represent to this instructor is guiding this student on this project. Note that we have assumed here that project is an entity. If project were not modeled as an entity, then maybe it's just the attribute of relationship between student and instructor. But we do in this case want to model a project as an entity. Maybe there is other information about the project, so it is an entity. Now, how do we represent this? So as a ternary relationship, no problem. So a student is connected to a project and an instructor and it's also mandatory in our case, let's say to have a instructor for a project. If not, we can have a separate relationship between student and project indicating the student is working on the project. So let's keep that aside. So this ternary relationship, if you try to break it into parts. Supposing we break it into student project and student instructor. What is going to happen? We know the student is working on this project. We know the student is working for this instructor. But is this instructor guiding the student on this project? It could be that a student is working with two different instructors and on two different projects. But which instructor is guiding the student on which project? We don't know. So we break it into two binary relationships. Therefore, in this case, a ternary relationship is more appropriate. But it turns out it's a little hard to come up with examples. I'm sure you're thinking in your mind, this is a very artificial example. Isn't there a more natural example? And we try to come up with a more natural example in the university domain. And we couldn't come up with a very good one. This is a little unusual, little contrived. But it just reflects the fact that most relationships are in fact binary. It's hard to come up with truly ternary relationships. So this is a diagrammatic representation of the same thing. We have instructor, we have student, we have project as entity sets. We have not shown the attributes of project. And project guide is the ternary relationship shown as a diamond linked to all of the above. So here is a quick quiz. This is something which I told you a few slides earlier. And we are talking of a person entity set and the relationship between the father and mother. And I've already told you two binary relationships are the appropriate thing here, so hopefully all of you were listening and got that answer already. I already explained why a ternary relationship is a bad idea. Because if we don't know one of the two parents, we can't represent it. The last one, I mean, sorry, the third one, an entity set parent linked by three relationships to person, the person whose parents we are noting, the father and the mother is actually an option. Technically it is not incorrect. We can always take any degree relationship and create a fake entity set representing the relationship and then relate it to each of the participants. I'll come back to this later in the talk, but that is a valid thing. And we will see why it is valid later. But in this case, it is inappropriate because in the first place, the ternary relationship was inappropriate. You could change a ternary relationship to a set of binary relationship, but in this case, it is inappropriate. And finally, an entity set with attributes person, father and mother is totally inappropriate because of the following thing. We have person entities that we want to recognize as entities. If we store an entity as an attribute, not as a relationship, we have lost track of the fact that this attribute represents an actual person in the database. If you just store the name, the name is there, but maybe we don't know who that person is in the database, the model does not show this. So in the model, to make it explicit that the relationship is to a person who we are modeling in the database, it should be a relationship. It should not be an attribute. This issue comes up repeatedly and it's in fact, sometimes it's a design choice. For example, should you treat a phone as an entity or just as a phone number, which is just a value. So many times, a phone number is just a value, it's not an entity. For a person who is keeping a phone book. But for the phone company, a phone is not just a attribute, it is an entity, a phone number, either it's an entity. Because there is a history to the calls made from that phone, a bill, and so on. It is truly an entity. So it depends on who's looking at it. But if you actually have an entity, and instead of creating a relationship with that entity, you put an attribute representing that entity. That is a design flaw. So for example, if we take this particular scenario, let's go to binary, this is better. Yeah, this diagram I've jumped way ahead, but the goal was to show you a situation where for student, if you recall in the original relational model, we had department names stored with the student. But when we come to the year modeling, we have modeled student as an entity set. We have modeled department as an entity set. But we have not stored the department name here. What we have done is, we've created a relationship. Forget all the other notation. Just look at this fact that there is a dimension to the relationship between student and department. So that's what we should do. We should not store department as an attribute of student. It should be a relationship. Yeah, so that was related to the last point where using person, father, and mother as attributes of something is wrong. It should be relationship. Now some more terminology and notation. It's usually very important to keep track of cardinality constraints on relationships. So for binary relationships, the constraints are one to one, one to many, many to one, and many to many. This expresses the number of entities to which a given entity can be associated via an entity set. So this is shown pictorially here. This is not a year diagram. This is just showing actual entities and the lines of relationship between the entities. In this case, each entity on the left side is associated at most one entity on the right side and vice versa. Note that you may have some extra things here which are not mapped. So this is one to one. One thing on the left can be associated at most one on the right. And similarly, one thing on the right can be associated at most one thing on the left. Some may not be associated in this. This one is one to many, which says that one on the left may be associated with many things on the right. On the other hand, a thing on the right can be associated with at most one from the left. Again, in both sides it's at most. The figure doesn't show it, but you can have extra elements here and here which are not mapped. That is, they do not participate in the relationship. Many to one is just a symmetric version of one to many with the two sides flipped. And many to many is the most general thing. There are no constraints basically. So anything here can be mapped to any number of things on the other side. So here we have A1 mapping to two things. And similarly, B1 and B3 mapping to two things on this side. B4 is not mapped to anything. So that's conceptually the kinds of things that can exist. Constraints on the relationships that can exist. And in the ER notation, we show the constraints as follows. There are again many different alternatives for showing the notation. And we can express it by a directed line with an arrow. There is an arrow which says that the thing on the arrow head side is one. And the other side could be one or many. And we'll see that in a moment. An undirected line with no arrow signifies many. So let's see this notation by an example here. Here the relationship between instructor and student, the advisor relationship is shown with an arrow on both sides. It means one to one. What does it mean? A student can have at most one advisor. And an instructor can have at most one advisor. Now in most engineering colleges, we have an advisor system. This is not meaningful because an instructor may have many students being advised by the instructor. But postgraduate medical rules are a little bit different. I think they may be getting changed. But at least some time ago in a medical college with post grad, I believe an advisor could have only one advisor. That is, a person could be guiding only one student, not more. In that case, the advisor relationship would be one to one between student and instructor. This is a diagram showing one to many, which shows that in this case, an instructor, the arrow is in the direction student to instructor. This means that a student can have at most one advisor. But there's no arrow in the other direction. So an instructor can be advising many students. So this represents the more common scenario where a student may have at most one advisor. But an instructor can be advising many students. This one is the converse where the arrow is from instructor to student. Here, an instructor can have at most one student being advised. But a student can have many instructors advising the student. This is pretty unrealistic. It's just to show the notation. There are other scenarios where it's useful. And finally, many to many relationships where an instructor can have any number of students being advised. And a student can have any number of instructors advising the student. This is a common scenario for our PhD, for example. There are often joint advisors, two people together advising a student. And so it's many to many. A student can have many advisors. Advisor can have many students. So those are the cardinality constraints on binary relationship. There's one other kind of constraint called the participation constraint of an entity set in a relationship set. So this is illustrated using a different example. Here, we are actually jumping ahead on some notation. So don't bother about this double diamond notation. That's coming up a little bit later. Don't bother about this dash underscore notation coming up. Focus on the double line here. What it is saying is that a section has a relationship with a course. Because a section is for a particular course. And every section must be associated with a course. It makes no sense to have a section where you don't know what course it is associated with. And that is indicated by a double line here. It says the participation of section in the relationship with course, that relationship is called sec course. So a section must have an associated course, not also the arrow here, which says that a section can have at most one course. So it must have exactly one course. At most one and at least one, so it's exactly one. It makes no sense to have a section which is representing two different courses. Therefore, we have this arrow. So verbally, we say that the participation of section in the relationship with a course is total. Partial participation means that some entities may not have a relationship, may not participate in that relationship. So going back here, you can say that an instructor need not be an advisor to any student. So the participation of instructor in the advisor relationship is partial. In this case, we've even allowed the student's participation to be partial. That is, we may have students who don't have an advisor. If he insists that every student have an advisor, at least one advisor, this line would become a double line. Now, there are a few other alternative notations to this double line and arrow notation. One which is very commonly used is to put limits, cardinality limits on the line here. So in this case, instructor participates zero dot dot star, meaning the instructor may not participate in advice at all, but the instructor may participate in many advisor relationships. On this side, the one dot dot one means the student must participate in at least one advisor relationship, and in at most one. So students must have exactly one advisor. So let's come back to the quiz shown in this slide. The alternative notation here, I'm not going to ask you to solve it. It's straightforward. This particular thing says the relationship is, the options are many to one from instructor to student, one to many from instructor to student, one to one, many to many. So in this case, a student can participate at most once. So it's clearly not many to many. And is it one to many from instructor to student or many to one from instructor to student? So this can cause confusion. So think of it this way. If you want a many to one from instructor to student, that means many instructors can be associated with one student, which is not correct here. It's actually the other way. One student can be associated with many instructors. So it is one to many from instructor to student. So one instructor can be associated with many students, but not the other way. So the answer is two. Okay, some more terminology. We have keys for relationship sets. We also have keys for entity sets, which we already saw underlined. Now a key for a relationship set is a super key for a relationship set is the combination of primary keys for the participating entity set. Now what does this statement mean? What do we mean by saying that the primary keys for all the participating entity sets form a super key for the relationship set? It means that a pair of entity sets can have at most one relationship in a particular relationship set. Now this may be equally confusing. So let me explain this by example. We had the advisor relationship between student and an instructor. Now that relationship can be many to many. That's not an issue. But the point to note here is that a particular student and a particular instructor can occur only once, at most one, in the advisor relationship. They cannot occur twice. To think of it as a table, there can be only one row in the relation advisor between a particular instructor and a particular student. Now why would we want it to appear more than once? So sometimes people want to represent an attribute for the relationship set. So in our case, we had a date. Supposing instead of date, we want an attribute which is maybe every time that the student met that instructor. We want to track how many times the student met the instructor. So every time the student met the instructor, we have a date. So let me switch to the whiteboard. So we have instructor, we have student, we have advisor in between, and we have a date here. So now this allows just one date in here. If you want to have more than one date, you may be tempted to say, well let's just say there are many relationships between the same instructor and the same student with a different date. So if we think of it as a relational table, instructor ID, student ID, date. This would be the relation which we create in the end. We have not yet come to how to translate it, but this is what we would have in the end. Now the previous slide, what we said is that the primary key of instructor and the primary key of student together form a primary key for the relationship. So what we are saying is that this plus this together is a key for the relationship. Therefore the date cannot occur multiple times. So we cannot have two rows with the same instructor ID and the same student ID in the advisor relation. That is exactly what that constraint means. So if you wanted multiple dates, what would you do? Well, you create a multivalued attribute. How do you translate that to a relation? We will see, but the net effect will be a relationship where date is also part of the primary key, but that is a translation process. It will come later. In terms of the diagram, if you want multiple dates, make it a multivalued attribute. This part just says what we just discussed. Now what we have said is that the combination of primary keys forms a super key. We did not say the combination is a primary key because there may be extra attribute. We do not have a slide on it here. But let us say the same advisor relationship. Supposing we had a constraint that the student has at most one advisor. Then if you come back to the white board, if we have a constraint that a student has at most one advisor, then the primary key of this thing should be just student ID. Instructor ID is not required because student ID by itself uniquely identifies a row in this table. The combination SID, IID is a super key, but it is not minimal. Then we should have just the SID. This is the case when we have an arrow this way. Student has at most one advisor. Then the primary key chosen will be just student ID. What this means is when we choose the primary key for a relationship set, we have to take into account the cardinality constraint. If there is many to one in this direction, then the primary key will be the thing on the many side. If it is in this way, in the opposite way, then the primary key will be the other one. The last case is if it is one to one, then this combination, IID, SID is the super key. But there are now two possible primary keys. IID by itself is enough because there is only one student with an instructor. SID by itself is enough because there is at most one instructor per student. And you have to choose between these two to decide what is the primary key. Which you choose, there is no clean way of choosing based on the one to one constraint. You may choose it based on whatever other things there are. But either of the two, IID or SID would be a choice for a primary key. This shows mistakes which can happen when you come up with an ER diagram. So I have kind of mentioned this particular thing earlier. When I said that if you have a relationship between instructor and department, that relationship you are going to call INS department. We saw this diagram earlier, actually. Relationship between instructor and department. Now if you have attribute department name in the entity set instructor, that would be wrong because there is already a relationship between instructor and department. If you try to fix this by removing the relationship and only storing the attribute, that is also wrong because we lose track of the fact that the department is actually an entity set. And if you store it as just an attribute, we lose track of many good things. In particular, when we convert to the relation schema later, we will have foreign keys corresponding to relationship. And we will lose that if we make it an attribute. So then you can store any old value as department name, not necessarily a valid department name. So we don't want department name as an attribute. We want it as a relationship. However, when we convert the relationship set to an actual relation in the database, that thing may reappear as indeed happened. In our relational schema, department name was an attribute of instructor. Couple of other notations. We have a notion of a role. Pre-requisite is a relationship between course and itself. One course is a prerequisite of another course. But which one is the prerequisite of what is indicated by role, identification on the edge coming out of the relationship. So one of the edges is labeled course and the other edge is called prerequisite. Similarly, for advisor, one of the edges is called, well, there it's okay. We have separate instructor and student, a separate relationship set. Here, because both relationship, both edges are to the same entity set course. We need a role. So that completes the basic here notation that I want you to cover.