Sometimes it may be unclear as to whether something is an attribute or a relationship. Both attributes and relationships express something about an entity. An entity's attributes express qualities in terms of properties or characteristics. Relationships express associations with other entities.
Suppose we are constructing a library database. Suppose further that we create another primary entity BOOK that has an attribute, borrower. In some cases, an attribute construct is likely to be inappropriate for expressing an optional association that really ought to be a relationship between two entities. As a side issue, borrower would require the use of a null value for those BOOK entities that were not loaned out. In reality, only a very small fraction of a library's books are on loan at any given time. Thus, the "borrower" attribute would be null for most of the BOOK entities. This recurrence of many nulls might indicate that the attribute borrower_name could be an entity. If a BORROWER entity were created, and the association between the entities BOOK and BORROWER was explicitly stated as a relationship, the database designer would likely be closer to putting
attributes and entities in their correct places. It is important to understand the distinction between the types of information that can be expressed as attributes and those that should be treated as relationships and entities.
Checkpoint 3.2
1. Are relationships between two entities permanent, or can the nature of this relationship change over time?
2. Are attributes of an entity permanent?
3. Does there always exist a relationship between two entities?
4. What is a binary relationship?
ER Design Methodology
Step 1: Select one, primary entity from the database requirements description and show attributes to be recorded for that entity. Label keys if appropriate and show some sample data.
Step 2: Use structured English for entities, attributes, and keys to describe the database that has been elicited.
Step 3: Examine attributes in the primary entity (possibly with user assistance) to find out if information about one of the attributes is to be recorded.
Step 3a: If information about an attribute is needed, make the attribute an entity, and then
Step 3b: Define the relationship back to the original entity.
Step 4: If another entity is appropriate, draw the second entity with its attributes. Repeat step 2 to see if this entity should be further split into more entities.
Step 5: Connect entities with relationships if relationships exist.
Chapter Summary
Entities, attributes, and relationships were defined in Chapter 2. However, in real life, while trying to design databases, it is often difficult to determine whether something should be an attribute, entity, or a relationship. This chapter discussed ways (techniques) to determine whether something should be an entity, attribute, or a relationship.
This chapter also introduced the concept of binary relationships. Real-life databases will have more than one entity, so this chapter developed the ER diagram from a one-entity diagram to a two-entity diagram, and showed how to determine and depict binary relationships between the two entities using the Chen-like model. Because the concept of relationships was only introduced, and structural constraints of relationships have not yet been discussed, we have not included mapping rules in this chapter.
Chapter 3 Exercises
Exercise 3.1
Draw an ER diagram (using the Chen-like model) for an entity called HOTEL and include no fewer than five attributes for the entity. Of the five attributes, include at least one composite attribute and one multi-valued attribute.
Exercise 3.2
Let us suppose that we reconsider our STUDENT example and the only attributes of STUDENT are student number and name. Let us suppose that we have another entity called HIGH SCHOOL, which is going to be the high school from which the student graduated. For the HIGH SCHOOL entity, we will record the high school name and the location (meaning, city and state). Draw the ER diagram using the concise form (as Figure 2.1, bottom). What would you name the relationship here? Write out the grammar for the relationship between the two entities.
Exercise 3.3
Suppose that a college had one dormitory with many rooms. The DORMITORY entity, which is actually a "dormitory room" entity because there is only one dorm, has the attributes room number and single/double (meaning that there are private rooms and double rooms). Let us suppose that the STUDENT entity in this case contains the attributes student number, student name, and home telephone number. Draw the ER diagram in the Chen-like model linking up the two entities. Name your relationships. Write out the grammar for the relationship between the two entities.
Exercise 3.4
We have two entities, a PLANE and a PILOT, and describe the relationship between the two entities as "A PILOT flies a PLANE." What should the relationship read from the other entity's side?
Exercise 3.5
Complete the methodology by adding sample data to Figures 3.3, 3.5, as well as to Exercises 1, 2, 3, and 4.
References
Atzeni, P., Ceri, S., Paraboschi, S., and Torlone, R., Database Systems, McGraw-Hill, New York, 1999.
Elmasri, R. and Navathe, S.B., Fundamentals of Database Systems, 3rd ed., Addison-Wesley, Reading, MA, 2000.
Lochovsky, F.H., Ed., Entity-Relationship Approach to Database Design and Querying, Elsevier Science, New York, 1990.