Relationships

A relationship between entities is the database equivalent of a verb. An employee is a member of a department, or an office is located in a city. As will be explained later, relationships in a database may appear as foreign key relationships between tables, or may appear as separate tables themselves.

The relationships in the database are an encoding of rules or practices that govern the data in the entities. If each department has one department head, you can create a one-to-one relationship between departments and employees to identify the department head.

Once a relationship is built into the structure of the database, there is no provision for exceptions. There is nowhere to put a second department head. Duplicating the department entry would involve duplicating the department ID, which is the identifier. Duplicate identifiers are not allowed.

Tip

Strict database structure can benefit you because it can eliminate inconsistencies, such as a department with two managers. On the other hand, you as the designer should make your design flexible enough to allow some expansion for unforeseen uses. Extending a well-designed database is usually not too difficult, but modifying the existing table structure can render an entire database and its client applications obsolete.

Cardinality of relationships

There are three kinds of relationships between tables. These correspond to the cardinality (number) of the entities involved in the relationship.

  • One-to-one relationships   You depict a relationship by drawing a line between two entities. The line may have other markings on it such as the two little circles shown below. Later sections explain the purpose of these marks. In the following diagram, one employee manages one department.

    The management relationship between Department and Employee.

  • One-to-many relationships   The fact that one item contained in Entity 1 can be associated with multiple entities in Entity 2 is denoted by the multiple lines forming the attachment to Entity 2. In the following diagram, one office can have many phones.

    The phone location relationship between office and telephones.

  • Many-to-many relationships   In this case, draw multiple lines for the connections to both entities. This means that one warehouse can hold many different parts, and one type of part can be stored at many warehouses.

    The storage relationship between parts and warehouses.

Roles

You can describe each relationship with two roles. Roles are verbs or phrases that describe the relationship from each point of view. For example, a relationship between employees and departments might be described by the following two roles.

  1. An employee is a member of a department.
  2. A department contains an employee.
The Employees table and Departments table roles.

Roles are very important because they afford you a convenient and effective means of verifying your work.

Tip

Whether reading from left-to-right or from right-to-left, the following rule makes it easy to read these diagrams: read the name of the first entity, the role next to the first entity, the cardinality from the connection to the second entity, and the name of the second entity.

In the diagram above, reading left to right, each Employee is a member of one Department. Reading right to left, a Department contains many Employees.

Mandatory elements

The little circles just before the end of the line that denotes the relation serve an important purpose. A circle means that an element can exist in the one entity without a corresponding element in the other entity.

If a cross bar appears in place of the circle, that entity must contain at least one element for each element in the other entity. An example will clarify these statements.

The Publisher table, Book table, and Author table.

This diagram corresponds to the following four statements.

  1. A publisher publishes zero or more books.
  2. A book is published by exactly one publisher.
  3. A book is written by one or more authors.
  4. An author writes zero or more books.
Tip

Think of the little circle as the digit 0 and the cross bar as the number one. The circle means at least zero. The cross bar means at least one.

Reflexive relationships

Sometimes, a relationship will exist between entries in a single entity. In this case, the relationship is said to be reflexive. Both ends of the relationship attach to a single entity.

The Employees table.

This diagram corresponds to the following two statements.

  1. An employee reports to at most one other employee.
  2. An employee manages zero or more employees.

Notice that in the case of this relation, it is essential that the relation be optional in both directions. Some employees are not managers. Similarly, at least one employee should head the organization and hence report to no one.

Naturally, you would also like to specify that an employee cannot be his or her own manager. This restriction is a type of business rule. Business rules are discussed later as part of The design process.

Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0