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)

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

(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)

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

| = 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)

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)

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

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

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

– Email

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)

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

– Email

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

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

PERSON ||———— PASSPORT

Design:

PERSON:

– PersonID (PK)

– Name

– PassportID (FK, UNIQUE, NOT NULL)

PASSPORT:

– PassportID (PK)

– Number

– IssueDate

Constraint: PassportID must be unique in PERSON

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)

DEPARTMENT |———— EMPLOYEE

Design:

DEPARTMENT:

– DepartmentID (PK)

– DepartmentName

EMPLOYEE:

– EmployeeID (PK)

– Name

– DepartmentID (FK, NOT NULL)

Constraint: Every employee must have department

MANAGER o———— EMPLOYEE

Design:

EMPLOYEE:

– EmployeeID (PK)

– Name

– ManagerID (FK, NULL allowed)

Constraint: ManagerID can be NULL (some don't have manager)

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

WRONG:

CUSTOMER — CustomerID (optional FK)

Should require: Every order must have customer

CORRECT:

CUSTOMER — CustomerID (NOT NULL FK)

Every order has a customer

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

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

💡 Key Insights

📚 Study Notes