 Hello, welcome all. In this lecture, we discuss the entity relationship design issues, myself Mr. S. N. Chamath Gowder from Walchand Institute of Technology, Solapur. At the end of this session, students will be able to specify entity relationship design issues. Generally, the idea of the entity set and the relationship set are not very much clear and in most of the cases, it is also possible to define an entities and the relationships between those entities in a different ways, which leads to the design issue. In this lecture, we discuss the basic issues in the design process of the ER schema. The basic issues are as listed below. First, use of entity set versus attributes. Use of entity sets versus relationship sets. Binary versus NRA relationship sets. And last, placement of relationship attributes. Now, we discuss these issues one by one. First, we start with the use of entity set and attributes. Generally, while designing an ER database schema, database engineer face a situation where a designer cannot able to decide with a particular thing that this particular thing must be considered as an entity or this particular thing must be considered as an attribute. In such situations, two questions will arise. What makes that particular thing as an attribute and what makes that particular thing as an entity? Such a confusion will lead to the design issue and such confusions is to be removed. The selection of a particular thing as an entity or as an attribute mainly depends on the structure of the real world enterprise being modeled and meaning of the word associated with the attribute in question. Consider an example here. Consider an instructor entity set as shown in the figure A with the following attributes ID, name, phone number and salary. Here, defining a phone number as an attribute indicates that each instructor is having only one phone number in other words, each instructor is using only one phone number. But nowadays, we can see that instructors are using more than one phone number. In such a case, we can define phone number as a multi valued attribute. If each instructors are using more than one phone number, this is one kind of a situation and treating phone number as an attribute is more appropriate as shown in the figure A. Now, what if a university wants to add more information about a phone number like a location of a phone number or a type of a phone like mobile phone or IP phone or land phone and also information like whether it is a general purpose phone or it is a personal phone. In such situation, treating phone as an entity is more appropriate than treating it as an attribute of some entity. Here also it is not clear whether an object is best expressed as an entity set or a relationship set. To understand this issue, let us consider a bank example where a customer has a loan of particular amount in a branch. Now, consider a situation where every loan is held by exactly one customer and is associated with exactly one branch. Then the design shown in figure A will satisfy this situation. In this design, a loan is expressed as a relationship between a customer and a branch with the descriptive attribute loan number and amount. Now, consider another situation where a several customer hold a loan jointly. In such situation, if we use the design shown in figure A, then we must replicate the values for the descriptive attributes loan number and the amount in each such relationship where the loan is held by several customers jointly. This will lead to the wastage of the storage because the same data must be stored multiple times and also this will leave the data in inconsistent state because there is a high chance where the values may differ in two relationships for attributes that are supposed to have the same value. These two problems can be ruled out by the design shown in the figure B because in this design the loan itself is an entity. Next binary versus an array relationship. It is always possible to replace a non-binary relationship set by a number of distinct binary relationship sets. Consider an example where a child is related to a mother and a father through a relationship parent has shown in figure A and this appears to be a good representation at first. But if we represent this relationship with the two binary relationships as shown in figure B, this will provide us a record of a child's mother even if we do not have a father's identity. If we use the ternary relationship as shown in figure A, then we have to store a null value if the father's identity is unknown and using a null value is not preferable. In such case, using binary relationship sets instead of ternary relationship sets is more preferable. But still there are some relationship sets that are naturally non-binary like an example project guide relationship set has shown in the figure which is relating instructor, student and project. Here we cannot split this relationship into binary relationship sets between instructor and project and between instructor and student just like we did with the parent relationship earlier. Even if we split this into binary relationship sets between instructor and project and between instructor and student, then we would be able to record the data that instructor A is working on project A and project B with student A and student B. But we would not be able to record that instructor A is working on project A with student A, but not with student B. Similarly, instructor A is working on project B with student B, but not with student A. Next, we discuss placement of relationship attributes. We know that every entity will have some attributes as shown in the figure. Here, instructor is having the attributes ID, name and salary. Similarly, student is having the attributes ID, name and total credits. In this way, even the relationships can have attributes. In this figure relationship advisor has an attribute date. I have considered date as an attribute for advisor relationship because I want to record the date when the particular instructor has become an advisor of the particular student. Now, as a database schema designer, I need to think is it necessary to add an attribute to the relationship set because adding more and more attributes to the relationship set makes the things more complicated. So, I want to keep minimum number of attributes to the relationship set as much as possible. Hence, we try to avoid adding attributes to the relationship sets whenever and wherever it is possible. As a designer, I need to think whether this date attribute must be added to the relationship set or is it possible to add this attribute to any of the entities participating in the relationship. Now, I want to use this date attribute, but instead of using this attribute in the relationship advisor, I want to use it either in the instructor entity or in the student entity. Now, the question is which side will give more meaning. If one too many relationship is there as shown in this figure where instructor is on one side and student is on many side. In such case, it is convenient to use date attribute on the many side that attach the date to the student entity because in one too many relationship as in this example, many students are being advised by the same instructor and adding date attribute with every student entity is beneficial because we can attach this date with every student entity without problem. But if I attach the date on one side that is with the instructor entity, then I have to define this date attribute as multi-valued attribute because for every student I have to add date when they are being advised by this instructor and it becomes very confusing to identify on which date this instructor become an advisor for which student. Similarly, in one to one relationship sets the relationship attribute date can be repositioned with either instructor entity or the student entity because in this example, one instructor will be advising only one student and one student will be advised by one instructor only. So, we can easily place relationship attribute date with any of the entities without any confusion. Let us take one more case where the relationship set is many to many. This example indicates that an instructor can advise one or more students. Similarly, a student may be advised by one or more instructor. In such a case, it is very difficult to move the relationship attribute date to any of the entity. So, it is recommended that it must be associated with the advisor relationship only. For instance, if we try to move date to the student's entity, then it becomes difficult to identify which instructor become an advisor on that particular date. Similarly, if we try to move date to the instructor entity, then it becomes difficult to identify which student was advised on that particular date. Therefore, when an attribute is determined by the combination of participating entity sets hash, it is advisable that attribute must be associated with the relationship set only. But still as a designer, you have a freedom to use this attribute wherever you want. Now pause the video for some time and try this example. For the following year diagram, specify the attribute placement of relationship one to many, one to one and many to many. This is the solution. Figure A shows the one to one relationship. In such case, access date can be positioned with any of the entity, customer or account. Figure B and C shows one to many relationships in such case, access date can be positioned with the many side. In figure B, it is the customer entity and in figure C, it is account entity. Figure D shows many to many relationship. In this case, access date is kept with the depositor relationship only. Thank you.