What Occurs When Two Entities Can Be Related to Each Other in Many Instances?
There are some modeling situations that you lot will detect over and once again as you design real databases. We refer to these as design patterns. If you empathise the concepts backside each one, you lot volition in effect be calculation new "tools" to your blueprint toolbox that you can utilize in edifice the model of an enterprise.
Many-to-many relationships
Introduction
As we learned in the commodity on associations, objects interact with each other and we utilise relationships in our model of these objects to capture these interactions. In that section nosotros learned about 1-to-many relationships. There are also many-to-many and one-to-one relationships; every bit yous can see these iii types of relationships are named afterward the cardinality constraints. In that location is a quaternary type, the many-to-one human relationship, however, that is only the opposite management of the one-to-many relationship. This article introduces a design pattern for the many-to-many relationship, and shows the UML course diagram which is then mapped to the relational model.
The sales database
And then far our sales database has customers and orders. To end the blueprint pattern, we need products to sell. We'll first describe what the Product grade ways, and how it is associated with the Order grade. Below is how Product might be described
A product is a specific type of item that we have for sale. Each production has a descriptive proper name; we distinguish like products by the manufacturer'due south proper noun and model number. For each product, nosotros need to know its unit of measurement list price and how many units of this product we accept in stock.
It is important to understand exactly what is meant by a product so that information technology tin be modeled correctly. An case product (an example of the Production grade, a Production object) might exist named "Blender, Commercial, ane.25 Qt.", manufactured by Hamilton Beach, model number 908. This is a type of product, non an private boxed blender that is sitting on our shelves. The aforementioned manufacturer probably has dissimilar blender models (909, 918, 919), and there are probably blenders that we stock that are made by other companies. Each such blender would be a different instance of this class.
The following would be a typical description of how products chronicle with orders.
An order tin include many products, though a product will only exist in an order once. Each fourth dimension an order is placed for a production, nosotros need to know how many units of that product are being ordered and the cost we are actually selling the product for. The latter is needed as the auction price might vary from the listing price by customer discount, special sale, price changes, etc.
From the above description, we can excerpt the following sentences which focus on the constraints that will need to exist modeled.
-
Each Order must incorporate 1 and at most many Products.
We use the verb "must" here because it doesn't brand sense to have an order with no products; that is, for the guild to exist it is mandatory for information technology to comprise at to the lowest degree ane product. The rest tells us that an social club tin contain many products. -
Each Product may be independent in many Orders
. Here, we use the verb "may" because a product tin can exist in the database and not have been ordered by anyone, and then it'southward optional for it to be in an order. Of grade, nosotros need to permit a product to be in many orders.
There are significant differences in this modeling problem compared to what we accept seen before; these are listed beneath.
- The maximum multiplicity (i.due east. the cardinality) in each management is "many", this is called a many-to-many human relationship between Orders and Products. We will meet how to model this in UML and so how to map this to the relational model.
- There are two attributes described, the sale price and the quantity of the product being ordered, that cannot peradventure exist attributes of either the Products or Orders classes. These attributes are a effect of the association betwixt the Order and the Product, they describe each individual instance of the clan. Below, we learn how to model such attributes in UML and then how to implement these in the relational model.
Course diagram
The many-to-many relationship volition be modeled with an association that specifies a many cardinality in the multiplicity constraints of both ends of the association.
UML gives us a way to model relationship attributes by showing them in an association form that is connected to the association past a dotted line. This is shown in the figure below with the OrderLine association class and the two attributes quantity and unitSalePrice as described above. If no information needs to be modeled most specific associations between objects (such equally the quantity and unitSalePrice), and so the UML class diagram omits the clan class. Clan classes can be fastened to associations of unlike cardinalities, not just many-to-many associations as is the case with OrderLine.
There are ii additional attributes shown in the class diagram that nosotros oasis't talked almost yet. We need to know the subtotal for each order line (that is, the quantity times the unit auction cost) and the full dollar value of each order (the sum of the subtotals for each line in that society). As simply described, the values of these two attributes can exist computed by using values in other attributes; in the class diagram nosotros precede their name by a "/" to announce that they are derived attributes.
Since these values can be computed or derived from the values of other attributes already in the database, they don't need to be stored in the database, and they are non included in the relation scheme. In fact, they could exist stored. The reader should consider the advantages and disadvantages of whether or not to shop derived attributes in the database.
Relation scheme diagram
We tin can't stand for a many-to-many association directly in a relation scheme, because two tables tin can't be children of each other — there are no parent/child roles that can exist assigned every bit both cardinalities are many. So for every many-to-many association, nosotros will need an additional tabular array, known equally a junction table, in the database that will store all the relationship instances. The relation scheme of such junction tables are also part of the database scheme diagram. The scheme of a junction table (sometimes also chosen a join tabular array or linking table) will incorporate two sets of FK attributes, one for each side of the clan. If there is no association grade, then the scheme only contains these two FKs. If there is an association class (as is the case hither with OrderLines), its attributes will go into the junction table scheme.
In general, to map a many-to-many association to the relational model, a junction table is introduced to the relational database scheme. The junction table includes 2 FKs, i to each of the related schema. The PK of this junction table is both FKs combined. If the many-to-many association has an association class, so its attributes are added to the junction table. These attributes of the association class have no influence on the PK of the junction tabular array. The PK of a junction table that implements a many-to-many association is e'er and exactly the combination of the ii FKs.
In the relational model, the many-to-many association between Orders and Products has turned into a one-to-many relationship between Orders and Club Lines, plus a many-to-one relationship between Order Lines and Products. However, this does not mean the UML class diagram tin can be updated to evidence a 1-to-many association and a many-to-one association with the respective classes. The UML form diagram given above is the correct model for the trouble. The list below provides the English description matching these 2 relational model relationships along with what the constraints say well-nigh the data in those tables
- Each Guild must be related to many OrderLines. The
1..*constraint next to the FK{custID, orderDate}ways that each PK value in the Orders table must appear as a FK value in one row of the OrderLines table, and can appear in many rows. If a FK{custID, orderDate}value appears in simply one OrderLines row, it is because the respective Order merely includes i Product. If a FK{custID, orderDate}value appears in many rows of the OrderLines tabular array, it is because the corresponding Social club includes several products so there need to be several rows in OrderLines with the same value for the FK to Orders. - Each OrderLine must be related to merely one Order. These constraints mean that each value of the FK
{custID, orderDate}must refer to at most one row in the Orders table and that row must exist. This is the referential integrity constraint which databases enforce. - Each OrderLine must be related to only 1 Production. This is another example of the referential integrity constraint, this fourth dimension it has to do with the FK
{UPC}in OrderLines referring to the PK{UPC}in Products. - Each Product may be related to many OrderLines, the
0..*constraint adjacent to the FK{UPC}in OrderLines. These constraints country that a product's PK value does not need to appear as a FK value in any row of the OrderLines table and that it could appear there in many rows. This is logical as a product might non accept been included in any gild and information technology could be included in many orders.
Unique identifiers
The UPC is an external key. UPCs are divers for virtually all grocery and manufactured products by a commercial organization called the Uniform Code Council, Inc.® Nosotros will utilize it as the chief primal of our Products table, which likewise has a candidate fundamental here: {mfgr, model}.
With Orders now a parent of OrderLines, nosotros might accept decided that it needs a surrogate cardinal (order number) to exist copied into the OrderLines. In fact, most sales systems do this, equally you lot know if you lot've e'er tried to bank check on the status of something yous've ordered from a company. For this example, it seems to be merely as like shooting fish in a barrel to stick with the existing PK of Orders, since it already has a surrogate key from Customers, and the order date doesn't add much size.
To uniquely identify each order line, we demand to know both which order this line is contained in, and which product is being ordered on this line. The 2 FKs, from Orders and Products, together form the only candidate key of this relation and therefore the chief fundamental.
Information representation
The key to agreement how a many-to-many association is represented in the database is to realize that each row of the junction table (in this example, OrderLines) connects exactly i row from the left table (Orders) with exactly 1 row from the right table (Products). Each PK of Orders can be copied many times to OrderLines; each PK of Products can also be copied many times to OrderLines. But the same pair of FKs in OrderLines can only occur once. In the figure below we testify simply the PK and FK columns for sake of space.
Source: https://web.csulb.edu/colleges/coe/cecs/dbdesign/dbdesign.php?page=manymany.php
0 Response to "What Occurs When Two Entities Can Be Related to Each Other in Many Instances?"
Post a Comment