So basically in this lecture I want focus on how the information that we have represented in theE/R model you know. Information about an enterprise database that we are trying to design we will capture that in E/Rmodel which is a conceptual level model into the relational model. We have just studied thisrelational data model and we know that you know it gives certain concepts like relations, keys,foreign keys and things like that. So now we focus on how to translate or map the informationthat we represented in the E/R model to the relational model.So they give in some sense are different representations of the real world enterprise and now wewere trying to get the relational scheme. Now, so the general approach that we will do we willuse is that for both entities entity types right entity types as well as the relation types, we willbasically map them into relations ok. So notice that we have in the E/R model context we havethese 2 concepts, the concept of an entity type, the concept of a relationship type.And of course there are variations of this entity type like strong, weak and all that ok, but when itcomes to relational data model we have only one the concept of a relation. So basically for eachentity set as well as the relationship set, relationship set or type right entity type or relationshiptype we interchangeably use this words. So for each entity set and relationship set in the E/Rmodel we will have a corresponding relational table.So let me use this word table in this set of slides to kind of make it very clear that we are talkingabout relations. Now the relation table with the same name as that relationship as the entity typeor the relationship set. So that is the basic approach and then each of these tables will havemultiple columns, so kind of we are calling attributes as columns here, whose names areobtained from the attributes of the entity types or the relationship types. So we will see thedetails as to how exactly we should obtain these names ok. So let us first focus on strong entity sets ok, so let us say there is a strong entity set E i. So whatwe will do is to create a table T i for that particular entity tab set the name of the table will be thesame as in the name of the entity. But in this case we just representing it as you know symbolslike E i, T i. Now the entity set will have attributes right now it will have different kinds ofattributes.So we will take the simple attributes and then if there are composite attributes what we dobasically here is to take the simple components of those composite attributes ok, each of thesecomposite attribute has components right. So and that component itself can be again compositeright and so we kind of go to the leaf level in some sense and pick up all the simple attributessimple components of all these composite attributes and those names alone will be the attributenames for the at table T i ok.One thing that we have to do is to handle this multi-valued attributes of these entities separatelyand I will talk about how to handle multi-valued right now we will ignore the multi-valuedattributes of the entity set E i ok. We will take this simple attributes and composite attributes ok,for composite attributes what we will do is to pick up their simple components, components andthen check whether they are simple or not and then pick up that ok.So basically you might also realize here that we are in some sense losing some information likefor example, while modeling a person we might say that the name has 2 components, the lastname and the first name ok and these 2 things together are called name that is how the modelhand as composite attribute. So we will now basically ignore that name thing and then only takethe L name F name if that is the name those are the attributes the names that are being used.Then we will simply pick up L name and N name as the attributes of the table we are creatingand a little later we will see how exactly to handle multi-valued attributes of these entities. Nowwe also now decide the primary key for this table T i, so the primary key will be basicallydecided by picking up the primary key of the entity T i. So whatever was the primary keyattribute of the entity and that would be in at attribute of the table we have created and so we willmake that as the key here.Now as we will see later basically the primary key can would be actually referred to by othertables via the foreign keys you know in order to capture relationships ok. We will see that sayhere relationship sets also are going to be translated into tables. And so we will see how exactlywe will use foreign case in order to capture that associations ok.So move on, let us look at weak entity sets, entity sets and weak entity sets, recall that weakentity sets are basically entities that do not have an independent existence and they kind ofdepend on some other entity for their relevance to the information system. So we have weakentities, so let us say some E prime is a weak entity owned by some strong entity E it could evenbe owned by a another weak entity but we will take up that case a little later.So let us say it is owned by a strong entity, so this E prime is going to be converted into a tablesay R prime, where you know we will pick up we will have to make up their attributes for this Eprime the table. So these attributes of R prime the table will be basically the attributes of theweak entity set E prime and in addition the primary key attributes of the identifying strong entityare the owner entity there is a owner right, for because E prime is a weak entity it has a owner.So pick up the primary key attributes of the owner and include that as attributes of R prime ok.Now you can see that the attribute of E prime you know will have only what is called a partialkey ok. And so in order to distinguish between the various entities in the weak entity set, weneed the information about the owner entity. So the owner entity's information will comebecause we are including this primary key attributes of that ok.Now let us look at this case we suppose the owner is itself is a weak entity in which case thepartial key of that entity plus the primary key of the owner of that would be the attributes that wewill include. Of course this chain will go on because it has to end with some strong entity ok. Sobasically you can see that in case a weak entity is owned by another weak entity. Then theapproach we can follow is basically first map the owner range, owner weak entity into a table ok.And then bother about mapping the weak entity ok, so that is how we will need to handle theweak entity type. Now these attributes these means these are the primary key attributes of theidentifying relationship ok and the so will in fact also be a foreign key in R prime referring to thetable corresponding to the owner entity. The owner entity we has been mapped to a table.And so now these primary key attributes of that particular that we have included into the table ofR prime will be the foreign key in R prime referring to the table corresponding to E. We will seesome examples and then it will become clear and what should be the key of R prime, is thepartial key of E prime plus the key of the owner entity. The key of owner entity has anywaybeing included into this table.And so that set of attributes plus the partial key together would be the key for R prime the tableR prime and then multi-valued attributes have to be dealt separately as we will see a little laterok. And now let us look at an example to clarify this situation, we had this kind of a example in ourearlier discussion. So section is a weak entity owned by course ok and the identifyingrelationship is this has section and partial key is section number ok. So let us assume that thecourse has been already mapped to a table like this course Id, name, credits ok. So it is a strongentity and so we will map it directly into a table with these attributes and then the whatever is thekey for the entity will become the key attribute for the tables also.Now this is a weak entity and it is owned by a strong entity and so all these attributes will comebut then the primary key of the owner entity also is added as an attribute and in case that ownerentity is a itself is a weak entity as I was pointing it out in the previous slide. Then we will firstmap that into a table in which case you know it will get the partial key of that plus the you knowprimary key of it is own identity as the key.So those 2 things together will now come to the table here, so like that we will be able to capturethe information. So section number course Id now together will be the key, so course Id becauseit is the primary key of the owner, section number it is because it is the partial key of the weakentity. So these 2 things together will be the key, so we underlined them and now the course Idthat we have introduced here ok, borrowed from the owner entity will or obviously should be aforeign key referring to the courses.In general the primary key attributes that we introduced from the owner will be the foreign keyattributes that refer to the owner relation, whatever is the owner relation ok. So I hope this so letus go back to this slide, so attributes of R primary or attributes of the weak entity plus theprimary key attributes of the identifying strong entity. And in case that is a weak entity thepartial key of E plus the primary key of the owner of E will be the attributes.And these attributes will be a foreign key also in R prime referring to the table bar and so on. Sothis should be clear now, any questions in that. Now let us talk about multi-valued attributes. Multi-valued attributes actually give rise to aspecial kind of a situation recall that in the relational data model. In the relational data model Ihave been emphasizing that a cell of a table in a particular rho contains only atomic values. Itcannot contain set of values or lists of values or anything else it contains only one value, so thesemulti-valued attributes is you know by it is definition is a bunch of values right.So we cannot directly store it as a value in a cell of our any relational table because of theconstraints that the relational model has ok and so what happens here is that we need to kind ofintroduce a separate table for each of the multi -valued attributes that the entity has. So let ustake this just one example here, so Email Id, so we will do this after the entities have beenmapped into tables.So right now student has already been mapped and it does not have the multi-valued attribute, soit is we thought. So that is why we did not mention about the multi-valued attribute when we aremapping, we said they will be dealt with separately. So now take up this multi valued-attributecreate a separate relation for that whatever be the so you will not invent the name for the relation.And then introduce this attribute name and then the primary key of the entity for which this is amulti-valued attribute and then make them together as the key. So you can see that why is thisneeded you can see that since roll number which kind of identifies the student you know hasassociation with multiple Email Ids we will have to repeat the roll number with all of thoseEmail Ids that is the only way to capture a set of entity, a set of values who associated with aparticular entity.Because this relational model has a constraint that this particular column can only have exactlyone value it cannot have list or set of values right. So roll number Email Id will obviously be thekey together now unless both of them are kind of given you cannot identify the rho in a you atuple in this relation ok. So it is also obvious that this roll number is referring to the studententity and so it will be a foreign key referring to the corresponding entity I mean the tablecorresponding to that entity ok.So 1 column for this attribute what is that attribute is this multi valued attribute. So 1 column forthat and then the columns for the primary key attributes, so in general this primary key therecould be multiple attributes in the primary key. So 1 column for each of those multi printcorresponding to the either the entity type or the relationship type for which we are we have thisas the multi-valued attribute.Recall that even relationships can have attributes and they can also be multi-valued in the case ofE/R model and so when we deal with multi-valued attributes we would already map thatrelationship type into a table ok and so we take the key attributes of that table and then includethem here ok. So that is how we have to deal with multi-valued attributes ok, so moving on let ussee how we can so far we have focused on entities, let us see how to handle relationships in theE/R model. Let us first take up this binary 1 to 1 kind of relationship, so 1 to 1 relationship as you can recallis involves it is binary. So there are 2 entities, so S and T let them be the entities that are inrelationship R and let us say we have already mapped S into S prime, T into T Prime right. So wedid the mapping of the entities and so we have got 2 tables and now we have to handle thisrelationship.One approach to handle this relationship is the you know a kind of a simple you know interestingapproach, where we kind of introduce what is called a what can be probably called as a lookuptable ok. So which will basically just have 2 attributes when whatever is the number of attributesbasically contain the key of S key of S prime and key of T prime together that is all that is oneapproach, we will see that actually I will come back to that little later.But let us see how we can handle the specific case of 1 to 1 relationships, let us say choose anentity which has a total participation in R. One of them is likely to have you know a totalparticipation, let us say if there is 1, let us say S has a total participation. Then in this case whatwe can actually do is to include the primary key of T prime, the other relation as a foreign key inS prime referring to the relation or the table T prime.And in case this relationship type itself has some attributes then map all those simple attributesor simple components of this composite attributes as attributes of S prime ok, so this is 1approach, I will show you an example and then it will become clear as to what we are exactlydoing. So we give reference to the one that has total participation and we basically change therelation scheme of that particular a thing by including the primary key of the other as a foreignkey here ok.In this case we can in some sense do the other way around also but I will show you with anexample ok. So let us look at this student entity , hostel room entity ok and student is identified by rollnumbers and hostel room for a moment let us assume that hostel rooms are identified by roomnumber, room number is the key for hostels let us say ok will make a specific assumption here.We want basically 2 entities with their keys, so we have taken student and hostel room and let ussay it is a 1 to 1 relationship that means every student is given a hostel room ok and this is ahostel room can only have 1 person if at all.So this is a total participation that means every student is given a room and this is partial thatmeans some hostel rooms could be unallocated it is a luxury of course and to have a hostel roomsnot allocated ok. So let us say the so S is the student R is that relationship T is this one. So whatbasically we have done here is that the student would have let us say student is actually mappedto student table in which case we have roll number, name and home address as the 3 attributesonly up to that will be the student entity.And the hostel room will be room number, hostel name, address because these are the 3attributes room number is the key, so we will write it here. Now in order to take care of this,what we basically have done is to augment this side which has a total participation by basicallyadding 1 foreign key to take care of this relationship one key sorry one attribute that is actually aforeign key referring to the other relation.So basically we want to capture as to what is the room to which this particular student isassociated with. So we augment the student and then introduced a new attribute room Id and thensince it is value ok this values are going to be used to refer to the hostel rooms we make it as aforeign key not refers to the hostel table ok. Now you might say that why do not we do the otherway round, why do not we include the roll number in the hostel table and saying that thisparticular hostel room is assigned to so and so student.In this case we could even do that because it is a 1 to 1 relationship, so there is exactly 1 studentto which it is been assigned and so you could actually augment the hostel table right hostel Imean the room this table with a roll number as with roll number or student Id or whatever youcan call that attribute. And then make that as a foreign key that refers to the student just like wedid here we can do it in the other way not also.But only issue would be that since we have assumed that this is not say partial participation. Sothere is lots of there might be several rooms which are not allocated in which case they will thevalue of roll number or a student Id here would be null in case it is not allocated it would be null.If it is allocated it will be the student Id of the corresponding thing, so there will be lot of nullvalues if we choose the other relation which has partial participation, you would also go toanother extreme and then ok.So this foreign key name in general the foreign key name of course need not be the same as aprimary key name. But it could be some other name right, for example I said here room Id whichis basically refers to room number. Now we could go also to a little bit of a extreme approach inwhich we can merge these 2 relations together and then put them you know as one big relationwe could do that in this case.Because it is a one to one you know there are exactly as many if you know it is one to one andthen let us say both of them. So both entities participate fully say this is an important thing ifboth entities participate fully that means every hostel room is also you know participates heresaying that it is assigned to some student no room is ok. If that is the case, so if both entitiesparticipate fully then we can basically merge these 2 relations.And then get one abroad relation a merge relation which is a little I do not advice that mainlybecause these entities have you know their own identity in some sense. So we would like tocapture them as separate relations and then the association or the relationship is best capturedusing foreign keys rather you know in some sense merging them because they would not thenlogically make sense.So as far as possible keep a relation to ensure that a relation is you know obtained from oneentity. An entity is represented as one relation ok, so that is the approach that we can do , theother approach that I was referring to like lookup table kind of approach is that let this particularstudent relation be separate roll number, name, home address, let this be separate room number,hostel name address.We will create a third table in which we just you know capture the association only what is theroll number, what is the room Id associated room Id that we can capture separately as I look uptable in some sense right. And then of course in that relation you will have to make the room Idas a foreign key that refers to roll number and the student Id is a foreign key that refers to rollnumber and room Id as a foreign key that refers to the first one.You can have a third relation leaving these 2 things as they are but we do not normally advicethat in the case of these 1 to 1 relationships because you can manage the situation by augmentingone of these relation tables ok. So now let us move on to how to handle a one to many relationship right, in this case what we dolet us say S is a participating entity on the N side one to-many right. So there is a one side and Nside and T is the other entity let us say and let us say we have already mapped S to S prime T toT prime these are the corresponding tables now what the approach here is in order to handle this1 to many relationship is to kind of modify the N side table.The table corresponding to the N side entity and include the primary key of the other as theforeign key here and then include any simple attributes or simple components of the compositeattributes of that relationship itself as attributes of S prime. So basically we augment the schemefor S prime which is the table corresponding to the N side entity. So again this will become clearif we show an example. Let us say professor, guides, many students say each professor guides, many students, but eachstudent is guided by exactly 1 professor. Each student is guided by exactly 1 professor but aprofessor guides many students, let us say that is the one to many relationship and so what weare now saying is that take the N side entity that entity had the N entity has come this side, the Nentities here ok.The N side entity is student, so it has roll number and name I just simplified the things you meanit could have several other attributes but for the illustration purpose let us just have roll numberand name. Now this is the N side entity which is so augment this, augment this by introducing anew attribute and make that as a foreign key that refers to the other relation, what is the otherrelation, the other relation is the 1 side entity which is professor.So it has name, phone, professor Id, so name, phone, prof Id, now basically what we have done isto for each student in each student rho we now introducing a new attribute called the guide of thestudent which will be an employee Id that refers to the professor who is the guide of the student.So notice that we cannot do the other way around here because a professor guides many students.And so if you want to now augment this professor side table with a new attribute saying that youknow whatever students guided then that will be multiple values and you cannot put multiplevalues into a cell and that causes trouble. So we actually solve the problem by augmenting of theN side of the N side entity, I mean the table corresponding to the N side entity by appropriatelyintroducing additional attributes ok.So let us just reread this again if you want, so let S be the participating entity on the N side and Tthe other side so include the primary key of T prime as a foreign key in S prime that is what weare saying. And in case there are attributes for the relationship include them also as additionalattributes in S prime. So in case there is an attribute called begin date or something like that, youbasically make that as an attribute on the student side itself ok. So that is about the 2 specializedrelationships 1 to 1 and one to many. Let us now look at the many to many relationship, here there is actually no option to eitheraugment the 1 side or the other side. So now we basically have to go for a third relation ok, somake a separate table T for this relationship R ok between entities even E 1 and E2 ok let us sayeven in it were already mapped to R 1 and R 2, E 1 is mapped to R 1, E 2 is mapped to R 2, so inorder to handle this many to many relationship.Now introduce the third table where you basically include the primary key attributes of R 1 andthe primary key attributes of R 2 as foreign keys ok. Because this is what basically captures theassociation ok, here is 1 entity, here is another entity. These 2 entities are associated with eachother. So let us take that combination and then give a provision for expressing thosecombinations in some sense ok.Now and of course once we do that these FK 1 will be the obviously the foreign key that refers tothe primary key of R1 and FK 2 will be the foreign key that refers to the primary key of K2 andthese 2 things together will be the key for the new table in some sense they lookup table ok. Again an example will clarify the situation, student enrolls for courses, so course is an entityidentified by the course Id. Student is an another entity roll number is the key and now this is amany to many relationship, student enrolls for many courses and each course is unrolled bymany student right, that is why it is many to many relationship and so you cannot ok basically letus say student is mapped to roll number, name, course is mapped to a course Id name ok.Now in order to handle this we have to necessarily go for a third relation which basicallycaptures what roll numbers are associated, what course Ids and these things together for everycourse I say for a, if you from a student side if you see if he is doing 5 courses then that rollnumber will get associated with those 5 courses and will get listed in the as tuples here and ifcourse Id one particular course is being done by 40 people.Then all those 40 people along with that course Id will be there as tuples here ok. So youbasically have to realize that we cannot augment the student table with a course Id because thereare multiple courses that student is doing and we cannot augment the course with a student Idbecause there are multiple students doing the same course. So we introduced a new relationwhich is in some sense called the.You know in some sense a cross reference kind of relation or it can also be called as a lookuptable kind of relation which basically allows us to look capture the association and then in orderto get more information about this we need to refer to the table. So we make this as a foreignkey, in order to get more details about the course we have to refer to the course table and so wemake this as a foreign key ok.So this is how we capture many to many relationship, so you might, so since the otherrelationships like 1 to 1 and 1 to many or you know in some sense a special cases of many tomany. We could take this approach in order to handle the other cases also but then know forthose cases as we have argued earlier there are better solutions, where now we can avoid havinga third relation instead we can simply go for a better solution where we either augment the oneside or the another side ok. Now as far as finally we have to handle these so called recursive relationships, where if a courseI mean a particular entity is participating more than once in a relationship in appropriate kind ofrolls. So but then this is if you think about it is not a major issue because we basically can treat itas 2 different entities and then basically create foreign keys to refer to the same table more thanonce ok.So let us look at this, so is prerequisite of is a recursive relationship involving course in 2 rolesonce as a course, once as a precursor. So course itself has course Id credits and some I do notknow timing should not be here actually the department offering department take it as offeringdepartment course Id, credits, offering department let us say . So now in order to handle this arespect to whether it is many to many or otherwise we basically create a new table.And then create attributes corresponding to the role names, prerequisite and course and thenmake both of them as foreign keys that refer to the entity ok. So this is how the all the majorconstructs in the relational model can be mapped and the information can be mapped into therelation all the major elements of the E/R model can be handled and then we can capture theinformation into the relational model.