IS5413 Chap.6 Mapping an Enhanced ER Diagram to Relations
Mapping an Enhanced ER Diagram to Relations
One word of vocabulary before the rules
The course flags this explicitly and it is worth taking seriously. A relation in the relational model is a table with a name, rows and columns. A relationship in the entity relationship model is an association between entity types. The two words are not interchangeable, and a sentence that swaps them reads as a misunderstanding even when the schema underneath is correct.
A relation qualifies as one only if it has a unique name, every attribute value is atomic, every row is unique, attribute names are unique within it, and the order of rows and columns carries no meaning.
The rule set, construct by construct
Every regular entity type becomes a relation, with simple attributes mapping straight across.
A composite attribute contributes only its component parts, so an address written as street, city and state becomes three columns and never one. A multivalued attribute leaves and becomes its own relation carrying a foreign key from the owning entity type.
A weak entity type becomes a relation whose primary key is its partial identifier together with the primary key of the identifying owner, and that foreign key must not be allowed to hold a null.
Binary relationships take their shape from cardinality: one to many puts the key of the one side into the many side, many to many creates a new relation keyed on the pair of foreign keys, and one to one sends the key of the compulsory side across to the optional one.
Unary relationships fold back, with one to many becoming a recursive foreign key inside the same relation and many to many becoming a second relation holding two copies of the original key.
A supertype and each of its subtypes become one relation each, with the subtype relations repeating the supertype key.
The one row that changes the key rather than the columns
An associative entity with no identifier of its own takes the combination of participant keys as its primary key, exactly like a plain many to many relationship.
An associative entity that already carries a business identifier is different: that identifier becomes the primary key and the participant keys are demoted to ordinary foreign keys beside it. The published warning attached to this case is the important part, which is that the default combination key may not be unique.
A shipment involving the same customer and the same vendor can happen more than once, so keying on the pair would silently lose rows.
Two constraints every conversion has to satisfy
Entity integrity says no part of a primary key may be null, so every row remains identifiable.
Referential integrity says every foreign key has to point at a primary key value that really exists in the parent relation or be null where nulls are permitted.
Referential integrity also brings delete rules with it: restrict refuses to delete a parent while dependent rows exist, cascade deletes the dependents with it, and set to null empties the foreign key in the dependents, which is not available where the dependent is a weak entity because its key would lose part of itself.
What this chapter covers
- 01
Relation against relationship, and the five properties of a relation
- 02
Regular entities, composite and multivalued attributes
- 03
Weak entities and their composite keys
- 04
Foreign key placement in each binary cardinality
- 05
Unary and ternary relationships
- 06
Associative entities with and without their own identifier
- 07
Entity integrity, referential integrity and the delete rules
Worked example · free
Convert a small diagram including its awkward parts
- 3Convert the section and plot entity types, handling the soil treatments.
- 3Convert the gardener entity type including the mentoring relationship.
- 4Convert the joint tending relationship and justify its key.
Key terms
- Relation
- A table carrying a name nothing else shares, atomic values, no repeated rows, no repeated column names, and no significance in the order of either.
- Primary Key
- The chosen unique identifier of a relation. No part of it may be null, which is the rule the course calls entity integrity.
- Foreign Key
- An attribute that lets a dependent relation refer to its parent by carrying the parent's primary key value, implementing a relationship in the relational model.
- Entity Integrity
- The constraint that no primary key attribute may hold a null value, so that every row in a relation can be identified.
- Recursive Foreign Key
- A foreign key inside the same relation it refers to, used to implement a one to many unary relationship such as one member of staff supervising another.
- Cascade Delete
- A referential integrity rule that automatically removes dependent rows when the parent row they refer to is deleted, as opposed to restricting or nulling.
Mapping an Enhanced ER Diagram to Relations FAQ
Where does the foreign key go in a one to one relationship?
On the side whose participation is optional, carrying the key of the side whose participation is compulsory. Putting it on the mandatory side would force every row there to hold a value that may not exist, producing a column full of nulls in a place where nulls should not be. When both sides are mandatory or both optional the choice is genuinely open, and a stated reason is what earns the mark.
Why does a multivalued attribute have to become its own relation?
Because a relation requires every attribute value to be atomic, and a set of values is not atomic. Leaving a list in a column means the table is not a relation at all, which is why the course says all relations are in first normal form by definition. The new relation takes a foreign key from the owner, producing a one to many relationship between the original entity type and the values it used to hold.
What happens if I forget that an associative entity already has an identifier?
You will key it on the pair of foreign keys, and the schema will silently refuse to hold two occurrences of the same pair. Where the same two participants can be involved more than once, and a shipment or a booking usually can, that is data loss rather than a style issue. The published note warns that the default combination key may not be unique, and this is what the warning means in practice.
Exam move
Convert three diagrams into relations without looking at the rule table, then check each against it and mark which rule you applied wrongly. Keep a running tally of your own errors by rule, because almost everyone has two favourites rather than a general weakness. Finish each conversion by counting foreign keys against relationship lines, since that arithmetic catches both an omitted relationship and an invented one.
Working through Mapping an Enhanced ER Diagram to Relations in IS5413? Sia is AskSia’s AI Information Technology tutor — ask any IS5413 Mapping an Enhanced ER Diagram to Relations question and get a clear, step-by-step explanation grounded in how IS5413 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
