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.
If I understood the question correctly, this should be close.
Here are few details for keys
--
-- Keys for SalesPerson
--
alter table SalesPerson
add constraint PK_salesperson primary key (PersonID)
, add constraint AK1_salesperson unique (ManufacturerID, ShowRoomNo, PersonID)
, add constraint FK1_salesperson foreign key (PersonID)
references Person (PersonID)
, add constraint FK2_salesperson foreign key (ManufacturerID, ShowRoomNo)
references ShowRoom (ManufacturerID, ShowRoomNo)
;
--
-- keys for Sale table
--
alter table Sale
add constraint PK_sale primary key (SaleID)
, add constraint FK1_sale foreign key (BuyerID)
references Person (PersonID)
, add constraint FK2_sale foreign key (ManufacturerID, ModelName, ShowRoomNo)
references CarDisplay (ManufacturerID, ModelName, ShowRoomNo)
, add constraint FK3_sale foreign key (ManufacturerID, ShowRoomNo, SalesPersonID)
references SalesPerson (ManufacturerID, ShowRoomNo, PersonID)
;