ERD Cardinalities

Cardinalities and ERD Reading Guide

• Types of relationships in databases

  • Binary relationships: between two entities (e.g., Vendor and PurchaseOrder).

  • Ternary relationships: involve three or more entities; they exist in theory, but this course only uses binary relationships. Example given: a scenario where a Vendor, PurchaseOrder, and Warehouse intersect for a delivery, though you will not see a ternary relationship in this class.

  • Binary relationship examples from the lecture: a PurchaseOrder relates to a Vendor, a Delivery relates to a Warehouse, and so on.

• Cardinalities: what they are and why they matter

  • Cardinalities tell us how many times two tables/entities can be related to each other in a relationship.

  • They define a numerical constraint for a given pair of entities.

  • In this course we simplify to two values: minimum and maximum.

  • The minimum cardinality is the lower bound (0 or 1 in this course).

  - The maximum cardinality is the upper bound (1 or many). In practice we use: 1 or many, where many means more than one (often denoted as

  • The terms “optional” vs “mandatory” refer to the minimum:

  • Minimum 0 means optional (the entity may or may not participate in the relationship).

  • Minimum 1 means mandatory (it must participate).

  • The maximum options in this simplified course are: 1 or many (many means more than one).

• Cardinality notation and how to draw it

  • In this course, you draw only three symbols on the relationship lines:

  • 0 (zero participation)

  • 1 (one participation)

  • many (more than one; crow’s feet notation is used to signify many at the far end)

  • The minimum (0 or 1) is placed near the diamond symbol on the line, and the maximum (1 or many) is placed near the entity box.

  • Crow’s feet notation is referred to as the “many” side of a relationship; the small line next to it is the crow’s feet symbol.

  • Example notations:

  • Optional one: $ext{min}=0, ext{max}=1$

  • Optional many: $ext{min}=0, ext{max}= ext{many} \ ( ext{often represented as } \infty)$

  • Mandatory one: $ext{min}=1, ext{max}=1$

  • Mandatory many: $ext{min}=1, ext{max}= ext{many} \ ( ext{often } \infty)$

  • Common binary relationship shapes in the notes:

  • 1 to 1 (1:1)

  • 1 to many (1:N)

  • many to many (M:N)

• Quick rules of thumb for reading the scenario (no assumptions allowed)

  • Read sentences carefully to identify the two entities involved in a relationship.

  • Look for phrases that indicate quantity (one, many, at least, exactly, delivered to, etc.).

  • Identify the direction of the relationship and which side is 1 vs many.

  • If a sentence mentions an instance or a specific event, use that to determine minimum/maximum constraints.

  • The instructor emphasizes avoiding assumptions; rely on explicit wording and business logic only after identifying what is stated.

  • For some relationships the scenario explicitly gives min/max; for others you may need to infer using common sense and business rules, but the course often requires accepting a stated minimum (e.g., a PO cannot exist without at least one item) as given unless the text says otherwise.

• Key terms defined in the lecture

  • Instance: a single row in an entity (e.g., one Vendor, one PurchaseOrder). In the lecture, instance = row.

  • Entity box: the table in the ERD (e.g., Vendor, PurchaseOrder, Warehouse, Inventory).

  • Attribute: a property of an entity (not the focus of the cardinality discussion here, but used for quantities like inventory quantity).

  • Schema on write: the database must be fully designed before implementation; changes later are painful.

  • Schema on read: not the focus of this course; decisions can be deferred to querying time in some other contexts.

  • Notation: the diamond (min) and the box (max) indicate the minimum and maximum cardinalities, respectively; the crow’s feet indicate the many side.

• Scenario entities covered in the lecture (high-level overview)

  • Vendors, PurchaseOrders, Deliveries (a concept used to connect POs and Warehouses), Warehouses

  • Inventory and Raw Materials (inventory items, including non-unique items like batch quantities)

  • Buyers (Employees who place POs), Receiving Employees (Employees who receive goods)

  • Cashiers, Checks/Payments, Banks, Cash Accounts

  • Vendors offer inventory items (noted as a many-to-many scenario in this course, although often modeled as a true M:N in more advanced coursework)

  • Initial setup concepts: space in warehouses for inventory, not-yet-ordered inventory that is proposed or offered by vendors, and the relationship to warehouses.

  • Notion of uniqueness: not unique vs unique inventory (examples: non-unique consumer goods vs unique serialized items like VINs).

• Notable distinctions emphasized in the lecture

  • Not unique inventory: many items can share the same item number/UPC; common for consumer goods (e.g., Dell laptops as a generic SKU).

  • Unique inventory: serialized items or uniquely identified assets (e.g., VIN on a car, serialized machines, software licenses with product codes).

  • For this course, inventory items are treated as not unique unless explicitly stated; the instructor notes that some real-world systems require treating unique items differently.

• Reading through the provided scenario (example lines and the cardinalities derived)

  • “Each delivery is made to any one of the three locations” → A purchase order/delivery is associated with exactly one Warehouse (min 1, max 1 for that link). Conceptually, a single delivery targets one warehouse; a PO can have multiple deliveries to different warehouses in some extended models, but this scenario emphasizes one delivery per warehouse per PO.

  • “Raw materials inventory consists of parts used to build robot kits, electronic components… These inventory items are not unique” → Inventory items share a common item number; not unique inventory implies that one item number can represent multiple physical units.

  • “During initial setup, warehouse space is set aside for items” → Each Warehouse has room for at least one product; every inventory item is stored in exactly one warehouse (one-to-many from Warehouse to Inventory).

  • “Every warehouse has room set aside for at least one product” → Confirms One Warehouse → One or more Inventory items (1:N, minimum 1 on the Inventory side for each Warehouse).

  • “To acquire inventory, one of the buyers places a purchase order” → An Employee (Buyer) creates PurchaseOrders; A PurchaseOrder is completed by exactly one Employee; An Employee can create zero or more PurchaseOrders (0..N → 1 relationship).

  • “Purchase order is placed with an approved vendor” → Each PurchaseOrder is associated with exactly one Vendor; A Vendor can be associated with zero or many PurchaseOrders (0..N → 1).

  • “Inventory items are offered by at least one Vendor” and “A Vendor offers at least one InventoryItem” → Inventory and Vendor relationship is many-to-many in nature; in this course it’s treated as M:N although sometimes simplified for teaching.

  • “Goods are received… a receiving employee accepts, counts, and counts the items into inventory” → A PurchaseOrder is received by exactly one ReceivingEmployee; A ReceivingEmployee can handle many Receipts/POs (0..N → 1).

  • “A purchase order orders at a maximum many inventory items; minimum not explicitly stated” → The PO can include many InventoryItems; Items may be added to Inventory, and each item may be included in multiple POs in a real system (the course notes acknowledge M:N relationships here and sometimes simplify).

  • “All vendors ship complete orders only” and “each shipment contains one PurchaseOrder” → In this scenario, a shipment is tied to a single PurchaseOrder; a PurchaseOrder yields a single shipment (1:1 for the PO–Shipment pair in this context); shipments are not created until all items are included in the PO’s shipment (conceptually, this enforces the complete shipment rule).

  • “Vendor includes a vendor invoice with every shipment” → The scenario mentions invoices but weights nothing to derive a cardinality on that; it’s a business process note rather than a structural constraint.

  • “Vendors recorded when they offer inventory” → Vendors are captured in the system when they offer items (not necessarily only when they are used); The word “offer” is a trigger for the vendor’s presence in the database in this scenario.

  • “Bank accounts and cash accounts” → A cash account belongs to a single bank; a bank can have many cash accounts. A payment (check) is drawn from one cash account; Each payment is assigned to one vendor; A vendor may receive multiple payments.

  • “Cashiers prepare electronic checks… and write a single check to each vendor for the total balance due at the end of the week” → Each Check is issued to one vendor; A given check is associated with a single vendor and a single cash account; You can have multiple checks to different vendors on the same payday; A single cash account can be used to pay multiple vendors; A single vendor can receive multiple checks across different weeks.

  • “An eCheck is created and electronically signed by a single cashier, but depending on the amount it may be reviewed by one or more cashiers” → Each payment can involve one or more cashiers; Each eCheck is created by one cashier, but there is a potential multi-cashier review process.

  • “Employees are recorded when they are hired” → This provides a business rule for who can perform actions (e.g., onboarding, PO creation, receiving goods, writing checks). Onboarding day typically has zero PO activity; over time they might write many POs.

  • “On the first day of work, an employee is onboarded and then becomes a donor of future transactions” → Supports the idea that for a given employee, the minimum number of related PO records at onboarding is zero, but over time it can be many.

• Summary of the practical implications and modeling notes

  • The cardinalities guide how you would draw the ERD: where 1 sits vs where many sits, and how to indicate optional vs mandatory participation.

  • The course emphasizes starting with reading and identifying the two entities involved in each sentence that describes a relationship, then mapping min/max constraints based on explicit wording.

  • There is emphasis on validating cardinalities against typical business processes (e.g., a purchase order exists only if it has at least one item; a purchase order is delivered in a fully complete shipment; a warehouse has at least one product space).

  • The instructor notes that some real-world cardinalities (like many-to-many without a linking table) are glossed over for Level 1 DB study; they will be refined in more advanced courses.

• Important formulas and concepts to remember (LaTeX)

  • Cardinality constraints between two entities can be summarized as a pair (min, max):

  • Optional one: $ext{min}=0, ext{max}=1$

  • Optional many: $ext{min}=0, ext{max}= ext{infty}$

  • Mandatory one: $ext{min}=1, ext{max}=1$

  • Mandatory many: $ext{min}=1, ext{max}= ext{infty}$

  • Useful notations:

  • 1:N relationship indicates one side has exactly one and the other side can have many.

  • M:N relationship indicates that the two entities can relate in many ways to each other; in practice this is implemented with a linking table.

  • In this course, the words are often expressed with phrases like:

  • “one and only one” corresponds to $ext{min}=1, ext{max}=1$

  • “at least one” corresponds to $ext{min}=1, ext{max}= ext{infty}$

  • “delivered to one of the three locations” indicates a single target location per delivery (min 1, max 1 for that link), with a constrained set of locations.

• Practical tips for exam preparation

  • Be able to identify the two entities in a sentence and map the cardinality using min/max language.

  • Recognize when a relationship is clearly one-to-many or many-to-many, and know how to annotate accordingly.

  • Remember the difference between non-unique vs unique inventory and how that affects ordering and tracking in the model.

  • Understand the flow of business processes: order placement, goods receipt, inventory updates, and payment.

  • Be prepared to defend your choices using business logic and the explicit wording in the scenario, but avoid making ungrounded assumptions.

• Quick practice prompts to test understanding

  • If a purchase order is created by exactly one buyer, what is the cardinality from Buyer to PurchaseOrder? (Answer: Buyer 0..N to PurchaseOrder 1; a buyer can create many POs, but a PO is created by exactly one Buyer.)

  • If a warehouse has space for at least one product, what is the minimum cardinality from Warehouse to Inventory? (Answer: min 1, max many for the Inventory side; i.e., Warehouse 1..N to Inventory 1.)

  • If an inventory item is offered by at least one vendor and a vendor offers at least one inventory item, what is the cardinality between Inventory and Vendor in this course? (Answer: Many-to-many in reality; treated as such for Level 1; notated as M:N, with each side potentially having zero or more related items depending on whether the vendor or inventory item exists in the database.)

• Final note from the lecturer

  • The instructor emphasizes not to assume missing cardinalities; use explicit statements or business logic only when clearly justified, and prepare to discuss reasoning in class.

• key takeaway

  • Cardinalities are a foundational tool for designing robust relational schemas; careful reading of scenario text and consistent notation ensure a correct and scalable model.