Consider this situation: a Car is bought from a Salesperson. A Salesperson works at a Showroom (and at only one Showroom). A Showroom is affiliated to a Manufacturer, and only sells cars made by that Manufacturer. At the same time, a Car is of a particular Model, and a Model is made by a Manufacturer.
Restriction R: A Car's Model's Manufacturer must be the same Manufacturer as the Car's Salesperson's Showroom's affiliated Manufacturer.
The diagram shows the obvious foreign key relationships.
----> Manufacturer <----
| |
| |
Showroom |
^ |
| Model
| ^
Salesperson |
^ |
| |
--------- Car ----------
How do you enforce Restriction R? You could add a foreign key relationship Car --> Manufacturer
. Yet the Manufacturer of a Car can be established by joining tables one way or another around the "diamond", so surely to do this would not be normalised? And yet I do not know otherwise how to enforce the constraint.