In an exam past paper I am asked:
Use Entity Relationship modelling to identify relations, including attributes and indicating primary and foreign keys.
The narrative is as follows:
A Pizza joint stores data on pizza orders. Orders have a unique order id, customer name and consist of one or more pizza requests. For each pizza requested in an order, an identifying number is allocated, and the pizza name and type of base desired is held; optional extra topping details are also stored. Toppings are identified by a topping code and have the name stored.
So, going with the narrative, I figured:
Order(OrderID, CustomerName, PizzaorderNo*)
PizzaOrder(OrderID*, PizzaOrderNo, pizzaName, baseType)
Topping(ToppingCode , ToppingName)
Please note that I have used bold for Primary key, and * for a Foreign Key.
However, in the answers section, it states:
OrderPizza(OrderId*, PizzaOrderNo, PizzaName, DesiredBase)
OrderPizzaTopping(OrderId*, PizzaOrderNo*, ToppingCode*)
I understand that OrderPizzaTopping above is used to denote all the foreign keys, thereby rendering it as a link entity, but I don't understand how to determine where a link entity is required using a narrative such as this. I understand that the relational model does not like/permit M:N (many-to-many) and so this requires the use of the link entity to decompose this into multiple 1:M or 1:1 relationships.
Is there a hard and fast rule such as, if there is more than 2 primary keys included, a link entity is required?
I am also unsure as to why the answer section has the Order relation as only containing the OrderID and customerName, because the narrative clearly stipulates that "...and consists of one or more pizza requests". Why then has the pizzarequests been parsed into its own relation and not included in the Order relation? Is it because it contains attributes not directly related to the Order, but which are more pertinent to the order directly(pizzaName, baseType)