Mapping Rules for Recursive Relationships
PERSONNEL
name.first name.last name.mi employee_id super_id
Richard Earp W 8945 9090
Boris Yelsen 9090 null
Helga Hogan H 3841 9090 Sudip Bagui K 8767 9090 Tina Tanner 5050 8945 PERSONNEL_SUPERVISOR employee_id super_id 8945 9090 9090 null 3841 9090 8767 9090 5050 8945
Chapter Summary
This chapter viewed the different aspects of binary relationships in ER diagrams and refined several of the steps in the ER design methodology. The refining of the ER design methodology means a continuous assessment and reassessment of the ER diagram that is drawn after discussion with the users. The idea that relationships could have attributes, how attributes evolve into entities, recursive relationships, and derived and redundant relationships were discussed with examples and diagrams. The ER design methodology steps were refined to include all of the above into the new and evolving methodology.
Toward the end of the chapter, an alternative ER notation for specifying structural constraints on relationships was presented. Upon completing this chapter, the reader or database creator should be able to efficiently design a database with binary relationships. Chapter 7 deals with ternary and other higher-order relationships.
Chapter 6 Exercises
In each of the exercises that follow, the admonition to "construct an ER diagram" implies not only the diagram, but also the structured grammatical description of the diagram.
Exercise 6.1
Define and state in precise terms the cardinality and participation in Figure 6.5, the STUDENT/COURSE/INSTRUCTOR/BUILDING database. Discuss the structural constraints of Figure 6.5. Under what circumstances would the structural constraints depicted be correct or incorrect?
Exercise 6.2
Consider the following data and construct an ER diagram — use structured grammar to rationalize your constraints. The data: horse name, race, owner, odds at post, post position, date of race, order of finish, year to date
earnings, owner name and address.
Exercise 6.3
In this chapter we described a database that had two entities: COURSE and INSTRUCTOR (refer to Figure 6.10). Book was left as an attribute of
COURSE. Extend the database to include BOOK as an entity. Attributes of BOOK might include: book title, author, price, edition, publisher.
Exercise 6.4
Refer to Figure 6.7. Change Figure 6.7 to include the following information: One building can have a maximum of 99 students living in it. A student has to enroll in at least one class, and can enroll in a maximum of five classes. A class has to enroll at least five students, and can enroll a maximum of 35 students. A instructor may or may not teach a class, and can teach up to three classes. A course has to have one instructor teaching it, and only one instructor can teach a particular course. An instructor may or may not have an office, and can have up to two offices. A building may or may not have an office, and can have up to 15 offices. A course has to be offered in one classroom, and can only be offered in one classroom.
References
Bracchi, G., Paolini, P., and Pelagatti, G., "Binary Logical Associations in Data Modelling,"Modelling in Data Base Management Systems, G.M. Nijssen, Ed., North-Holland, Amsterdam, 1976.
Earp, R. and Bagui, S., "Binary Relationships in Entity Relationships in Entity Relationship (ER) Diagrams,"Data Base Management, Auerbach Publications, Boca Raton, FL, 22-10-43, 1–17, April 2000.
Hawryszkiewycz, I., Database Analysis and Design, SRA, Chicago, 1984.
Mark, L., "Defining Views in the Binary Relationship Model,"Information System, 12, 3 (1987), p. 281–294.
Martin, J., Managing the Data-Base Environment, Prentice Hall, Englewood Cliffs, NJ, 1983.
Sanders, L., Data Modeling, Boyd & Fraser Publishing, Danvers, MA, 1995.
Teorey, T.J., Database Modeling and Design, Morgan Kaufman, San Francisco, 1999.
Case Study: West Florida Mall (continued)
Thus far in our case study, we have developed the major entities and relationships and mapped these to a relational database (with some sample data). Then, upon reviewing step 7, which says:
Step 7: Present the "as-designed" database to the user, complete with the English for entities, attributes, keys, and relationships. Refine the diagram as necessary.
Suppose we got some additional input from the user:
An employee can also be a department manager, and a department manager can manage at most one department. We have to store information on the department manager — the name, social security number, which store he/she is working for, which department he/she is working for. A department manager supervises at least one employee, and may manage several employees.
Upon reviewing these additional specifications, we can see that we have a recursive relationship developing here, because an employee can be a department manager supervising other employees.
So, using mapping rule M5a, we will reinclude the primary key of the EMPLOYEE entity in itself, giving us the following EMPLOYEE relation:
This recursive relationship is also shown in Figure 6.17.
EMPLOYEE
ename essn dnum snum dm_ssn
Kaitlyn 987–754–9865 501 1 276–263–9182 Fred 276–263–9182 502 1 null Katie 982–928–2726 503 1 987–754–9865 Seema 837–937–9373 501 1 276–263–9182 . . .
Figure 6.17: An ER Diagram of West Florida Mall Developed Thus
Far
So, in summary, our relational database has now developed to (without the data): MALL-Store MALL STORE OWNER STORE MANAGER DEPARTMENT EMPLOYEE name store_name name address
sloc sname snum mall_name so_owner sm_ssn
so_ssn so_name so_off_phone so_address
sm_ssn sm_name salary
We will continue with the development of this case study at the end of
Chapter 8.