In this section we develop the mapping rules for mapping weak entities to a relational database.
M4 — For weak entities — Develop a new table (relation) for each weak entity. As is the case with the strong entity, include any atomic attributes from the weak entity in the table. If there is a composite attribute, include only the atomic parts of the composite attribute, and be sure to qualify the atomic parts in order to not lose information. To relate the weak entity to its owner, include the primary key of the owner entity in the weak relation as a foreign key. The primary key of the weak relation will be the partial key in the weak entity concatenated to the key of the owner entity.
If weak entities own other weak entities, then the weak entity that is connected to the strong entity must be mapped first. The key of the weak owner entity has to be defined before the "weaker" entity (the one furthest from the strong entity) can be mapped.
For example, refer to Figure 5.3. The EMPLOYEE relation and DEPENDENT relation would be mapped as shown below:
(employee_id is the primary key of EMPLOYEE).
The primary key, employee_id, from the owner relation, EMPLOYEE, is included in the weak entity, DEPENDENT. employee_id now becomes part of the primary key of DEPENDENT. Because dname.first, dname.last, and dname.mi are the partial key of the DEPENDENT relation, the primary key of the DEPENDENT relation now finally becomes dname.first, dname.last,
EMPLOYEE
ename.first ename.last ename.mi employee_id
Richard Earp W 589 Boris Backer 909 Helga Hogan H 384 Arpan Bagui K 876 Hema Malini 505 DEPENDENT
dname.first dname.last dname.mi birth_date insurance employee_id
Beryl Earp W 1/1/74 SE 589
Kaitlyn Backer 2/25/78 SE 909
David Earp H 3/4/75 BlueCross 589
Fred Earp K 3/7/98 BlueCross 589
dname.mi, and employee_id all together.
Now refer to Figure 5.6. Here, the DEPENDENT relation is dependent on the EMPLOYEE relation, and the HOBBY relation is dependent on the
DEPENDENT relation. The EMPLOYEE relation and DEPENDENT relation would be mapped as shown above, and then the HOBBY relation would be mapped as shown below:
The partial key of HOBBY was type. The primary key of the HOBBY relation now becomes dname.first, dname.last, dname.mi, employee_id, and type, all together.
Checkpoint 5.3
1. What are the rules for mapping weak entities? Map Figure 5.5 and show some sample data.
2. When mapping weak entities, what becomes their new primary key?
3. How would you map multi-valued attributes in a weak entity? Discuss.
HOBBY
dname.first dname.last dname.mi employee_id type years_involved
Beryl Earp W 589 swimming 3
Kaitlyn Backer 909 reading 5
David Earp H 589 hiking 1
Fred Earp K 589 fishing 2
Chapter Summary
This chapter discussed and developed the concept of the "weak entity." The grammar for the weak entity was enhanced, along with the ER design methodology. The mapping rules for mapping the weak entity were also developed. This concept of the weak entity is available in the Chen-like model but is treated differently in many other ER diagram models.
Chapter 5 Exercises
Exercise 5.1
Construct an ER diagram (in the Chen-like model) for a database that is to contain the following: employee name (ename), employee number (enum), employee address (eaddr), skill(s) (eskill). An employee may have more than one skill. Then enhance the diagram to include: level of skill, date skill certified (if certified), and date began using the skill. Are there any weak entities in this database? Map this ER diagram to a relational database.
Exercise 5.2
Construct an ER diagram (in the Chen-like model) for sports and players. Attributes of SPORTS are: sport name — type of sport — timed or untimed. Attributes of PLAYERS are: name, person ID, date of birth. Players may play multiple sports. Which entity or entities would you consider weak? Write out the grammar for the ER diagram. Map this ER diagram to a relational database.
Exercise 5.3
How are weak entities generally identified?
Exercise 5.4
What mapping rules would be used to map Figure 5.4? Map Figure 5.4 to a relational database and show some sample data.
References
Chen, P.P. "The Entity Relationship Model — Toward a Unified View of Data,"ACM TODS 1, No. 1, March 1976.
Connolly, T., Begg, C., and Strachan, A. Database Systems, A Practical Approach to Design, Implementation, and Management, Addison- Wesley, Harlow, England, 1998.
Elmasri, R. and Navathe, S.B. Fundamentals of Database Systems, 3rd ed., Addison-Wesley, Reading, MA, 2000.
Ramakrishnan, R. and Gehrke, J. Database Management Systems, 3rd ed., McGraw-Hill, New York, 2003.
Case Study: West Florida Mall (continued)
In the previous chapters we selected our primary entities, defined the attributes and relationships for this case study, and mapped it to a relational database (with some sample data). In Chapter 4 we also determined the structural constraints of the relationships and adjusted some of the mappings accordingly. Then we reviewed 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: A store must have one or more departments. A department will not exist without a store. For each department we will store the department name, department number, and department manager. Each department has at least one employee working for it.
We have to record information about the employees in the store. For each employee in a store, we will have to keep an employee's name, social security number, and the department in which that the employee works. Employees must work in one and only one department.
In Chapter 3 we determined that departments was a multi-valued attribute of STORE (that is, one store had many departments). But, upon reviewing these additional (above) specifications, we can now see that DEPARTMENT needs to be an entity on its own because we have to record information about a DEPARTMENT. Also, we can see that we have to record information about another new entity, EMPLOYEE. So, these above specifications add two new entities: DEPARTMENT and EMPLOYEE.
First we will select an entity, DEPARTMENT. Now, repeating Step 2 for DEPARTMENT:
The Entity
This database records data about a DEPARTMENT. For each DEPARTMENT in the database, we record a department name (dname) and department number (dnum).