Search code examples
sqldatabase-normalization

What normalisation form is a restaurant menu?


This is probably off-topic but I'm curious.

Say you have a normal restaurant menu with an itemID, meal name and price. What normal form is it?

I say it's BCNF because but a friend brought up that it isn't in 3NF because of transitive dependency price -> item name -> item number. What do you guys think?


Solution

  • A restaurant menu isn't in any normal form. As with any other set of requirements, a menu could be represented either as a relation schema in BCNF or as some other schema not in BCNF. That's a choice the database designer makes when he creates the schema; not something implicit in the menu information itself.

    You haven't exactly specified the schema in question. Assuming the relation:

    Menu {ItemID, Name, Price}
    

    with two keys: {ItemID} and {Name} then Menu is in BCNF with respect to the set of functional dependencies:

    ItemID -> Name -> Price
    Name -> ItemID -> Price
    

    These are not non-key transitive dependencies because both Name and ItemID are candidate keys. Since Menu is in BCNF it is also in 3NF.

    Functional dependencies are always written in the form Determinant -> Dependant. I don't see how Price would ever be likely to be a determinant (a unique price for every item on the menu seems extremely unlikely) so the dependency you mentioned: price -> item name -> item number doesn't make much sense to me. If price did happen to be a non-key determinant for some reason then the above Menu relation would of course violate BCNF (and 3NF).