 In part 2, we are going to look at more features of VR diagram. The first feature we are going to look at is weak entity set. This is sometimes a confusing concept. Conceptually, a weak entity set is any entity set that does not have a primary key. Now, this may sound weird. Primary key is something we define for it. So, why would it not have a primary key? I will explain that in more detail in a little bit. But, another way to think about it is a weak entity set is one where the existence of an entity depends on the existence of another entity which is in a set called the identifying entity set. This is actually a better way to think about it. So, in our university schema, there is a notion of a course. A course may not have been offered. It is a brand new course. The course has an existence. It has a syllabus. It has a number of credits and so forth. But, can a section of a course exist without a course? It does not make any sense to have a section with no associated course. So, section is existence dependent on course. And therefore, it might make sense to model a section as a weak entity set, not as an identifying entity set. I will come back to this section issue in a moment. But, a few more terminology. The discriminator of partial key of a weak entity set is part of a primary key. So, we do not have a real primary key, meaning we do not have enough attributes to uniquely identify it. But, there are some attributes which are used to distinguish different occurrences which are related to the same identifying set. Now, what does all this mean? Let me show a figure and explain all these concepts. First of all, course is a strong entity set is one which is not weak. Course is a strong entity set. It has a primary key course ID. It has a couple of attributes. Section, we decided to make it a weak entity set and related to course. Now, what are the alternatives? We know a section is dependent on a course. So, it has to be identified with a course. So, we could have created several alternative diagrams. One alternative would be to give a section ID which is like a globally unique integer. And then, a diagram would have looked like this. Instead of section ID being dashed line here. A dashed line means it is not a full primary key by itself, but it is a discriminator attribute. And a section is uniquely identified by the course ID of the identifying course and section ID semester here. These four things put together only form a primary key. These three by themselves are not sufficient as a primary key. The other notational thing here is that we draw a double diamond to indicate that section is identified by course. The fact that section does not have a underline here means it is a weak entity set. There is no primary key. And therefore, it must be identified by some other entity set. And which one? The double diamond is what helps you to understand which is the identifying strong entity set. Not also that there are a couple of other constraints. A section must be identified with a course. Therefore, there is a double line. A section can be associated with only one course. It cannot be associated with two courses. Therefore, there is a arrow on this side. So, that is the summary of this. Now, why did we choose section this way? We had two alternatives. One we could have put course ID also as an attribute of section. And then the primary key would be course ID, section ID, semester here. Let me repeat that. One alternative to this one would have been to add course ID as a field of section. Now, this has a problem which we discussed earlier. We have a course entity. If we store course ID as an attribute here instead of as a relationship, we are losing this connection. We are losing the ability to say that course ID here must be a valid course ID in this entity set. We lose that. So, we do not want course ID as an attribute here alone. Supposing we store course ID as an attribute and have this relationship, both section course and course ID here. Now, you have duplication. This relationship already identifies which course. The course ID stored here must also be for the same course. So, then you will have written an information. Therefore, we chose not to put course ID here. We chose to in effect inherited from the identifying course and this became a weak entity. So, this is the design decision we chose. The alternative design decision would have been to have a globally unique section ID. Just an integer which is created internally in the database and then that would become a primary key and that has no other meaning. Then, you associate that section with course as we have done here. Except now, it should not be a double-diamond. It would be a single-diamond because this is now no longer a weak entity set. It is a strong entity set. This is also perfectly valid. There is nothing inherently wrong with this design. Many people do create schemas like this where every entity set is a strong set and you just create artificial IDs. In the actual implementation, these IDs are generated within the database. There is a downside to this. If you want to export information from such a database and import it in another database, these IDs have no meaning externally. So, when you export that information, you are going to export an ID which has no meaning elsewhere. When you want to import it there, that ID may clash with something else there and become a mess. Whereas, if you do not create artificial IDs, all the IDs are things which are actually visible, then what you export is clean. There is no internally generated stuff there. So, there are trade-offs. If you do not care about exporting information from here to somewhere else, maybe that design is okay. Our design does not have any hidden new values. So, it has some benefits. We have already covered everything in this slide. I will just let you read it for a moment. This slide simply repeats the points I made earlier. So, moving ahead with this extra notation, there is a diagram which you cannot possibly read on your screen. So, font is way too small, but you can look at it offline. So, this is the ER diagram we have used for the university. We have seen bits of this earlier and I will again cover some bits of this later. But let me just mention a few things. Even if you are not able to read the details, this is student over here. This is department and this is a relationship student-department between student and department. Similarly, section is a weak entity. We just saw this section and course. This is exactly what we just saw. Course has a relationship pre-rec from course to course, but course also has a relationship to department which is this relationship called course department. Note that student does not have a department name, instructor does not have department name, course does not have department name at this stage. All of these were there in the relations schema, but here they have all become relationships. And here you have time slot as an entity with time slot ID as a primary key and then the times when this time slot meet. So, those not familiar with time slot in IIT as in many places, we have certain pattern. So, time slot 1 will meet at a certain time on Monday, a certain time on Tuesday and a certain time on say Wednesday. So, what we want to record here is time slot ID 1 is on it has a multi set here, a set rather. The curly brackets if you can see it, there are curly brackets here. That means there is a set of these things. Each thing here has a day start time and end time. What is this? This is basically composite thing which says day Monday, start time 830, end time 930. Day Tuesday will be the next value in that set with start time say 930, end time 1030. And the third thing in the set may be day Wednesday, start time 1030, end time 1130. So, time slot ID 1 is now associated with this set of days, start time and end time triple. That is what is represented by the entity set time slot here. Entity set is time slot, its primary key is time slot ID. Note that this is not how the relational model looks. This is the ER model. Now, a section we already saw is associated with the course, but it is also associated with the time slot and it is associated with the classroom. Again, classroom has building room number capacity and section is associated with the class. You cannot see it very well, but there are some constraints here. This double line means section must have a time slot. The arrow here means at most one time slot. This double line means section must have a room. At most one room is this arrow here and so forth. The next major task is if we have an ER diagram like this, can we just use the ER diagram directly and build an application using the ER model itself? Now, if you look at history, the relational model is what we are using to build stuff. There is a query language for the relational model, but somebody had asked this question on chat. Can we use ER diagram directly, ER model directly? This is definitely a very valid question. Can't we have a database that directly implements the ER model and just specify the model and then have a query language which works with this and leave all the underlying details of how the data is stored as a lower level detail which the database deals with. Maybe we can control it, but at the logical level, we do not need to worry about the lower level details. My lower level details here I mean the actual relational schema, but that is not how things evolved. The evolution was the relational model came, the relational database came and then people who are doing design realized that jumping straight into relational database design is counterproductive. What you want is a higher level view of what is going on. After that, only you should move to the relational model. So, ER diagrams came as a modeling tool not as a database implementation, which is why you do not see any database which says I am an ER model database. They say I am a relational database, but nobody says I am an ER database. Well, not quite true. Some years back, Microsoft decided to push what it called the entity data model and build a database implementation which supported this model directly. Actually, underneath they store it as a relation, but the model that they exposed to programmers is directly this entity model. It is not exactly the entity relationship model, but it is very closely related called entity data model and then they defined their own query language and what not for it. But to the best of my knowledge, this EDM has not taken off that much. They had some nice features. They have nice integration with programming languages, something called link, li and q, language integrated query. So, they did a lot of nice things, but unfortunately, it is tied totally to Microsoft and people have not warmed up to it that much. So, yes, you can directly build a database on a ER model and it has kind of been done, but very few people actually use those. So, it comes back to using ER modeling as a design tool and then we convert the design to relational database. So, how do you do this conversion? So, here we are going to show how to do it manually. Again, there are some tools which can help you with this. Somebody had asked a question. Are there tools for converting an ER model directly to relational table? And I believe ER win offers such a tool. I have not used it myself. So, I cannot say much about it, but there are tools to do this. But usually, this is not such a big deal. So, doing it manually is not major problem. So, here is how we convert ER schema to a relational schema. So, we will start with entity sets with simple attributes. There is no composite, no multivalued, no derived attributes. So, this is actually very, very straightforward. In fact, it is very, very straightforward for strong entity sets. If I had a strong entity set course, what I will do is create a relation course with course ID, title and credits as I will be able to do. This is not exactly the final relation because department name is not yet in it. That will come about later on. Similarly, student strong entity set would turn into relation student ID name total credits. Note that the primary key in the entity case will become the primary key in the relation also. The student relation has ID as primary key. What about weak entity sets? Section is a weak entity set. What happens to it? We form a relation section with first of all the primary key of the identifying entity sets. In this case, section is identified by codes. The double diamond indicates that. So, we will take the primary key of this, which is codes ID, take it in there. Then, we take all the discriminator attributes here, the ones with the dash underline and stick it all there. And the primary key for section will be this thing plus all the discriminator attributes. There may be extra attributes here. In this particular case, there are no more attributes right now. So, the primary key contains all four attributes, which are currently there. So, I hope that is clear. So, it is very straight forward to convert entity set simple attributes. We will come back to complex attributes later. Now, moving on to relationship sets, what do we do? So, any relationship set, what we do is we take the primary keys of the entity sets that relate. In this case, advisor relates student and instructor. So, we create a relation containing the primary key of student ID, the primary key of instructor, which is also ID. We have a name class here. So, we renamed it to SID and IID. Now, we have to choose the primary key for this. This particular thing seems to indicate that the primary key is SID and IID. But actually, that does not match this diagram. In this diagram, there is an arrow in the direction student to instructor. What does that mean? A student cannot have more than one instructor as advisor. Therefore, the primary key for this advisor relationship should be just SID. IID should not be part of the primary key. This is a mistake. Now, let us see what happened to those department name attributes. We had student, we did not have a department name attribute here and we had a relationship between student and department. Similarly, we had a relationship in step between instructor and department. Note that the relationship is total, double line and many to one to department. One option would be to create a table for instep. What would be the attributes of this instep table that we create? Instructor ID and department name. But the basic observation here is that we are creating an extra table unnecessarily, just increasing the overheads. Because each instructor can have at most one department and in fact, they have exactly one department. So, it actually makes sense to merge the relation which we might have created for instep. We could have, we already saw how to create a relation for a relationship and a relation for instructor. This one for this one for this. We can merge those two relations into one in the case of many to one and one to many relationship or also for one to one. We will come to that later. So, in this case the table for instep is merged into instructor which side on the many side. It is not merged into the table for department because there may be many instructors in a department. But there is only one department for instructor. So, when we merge the table what do we get? ID is already present. So, the extra attribute is department name which comes from this thing. So, the table for instep would have had ID department name. Now, what do we do? We take the department name and stick it into instructor. This is the schema which we got. In our schema, there was no instep table, there was only an instructor table and instructor had this extra attribute department name. Similarly, student had the extra attribute department name. So, that is what happened here. What about the advisor table? We could have stuck it into student, but we chose not to. Now, one of the reasons we chose not to is that this relationship is not total. If it is not total, what does it mean? A student may have no advisor. If it has no advisor, what would happen here? The advisor ID field of the student relation would have had a null value for advisor. So, we prefer not to have null values unless forced to. Therefore, we kept this relationship as a separate relation called advisor and then there are no null values. We avoid the null value. Now, moving on to one-to-one relationship, either of the two sides can be chosen as the many sides. That is the relationship for a, sorry. If we have a one-to-one relationship, when we convert that relationship to a relation, that relation can be merged into either of the participating entity sets relations. Which one? It is up to you. Either would work. The next bullet here is if participation is partial on the many side, replacing a schema by an extra attribute would result in null value. This is exactly what I told you. If we had folded advisor into student, we would have had null values on the many side. And the last bullet here, say the schema corresponding to the identifying entity set, that is this one. This one, set course. This, it turns out is totally redundant. We do not have to create anything for it. Because, if you think about it, what does it say? That a particular section is associated with a particular course. But if you see the section table we created, it already has the course ID as a primary key. So, there is no more, any need to create this relationship as a relation. So, this is simply ignored totally. Not needed to be explicitly done, because course ID from here is copied into section. In fact, you can think of it as, you know, many-to-one case, where the primary key from the one side is copied into the many side. And then this relationship is redundant and thrown up. It is really the same thing. So, that wraps up the basic constructs, entities and relationships. Now, the next construct is composite and multi-valued attributes. What do we do for this? Composite attributes are actually very easy. So, if I had name with the field inside it first name, what do I do? I can replace it like this instructor. ID is the first field. The second one is first name becomes directly the second field. If there is a name conflict, supposing there is one more attribute here with the same name inside it, I could have said name underscore first name, name underscore middle initial, name underscore last name. But, here it is not needed. So, first name, middle initial, last name directly become attributes of the relation schema. You are basically flattening the whole thing out. They were in separate parts. Now, we are simply quishing it together into a flat schema. Similarly, address, what happened? Street numbers, street name, apartment number, they are all here. Street number, street name, apartment number, city, state, zip code and date of birth is here. What about phone number? It is not in this schema. It is a multi-valued attribute. Now, we cannot store it in this schema because our basic assumption is that relations do not have set valued attributes. The relation model does not allow this. There are extensions which allow it, but there are good reasons not to use it, which we will see later. If you did that, it would not be even in first normal form. Therefore, what do we do about multi-valued attributes? The answer is we create a separate relation for multi-valued attributes. So, I will come back here. We have instructor with a multi-valued attribute, phone number. What do we do? We create a new relation, inst phone. What does inst phone have? It has the primary key of the instructor, which is id and then it has all the attributes of the multi-valued attribute here, multi-valued attribute here, phone number. Here there is just one attribute. So, that becomes an attribute here. So, inst phone with id and phone number. That is what we end up creating. So, every multi-valued attribute is turned into a separate schema called EM, in this case inst phone. Now, what is the primary key of this relation inst phone? The primary key of instructor is not enough, because there may be many phone numbers. So, this plus this together forms the primary key and every value of the multi-valued attribute results in a new couple here. So, for example, if I had an instructor with id 22222 and two phone numbers, this and that, we would end up creating two tuples. This is one 2222 with the first phone number and the second tuple, same id and the second phone number. And these two tuples would be in the inst phone relation. So, I hope this is clear. Any multi-valued attribute, we create a new relation. Every value in the multi-valued attribute set turns into a new tuple here and the primary key of the entity set becomes one of the attributes here. Now, what about time slot? What did we do there? We actually, if we did it exactly as described, here is what would have happened? Time slot is a entity set, which has a multi-valued attribute with day start time and time. What we would have ended up doing is creating two relations. The first relation is a time slot relation with a single field time slot id, which is a primary key, because I know the field here. The second one is a relation corresponding to the multi-valued set. What would its attributes be? The primary key of this thing, which is time slot id, day start time and time. So, that is this relation, time slot id, day start time and time. According to what I just said, the primary key would be all of these attributes, but actually there is some extra information here, which says that given a day and a start time, the end time is automatically, you can infer it. And therefore, what did we do? We made the primary key as time slot id, day start time. It did not include end time. So, that is an extra step, which was not in the basic thing, but we could infer it and the primary key does not include end time over here. Now, we would have created two separate tables. Let me draw it on the white board to explain what we would have created. If we would have created this section, we would have created, let me call it time slot master, just to distinguish it from the current time slot table with time slot id. And then we would have created the existing time slot table, time slot id, day start time and end time. So, these are the relations, which we would have created. There is some glitch here. Section would be one table. I have not shown all the attributes here, but let me just show the one relevant attribute. So, section has an attribute called time slot id, which would be a foreign key referencing this. And here, again this would be a foreign key referencing that. And the primary key for this would be these things. So, this is a design, which we would have generated if we applied whatever we have seen so far. But this is not the schema we created. And the reason we did not create this schema is we would end up with two tables, time slot master and then time slot. We could have called it maybe time slot details to make the name a little more clear. Sorry, spelling mistake there. Time slot details. So, we could have done that. We did not partly to keep our schema simple. If we had done this, then this could have time slot id to time slot master could have been enforced as a foreign key. What we cannot enforce though is that a time slot id here actually has some time slots associated with it. So, we could put in a value here, which has which never meets. Time slot 11 never meets at all. Is that reasonable? I do not know. Maybe it is in some context, because you have like self study courses, which do not have a formal meeting time. So, maybe you have a special time slot for self study courses, which never actually meets. But we chose to go the other way partly just like that, just to show you some alternatives. So, this is an alternative we could do. There are some trade-offs. The benefit is we have one less table. We do not have this extra table time slot master. We just have a single time slot table. The drawback is we can no longer enforce a foreign key. So, that is a trade-off. We just chose one of these things for simplicity. Coming back to our slide, this is just completing what I told you. So, the last two features which I want to cover are specialization and generalization. And aggregation I am not actually going to cover in detail here, but I have a slide. I will just briefly mention it. What I am going to do though is first cover specialization generalization. And then let us go back to the ER model, which we have been doing for Moodle and then see how to apply those concepts. And then I will come back to converting specialization and generalization to relational theme. So, what is specialization? The idea is we start with some higher level entity, let us say person. And then we realize that not all persons are the same. There are some persons who are instructors, some who are students. Now, can a person be both an instructor and a student? Maybe or maybe not. That we can decide later. The key thing to notice, we have an entity set person and we decide there are going to be subsets of this called instructor and student, which may have extra attribute. Person has a set of attribute. Instructor may have some extra attribute. Student may have some extra attribute. But instructor and student are persons. So, instructor definitely has all the attributes of person and a student definitely has all the attributes of person. This is called attribute inheritance. Diagrammatically, we show it by a triangle and I will show it to you in the next page. So, this is the notation. We have person. In fact, here what we did is instead of just instructor and student, we create a deeper hierarchy to illustrate the possibilities. A student is a person. So, here we are following the UML convention for showing specialization, where we have an arrow pointing up that way, an arrow with an empty arrowhead. An arrow with a filled arrowhead means something else in UML. We are not using that here. Note that this is a single line. It is not a relationship. But if you had a solid arrow here, you might think that UML actually allows binary relationships to be shown by a single line without a diamond. And if you put a solid arrow, you might think it is a relationship with a many to one relationship. The hollow arrow removes that ambiguity. So, anyway, student is a person. Employee is a person. Instructor is an employee. Secretary is an employee. These are artificial. It is cooked up. Employee must have a salary. Student must have credits. They have completed. Instructor must have a rank. Maybe, you know, S10 processor, associate, full processor, so forth. And persons, all persons must have ID, name and address. Now, here also there are some constraints. First of all, which entities can be members of a given lower level entity set. So, you could, this could be user defined. That is, there is no rule to it. When the user creates an entity, the user says this entity is a person and a student or this person is also an instructor. In condition defined, the decision is made based on some condition of the attribute. So, supposing you want to have senior citizens who are a subset of person. Now, which people are senior citizens? Well, there is a rule for it. Anyone above 65 may be senior citizens. So, the condition is that all people above 65 are in the senior citizen entity set, which is a specialization of the person entity. The next constraint is whether or not entities may belong to more than one lower level entity set. What do we mean by this? Going to a previous diagram, can a person be both a student and an employee? Yes, we do in general want to allow that. There are many students who are also working and then there are people who are working who are allowed to do a part time degree and therefore, they also become students. So, a person can be both an employee and a student. On the other hand, can an employee be both an instructor and a secretary? Probably not and we have imposed that constraint in our notation as follows. We have a single arrow and then from that, there is a branch, two way branch, which says that this indicates that an employee cannot be an instructor and a student. This is again not standard UML, but in UML you can actually add a tag here saying disjoint to indicate that the specialization here is disjoint. The converse of disjoint is overlapped meaning an entity can belong to more than one lower level entity set. So, in our case, the person specialization to employee and student, this one, the specialization of person to employee and student is overlapped. So, somebody can be both an instructor and finally, there is a completeness constraint, which says if it is total, that means every person must be, everything at one level must also appear at a lower level. So, for example, if you said that the specialization of person is total, that means any person in a database must be an employee or a student. You cannot have a person who is neither. Does that make sense in a university? Maybe. If you are looking at only instructors and students, yes, everybody should be one of these. On the other hand, IIT, IIT security specifically keeps track of all IIT residents. So, there are some who are not employees or students. They are just there is absolutely no other information. So, there is no use creating a specialization of person and that, so they can be persons without being employee or student. So, there the specialization is partial. Somebody can be a person without being student or employee. The next few slides talk about how to represent specialization via schemas, but maybe what I will do is come back to this after a break where we can discuss the design. So, what I want to do to start with is use some of these concepts which we just saw. What are the concepts? Week entity sets, specialization, generalization. I want to see if we can apply it to a model schema which we have been creating. Let us go back to the white board. So, let us see this particular diagram. In this case, is there any specialization possible? Not really. These are all different kinds of things. We cannot say a submission is an assignment. A submission is for an assignment, but a submission is not an assignment by itself. Submission has the students whatever the student has done. It does not have the original assignment specification. So, there is no scope for creating a specialization or generalization with this set of things. But if we decided to have maybe different kinds of users in Moodle with different rights, different extra attributes about them, then maybe we would break this up and have specializations of users. So, I do not think Moodle has such a notion, but if you wish to, we could create a specialization of user called, I do not know what administrator may be with some extra attributes if you wish. So, that was for specialization. There is no good example here. The other part, weak entity sets actually could make a lot of sense here. The first part is is assignment a strong entity set or is it a weak entity set? That is a decision we have to make. Now, in our diagram here, we are showing assignment as a strong entity set. We have shown assignment as a strong entity set by having an ID which is a key for it, primary key for it. Similarly, submission we have shown as a strong entity set. Now, that we know about weak entity set, we could revisit our decision. Supposing we want assignment as a weak entity set identified by the course for which it is used, then the ID for assignment need not be globally unique. It only has to be unique within the course. So, you can say assignment 1, assignment 2, 3 and so forth. Another course could also have assignment 1, 2, 3, 4. So, then what would we do? If we chose to make it a weak entity set, instead of having underline here, this would become a dash. So, in ID we would replace by ID. I hope the font is visible with a dash underline under it. So, there we are signifying that it is a weak entity and the ID is only unique within assignments for a course. So, now we have to give it an identifying relationship. So, moving to the next diagram, since we chose assignment to be a weak entity set, we have to identify it and this diagram, I am sorry my paper is not properly aligned here. So, my diagram became a bit twisted. So, course assignment now we have a double diamond indicating that it is an identifying relationship for assignment and it is identified by course of here. So, now we have a weak entity set with an identifying entity set course and an identifying relationship course assignment. Now, in fact, this can be nested. We could have done the same for submission. Submission is identified by assignment. We will not give a overall ID to submission. It is only relative to an assignment we want to see which is the identify the submission. So, what we could do is make submission also a weak entity set and identify it by the assignment. So, now we have made submission assignment and identifying relationship for submission which we have chosen to make weak now. In fact, you can do something interesting here and submission can have two identifying relationships. One could be the assignment, the other could be submitted by. So, let us try that. So, now submitted by is a double diamond and submission is identified by submitted by and by submission assignment. Now, what attributes what discriminator would submission need in this case? Do we need any other discriminator within this? The answer is no actually assignment plus who submitted it uniquely identifies the submission unless you want repeat submission actually Moodle allows you. It will allow you to submit multiple time and you can keep track of all the submission in which case you may have a discriminant called version number. So, let us go back to that. Submission has an ID here. So, instead of ID being underlined I will just if I do not even need an ID. So, I could get rid of ID and maybe the timestamp could become the discriminant meaning a submission is now identified by which assignment and which person submitted it, but the discriminant is the timestamp. So, the three things together are sufficient to uniquely identify a particular submission. So, that is what we lined up with. So, here is a new modified diagram for the same Moodle scenario, but now using weak entity sets and identifying relationships. Let us see if there are any other features of the Moodle thing which we missed. We had posts that is the feature which we did not fully model at so far. So, let us try modeling that. What is a post? So, first let us think about what a post is in Moodle. A post is done by a particular person. It has a title and sometimes a post is a new post. Sometimes a post is a reply to an earlier post. So, let us see how to model this. So, we have a post which is an entity. How do we identify a post? Again, let us for simplicity make it a strong entity set by giving it a unique ID. This is fairly standard. So, there is a post. A post is associated with a user. So, we have post by user. A post is also in the context of a course. So, it does not have an existence outside of a course. So, there is also a course and we have posted in this course posted for a particular course. What else do we have? It has to have some text. So, we have text. It has a time when it was posted. So, time and finally, we said that it is a post might be in reply to another post. So, how do we track this? Should we have a field called in reply to? No, that basically loses track of the fact that it must be related to another post not to something completely irrelevant. So, instead of making it an attribute called in reply to, we will have a relationship in reply to is a relationship from post back to post. So, this is like a pre-reg. Both the sides of in reply to are the same relation post and same entity set also. And therefore, one of them should be maybe post id and then reply to. So, the other edge we have given it a tag which in this case is the same as the tag of the relationship, but you could give it any name you want. So, the two edges from in reply to have to be given role name. So, that when we create a relationship out of it, there are meaningful attributes. So, otherwise what would happen? If we did not have role names, when we create a relation for let us say post by, we are going to have a id here. Let us call it p id to distinguish it from other id. So, if we create a relationship, a relational schema for post by, what will be there? For user there is an id. So, the schema for post by would look like p id comma id. So, that I think covers all the things I can think of for post except that we have not put in constraints. Post must be by somebody. Therefore, we have a total participation constraint there and only by one person. Therefore, we have an arrow head. Similarly, a post must be in a core cannot be by itself and only one core. So, there is an arrow head there. So, we have these extra constraints. Now, given these constraints, what is the relational schema you would create? We just saw that if you have many to one relationship, in particular one which is total, you might as well hold that relation schema into the many side. So, what we would end up with is a relation post with attributes p id which is a primary key and then we have an id that is the user id. Then we have a course id and then we have the remaining attributes text and time. Now, what about this in reply 2? Can this be folded in? Well, a particular post must be in reply 2 at most one post. So, this side of in reply 2 does not look very good, but imagine this is an arrow head here. So, the in reply 2 is many to one from the post id side to the in reply 2 side. What does this mean? For a given post, there may be many replies, but a given post is in reply 2 at most one post. So, even though it is a relationship from post to itself, it can still be many to one on one of the side. So, now we have the option of folding this also into the post itself. So, we can have in reply 2 as a field here. So, what is happening here? The in reply 2 field can be null. Why? Because the in reply 2 relation is not total from post. Not all posts are in reply to something else. The first person who creates a post on some topic, that particular post is not in reply to anything. Only the subsequent ones on the same topic are in reply 2. Therefore, for the very first post on a topic, in reply 2 is going to be null. So, that is how we convert this schema to a relational schema. So, I hope that has given you some idea of how we can do these things.