Cardinality and Optionality
Cardinality and optionality provide precise specifications for relationships, defining not just the type of relationship but whether participation is mandatory or optional—understanding these concepts is essential for designing databases that accurately enforce business rules and prevent invalid data—this topic explores how to specify and implement cardinality and optionality constraints.
Cardinality vs Optionality
Cardinality
Definition: The maximum number of instances that can participate in a relationship
Types:
CUSTOMER PLACES ORDER
Cardinality from CUSTOMER side:
– One customer can place zero , one , or many orders
– Cardinality = M ( many )
Cardinality from ORDER side:
– One order belongs to exactly one customer
– Cardinality = 1 ( one )
Result: One – to – Many ( 1 :M )
Optionality
Definition: Whether participation in a relationship is mandatory or optional
Types:
EMPLOYEE WORKS – IN DEPARTMENT
Two interpretations:
MANDATORY ( every employee must be in a department ) :
– Minimum: 1 employee per department
– Maximum: Many employees per department
– Notation: 1 ..M
OPTIONAL ( employee might not be assigned yet ) :
– Minimum: 0 employees per department ( new dept can be empty )
– Maximum: Many employees per department
– Notation: 0 ..M
Chen's Notation (Traditional ER Model)
Notation System
( Min , Max ) Notation
( 1 , 1 ) = Exactly 1 ( mandatory , singular )
( 0 , 1 ) = 0 or 1 ( optional , at most one )
( 1 , M ) = 1 to many ( mandatory , multiple )
( 0 , M ) = 0 to many ( optional , multiple )
Example: E-Commerce System
CUSTOMER ( 1 , M ) PLACES ( 1 , 1 ) ORDER
Customer side : ( 1 , M )
– Minimum: 1 customer per order ( every order has a customer )
– Maximum: M customers ( doesn 't apply, one order one customer)
– Read: "One customer places one or more orders"
Order side: ( 1 , 1 )
– Minimum: 1 order per customer ( if customer exists in context )
– Maximum: 1 order ( each order belongs to one customer )
– Read: "One order belongs to one customer"
Business meaning:
– Every customer can place many orders ( required to have customer )
– Every order belongs to exactly one customer
Crow's Foot Notation (Modern)
Symbols
| = 1 ( exactly one )
< = < is "many" symbol
o = optional ( 0 )
| = mandatory ( 1 )
Combining :
| | = 1 :1 ( mandatory on both sides )
o | = 0 :1 ( optional one side , mandatory other )
| < = 1 :M ( one to many )
o < = 0 :M ( optional many )
> < = M:M ( many to many )
Examples
Mandatory One – to – Many:
DEPARTMENT | ———— EMPLOYEE
( Every employee in exactly one department – mandatory )
Optional One – to – Many:
MANAGER o———— EMPLOYEE
( Employee may or may not have manager )
One – to – One:
PERSON || ———— PASSPORT
( One person , one passport , both mandatory )
Optional One – to – One:
EMPLOYEE o | —— || o PROJECT_MANAGER
( Employee may not have assigned project )
Many – to – Many:
STUDENT >< ———— COURSE
( Students take multiple courses , courses have multiple students )
Optionality: Mandatory vs Optional
Mandatory Relationships (Minimum = 1)
Definition: Every instance must participate
EMPLOYEE WORKS – IN DEPARTMENT
Business rule : "Every employee must be assigned to a department"
ER Notation :
EMPLOYEE ( 1 , M ) WORKS – IN ( 1 , 1 ) DEPARTMENT
Design :
EMPLOYEE Table :
– EmployeeID
– EmployeeName
– DepartmentID ( NOT NULL – cannot be empty )
Enforcement :
– FK constraint ensures DepartmentID exists
– NOT NULL constraint ensures always specified
When to use mandatory:
Optional Relationships (Minimum = 0)
Definition: Instance may or may not participate
EMPLOYEE MANAGES EMPLOYEE
Business rule : "Not every employee manages anyone"
ER Notation :
EMPLOYEE ( 0 , M ) MANAGES ( 0 , 1 ) EMPLOYEE
Design :
EMPLOYEE Table :
– EmployeeID
– EmployeeName
– ManagerID ( NULL allowed – can be empty )
Enforcement :
– FK constraint allows NULL
– NULL = no manager ( staff member , not manager )
– Non – NULL = has manager
When to use optional:
Real-World Examples
Example 1: Bank Account System
CUSTOMER ( 1 , M ) OPENS ( 1 , 1 ) ACCOUNT
Cardinality :
– CUSTOMER side : One customer can open many accounts ( checking , savings , etc .)
– ACCOUNT side : One account belongs to exactly one customer
Optionality :
– CUSTOMER ( mandatory ) : Every account must have a customer
– ACCOUNT ( mandatory ) : Every customer must have at least one account to be a customer
ER Notation : ( 1 , M ) and ( 1 , 1 ) = Both mandatory
Design :
CUSTOMER Table :
– CustomerID
– Name
ACCOUNT Table :
– AccountID
– CustomerID ( NOT NULL – FK )
– AccountType
– Balance
Business Rules :
✓ New customer can open first account
✓ Existing customer can open more accounts
✓ Account must have associated customer
✗ Account without customer not allowed
✗ Customer without accounts not meaningful ( but might allow for setup )
Example 2: University Course Enrollment
STUDENT ( 0 , M ) ENROLLS – IN ( 0 , M ) COURSE
Cardinality :
– STUDENT side : One student takes many courses
– COURSE side : One course has many students
Optionality :
– STUDENT ( optional ) : Course can exist without enrolled students ( new course )
– COURSE ( optional ) : Student can exist without enrollments ( new student )
ER Notation : ( 0 , M ) and ( 0 , M ) = Both optional
Design :
STUDENT Table :
– StudentID
– Name
COURSE Table :
– CourseID
– CourseName
– Instructor
ENROLLMENT Table ( Junction ) :
– StudentID ( FK )
– CourseID ( FK )
– EnrollmentDate
– Grade
Business Rules :
✓ New student with no enrollments
✓ New course with no enrollments
✓ Student enrolls in multiple courses
✓ Course has multiple students
✗ Enrollment records link them
Example 3: Employee-Department-Project
EMPLOYEE ( 1 , M ) WORKS – FOR ( 1 , 1 ) DEPARTMENT
EMPLOYEE ( 0 , M ) WORKS – ON ( 0 , M ) PROJECT
Works – For:
– Mandatory: Every employee must work in exactly one department
– Min = 1 , Max = M for employee side
– Min= 1 , Max = 1 for department side
Works – On:
– Optional: Employees may not be assigned to projects
– Min= 0 , Max =M for employee side
– Min= 0 , Max =M for project side
Design :
EMPLOYEE :
– EmployeeID
– Name
– DepartmentID ( NOT NULL )
DEPARTMENT :
– DepartmentID
– DepartmentName
PROJECT :
– ProjectID
– ProjectName
EMPLOYEE_PROJECT ( Junction ) :
– EmployeeID ( FK )
– ProjectID ( FK )
– HoursAllocated
Implementing Cardinality and Optionality
One-to-One (1:1)
Mandatory Both Sides: (1,1) to (1,1)
PERSON || ———— PASSPORT
Design :
PERSON :
– PersonID ( PK )
– Name
– PassportID ( FK , UNIQUE , NOT NULL )
PASSPORT :
– PassportID ( PK )
– Number
– IssueDate
Constraint : PassportID must be unique in PERSON
Optional One Side: (0,1) to (1,1)
EMPLOYEE o | —— || PARKING_SPOT
Design :
EMPLOYEE :
– EmployeeID ( PK )
– Name
– ParkingSpotID ( FK , UNIQUE , NULL allowed )
PARKING_SPOT :
– SpotID ( PK )
– Location
– SpotNumber
Constraint : ParkingSpotID can be NULL ( not all have spots )
One-to-Many (1:M)
Mandatory Child: ( 1,M ) to (1,1)
DEPARTMENT | ———— EMPLOYEE
Design :
DEPARTMENT :
– DepartmentID ( PK )
– DepartmentName
EMPLOYEE :
– EmployeeID ( PK )
– Name
– DepartmentID ( FK , NOT NULL )
Constraint : Every employee must have department
Optional Child: ( 1,M ) to (0,1)
MANAGER o———— EMPLOYEE
Design :
EMPLOYEE :
– EmployeeID ( PK )
– Name
– ManagerID ( FK , NULL allowed )
Constraint : ManagerID can be NULL ( some don 't have manager)
Many-to-Many (M:M)
Mandatory Junction: ( 1,M ) to ( 1,M )
EMPLOYEE >< ———— PROJECT
Design :
EMPLOYEE :
– EmployeeID ( PK )
– Name
PROJECT :
– ProjectID ( PK )
– ProjectName
ASSIGNMENT ( Junction ) :
– EmployeeID ( FK , PK )
– ProjectID ( FK , PK )
– HoursAllocated
Constraint : Records created only when employee assigned to project
Common Mistakes
Mistake 1: Wrong Optionality
WRONG :
CUSTOMER — CustomerID ( optional FK )
Should require : Every order must have customer
CORRECT :
CUSTOMER — CustomerID ( NOT NULL FK )
Every order has a customer
Mistake 2: Confusing Cardinality and Optionality
CONFUSION :
"One-to-Many means optional"
CLARITY :
One – to – Many = cardinality ( max relationship count )
Mandatory / Optional = optionality ( min relationship count )
Can have mandatory one – to – many or optional one – to – many
Mistake 3: Not Enforcing in Database
NO ENFORCEMENT :
Create FK but allow NULL ( optional ) when should be mandatory
PROPER ENFORCEMENT :
Use NOT NULL constraints
Use FK constraints
Add CHECK constraints for complex rules
Decision Matrix
When designing relationships, ask:
|
Question |
Answer |
Optionality |
|---|---|---|
|
Must instance participate? |
Yes |
Mandatory (1) |
|
Can instance exist alone? |
No |
Mandatory (1) |
|
Is relationship required? |
Yes |
Mandatory (1) |
|
Can instance exist alone? |
Yes |
Optional (0) |
|
Is relationship optional? |
Yes |
Optional (0) |
|
Some instances don't need? |
Yes |
Optional (0) |
💡 Key Insights
• Cardinality: How many (1 or many)
• Optionality: Whether required (mandatory) or not (optional)
• Minimum/Maximum: Min 0/1, Max 1/M
• Enforce in database: Use constraints
• Mandatory = NOT NULL: In relational design
• Optional = NULL allowed: In relational design
📚 Study Notes
• Cardinality: 1 (one) or M (many)
• Optionality: 0 (optional) or 1 (mandatory)
• Chen notation: (Min, Max) format
• Crow's foot: Visual symbols
• Mandatory: Must participate ( min =1)
• Optional: May not participate ( min =0)
• ( 1,1): Exactly one, mandatory
• ( 0,1): Zero or one, optional
• (1,M ): One or many, mandatory
• (0,M ): Zero or many, optional
• FK NOT NULL: Mandatory relationship
• FK NULL allowed: Optional relationship