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:
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.
• 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.
• 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.
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.