Basic structure: associations

The UML association

The UML association (ER term: relationship) is the way that two classes are functionally connected to each other.

Example: We want our customers to be able to place orders for the products that we sell, so we need to model the Order class and its association with the Customer. Notice that while the Customer class represents a physical “thing,” the Order class represents an event that happens in the enterprise. Both are equally valid class types. We will first describe the Order:

“An order is created when a customer decides to buy one or more of our products. We need to know when the order was placed (date and time), and which customer representative sold the order.”

The association between the customer and the order will tell us which customer placed the order. We will describe the association in natural language just as we described the classes, but we will also include information about how few (at minimum) and how many (at maximum) individuals of one class may be connected to a single individual of the other class. This is called the multiplicity of the association (ER term: cardinality), and we describe it in both directions.

“Each customer places zero or more orders.” (* in the diagram below means “many”, and any quantity more than one is the same as “many” in a database.)

“Each order is placed by one and only one customer.” (Bad English—passive voice—but makes sense!)

Class diagram

Customer-order class diagram

Other views of this diagram: Large image - Data dictionary (text)

• In the diagram, the association is simply shown by a line connecting the two class types. It is named with a verb that describes the action; an arrow shows which way to read the verb. Symbols at each end of the line represent the multiplicity of the association, as we described it above.

• Looking at the maximum multiplicity at each end of the line (1 and * here), we call this a one-to-many association.

• The UML representation of the Order class contains only its own descriptive attributes. The UML association tells which customer placed an order. In the database, we will need a different way to identify the customer; that will be part of the relation scheme (below).

Relation scheme diagram

The relation scheme for the new Orders table contains all of the attributes from the class diagram, as before. But we also need to represent the association in the database; that is, we need to record which customer placed each order. We do this by copying the PK attributes of the Customer into the Orders scheme. The copied attributes are called a foreign key (FK), which is simply an image of the linked relation’s primary key.

Customer-order
relation scheme diagram

Other views of this diagram: Large image - Data dictionary (text)

• Since we can’t have an order without a customer, we call Customers the parent and Orders the child scheme in this association. The “one” side of an association is always the parent, and provides the PK attributes to be copied. The “many” side of an association is always the child, into which the FK attributes are copied. Memorize it: one, parent, PK; many, child, FK.

• An FK might or might not become part of the PK of the child relation into which it is copied. In this case, it does, since we need to know both who placed an order and when the order was placed in order to identify it uniquely.

The child table

The Orders table is created in exactly the same way as the Customers, including all of the attributes from the Orders scheme:

        CREATE TABLE orders (
          cfirstname    VARCHAR(20),
          clastname     VARCHAR(20),
          cphone        VARCHAR(20),
          orderdate     DATE,
          soldby        VARCHAR(20));

• Notice that the FK attributes must be exactly the same data type and size as they were defined in the PK table.

• The DATE data type includes the time in some database systems, but not in others (which would need an additional ordertime attribute to permit more than one order per customer in a single day). For simplicity, we have omitted the time from our illustrations.

• To insure that every row of the Orders table is unique, we need to know both who the customer is and what day (and time) the order was placed. We specify all of these attributes as the pk:

        ALTER TABLE orders 
          ADD CONSTRAINT orders_pk
          PRIMARY KEY (cfirstname, clastname, cphone, orderdate);

• In addition, we need to identify which attributes make up the FK, and where they are found as a PK. The FK constraint will insure that every order contains a valid customer name and phone number—this is called maintaining the referential integrity of the database.

        ALTER TABLE orders 
          ADD CONSTRAINT orders_customers_fk
          FOREIGN KEY (cfirstname, clastname, cphone)
          REFERENCES customers (cfirstname, clastname, cphone);

• When you look at some typical data in the Orders table, you will see that some customers have placed more than one order. For each of these, the same customer information is copied in the FK columns—but the dates will be different. Of course, we hope to see many orders that were placed on the same date—but the customers will be different. You will also see that some customers haven’t placed any orders at all; their PK information is simply not found in the orders table.

Orders
cfirstname	clastname	cphone	orderdate	soldby
Alvaro	Monge	562-333-4141	2003-07-14	Patrick
Wayne	Dick	562-777-3030	2003-07-14	Patrick
Alvaro	Monge	562-333-4141	2003-07-18	Kathleen
Alvaro	Monge	562-333-4141	2003-07-20	Kathleen

Note: The date format shown in our examples ('yyyy-mm-dd') is used by many but not all systems. Consult the reference for your own software to be sure.

Copyright © 2002–2006, by Tom Jewett. Links to this site are welcome and encouraged. Individual copies may be printed for non-commercial classroom or personal use; however, this material may not be reposted to other web sites or newsgroups, or included in any printed or electronic publication, whether modified or not, without specific permission from the author.