Implementing Relationships

---

Implementing Many-One Relationships

• Implement entities step converts identifying relationships into foreign keys
• implement relationships step converts all other relationships into foreign keys or tables
• a many-one or one-many relationship becomes a foreign key
  • FK goes in the table on the ‘many’ side and refers to the table on the ‘one’ side
  • if entity on the ‘one’ side is required, the FK column is also required
• FK name is the same name as the referenced PK
  • can have optional prefix
  • prefix is usually derived from the relationship name

Implementing One-One Relationships

• A one-one relationship becomes a foreign key
  • FK can go in the table on either side of the relationship
    • usually placed in the one with fewer rows to minimize NULL values
• FK refers to the table on the opposite of the relationship
• FK column is unique
• if the entity on the opposite side is required, then the FK column is required
• FK name is the name of the referenced PK
  • can have optional prefix
  • derived from the relationship name

Implementing Many-Many Relationships

• a many-many relationship becomes a weak table
  • contains two foreign keys
    • referring to the primary keys of the related tables
  • PK of the new table is the composite of the two FK
  • new table is identified by the related tables
    • PK cascade and FK restrict rules are usually specified
• occasionally, an attribute describes a many-many relationship and becomes a column of the weak table
• table name consists of the related table names
  • can have an optional qualifier in between
    • usually derived from the relationship

Database Design

• in implement entities step, identifying relationships become foreign keys
• all other relationships become foreign keys in the implement relationships step
• each many-one and one-one relationship becomes a new FK
  • FK names are the referenced PK names
    • optional prefix derived from the relationship
• each many-many relationship becomes a weak table
  • table contains two FKs that refer to the related tables
  • table name consists of the related table names
    • optional qualifier derived from the relationship name

Implement Relationships

Step	Activities
6A	Implement many-one relationships as a foreign key on the ‘many’ side.
6B	Implement one-one relationships as a foreign key in the table with fewer rows.
6C	Implement many-many relationships as new weak tables.