MAP UID'S TO PRIMARY KEYS

Map any attribute(s) which are part of the entity's UID to PK column(s). Label the columns PK.
Example: The attribute id is the UID of the entity INSTRUCTOR, so make the corresponding column INST_ID the PK of  the INSTRUCTOR table.

If an entity's UID includes a relationship - add foreign key columns to the table and mark them as part of the primary key.
Example: The UID of the ENROLLMENT entity is composed of its relationship to COURSE and its relationship to STUDENT. Add two FK columns to the ENROLLMENT table for the PK of the COURSE table and the PK of  the STUDENT table.

 

MAP RELATIONSHIPS TO FOREIGN KEYS


For M:1 relationships, take the PK at the one end and put it in the table at the many end.
Example: Take the PK INST_ID at the one end, and put it in the table COURSE at the many end.

If the table's PK includes a foreign key, the FK columns to support the relationship may have been
added.
Example: The PK for the ENROLLMENT table included both the foreign key COURSE_CODE and the foreign key ST_ID.  Therefore, these two columns already exist, and do not need to be added to support the relationships.

Mandatory 1:1 relationship - Place the unique FK in the table at the mandatory end and use the NOT NULL constraint to enforce the mandatory condition.
Example: Since the relationship from PERSONAL COMPUTER is mandatory, place the FK for the relationship in the PERSONAL_COMPUTER table and label it NOT NULL. MB_ID is the FK column added. The FK is labeled U to enforce the 1:1 relationship.


 

1:1 relationship is optional in both directions - place the FK in the table at either end of the relationship.
Example: For the optional 1:1 relationship between BERTH and SHIP, the FK column could also be placed either in the BERTH or SHIP table. The B_NUM column is added to the SHIP table, and labeled Unique to enforce the 1:1 relationship.

1:M recursive relationship - Add a FK column to the single table. This FK column will refer to values of the PK column.
Example: For this 1:M recursive relationship, add an FK column to the EM-PLOYEE table for each employee's manager. Name the column MGR_ID to reflect the relationship.

1:1 recursive relationship - add a unique FK to the table. This FK column will refer to values of the PK column.
Example: For this 1:1 recursive relationship, add a unique column to the PERSON table.

 

CHOOSE ARC OPTIONS

Arcs represent a kind of multiple alternative foreign key. Choose between two alternative designs for mapping arcs to foreign keys.
Alternative Designs
• Explicit Arc Design
• Generic Arc Design
Example: This E-R Model will map to four tables. The OFFICE SUITE entity has an arc across the many ends of three relationships, and corresponding FK columns must be added to the OFFICE_SUITE table. Use either an Explicit Arc Design or a Generic Arc Design to add these multiple alternative foreign keys.

Explicit Arc Design  - Creates a foreign key column for each relationship included in the arc.
Example: The following E-R Model contains four simple entities, and will be mapped to four separate tables. The arc spans the many end of three relationships. Therefore, FKs must be added to the OFFICE_SUITE table. Using an Explicit Arc Design, create a FK column for each relationship.

Generic Arc Design - Creates a single foreign key column and one relationship flag column for the arc. Since the relationships are exclusive, only one FK value will exist for each row in the table.
Example: Again, create four separate tables for this E-R Model - one for each entity. Since the arc spans the many end of the relationships, add the to the OFFICE_SUITE table. Using the Generic Arc Design, create a single foreign key column, and add a type column to indicate which of the three tables is referenced by the FK column in each row. For example, I for INDIVIDUAL, P for PARTNERSHIP, and C for COMPANY.

CHOOSE SUBTYPE OPTIONS

Subtype Table Mapping Options Example: In the following supertype/subtype construct, the EMPLOYEE, EXEMPT EMPLOYEE, and NON-EXEMPT EMPLOYEE entities may be mapped to one, two, or three tables, depending upon the subtype table mapping option selected.

Option 1—Single Table Subtype Design
Example: Map the EMPLOYEE supertype and its subtypes onto a single EM-PLOYEE table.


Option 2—Separate Tables Subtype Design
Example: Map the EMPLOYEE supertype onto two tables—one for each subtype. First create a separate table for the EXEMPT EMPLOYEE subtype.