The ER Diagram

Participation. Whether every entity of a type must take part in the relationship. Total: every order is placed by some user. Partial: a user may have placed no orders at all.

Weak entity. An entity that cannot be identified by its own attributes, only together with the entity it depends on. A line of an order, identified by its order and its menu item, is the classic example.

Two notations

Chen's notation

The notation textbooks name after Chen draws:

Chen's notation is good for thinking: every attribute, key and relationship is a separate shape, so the structure of the data is visible at a glance. It is poor for a large design, because the ellipses soon fill the page.

Crow's foot notation

The other common notation draws each entity as a box listing its attributes, and puts the cardinality and participation at the ends of the relationship line:

The circle means zero, so the participation is optional; the bar means one; the three-pronged crow's foot means many. Read it the way you read a UML multiplicity: from one entity, along the line, to the symbol at the far end. This is the notation of most database design tools, and it fits a design with every column in it.

Resolving a many-to-many relationship

A relational database cannot store a many-to-many relationship directly: a column holds one value, so neither the orders table nor the menu items table can hold "all the items of this order" or "all the orders of this item". The relationship becomes a table of its own, an associative entity:

1. Make a new entity for the relationship: order_items.
2. Give it a foreign key to each side: order_id and menu_item_id.
3. Make the pair its primary key, if each pair may occur only once: one line per item per order.
4. Move the relationship's own attributes into it: quantity, and the unit_price_paise copied at the moment of ordering.

The one M:N relationship becomes two 1:N relationships: an order has many order items, and a menu item appears in many order items. The line of an order is also exactly the weak entity described above.

Conceptual, logical, physical

The same data can be designed at three levels, and the worked team drew two of them:

• A conceptual model shows the entities, their relationships and the important attributes, in the language of the problem. It ignores tables and types. Chen's notation suits it.
• A logical model shows every attribute, every key and every relationship in the form the database will need, with many-to-many relationships resolved. Crow's foot suits it.
• A physical model is the schema itself: the SQL that builds the tables, with exact types, constraints and indexes (Chapter 29).

The worked diagrams

The conceptual model, in Chen's notation

Figure 24.1 The worked conceptual model in Chen's notation

@startchen er-chen
top to bottom direction
entity USER {
  Id <<key>>
  Name
}
entity ORDER {
  Id <<key>>
  Slot
  Total <<derived>>
}
entity MENU_ITEM {
  Id <<key>>
  Price
}
relationship PLACES {
}
relationship CONTAINS {
  Quantity
  UnitPrice
}
USER -1- PLACES
PLACES =N= ORDER
ORDER =M= CONTAINS
CONTAINS -N- MENU_ITEM
@endchen

Three entities and two relationships. A USER PLACES many ORDERs, 1:N. An ORDER CONTAINS many MENU_ITEMs, and a menu item is in many orders, M:N, with the quantity and the unit price as attributes of CONTAINS, because they belong to the pair.

Keys are underlined: each Id. Total, dashed, is derived: it can be computed from the quantities and unit prices of the order's items.

Participation. ORDER's two lines are drawn thick, because its participation in both relationships is total: every order is placed by a user and contains at least one item. USER and MENU_ITEM take part partially: a user may never order, and an item may never be ordered. The textbook symbol for total participation is a double line. PlantUML draws a thick line instead, from the = in PLACES =N= ORDER; say so beside your diagram if you draw it this way, since an examiner may look for the double line.

What is left out. The conceptual model shows the canteen's data as the canteen would describe it, so the table of signed-in sessions, which exists only because the application needs it, does not appear, and each entity shows only the attributes that make the relationships clear. Every attribute is in the logical model below.

The logical model, in crow's foot notation

Figure 24.2 The worked logical model in crow's foot notation: every column of the schema

@startuml er
!pragma layout smetana
top to bottom direction
hide circle
skinparam linetype ortho

entity users {
  * id : INT <<PK>>
  --
  * name : VARCHAR(80)
  * email : VARCHAR(120) <<unique>>
  * password_hash : VARCHAR(200)
  * role : ENUM
  * is_active : BOOLEAN
  * created_at : DATETIME
}

entity sessions {
  * id : CHAR(64) <<PK>>
  --
  * user_id : INT <<FK>>
  * created_at : DATETIME
  * expires_at : DATETIME
}

entity orders {
  * id : INT <<PK>>
  --
  * user_id : INT <<FK>>
  * pickup_date : DATE
  * pickup_slot : TIME
  * status : ENUM
  * total_paise : INT
  * created_at : DATETIME
  * updated_at : DATETIME
}

entity order_items {
  * order_id : INT <<PK, FK>>
  * menu_item_id : INT <<PK, FK>>
  --
  * quantity : TINYINT
  * unit_price_paise : INT
}

entity menu_items {
  * id : INT <<PK>>
  --
  * name : VARCHAR(60) <<unique>>
  * category : ENUM
  * price_paise : INT
  * is_veg : BOOLEAN
  * is_available : BOOLEAN
  * stock_left : INT
}

users ||--o{ sessions
users ||--o{ orders
orders ||--|{ order_items
order_items }o--|| menu_items
@enduml

Five tables, every column of the schema, and the M:N relationship already resolved into order_items. Read each relationship both ways, from one table along the line to the far end:

• users to sessions: a user has zero or many sessions; a session belongs to exactly one user.
• users to orders: a user has zero or many orders; an order belongs to exactly one user.
• orders to order_items: an order has one or many items, never none; an item line belongs to exactly one order.
• order_items to menu_items: a menu item appears in zero or many item lines; each line is for exactly one menu item.

Inside the boxes, PlantUML's * before a column marks it mandatory: it must have a value, which is NOT NULL in SQL, and every column here is. «PK» and «FK» mark the primary and foreign keys; order_items has a composite primary key made of its two foreign keys, which is why each item can appear only once in an order. «unique» marks the other candidate keys: no two users share an email, and no two menu items share a name.

The source, line by line

• entity users { ... } draws an entity box; -- inside it draws the line that separates the key from the other columns.
• * id : INT <<PK>> is a mandatory column with a keyword.
• users ||--o{ sessions is a relationship: || at the users end means exactly one, o{ at the sessions end means zero or many. |{ would be one or many, and o| zero or one.
• hide circle removes PlantUML's own lettered circle from each box, and skinparam linetype ortho draws the lines with right angles.
• In the Chen source, entity, relationship, <<key>> and <<derived>> draw the shapes, and -1-, =N= put the cardinality on each line, = for total participation.

Checking it against the class diagram

Chapter 19's fifth check, class against ER, and one of Chapter 21's rules, meet here:

The check runs from the class diagram to the ER diagram: every attribute of every class must be stored. The reverse need not hold, and the table shows the one kind of column that is stored without being in the domain model.

The composition rows are worth noticing. The association from Order to User has no ON DELETE CASCADE, and that is deliberate: a user's orders are the canteen's records of what it sold, so the application never deletes a user. An account is switched off instead, by setting is_active to false, which sign-in and every session check honour. The first release has no screen for that; it is done in the database.

Do this for your project

1. List the entities from your class diagram's classes, and give each a primary key.
2. List each entity's attributes; mark composite, multivalued and derived ones, and every candidate key.
3. Draw each relationship, and give it a cardinality ratio and a participation at both ends.
4. Resolve every many-to-many relationship into an associative entity, with its own attributes.
5. Draw a conceptual model in Chen's notation for your report, and a logical model with every column in crow's foot notation for building the database.
6. Check it against your class diagram in both directions, and write down every difference with its reason.

Mistakes that cost marks

A many-to-many relationship left unresolved in a logical model: it cannot become a table.

A relationship's attributes placed in an entity, such as quantity in the menu item.

No primary key, or a name used as the key when two things can share a name.

Cardinality written at the wrong end. Read from one entity across the line to the far end.

Participation missing. "Every order has at least one item" is a rule the database should enforce, and it starts in the diagram.

Foreign keys without the line, or lines without a foreign key: the diagram and the tables must say the same thing.

Quick revision

• Entity type (rectangle), attribute (ellipse), relationship (diamond), in Chen's notation; key underlined, multivalued double ellipse, derived dashed ellipse, weak entity double rectangle.
• Keys: candidate, primary, unique, composite, foreign.
• Cardinality ratio: 1:1, 1:N, M:N. Participation: total (every entity takes part; double line) or partial.
• Crow's foot: bar = one, circle = zero, crow's foot = many; read from one entity to the far end.
• Resolve M:N into an associative entity with a foreign key to each side and the relationship's attributes.
• Conceptual (Chen), logical (crow's foot, every column), physical (the SQL schema).

Questions you must be able to answer

1. What does an ER diagram show? The entity types a system stores data about, their attributes and keys, and the relationships between them, with each relationship's cardinality ratio and participation. It is the design from which the database tables are built.

2. Distinguish cardinality from participation, with an example of each from the worked diagram. Cardinality says how many entities of one type can relate to one of the other: a user places many orders, and each order is placed by one user, 1:N. Participation says whether every entity must take part: every order must be placed by some user, total; a user need not place any order, partial.

3. How is a many-to-many relationship turned into tables? Use orders and menu items. It becomes an associative table, order_items, with a foreign key to each side, order_id and menu_item_id, which together form its primary key, and with the relationship's own attributes, the quantity and the unit price at the time of ordering. The M:N relationship becomes two 1:N relationships.

4. What is a derived attribute? Give the worked example. An attribute whose value can be computed from others. An order's total is the sum of its items' quantities multiplied by their unit prices; Chen's notation draws it as a dashed ellipse.

5. What is a weak entity? An entity that cannot be identified by its own attributes alone, only together with the entity it depends on. An order's line is identified by its order and its menu item together, and has no identity without the order.

6. Why are the canteen's users never deleted from the database? Because their orders are the canteen's records of what it sold, and deleting a user would mean deleting or orphaning those orders. So the orders table does not cascade deletes from users, the application has no way to delete one, and an account is switched off instead by setting is_active to false, which sign-in and every session check honour.