Computer Engineering is coming soon

We're still writing and reviewing the notes for every subject, and we'll open it when every subject is ready. Meanwhile you can practise aptitude, ask Aria about any subject, or see the branches that are ready.

Extended ER features and mapping ER diagrams to tables

Extended ER features (weak entities, multi-valued/composite attributes, specialization/generalization, aggregation, n-ary relationships) and clear step-by-step rules for mapping any ER diagram into relational tables.

Drafted with Aria, reviewed by the AiCanCode.org team · Updated . How we write and check our notes. Spotted an error? Use Give Feedback at the bottom of the page.

Why it matters

Database designers and engineers use Extended ER features when modelling real-world systems that have inheritance (specialization), entities that cannot exist alone (weak entities), attributes that hold multiple values, composite attributes, and relationships among more than two entity types. Mapping these ER diagrams correctly into relational tables is a routine engineering task: it affects schema correctness, query simplicity, integrity constraints, and performance.

Key ideas

  • Attribute: a property of an entity (e.g., name). A key attribute uniquely identifies an entity in an entity set (e.g., StudentID).
  • Relationship: association among two or more entity sets (e.g., Enrollment between Student and Course). A relationship can have attributes (e.g., grade).
  • Strong (regular) entity: an entity with its own primary key.
  • Weak entity: an entity that has no full key of its own and depends on another (owner) entity; it has a partial key (discriminator) that, combined with the owner's primary key, uniquely identifies it.
  • Participation constraint: whether every member of an entity set must participate in a relationship. Total participation means every entity appears at least in one relationship instance.
  • Cardinality ratio: 1:1, 1:N, N:1, M:N describe counts of related instances.
  • Multi-valued attribute: attribute that may have multiple values for a single entity (e.g., phone numbers).
  • Composite attribute: attribute composed of subparts (e.g., address -> {street, city, zip}). Composite attributes are usually flattened into simple attributes in tables.
  • Derived attribute: attribute that can be computed from other attributes; normally not stored.
  • Specialization/Generalization (ISA): hierarchical relationship where a supertype (e.g., Person) is divided into subtypes (e.g., Student, Teacher). Subtypes may be disjoint or overlapping, and participation of supertype in subtypes may be total or partial.
  • Union type (category): a subtype whose instances may come from two or more unrelated supertypes.
  • Aggregation: treating a relationship as an abstract entity when it participates in another relationship.
  • n-ary relationship: relationship among n > 2 entity sets (e.g., Project assigned to Employee with Role and Hours).

Exact mapping rules (the laws universities expect):

  1. Regular entity set -> table with its key as primary key; attributes become columns. (Assumption: attributes are simple). Draw: rectangle labelled EntityName; map to table EntityName(pk, attr1, attr2, ...).

  2. Multi-valued attribute A of entity E -> create separate table E_A with columns (E_pk, A) and primary key (E_pk, A). Draw: oval A connected to E with double oval for multi-valued.

  3. Composite attribute C -> flatten: include sub-attributes as columns in E table. Do not create a column named C unless needed.

  4. Relationship R between E1 and E2 with cardinality M:N -> create relation table R with foreign keys referencing E1(pk) and E2(pk); primary key is the combination (E1_pk, E2_pk) unless R has its own key. Include relationship attributes as columns. Draw: diamond R between E1 and E2; map to table R(E1_pk, E2_pk, rattr1,...).

    Derivation: because many instances on both sides means a separate table needed to store pairs — keys must be combined to identify each pair.

  5. Relationship R that is 1:N between E1 (1 side) and E2 (N side) -> add foreign key in E2 table referencing E1(pk); include relationship attributes in E2 if they semantically attach to the pair. If participation is total on N side and relationship attributes exist, still place FK in N side and create columns for attributes there.

  6. Relationship R that is 1:1 -> choose either entity's table to place a foreign key referencing the other. Decision rules:

    • If one side has total participation, put the foreign key on that side and declare it NOT NULL and UNIQUE to enforce 1:1. If both sides have total participation, you may place the FK on either side but it must be UNIQUE (or use a separate table).
    • If one entity is strong and the other weak, follow weak-entity mapping rule.
    • If there are relationship attributes, prefer to create a separate table for R.
  7. Weak entity W with owner E: create table W whose primary key is (E_pk, W_partialKey); include foreign key E_pk references E. Mark PK accordingly. If W has its own attributes, they are columns of W table.

    Example derivation: weak entity cannot be uniquely identified by its partial key alone; concatenation with owner pk yields uniqueness.

  8. n-ary relationship R among E1, E2, ..., En: create relation R with columns (E1_pk, E2_pk, ..., En_pk, rattr1,...). Primary key is usually the combination of all participating pks unless domain knowledge gives a different key.

  9. Specialization (ISA) mapping: three standard strategies. State assumptions: subtypes fully share supertype key.

    • Strategy A (one table per hierarchy - single table inheritance): Single table for supertype and all subtype attributes; add a discriminator column type. Advantages: no joins; Disadvantages: many NULLs for subtype attributes. Draw: one large rectangle containing all attributes.
    • Strategy B (one table per concrete subtype): Table for each subtype with its own attributes plus all supertype attributes duplicated; do not create a separate supertype table. Advantages: queries for subtype are fast. Disadvantages: redundancy and no straightforward way to represent an entity that belongs to multiple subtypes.
    • Strategy C (one table for supertype + one table per subtype): Supertype table holds common attributes and PK; each subtype table holds subtype attributes and PK also as FK to supertype PK. This preserves integrity and avoids NULLs. Choose based on disjoint vs overlapping and expected queries.

    Include rules for disjoint/overlapping and total/partial: If specialization is total and disjoint, Strategy C is common; if overlapping, Strategy C with separate subtype tables that reference supertype PK is required (an instance can have rows in multiple subtype tables).

  10. Aggregation: when a relationship R (say WorksOn between Employee and Project) participates in another relationship S (say Inspect where Supervisor inspects a WorksOn instance), treat R as an entity: create table for R as above, then create table for S referencing R's PK and the other participant(s).

  11. Union type (category) mapping: Suppose subtype C is a union of supertypes A, B, ... (not in the same hierarchy). Common implementations:

  • Pattern 1 (category table + membership table): Create table C(C_ID PK, cattr1, ...). Create C_Member(C_ID PK/FK to C, A_ID FK NULL, B_ID FK NULL, ...). Add a CHECK that exactly one of (A_ID, B_ID, ...) is non-NULL; enforce disjointness/uniqueness as needed with UNIQUE constraints; total participation can be enforced by NOT NULL on the appropriate FK. Some DBMS require triggers to enforce the "exactly one" rule.
  • Pattern 2 (single category table with alternative FKs): Create C with columns C_ID PK, A_ID FK NULL, B_ID FK NULL, ... and a CHECK enforcing exactly one non-NULL FK. As above, NOT NULL and UNIQUE are used to enforce totality/disjointness where applicable. Triggers may be needed in SQL dialects with limited CHECK support.

Notes on integrity and NOT NULL: If participation is total, enforce NOT NULL. For 1:1 relationships implemented via a foreign key, add a UNIQUE constraint on that FK to enforce 1:1. Use FK actions (ON DELETE/ON UPDATE) depending on semantics.

Union types (categories): In relational mapping, enforce inclusion constraints using one of the patterns in rule 11. Disjointness and totality typically need CHECK constraints and/or triggers depending on the DBMS.

Formulas

CREATE TABLE TableName (col1 TYPE [constraints], col2 TYPE, ..., PRIMARY KEY(col1,...), FOREIGN KEY(colx) REFERENCES Other(pk)) — SQL DDL syntax to create a relation; no SI units. Use when mapping entity/relationship to tables.

Table_R = (E1.pk, E2.pk, ..., rattr1, rattr2, ...) — mapping of relationship R to table with participating primary keys and relationship attributes; pk is combined unless alternative key exists.

W.pk = (Owner.pk, W.partialKey) — primary key for weak entity table W is combination of owner's primary key and weak entity's partial key.

E_A = (E.pk, A) — table for multi-valued attribute A of entity E; PK is (E.pk, A).

Worked examples

Example 1 — Many-to-many: Student — Course — Enrollment

ER description to draw: rectangle Student (StudentID PK, Name), rectangle Course (CourseID PK, Title), diamond Enrollment between Student and Course with attribute Grade.

Mapping steps:

  1. Student -> table Student(StudentID PRIMARY KEY, Name VARCHAR(100)).
  2. Course -> table Course(CourseID PRIMARY KEY, Title VARCHAR(100)).
  3. Enrollment (M:N) -> table Enrollment(StudentID, CourseID, Grade, PRIMARY KEY(StudentID, CourseID), FOREIGN KEY(StudentID) REFERENCES Student(StudentID), FOREIGN KEY(CourseID) REFERENCES Course(CourseID)).

SQL DDL and sample run (SQLite-like output):

CREATE TABLE Student (StudentID INT PRIMARY KEY, Name TEXT);

CREATE TABLE Course (CourseID INT PRIMARY KEY, Title TEXT);

CREATE TABLE Enrollment (StudentID INT, CourseID INT, Grade TEXT, PRIMARY KEY(StudentID, CourseID), FOREIGN KEY(StudentID) REFERENCES Student(StudentID), FOREIGN KEY(CourseID) REFERENCES Course(CourseID));

Sample inserts and a query:

INSERT INTO Student VALUES (1, 'Asha'); INSERT INTO Student VALUES (2, 'Ravi'); INSERT INTO Course VALUES (10, 'DBMS'); INSERT INTO Course VALUES (20, 'OS'); INSERT INTO Enrollment VALUES (1, 10, 'A'); INSERT INTO Enrollment VALUES (2, 10, 'B');

SELECT S.StudentID, S.Name, C.Title, E.Grade FROM Enrollment E JOIN Student S ON E.StudentID = S.StudentID JOIN Course C ON E.CourseID = C.CourseID ORDER BY S.StudentID;

Exact output table (rows): 1 | Asha | DBMS | A 2 | Ravi | DBMS | B

(Format: StudentID | Name | Title | Grade)

Example 2 — Weak entity: Employee and Dependent

ER description: Rectangle Employee (EmpID PK, Name), double rectangle Dependent (DependentName partial key, Sex, BirthDate); double-diamond identifying relationship "HasDependent" from Employee to Dependent; Dependent has total participation in HasDependent.

Mapping to tables:

CREATE TABLE Employee (EmpID INT PRIMARY KEY, Name TEXT);

CREATE TABLE Dependent (EmpID INT, DependentName TEXT, Sex CHAR(1), BirthDate DATE, PRIMARY KEY(EmpID, DependentName), FOREIGN KEY(EmpID) REFERENCES Employee(EmpID));

Sample inserts and query:

INSERT INTO Employee VALUES (100, 'Sunil'); INSERT INTO Dependent VALUES (100, 'Anu', 'F', '2010-05-03');

SELECT E.EmpID, E.Name, D.DependentName, D.Sex FROM Employee E JOIN Dependent D ON E.EmpID = D.EmpID;

Output rows:

100 | Sunil | Anu | F

(Format: EmpID | Name | DependentName | Sex)

Example 3 — Specialization (ISA): Person -> Student, Teacher

ER description: Rectangle Person (PersonID PK, Name), two rectangles Student(StudentNo, Major) and Teacher(Salary), ISA arrows from Student and Teacher to Person. Assume disjoint and partial participation.

Strategy C (recommended):

CREATE TABLE Person (PersonID INT PRIMARY KEY, Name TEXT);

CREATE TABLE Student (PersonID INT PRIMARY KEY, StudentNo INT UNIQUE, Major TEXT, FOREIGN KEY(PersonID) REFERENCES Person(PersonID));

CREATE TABLE Teacher (PersonID INT PRIMARY KEY, Salary DECIMAL(10,2), FOREIGN KEY(PersonID) REFERENCES Person(PersonID));

Sample data and query to list all students with names:

INSERT INTO Person VALUES (1, 'Meera'); INSERT INTO Student VALUES (1, 2001, 'CSE');

SELECT P.PersonID, P.Name, S.StudentNo, S.Major FROM Person P JOIN Student S ON P.PersonID = S.PersonID;

Output row:

1 | Meera | 2001 | CSE

Common mistakes

  • Wrong primary key for weak entity (using only partial key instead of combining with owner PK).
  • Flattening composite attributes incorrectly (creating nested structures or a single text column instead of sub-attributes).
  • Misplacing foreign key in 1:1 relationships without considering participation constraints.
  • Not handling multi-valued attributes (leaving them as comma-separated values in one column).
  • Wrong mapping for n-ary relationships: creating only pairwise relations loses information.
  • For specialization: using single table and ignoring discriminator leading to ambiguity when overlapping specializations allowed.

For your exam

  • Diagrams: draw an ER with a weak entity, a M:N relationship with attributes, a specialization (show disjoint/overlapping), or a ternary relationship — label keys and participation. A full answer draws clean shapes (rectangles for entities, diamond for relationships, ovals for attributes), marks PKs, partial keys (dashed underline), and participation (double line for total).
  • Mapping derivations: given an ER diagram, step-by-step produce DDL. Full marks: list mapping rules used, show resulting CREATE TABLE statements, explain choice of PKs and FKs, mention NOT NULL if total participation.
  • Practical SQL: write CREATE TABLE for entities and relationships, insert a few rows and show sample SELECT that demonstrates correctness.

Quick check

  1. How do you map a multi-valued attribute Phones of Employee? (Short)
  2. What is the primary key of a weak entity table Dependent with partial key DepName and owner Employee(EmpID)?
  3. For a ternary relationship R(A,B,C) with no relationship attributes, what should be the primary key of R's table?

Answers: 1) Create table Employee_Phone(EmployeeID, Phone) with PK(EmployeeID, Phone) and FK(EmployeeID) references Employee. 2) (EmpID, DepName). 3) The combination (A.pk, B.pk, C.pk) unless a different key is specified.

Finished this topic? Mark it so your progress, study plan and readiness keep up.

Stuck on something here?