The details that decide whether a model is right or wrong. Break attributes into their parts, then read participation constraints — when every row must join in, and when it's optional — through two guided activities.
This is the full written version of an interactive, slide-by-slide lesson deck used in teaching. Everything covered in the deck is below — open the interactive version to work through it with live diagrams, worked examples and a practice quiz where included.
A composite attribute is an attribute that can be broken down into smaller sub-attributes, each representing a distinct, meaningful piece of information. Unlike a simple attribute, it has internal structure.
Key Insight — When you need to query or process individual parts of an attribute (e.g., search by City, sort by LastName, extract PostCode for a report), model it as composite.
① Draw an ellipse for the composite attribute — use a thicker border to distinguish it
In SQL mapping, the composite parent is never a column. Only the leaf sub-attributes become columns in the table.
A PERSON entity commonly has two composite attributes: Name (FirstName, MiddleName, LastName) and Address (StreetName, City, PostCode). Simple attributes like DateOfBirth and PersonId remain flat ellipses.
SQL Impact: The PERSON table will NOT have "name" or "address" columns. Instead: first_name, middle_name, last_name, street_name, city, post_code.
Always ask: "Will I ever need to search, sort, or filter by a part of this attribute?" If yes → make it composite.
A participation constraint specifies whether ALL or only SOME entities in an entity set must participate in a relationship. It captures a business rule about obligation.
Think of it as a contract: "Every X must be linked to a Y" (total) vs. "Some X may be linked to a Y, but not required" (partial).
When every entity in the set must participate in at least one relationship instance, we draw a double line between the entity and the relationship diamond.
Also called mandatory or existence-dependent participation. Business rule language: "Every X must…", "All X are…", "X is required to…"
Business rule: "Every EMPLOYEE must belong to exactly one DEPARTMENT." → EMPLOYEE side gets a double line to the works_in relationship.
When only some entities need to participate in a relationship, we use the default single line. This is the optional constraint — entities may or may not be linked.
Also called optional participation. Business rule language: "Some X may…", "An X can but doesn't have to…", "X is not required to…"
Business rule: "Some EMPLOYEE may manage a DEPARTMENT (but most employees don't manage anything)." → single line from EMPLOYEE to manages.
Each BOOK has a BookId (key), a Title, a Price, and a full publication address comprising Building, StreetName, City, and Country.
2. Draw the ER diagram showing both entities with all their attributes using Chen's notation. Show composite sub-attributes branching correctly.
Look for attributes described with "comprising", "consisting of", or that have multiple parts. Each part that could be queried independently is a sub-attribute.
Draw the ER diagram segment showing LECTURER, MODULE, and DEPARTMENT with their teaches and assigned_to relationships. Apply the correct participation constraints (double or single lines) based on the 4 business rules above.
A composite attribute has sub-attributes — draw as an outer teal ellipse with smaller ellipses branching off it via lines.
Sub-attributes represent individually meaningful parts — e.g., City and PostCode from Address. You can query each part independently in SQL.
In SQL mapping, only the leaf sub-attributes become columns. The composite parent is never a column — it only exists in the ER diagram.
Total participation (══) = every entity MUST participate. The business rule says "must", "every", or "all". Maps to NOT NULL FK in SQL.
Partial participation (──) = some entities are optional. The rule says "may", "can", or "optional". The FK column allows NULL in SQL.
A composite attribute is one value split into parts — Name is First plus Last. A multivalued attribute is several separate values of the same kind — a customer with three phone numbers. They use different notation and map to the database differently: composites become multiple columns, multivalued attributes become a separate table.
An attribute whose value is calculated from other stored data rather than stored itself — age derived from date of birth, or order total derived from line items. It is drawn with a dashed ellipse, and is normally computed on read instead of being stored, so it cannot fall out of sync.
An attribute made up of multiple sub-attributes, each holding a distinct piece of information. Example: Address = StreetNumber + StreetName + City + PostCode.
An outer ellipse (the composite parent, drawn with a thicker teal border) with smaller sub-attribute ellipses connected to it by lines — like branches.
GuestName (FirstName, LastName), CheckInAddress (StreetName, Suburb, City, PostCode), or ContactDetails (PhoneNumber, Email).
To allow querying or processing individual parts — e.g., sorting by LastName, filtering by City, or extracting PostCode for delivery routing.
Composite: ONE value split into parts (Name = First + Last). Multivalued: MULTIPLE separate values ({PhoneNumber} = 021…, 09…). Different notations and SQL mappings.
Each sub-attribute becomes its own column. The composite parent itself does NOT become a column. E.g., Address → street_name, city, post_code columns.
A rule specifying whether ALL entities in an entity set (total participation) or just SOME (partial participation) must participate in at least one instance of a relationship.
Every entity instance MUST participate in at least one relationship instance. Drawn as a DOUBLE LINE (══) between the entity and the relationship diamond.
Some entity instances do NOT have to participate in any relationship instance. Drawn as a SINGLE LINE (──) — the default notation.
Total participation — drawn as a double line from ORDER to the places/belongs_to relationship diamond. Maps to NOT NULL FK in SQL.
Partial participation — drawn as a single line (default) from CUSTOMER to the relationship diamond. The FK column in ORDER allows NULL.
Total: key words are "must", "every", "all", "required", "always". Partial: key words are "may", "can", "optional", "might", "not necessarily".
The interactive deck adds live diagrams, step-by-step reveals and practice activities that this written version can't carry.