Skip to content

Lesson 19: ER-to-Relational Mapping

The seven-step algorithm, applied end to end

About this lesson

Course: Database Management Systems

Unit 5: Database Design · 10 Lecture Hours

Lesson duration: 2 lectures

Reading: Elmasri & Navathe, Ch. 9.1

Learning Outcomes

  1. State the seven-step mapping algorithm from memory.
  2. Map strong entities, weak entities, and all three binary relationship types.
  3. Map multivalued attributes and n-ary relationships.
  4. Justify the choice of side for a 1:1 foreign key.
  5. Map a complete ER diagram, producing full SQL DDL.

19.1 Where This Sits

Unit 2 produced an ER diagram — a technology-free description. Unit 3 and 4 described relations and SQL. This lesson is the bridge: a mechanical procedure converting the one into the other.

   ER DIAGRAM  (Unit 2)                RELATIONAL SCHEMA  (Units 3, 4)
   entity types      ------ step 1 ----->  relations
   weak entities     ------ step 2 ----->  relations with composite keys
   1:1 relationships ------ step 3 ----->  a foreign key
   1:N relationships ------ step 4 ----->  a foreign key on the N side
   M:N relationships ------ step 5 ----->  a NEW relation (junction table)
   multivalued attrs ------ step 6 ----->  a NEW relation
   n-ary relationships----- step 7 ----->  a NEW relation

Why the algorithm matters

A schema derived by this algorithm from a correct ER diagram is already close to normalised — usually in 3NF or BCNF. Hand-written tables usually are not, and must be repaired by the normalisation of Lessons 2 to 5.

Good conceptual design is therefore not merely documentation; it is the cheapest route to a good logical design.

19.2 The Seven Steps

Step 1 — Regular (strong) entity types

Rule

For each regular entity type E, create a relation R that includes all the simple attributes of E.

  • A composite attribute contributes its simple component attributes as separate columns; the composite name itself does not appear.
  • Choose one key attribute of E as the primary key of R. If the chosen key is composite, the set of its simple attributes together forms the primary key.
  • Other candidate keys become UNIQUE constraints.
  • Multivalued and derived attributes are not included here (steps 6 and "omit" respectively).

EMPLOYEE with Ssn (key), Name(Fname, Minit, Lname) composite, Bdate, Address, Salary, Sex, derived Age, multivalued {Degree}:

EMPLOYEE ( Ssn, Fname, Minit, Lname, Bdate, Address, Salary, Sex )
--         ^^^ primary key
-- Name -> three columns.  Age -> omitted (derived).  {Degree} -> step 6.

Step 2 — Weak entity types

Rule

For each weak entity type W with owner E, create a relation R containing all simple attributes of W, plus the primary key of the owner as a foreign key.

The primary key of R is the owner's primary key together with the partial key of W.

Declare ON DELETE CASCADE, because a weak entity is existence-dependent on its owner.

DEPENDENT (partial key DependentName) owned by EMPLOYEE:

DEPENDENT ( Essn, DependentName, Sex, Bdate, Relationship )
--          ^^^^^^^^^^^^^^^^^^^ composite primary key
--          Essn is also a FOREIGN KEY to EMPLOYEE(Ssn), ON DELETE CASCADE

If the owner is itself weak, apply step 2 to the owner first — the keys propagate down the chain.

Step 3 — Binary 1:1 relationship types

Three approaches; the foreign key approach is almost always correct.

Rule (foreign key approach)

Choose one of the two relations — say S — and include as a foreign key in S the primary key of the other relation T. Add the relationship's own attributes to S as well.

Choose for S the entity type with TOTAL participation. Then the foreign key is never NULL.

EMPLOYEE MANAGES DEPARTMENT, 1:1, with DEPARTMENT total (1,1) and EMPLOYEE partial (0,1), relationship attribute StartDate:

DEPARTMENT ( Dnumber, Dname, MgrSsn, MgrStartDate )
--                           ^^^^^^ FK to EMPLOYEE(Ssn), NOT NULL

Why the total side receives the foreign key

Put MgrSsn in DEPARTMENT: every department has exactly one manager, so the column is never NULL and can be declared NOT NULL.

Put ManagedDept in EMPLOYEE instead: only a handful of employees manage anything, so the column is NULL for almost every row — wasted space, and a design smell suggesting a specialisation is missing.

If both sides are total, the two entity types can be merged into one relation (approach 2). If both are partial, a separate relation (approach 3, as for M:N) avoids the NULLs.

Step 4 — Binary 1:N relationship types

Rule

Include in the relation S on the N side a foreign key referencing the primary key of the relation T on the 1 side. Relationship attributes also go to S.

There is no choice here: the foreign key must go on the N side. Each entity on the N side participates in exactly one relationship instance, so it has exactly one value to store.

DEPARTMENT 1:N EMPLOYEE (WORKS_FOR), EMPLOYEE total:

EMPLOYEE ( Ssn, Fname, ..., Dno )
--                          ^^^ FK to DEPARTMENT(Dnumber), NOT NULL

For a recursive 1:N relationship, the foreign key references the same relation:

EMPLOYEE ( Ssn, Fname, ..., Dno, SuperSsn )
--                               ^^^^^^^^ FK to EMPLOYEE(Ssn), NULLable

SuperSsn must allow NULL, because the chief executive has no supervisor.

Step 5 — Binary M:N relationship types

Rule

Create a new relation S (a junction, linking or associative table) containing:

  • the primary keys of both participating relations, as foreign keys;
  • all attributes of the relationship.

The primary key of S is the combination of the two foreign keys.

A foreign key approach is impossible here — it would require storing many values in one column, violating 1NF.

EMPLOYEE M:N PROJECT (WORKS_ON) with attribute Hours:

WORKS_ON ( Essn, Pno, Hours )
--         ^^^^^^^^^ composite primary key; both are foreign keys

When the composite key is not enough

The pair (Essn, Pno) is the key only if an employee works on a project once. If the same pair may recur — a member borrowing the same book in different years, a student re-taking a course — a distinguishing attribute must be added to the key, typically a date:

BORROWS ( MemberID, ISBN, IssueDate, DueDate )
--        ^^^^^^^^^^^^^^^^^^^^^^^^^ three-part primary key

This is exactly the check that Step 7 of the ER design procedure (read the diagram back in English) is meant to catch.

Step 6 — Multivalued attributes

Rule

For each multivalued attribute A, create a new relation R containing:

  • a column for A;
  • the primary key of the owning relation, as a foreign key.

The primary key of R is both together. If A is composite, include its simple components.

Declare ON DELETE CASCADE.

{Dlocations} of DEPARTMENT:

DEPT_LOCATIONS ( Dnumber, Dlocation )
--               ^^^^^^^^^^^^^^^^^^ composite primary key

This is the 1NF requirement in action (Unit 3, Lesson 1): a set of values cannot occupy a single column.

Step 7 — n-ary relationship types

Rule

For each n-ary relationship type (degree > 2), create a new relation S containing the primary keys of all participating relations as foreign keys, plus the relationship's own attributes.

The primary key of S is normally the combination of all the foreign keys — unless a participating entity has cardinality 1, in which case its key may be omitted from the primary key.

SUPPLIER – PART – PROJECT (SUPPLIES) with attribute Quantity:

SUPPLIES ( Sno, Pno, Jno, Quantity )
--         ^^^^^^^^^^^^^ three-part primary key

Summary table

ER construct Maps to Primary key of the result
Strong entity type A relation Its own key attribute
Weak entity type A relation Owner's key + partial key
1:1 relationship A foreign key in one relation (unchanged)
1:N relationship A foreign key on the N side (unchanged)
M:N relationship A new relation Both foreign keys combined
Multivalued attribute A new relation Owner's key + the attribute
n-ary relationship A new relation All foreign keys combined
Composite attribute Its simple components, as columns —
Derived attribute Omitted (computed on demand) —

19.3 Worked Mapping 1 — The College Database

Input — the ER diagram of Unit 2, Lesson 4:

STUDENT ( Rollno, Name, Dateofbirth )                       key Rollno
COURSE  ( SID, Cname, TotalSeats, Duration,
          Coursetype, TeacherInCharge )                     key SID
SOCIETY ( SocID, Socname, Mentor, TotalSeats )              key SocID

TAKES_ADMISSION  STUDENT (1,N) -- COURSE  (0,N)   attribute Dateofadmission
ENROLS_IN        STUDENT (0,N) -- SOCIETY (0,N)   attribute Dateofenrollment

Step 1 — three strong entity types give three relations.

Steps 2, 3, 4 — no weak entities, no 1:1, no 1:N relationships. Nothing to do.

Step 5 — two M:N relationships give two new relations:

  • ADMISSION(Rollno, SID, Dateofadmission), primary key (Rollno, SID)
  • ENROLLMENT(Rollno, SocID, Dateofenrollment), primary key (Rollno, SocID)

Steps 6, 7 — no multivalued attributes, no n-ary relationships.

Result:

CREATE TABLE STUDENT (
    Rollno      VARCHAR(8)  PRIMARY KEY,
    Name        VARCHAR(40) NOT NULL,
    Dateofbirth DATE
);
CREATE TABLE COURSE (
    SID             VARCHAR(6)  PRIMARY KEY,
    Cname           VARCHAR(40) NOT NULL UNIQUE,
    TotalSeats      INT         NOT NULL CHECK (TotalSeats > 0),
    Duration        INT,
    Coursetype      VARCHAR(10) NOT NULL CHECK (Coursetype IN ('Fulltime','Parttime')),
    TeacherInCharge VARCHAR(40)
);
CREATE TABLE SOCIETY (
    SocID      VARCHAR(6)  PRIMARY KEY,
    Socname    VARCHAR(40) NOT NULL UNIQUE,
    Mentor     VARCHAR(40),
    TotalSeats INT         NOT NULL CHECK (TotalSeats > 0)
);
CREATE TABLE ADMISSION (
    Rollno          VARCHAR(8) NOT NULL,
    SID             VARCHAR(6) NOT NULL,
    Dateofadmission DATE       NOT NULL,
    PRIMARY KEY (Rollno, SID),
    FOREIGN KEY (Rollno) REFERENCES STUDENT(Rollno) ON DELETE CASCADE  ON UPDATE CASCADE,
    FOREIGN KEY (SID)    REFERENCES COURSE(SID)     ON DELETE RESTRICT ON UPDATE CASCADE
);
CREATE TABLE ENROLLMENT (
    Rollno           VARCHAR(8) NOT NULL,
    SocID            VARCHAR(6) NOT NULL,
    Dateofenrollment DATE       NOT NULL,
    PRIMARY KEY (Rollno, SocID),
    FOREIGN KEY (Rollno) REFERENCES STUDENT(Rollno) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (SocID)  REFERENCES SOCIETY(SocID)  ON DELETE CASCADE ON UPDATE CASCADE
);

What the mapping cannot carry across

The total participation of STUDENT in TAKES_ADMISSION — "every student must be admitted to at least one course" — has not been captured. No column or foreign key can express it: the constraint says a row in STUDENT must have a matching row in ADMISSION, which is the reverse direction of a foreign key.

Enforcing it requires a trigger or a deferred assertion. This is a general limitation: total participation on the "1" side of a relationship, and on both sides of an M:N, is not expressible in standard DDL. Always state this explicitly in an examination answer.

19.4 Worked Mapping 2 — The COMPANY Database

The full textbook example, exercising every step.

Step 1 — strong entity types.

EMPLOYEE   ( Ssn, Fname, Minit, Lname, Bdate, Address, Sex, Salary )
DEPARTMENT ( Dnumber, Dname )            -- Dname also UNIQUE (second candidate key)
PROJECT    ( Pnumber, Pname, Plocation ) -- Pname also UNIQUE

Name was composite → three columns. NumberOfEmployees was derived → omitted. {Dlocations} was multivalued → deferred to step 6.

Step 2 — the weak entity type DEPENDENT, owner EMPLOYEE, partial key DependentName:

DEPENDENT ( Essn, DependentName, Sex, Bdate, Relationship )
-- PK (Essn, DependentName);  Essn FK -> EMPLOYEE(Ssn) ON DELETE CASCADE

Step 3 — the 1:1 relationship MANAGES. DEPARTMENT is total (1,1), EMPLOYEE partial (0,1), so the foreign key goes into DEPARTMENT:

DEPARTMENT ( Dnumber, Dname, MgrSsn, MgrStartDate )
-- MgrSsn FK -> EMPLOYEE(Ssn), NOT NULL

Step 4 — the 1:N relationships. Foreign key on the N side each time:

EMPLOYEE ( Ssn, ..., Dno, SuperSsn )
--   Dno      FK -> DEPARTMENT(Dnumber), NOT NULL   (WORKS_FOR, employee total)
--   SuperSsn FK -> EMPLOYEE(Ssn), NULLable         (SUPERVISION, recursive)
PROJECT ( Pnumber, Pname, Plocation, Dnum )
--   Dnum FK -> DEPARTMENT(Dnumber), NOT NULL       (CONTROLS)

Step 5 — the M:N relationship WORKS_ON with attribute Hours:

WORKS_ON ( Essn, Pno, Hours )    -- PK (Essn, Pno); both are foreign keys

Step 6 — the multivalued attribute {Dlocations}:

DEPT_LOCATIONS ( Dnumber, Dlocation )   -- PK both; Dnumber FK, ON DELETE CASCADE

Step 7 — no n-ary relationships.

Final schema — eight relations:

   EMPLOYEE(Ssn, Fname, Minit, Lname, Bdate, Address, Sex, Salary, Dno, SuperSsn)
   DEPARTMENT(Dnumber, Dname, MgrSsn, MgrStartDate)
   DEPT_LOCATIONS(Dnumber, Dlocation)
   PROJECT(Pnumber, Pname, Plocation, Dnum)
   WORKS_ON(Essn, Pno, Hours)
   DEPENDENT(Essn, DependentName, Sex, Bdate, Relationship)

   Foreign keys:
     EMPLOYEE.Dno        -> DEPARTMENT.Dnumber
     EMPLOYEE.SuperSsn   -> EMPLOYEE.Ssn            (recursive, NULLable)
     DEPARTMENT.MgrSsn   -> EMPLOYEE.Ssn
     DEPT_LOCATIONS.Dnumber -> DEPARTMENT.Dnumber
     PROJECT.Dnum        -> DEPARTMENT.Dnumber
     WORKS_ON.Essn       -> EMPLOYEE.Ssn
     WORKS_ON.Pno        -> PROJECT.Pnumber
     DEPENDENT.Essn      -> EMPLOYEE.Ssn

The circular-reference problem

EMPLOYEE.Dno references DEPARTMENT, and DEPARTMENT.MgrSsn references EMPLOYEE. Neither table can be created with both constraints in place, and neither row can be inserted first.

Solutions: create both tables without the foreign keys and add them afterwards with ALTER TABLE; make one of the columns temporarily nullable and update it after both rows exist; or use DEFERRABLE INITIALLY DEFERRED constraints in systems that support them.

CREATE TABLE EMPLOYEE ( Ssn ..., Dno INT );        -- no FK yet
CREATE TABLE DEPARTMENT ( Dnumber ..., MgrSsn ... );
ALTER TABLE EMPLOYEE   ADD FOREIGN KEY (Dno)    REFERENCES DEPARTMENT(Dnumber);
ALTER TABLE DEPARTMENT ADD FOREIGN KEY (MgrSsn) REFERENCES EMPLOYEE(Ssn);

19.5 Mapping EER Constructs (brief)

For a specialisation of superclass C into subclasses S1 ... Sm, four options:

Option Relations created Best when
A — multiple relations, superclass and subclasses One for C, one per Si holding the key of C plus the subclass's own attributes Any case; works always. Needs a join to see a subclass entity whole.
B — multiple relations, subclasses only One per Si holding all attributes (inherited and own) Specialisation is total and disjoint. No join needed; but common attributes are duplicated across relations.
C — single relation with one type attribute One relation with all attributes plus a discriminator Specialisation is disjoint; few subclass-specific attributes. Produces NULLs.
D — single relation with several Boolean type attributes One relation with one flag per subclass Specialisation is overlapping. Produces the most NULLs.

19.6 Solved Questions

Solved Question 1

Q. State the seven steps of the ER-to-relational mapping algorithm.

A.

Step Construct Action Primary key
1 Regular entity type Create a relation with all simple attributes (composite → components; derived omitted; multivalued deferred) The chosen key attribute
2 Weak entity type Create a relation with its simple attributes plus the owner's primary key as a foreign key, ON DELETE CASCADE Owner's key + partial key
3 Binary 1:1 Put the other relation's key as a foreign key in one of them, together with any relationship attributes Unchanged
4 Binary 1:N Put the 1-side key as a foreign key in the N-side relation Unchanged
5 Binary M:N Create a new relation holding both keys as foreign keys plus the relationship attributes Both foreign keys combined
6 Multivalued attribute Create a new relation holding the attribute and the owner's key Both together
7 n-ary relationship Create a new relation holding all participants' keys plus relationship attributes All the foreign keys

The order matters: steps 1 and 2 create the relations that steps 3 to 7 refer to.

Solved Question 2

Q. Why does an M:N relationship require a new relation while a 1:N does not?

A. Because of how many values must be stored per entity.

In a 1:N relationship each entity on the N side participates in exactly one relationship instance, so it needs to record exactly one related key value. A single foreign-key column holds it perfectly. (The 1 side would need many values, which is why the foreign key must go on the N side, not the 1 side.)

In an M:N relationship both sides may participate many times. A foreign key in EMPLOYEE could hold only one project; a foreign key in PROJECT only one employee. Storing several would mean putting a set of values in one column, which violates atomicity and hence 1NF (Unit 3, Lesson 1).

The only structure that can represent a set of pairs is a relation of pairs — a junction table with one row per relationship instance:

WORKS_ON ( Essn, Pno, Hours )    -- one row per (employee, project) pair

The composite key (Essn, Pno) enforces that each pair occurs once, which is exactly the meaning of the M:N relationship set as a subset of the Cartesian product.

A second, equally decisive reason: an M:N relationship's own attributes (here Hours) belong to the pair, not to either entity (Unit 2, Lesson 3), so they have nowhere else to live.

Solved Question 3

Q. For a 1:1 relationship, which side should receive the foreign key? Justify with the MANAGES example, and state the two alternative approaches.

A. The side with total participation.

In EMPLOYEE (0,1) MANAGES (1,1) DEPARTMENT: every department has exactly one manager, but only a few employees manage anything.

Foreign key in DEPARTMENT — MgrSsn has a value in every row, so it can be declared NOT NULL. No space is wasted and the constraint is enforceable.

Foreign key in EMPLOYEE — ManagedDept would be NULL for almost every employee. This wastes space, makes the column useless as a constraint, and is the classic "too many NULLs" design smell that usually indicates a missing specialisation.

Two alternative approaches:

  1. Merged relation. If both sides have total participation, the two entity types are in one-to-one correspondence and may be combined into a single relation containing all attributes of both. Use with care: it obscures the conceptual distinction, and it becomes wrong if the participation later relaxes.

  2. Cross-reference (relationship) relation. Create a separate relation holding both keys, exactly as for M:N — but with a UNIQUE constraint on each key column to enforce the 1:1 ratio. This is the right choice when both sides are partial, since it stores a row only for pairs that actually exist and avoids NULLs entirely. Its cost is an extra join.

Solved Question 4

Q. Map this ER diagram to relations, giving full DDL:

HOTEL(HotelID, Name, {Phone}); ROOM numbered 1 upward within a hotel, with Type and Rate; GUEST(GuestID, Name); a BOOKS relationship between GUEST and ROOM with CheckIn and CheckOut, where a guest may book many rooms over time and a room is booked by many guests over time.

A.

Step 1 — HOTEL and GUEST are strong. Step 2 — ROOM is weak: room 1 exists in every hotel, so RoomNo alone identifies nothing. Owner HOTEL, partial key RoomNo, full key (HotelID, RoomNo). Step 5 — BOOKS is M:N, so it becomes a new relation. Because a guest may book the same room again later, the pair is not unique and CheckIn must join the key. Step 6 — {Phone} is multivalued, so it becomes a new relation.

CREATE TABLE HOTEL (
    HotelID VARCHAR(6)  PRIMARY KEY,
    Name    VARCHAR(50) NOT NULL
);

CREATE TABLE HOTEL_PHONE (                       -- step 6
    HotelID VARCHAR(6) NOT NULL,
    Phone   CHAR(10)   NOT NULL,
    PRIMARY KEY (HotelID, Phone),
    FOREIGN KEY (HotelID) REFERENCES HOTEL(HotelID) ON DELETE CASCADE
);

CREATE TABLE ROOM (                              -- step 2, weak entity
    HotelID VARCHAR(6)   NOT NULL,
    RoomNo  INT          NOT NULL,
    Type    VARCHAR(20),
    Rate    DECIMAL(8,2) CHECK (Rate > 0),
    PRIMARY KEY (HotelID, RoomNo),
    FOREIGN KEY (HotelID) REFERENCES HOTEL(HotelID) ON DELETE CASCADE
);

CREATE TABLE GUEST (
    GuestID VARCHAR(8)  PRIMARY KEY,
    Name    VARCHAR(40) NOT NULL
);

CREATE TABLE BOOKS (                             -- step 5, M:N
    GuestID  VARCHAR(8) NOT NULL,
    HotelID  VARCHAR(6) NOT NULL,
    RoomNo   INT        NOT NULL,
    CheckIn  DATE       NOT NULL,
    CheckOut DATE       NOT NULL,
    PRIMARY KEY (GuestID, HotelID, RoomNo, CheckIn),
    FOREIGN KEY (GuestID)          REFERENCES GUEST(GuestID) ON DELETE RESTRICT,
    FOREIGN KEY (HotelID, RoomNo)  REFERENCES ROOM(HotelID, RoomNo) ON DELETE RESTRICT,
    CHECK (CheckOut > CheckIn)
);

Three points worth noting. The foreign key in BOOKS is composite — (HotelID, RoomNo) — because the weak entity's key is composite. CheckIn is part of the primary key because the same guest may return to the same room. And ON DELETE RESTRICT protects the booking history, whereas ROOM and HOTEL_PHONE cascade because they are genuinely existence-dependent on the hotel.

Solved Question 5

Q. Which ER constraints cannot be carried into the relational schema by the mapping? How are they enforced?

A. Three kinds.

1. Total participation on the "1" side of a 1:N, and on either side of an M:N. "Every student must be admitted to at least one course" asserts that a STUDENT row must have a matching ADMISSION row. A foreign key enforces the opposite direction — that every ADMISSION has a matching STUDENT. There is no declarative construct for the required direction. Enforcement: a trigger on STUDENT (and on DELETE from ADMISSION), or a deferred assertion in systems that implement them. Some designs instead enforce it in the application layer at the point of admission.

2. Cardinality upper bounds other than 1 and N. "A student may take at most three societies" cannot be expressed by a key or foreign key. Enforcement: a trigger counting the related rows before an insert.

3. Arbitrary business rules and inter-relation constraints. "A department's manager must be an employee of that department", "an employee's salary may not exceed their supervisor's". These span two tables or two rows and are beyond CHECK, which may reference only its own row. Enforcement: triggers, or assertions.

A fourth, subtler loss: role names and much of the diagram's documentary meaning disappear, surviving only as column names. This is one reason the ER diagram should be kept as part of the system's documentation rather than discarded once the tables exist.

Solved Question 6

Q. Explain the circular-reference problem in the COMPANY schema and give two solutions.

A. EMPLOYEE.Dno is a foreign key to DEPARTMENT, and DEPARTMENT.MgrSsn is a foreign key to EMPLOYEE. The dependency is a cycle, which breaks the ordinary rule "create parents before children" — each table is a parent of the other.

The problem appears twice:

  • At creation time. Whichever table is created first refers to one that does not yet exist, so the CREATE TABLE fails.
  • At insertion time. The first employee cannot be inserted without an existing department, and the first department cannot be inserted without an existing manager.

Solution 1 — create the tables without the foreign keys, then add them.

CREATE TABLE EMPLOYEE   ( Ssn VARCHAR(9) PRIMARY KEY, ..., Dno INT );
CREATE TABLE DEPARTMENT ( Dnumber INT PRIMARY KEY, ..., MgrSsn VARCHAR(9) );
ALTER TABLE EMPLOYEE   ADD CONSTRAINT fk_e_d FOREIGN KEY (Dno)    REFERENCES DEPARTMENT(Dnumber);
ALTER TABLE DEPARTMENT ADD CONSTRAINT fk_d_e FOREIGN KEY (MgrSsn) REFERENCES EMPLOYEE(Ssn);

Solution 2 — make one column nullable and fill it in afterwards. Insert the department with MgrSsn = NULL, insert its employees, then UPDATE DEPARTMENT SET MgrSsn = .... This works because referential integrity permits a NULL foreign key.

INSERT INTO DEPARTMENT VALUES (5, 'Research', NULL, NULL);
INSERT INTO EMPLOYEE   VALUES ('123456789', 'John', ..., 5, NULL);
UPDATE DEPARTMENT SET MgrSsn = '123456789', MgrStartDate = '2020-05-22' WHERE Dnumber = 5;

A third solution, where supported, is to declare the constraints DEFERRABLE INITIALLY DEFERRED, so that both inserts occur inside one transaction and the constraints are checked only at COMMIT. Note that solution 2 conflicts with declaring MgrSsn NOT NULL, so the designer must choose between the stricter constraint and the simpler loading procedure.

19.7 Exercises

A. Short answer

  1. State the seven steps of the mapping algorithm.
  2. What happens to a composite attribute? A derived attribute?
  3. What is the primary key of the relation produced from a weak entity type?
  4. On which side of a 1:N relationship does the foreign key go, and why?
  5. Why can an M:N relationship not be mapped with a foreign key?
  6. What is the primary key of the relation produced from a multivalued attribute?
  7. Name one ER constraint that cannot be mapped to DDL.

B. Mapping problems — give full DDL for each

  1. DEPARTMENT(Dno, Dname) 1:N EMPLOYEE(Eno, Ename), employee total.
  2. STUDENT M:N SOCIETY with Dateofenrollment.
  3. PERSON(PID, Name) 1:1 PASSPORT(PassportNo, Expiry), passport total.
  4. COURSE(SID, Cname) with weak entity SECTION numbered within the course, having Room and Timing.
  5. EMPLOYEE with multivalued {Skill} and composite Name(First, Last).
  6. EMPLOYEE recursive 1:N SUPERVISES with roles supervisor / supervisee.
  7. Ternary DOCTOR – PATIENT – DRUG (PRESCRIBES) with Dosage and Date.
  8. The full hospital diagram of Unit 2, Lesson 4 (wards, beds, patients, doctors).
  9. The full airline diagram from Unit 2 exercise 11.

C. Analytical

  1. For the college schema, write the trigger (in pseudocode or SQL) that would enforce the total participation of STUDENT in TAKES_ADMISSION. What events must it fire on?
  2. A designer maps a 1:1 relationship by putting the foreign key on the partial side. 95 per cent of rows are NULL. Explain the cost and give the correct design.
  3. Explain when the merged-relation approach to a 1:1 relationship is correct, and give a case where it would be wrong.
  4. Show that mapping a ternary relationship as three binary relationships loses information, using concrete tuples.
  5. Take the BORROWS example. Explain precisely why (MemberID, ISBN) is an insufficient primary key, and construct the two rows that would collide.
  6. Compare the four EER specialisation mapping options for STUDENT specialised into UG and PG, and recommend one with justification.
  7. Explain why a schema derived by this algorithm is usually already in 3NF. Give an ER diagram whose mapping is not in 3NF, and say what went wrong at the conceptual stage.

D. Objective — True or False with reason

  1. A derived attribute becomes a column in the relation.
  2. Every relationship type becomes a relation.
  3. A weak entity's relation has a composite primary key.
  4. The foreign key for a 1:N relationship may be placed on either side.
  5. A multivalued attribute becomes several columns.
  6. Total participation is always enforceable with a NOT NULL foreign key.

Answers to Section D

  1. False. It is omitted and computed on demand; storing it creates redundancy that goes stale.
  2. False. Only M:N and n-ary relationships become relations. 1:1 and 1:N become foreign keys.
  3. True. The owner's primary key together with the partial key.
  4. False. It must go on the N side, where each entity participates exactly once and so has exactly one value to store.
  5. False. It becomes a separate relation — several columns would violate 1NF and fix the number of values in advance.
  6. False. NOT NULL on a foreign key enforces total participation of the child (the N side). Total participation of the parent — "every student must have an admission" — needs a trigger or assertion.

Lesson Summary

  • Seven steps: strong entities → relations; weak entities → relations with owner key + partial key; 1:1 → foreign key on the total side; 1:N → foreign key on the N side; M:N → new relation; multivalued attributes → new relation; n-ary → new relation.
  • Composite attributes become columns; derived attributes are omitted.
  • An M:N junction table's key is both foreign keys — plus a distinguishing attribute if the pair may recur.
  • Total participation on the parent side, cardinality bounds, and cross-table business rules cannot be mapped — they need triggers or assertions.
  • Watch for circular foreign-key references; break them with ALTER TABLE or a nullable column.
  • A schema produced by this algorithm from a good ER diagram is usually already close to 3NF.