Strong and Weak Entities

Concept

Notation

    +------------+                 +==============+
    |  EMPLOYEE  |=====< HAS >=====|| DEPENDANT  ||
    +------------+   identifying    +==============+
       emp_id           (double        name (dashed
     (underlined)        diamond)       underline =
                                        PARTIAL KEY)

  Read as: a dependant is identified by its name TOGETHER
  WITH the employee id of the employee it belongs to.
  Two employees may each have a dependant named Asha; the
  pair (emp_id, name) still distinguishes them.

  Full key of DEPENDANT = { emp_id, name }

The three conditions

An entity type is weak when all three hold:

1. It has no key attribute of its own that is unique across the whole entity set.
2. It is existence dependent on an owner entity type — remove the owner and it is meaningless.
3. Its identifying relationship has total participation on the weak side, and cardinality one to many from the owner.

Partial key

The partial key, also called the discriminator, distinguishes weak entities within one owner. It is drawn with a dashed underline. The full identifier is always the owner primary key plus the partial key.

Example

  Three classic weak entities

  1. DEPENDANT of an EMPLOYEE
       partial key   name
       full key      ( emp_id, name )
       tables        dependants ( emp_id, name, dob, relation )
                     primary key ( emp_id, name )
                     emp_id references employees ON DELETE CASCADE

  2. ORDER LINE of an ORDER
       partial key   line_no
       full key      ( order_id, line_no )
       line 1 of order 500 and line 1 of order 501 are
       different things

  3. ROOM of a BUILDING
       partial key   room_no
       full key      ( building_id, room_no )
       room 101 exists in every building on campus

Converting a weak entity to a table

1. Create a table for the weak entity.
2. Include its own attributes, including the partial key.
3. Add the owner primary key as a foreign key.
4. Make the primary key the combination of the owner key and the partial key.
5. Set the foreign key NOT NULL, because participation is total.
6. Use cascading delete, because the weak entity cannot outlive its owner.

-- Illustration only, to show the resulting shape.
-- dependants ( emp_id, name, dob, relationship )
--   PRIMARY KEY ( emp_id, name )
--   FOREIGN KEY ( emp_id ) REFERENCES employees ( emp_id )
--     ON DELETE CASCADE

Weak entity or multivalued attribute?

Both become a separate table, so how do you choose?

• If the thing has only one value and no further detail, it is a multivalued attribute — a list of phone numbers.
• If it has several attributes of its own, it is a weak entity — a dependant has a name, a date of birth and a relationship.

Common mistakes

• Calling any entity with a foreign key weak. A student has a department foreign key and is perfectly strong, because roll_no identifies it alone.
• Giving the weak entity a primary key of its own. If it has one, it is not weak.
• Forgetting the owner key in the primary key. The partial key alone is not unique across the set.
• Using a single diamond for the identifying relationship. It must be a double diamond.
• Omitting cascading delete. Orphaned weak entities are meaningless rows.

Exam and interview questions

1. Differentiate strong and weak entities on at least five points.
2. State the three conditions that make an entity weak.
3. What is a partial key and how is it drawn?
4. Write the conversion steps from a weak entity to a table.
5. When would you model something as a weak entity rather than a multivalued attribute?

Practice

1. Identify the weak entity and its partial key: an ORDER and its ORDER LINES, a BUILDING and its ROOMS, a BANK and its ACCOUNTS.
2. Explain in three sentences why a bank account is usually strong, not weak.
3. Convert a HOTEL and its ROOMS into tables, stating both primary keys.

Conclusion

A weak entity has no key of its own, depends on an owner for existence, and is identified by the owner key plus its partial key. Double rectangle, double diamond, dashed underline, composite primary key and a cascading delete — that is the complete pattern.