O'Reilly logo

Database Design and Relational Theory by C.J. Date

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

THE INVOICES AND SHIPMENTS EXAMPLE

I now turn my attention to a more elaborate example. The example—which is based on a real world application, incidentally—concerns invoices and shipments, and there’s a one to one relationship between these two entity types: Each shipment has exactly one invoice, each invoice has exactly one shipment. Here then is the “obvious” database design (for the sake of the example, I use a hypothetical syntax that explicitly distinguishes between primary and alternate keys):[175]

     INVOICE  { INVNO , SHIPNO , INV_DETAILS }
              PRIMARY KEY { INVNO }
              ALTERNATE KEY { SHIPNO }
              FOREIGN KEY { SHIPNO } REFERENCES SHIPMENT

     SHIPMENT { SHIPNO , INVNO , SHIP_DETAILS }
              PRIMARY KEY { SHIPNO }
              ALTERNATE KEY { INVNO }
              FOREIGN KEY { INVNO } REFERENCES INVOICE

So the database structure is as shown in Figure A-1 (note that the arrows in that figure, in contrast to arrows in figures elsewhere in this book, represent foreign key references, not functional dependencies):

The invoices-and-shipments database

Figure A-1. The invoices-and-shipments database

Now, each relvar in this example actually has two keys, {INVNO} and {SHIPNO}. However, I assume we can agree for the sake of argument that the “natural” primary key for INVOICE is {INVNO} and the “natural” primary key for SHIPMENT is {SHIPNO}; then {SHIPNO} in INVOICE and {INVNO} in SHIPMENT are alternate keys. Furthermore, of course, each of those alternate keys is also ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required