ERD Conversion Rules

1
ERD Conversion Rules

2
1. Entity Type Rule

• Each entity type becomes a table.
• Primary key of the entity becomes the key of the
  table.
• Weak entities are exceptions.

3
2. 1-M Relationship Rule

• Each 1-M relationship becomes a foreign key in
  the child entity type
• No nulls for mandatory relationships

4
3. M-N Relationship Rule

• Each M-N relationship becomes an associative
  table with a combined primary key.

5
4. Identification Dependency Rule

• Each identifying relationship adds a column to a
  primary key.

6
5. Optional 1-M relationship rule

• Separate table for each optional 1-M relationship
• Avoids null values
• Requires an extra table and join operation
• Teaches table has a 1-1 relationship to offering
  and 1-M relationship to Faculty
• PK of Teaches is an FK referencing Offering
• Controversial in most cases 1-M rule is
  preferred with a mandatory relationship
• Do not use Conversion Rule 5 for CIS305 projects
  / assignments / tests

• CREATE TABLE Teaches ( PRIMARY KEY (OfferNo) ,
  FOREIGN KEY(OfferNo) REFERENCES Offering,
  FOREIGN KEY(FacSSN) REFERENCES Faculty )

7
6. Generalization Hierarchy Rule

• Each entity type becomes a table
• Each subtype table contains specific columns plus
  the primary key of its parent table.
• Foreign key constraints for subtype tables
• CASCADE DELETE option for referenced rows
• Reduce need for null values
• Need joins and outer joins to combine tables

8
7. 1-1 Relationship Rule

• Each 1-1 relationship is converted to a foreign
  key
• For a non-optional 1-1 relationship, add two
  foreign keys, one for each entity

• CREATE TABLE Office ( PRIMARY KEY (OfficeNo) ,
  FOREIGN KEY(EmpNo) REFERENCES Employee, UNIQUE
  (EmpNo) )