An in-depth look at Cardinality & Modality in Database Relationships.
A guide to designing a powerful database.
In database design, cardinality and modality are two modelling concepts which are used for analyzing entities, attributes and relationship structure in the database. This article aims to give a clear understanding of what cardinality and modality are all about.
Cardinality
Cardinality is a very important concept in designing database, there are used when creating an E/R diagram. Cardinality shows the largest number of occurrences allowed in a relationship, not the smallest. This is usually expressed as one to many. In simple terms, cardinality refers to the relationship between a row of one table and a row of another table, the only two options for cardinality are one or many.
Example: think of a university, with two tables: a table for a student and a table for a department; However a department can have many students, while a student can have only one department. This demonstrates that a single occurrence of entity type A (department) can be related to one or many occurrences of entity type B(students),but entity B can be related to only one occurrence of entity A. This is called one to many relationships, other use cases are, a mother can have many children while a child can have only one mother. A museum can have many works of art, but each work of art can only be in one museum.
There are other types of relationship aside one to many, each country has only one flag and each flag belongs to only one country. Here the relationship is one to one.
Lastly, in this case, a book can have many authors, while authors can have many books .this is a many to many relationships.
There are four cardinality states.
• One-to-one (1:1): An occurrence of entity A can relate, at most, to one occurrence of entity B, and an occurrence of entity B can relate, at most, to one occurrence of entity A. For example, a husband can have only one wife, and a wife only one husband(but in my country its one to many).
• One-to-many (1:N): One occurrence of entity A can relate to many occurrences of entity B, but an occurrence of B can relate to only one occurrence of A. For example, each pet has one owner, but each owner can have one or more pets.
• Many-to-many (M: N): An occurrence of entity A can relate to multiple occurrences of entity B, while an occurrence of entity B can relate to many occurrences of entity A. For example, a doctor-patient relationship, each doctor sees many patients and each patient sees many doctors.
• Many-to-one (M:1): Because relationships are bidirectional, a many-to-one relationship is the inverse of a one-to-many relationship.
There are different notations to diagramming cardinality; the crow’s foot and the Chen style notation out of the many. Chen placed the number 1 on the relationship line next to the diamond to represent one and an N or M to represent many. Most modern modellers have dropped the Chen notation in favour of the bar to represent the one and the trident, or, the crow’s foot notation.
Modality
Modality is completely different from cardinality. The modality value is displayed as “O”, if the relationship is optional or when there is no requirement, in a case where the modality value is represented as “1” (or a bar) then there is a compulsion for the occurrence of a relationship. In simple terms, it describes whether a relationship between two or more entities is mandatory or not.
Take, for example, the relationship-entity pairs Orders Contain Line Items and Artists Paint Pictures (the figure below). We know that the cardinality of Orders Contain Line Items is one-to-many — an order can consist of many line items, but a line item can be part of only one order. We also know that the cardinality of Artists Paint Pictures is also one-to-many, Yet there is a significant difference between the two. You cannot have an order without at least one line item, but many artists have never painted a picture.
Because you cannot have a line item without an order, then a Line Item occurrence must be linked to an Order occurrence; put another way, Line Item’s role in the relationship Contain is mandatory. Can you have an order without a line item? If not, then the Order’s role in Contain is also mandatory. The same is true for Pictures and Artists. A Picture occurrence must be linked to an Artist occurrence, so the modality is mandatory. However, an Artist occurrence need not be linked to any Picture occurrence, so Artist’s involvement in the relationship Paint is optional.
Modality depicts whether an entities role in a relationship is mandatory or optional. As said before, By convention, mandatory modality is represented by a bar “1”(a bar), while optional modality is depicted by “O”.The diagram below shows the appropriate positions of cardinality and modality.
There are four possible modality cases.
• Mandatory-optional: Every occurrence of entity A must be related to at least one occurrence of entity B, but an occurrence of entity B need not be related to any occurrences of entity A.For example, an Account need not be related to any Orders (it might have been just set up), but an Order must be related to an Account.
• Mandatory-mandatory: Every occurrence of entity A must be related to at least one occurrence of entity B, and every occurrence of entity B must be related to at least one occurrence of entity A. For example, an Order must be related to at least one Line Item and a Line Item must be related to an Order.
• Optional-optional: An occurrence of entity A need not be related to any occurrences of entity B, and an occurrence of entity B need not be related to any occurrences of entity A. For example,in Banks Finance Cars, a Bank might, but need not, finance any Cars and a Car might not have been Financed by a Bank.
• Optional-mandatory: Because relationships are Bi-directional, an optional-mandatory relationship is an inverse of a mandatory-optional relationship.
In conclusion cardinality, the absence of the crow’s foot could state a one, and for modality, the absence of the “O” could indicate mandatory. However, the bar is not redundant — it communicates an important message. Having all three symbols (bar, crow’s foot, and O) tells you when the modeller knows the cardinality or modality and when they do not.
