Is it possible for total participation constraints to be enforced by an attribute?

Posted on

Question :

For a school Food Delivery application I am developing, I’ve been told to use the most accurate ER model that captures as many constraints as possible. In particular, I have decided to enforce a given constraint

“Each order’s food items must be from a single restaurant”

using an aggregation from Orders to Restaurant-FoodItem relationship. However, as the application layer requires to get a restaurant_id associated to an Order, what I am currently doing to get restaurant_id is to join all the way from Orders to Restaurants, following the edges on the ER model.


There was another alternative suggested by my teammate to just put restaurant_id as an attribute in Orders to enforce the constraint as well as allow easier more efficient retrieval of restaurant_id. May I know whether the suggested alternative allows us to enforce the earlier constraint?

Answer :

This would be one way to enforce the constraint.

-- User USR exists.
user {USR}
  PK {USR}
-- Restaurant RST exists.
restaurant {RST}
        PK {RST}
-- Fare (food, drink) FAR exists.
fare {FAR}
  PK {FAR}
-- Restaurant RST serves fare FAR.
menu {RST, FAR}

FK1 {RST} REFERENCES restaurant {RST}
FK2 {FAR} REFERENCES fare       {FAR}
-- User USR placed (his/her) order number ORD#,
-- from restaurant RST.
order {USR, ORD#, RST}
   PK {USR, ORD#}
   SK {USR, ORD#, RST}

FK1 {USR} REFERENCES user       {USR}
FK2 {RST} REFERENCES restaurant {RST}
-- User USR ordered QTY of fare FAR
-- from restaurant RST in (his/her) order number ORD#,
-- as item number ITM# of that order.
order_item {USR, ORD#, RST, ITM#, FAR, QTY}
        PK {USR, ORD#, RST, ITM#}
        AK {USR, ORD#, RST, FAR}

order {USR, ORD#, RST}

menu {RST, FAR}


All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key
Using suffix # to save on screen space.
Works for SQL Server and Oracle, for others use _NO.
For example, rename ORD# to ORD_NO.

Leave a Reply

Your email address will not be published. Required fields are marked *