Database N-ary Association
Database Essentials - Part 6
Division 1
Forward: In this part of the series, we look at what is called N-ary Association.
By: Chrysanthus Date Published: 4 Aug 2012
Introduction
Example of Many-to-Many relationship
You will have to open the following link in a browser tab in order to see the diagrams of this tutorial (link below).
If you have opened the link, then note that fig 6.1 shows three main entities. The word entity here means a table that is large and should not be implemented as it is (see later). You have the Employee, Product and Component entities. These entities belong to a company (factory) that produces bicycles. In this company, bicycles are assembled by employees (by hand). One bicycle can be assembled by only one employee. In the diagram, bicycles are called products. Product forms one entity in the diagram. You have the employee entity; we have seen an example of an employee entity (table) before. You also have the component entity. This entity has the mechanical parts that are used to assemble the bicycles.
Table 6.1 is a sample table for the Employee entity. It shows only the Employee ID column and the Employee Name columns. That is all we need for the employee entity for this tutorial. Table 6.2 shows the Product table. It has three columns. Table 6.3 shows the Component table. It also has three columns. You should look at the tables to see what the columns hold as data. Each of the tables has a one column primary key.
In the diagram there are two many-to-many relationships: one between the Employee and Product entity; the other between the Product and the Component entity. The aim of this tutorial is not to discus how many-to-many relationships come about. The aim is not even to show how to break a many-to-many relationship into one-to-many relationships as mentioned in the previous part of the series; even though we shall do that here. The aim is to make you understand a kind of association (relationship) called N-ary Association. In order to arrive at an example of N-ary association, we shall have to break the two many-to-many relationships down.
The class diagram in fig.6.1 poses problems. These are some of the problems: Imagine that the three entities represent three tables, one table for each entity. From these two many-to-many relationships, we cannot tell which employee assembled which bicycle using which components. The solution is to have another table (Assembly), which would link the three tables, as shown in fig 6.2. In a different series I will show you how to convert a many-to-many relationship to one-to-many relationships. Here I just want you to know what is meant by N-ary relationship. We are just about to see what N-ary relationship means.
A sample table for the new class, Assembly in fig 6.2 is shown in Table 6.4. The new class (table) in fig 6.2 is drawn as a diamond. A dash line is drawn from it, ending with a rectangle that indicates the table columns of the diamond class.
When more than two classes are related, through another class, the relationship is called an n-ary association and is focused in the class diagram as a diamond. The diamond class in fig 6.2 is called the Assembly class.
The columns of the Assembly class are the keys of the three main entities. Using the Assembly table (class) as a base, you can now know which employee used which components to assemble which product (bicycle). The Assembly class has the IDs of the different tables. From these IDs you can then go to the different tables and know the name of the employee, or component or product accordingly.
The kind of diagram of fig 6.1 is used when you want an overview of the company main classes. Such an overview diagram will not contain only three main entities; it would contain more. For implementation purpose, you need the other kind (detail) of diagram in fig 6.2. Note that the entities for the detailed class diagrams are also usually many.
You should now know the meaning of N-ary relationship. How to arrive at an N-ary relationship when given a problem, should not be your concern in this tutorial. We shall look at that in a different series. Let us end here and continue in the next part of the series.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT