Erd cardinality

1. ERD Cardinality
OOD Inge Powell

2. ERD Cardinality
When we show relationships between
tables we need to show the cardinality.
Depending on the data we are storing
there are several options to choose
from.
If using Visio to create a diagram, it
takes its initial value from the database
store.

3. Erd Cardinality
The cardinality relationship is shown with
lines between two entities to indicate a
relationship.
This line denotes that there is a
relationship between the entities.
Entity A Entity B

4. ERD Cardinality
The ENDS of these lines denote the cardinality
to that entity.
This is a ‘One to One’ relationship.
This means that Entity A will relate to only one
of Entity B.
And Entity B will relate to only one of Entity A
Entity A Entity B

5. Erd Cardinality
Here is an example..
One Driver drives just one Bus.
One Bus has just one Driver.
The records might be held like this:
Bus Driver
Bus
PK BusID
Colour
NoOfSeats
FK DriverID
Bus
PK BusID
Colour
NoOfSeats
FK DriverID
Driver
PK DriverID
Name
Points
FK BusID
Driver
PK DriverID
Name
Points
FK BusID

6. ERD Cardinality
The use of ‘Crows Feet’.
This is a ‘One to Many’ relationship.
This means that Entity A will relate to many of
Entity B.
And Entity B will relate to only one of Entity A.
Entity A Entity B

7. Erd Cardinality
Here is an example.
One Customer may have many orders.
One Order is for just one Customer.
The records might be held like this:
Customer Order
Customer
PK CustID
Name
Address
TelNo
Customer
PK CustID
Name
Address
TelNo
Order
PK OrderNo
Date
FK CustID
Order
PK OrderNo
Date
FK CustID

8. ERD Cardinality
The use of ‘Crows Feet’.
This is a ‘Many to One’ relationship.
This means that Entity A will relate to only one
of Entity B.
And Entity B will relate to many of Entity A.
(We still tend to refer to as a one to many, just the
other way round from B to A)
Entity A Entity B

9. Product
PK ProdID


PName
Price
FK
SuppID
Product
PK ProdID


PName
Price
FK
SuppID
Erd Cardinality
Here is an example.
One Product will have just one Supplier.
One Supplier will supply many Products.
The records might be held like this:
Product Supplier
Supplier
PK SuppID
Name
Address
TelNo
Supplier
PK SuppID
Name
Address
TelNo
Product
PK ProdID


PName
Price
FK
SuppID
Product
PK ProdID


PName
Price
FK
SuppID
Product
PK ProdID


PName
Price
FK
SuppID
Product
PK ProdID


PName
Price
FK
SuppID
Supplier
PK SuppID
Name
Address
TelNo
Supplier
PK SuppID
Name
Address
TelNo
Product
PK ProdID


PName
Price
FK
SuppID
Product
PK ProdID


PName
Price
FK
SuppID

10. ERD Cardinality
The use of ‘Crows Feet’.
This is a ‘Many to Many’ relationship.
This means that Entity A will relate to many of
Entity B.
And Entity B will relate to many of Entity A.
Entity A Entity B

11. ERD Cardinality
Many to Many - This can cause problems with
data, as we can’t pinpoint the link.
Imagine this, a Lender can borrow many
Books. But a Book can be borrowed by more
than one Lender.
Entity A Entity B
Lender Book

12. Book
PK BookID

Title
Author
FK
LenderID
FK
LenderID
FK
LenderID
FK LenderID
Book
PK BookID

Title
Author
FK
LenderID
FK
LenderID
FK
LenderID
FK LenderID
ERD Cardinality
How do we know who has each individual
book?
Also when we look at the data, how will we
store this?
Lender Book
Lender
PK LenderID

Name
Address
FK BookID
FK BookID
FK BookID
FK BookID
Lender
PK LenderID

Name
Address
FK BookID
FK BookID
FK BookID
FK BookID

13. ERD Cardinality
To manage the many to many relationship, the
data would need to keep growing.
This still causes problems,
Who has the book now?
This is silly!!
We can resolve this
By adding a link table.
Book
PK BookID

Title
Author
FK LenderID
FK LenderID
FK LenderID
FK LenderID
FK LenderID
FK LenderID
FK LenderID
FK LenderID
FK LenderID
FK LenderID
FK LenderID
Book
PK BookID

Title
Author
FK LenderID
FK LenderID
FK LenderID
FK LenderID
FK LenderID
FK LenderID
FK LenderID
FK LenderID
FK LenderID
FK LenderID
FK LenderID

14. ERD Cardinality
To resolve a many to many relationship.
We resolve it by adding a link table.
This means we now have two manageable
one to many relationships instead.
Entity A Entity BEntity C
Entity A Entity B

15. ERD Cardinality
The resolved many to many relationship.
One lender can have several LendRecords
One LendRecord is for just one Lender
One Book can have several LendRecords
One LendRecord is for just one Book
Lender BookLendRecord

16. ERD Cardinality
Here is the resolved example.
The data for this system might look something
like this:
Lender BookLendRecord
Book
PK BookID

Title
Author
Book
PK BookID

Title
Author
LendRecor
d
PK RecordID
FK BookID

FK LenderID

LendRecor
d
PK RecordID
FK BookID

FK LenderID

Lender
PK LenderID

Name
Address
Lender
PK LenderID

Name
Address

17. ERD Cardinality
The LendRecord record could be deleted
when the book is returned.
Or fields added to identify the current loan.
Lender BookLendRecord
Book
PK BookID

Title
Author
Book
PK BookID

Title
Author
LendRecor
d
PK RecordID
FK BookID

FK LenderID

OnLoanYN
Date

LendRecor
d
PK RecordID
FK BookID

FK LenderID

OnLoanYN
Date

Lender
PK LenderID

Name
Address
Lender
PK LenderID

Name
Address