Relationships in Relational Database

In addition to the attributes of each entity, a data model must specify the relationships between entities. At the conceptual level, relationships are simply associations between entities. The statement “Customers buy products” indicates that a relationship exists between the entities Customers and Products. The entities involved in a relationship are called its participants. The number of participants is the degree of the relationship. (The degree of a relationship is similar to, but not the same as, the degree of a relation, which is the number of attributes.)

The vast majority of relationships are binary, like the “Customers buy products” example, but this is not a requirement. Ternary relationships, those with three participants, are also common. Given the binary relationships “Employees sell products” and “Customers buy products,” there is an implicit ternary relationship “Employees sell products to customers.” However, specifying the two binary relationships does not allow us to identify which employees sold which products to which customers; only a ternary relationship can do that.

A special case of a binary relationship is an entity that participates in a relationship with itself. This is often called the bill of materials relationship and is most often used to represent hierarchical structures. A common example is the relationship between employees and managers: any given employee might both be a manager and have a manager.

The relationship between any two entities can be one-to-one, one-to-many, or many-to-many. One-to-one relationships are rare, most often being used between supertype and subtype entities. To return to our earlier example, the relationship between an employee and that employee’s salesperson details is one-to-one.

One-to-many relationships are probably the most common type. An invoice includes many products. A salesperson creates many invoices. These are both examples of one-to-many relationships.

Although not as common as one-to-many relationships, many-to-many relationships are also not unusual and examples abound. Customers buy many products, and products are bought by many customers. Teachers teach many students, and students are taught by many teachers. Many-to-many relationships can’t be directly implemented in the relational model, but their indirect implementation is quite straightforward.

The participation of any given entity in a relationship can be partial or total. If it is not possible for an entity to exist unless it participates in the relationship, the participation is total; otherwise, it is partial. For example, Salesperson details can’t logically exist unless there is a corresponding Employee. The reverse is not true. An employee might be something other than a salesperson, so an Employee record can exist without a corresponding Salesperson record. Thus, the participation of Employee in the relationship is partial, while the participation of Salesperson is total.

The trick here is to ensure that the specification of partial or total participation holds true for all instances of the entity for all time. It’s not unknown for companies to change suppliers for a product, for example. If the participation of Products in the “Suppliers provide products” relation has been defined as total, it won’t be possible to delete the current supplier without deleting the other product details.