Implementing Entities

---

Selecting Primary Keys

• Primary keys should be:
  • Required:
    • unique and required (not NULL)
  • desirable, but not required:
    • Stable
      • Primary key values should not change
    • Simple
      • should be easy to type and store
    • Meaningless
      • should not contain descriptive information
      • bc this could change, and thus is unstable

Implementing Strong Entities

A strong entity becomes a strong table.

• primary key
  • must be
    • unique and required
  • should be
    • stable, simple, and meaningless

An artificial key is a simple primary key created by the database designer.

• Usually are integers
  • generated automatically by the database as new rows are inserted
• are stable, simple, and meaningless

Implementing Weak Entities

A weak entity becomes a weak table.

• has a foreign key that references the identifying table and implements the identifying relationship
• primary key depends on the cardinality of the identifying relationship:
  • Usually, weak entity is plural
    • primary key is the composite of the foreign key and another column
  • Occasionally, weak entity is singular
    • primary key is the foreign key only
• foreign key usually has the following referential integrity actions:
  • Cascade on primary key update and delete
  • Restrict on foreign key insert and update
• in table diagrams, an arrow indicates a foreign key
  • starts at the foreign key and points to the table containing the referenced primary key
• PK includes one FK for each identifying relationship
• PK may include an additional column for uniqueness

Implementing Supertype and Subtype Entities

A supertype entity becomes a supertype table.

• supertype entity that has an identifying attribute is implemented like a strong entity
• supertype entity that has an identifying relationship, rather than an identifying attribute, is implemented like a weak entity

A subtype entity becomes a subtype table:

• primary key is identical to the supertype primary key
• primary key is also a foreign key that references the supertype primary key

The foreign key usually has the following referential integrity actions:

• Cascade on primary key update and delete
• Restrict on foreign key insert and update

Foreign key implements the IsA relationship between subtype and supertype entities.

• aka identifying relationship

Database Design

• implement entities step:
  • creates initial table design
  • specifies primary keys
    • can use artificial key

Implement Entities

Step	Activity
5A	Implement strong entities as tables.
5B	Create an artificial key when no suitable primary key exists.
5C	Implement weak entities as tables.
5D	Implement supertype and subtype entities as tables.