9.1 Binary Relationships
Cardinality
One-to-One Binary Relationship
Cardinalities display the maximum number of entities that can be involved in a particular relationship.
One-to-One (1-1) binary relationship, which means that a single occurrence of one entity type can be associated with a single occurrence of the other entity type and vice versa.
Ex. A particular salesperson is assigned to one office. Conversely, a particular office (in this case they are all private offices!) has just one salesperson assigned to it. The way to read these diagrams is to start at one entity, read the relationship on the connecting line, pick up the cardinality on the other side of the line near the second entity, and then finally reach the other entity. Modality
Figure shows the addition of the modality, the minimum number of entity occurrences that can be involved in a relationship. In our particular salesperson environment, every salesperson must be assigned to an office. On the other hand, a given office might be empty or it might be in use by exactly one salesperson.
This situation is recorded in Figure a, where the "inner" symbol, which can be a zero or a one, represents the modality—the minimum—and the "outer" symbol, which can be a one or a crow's foot, represents the cardinality—the maximum.
Reading Figure a from left to right tells us that a salesperson works in a minimum of one and a maximum of one office, which is another way of saying exactly one office. Reading from right to left, an office may be occupied by or assigned to a minimum of no salespersons (i.e. the office is empty) or a maximum of one salesperson.
More about many-to-many relationships
Intersection data
Generally, we think of attributes as facts about entities.
Each salesperson has a salesperson number, a name, a commission percentage, and a year of hire.
At the entity occurrence level, for example, one of the salespersons has salesperson number 528, the name Jane Adams, a commission percentage of 15 %, and the year of hire of 2003. In an E-R diagram, these attributes are written or drawn together with the entity. This certainly appears to be very natural and obvious. Are there ever any circumstances in which an attribute can describe something other than an entity? Example
Let's say that since salesperson number 137 joined the company, she has sold 170 units of product number 24 013. The quantity 170 doesn't make sense as a description or characteristic of salesperson number 137 alone.
She has sold many different kinds of products. To which one does the quantity 170 refer? Similarly, the quantity 170 doesn't make sense as a description or characteristic of product number 24 013 alone.
It has been sold by many different salespersons.
The quantity 170 falls at the intersection of salesperson number 137 and product number 24013.
It describes the combination of or the association between that particular salesperson and that particular product and it is known as intersection data. The intersection data describes the relationship between the two entities. We know that an occurrence of the Sells relationship specifies that salesperson 137 has sold some of product 24013. The quantity 170 is an attribute of this occurrence of that relationship, further describing this occurrence of the relationship. Not only do we know that salesperson 137 sold some of product 24013 but we know how many units of that product that salesperson sold.
Associative Entity
Associative Entities are used to indicate a relationship a salesperson and a product; More specifically that a certain salesperson has been involved in selling a particular product and includes any intersection data that describes their relationship.
9.2 Unary Relationships
Unary relationships associate occurrences of an entity type with other occurrences of the same entity type
This happens when an entity is associated with itself. Think of it like a person having a connection with another person within the same group.
One-to-One Unary Relationship (1:1)
Example: A salesperson backs up another salesperson.
Every salesperson must have exactly one backup, and the backup must also have one. Key Idea: Each entity relates to only one other entity in this scenario
One-to-Many Unary Relationships
Example: A person can be a parent of multiple children.
One parent can relate to many children, but each child has only one parent in the context of this relationship.
Many-to-Many Unary Relationships
Example: People can be friends with each other.
One person can have many friends, and each friend can have many other friends Key Features of Unary Relationships:
The same entity type points back to itself. Optional: Some entities might not have relationships (e.g., an unmarried person doesn't have a spouse). Mandatory: Some relationships are required (e.g., every salesperson must have a backup).
9.3 Ternary Relationships
A ternary relationship involves three different entities that are interconnected. Unlike binary relationships (which connect two entities), a ternary relationship shows how three entities are related in a single instance.
Example Scenario (Based on the Diagram)
Customer: Someone buying products. Salesperson: A person selling the products. Product: The item being sold. A salesperson sells a specific product to a customer. This sale is logged with details like the date and quantity of products sold. Key Characteristics
One salesperson can sell many products to many customers. One customer can buy many products from many salespersons. One product can be sold to many customers by many salespersons. SALE in this case is the intersection table. It stores the following details: Salesperson Number (Who made the sale) Customer Number (Who bought the product) Product Number (What product was sold) Date and Quantity (Additional details about the sale) Why Use a Ternary Relationship?
Efficiency: It avoids the need to create multiple binary relationships. Captures Complex Scenarios: Some real-world cases involve three entities interacting simultaneously, and this structure captures that complexity. Real-World Examples
Doctor, Patient, and Treatment: A doctor provides a specific treatment to a patient. Teacher, Student, and Course: A teacher teaches a course to a student.
Unit 9: Data Modeling
