A39583 589 B83974 909 E98722 876 F77665 384 STUDENT
name.first name.last name.mi student_number address
Richard Earp W 589 222 2nd St
Boris Backer 909 333
Dreistrasse
Helga Hogan H 384 88 Half
Moon Ave
Arpan Bagui K 876 33 Bloom
Ave
Hema Malini 505 100
Livingstone
AUTOMOBILE
M3b_3 — For binary 1:1 relationships, if both sides have full participation constraints, you may use the semantics of the relationship to select which of the relations should contain the key of the other. It would be inappropriate to include foreign keys in both tables as you would be introducing redundancy in the database. Include any attributes on the relationship, on the relation that is getting the foreign key.
Now assuming full participation on both sides of Figure 4.1, the two tables STUDENT and AUTOMOBILE could be:
In this above case, the student_number was included in AUTOMOBILE, making student_number a foreign key in AUTOMOBILE. We could have also taken the primary key from AUTOMOBILE, vehicle_id, and included that in STUDENT table.
In this case, if the relationship had any attributes, these would have been stored in AUTOMOBILE, along with student_number.
A39583 Ford Compact Blue 1999 B83974 Chevy Compact Red 1989 E98722 Mazda Van Green 2002 F77665 Ford Compact White 1998 G99999 Chevy Van Grey 1989
STUDENT
name.first name.last name.mi student_number address
Richard Earp W 589 222 2nd St
Boris Backer 909 333
Dreistrasse
Helga Hogan H 384 88 Half
Moon Ave
Arpan Bagui K 876 33 Bloom
Ave
Hema Malini 505 100
Livingstone
AUTOMOBILE
vehicle_id make body_style color year student_number
A39583 Ford Compact Blue 1999 589 B83974 Chevy Compact Red 1989 909 E98722 Mazda Van Green 2002 876 F77665 Ford Compact White 1998 384 G99999 Chevy Van Grey 1989 505
The next set of mapping relationships maps binary 1:N relationships:
M3c — For binary 1:N relationships, we have to check what kind of participation constraints the N side of the relationship has:
M3c_1 — For binary 1:N relationships, if the N-side has full participation, include the key of the entity from the 1 side, in the relation on the N side as a foreign key.
For example, in Figure 4.4 if we assume full participation on the student side, we will have:
Dorm rooms may have zero or more students.
and
Students must live in one and only one dorm room.
The relational realization would be:
Here, the full participation is on the N side, that is, on the STUDENT entity side. So, we take the key from DORM, dname, and include it in the
STUDENT relation. In this case, if the relationship had an attribute, it would be included in STUDENT, the N side.
M3c_2 — For binary 1:N relationships, if the N-side has partial participation, the 1:N relationship is handled just like a binary M:N relationship with a separate table for the relationship. The key of the new relation consists of a concatenation of the keys of the related entities. Include
STUDENT
name.first name.last name.mi student_number dorm
Richard Earp W 589 A Boris Backer 909 C Helga Hogan H 384 A Arpan Bagui K 876 A Hema Malini 505 B DORM dname supervisor A Saunders B Backer C Hogan D Eisenhower
any attributes that were on the relationship, on this new table.
Checkpoint 4.4
1. State the mapping rules that would be used to map Figure 4.5? Map
Figure 4.5 to a relational database and show some sample data.
2. State the mapping rules that would be used to map Figure 4.8? Map
Chapter Summary
This chapter discussed cardinality and participation ratios in ER diagrams. Several examples and diagrams of binary relationships with structural constraints (developed in the Chen-like model) were discussed. Tighter English grammar was presented for each of the diagrams, and steps 7 and 8 of the ER design methodology were defined. The final section of the chapter discussed mapping relationships.
Chapter 4 Exercises
Exercise 4.1
Refer to Figure 2.3. Suppose that the only attributes of STUDENT are student number and name. And, 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 diagrams using the Chen-like model. Follow the methodology and include all English descriptions of your diagrams. Map the ER diagrams to a relational database.
Exercise 4.2
Suppose that a college has 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 diagrams using the Chen-like model. Follow the methodology and include all English descriptions of your
diagrams. Map the ER diagrams to a relational database.
Exercise 4.3
Consider a student database with students and campus organizations. Students will have the attributes of student number and student name. Organizations will have the following attributes: organization name and organization type. Draw the ER diagrams using the Chen-like model. Follow the methodology and include all English descriptions of your diagrams. Map the ER diagram to a relational database and include some sample data.
Exercise 4.4
Consider a student and advisor database. Students have a student number and student name. Advisors have names, office numbers, and advise in some major. The major that the advisor advises in is designated by a major code (e.g., Chemistry, CHEM; Biology, BIOL; Computer Science, COMPSC: etc.) Draw the ER diagrams using the Chen-like model. Follow the
methodology and include all English descriptions of your diagrams. Map the ER diagram to a relational database and include some sample data.
Exercise 4.5
You want to record the following data in a database: restaurant name and location, employee names and IDs, capacity of restaurant (smoking and non- smoking), hours of operation (assume same hours every day), employee salaries and titles. An employee can work for only one restaurant. Draw the ER diagrams using the Chen-like model. Follow the methodology and include all English descriptions of your diagrams. Map the ER diagram to a relational database and include some sample data.
Exercise 4.6
telephone #(s), delivery truck number, truck capacity, usual route description (e.g., North, West, Central, Lake). Draw the ER diagrams using the Chen- like model. Follow the methodology and include all English descriptions of your diagrams.
Exercise 4.7
Refer to Figure 4.10. What are the English language statements you can make about the figure?
Figure 4.10
Exercise 4.8
Refer to Figure 4.9. Complete the diagram by adding a precise English description of each attribute. Map Figure 4.9 to a relational database.
Exercise 4.9
What is the cardinality of the following? a. Each student can have only one car
c. Each car can be driven by many students
d. Each car must be driven by many students.
Which of these above cardinality rules are optional? Which rules are mandatory? Diagramatically show these relationships.
References
Batani, C., Ceri, S., and Navathe, S.B., Conceptual Database Design. Benjamin/Cummings Publishing, Redwood City, CA, 1992.
Earp, R. and Bagui, S., "Extending Relationships in the Entity
Relationship Diagram,"Data Base Management, Auerbach Publications, Boca Raton, FL, 22-10-42, 1-14, May 2001.
Elmasri, R. and Navathe, S.B., Fundamentals of Database Systems, 3rd ed., Addison-Wesley, Reading, MA, 2000.
Kroenke, D.M., Database Processing, Prentice Hall, Upper Saddle River, NJ, 2000.
McFadden, F.R. and Hoffer, J.A., Modern Database Management, 4th ed., Benjamin/Cummings Publishing, Redwood City, CA. 1994.
Ramakrishnan, R. and Gehrke, J., Database Management Systems, 3rd ed., McGraw-Hill, New York, 2003.
Sanders, L., Data Modeling, Boyd & Fraser Publishing, Danvers, MA, 1995.
Case Study: West Florida Mall (continued)
In the past few chapters we selected our primary entities (as per the specifications from the user so far) and defined the relationships between the primary entities. In this chapter we proceed with the ER diagram for this case study by looking at steps 6 and 7 of the ER design methodology, and map the ER diagram to a relational database (with some sample data) as we proceed.
Step 6 develops the structural constraints of binary relationship by stating:
Step 6: State the exact nature of the relationships in structured English