Entity relationship (ER) modelling
An ER model is a picture of a database plan showing entities, their attributes, and the relationships between them with their cardinality.
ER diagram symbols (Chen notation)
| Element | Symbol | Example |
|---|---|---|
| Entity | Rectangle | Customer, Product |
| Attribute | Oval | Name, Price |
| Key attribute | Oval with the name underlined | CustomerID |
| Relationship | Diamond | places, supplies |
| Connecting line | Line marked 1, M or N | Shows cardinality |
Types of relationship (cardinality)
| Type | Meaning | Example |
|---|---|---|
| One-to-one (1:1) | One record on each side | One manager manages one branch |
| One-to-many (1:M) | One record links to many on the other side | One customer places many orders |
| Many-to-many (M:N) | Many on both sides | Many students take many courses; solved with a linking table such as Enrolment |
Example ER model: a small shop
Attributes: Customer (CustomerID, Name, City); Order (OrderID, Date); Product (ProductCode, Name, Price); Supplier (SupplierID, Name, Phone).
Drawing an ER diagram
- 1List the entitieslook for the nouns in the case
- 2List each entity's attributes and underline the key
- 3Find the relationshipslook for the verbs: places, supplies, teaches
- 4★Mark the cardinality: 1:1, 1:M or M:N
- 5Resolve each M:N with a linking entity
- 6Convert to tableseach entity becomes a table
★ one-way, controls the pace
From ER diagram to tables
Each entity becomes a table and each attribute becomes a field. For a 1:M relationship, copy the primary key of the 'one' side into the 'many' side as a foreign key (CustomerID goes into Order). For an M:N relationship, create a linking table, e.g. Order_Line with OrderID, ProductCode and Quantity.
A Sri Lankan private hospital or bus company could model Patient-Appointment-Doctor or Route-Trip-Bus as entities before building its booking system, so that double bookings are blocked by the design itself.
Short answer: “What is an entity? Give an example.” A person, place, thing or event about which data is stored, e.g. Customer or Product.
Structured question: “Explain one-to-one, one-to-many and many-to-many relationships with examples.” 1:1 manager-branch; 1:M customer-orders; M:N students-courses, solved with a linking table.
Essay: “Draw an ER diagram for a small retail shop and explain its components.” Identify entities, attributes and keys, relationships and cardinality; draw the diagram; resolve M:N; show how it becomes tables.
Rectangle = thing, oval = detail, diamond = doing. Nouns become entities; verbs become relationships.