This appendix overviews the physical data modeling notation used throughout the book. We have used a subset of the notation described in the Unified Modeling Language (UML) profile originally presented in Agile Database Techniques (Ambler 2003) and now maintained online at www.agiledata.org/essays/umlDataModelingProfile.html.

Figure A.1 describes the basic notation for tables within a database schema. Tables are represented as boxes with one, two, or three sections. The first section, the one containing the table name, is mandatory. The other two are optional, the second one listing the columns of the table, and the third one listing the triggers associated with the table (if any). In the column list, only the names are mandatory; throughout this book, for the sake of simplicity, we often list the names but not the types of the columns. When a column is part of a key, it is followed by one or more UML stereotypes, described in Table A.1.

Figure A.1. Notation for modeling tables.

It is possible to indicate more information pertaining to keys, as you can see in Figure A.2. The PolicyNotes table has three keys: a primary key made up of the PolicyNumber and NoteNumber columns, the first alternate key made up of the PolicyOID and NoteNumber columns, and the second alternate key PolicyNoteOID column. When a key is composite—in other words, it is made up of several columns—it can be important to indicate the order of the columns within the key so that the corresponding indices are defined properly. Order is shown via the order named value. For example, we can see that the PolicyNumber column is the first column within the primary key and that NoteNumber is the second column. Because it adds clutter to your diagrams, you should indicate the order of columns only when necessary.

Figure A.2. Notation for modeling the details of keys.

Relationships, often called associations, are modeled as solid lines between two tables. In Figure A.3, you would say that a customer may own zero or more policies, and that a policy is owned by a single customer. The arrowhead beside the owns label on the relationship between Customer and Account indicates the direction in which to read the relationship; this is an optional symbol to be used only when it is not clear which way to read it. Common convention is to write a label so that it makes sense when read from left to right, or top to bottom, as the case may be (Ambler 2005b). We know that customers may own zero or more policies—because of the multiplicity indicator of 0..* on the relationship line beside the Policy table—and that any given policy is owned by only one customer (as indicated by the other multiplicity indicator). Table A.2 summarizes the various multiplicity indicators.

Figure A.3. Notation for modeling relationships.

In Figure A.3, we would also say that a customer accesses zero or more accounts and that an account is accessed by one or more customers. Although there is a many-to-many association between the customer and account entities, we cannot natively implement this in a relational database, hence the addition of the CustomerAccount associative table to maintain the relationship. Furthermore, an order item is part of a single order, and an order is made up of one or more order items. The diamond on the end of the line indicates that the relationship between Order and OrderItem is one of aggregation, also called a “part of” association. When the multiplicity is not indicated beside the diamond, a 1 is assumed. Figure A.4 presents several more examples of relationships between tables and how to read them.

Figure A.4. Examples of relationships.

Figure A.5 overviews the notation for several other common database concepts:

Figure A.5. Notation for modeling stored procedures, views, and indices.

• Stored procedures. Stored procedures are presented in a two-section rectangle. The top section indicates the name of the database and the stereotype of Stored Procedures. The bottom section lists the signatures of the stored procedures (or functions) within the database.

• Indices. An index is modeled as a box with the name of the index, the stereotype Index, and dependency relationships pointing to the column(s) upon which the index is based.

• Views. A view is depicted as a two-section rectangle. The top section indicates the name of the view and the stereotype View. The bottom section, which is optional, lists the columns contained within the view.