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
- State the seven-step mapping algorithm from memory.
- Map strong entities, weak entities, and all three binary relationship types.
- Map multivalued attributes and n-ary relationships.
- Justify the choice of side for a 1:1 foreign key.
- 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
Eas the primary key ofR. If the chosen key is composite, the set of its simple attributes together forms the primary key. - Other candidate keys become
UNIQUEconstraints. - 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:
-
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.
-
Cross-reference (relationship) relation. Create a separate relation holding both keys, exactly as for M:N — but with a
UNIQUEconstraint 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});ROOMnumbered 1 upward within a hotel, withTypeandRate;GUEST(GuestID, Name); aBOOKSrelationship betweenGUESTandROOMwithCheckInandCheckOut, 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 TABLEfails. - 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
- State the seven steps of the mapping algorithm.
- What happens to a composite attribute? A derived attribute?
- What is the primary key of the relation produced from a weak entity type?
- On which side of a 1:N relationship does the foreign key go, and why?
- Why can an M:N relationship not be mapped with a foreign key?
- What is the primary key of the relation produced from a multivalued attribute?
- Name one ER constraint that cannot be mapped to DDL.
B. Mapping problems — give full DDL for each
DEPARTMENT(Dno, Dname)1:NEMPLOYEE(Eno, Ename), employee total.STUDENTM:NSOCIETYwithDateofenrollment.PERSON(PID, Name)1:1PASSPORT(PassportNo, Expiry), passport total.COURSE(SID, Cname)with weak entitySECTIONnumbered within the course, havingRoomandTiming.EMPLOYEEwith multivalued{Skill}and compositeName(First, Last).EMPLOYEErecursive 1:NSUPERVISESwith roles supervisor / supervisee.- Ternary
DOCTOR–PATIENT–DRUG(PRESCRIBES) withDosageandDate. - The full hospital diagram of Unit 2, Lesson 4 (wards, beds, patients, doctors).
- The full airline diagram from Unit 2 exercise 11.
C. Analytical
- For the college schema, write the trigger (in pseudocode or SQL) that would enforce the total participation of
STUDENTinTAKES_ADMISSION. What events must it fire on? - 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.
- Explain when the merged-relation approach to a 1:1 relationship is correct, and give a case where it would be wrong.
- Show that mapping a ternary relationship as three binary relationships loses information, using concrete tuples.
- Take the
BORROWSexample. Explain precisely why(MemberID, ISBN)is an insufficient primary key, and construct the two rows that would collide. - Compare the four EER specialisation mapping options for
STUDENTspecialised intoUGandPG, and recommend one with justification. - 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
- A derived attribute becomes a column in the relation.
- Every relationship type becomes a relation.
- A weak entity's relation has a composite primary key.
- The foreign key for a 1:N relationship may be placed on either side.
- A multivalued attribute becomes several columns.
- Total participation is always enforceable with a
NOT NULLforeign key.
Answers to Section D
- False. It is omitted and computed on demand; storing it creates redundancy that goes stale.
- False. Only M:N and n-ary relationships become relations. 1:1 and 1:N become foreign keys.
- True. The owner's primary key together with the partial key.
- False. It must go on the N side, where each entity participates exactly once and so has exactly one value to store.
- False. It becomes a separate relation — several columns would violate 1NF and fix the number of values in advance.
- False.
NOT NULLon 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 TABLEor a nullable column. - A schema produced by this algorithm from a good ER diagram is usually already close to 3NF.