Up to this point we have made several references to the concept of relationship. Now, we will make our understanding of this concept more complete. A relationship is an association amongst entities. Relationships will have justification in business rules, in the way an enterprise manages its business.
There are several ways of classifying relationships, according to degree, participation, cardinality, whether recursion is involved, and whether or not a relationship is identifying.
We consider the degree as the number of entities that participate in the relationship. When we speak of a student enrolling in a course, we are considering a relationship (say, the enroll in relationship) where two entity types (Student and Course) are involved. This relationship is of degree 2 because each instance of the relationship will always involve one student entity and one course entity.
With binary relationships there must be two defining statements we can express, one from the perspective of each entity type. In this case our statements are:
- A student may enroll in any number of courses.
- A course may have any number of students enrolled.
Many database modeling tools only support binary relationships. However there are situations where relationships of higher degree are useful. A relationship involving 3 entity types is called ternary; more generally we refer to relationships with n entity types as n-ary. Our primary focus in this text is on binary relationships.
Suppose we are designing a database for a company that has several departments and employees. Suppose further that each employee must be assigned to work in one department. We can define a works in relationship involving Department and Employee. Employees must participate in the relationship and we show this using a double line joining the diamond symbol to the Employee entity type.
The double line stands for total or mandatory participation which means that instances of the adjacent entity type must participate in the relationship – in the case above, all instances of Employee must be assigned to some department. Any time we show a single line we are stating participation is optional; for the above we are saying that a department will have zero or more employees who work there.
Cardinality is a constraint on a relationship specifying the number of entity instances that a specific entity may be related to via the relationship. Suppose we have the following rules for departments and employees:
- A department can have several employees that work in the department
- An employee is assigned to work in one department.
From these rules we know the cardinalities for the works in relationship and we express them with the cardinality symbols 1 and n below.
The n represents an arbitrary number of instances, and the 1 represents at most one instance. For the above works in relationship we have
- a specific employee works in at most only one department, and
- a specific department may have many (zero or more) employees who work there.
n, m, N, and M are common symbols used in ER diagrams for representing an arbitrary number of occurrences; however, any alphabetic character will suffice.
Based on cardinality there are three types of binary relationships: one-to-one, one-to-many, and many-to-many.
One-to-one relationships have 1 specified for both cardinalities. Suppose we have two entity types Driver and Vehicle. Assume that we are only concerned with the current driver of a vehicle, and that we are only concerned with the current vehicle that a driver is operating. Our two rules associate an instance of one entity type with at most one instance of the other entity type:
a driver operates at most one vehicle, and
a vehicle is operated by at most one driver.
and so the relationship is one-to-one.
One-to-many relationships are the most common ones in database designs. Suppose we have customer entities and invoice entities and:
- an invoice is for exactly one customer, and
- a customer could have any number (zero or more) of invoices at any point in time.
Because one instance of an Invoice can only be associated with a single instance of Customer, and because one instance of Customer can be associated with any number of Invoice instances, this is a one-to-many relationship:
Suppose we are interested in courses and students and the fact that students register for courses. Our two rule statements are:
- any student may enroll in several courses,
- a course may be taken by several students.
This situation is represented as a many-to-many relationship between Course and Student:
As will be discussed again later, a many-to-many relationship is implemented in a relational database in a separate relation. In a relational database for the above, there would be three relations: one for Student, one for Course, and one for the many-to-many. (Sometimes this 3rd relation is called an intersection table, a composite table, a bridge table.)
Partly because of the need for a separate structure when the database is implemented, many modellers will ‘resolve’ a many-to-many relationship into two one-to-many relationships as they are modelling. We can restructure the above many-to-many as two one-to-many relationships where we have ‘invented’ a new entity type called Enrollment:
A relationship is recursive if the same entity type appears more than once. A typical business example is a rule such as “an employee supervises other employees”. The supervises relationship is recursive; each instance of supervises will specify two employees, one of which is considered a supervisor and the other the supervised. In the following diagram the relationship symbol joins to the Employee entity type twice by two separate lines. Note the relationship is one-to-many: an employee may supervise many employees, and, an employee may be supervised by one other employee.
With recursive relationships it is appropriate to name the roles each entity type plays. Suppose we have an instance of the relationship:
John supervises Terry
Then with respect to this instance, John is the supervisor employee and Terry is the supervised employee. We can show these two roles that entity types play in a relationship by placing labels on the relationship line:
This one-to-many supervises relationship can be visualized as a hierarchy. In the following we show five instances of the relationship: John supervises Lee, John supervises Peter, Peter supervises Don, Peter supervises Mary, and John supervises Noel.
In the above example note the participation constraint at both ends of supervises is optional. This has to be the case because some employee will not be supervised, and, for some employees there are no employees they supervise.
Generally recursive relationships are difficult to master. Some other situations where recursive relationships can be used:
- A person marries another person
- A person is the parent of a person
- A team plays against another team
- An organizational units report to another organizational unit
- A part is composed of other parts.
When entity types were first introduced we discussed an example where a department offers courses and that a course must exist in the context of a department. In that case the Course entity type is considered a weak entity type as it is existence-dependent on Department. It is typical in such situations that the key of the strong entity type is used in the identification scheme for the weak entity type. For example, courses could be identified as MATH-123 or PHYS-329, or as Mathematics-123 or Physics-329. In order to convey the composite identification scheme for a weak entity type we specify the relationship as an identifying relationship which is visualized using a double-lined diamond symbol:
Additionally in situations where we have an identifying relationship we usually have
- a weak entity type with a partial key
- a weak entity type that must participate in the relationship (total participation)
and so the ERD for our hypothetical educational institution could be:
Note the keys for the strong entity type appear only at the strong entity type. The identifying relationship tells one that a department key will be needed to complete the identification of a course.
Consider a company that owns and operates parking lots. Draw an ERD to include the following specifications. Each parking lot has a unique address (use the typical fields for addresses) and each parking lot has a certain number, say n, of parking spaces. Each space in a lot has a number between 1 and n. The cost of renting a parking space is the same for all spaces in a lot. The company rents individual spaces out to its customers. Each customer is identified by a driver’s license id, has a first and last name. Each customer will identify possibly several cars that they will park in the space rented to them. For each car the company needs to know the year, make, model, colour and its license plate number.
Modify your model from the previous question to allow for scrambled parking. By this we mean that a customer is rented a space in a lot, but the customer may park in any available space.
Draw an ERD involving employees and their dependents where each employee has a unique id number and where dependents of the same employee are numbered starting at 1. It may be rare, but we will allow for dependents of the same employee to have the same name and birthdates. Include typical attributes for an employee, and for a dependent include the birthdate, first and last names.
Draw an ERD for marriages between two people. For persons include birthdate, first name, last name, and a unique person id. Consider marriage to be a relationship between two people and suppose we want our model to allow for people to have more than one marriage. Use the date of the marriage as a discriminator.
Consider marriages again but now let marriage be an entity type. Suppose when people marry there is a marriage certificate that is granted by a government authority. Include attributes applicable to a marriage.
Suppose we are modeling marriage as a relationship between two people. When, or under what circumstances, can we model this as a one-to-one relationship?
Draw an ERD that allows for marriages between possibly more than two people.
Consider the one-to-one operates relationship in this chapter. Modify the example so that drivers have attributes: driver license, name (which comprises first name and last name), and vehicles have attributes: license plate number, VIN, year, colour, make and model. Note that VIN stands for vehicle identification number and this is unique for each vehicle. Assume that each driver must be assigned to a vehicle.
Consider the enroll in relationship used in this chapter. Suppose we must allow for a student to repeat a course to improve their grade. Develop an ERD and include typical attributes for student, course, etc. We need to keep a complete history of all course attempts by students.
What problems arise if one makes the supervises relationship mandatory for either the supervising employee or the employee who is supervised?
Consider requirements for teams, players and games, and develop a suitable ERD. Each team would have a unique name, have a non-player who is the coach, and have several players. Each player has a first and last name and is identified by a number (1, 2, 3, etc.). One player is designated the captain of the team. Assume a game occurs on some date and time, and is played by two teams where one team is called the home team and the other team is called the visiting team. At the end of the game the score must be recorded.
Modify your ERD for the above to accommodate a specific sport such as curling, baseball, etc.
Consider an ERD for modelling customers, phones, and phone calls. Each customer owns one phone and so the phone number identifies the customer. Include other attributes such as credit card number, first name, and last name for a customer. We must record information for each phone call that is made: for each call there is a start time, end time, and of course the phone number/customers involved.
Create an ERD suitable for a database that will keep genealogy data. Suppose there is one entity type Person and you must model the two relationships: marries and child of.
Develop an ERD to support home real estate sales. Consider there are several sales employees who list and sell properties. For each employee we need to know their name (first and last), the date they started working for this company, and the number of years they have been with the company. Each property has owners (one or more people), and may have certain features such as number of baths, number of levels, number of bedrooms. For each owner we must keep track of their names (first and last). Each property has an address; each address has the usual attributes: street (comprising apartment number, street number, street name), city, province, and postal code. A home is listed at a certain price and sold at possibly a different price. Of course, we need to track the names of the buyers, the date of a listing and the date of a sale.
Develop an ERD to keep track of information for an educational institution. Assume each course is taught by one instructor, and an instructor could teach several courses. For each instructor suppose we have a unique identifier, a first name, a last name, and a gender. Each course belongs to exactly one department. Within a department courses are identified by a course number. Departments are identified by a department code.
Develop an ERD to allow us to keep information on a survey. Suppose a survey will have several questions that can be answered true or false. Over a period of time the survey is conducted and there will be several responses.
Modify the ERD above to allow for surveys that have multiple choice answers.
Develop an ERD to support the management of credit cards. Each credit card has a unique number and has a customer associated with it. A customer may have several credit cards. The customer has a first name, last name, and an address. Each time a customer uses a credit card we must record the time, the date, the vendor, and the amount of money involved.
Modify the ERD for the above to accommodate the monthly billing of customers. Each month a customer receives a statement detailing the activity that month.
Develop an ERD to be used by a company to manage the orders it receives from its customers. Each customer is identified uniquely by a customer id; include the first name, last name, and address for each customer. The company has several products that it stocks and for which customers place orders. Each product has a unique id, unique name, unit price, and a quantity on hand. At any time a customer may place an order which will involve possibly many products. For each product ordered the database must know the quantity ordered and the unit price at that point in time. If the customer does this through a phone call then an employee is involved in the call and will be responsible for the order from the company side. Some orders are placed via the internet. For each order an order number is generated. For each order the database must keep track of the order number, the date the order was placed and the date by which the customer needs to receive the goods.