 Hello and welcome to video lecture on entity relationship design issues. At the end of this session, students will be able to specify entity relationship design issues. The notions of an entity set and a relationship set are not precise and it is possible to define a set of entities and the relationships among them in a number of different ways. Specific issues and relative solutions concerning them are use of entity set versus attributes, use of entity set versus relationship sets, binary versus an array relationship sets and placement of relationship attributes. Consider the entity set instructor with the additional attribute phone number as shown in figure. It can easily be argued that a phone is an entity in its own right with attributes, phone number and location. The location may be the office or home where the phone is located. If we take this point of view, we do not add the attribute phone number to the instructor rather we create a phone entity set with attributes, phone number and location. A relationship set instructor phone denoting the association between instructors and the phones that they have. Treating a phone as an attribute phone number implies that instructor have precisely one phone number each. Treating phone as an entity phone permits instructor to have several phone numbers including zero associated with them. However, we could instead easily define phone number as a multi valued attribute to allow multiple phones per instructor. The main difference then is that treating a phone as an entity better models a situation where one may want to keep extra information about a phone such as its location or its type. Thus treating phone as an entity is more general than treating it as an attribute and is appropriate when the generality may be useful. In contrast it would not be appropriate to treat the attribute name of an instructor as an entity. It is difficult to argue that name is an entity in its own right in contrast to the phone. Thus it is appropriate to have name as an attribute of the instructor entity set. Two natural questions thus arise what constitute an attribute and what constitute an entity set. Unfortunately there are no simple answers. The distinction mainly depends on the structure of a real world enterprise being modeled and on the semantics associated with the attribute in questions. It is not always clear whether an object is best expressed by an entity set or a relationship set. In a given figure A we use the takes relationship set to model the situation where a student takes a section of a course. An alternative is to imagine that there is a course registration record for each course that each student takes. Then we have an entity set to represent the course registration record. Let us call that entity set registration. Each registration entity is related to exactly one student and to exactly one section. So we have two relationship sets one to relate course registration records to students and one to relate course registration records to sections. In the figure B we show the entity sets section and student from figure A with the takes relationship set replaced by one entity set and two relationship sets. Section the entity set representing course registration records. Section registration the relationship set relating registration and section. Student registration the relationship set relating registration and student. Note that we use double lines to indicate total participation by registration entities. Both the approach figure A and figure B accurately represent the university's information. But the use of takes is more compact and probably preferable. However, if the registrar's office associated with other information with a course registration record it might be best to make it an entity in its own right. One possible guideline is in determining whether to use an entity set or a relationship set is to designate a relationship set to describe an action that occurs between entities. This approach can also be useful in deciding whether certain attributes may be more appropriately expressed as relationships. Relationships in databases are often binary. Some relationships that appear to be non-binary could actually be better represented by several binary relationships. For instance, one could create a ternary relationship parent relating a child to his or her mother and father. However, such a relationship could also be represented by two binary relationships mother and father relating a child to his or her mother and father separately. Using the two relationships mother and father provides us a record of a child's mother even if we are not aware of the father's identity. An all value would be required if the ternary relationship parent is used. Relating binary relationship set is preferable in this case. In fact, it is always possible to replace a non-binary relationship set by a number of distinct binary relationship sets. For simplicity, consider the abstract ternary relationship set R that is n equal to 3 relating entity sets A, B and C. We replace the relationship set R by an entity set E and create three relationship sets as shown in figure R A relating E and A, R B relating E and B, R C relating E and C. If the relationship set R add any attributes, these are assigned to entity set E. Consider the relationship set project guide relating instructor, student and project. We cannot directly split project guide into binary relationships between instructor and project and between instructor and student. The relationship set project guide can be split into binary relationships by treating a new entity set however, doing so would not be very natural. The cardinality ratio of a relationship can affect the placement of relationship attributes. Thus, attributes of one-to-many or many-to-one relationship sets can be repositioned to only the entity set on the many side of the relationship. For instance, let us specify that advisor is a one-to-many relationship set such that one instructor may advise several students but each student can be advised by only a single instructor. In this case, the attribute date which specifies when the instructor become the advisor of a student could be associated with the student entity set. Since each student entity participates in a relationship with at most one instance of instructor, making this attribute designation as the same meaning as would placing date with the advisor relationship set. Attributes of a one-to-many relationship set can be repositioned to only the entity set on the many side of the relationship. Here we can see attribute date of relationship sets is repositioned to instructor entity. For one-to-one relationship sets, on the other hand, the relationship attribute can be associated with either one of the participating entities. Hence, in this figure, date is repositioned with either instructor or student entity. The choice of attribute placement is more clear-cut for many-to-many relationship sets. According to our example, let us specify the perhaps more realistic case that advisor is a many-to-many relationship set expressing that an instructor may advise one or more students and that a student may be advised by one or more instructors. If we are to express the date on which a specific instructor become the advisor of a specific student, date must be an attribute of the advisor relationship set rather than either one of the participating entities. If date were an attribute of student, for instance, we could not determine which instructor become the advisor on that particular date. When an attribute is determined by the combination of participating entity sets rather than by either entity separately, that attribute must be associated with the many-to-many relationship set. Now, pause the video and answer the following question. For the following entity relationship diagram, specify the attribute placement of relationship one-to-one, one-to-many, many-to-one, many-to-many. The answer is, attributes of one-to-one relationship sets can be associated with one of the participating entity sets rather than with the relationship set. That is, as shown in figure A, access date can be repositioned with either customer or account entity. Attributes of many-to-one or one-to-many relationship sets can be associated with many-side. That is, as shown in figure B, for many-to-one, relationship sets access date can be repositioned to customer entity, and as shown in figure C, for one-to-many relationship sets, access date can be repositioned to account entity. For many-to-many relationship set, attributes must be associated with relationship sets rather than one of the participating entity. That is, as shown in figure D, access date must be associated with depositor relationship. Thank you.