ISYS1055 Chap.4 Refining an ER Model: Design Choices and Weak Entities
Refining an ER Model: Design Choices and Weak Entities
Week 4's stated outcomes are to refine an ER model by applying better design choices and to improve a model so it accommodates additional requirements presented by a client, so this is the iteration chapter rather than a new-concepts chapter. Database design with ER modelling is explicitly an iterative process: you present a first model, the client adds a requirement, and the model changes. This is also the heaviest single item in Assessment Task 1 - Task 3, Optimise ER Model, carries 6 of the 20 rubric points - and the same skill returns in Part B of the Database Design Project when your first conceptual model meets the real shape of a public dataset.
What this chapter covers
- 01Why ER design is iterative: the first model is a draft to be reviewed against the operation and its business rules
- 02The four refinement moves - introduce a new entity, convert an attribute into an entity, downgrade an entity into an attribute, convert a relationship into an entity
- 03Attribute or entity? Model something as an entity only if it is primary - of direct interest to the enterprise
- 04Entity or relationship? Test whether the association has attributes of its own and which choice represents the facts more accurately
- 05Weak entity types in UML: {Partial Key}, the identifying association, mandatory participation, and the owner key plus partial key as the identifier
- 06Self-association for recursive relationships, with a role name at each end (for example predecessor and follow-up)
- 07Connection traps: the fan trap, where two 1:N relationships fan out from the same entity and the association between the two many ends cannot be resolved - and the re-arrangement that fixes it
- 08Working a new client requirement through the model without breaking what was already signed off
Refine a first-draft podcast model: attribute to entity, weak entity, and a requirement that forces a promotion
- +1(i) first move: convert an attribute into an entity. episodeTitles[1..N] is multi-valued and the client now wants several facts per episode, which an attribute cannot carry. Promote it to its own entity type associated with Show. This is refinement move 2, and the trigger is always the same - an attribute that has acquired attributes of its own.
- +1(i) continued: recognise that the new entity is weak. An episode number is unique only within its show, so Episode cannot be identified on its own; it depends on Show for its identity. In UML: Episode(seasonNo {Partial Key}, episodeNo {Partial Key}, releaseDate, durationMin), joined to Show by an identifying association, with Episode participating mandatorily (1 .. 1 at the Show end - every episode belongs to exactly one show) and Show 0 .. * at the Episode end. The identifier is the owner's key plus the partial key.
- +1(ii) second move: convert a relationship into an entity. A relationship stores only the current state, so it cannot hold a history of several re-publications of the same episode with different dates and notes. Promote it: Republication(publishedAt, editNote) becomes an entity of its own, associated with Episode as 1 .. 1 - 0 .. *. The generalisable rule the course states is that when an association needs its own identity, its own attributes, or has to be recorded more than once between the same pair of entities, it stops being a relationship and becomes an entity.
- +1(iii) self-association. "Is a follow-up to" joins Episode to itself, so it is a recursive relationship drawn in UML as a self-association on the Episode box with a role name at each end - follow-up and predecessor - and multiplicity 0 .. 1 at the predecessor end and 0 .. * at the follow-up end, since not every episode has a predecessor and one episode may be followed by several. Nothing else in the model changes.
Key terms
- Refinement move
- One of the four changes the course names for improving a draft ER model: introduce a new entity, convert an attribute into an entity, downgrade an entity into an attribute of another entity, or convert a relationship into a new entity.
- Primary (of direct interest)
- The rule of thumb for the attribute-or-entity decision: model something as an entity only if it is of direct interest to the enterprise. A home address is an attribute of an employee for most businesses, but a house is an entity for a real-estate business.
- Weak entity type
- An entity type with no key of its own, identified only in combination with an owner entity. It carries a partial key, tagged {Partial Key}, and participates mandatorily in the identifying association; its full identifier is the owner's primary key plus the partial key.
- Self-association
- The UML form of a recursive (unary) relationship, where both ends are the same entity type. Each end carries a role name so the two directions can be told apart - supervisor and supervised, predecessor and follow-up.
- Connection trap
- A defect where associations among entities are misrepresented, so a path exists through the model but produces the wrong or an indeterminate answer.
- Fan trap
- The connection trap this course develops: two or more 1:N relationships fan out from the same entity, and the required association between the two "many" ends cannot be resolved. The fix is to re-arrange the relationships so the real association is direct.
Refining an ER Model: Design Choices and Weak Entities FAQ
How do I know whether something should be an entity or an attribute?
Ask whether it is primary - of direct interest to the enterprise in its own right - and whether it has acquired facts of its own. A supplier's name sitting on a product row is an attribute until the business wants the supplier's contact details, rating and history, at which point it has become an entity and the product simply references it. A multi-valued attribute that keeps growing is the strongest single signal.
When should a relationship become an entity?
When it needs its own identity, carries its own attributes, or has to be recorded more than once between the same pair of entities. A borrowing relationship between a member and a tool holds only the current loan; the moment the business wants every past loan with its own date, it is a history, and a relationship cannot store a history.
What exactly goes wrong in a fan trap?
Two 1:N relationships fan out from one entity, and the question you actually need to answer sits between the two many ends, where no direct path exists. If students register in a program and a program contains courses, the model cannot say which of that program's courses a given student takes - the association has been misrepresented. The fix is to re-arrange the relationships so the student-to-course association is drawn directly.
Is a weak entity the same as a table with a composite key?
Not quite - the weak entity is the conceptual idea, and the composite key is what it becomes after mapping. In the ER model, a weak entity has no key of its own and cannot be identified without its owner; when Step 2 of the seven-step mapping turns it into a relation, the owner's primary key is added and the owner key plus the partial key together form the composite primary key.
Assessment move
Practise this chapter as a two-column exercise rather than as reading: on the left, the requirement that arrived; on the right, the refinement move it forces and the diagram change it produces. Build a small model of something you know, then invent three new client requirements for it - one that adds an entity, one that promotes an attribute, one that demands a history - and redraw it each time, keeping every version so you can see the iteration. Check each redraw for a fan trap by asking the awkward question the model is supposed to answer and tracing whether a path actually resolves it. This is the material the Week 5 quiz covers alongside Week 3, and it is the 6-mark optimise task in Assessment Task 1, so the reasoning is worth as much as the diagram.
Working through Refining an ER Model: Design Choices and Weak Entities in ISYS1055? Sia is AskSia’s AI Information Technology tutor — ask any ISYS1055 Refining an ER Model: Design Choices and Weak Entities 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.
