Logical - Physical Design Transition Problem

From: Matt Randle <matt_at_imat.demon.co.uk>
Date: Wed, 19 May 1999 13:13:09 +0100
Message-ID: <927116339.11503.0.nnrp-02.d4e48d0d@news.demon.co.uk>

Hello,

I am running into problems trying to create integrity constraints to model the following problem. I think I am missing some fundamental RDB principle but can't see what it is!! Has anyone any ideas ??

In the Logical model we have identified four Entities - Customer, Warehouse, Order and Shipment. They are related as follows,

Customer has many Warehouses
Warehouse belongs to one Customer

Customer has many Orders
Order is for one Customer

Order has many Shipments
Shipment is for one Order

Shipment is sent to one Warehouse
Warehouse receives goods from many Shipments

At first glance it seems easy as most of the referential integrity constraints are basic one to many.

However, the rule I cannot enforce is that Shipments must be sent to a Warehouse owned by the Customer that placed the Order. For example, the .db should not let you create a shipment record for Order A & Warehouse B if Warehouse B is not owned by the Customer placing Order A.

Matt. Received on Wed May 19 1999 - 07:13:09 CDT