ISYS1055 Chap.5 The Seven-Step ER-to-Relational Mapping Process
The Seven-Step ER-to-Relational Mapping Process
Week 4's third outcome is to map an ER model to a relational database schema using a seven-step mapping process, and this is the chapter that makes it mechanical. It is the single most assessable procedure in ISYS1055: Task 2 of Assessment Task 1 is exactly this, the mapping question as the course words it requires the partially built schema to be shown at the end of every step (no marks are awarded to a final schema with no working), and Part B of the Database Design Project runs the same process on your own model. Get the order right, decide 1:1 relationships by participation rather than by cardinality, and the schema falls out with primary keys underlined and foreign keys asterisked.
What this chapter covers
- 01Step 1 - strong entity types: one relation per entity holding all its simple attributes; entity key becomes the relation key; composite attributes split into components
- 02Step 2 - weak entity types: relation holds its simple attributes plus the owner's primary key; owner key plus partial key form the composite primary key, and the owner key is also a foreign key
- 03Step 3 - 1:1 relationships, decided by participation not cardinality: mandatory on both sides means combine into one relation; mandatory on one side means the optional side is the parent and a copy of its key goes into the child; optional on both sides has no definitive direction
- 04Step 4 - 1:N: copy the primary key of the "one" side into the "many"-side relation as a foreign key, and relationship attributes travel with it
- 05Step 5 - M:N: create a new relation holding both primary keys; together they form its primary key and each is a foreign key; relationship attributes become ordinary attributes
- 06Step 6 - multi-valued attributes: create a new relation with the attribute plus the owner's primary key, both together forming the key
- 07Step 7 - higher-degree (ternary and above): one new relation holding all participating keys, which together form its primary key
- 08Recursive relationships as a special case of Step 4, and the assessment requirement to show the partially built schema at the end of every step
Map a bicycle-hire model through all seven steps, showing the schema after each
- +1Step 1, strong entity types. One relation each, holding the simple attributes, with the entity key as the relation key; the multi-valued paymentMethods is deliberately left out and handled at Step 6. Schema so far: Station(stationID, name, suburb, capacity) · Bike(bikeID, model, purchaseDate) · Rider(riderID, fullName, email).
- +1Step 2, weak entity types. ServiceJob takes its own simple attributes plus the owner's primary key, and the owner key together with the partial key forms the composite primary key, with the owner key also a foreign key: ServiceJob(bikeID*, jobNo, jobDate, notes), key (bikeID, jobNo).
- +1Step 3, 1:1 relationships - none in this model, so nothing is produced. Say so explicitly rather than skipping the line, because the question wording wants every step shown. Step 4, 1:N: "docked at" has Station on the one side, so a copy of its primary key goes into the many-side relation as a foreign key: Bike(bikeID, model, purchaseDate, stationID*).
- +1Step 5, M:N. "Favourite" is many-to-many, so it becomes a new relation holding both primary keys, which together form its primary key and each of which is a foreign key; the relationship's own attribute joins them as an ordinary attribute: Favourite(riderID*, stationID*, nominatedOn), key (riderID, stationID).
- +1Step 6, multi-valued attributes: paymentMethods becomes its own relation holding the attribute plus the owner's key, both together forming the primary key - StationPayment(stationID*, paymentMethod), key (stationID, paymentMethod). Step 7, higher-degree relationships: none, since every association here is binary. Final schema: Station(stationID, name, suburb, capacity) · Bike(bikeID, model, purchaseDate, stationID*) · Rider(riderID, fullName, email) · ServiceJob(bikeID*, jobNo, jobDate, notes) · Favourite(riderID*, stationID*, nominatedOn) · StationPayment(stationID*, paymentMethod).
Key terms
- Strong (regular) entity type
- An entity type that does not depend on another for its identity and has a primary key of its own. Step 1 turns each one into a relation holding all its simple attributes, splitting composite attributes into their components.
- Step 2 composite key
- The primary key produced when a weak entity is mapped: the owner entity's primary key plus the weak entity's partial key, together identifying the row. The owner's key is simultaneously a foreign key back to the owner relation.
- Participation-driven 1:1 mapping
- The Step 3 rule. Mandatory on both sides: combine the two entities into one relation and pick one of the original keys. Mandatory on one side only: the optionally participating entity is the parent, and a copy of its key goes into the mandatory (child) relation. Optional on both sides: no definitive direction, so choose the more central entity and the side that produces fewer nulls.
- Posting a key (Step 4)
- Copying the primary key of the entity on the "one" side of a 1:N relationship into the relation representing the "many" side, where it becomes a foreign key. Any attributes of the relationship travel into the same relation.
- Link relation (Step 5)
- The new relation created for a many-to-many relationship. It holds the primary key of each participant; together those attributes form its primary key, each is also a foreign key, and the relationship's own attributes become ordinary attributes of the new relation.
- Recursive relationship mapping
- A unary relationship where both ends are the same entity type. It obeys the ordinary rules once you decide which end plays the "one" role, so a 0 .. 1 to 0 .. * supervision is still 1:N and the key is copied into the same relation as a foreign key under a role name.
The Seven-Step ER-to-Relational Mapping Process FAQ
Why is a 1:1 relationship decided by participation rather than by cardinality?
Because the cardinality is the same on both sides, so it cannot tell you which relation should hold the foreign key. Participation can: if one entity participates mandatorily, every one of its rows will have a value, so putting the key there produces no nulls - which is why the mandatory side is the child and a copy of the optional side's key goes into it. When both sides are mandatory the two entities are effectively one thing and are combined; when both are optional there is no definitive direction and you justify a choice.
Do I really have to show every intermediate step?
Yes. The mapping question as the course words it - on the Week 4 lectorial's past-assignment slides and again on the Week 4 tute/lab sheet - states that no marks are awarded to the final schema if you do not show the partially built schema at the end of each step. Practically this means writing the growing schema seven times, including the steps that add nothing to your particular model - record those as "no 1:1 relationships in this model" rather than leaving a gap.
Where do multi-valued attributes go?
Never into the entity's own relation. Step 6 gives each multi-valued attribute its own relation containing the attribute plus a copy of the owner's primary key, and the two together form that relation's primary key. Leaving a multi-valued attribute in place, or spreading it across numbered columns, is the repeating group that Week 9's 1NF work then has to undo.
What happens to attributes that belong to a relationship?
They travel with the key. In a 1:N mapping they move into the many-side relation alongside the posted foreign key; in an M:N mapping they become ordinary attributes of the new link relation; in a higher-degree mapping they join the relation holding all the participating keys. They never stay behind on an entity that does not determine them.
Assessment move
Learn the steps as an ordered checklist and rehearse them against models you did not draw, because in the assignment you will be mapping someone else's diagram. Write the seven step names on one line each, then take three small models and map each one end to end, physically re-writing the whole schema after every step until the rhythm is automatic. Give the 1:1 case extra time - it is the step students most often decide by cardinality and lose marks on - and check every result against the notation the course marks: primary keys first and underlined, foreign keys asterisked. When you finish a mapping, validate it with the Week 9 and 10 normalisation checks; the course's own point is that not every functional dependency is captured in an ER model, so a mapped schema is occasionally not in 3NF.
Working through The Seven-Step ER-to-Relational Mapping Process in ISYS1055? Sia is AskSia’s AI Information Technology tutor — ask any ISYS1055 The Seven-Step ER-to-Relational Mapping Process question and get a clear, step-by-step explanation grounded in how ISYS1055 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
