ER/EER-to-Relational Mapping

Introduction

An ER diagram describes entities, attributes, relationships, and business rules. A relational database stores that information in tables connected through keys. ER-to-relational mapping translates the conceptual design into a relational schema while preserving its meaning.

EER-to-relational mapping extends this process to superclass–subclass hierarchies and other enhanced modeling constructs. The task involves deciding which tables to create, where foreign keys belong, and how to enforce the original constraints.

Mapping Entities and Attributes

A relation is represented as a table in a relational database. Its attributes become columns, and a primary key identifies each row.

Strong Entities

Create a table for each strong entity type. Include its simple attributes and choose a candidate key as the primary key. For a composite attribute, include its component attributes.

For example, a STUDENT entity might become:

A derived attribute such as Age is usually calculated from BirthDate when needed. Storing it requires a mechanism to keep it current.

Weak Entities

Create a table containing the weak entity’s attributes and its owner’s primary key. The owner key becomes a foreign key and combines with the partial key to identify the weak entity.

For rooms numbered uniquely within each building:

BuildingID in ROOM also references BUILDING. Consequently, room (B1, 101) is distinct from room (B2, 101).

Mapping Binary Relationships

A binary relationship connects two entity types. Its cardinality determines how their tables are connected.

The following diagram shows the usual mapping for each cardinality.

One-to-One Relationships

Place one table’s primary key as a foreign key in the other table. Prefer the side with total participation when that avoids unnecessary null references.

The foreign key also needs a uniqueness constraint to preserve the maximum of one on both sides.

For example, INSTRUCTOR can contain OfficeID as a foreign key referencing OFFICE. Making OfficeID unique prevents two instructors from sharing the same assigned office. Add NOT NULL if every instructor must have an office.

Merging the tables or creating a separate association table are alternatives, depending on participation and design requirements.

One-to-Many Relationships

Place the primary key from the one side into the table on the many side as a foreign key.

For example, DEPARTMENT employs many instructors. DepartmentID therefore belongs in INSTRUCTOR. An attribute of that relationship, such as DepartmentJoinDate, can also be stored there.

Many-to-Many Relationships

Create a separate association table containing foreign keys to both participating tables. Store relationship attributes in that table. These are the standard foreign-key and association-table approaches to binary mapping.

For student enrollment, ENROLLMENT contains StudentID, CourseID, and Grade.

StudentID and CourseID can form its composite primary key if each student–course pair occurs only once. If repeat enrollments are allowed, the key must also distinguish the term or course offering.

Mapping Multivalued Attributes and Higher-Degree Relationships

Multivalued Attributes

A multivalued attribute needs a separate table containing the owner’s key and one value per row.

Suppose a student can have several phone numbers. Create STUDENT_PHONE with StudentID and PhoneNumber. Their combination identifies each stored association, and StudentID references STUDENT.

This allows additional phone numbers without adding columns such as Phone1, Phone2, and Phone3.

Ternary and Other N-ary Relationships

Create a table containing foreign keys to all participating entity tables, together with the relationship’s attributes.

For example, TEACHING_ASSIGNMENT may connect INSTRUCTOR, COURSE, and CAMPUS.

Its key must follow the business rules. If an instructor can teach a course at several campuses, the three identifiers may be required together. If each instructor–course pair determines exactly one campus, that pair may already be a candidate key.

Preserve the joint association: separate pairwise tables may lose which instructor teaches which course at which campus.

Mapping EER Specialization and Generalization

A superclass–subclass hierarchy has several possible relational representations.

Superclass and Subclass Tables

Create a superclass table for shared attributes and separate subclass tables for specialized attributes. Each subclass uses the superclass key as both its primary key and a foreign key.

The following example preserves PERSON, STUDENT, and EMPLOYEE separately.

Person P7 has shared details in PERSON, study details in STUDENT, and employment details in EMPLOYEE. The same identifier connects these records.

This strategy supports disjoint or overlapping subclasses and total or partial specialization. Other standard options are compared below.

For partial specialization in a single-table design, include a way to represent entities belonging to no listed subclass.

Categories or Union Types

A category can draw members from different entity types. For example, OWNER may represent either a PERSON or a COMPANY.

One implementation introduces OwnerID as a shared reference identity, with links to the appropriate source entity. Additional constraints must ensure those links follow the category’s membership rules. Categories are another EER construct requiring explicit mapping decisions.

Preserving Constraints After Mapping

Creating the tables is only part of the transformation. The resulting database must enforce the intended rules.

• Identity: Define primary keys and preserve other candidate keys with suitable uniqueness constraints. Adding a surrogate key should not silently allow duplicate business identities.

• Referential integrity: Define foreign keys explicitly. A column named DepartmentID does not enforce a reference merely because its name matches another table.

• Mandatory participation: Use NOT NULL where every referencing row requires an association. Requiring every parent to have at least one child usually needs additional enforcement.

• Subclass membership: Foreign keys ensure subclass rows reference superclass rows. They do not alone enforce disjointness or require every superclass entity to appear in a subclass.

• Deletion behavior: Choose whether referenced-row deletion is restricted, cascaded, or otherwise handled. The mapping does not automatically determine the appropriate policy.

Finally, check the schema with small valid and invalid examples. Try assigning one office to two instructors, repeating an enrollment, or creating a room without a building. Each outcome should match the original requirements.

Summary

ER/EER-to-relational mapping converts conceptual designs into tables, columns, keys, and constraints. Entities generally become tables, one-to-many relationships use foreign keys, and many-to-many relationships need association tables. EER hierarchies offer several mapping strategies. A successful mapping preserves identification, relationship meaning, participation requirements, and subclass membership rules.